Reference no: EM133761151

Database Design and Development

Question 1. Basic database design and queries

A small book publisher wants to create a simple database to keep track of which reviewers have been assigned to review which book for the publisher, along with their review reports, and which book has been written by which authors. For simplicity, a book will be recorded for its title and author/s, an author or reviewer will be recorded for his or her name, and a review report should contain an integer rating value between 1 and 5 (inclusive) and the date on which the review report is completed. Additional details are not required unless a proper database design mandates them.

Design a minimum database (a database of only essential attributes) to fulfil this purpose, and draw the ER diagram for your design. Indicate on the ER diagram the primary keys (PK), additional candidate keys (CK) if any, and the relationship multiplicities. By minimum we mean that you don't need to add anything that is not explicitly stated in the requirements, unless it's one of your artificially generated keys. For instance, you don't need to include a telephone number or email address for the entity corresponding to an author or reviewer.

Draw the Global Relation Diagram (GRD) corresponding to the ER diagram in the above, indicating all the primary keys, additional candiate keys and foreign keys where applicable. The GRD should be in a form similar to Figure 17.9 (page 554 or 516 for edition 5) of the textbook, but all the attributes should be kept there too.

Write an SQL script (of statements) that generates all the tables for your designed database.

Write an SQL script to insert sufficient records into your tables. Each table should contain no less than 3 records. At least 2 books should each have 2 or more reviewers' reports. Screenshots are required for the records of all the tables.

List all authors whose name contains your own family name as a substring. Insert sufficient records into your table/s so that your query returns at least 2 records. (screenshot required).

For a given book (or book number), write an SQL statement to list all the assigned reviewers by their name and their corresponding rating for the book. Properly choose your book or insert sufficient records into your table/s so that your query returns at least 2 records. (screenshot required).

List all books by their title, along with the name/s of the assigned reviewer/s, their respective rating and date on which the review is completed. Order the output alphabetically, first by the book title, then the reviewers' name, and finally the completion date. (screenshot required).

Note that you must provide the screen shots (via ALT- PRINT-SCREEN on Windows for instance) for your results (of executing SQL statements). For each screenshot of executing an SQL script, you must keep your username and at least part (if long) of the SQL script in your screenshot. If your username is absent from a screenshot, then the screenshot may not be considered as your work. The screenshot
on the right is such an example in which you can see the username "zhuhan" and the full SQL script that produces the result. Not supplying the screenshots, or the screenshots are not readable, may lead to the loss of up to 30% of the corresponding marks.

Question 2. More on SQL queries
A DreamHome database has been created according to a case study for a property rental business (see §11.4 at pages 381-401, or 347-367 for edition 5, of the textbook for the case study). Its (simplified) database schemas (§6.3 at page 197 or 189 for edition 5) and the relation diagram are given as

Draw an ER diagram to represent the above table-linking diagram (which is essentially what we would call a relation diagram). The ER diagram should bear fewer entity types than the number of tables in the above displayed diagram. That is, the table or tables that essentially represent relationships should be represented as relationships on the ER diagram, not as entities.

Create this set of tables and fill the records by excuting this given SQL script dreamhome.sql. Then use an UPDATE statement in SQL to modify the staff member "Julie Lee" into your own name and modify the date of birth (13/6/1975) into a date after 1990s. If you have a team member for this assignment, then also UPDATE the staff record for staffNo="SL21" by replacing the name "John White" there by that of your team partner (screenshot required for the resulting Staff table).

Write an SQL statement to list the staff name, their position, gender and annual salary, for all those who earn 25000 per annum or more (screenshot required).

Write an SQL statement to list propertyNo, postcode, and the name of the owner of the property. Sort the output according to the owner name alphabetically (screenshot required).

Write an SQL statement to list all the properties that have been viewed by one or more clients. More precisely, list the postcode, propertyNo, the street of the property, last name of the staff responsible for this property, client's last name, and the viewing date. Order the output first by the postcode, then by the street (screenshot required).

Write an SQL statement to list for all properties for rent the propertyNo, the city where the property is located, the city of the branch which is contracted to rent the property, as well as the city of the branch that provides the individual staff who handles the actual renting of the property. Insert enough records, if necessary, to show that these 3 cities for a given property could be totally different with the current GRD prescribed in the above (screenshot required).

Note that the screenshots you provide should again conform to the requirements indicated in the previous question. For students working in a team of 2 members, the student name contained in the screenshots for this question should be different from the one in the previous question. Otherwise the person whose name is neither on the screenshots of this question nor on those of the previous one may lose the corresponding marks.

Question 3. Database modelling - case study

A combined medical and herbal centre wants to create a Prescription database to keep track of its medics, patients, herbal medicine, diagnosis, and herbal prescriptions. For simplicity, both a herbal medic and a patient will be recorded for his or her name; a herbal medicine will be recorded for its name, unit and the price per unit; a diagnosis will be recorded for a line of textual description, the date of the diagnosis, and the relevant patient and medic; and a prescription will be recorded for its date, the patient and medic, and all the herbs included in the prescription. We note that a unit for a herbal medicine could be a capsule, a gram, a kilogram, a piece, a bunch, a box, or a bottle, etc. Also bear in mind that a medical prescription here may or may not be directly related to a particular diagnosis.

Design a minimum database (a database of only essential attributes) to fulfil this purpose, and draw the ER diagram for your design. Indicate on the ER diagram the primary keys, additional candidate keys if any, and the relationship multiplicities. You must use the same notation scheme for the ER diagram as the textbook, and the ER diagram should be strictly in the sense the textbook uses. Briefly explain the roles played by each entity type and relationship type in your design in terms of the design goals.

List just the schemas for all the derived tables for this database, indicating their respective primary keys, additional candidate keys and foreign keys where applicable.

For a given prescription (number), write an SQL statement to list all the herbs there, their corresponding price per unit, and the number of units prescribed (screenshot required).

Rewrite the previous query as an expression in the form of a relational algebra.

For all prescriptions, list the patient name, prescription date, prescription number, and the corresponding doctor's name. Sort the output according to patient name alphabetically, and then to the prescription date in the reverse order (screenshot required).

Question 4. Selected Additional Exercises

Complete Questions α in the Additional Exercises for Practical 5.
Complete Question β in the Additional Exercises for Practical 6.
Complete Questions α in the Additional Exercises for Practical 7.