HSQ - DATABASES & SQL

1. And Franchise Colleges HSQ - DATABASES & SQL 09 ER Decomposition of Many-to-Many ER Decomposition Of Many To Many

2. Decomposition of a Many to Many Relationship • A MANY to MANY relationship can be decomposed into two ONE to MANY relationship. • Decomposition of many to many relationships is a standard E-R technique. • All many to many relationship types must be decomposed to form a complex entity as above. • Mapping many to many relationship types back to reality often is very difficult .... ER Decomposition Of Many To Many

3. Example : Many PARTS supplied by Many SUPPLIERS p# pdesc cost rrp s# sname saddress p1 4mb simm 10 20 s1 CMM Newcastle p1 4mb simm 10 20 s4 INGRAM Redcar p2 850mb HD 175 249 s1 CMM Newcastle p3 1gb HD 199 278 s2 ABC Darlington p3 1gb HD 199 278 s5 Comp. R. Us Darlington p4 20” Mtr 699 899 s1 CMM Newcastle p4 20” Mtr 699 899 s2 ABC Darlington p4 20” Mtr 699 899 s5 Comp. R. Us Darlington ENTITIES : PARTS, SUPPLIERS RELATIONSHIP : many parts supplied by many suppliers. ER MODEL : supplied PARTS SUPPLIERS by ER Decomposition Of Many To Many

4. P# p1 p2 p3 p4 S# s1 s2 s3 s4 s5 OCCURRENCE DIAGRAM : The relationship between PARTS and SUPPLIES cannot be directly mapped into tables because of the variable number of links. Parts->Supplier Link Supplier->Parts Link p1 s1, s4 s1 p1, p2, p4 p2 s1 s2 p3, p4 p3 s2, s5 s3 p4 s1, s2, s5 s4 p1 s5 p3, p4 ER Decomposition Of Many To Many

5. supplied PARTS SUPPLIERS by BECOMES PART SUPPLIER * PART-SUPP * part-supp p# s# p1 s1 p1 s4 p2 s1 p3 s2 p3 s5 p4 s1 p4 s2 p4 s5 SUPPLIER s# sname saddress s1 CMM Newcastle s2 ABC Darlington s3 SIMMTECH Hartlepool s4 INGRAM Redcar s5 Comp. R. Us Darlington PART p# pdesc cost rrp p1 4mb simm 10 20 p2 850mb HD 175 249 p3 1gb HD 199 278 p4 20” Mtr 699 899 * THE RELATIONSHIPS BECOME REDUNDANT ALL LINKAGE IS DONE THROUGH THE NEW ENTITY PART-SUPP ER Decomposition Of Many To Many

6. m:n MANY-to-MANY Relationship staff# student# staff#, lname, room, ext. student#, sname, saddress LECTURER lectures STUDENT • Enterprise Rule : • ‘MANY LECTURERS LECTURES MANY STUDENTS • ‘MANY STUDENTS LECTURED by MANY LECTURER • Occurrence Diagram : LECTURER L1 L2 L3 L4 L5 L6 STUDENT S1 S2 S3 S4 Sn lectures ER Decomposition Of Many To Many

7. Example : Many LECTURERS lectures Many STUDENTS staff# student# student#, sname, saddress staff#, lname, room, ext. LECTURER STUDENT lectures • Enterprise Rule : • MANY LECTURERS LECTURES MANY STUDENTS • MANY STUDENTS LECTURED by MANY LECTURER • Occurrence Diagram : LECTURER L1 L2 L3 L4 L5 L6 STUDENT S1 S2 S3 S4 Sn lectures ER Decomposition Of Many To Many

8. Skeleton Tables : Simple Solution • LECTURER(staff#, lname, room, ext) • lectures(staff#, student#) • STUDENT(student#, sname, saddress) • Populated Tables • using links from occurrence diagram & sample data to show relationship) LECTURER lectures DEPARTMENT staff# lname room ext staff# student# student# sname L1 ALBERT A4 12 L1 S3 S1 bloggs L2 BLOGGS B2 18 L3 S1 S2 jones L3 COATES C1 22 L5 Sn S3 green L4 GREEN A3 11 L1 S2 S4 smith L5 JONES D1 53 L1 S4 Sn zues L6 SMITH A5 18 L1 S1 L2 S1 L3 S1 ER Decomposition Of Many To Many

9. Skeleton Tables : Foreign Key Solution (MS ACCESS) • m:n cannot be represented by foreign key import • MS ACCESS cannot map m:n relationships • Populated Tables • using links from occurrence diagram & sample data to show relationship) LECTURER STUDENT staff# lname room ext student# student# sname staff L1 ALBERT A4 12 S1, S2, S3, S4 S1 bloggs L1, L2, L3, L4 L2 BLOGGS B2 18 S1 S2 jones L1 L3 COATES C1 22 S1 S3 green L1 L4 GREEN A3 11 S1 S4 smith L1 L5 JONES D1 53 Sn Sn zues L5 L6 SMITH A5 18 • VARIABLE LENGTH RECORDS ARE GENERATED!!! • SOLUTION - DECOMPOSE M:N to 1:M and M:1 ER Decomposition Of Many To Many

