ER to RDM Mapping

1. ER to RDM Mapping Database Principles

2. Mapping from ER to Relational Data Model the next phase Exercise: Give me some suggestions on what this mapping would look like. Database Principles

3. remember this word Relational Data Model • Models all information stored in a database as a collection of related mathematical structures called relations. • Suppose • Students = {s1, s2, s3, …, sn} and • Courses = {c1, c2, c3, …, cm} • The Cartesian Product of these two sets is: • Students x Courses = { (si, cj) | siε Students, cjε Courses } Database Principles

4. Student x Course Grid: (sn,cm) (s2,c3) (s1,c1) Database Principles

5. Relational Data Model • A relation is a subset of a Cartesian Product. • Enrollment = {(s1, c2), (s2, c3), (s2, c2) } • Enrollment is a subset of Students x Courses Exercise: Suppose Student is a set of patients and Professor is a set of doctors.Give several examples of relations that are subsets of Student x Professor. Exercise: Go through the exercise of asking a student you do not know for examples of useful relations and identity their corresponding Cartesian Products. Database Principles

6. Enrollment Enrollment NOTE: Points inside theellipse correspond to real-world information. Points outside the ellipse do notcorrespond to any real-world event and we callthem noise. noisepoints Database Principles

7. Exercise: • A tournament of local soccer teams is run as a double round-robin tournament. What does this mean? • Describe the difference between such a tournament and one we could describe as a cartesian product tournament. • Work in pairs. If you know what double round-robin means find someone who doesn’t and explain it to them. Then answer the question. • Draw a simple ER for thisexample Database Principles

8. ID SName SAddress Relational Data Model • A table containing a list of all suppliers - an ID, name and address for each – is called Supplier. • This table is a subset of the Cartesian Product so mathematically it too is a relation. • Of course, the only rows in the table are the useful tuples of the Cartesian Product, those that really correspond to some supplier. Supplier SupplierIDs x SupplierNames x SupplierAddresses Database Principles

9. Exercise: • Consider the previous round-robin tournament. • What happens if we have an extra line in the Plays table? • This line is noise!! It makes the entire table useless. Why? Plays noise A C 2 1 Rule to Follow: Database tables should contain no noise. Database Principles

10. ID SName SAddress Table Parts • A table has two parts – intension and extension • Intension: what the table is “intended” to model. In essence, the table name and the name of each of its columns; otherwise known as the table schema. • Extension: A list of all possible rows of the table; both presently existing and what might exist in the future or has existed in the past. Supplier Table Schema extension Database Principles

11. ER Entity Relationship Attribute Key Attributes Instance RDM Table/Relation Schema Table/Relation Schema Column/Attribute Primary Key Row/Tuple Notational Correspondence: Exercise: The title of this lecture is “ER-to-RDM Mapping“. The above notational correspondence leads one to think that this mapping is quitestraight forward. What is the one thing that is not straight forward about this implied mapping? Database Principles

12. ER-to-RDM Mapping, Rule 1: • Each entity is mapped to a table schema with the same columns as the entity has attributes. CARDHOLDER borrowerid b_name b_addr b_status loan_limit maps to CARDHOLDER borrowerid b_name b_addr b_status loan_limit pk Database Principles

13. Rule 1 (cont) • The actual mapping is to syntax in SQL which can be used to create the table described by our schematic. Create table CARDHOLDER ( borrowerid int not null primary key, b_name varchar(10), b_addr varchar(10), b_status char(6), loan_limit int ) its an integercan never be nullis the primary key of the table CARDHOLDER borrowerid b_name b_addr b_status loan_limit pk Database Principles

14. ER-to-RDM Mapping, Rule 2a: • If a relationship has no (1,1) participation number pair then it too is mapped to a table schema. The table schema consists of: • The key attributes of all entities participating in the relationship • All attributes of the relationship. • The primary key of the new table consists of all the key attributes of the entities participating in the relationship and any relationship attribute identifies as part of the key. Database Principles

15. ER-to-RDM Mapping, Rule 2a: Database Principles

