ERD Narrative (p158 Lab Manual)

1. ERD Narrative (p158 Lab Manual) With Additional Notes!!

2. Oakfield Orthopaedic Hospital Oakfield is a private orthopaedic hospital located in the Galway region. It has three operating theatres and five clinics. When operations are being performed, there will always be two or more doctors present. A patient has at least two appointments prior to an operation.

4. Cannot have Many to Many • Will create duplicate entries to a database and as such this significantly compromises the database design • Need to remove Many to Many relationship using an associative entity

6. So Where to put the foreign key? • How do you decide which entity to put the foreign key within • E.G. Why not put the Op Theatre_ID in the Hospital entity instead of having the Hospital_ID within the Operating Theatre entity

7. So Where to put the foreign key? • Think about how the data is stored. • The hospital entity will be created once in the system (i.e. there is one hospital) • There are many different operating theatres and as such an instance of that entity must be created for each operating theatre

8. So Where to put the foreign key? • The hospital entity will be created once in the system (i.e. there is one hospital) • There are many different operating theatres and as such an instance of that entity must be created for each operating theatre Op Theatre Hospital Op Theatre Op Theatre Op Theatre

9. If we stored the Op Theatre_ID in the Hospital Entity • Each time we create a link between the hospital and the instance of the operation theatre we would have to insert all the hospital details again (creating Redundant data in the dB)

10. First Instance in the dB • Enter Details for the hospital • Hospital_ID: 1 • Name: Oakfield Hospital • Location: Brookfield, Cork City • Etc etc • Enter a new operating theatre • Op Theatre_ID: 11 • Name: The head operating room • Link the two entities • Hospital_ID: 1 • Name: Oakfield Hospital • Location: Brookfield, Cork City • Op Theatre_ID: 11

11. Second Instance in the dB • Enter a new operating theatre • Op Theatre_ID: 12 • Name: The leg operating room • Link the two entities • Hospital_ID: 1 • Name: Oakfield Hospital • Location: Brookfield, Cork City • Op Theatre_ID: 12

12. How this would look in the Hospital dB entity • Each entity will be a table in the dB Hospital ID: 1 Name: Oakfiel Hospital Location: Brookfield, Cork City Op Theatre_ID: 11 Name: the head operating room ------------------------------ Hospital ID: 1 Name: Oakfiel Hospital Location: Brookfield, Cork City Op Theatre_ID: 12 Name: the leg operating room A lot of duplicate details must be inserted repeatedly each time a operating theatre instance is created

13. The right way to do it • Store the Hospital_ID in each instance of the Operation Theatre Entity • Each time we create a link between the hospital and the instance of the operation theatre we would now only have to insert the hospital_ID into each operation theatre entity instance!

14. Hospital Details once in the dB • Enter Details for the hospital • Hospital_ID: 1 • Name: Oakfield Hospital • Location: Brookfield, Cork City • Etc etc

15. Add Operating Theatres • Enter a new operating theatre • Op Theatre_ID: 11 • Name: The leg operating room • Hospital_ID: 1 • Enter another operating theatre • Op Theatre_ID: 12 • Name: The face operating room • Hospital_ID: 1

16. How this would look in the Op Theatre Entity in dB • Op Theatre_ID: 11 • Name: The leg operating room • Hospital_ID: 1 ------------------------------ • Op Theatre_ID: 12 • Name: The face operating room • Hospital_ID: 1 Reduced all the duplicate data as per the other option

17. Student Project Showcase Registered students can use the UCC student showcase to show their projects in any of their course modules. Group projects are also catered for by the student project showcase. Identify the entities for an ERD Identify the primary key of each entity Make any assumptions Identify the relationship between entities