Database modelling case study
### Q3. Database modelling - case study (3 marks)
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 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. List the schemas for all the derived tables for this database, marking out the primary keys and foreign keys if any
### Q4. Selected Additional Exercises (3 marks)
Q4- (i) - Describe briefly a database application case of your choice and represent your data in terms of 3 linked (via foreign key/s) tables. List their respective primary key and foreign key/s if any. Your database application case should be different from those already covered in the lectures or practicals.
Q4- (ii) - Describe the relation that is produced by the following relational algebra operation
city,hotelName,type,price (Hotel Hotel.hotelNo=Room.hotelNo (type='Family' ∨ type='Suite'(Room))
(iii) - For the query "list all the distinctive hotel names for all the hotels in the city London", write it as an expression for the relational algebra.
### Q2. More on SQL queries (0.5 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)
- 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. (0.5 marks)