16. ER-to-RDM Mapping, Rule 2b: • If a relationship has a (1,1) participation number pair then it is not mapped to a table schema. • Instead, the table schema corresponding to the entity with the (1,1) pair takes on additional columns: • The keys of all other entities participating in the relationship are migrated to the entity table schema • The attributes of the relationship itself are migrated to the entity able schema. • The primary key of the entity table schema does not change. Database Principles

17. ER-to-RDM Mapping, Rule 2b: BOOK stays the same Database Principles

18. Review of Mapping Complexity: • Relationships model real-world associations. • In a database, these associations can be modeled in one of two ways: • By a table of their own; the table key is a combination of entity keys • Within one of the participating entity tables. Database Principles

19. Weak Entity ER-to-RDM Mapping, Rule 2b: • Relationships that attach to a weak entity always follow Rule 2b. This is because the weak entity always has a (1,1) pair. However, the key to the table schema corresponding to the weak entity consists of the attributes in the weak entity key. Database Principles

20. IS_A ER-to-RDM Mapping, Rule 2b: • IS_A relationships always follow Rule 2b because the sub-entity always has a (1,1) pair. Database Principles

21. Considering (0,1) as (1,1) • What if a relationship with a (0,1) pair were mapped to a RDM under the assumption that the (0,1) pair is really (1,1)? becomes (1,1) disappears Database Principles

22. Are These Models Equivalent? • Suppose two copies exist – one on loan and one not. since this book is noton loan there is no borrowerid or l_date So there can only be one possible value that can go into the borrowerid columnof the row in Copy that contains “qt-2.34_c1” There can only be one row in Borrows that contains thevalue “qt-23.4_c1”. Why? Database Principles

23. Considering (1,1) as (1,n): • Suppose you are a database designer and are told that every project is managed by precisely one employee. • But you ask, “Might it be possible in the future to have more than one manager for a project?”, and the answer is “Yes”. Database Principles

24. Considering (1,1) as (1,n): • x Database Principles

25. Considering (1,1) as (1,n): • The advantage of the second model is that the relationship <managed by> will be mapped to its own table. This is not true in the first case. • If you create the ManagedBy table from the very beginning it will be there when you need it. • A little more work up front saves a lot of work later on. • You can show off later on by saying “It will only take a second”, come back in 15 minutes and say “the database can now handle multiple project managers” because it always could even when it didn’t need to. • If you are working as a consultant you can bill for 8 hours even though it didn’t take any time at all. Database Principles

26. Library to Relational Data Model • In the next series of slides we map the Library ER Model to a Relational Data Model Database Principles

27. Cardholder to RDM not (1,1) NOTE: As an entity,Cardholder maps to a table with all its attributesas table columns.The key to Cardholderbecomes the primary key to the table Cardholder.Since Cardholder has no(1,1) participation numberpairs, no new columns are added to the table. Database Principles

28. Book to RDM not (1,1) NOTE: As an entity,Book maps to a table with all its attributesas table columns.The key to Bookbecomes the primary key to the table Book.Since Book has no (1,1)participation numberpairs, no new columns are added to the table. Database Principles

29. Reserves to RDM NOTE: As a relationshipwith no (1,1) pair, reservesis mapped to a table whosecolumns include the keysto the entities participatingin the reserves relationshiptogether with the attributesof reserves. Database Principles

30. Borrows to RDM NOTE: As a relationshipwith no (1,1) pair, borrowsis mapped to a table whosecolumns include the keysto the entities participatingin the borrows relationshiptogether with the attributesof borrows. Database Principles

31. Copy to RDM NOTE: As an entity witha (1,1) participation numberpair, Copy is mapped to a table that contains all the Copy attributes and the keyattributes of the other entities(Book) in the relationship where Copy has a (1,1) pair. The relationship with the (1,1) pair is not mapped toa table. Database Principles

32. Putting it all Together: Database Principles

33. Exercise: ER 2 RDM Person, Office, Professor Student, Course, Has_taughtCrsSection, Enrolled_in Database Principles