1 / 18

44220: Database Design & Implementation ER Diagramming

44220: Database Design & Implementation ER Diagramming. Ian Perry Room: C41C Tel Ext.: 7287 E-mail: I.P.Perry@hull.ac.uk http://itsy.co.uk/ac/0809/sem2/44220_DDI/. Conceptual Data Modelling Process. Identify ALL of the relevant Entities .

Thomas
Télécharger la présentation

44220: Database Design & Implementation ER Diagramming

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 44220: Database Design & ImplementationER Diagramming Ian Perry Room: C41C Tel Ext.: 7287 E-mail: I.P.Perry@hull.ac.uk http://itsy.co.uk/ac/0809/sem2/44220_DDI/

  2. Conceptual Data Modelling Process • Identify ALL of the relevant Entities. • must play a necessary role in the business system. • Identify those Attributes that adequately describe each Entity. • remember to choose ‘key’ attribute(s). • Identify the Relationships between Entities. • determine the Degree of each Relationship: • determine the Type of each Relationship. • attempt to decompose any many-to-many Relationships that you have identified.

  3. Hospital Example • Let us suppose that you have been asked to build a Conceptual Data Model for a Hospital. • The questions you must ask (and answer?) are as follows: • What are the major objects of interest? • Entities • What details adequately describe each of these objects? • Attributes • What associations are valid between these objects? • Relationships

  4. What are the Entities? • i.e. the major objects of interest. • Doctor • Nurse • Ward • Patient • Medication • Operation • Clinic • etc.

  5. What are the Attributes? • i.e. the details that adequately describe each of the Entities. • Ward • Name, Location, Number of Beds, … • Patient • Name, Address, Date-of-Birth, Gender, … • Doctor • Name, Room, Extension, Speciality, … • Nurse • Name, Room, Extension, Speciality, … • NB. • Perhaps ‘people’ who work in the Hospital could be described using ONE Entity, i.e. Staff?

  6. Entity Definition • Syntax: • EntityName (key attribute(s), attribute, ...) • For Example: • Staff (FirstName, FamilyName, Role, Room, Extension, Speciality, …) • Ward (WardName, Location, Number-of-Beds, …) • Medication (DrugCode, Type, Dosage, …) • Patient (FirstName, FamilyName, DOB, Gender, Street, Town, Post Code, Tel. No., …) • Always Remember: • to indicate the ‘key’ Attribute(s).

  7. ‘Type’ of Relationships? • i.e. associations which are valid, in BOTH directions, between pairs of Entities. • PatienthasOperation. • Operationperformed onPatient • Staffwork in Ward • Ward employs Staff • Ward has beds for Patient • Patientstays inWard • PatientattendsClinic • ClinictreatsPatient • Etc.

  8. ‘Degree’ of Relationships? • One-to-One (1:1) • Very rare! • One-to-Many (1:M) • Quite rare as a ‘real world’ relationship; however, what we require for both Logical and Physical Modelling. • Many-to-Many (M:M) • Most common as a ‘real world’ relationship; however, impossible to implement Logically or Physically. • MUST be decomposed into two One-to-Many relationships linked by an ‘artificial’ Entity.

  9. Occurrence Diagrams? 1 M M 1 1 M • Use these (with values for Key Attributes) to discover how many occurrences of each Entity are actually on either side of a Relationship. • Staff • Fred Smith • Jane Bloggs • Arthur Jones • Angela Oust Ward Ward 1 Ward 2 Ward 3

  10. Participation in a Relationship? • Mandatory: • compulsory attachment on both sides (very rare). • Contingent: • compulsion on one side only (most common). • Optional: • no compulsory attachment for either Entity (rare). • Inclusive: • attachment only occurs when another relationship exists (very rare). • Exclusive: • only one attachment from a set of possible relationships is permitted (quite rare).

  11. Degree, Type & Participation - 1 • One-to-Many, Contingent (compulsion one side) has beds for => 1 M Ward Patient <= stays in • One-to-Many, Contingent (compulsion one side) treats => 1 M Clinic Patient <= attends NB. a Patient can NOT both be staying in a Ward AND attending the Clinic, so these two Relationships are also Exclusive.

  12. Degree, Type & Participation - 2 • Many-to-Many, Contingent (compulsion one side) has => M M Patient Operation <= performed on • Many-to-Many, Contingent (compulsion one side) work in => M M Staff Ward <= employs

  13. Decompose Complex Relationships • Can’t have any Many-to-Many Relationships; for example this one: work in => M M Staff Ward <= employs • MUST be decomposed into 2 x One-to-Many Relationships, linked by an ‘artificial’ Entity, i.e.: M M 1 1 Ward Team Staff work in => <= employs FirstName FamilyName WardName FirstName FamilyName WardName

  14. Rules for Drawing ER Diagrams • Need to look good: • So, DON’T draw them by hand! • Need to be well laid out, so that: • Entities with several Relationships are in the centre of the diagram. • AND related Entities are adjacent to each other. • AND Relationship lines do not cross.

  15. The final Degree, Type & Participation Diags. M 1 1 M Team Ward Staff <= employs work in => treats => 1 M Clinic Patient 1 M M 1 Pat/Op Operation Patient <= attends has => <= performed on has beds for => 1 M Ward Patient <= stays in NB. the above Relationships are also Exclusive. i.e. having now ‘solved’ BOTHM:M Relationship ‘problems’.

  16. Plan BEFORE you begin! • ‘Patient’ has Relationships with THREE other Entities: • so, place ‘Patient’ in centre of the Diagram. • ‘Ward’, ‘Team’ & ‘Pat/Op’ have Relationships with TWO other Entities: • so, place these a little further out. • The other 3 Entities (i.e. ‘Staff’, ‘Operation’ & ‘Clinic’ only have ONE Relationship: • so, they should be placed on the outside of the digaram, close to the Entity that they have a Relationship with. • Join all of the Entities: • indicating the Degree, Type and Participation of each Relationship.

  17. ER Diagram for a Hospital has => Ward Clinic Team 1 M M 1 1 <= work in <= attends has beds for => <= stays in treats => 1 Staff #==========# M M Pat/Op Patient has => M 1 M <= performed on 1 Operation

  18. This Week’s Workshop • The purpose of this week’s Workshop it to check that you can create good looking ER Diagrams, using a suitable software tool. • If you haven’t already got a favourite drawing application I suggest you use PowerPoint. • Your ER Diagrams MUST follow the diagram conventions given in this lecture. • The same is true for Assignment 1. • Part 1 - Draw Degree, Type & Participation diagrams, which: • depict 4, relatively simple, ‘real-world’ situations. • Part 2 - Begin working on Assignment 1, by: • identifying the Entities from the Case Study. • then, drawing Occurrence diagrams for each Pair of Entities.

More Related