University of Sussex Autumn 2016 Informatics
Databases Assignment 1 (Deadline 20.10.16, 4pm)
The coursework assigned below must be submitted as E-submission in one single pdf file on StudyDirect by 4 pm on Thursday, 20 October 2016. Note that no other formats than pdf will be accepted.
You are not allowed to share or discuss solutions with other students. The university rules for collusion and plagiarism apply and any cases discov- ered will be reported and investigated. A decent presentation of your work is expected.
All THREE questions must be answered.
1. You have been asked to design part of a database for a hospital. The hospital managers provided you with an informal description of their data which you can find on the next page in Table 1.
(a) Provide an Enhanced Entity-Relationship Model for the hospi- tal’s data model (Table 1) in form of a diagram in Chen notation. Identify special attributes (including keys) in the diagram and include participation and cardinality constraint annotations for your relationships.
Please list and explain any additional assumptions you think you have to make because the specification was unclear or incom- plete. Note that there should not be many such extra assump- tions.
The following criteria will be used to mark your model:
- extent to which your model is correct, minimal (no feature modelled twice), complete and expressive
- correct use of Chen notation (including special attributes)
- correct cardinality and participation constraints (including
notation)
- readability and presentation of your answer. You can write
the diagram in any software system you like, as long as you stick to the notation used in the lectures and embed it into your pdf-submission.
1
[44 marks]
(b) Document and explain your choice of cardinality and participa- tion constraints from Question (a) (following the schema pre- sented in lectures). Include references to the specification to cor- roborate your decisions. Note that your answer must be in agree- ment with the constraints you added to your Entity-Relationship diagram in Question (a). [18 marks]
We employ doctors and we keep their first and last name, national insur- ance number, salary, date of birth, various phone numbers so we can reach them at all times, a home address, and their special area of expertise. An address consists of street, house number, name of town, and postcode. We also keep track of the number of operations a doctor has been involved in.
Our operating theatres are numbered. For each operation we store the theatre number. Moreover, we record the type of the operation, the date of the operation, the time the operation started and how long it lasted. An operation is carried out by one or several doctors on a single patient and we need to be able to later recall who operated on whom. Operations are only put on record once we know who the doctors are and who the patient is. Each doctor involved in an operation provides a short textual statement about how it went.
About a patient we keep the following information: their national insurance number, date of birth, first name and last name, gender, height, weight, address, and medical records which are numbered sequentially for each patient. An address consists of street, house number, name of town, and postcode.
A medical record consists of a date and time and a diagnosis (text). Such a record must have been made by exactly one doctor. The medical records for a patient do not have to come all from the same doctor. Patients can appear on our system before they have a medical record. Some doctors (e.g. in training) may not be allowed to diagnose patients (or operate on them) yet. Some doctors may have one mentor who is also a doctor. Only a few senior doctors are mentors and each usually has four or five tutees.
Table 1: Specification of the Hospital Database
2
2. Give a Relational Model that corresponds to the Entity-Relationship Diagram given below:
You can present this model as a Relational Database Schema or as a diagram (see Lecture 6). In any case, it must be clearly stated what primary and what foreign keys are and what the foreign keys reference exactly. You may want to use the translation explained in Lecture 7, but only provide your final result (don’t show your working). In your schema you are supposed to use the same names for attributes and relations as given in the diagram. [26 marks]
3. Give TWO constraints on relationship is Married To that are not in the Entity-Relationship diagram above, because they cannot be ex- pressed as structural constraints. The constraints should not be do- main constraints but concern how entities can be associated in this
relationship.
[12 marks]
3