10. We are able to map and model any number of relationships between entities. • Modelled below are the 3 different types of relationship covered in Section 04. • More entities can be linked to complete a data model of a system. LECTURER STUDENT MODEL staff#, lname, room, ext LECTURER staff#, student# staff#, student# staff#, student# lecturers proj-supp councels STUDENT ER Decomposition Of Many To Many student#, sname

11. FIRST CUT DATA MODEL : LECTURER STUDENT m:n not decomposed staff#, lname, room, ext LECTURER staff#, student# staff#, student# staff#, student# lecturers proj-supp councels STUDENT FIRST CUT TABLES : LECTURER STUDENT LECTURER (@staff#, lname, room, ext) STUDENT (@student, sname) lectures (@staff#, @student#) proj-spec (@staff#, @student#) councels (@staff#, @student#) student#, sname ER Decomposition Of Many To Many

12. SECOND CUT LECTURER STUDENT MODEL m:n decomposed staff#, lname, room, ext LECTURER * staff#, student# staff#, student# staff#, student# LECTURES proj-supp councels STUDENT * student#, sname TABLES : LECTURER STUDENT Simple Solution LECTURER (@staff#, lname, room, ext) STUDENT (@student, sname) LECTURES (@staff#, @student#) proj-spec (@staff#, @student#) councels (@staff#, @student#) TABLES : LECTURER STUDENT USING FOREING KEY DESIGN LECTURER (@staff#, lname, room, ext) STUDENT (@student, sname, proj-supp-staff#, councel-staff#) LECTURES (@staff#, @student#) ER Decomposition Of Many To Many

13. Example : Many CUSTOMERS hires Many CARS Customer (Customer_no, name, street, town, county, post_code) Car (reg_no, model, engine_size) Where would the data representing the date a car is hired out be held? As an attribute of Car? The identifier of Car is Reg_no. So, any reg_no can only appear once in this entity. A car may be hired out many times so the reg_no would appear many times if Car had an attribute Date_out. As an attribute of Customer? Does not make sense- the date a customer is hired out? There is an entity missing. This is a typical feature of Many-many relationships i.e. indicates incompleteness. ER Decomposition Of Many To Many

14. For_a Car Car Takes_out_a Current_ Contract Contracts_for_a Customer Customer Entities Customer (Customer_no, name, street, town, county, post_code) Car (reg_no, model, engine_size) Current_Contract (reg_no, customer_no, date_out, date_in) • Identifier of new entity is a composite identifier made up from the identifiers of the two original entities. • The new entity represents current contracts as a customer cannot hire the same car again as the entity currently stands. • This new entity is now the home for the date_in, date_out attributes. • Note how the original participation conditions are preserved. ER Decomposition Of Many To Many

15. For_a Car Takes_out_a Contract Customer Entities Customer (Customer_no, name, street, town, county, post_code) Car (reg_no, model, engine_size) Contract (reg_no, customer_no, contract_date, date_out, date_in) Adding a contract_date to the identifier of current_contract allows the same car to be hired to the same customer on a different date. ER Decomposition Of Many To Many

16. Attends A&E Dept m m Patient Patient P3 P7 P21 P5 P17 Attends A&E Dept AE4 AE29 AE3 Why does Decomposition work? • A possible Entity Occurrence Diagram ER Decomposition Of Many To Many

17. Patient P3 P7 P21 P5 P17 Attendance A3 A1 A6 A9 A7 A8 A&E Dept AE4 AE29 AE3 • Giving each Attendance a unique number: • Each instance of a relationship between Patient and A&E Dept • The Attendance Entity is revealed. A new Complex Entity. ER Decomposition Of Many To Many

18. 1 m 1 A&E Dept m by at Attendance Patient The New Complex Entity • Decomposing the original m:m relationship gives: • Notice how easy and simple it is to name the two new relationships outwards from the Complex Entity. • Also notice the pattern of the degree of association and membership class. • Is decomposition easy? So where is the difficulty??? ER Decomposition Of Many To Many

