HSQ - DATABASES & SQL

1. And Franchise Colleges HSQ - DATABASES & SQL 10 ER Problems and Styles • By MANSHA NAWAZ 10 ER Problems and Styles

2. m Is composed of Component 1 Introduction: Recursive Relationships • Entities may have relationships with themselves: • The Enterprise Rule: • A component may be composed of 0,1,m other components • A component may be part of 0,1 components • This is called a Recursive relationship. 10 ER Problems and Styles

3. 1 Is the twin of Twin 1 1 • Can you think of another example? ?? ?? 1 1:1 Recursive Relationships • Enterprise rule: • A Twin has exactly one Twin. • The enterprise is the same in both directions. • Very difficult - all other examples turn out to be twins of some kind. 10 ER Problems and Styles

4. 1 1 Is the twin of Precedes / succeeds Child Operation 1 1 1:1 Recursive Relationships - continued • Another useful example • Enterprise rule: • A Child has 0,1 Twins. • The enterprise rule is the same in both directions. • Enterprise rule: An Operation has 0,1 Preceding Operations. An Operation has 0,1 Succeeding Operations. • A linked list structure ... • These three examples are particularly useful as most 1:1 recursives are an example of one of these. 10 ER Problems and Styles

5. m Manages Employee 1 Manager 1:m Recursive Relationships • Can you see a problem with this? The boss has to manage themselves! • Notice one ‘leg’ of the relationship has a role name. • This is very effective in making recursive relationships understandable. • Enterprise rule: A employee manages 0,1,m employees A employee is managed by exactly 1 employee 10 ER Problems and Styles

6. Minor-component m Is part of Component 1 Major-component 1:m Recursive Relationships continued ... • Enterprise rule: A Major-component is composed of 0,1,m Minor-components A Minor-component is part of 0,1 Major-components • Here we can see the advantage of roles names. • Many end with non-obligatory membership class. 10 ER Problems and Styles

7. Minor-component m Is part of Component m Major-component Minor-component is Complex entity m 1 Sub- Component Component m 1 in Major-component m:m Recursive Relationships • What do we need to do with m:m relationships? • Enterprise rule: A Major-component is composed of 0,1,m Minor-components A Minor-component is part of 0,1,m Major-components • Decomposing ... 10 ER Problems and Styles

