Student ID: replace by your student ID
The questions are divided into six parts. Please answer the questions in the space provided (shaded cells only!), but do not make any other changes to the rest of the document. This includes both content and format of the document (e.g. font, spacing, etc.). Any deviation from the submission instructions above will result in a mark of zero for the assessment. Feedback will be entered using track changes and comments in Word.
Part 1: Relational data model – entities and attributes [0–24 marks]
Identify all entities (a total of 8) and their attributes. Write your answers in the following table, one entity per row. Underline attributes that belong to the primary key. You can introduce essential attributes that were not explicitly mentioned.
Entity
Attributes
Mark
1
3
2
3
3
3
4
3
5
3
6
3
7
3
8
3
Total
24
Part 2: Relational data model – relationships [0–40 marks]
Identify all relationships (a total of 10) between the entities from Part 1. Write your answers in the following table, one relationship per row. Please specify the relationship’s name, the two related entities and the cardinality (1:M, M:1 or M:M) of the relationship.
Relationship
Entity 1
Entity 2
Cardinality
Mark
1
4
2
4
3
4
4
4
5
4
6
4
7
4
8
4
9
4
10
4
Total
40
Part 3: Relational data model – many-to-many relationships [0–14 marks]
There should be exactly one M:M relationship in Part 2.
• Represent this relationship using an ER diagram.
• Split this relationship into two 1:M relationships and represent them using an ER diagram.
Provide your answers in the following table. Copy/paste an image of the ER diagram in an appropriate cell. You must use Chen notation as it was described in the lectures (see example below). No other notation will be accepted.
Part
ER diagram
Mark
a
replace this text by the image of your ER diagram
5
b
replace this text by the image of your ER diagram
9
Total
14
Part 4: ER modelling [0–20 marks]
Draw an entity-relationship (ER) diagram for the problem described in the brief. Provide entities and relationships (including their cardinality) only. Do not include attributes. The diagram has to be neat and readable to get full marks. You must use Chen notation as it was described in the lectures (see example above). No other notation will be accepted.
replace this text by the image of your ER diagram
20
Part 5: Data integrity [0–24 marks]
Assume that the relations listed in the following table are used to model information described in the brief. For each relation:
• Underline attributes that belong to its primary key.
• Declare its foreign keys using the SQL syntax.
• Add any other constraints that should support data integrity in accordance with the information given in the brief.
Note: If there are no foreign keys or other constraints, you need to specify ‘none’ in the corresponding cell in order to get the full mark.
Relation
Foreign keys
Other constraints
Mark
3
3
3
3
3
3
3
3
Total
24
Part 6: Data manipulation [0–28 marks]
Using the relations given in Part 5, create SQL queries to:
• Calculate the total number of private patients for each consultant. Make sure to provide the full name for each consultant.
• Identify all room(s) that have the highest number of nurses allocated per bed. This can be only one room or more than one if there is a tie. Make sure not to return any rooms with a lower number of nurses per bed.
• Update the database to allocate the first available room to a newly admitted patient whose other information is already stored in the database using their PIN ‘P314’. Note that the room should be either single or multiple occupancy depending whether the patient is private or not.
Please comply with the following restrictions:
• Use only one SQL query per each question above, i.e. do not store any results in temporary tables. This SQL query may, however, contain nested queries and combine queries using set operations.
• Do not use explicit JOIN operations including INNER JOIN, LEFT JOIN and RIGHT JOIN. Do not use outer join, i.e. (+). You are, however, allowed to join tables using the WHERE clause.
• Do not use CASE statement. Do not use IF function.
• Do not use tables that are unnecessary for the required output.
Part
SQL query
Mark
a
10
b
10
c
8
28
Your result:
Part
Subtotal
1. Relational data model – entities and attributes
0–24
2. Relational data model – relationships
0–40
3. Relational data model – many-to-many relationships
0–14
4. ER modelling
0–20
5. Data integrity
0–24
6. Data manipulation
0–28
Total:
0–150
Final mark calculated as total divided by 3
and rounded up to the next integer: