1 / 27

08 - ERD Modelling

And Franchise Colleges. HSQ - DATABASES & SQL. 08 - ERD Modelling. By MANSHA NAWAZ. Entity Relationship Modelling Introduction. In this lecture we will try out a practical example of Entity-Relationship data modelling.

aren
Download Presentation

08 - ERD Modelling

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. And Franchise Colleges HSQ - DATABASES & SQL 08 - ERD Modelling • By MANSHA NAWAZ ERD Modelling

  2. Entity Relationship Modelling Introduction • In this lecture we will try out a practical example of Entity-Relationship data modelling. • Data Modelling is a very creative process - logic, intuition and imagination are all needed. • At various stages you will need to attempt problems yourself so you will need a pen and paper. • E-R data modelling is the subject of more than half the examination or ica • Modelling from datastore and a case study scenario in this lecture. ERD Modelling

  3. ERD Examples Example 3 ERD from a CASE STUDY FILM CLUB Example 2 ERD from DATASTORE FLIGHTS Example 1 ERD from DATASTORE PO – PURCHASE ORDERS ERD Modelling

  4. Example 1 ERD from DATASTORE PO – PURCHASE ORDERS ERD Modelling

  5. ER MODEL : WORKED EXAMPLE • MODELLING FROM DATASTORES ORDERS • 1. Identify all entities in the above form. • ORDERS • SUPPLIERS • PARTS • PAYMENT-TERMS (PT) * tabular list of payment terms • CONTACT * tabular list of contact staff • 2. Allocate main attributes identified. • ORDERS(po#, odate) • SUPPLIERS(s#, sname, saddress, …….) • PARTS(p#, pdesc, …..) • PT(pt#, ptdesc) • CONTACT(c#, cname, cext) ERD Modelling

  6. Derive Entity Relationships • 1 ORDER must have 1 SUPPLIER • 1 SUPPLIER may have 0,1,M ORDER • 1 ORDER must have 1,M PARTS • 1 PART may be on have 0,1,M ORDER • 1 ORDER must have 1 PT • 1 PT may be on have 0,1,M ORDER • 1 ORDER must have 1 CONTACT • 1 CONTACT may be on have 0,1,M ORDER • 4 Diagram (ERD) for the above showing entities of interest with their associated attributes, relationships and dependency. ERD Modelling

  7. ERD Modelling

  8. 5. List a set of skeleton tables derived from your model • ORDERS(po#, odate) • SUPPLIERS(s#, sname, saddress, …….) • PARTS(p#, pdesc, …..) • PAYMENT-TERMS(pt#, ptdesc) • CONTACT(c#, cname, cext) • supplies(s#, po#) • contains(p#, QTY, po#) • terms(po#, pt#) • postaff(po#, c#) ERD Modelling

  9. 6. Populate your skeleton tables using the data provided in the above order form. ERD Modelling

  10. The previous data set is only a partial view. • Additional orders can be generated and added to the database. • The next slide shows how the sample order (figure A-2) affects our database. • Note : No REDUNDANT or DUPLICATED data in tables which show strong data INTEGRITY. ERD Modelling

  11. ERD Modelling

  12. PAYMENT-TERMS • (pt#, ptdesc) • 1 COD • 2 7 days • 3 30 days • 4 45 days • CONTACT • (c#, cname, cext) • 01 BLOGGS FRED 321 • 02 SMITH HARRY 322 • supplies • (s#, po#) • 000001 002594 • 000002 002595 • postaff • (po#, c#) • 01 • 002595 02 contains (p#, QTY, po#) CPU012 2 002594 PRI6214 3 002594 MON023 1 002594 CON061 4 002594 CPU015 1 002594 KEY031 10 002594 CPU072 2 002594 CPU012 10 002595 PRI6214 5 002595 MON023 4 002595 CON062 4 002595 CPU016 1 002595 CPU074 2 002595 terms (po#, pt#) 002594 1 002595 3 ERD Modelling

  13. Airlines hold information about flights. Data is held as follows : Flight Aircraft Make Seats Airport City A-time D-time BA069 747 BA 402 LHR London - 1300 BA069 747 BA 402 ZRH Zurich 1430 1530 BA069 747 BA 402 BAH Bahrain 2300 0015 BA069 747 BA 402 SEZ Seychelles 0545 0645 BA069 747 BA 402 MRU Mauritius 0910 - SK586 DC8 DC 123 LIS Lisbon - 1500 SK586 DC8 DC 123 ZRH Zurich 1815 1855 SK586 DC8 DC 123 CPH Copenhagen 2110 2145 SK586 DC8 DC 123 ARN Stockholm 2255 - SK783 DC8 DC 123 CPH Copenhagen - 0940 SK783 DC8 DC 123 ATH Athens 1400 1500 SK783 DC8 DC 123 DAM Damascus 1700 - SK961 DC10 DC 230 CPH Copenhagen - 1810 SK961 DC10 DC 230 ATH Athens 2030 0030 SK961 DC10 DC 230 JNB Johannesburg 0935 - Example 2 ERD from DATASTORE FLIGHTS ERD Modelling

  14. ER MODEL : WORKED EXAMPLESOLUTION 1 : ERD FROM DATASTORES FLIGHTS ERD Modelling

  15. carries Aircraft Flight Flight (flight#, arrival_airport, depart_airport, arrival_time, depart_time) Airport (code, city) Aircraft (aircraft, no_of_seats) Identifier of flight seems strange. ‘Flight_no’ alone should identify a flight. uses Airport SOLUTION 2 : ERD FROM DATASTORES FLIGHTS ERD Modelling

  16. SOLUTION 3 : ERD FROM DATASTORES FLIGHTS Aircraft Flight (flight#, arrival_airport, depart_airport, arrival_time, depart_time) Stopover (flight_no, code, arrival_time, depart_time) Airport (code, city) Aircraft (aircraft, no_of_seats) carries Stopover Flight Departs_from Leaves_from Stops_at Arrives_at Airport ERD Modelling

  17. Example 3 ERD from a CASE STUDY Film Club Case Study: Film Club UK is a company that owns or leases a number of small cinemas in the UK. They have commissioned a database designer to design a database solution to enable them to maintain details about their cinemas and the films that they show. Note that it is possible to have two cinemas in the same location with the same name (there used to be two Odeons in Newcastle). It is also possible to have different films with the same title (for example, different versions of a Shakespeare play). Films are scheduled for one or more showings at a cinema within a ‘season’. Season details are to be notified in advance of the dates and times of showings, takings, etc. to be notified later. Any one film may have more than one season at any one cinema (for example, a cinema showing ‘The Snowman’ each Christmas). At present, all cinemas are single-screen. ERD Modelling

  18. Queries: • Details of number of employees (if any – some cinemas are small and manned by volunteers) at a cinema • Analyse takings and numbers of showings for films with times and dates of showings • List cinemas by seating capacity • List films shown anywhere since a certain date • List and summarise films by classification. ERD Modelling

  19. m m Shows Cinema Film Film Club UK is a company that owns or leases a number of small cinemas in the UK. They have commissioned a database designer to design a database solution to enable them to maintain details about their cinemas and the films that they show. Entities (and initial thoughts on relationships) • What does this ERD say about Cinemas and Films? • Write down the enterprise rules shown on the diagram (0,1,m etc.) • This looks sensible but we need to know more about cinemas and films. • Just because it looks good does not mean it is correct. • We have not looked at dealing with m:m relationships yet - very complicated. • A good start - but what next? • Find attributes and identifier for these entities. ERD Modelling

  20. m m Shows Cinema Film Note that it is possible to have two cinemas in the same location with the same name (there used to be two Odeons in Leicester Square). It is also possible to have different films with the same title (for example, different versions of a Shakespeare play). Cinema: Identifier: Cinema_id Attributes: Cinema_name, location, address, …. Film: Identifier: Film_id Attributes: title, duration, category, …. • Do these make good sense? • Could we have missed things at this stage? • Clearly an iterative process. ERD Modelling

  21. m m Shows Cinema Film Showing Season Films are scheduled for one or more showings at a cinema within a ‘season’. Season details are to be notified in advance of the dates and times of showings, takings, etc. to be notified later. More entities ... and Showing: Attributes: date, time, takings? • Try to redraw the E-R diagram including these 2 entities. • A useful trick: Where is the ‘money’? • Working backwards from entities that are key business things (money?) can work well. ERD Modelling

  22. 1 1 m m of at Cinema Film Season • Write out the enterprise rules for this version. • Notice the direction of the relationship names. • The direction is away from the ‘business entity. • You can get a lot out of ‘Where is the money?’ and ‘Where is the business?’ We can add showing later ... A cinemahas 0,1,m season(s) A seasonis at exactly 1 cinema A filmis shown in 0,1,m season(s) A seasonof exactly 1 film • So what about the showing entity? • Try to add the showing entity to your ERD. ERD Modelling

  23. 1 1 m m of at 1 Film Cinema in m Showing Season • Write out the enterprise rules for new relationship. A seasoninvolves 0,1,m showing(s) A showingis in exactly 1 season • Why not relate showing to film? It is a showing of a film … • All the showings in a season are the same film. • Could you have a season that does not have any showings? (yet!) ERD Modelling

  24. 1 1 m m of at 1 Cinema Film in m Showing Season Any one film may have more than one season at any one cinema (for example, a cinema showing ‘The Snowman’ each Christmas). At present, all cinemas are single-screen. • Have we coped with this already? • Will the model cope with multi-screen cinemas? • We would need a screen entity - try this for yourself later. ERD Modelling

  25. Showing Season Identifiers and • Season: Identifier: season_idorfilm, cinema, start_date ?? • Attributes: start_date, end_date, total_takings • Showing: Identifier: season_id, date, time or showing_id • Attributes: takings, adult_tickets, child_tickets, • concession_tickets, free_passes, …… • Is total_takings redundant? • This is a derived attribute - explained later. ERD Modelling

  26. ERD SAMPLES ERD Modelling

  27. End of Lecture ERD Modelling

More Related