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.)
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)
Diagram 2. Entity Relationship Diagram (underline attributes are primary keys and foreign key are underlined and mentioned as FK)
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".
