- June 29, 2020
ITNPBD3 Repeat Assessment Summer 2020 Preamble This single piece of assessment is to be carried out by students who require a repeat of either the exam or the assignment from the spring 2020 ITNPBD3 module. The mark gained by the student will be used to replace any failed or missed grade from either the first assignment, exam or both. This assignment can be completed without access to database software. Assignment Read the questions carefully and provide exactly what is asked for. Check your answers against the list of things required for each question. Consider the following business scenario: A medical centre has patients and doctors. Doctors are responsible for many patients and each patient can be seen by more than one doctor. An appointment pairs a patient with a doctor at a given time and date. Doctors write prescriptions. Each prescription allocates a named medicine to a single patient and is signed by a doctor. Prescriptions cannot exist without an associated patient and doctor. Each appointment generates one or more prescription but each prescription comes from only one appointment. Each patient has a name, a unique health service number, a date of birth and a phone number. Each doctor has a name, a staff ID, gender, current salary and a list of specialisms. Each prescription has a serial number. Each medicine has a unique name, a list of side effects and a manufacturer. 1. Identify all the entities in this scenario. For each one, give the name of the entity, list some of its attributes, and say whether it is a weak entity or not [5 marks] 2. List the relationships between entity pairs. For each relationship, specify the two entities involved, the name of the relationship, its cardinality and its optionality. [5 marks] 3. Draw an ER diagram showing the entities and relationships you identified in questions 1 and 2 above. Choose any suitable notation, but be sure to show all the details. Include a key describing the notation you used. [10 marks] 4. Design a suitable database schema by decomposing your ER design. For each table, list the following: [3 marks each] a. Table name b. Field names and types c. Primary key field(s) – indicate whether the primary key is natural or artificial d. Any foreign key fields and the tables they reference e. Which normal form you chose for the table. Justify your choice both in terms of the definition of the normal form and in terms of usability and integrity considerations 5. Write the SQL code to create one of your tables. It does not matter which you choose. [5 marks] 6. Write the SQL code required to perform the following queries [3 marks each] a. Find the name of the patient whose health service number is 473431 b. List the names of all the doctors in the database c. Calculate the average salary of all doctors d. Calculate the average salary of doctors grouped by gender e. Select all the specialisms of the doctor with staff ID 34332 Now consider a solution to the same scenario using a document database like MongoDB. 7. Draw a document model design for the database. Make sure your model includes at least one example of a document reference and one example of an embedded document. Show the collections and the structure of the documents they contain. [10 marks] 8. Show two example documents in JSON format, each one from a different collection in your design. [5 marks] 9. For each collection you identified above, choose appropriate fields to index and justify your choice. Explain what indexing achieves and how it is implemented. Why would you not simply index all of the fields? [5 marks] 10. For each collection in your design, choose a suitable shard key. Explain what sharding is designed to achieve. [5 marks] 11. When the database is small, it will fit on a single machine but imagine it grows to cover every medical centre in the county. It will need to run over a cluster. Discuss the consequences for database design, including considerations of consistency and availability. [5 marks] 12. Write MongoDB commands to answer the following questions, based on the design you have given [5 marks each] a. Find the name (and only the name) of the doctor whose staff ID is 38543 b. List the specialities of the doctor whose staff ID is 38543 c. List all the patients ever seen by the doctor whose staff ID is 38543 Submission Write your answers in a document and upload it to Canvas on the course assignments page. See the course page for other details such as deadlines and plagiarism rules.