And Franchise Colleges HSQ DATABASES SQL 08 ERD
And Franchise Colleges HSQ - DATABASES & SQL 08 - ERD Modelling By MANSHA NAWAZ Section 08 ERD Modelling 1
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. Section 08 ERD Modelling 2
ERD Examples Example 1 Example 2 Example 3 ERD from DATASTORE ERD from a CASE STUDY PO – PURCHASE ORDERS FLIGHTS FILM CLUB Section 08 ERD Modelling 3
Example 1 ERD from DATASTORE PO – PURCHASE ORDERS Section 08 ERD Modelling 4
• ER MODEL : WORKED EXAMPLE • MODELLING FROM DATASTORES ORDERS 1. Identify all entities in the above form. ORDERS SUPPLIERS PARTS PAYMENT-TERMS (PT) CONTACT * tabular list of payment terms * 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) Section 08 ERD Modelling 5
3. Derive Entity Relationships 1 ORDER must have 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 may be on have 0, 1, M ORDER 1 ORDER must have 1 CONTACT may be on have 0, 1, M ORDER 4 Section 08 Diagram (ERD) for the above showing entities of interest with their associated attributes, relationships and dependency. ERD Modelling 6
Section 08 ERD Modelling 7
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#) Section 08 ERD Modelling 8
6. Populate your skeleton tables using the data provided in the above order form. Section 08 ERD Modelling 9
• 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. Section 08 ERD Modelling 10
Section 08 ERD Modelling 11
PAYMENT-TERMS (pt#, ptdesc) 1 COD 2 7 days 3 30 days 4 45 days CONTACT (c#, cname, 01 BLOGGS FRED 321 02 SMITH HARRY 322 supplies (s#, po#) 000001 002594 000002 002595 postaff (po#, • 01 • 002595 Section 08 cext) contains (p#, QTY, CPU 012 2 PRI 6214 3 MON 023 1 CON 061 4 CPU 015 1 KEY 031 10 CPU 072 2 CPU 012 10 PRI 6214 5 MON 023 4 CON 062 4 CPU 016 1 CPU 074 2 terms (po#, 002594 002595 c#) 02 ERD Modelling po#) 002594 002594 002595 002595 pt#) 1 3 12
Airlines hold information about flights. Data is held as follows : Flight Aircraft Make Seats Airport City A-time BA 069 747 BA 402 LHR London 402 ZRH Zurich 1430 ERD from DATASTORE BA 069 747 BA 402 BAH Bahrain 2300 BA 069 747 BA 402 SEZ Seychelles 0545 FLIGHTS BA 069 747 BA 402 MRU Mauritius 0910 SK 586 DC 8 DC 123 LIS Lisbon SK 586 DC 8 DC 123 ZRH Zurich 1815 SK 586 DC 8 DC 123 CPH Copenhagen 2110 SK 586 DC 8 DC 123 ARN Stockholm 2255 SK 783 DC 8 DC 123 CPH Copenhagen SK 783 DC 8 DC 123 ATH Athens 1400 SK 783 DC 8 DC 123 DAM Damascus 1700 SK 961 DC 10 DC 230 CPH Copenhagen SK 961 DC 10 DC 230 ATH Athens 2030 SK 961 DC 10 DC 230 JNB Johannesburg 0935 Example 2 Section 08 ERD Modelling D-time 1300 1530 0015 0645 1500 1855 2145 0940 1500 1810 0030 - 13
ER MODEL : WORKED EXAMPLE SOLUTION 1 : ERD FROM DATASTORES FLIGHTS Section 08 ERD Modelling 14
SOLUTION 2 : ERD FROM DATASTORES FLIGHTS carries Aircraft Flight (flight#, arrival_airport, depart_airport, arrival_time, depart_time) uses Airport (code, city) Aircraft (aircraft, no_of_seats) Identifier of flight seems strange. ‘Flight_no’ alone should identify a flight. Airport Section 08 ERD Modelling 15
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) carries Airport (code, city) Aircraft (aircraft, no_of_seats) Stopover Flight Departs_from Stops_at Section 08 Leaves_from ERD Modelling Arrives_at Airport 16
Example 3 Film Club Case Study: Film Club UK is a company that owns or leases a number of ERD from a CASE STUDY 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. Section 08 ERD Modelling 17
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. Section 08 ERD Modelling 18
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) Cinema • m Shows m Film 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. Section 08 ERD Modelling 19
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: m Shows m Film 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. Section 08 ERD Modelling 20
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. Cinema m Shows m Film More entities. . . Showing and Season 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. Section 08 ERD Modelling 21
• • 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? ’ Cinema 1 m at Season m of 1 Film We can add showing later. . . A cinema has 0, 1, m season(s) A season is at exactly 1 cinema A film is shown in 0, 1, m season(s) A season of exactly 1 film • So what about the showing entity? • Try to add the showing entity to your ERD. Section 08 ERD Modelling 22
• Write out the enterprise rules for new relationship. Cinema 1 m at Season 1 in 1 m of Film m Showing A season involves 0, 1, m showing(s) A showing is 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!) Section 08 ERD Modelling 23
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. Cinema 1 at m Season m of 1 Film 1 in m • Showing 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. Section 08 ERD Modelling 24
Identifiers Showing and Season: Identifier: season_id or film, 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. Section 08 ERD Modelling 25
ERD SAMPLES Ascent Resources Ascent S/W and ERD Solutions Installing Ascent At Home Using Ascent - ER Modeling Library of Free Data Models Section 08 ERD Modelling 26
End of Lecture Section 08 ERD Modelling 27
- Slides: 27