Q1. Basic database design and queries A combined medical and herbal centre wants to create a database to keep track of its medics, patients, herbal medicine, 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, and aprescription will be recorded for its date, the patient and medic, and all the herbs included in the prescription. i.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 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 a patient. ii.Draw the Global Relation Diagram (GRD) corresponding to the ER diagram in the above, indicating all the primary keys and foreign keys. 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. iii.Write an SQL script (of statements) that generates all the tables for your designed database. iv.Write an SQL script to insert sufficient records into your tables. Each table should contain no less than 3 records. At least 2 prescriptions should each contain 2 or more herbal items. Screenshots are required for the records of all the tables. v.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). (0.5 marks) vi.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). viiWrite an SQL statement that lists the prescription (number), the corresponding date of the prescription and the total price of the herbs within the prescription, for all the prescriptions in the database (screenshot required). viii.Extend the database to also keep track of the diagnoses of the patients, and bear in mind that a medical prescription here may or may not be directly related to a particular diagnosis. Draw the GRD for this extended database, and write an SQL script that would create all the tables for this extended database. The GRD and SQL script here should be given separately from those in the earlier subquestions. Q2. More on SQL queries (4 marks) 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 Branch(branchNo,street,city,postcode) Staff(staffNo,fName,lName,position,sex,DOB,salary,branchNo) PropertyForRent(propertyNo,street,city,postcode,type,rooms, rent,ownerNo,staffNo,branchNo) Client(clientNo,fName,lName,telNo,prefType,maxRent,eMail) PrivateOwner(ownerNo,fName,lName,address,telNo,eMail,password) Viewing(clientNo,propertyNo,viewDate,comment) i.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. ii.Create this set of tables and fill the records by excuting this given SQL script dreamhome.sql. Insert at least one extra record into each of these tables. This extra record should be "pertinent" to yourself as much as possible so that they stand out easily from other students' work. For example, you can put your name for the staff record, and your assignment partner's name for the client. All the foreign keys for your newly added records must point to these newly added records. iii.Write an SQL statement to list the staff name, their position, gender and annual salary, for all those who earn 10000 per annum or more (screenshot required). (0.5 marks) iv.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). (0.5 marks) v.Write an SQL statement to list by the city the total number of properties that have 4 or more rooms for rent (screenshot required). (0.5 marks) vi.Write an SQL statement to list staff name, position, and the number of properties managed by the staff member, ordered alphabetically according to the last name (screenshot required). (0.5 marks) vii.Write an SQL statement to list, for all the properties that have been viewed by one or more clients, the client name, the propertyNo of the property the client viewed, the street and the city of the property, and the viewing date. Order the output first by the client name, then by the viewing date (latest first), then by the city and finally by the street name (screenshot required). Q3. Database modelling - case study (4 marks) In this part, you are asked to design a database to support the recruitment of casual staff for a school. This design will be the basis for part of the forthcoming Assignment 2. The major business requirements are summarised below in the Mini Case: Instant Recruitment. You are asked to develop a detailed Entity-Relationship model for this mini case. Your ER model should consist of a detailed ER diagram integrated with itemised discussions on the features of the entities and relationships and all the assumptions you made. The ER diagram and the accompanying document should identify keys, constraints, entity types, relationship types, specialisation/generalisation, etc. The ER diagram should in general have more than 5 entity or relationship types and no more than 15. This means that students will have to selectively identify those most important entity types and relationship types for their work and for the fulfilment of the key business functionalities. More concretely, your deliverables should include a detailed ER diagram for the model, highlighting primary keys, multiplicity constraints, generalisation/specialisation, important attributes, and other pertinent details. 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. We note that our past experiences show drawing an ER diagram on Microsoft Powerpoint gives one better control and flexibility when compared with drawing diagrams on Microsoft Word. i.The ER diagram should include, among others, representative attributes for all entity types, proper subclassing, and correct participation multiplicities for the relationship types. It should be meaningfully and well designed, and should also include all relevant and necessary aspects, and indicate any supplementary business rules if you decide to introduce. (3 marks) ii.Among all the weak entity types, explain for one of them why it is a weak entity type. If your current design doesn't have any weak entities, then explain which one of your current entities may be made a weak entity and why. (0.5 marks) iii.Among all the generalisation/specialisation cases in the ER diagram, find a relatively nontrivial one in terms of the subclassing complexity, and explain the meaning of the actual subclassing in this case and the mearning of the associated participation and disjoint constraints. (0.5 marks) We note that, the design of this database can be much simpler or clearer if the "express recruitment" in the mini case is not considered, i.e. all casual recruitments are considered "regular". Hence students may initially design their database under this assumption, if they wish, before attempting the final complete design