19. A Complex Entity Type Mapping Tool • Decomposition of a many to many relationship type reveals a complex entity • An entity with more than one attribute in its identifier. • It may be a simple entity type that has not been identified so far. • The new entity may be something obvious to a user but not to the data analyst or equally something unfamiliar to both. •  You may be tempted simply to assign the complex entity a code or number as identifier, however: • A complex entity always has a 'natural' composite identifier. • It is helpful to the understanding of a entity to know its ‘natural’ identifier even if we eventually use a code instead. • To use a complex entity in an ERD you need the following:   ENTITY NAME ATTRIBUTES IDENTIFIER DESCRIPTION ER Decomposition Of Many To Many

20. Example Decompose the Many to Many relationship • Appropriately name the new entity type. • Complex entity naming may be difficult. • A weak name may initially be all that is possible at first such as Employee-Project. • An attempt should be made to find a 'good' name. • Asymmetric viewing often provides useful clues • Something like "Assignment", "Task" or "Job" would hopefully be found. ER Decomposition Of Many To Many

21. Asymmetric Viewing Tool for Complex Entities(look both ways!) • This is a Simple Tool to help in the mapping of complex entities • The analyst often finds it difficult to decide what the complex entity represents in reality. • Thus, in this example they cannot define an Assignment. • To put it another way, the analyst cannot say what it is a list of. • Using this tool the analyst examines the complex entity from the viewpoint of each contributing entity in turn. • Examining the diagram from the Employee viewpoint: • Assignment is a list of the projects that an employee is involved in and the roles they have in those projects. • Examining the diagram from the Project viewpoint: • Assignment is a list of the employees are involved in a project and the roles they have in those projects. ER Decomposition Of Many To Many

22. Identifiers for Complex Entities • Select appropriate identifier for the new entity type, often a composite of attributes. • A composite of the Foreign Keys? • For this example, [emp#,project#] seems appropriate at first. •  Invent several new attributes for the new entity type. This aspect is essential. • For example; role, supervisor, hours-allocated, hours-so-far, hours-required, date-started, finish-date. • Asymmetric viewing is useful again in finding new attributes of the complex entity. • For any entity type you wish to include in a database, you should be able to invent or observe appropriate attributes. ER Decomposition Of Many To Many

23. Checking the identifier of a Complex Entity • Draw up an appropriate relational table and create an example table occurrence. • We have guessed that the identifier is [emp#, project#] • Now simply invent a few reasonable rows using combinations of value of emp# and project# as instances of the identifier. An experiment! • The experiment also works well with fewer attributes; just one additional attribute (other than the identifier) is usually sufficient. • We then try to repeat one of the instances of the identifier in a new row. • We try to find out if different attribute values are possible for that row (illustrated below in the first and last rows). It does not make sense to have the same assignment repeated with a different finish-date. ER Decomposition Of Many To Many

24. A ‘better’ Identifier • The identifier [ emp#,project#] is clearly inadequate • It is possible that the same employee can have multiple roles on the same project. • The identifier [ emp#,project#, role] now seems more sensible. • Look at the new row below and work out why the above identifier is more appropriate. ER Decomposition Of Many To Many

25. The Best Identifier?? • Finally the entity can be documented as below: ENTITY NAME Assignment ATTRIBUTES emp#, project#, role, supervisor, hrs-alloc, hrs-so-far, start-date, finish-date hrs-rec, IDENTIFIER emp#,project#, role DESCRIPTION etc. • Repeat the process until your judgement is that the entity is well enough defined. Never integrate an entity into a model if don't know it's identifier and some attributes ER Decomposition Of Many To Many

26. The 'Assignment' problem solution is very general and other solutions are possible. • Perhaps an employee can be reassigned to a project for the same role (e.g. programming). • Consider the following identifiers for the 'Assignment' complex entity and their implications. emp#,project#, role, start-date, supervisor, hrs-alloc, hrs-so-far, hrs-rec, finish-date emp#,project#, start-date, role, supervisor, hrs-alloc, hrs-so-far, hrs-rec, start-date, finish-date emp#,project#, role, sequence-number, supervisor, hrs-alloc, hrs-so-far, hrs-rec, start-date, finish-date emp#,project#, sequence-number, role, supervisor, hrs-alloc, hrs-so-far, hrs-rec, start-date, finish-date The sequence number simply implies a chronological order to an employee's assignments. ER Decomposition Of Many To Many

27. Summary • Introduced Decomposition of m:m relationships to form Complex Entities. • Explained that understanding Complex Entities can be difficult. • Introduced Asymmetric Viewing (a long name for a simple idea). • Showed that for any entity you need to know: ENTITY NAME ATTRIBUTES IDENTIFIER DESCRIPTION • Introduced a technique for verifying the identifiers of Complex Entities. ER Decomposition Of Many To Many

28. ERD SAMPLES ER Decomposition Of Many To Many

29. End of Lecture ER Decomposition Of Many To Many