8. Minor-component is m 1 Sub- Component Component m 1 in Major-component m:m Recursive Relationships continued ... • Remember that when you have a complex entity you need to determine: • Name , Attributes, Primary Key (Identifier), Description • This may be much more difficult for a recursive complex entity. • However, the is composed of model above is quite useful generally. • Some possible versions of the above as table types. Sub-component(minor.component#, major.component#, quantity required, ..) or ... Sub-component(minor.component#, major.component#, assembly-sequence, ..) Could the second one be used to represent written documents?? 10 ER Problems and Styles

9. 1 1 m m of at 1 Cinema Film in m Showing Season Connection Traps • So far modelling looks very attractive. • A powerful tool based around a simple diagramming technique. • However, there are some pitfalls for database designers. • Generally these pitfalls are due to wrongly interpreting the meaning of relationships. • Poor design of complex entities usually results in significant connection trap problems. • Careful design of complex entities, together with an approach of working outwards from complex (transactional) entities helps to eliminate connection trap problems.(Work back from the money! - see below) 10 ER Problems and Styles

10. Fan Trap Fan Trap 1 1 m m of at 1 1 Cinema Film in m Showing m Season of Fan Traps • In this version of the previous model can we: • Determine which Cinema and Film relates to a particular Showing? • Can we: • Determine all the Showings in a particular Season? • Determine the Season for a particular Showing? • We can: • Find the appropriate Cinema and Film for any Showing or Season. • We can only relate a Showing to all the Seasons in a particular Cinema. • We can only relate a Showing to all the Seasons for a particular Film. • These are called Fan Traps. 10 ER Problems and Styles

11. 1 1 m m of at 1 Cinema Film in m Showing Season Avoiding Fan Traps • Does this version of the ERD avoid the previous problems? • So significant fan traps can be avoided by re-designing the relationships. • Again: Careful design of complex entities, together with an approach of working outwards from complex (transactional) entities helps to eliminate connection trap problems.(Work back from the money!) 10 ER Problems and Styles

12. Non-Significant Fan Trap 1 1 m m of at 1 1 in Film Cinema in m m Showing Actor Star Season m is 1 Non-Significant Fan Traps • Why don’t we care about the Fan Trap shown? • Stars are related to all Seasons, Showings and Cinema where their Films are shown. • All Seasons and Showings are related to all the Stars in the Film shown. • It is not a trap at all. • These patterns often occur at the edges of ERDs. 10 ER Problems and Styles

13. m 1 1 m in in Regiment Soldier Soldier S4 S11 S3 S19 S9 S2 S6 Platoon P1 P2 P3 Regiment R1 R2 Platoon Chasm Traps • This looks reasonable but has some hidden problems. • We would assume we can always find the regiment a soldier is in. • S2 is the Regimental Sergeant Major (not in a platoon) - which regiment is he in? • OOPS - you can tell him the system doesn’t know what regiment he is in…... 10 ER Problems and Styles

14. Regiment m 1 1 m in in m 1 in Soldier Platoon Chasm Traps - Removing • Usually such problems can be solved by re-designing the relationships or adding new relationships. • The above solution, adding a new relationship, resolves this problem. • In another scenario a similar problem may be solved by just re-designing (swapping around) the relationships. 10 ER Problems and Styles

15. ERD STYLES • All data model can be conveyed by Entity Relationship Diagram (ERD) • Commonly used structure is the NETWORK style ERD. • Good ERDs should also try and utilises the following styles. • This section covers popular style 10 ER Problems and Styles

16. HIERARCHY STYLE ERD LIVING CREATURE c#, cdesc is a c# PERSON female male type type c# c# MALE FEMALE 10 ER Problems and Styles

17. SET TYPE Consider the entity employee and the following data set : EMPLOYER eno ename esalary etype e1 Smith 12k lecturer e2 Jones 12k lectuere e3 Brown 7k dean e4 Green 10k lecturer EMPLOYEE We can ALSO map the above data into a set type ERD as follows : EMPLOYEE EMPLOYEE emp_type TYPE EMPLOYER emp_type EMPLOYEE_TYPE eno ename esalary eno etypeno etypeno etype e1 Smith 12k e1 et1 et1 lecturer e2 Jones 12k e2 et1 et2 dean e3 Brown 7k e3 et2 e4 Green 10k e4 et1 10 ER Problems and Styles

18. SET OPERATION S#, SNAME, SADDRESS, STEL S#, SNAME, SADDRESS, STEL STAFF STAFF sex position S# S# S# S# ENGINEERING BUSINESS STAFF MALE FEMALE STAFF UNION OBTAINED BY JOINING ALL DATA FROM SET TYPE TABLES. ie jjoin keys from MALE and FEMALE tables join keys from ENGINEERING and BUSINESS tables UNION = SET(A)+SET(B) INTERSECTION OBTAINED BY SELECTING DATA WHICH ONLY EXISTS IN ALL SET TYPE TABLES ie select all records which exist in both MALE and FEMALE tables select all records which exist in both ENGINEERING and BUSINESS tables INTERSECTION = SET(A)-SET(B) WE ARE THEREFORE ABLE TO USE SET THEORY TO OBTAIN SELECTIVE LISTS. 10 ER Problems and Styles

19. SUB SET P#, PNAME, PADDRESS, PTEL (decomposition) PROFESSORS by rank ASSOCIATE ASSISTANT FULL PROFESSORS PROFESSORS PROFESSORS P# P# P# 10 ER Problems and Styles

20. ALTERNATIVE P#, PNAME, PADDRESS, PTEL SET MODEL PROFESSORS full associate assistant FULL PROFESSORS ASSOCIATE ASSISTANT PROFESSORS PROFESSORS P# P# P# 10 ER Problems and Styles

21. SUPERTYPES and SUB TYPES A company operate cars, lorries and buses; furthermore these are the only type vehicles it operates. Suppose we are developing a data model for this company. One possible solution would be to use SET TYPE (92) and to have tables VEHICLE and VEHICLE TYPE to indicate CAR, LORRY or BUS. VEHICLE VEHICLE veh_type TYPE VEHICLE veh_type VEHICLE_TYPE vno reg vno vtypeno vtypeno vtype V1 Smith V1 B B BUS V2 Jones V2 B C CAR V3 Brown V3 C L LORRY V4 Green V4 L 10 ER Problems and Styles

22. However each vehicle type in reality requires different attributes. ie A bus would require passenger capacity A lorry would require tonnage and no’ of axles Furthermore each of the vehicle types may require different relationship participation. ie BUS services a ROUTE EMPLOYEE keeps a CAR EMPLOYEES can drive VEHICLES A SOLUTION WOULD BE TO USE THE ALTERNATIVE SET MODEL (95) THE MODEL BELOW IS WITHOUT THE DIAMOND NOTATION IT MAKES USE OF SUPERTYPES & SUB TYPES 10 ER Problems and Styles

23. ER MODEL - VEHICLES VEHICLE BUS CAR LORRY can drive keeps is served by EMPLOYEE ROUTE NOTE : THIS STYLE IS NOT MODELLED BY ASCENT REDRAW THE ABOVE ER DIAGRAM USING THE DIAMOND NOTATION. 10 ER Problems and Styles

24. 10 ER Problems and Styles

25. EXISTENCE PRECEDENCE DEPENDENCE RELATIONSHIP O#, OTOTAL, ODATE E#, ENAME, EADDRESS, ETEL EMPLOYEE ORDER O#, D# order-despatch emp - nok E#, NOK# NEXT OF KIN DESPATCH D#, DDATE NOK#, NOKNAME, NOKADDRESS, NOKTEL 10 ER Problems and Styles

26. WEAK ENTITY TYPE (OPTIONAL entities shown in double box) E#, ENAME, EADDRESS, ETEL EMPLOYEE E#, D# ID DEPENDENT D#, DNAME, DADDRESS, DTEL NOTE : THIS STYLE IS NOT MODELLED BY ASCENT 10 ER Problems and Styles

27. HIGH-LEVEL ENTITY TYPE : Style 1 (relationship types defined on relationships) ‘Products are shipped to customer. Shipping is performed by a Shipping Agent.’ S#, Sdate P#, Pname, Pdesc C#, Cname, Caddress, Ctel PRODUCT CUSTOMER shipping performed by A#, S# AGENT A#, Aname, Aaddress, Atel SEE TERNARY RELATIONSHIP for improvements to above model. NOTE : THIS STYLE IS NOT MODELLED BY ASCENT 10 ER Problems and Styles

28. HIGH-LEVEL ENTITY TYPE : Style 2 (relationship types defined on relationships) S#, Sdate S#, A# P#, C# P#, Pname, Pdesc C#, Cname, Caddress, Ctel shipping PRODUCT CUSTOMER performed by AGENT A#, Aname, Aaddress, Atel SEE TERNARY RELATIONSHIP for improvements to above model. NOTE : THIS STYLE IS NOT MODELLED BY ASCENT 10 ER Problems and Styles

29. TERNARY RELATIONSHIP TYPE (multi relationship types) P# C#, A# P#, Pname, Pdesc C#, Cname, Caddress, Ctel shipping PRODUCT CUSTOMER AGENT A#, Aname, Aaddress, Atel 10 ER Problems and Styles

30. QUAD RELATIONSHIP TYPE S#, Sdate P#, Pname, Pdesc C#, Cname, Caddress, Ctel S#, P#, C#, A# A#, Aname, Aaddress, Atel NOTE : THIS STYLE IS NOT MODELLED BY ASCENT 10 ER Problems and Styles

31. RECURSIVE RELATIONSHIP TYPE (mapping onto one entity types) E#, ENAME, EADDRESS, ETEL EMPLOYEE plays TEAM manages managerE#, workerE# SEE BINARY relationship to model this in ASCENT.. NOTE : THIS STYLE IS NOT MODELLED BY ASCENT 10 ER Problems and Styles

32. BINARY RELATIONSHIPS (Used to improve recursive relationships.) E#, ENAME, EADDRESS, ETEL EMPLOYEE manager worker E# E# supervise MANAGER manager E# 10 ER Problems and Styles

33. BINARY RELATIONSHIPS using SUPER & SUB TYPES – Employee Example (Used to improve recursive relationships.) supervises EMPLOYEE MANAGER WORKER contracts NOTE : THIS STYLE IS NOT MODELLED BY ASCENT 10 ER Problems and Styles REDRAW THE ABOVE ER DIAGRAM USING THE DIAMOND NOTATION.

34. 10 ER Problems and Styles

35. TWO OR MORE RELATIONSHIPS BETWEEN ENTITIES I#, Iname, Idesc ITEM sold to returned by I#, C#, Qty, date I#, C#, Qty, date CUSTOMER C#, Cname, Caddress, Ctel. 10 ER Problems and Styles

36. ERD SAMPLES 10 ER Problems and Styles

37. End of Lecture 10 ER Problems and Styles