Design a database system for the publishing company

Assignment Help Database Management System
Reference no: EM131305316

1) Analyse situations and/or environments for the application of database solutions including web connectivity

2) Create, alter and remove relational tables, indexes and data using an appropriate database language.

3) Explain the central concepts of database and web database systems, including constraints in the design of databases due to issues of concurrency, security and integrity.

4) Design and justify the design of database system and select, justify the selection and exploit any associated underpinning technologies, including web based technologies.

Part 1:

1. The Brief:

Consider the following requirements and design a database to support such an application.

Design a database system for the publishing company, "The National Publishing Company L.L.C." with the following information:

The company publishes books on various categories like stories, biography, poetry, horror, scientific, health, business, computer etc. The books are written by authors who can be from different nationality. They normally specialize in one category. Each book has ISBN number, publishing date, price etc.

One book is based on one category and it is generally written by one author. The company employs editors whose job is to edit one or more books. Each author works with one editor and is supervised by other editors.

The Company also employs a Managing Editor who supervises all the editors. Also, the Company employs other staff like photo editors, graphic designers, sub editors, PRO (Public Relations Officer) etc. In addition, the Company has plans to join in partnership with other publishing companies to become the leading publisher of the region.

You are free to add some more features in the database. In the design specification you must explain and justify what features you have added to the database.

2. What you have to do

You have to produce and submit the following THREE items:

1-An Entity-Attribute Diagram (see diagram I below for an example): This shows all entities in the above model, and for each entity, lists all attributes present along with the data type and size of each.

2-An Entity-Relationship Diagram (See diagram II below for an example): This details the relationships between entities you have identified in Entity-Attribute Diagram (Entities in this section should be in outline form, with only the primary and foreign keys detailed in the entity)

3-A Design Specification that discusses and details:

Database Design - Additional purposes that your database will address

Referential Integrity - How is it maintained?(Primary, Foreign key etc.)

351_figure3.jpg

Diagram II. Example Entity Relationship diagram(underlined attributes are primary keys

Part -2:

1. The Brief:

Using Oracle DBMS you are to implement the design outlined below, in which, following your submission of the required Structured Query Language(SQL) script files as listed below, you are to demonstrate your database in the presence of the module tutor. Please note that your database must be usable in the PC Labs of Gulf College.

Note: This assignment demonstrates your ability to implement and improve a database, from a given design. You are therefore required to implement the design as shown in Diagrams I and II below.

Diagram 1. Entity Attribute Diagram (with data types identified)

172_figure1.jpg

Diagram 2. Entity Relationship Diagram (underline attributes are primary keys and foreign key are underlined and mentioned as FK)

2112_figure2.jpg

For higher marks, this design should be enhanced by the appropriate use of domains, access control mechanisms, additional tables for extra functionality etc. However, note that any amended structural design should still meet the specification outlined above. If you choose to add additional tables, you must supply a revised Entity-Relationship diagram.

2. What you have to do

You have to produce, submit and demonstrate the following THREE SQL tasks:

(I) A script to create tables and all other structural components (domains, access control)

(ii) A script to populate the database with appropriate sample data to allow testing of your database

(iii) A collection of individual query scripts to execute as a minimum the following queries:

a. Develop an SQL query to list all the doctors in the alphabetical order of their names.

b. Develop an SQL query to list all the patients along with the doctor treated, sorted in ascending order of the doctor name, then patient name.

c. Develop an SQL query to show the doctors who have most number of patients.

d. Develop an SQL query to list the patient name, bill number, bill date, medicine name, medicine rate, quantity for the patient "Thahira".

532_figure4.jpg

Reference no: EM131305316

Questions Cloud

Why are structural controls a controversial mitigation tool : Why are structural controls a controversial mitigation tool? How can structural mitigation negatively affect the areas they are presumably protecting?
Why would investors have invested in stocks : "many investors who bought stocks in 2000 and held them through 2010 found that they had received a negative real return on their investment over the 10-year period." - Why would investors have invested in stocks during those years if they received..
Write an eassy using the given points : Write an eassy using the given points:In" A Review of Good Video Games and Good Learning," Katharine L. Daniels, a professor of University of North Carolina, Wilmington, USA, argues that playing video games is good for developing children's brain ..
Explain how they serve to reduce hazard risk : What are some impediments faced by communities wishing to perform hazard mitigation?
Design a database system for the publishing company : Design a database system for the publishing company- The National Publishing Company L.L.C - Explain the central concepts of database and web database systems, including constraints in the design of databases due to issues of concurrency, securit..
What is a pricing anomaly and mean reversion : What is a pricing anomaly? How might an investor use a pricing anomaly to earn above average returns?- What is mean reversion? How might an investor use mean reversion to earn above-average returns?
Find what special needs populations exist in your community : Determine what special needs populations exist in your community. Select one, and find out whether special preparedness and emergency planning considerations have been made to accommodate their unique needs.
Why supporters of efficient markets hypothesis unconvinced : What is a bull market?- If stocks issued by technology firms do in fact consistently outperform other stocks during the second year of a bull market, is this a pricing anomaly?
Prepare the film worksheet assignment : The Film Worksheet assignment is similar to the Art Worksheet, except that you will have more to keep track of -- sound effects, moving images, and a plot with characters. I recommend looking over the film worksheet before you go to the theater an..

Reviews

inf1305316

12/15/2016 7:55:49 AM

One doubt in point 4 of learning outcomes it is mentioned as web based technologies should also be addressed. May I know wat is that clearly.herewith attached the section 1 of the assignment

Write a Review

Database Management System Questions & Answers

  Knowledge and data warehousing

Design a dimensional model for analysing Purchases for Adventure Works Cycles and implement it as cubes using SQL Server Analysis Services. The AdventureWorks OLTP sample database is the data source for you BI analysis.

  Design a database schema

Design a Database schema

  Entity-relationship diagram

Create an entity-relationship diagram and design accompanying table layout using sound relational modeling practices and concepts.

  Implement a database of courses and students for a school

Implement a database of courses and students for a school.

  Prepare the e-r diagram for the movie database

Energy in the home, personal energy use and home energy efficiency and Efficient use of ‘waste' heat and renewable heat sources

  Design relation schemas for the entire database

Design relation schemas for the entire database.

  Prepare the relational schema for database

Prepare the relational schema for database

  Data modeling and normalization

Data Modeling and Normalization

  Use cases perform a requirements analysis for the case study

Use Cases Perform a requirements analysis for the Case Study

  Knowledge and data warehousing

Knowledge and Data Warehousing

  Stack and queue data structure

Identify and explain the differences between a stack and a queue data structure

  Practice on topic of normalization

Practice on topic of Normalization

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd