MAPPING 1 TO 1 AND 1 TO MANY RELATIONSHIPS FROM AN ER DIAGRAM INTO A RELATIONAL DATABASE
STUDENT OBJECTIVES
• Upon completion of this video, you should be able to:
Copyright By PowCoder代写 加微信 powcoder
• Look at an ER Diagram and represent each of the 1 to 1 relationships and 1 to Many relationship in the relational model.
• Identify how the foreign keys indicate the relationship between tuples within tables in the relational model.
REPRESENTING RELATIONSHIPS USING ONLY TABLES
Suppose you have the following 2 ENTITIES from your ER diagram, now mapped to relational database as the tables: Professor and Department:
FirstName LastName EmpID Office Ext
DEPARTMENT
DeptID DeptName Building
Middlesex College
Computer Science
Middlesex College
Social Science Centre
Psychology
REPRESENTING 1 TO MANY RELATIONSHIPS IN RELATIONAL DATABASE MODEL
We want to show the following relationship as a table:
M Works for 1
QUESTION: How could you model this relationship using only
tables (rows or columns)?
The rules are:
1. YOU CAN ADD AS MANY NEW COLUMNS AND ROWS AS YOU WANT TO THE EXISTING TABLES
Department
2. AND IF YOU NEED A NEW TABLE YOU CAN ADD THAT ALSO,
3. BUT THAT IS ALL YOU CAN ADDàCOLUMNS, ROWS and TABLES
CS319 9/19/19
M Works for 1
Let’s say that Laura, Doug and Jamie all work for the Computer Science Department. Stuart and Irving work for the Math Department. Michael works for the Psychology Department
QUESTION: HOW CAN WE REPRESENT THIS IN OUR TABLES?
Department
FirstName LasLtaNsat IDEmpOIDffice OfEfixcte Ext DeptID*
ST28368905
VanVcaisnecise
MC 421MC83432515
AtkAintskoinson
SSC 44 SS8C34456
RanRkainkin
MC 101MC87160718
AndArnedwrsews
MC 343MC86374839
RobRionbsoinson
MC 102MC86170323
DEPARTMENT
DeptID DeptName Building
Middlesex College
Computer Science
Middlesex College
Psychology
Social Science Centre
M Works for 1
What if we add attributes to the relationship? How do we show that?
FirstName LastName EmpID Office Ext DeptID* Hours StartDate FirstName LastName EmpID Office Ext DeptID*
Laura d Reid
86905 ST238
Doug Vancise
Michael Atkinson
SSC 44 83456 SSC 44
Stuart Rankin
Jamie Andrews
Irving Robinson
DEPARTMENT
DeptID DeptName Building
Middlesex College
Computer Science
Middlesex College
Psychology
Social Science Centre
Department
QUESTION: What is the primary key of table PROFESSOR? ___E_m__p_I_D___, foreign key(s) _D__e_p_t_ID_____
What is the primary key of table DEPARTMENT? _______, foreign keys(s) __N_o__n_e_._.._Y_E_T_!____
REPRESENTING 1 TO 1 RELATIONSHIPS IN RELATIONAL DATABASE MODEL
We want to show the following additional relationship:
M WorksFor 1
Department
QUESTION: How could you model the CHAIRS relationship using only tables (rows or columns)?
Let’s say that Jamie is chair of the Computer Science Department. Stuart is chair of the Math Department. Michael is chair of the Psychology Department
QUESTION: HOW CAN WE REPRESENT THIS IN OUR TABLES?
Department
FirstName LastName EmpID Office Ext DeptID*
FirstName LastName EmpID Office Ext DeptID* ManageDeptID*
Laura d Reid
ST238 ST238
86905 86905
Doug Vancise
MC 421 MC 421
83355 83355
Michael Atkinson
SSC 44 SSC 44
83456 83456
Stuart Rankin
MC 101 MC 101
87678 87678
Jamie Andrews
MC 343 MC 343
86789 86789
Irving Robinson
MC 102 MC 102
86733 86733
DEPARTMENT
DepttIID DeDpetNptaNmaeme BuBiludiilndging *ManagerID
MiMddidledslexseCxoClloelglege
CoCmopmupteurteSrcSiecniecence MiMddidledslexseCxoClloelglege
syPcsyhcohlogloygy
SoScioacliaSlciSecniecencCeeCnetrnetre
QUESTION: What is the primary key of table PROFESSOR? __E_m__p_I_D____, foreign key(s) _D_e_p_t_I_D_____
What is the primary key of table DEPARTMENT? _______,
foreign keys(s) ________________
ANOTHER EXAMPLE OF HOW TO MAP ER RELATIONSHIPS TO A RELATIONAL DATABASE:
ISBN Title
Publisher MovieDeal
PubName PublD
TTitiltele
PrPicreice AuAthuotrhorAvaAilavbalielable
Publishes 1 1
Address HeadWriter
Movie Deal
AcceptDate
DealID AcceptDateNegName
NegotiatorName
StudioName
HHeaeaddScSrcerenenWWrirtietrer *ISBN
MAPPING MANY TO MANY RELATIONSHIPS FROM AN ER DIAGRAM INTO A RELATIONAL DATABASE.
STUDENT OBJECTIVES
• Upon completion of this video, you should be able to:
• Look at an ER Diagram and represent each of the Many to Many relationship in the relational model.
• Given a ternary relationship in an ER diagram, map it correctly to the tables and attributes in the relational model
REPRESENTING MANY TO MANY RELATIONSHIPS USING ONLY TABLES
Suppose now we add have the tables:
FirstName LastName EmpID Office Ext
CourseNumber CourseName
Intro to Databases
Data Structures and Algorithms
CS Fundamentals II
Discrete Structures
And we have the following relationship:
Professor M
N Course CourseNum
CourseName
Stuart teach MA2222?
How do we show that Laura and Doug teach CS3319 AND Doug, Laura and Jamie teach CS2210 AND Doug teaches CS1027 AND Irving and
CourseNumber CourseName FirstName LastName EmpID Office Ext FirstName LastName EmpID Office Ext Teaches
Intro to Databases Laura
Data Structures and Algorithms Doug Vancise
CS Fundamentals II
Michael Structures Stuart
Doug MC 421
Reid 86905
Vancise 83355
Atkinson 83456
Rankin 87678
Andrews 86789
Robinson 86733
CS3319, CS2210
22 MC 421 83355
CS3319, CS2210, CS1027
*CourseNumber *EmpID
Ext Office
CourseName
In Many To Many relationships, you make a NEW table and the key for the new table is the combination of the keys from the entities participation in the many to many relationship. Also include any attributes on the relationship in the new table.
QUESTION: What is the primary key of table NEW table called TEACHES ___E_m__p_ID___A_N__D__C_o_u__rs_e__N_u_m___?
What are the foreign key(s) of the table TEACHES? ____________________
EmpID AND CourseNum
CS319 9/19/19
ANOTHER EXAMPLE OF HOW TO MAP MANY TO MANY RELATIONSHIPS TO A RELATIONAL DATABASE:
FirstName ActorID
ReleaseDate
Actor Starring
ReleaseDate
TERNERY RELATIONSHIPS
• Take all the keys involved and put them in a new table and they make up the new key and also include any extra attributes.
MAPPING WEAK ENTITIES AND MULTIVALUED ATTRIBUTES FROM AN ER DIAGRAM INTO A RELATIONAL DATABASE.
STUDENT OBJECTIVES
• Upon completion of this video, you should be able to:
• Look at an ER Diagram with weak entities and represent the weak entities in the relational model.
• Look at an ER Diagram with multivalued attributes and represent multivalued attributes in the relational model
• List the seven rules that must be followed when mapping an ER diagram to a relational database.
REPRESENTING WEAK ENTITIES
Suppose now we add have the tables:
SectionNum Room
SectionNum
CourseNum *
MC230 MC230
MC230 MC230
CourseNumber CourseName
Intro to Databases
Data Structures and Algorithms
CS Fundamentals II
Discrete Structures
And we have the following relationship:
Offered by 1
SectionNum
CourseName
With WEAK entities, you bring the key from the owning entity as part of the key for the weak entity, so make the owning key a new column in the weak entity table and combine it with the weak key to make the new key.
QUESTION: What is the primary key of table SECTION __S_e_c_t_io__n_N_u__m__a_n__d_C__o_u_r_s_e_N__u?m
What are the foreign key(s) of the table SECTION? ____________________
CS319 9/19/19
ANOTHER EXAMPLE OF WEAK ENTITIES
BuildingID
Floor RoomNum
1 has N Room squareFeet
NumOfFloors
BuildingID
NumberOfFloors
Room Doors
SquareFeet
BuildingID*
MULTIVALUED ATTRIBUTES
We want to show that Homer speaks English and Spanish, Marg speaks English and Arabic and Lisa speaks English, French and Spanish.
LANGUAGESPOKEN
StudentNum FirstName LastName
StudentNum* Language
LanguageSpoken
StudentNum
With MULTIVALUED attributes, you create a new table and bring the key from the entity as part of the key together with the multivalued attribute and combine them to create the new key for the new table. Do NOT include the multivalued attribute in the original entity anymore.
QUESTION: What is the primary key of table LANGUAGESPOKEN _S_tu_d__e_n_t_N_u_m___a_n_d__L_a__n_g_u_a__g_e_?
What are the foreign key(s) of the table LANGUAGESPKEN? ____________________
StudentNum
CS319 9/19/19
MAPPING ER DIAGRAMS TO RELATIONAL DATABASESà THE SEVEN RULES!
• Step 1: For each regular entity type E in the ER schema, create a relation R that includes all simple attributes of E. Include only the simple component attributes of a composite attribute. Choose one of the key attributes of E as primary key for R. If the chosen key of E is composite, the set of simple attributes that form it will together form the primary key of R.
• Step 2: For each weak entity type W in the ER schema with owner entity type E, create a relation R, and include all simple attributes (or simple components of composite attributes) of W as attributes of R. In addition, include as foreign key attributes of R the primary key attribute(s) of the relation(s) that correspond to the owner entity type(s); The primary key of R is the combination of the primary key(s) of the owner(s) and the partial key of the weak entity type W, if any.
CS319 9/19/19
• Step 3: For each binary 1:1 relationship type R in the ER schema, identify the relations S and T that correspond to the entity types participating in R. Choose one of the relations S, say and include as foreign key in S the primary key of T. It is better to choose an entity type with total participation in R in the role of S. Include all the simple attributes (or simple components of composite attributes) of the 1:1 relationship type R as attributes of S.
• Step 4: For each regular (non weak) binary 1:N relationship type R, identify the relation S that represents the participating entity type at the N-side of the relationship type. Include as foreign key in S the primary key of the relation T that represents the other entity type participating in R; this is because each entity instance on the N-side is related to at most one entity instance on the 1-side of the relationship type. Include any simple attributes (or simple components of composite attributes) of the 1:N relationship type as attributes of S.
CS319 9/19/19
• Step 5: For each binary M:N relationship type R,create a new relation S to represent R. Include as foreign key in S the primary keys of the relations that represent the participating entity types; their combination will form the primary key of S. Also include any simple attributes (or simple components of composite attributes) of the M:N relationship type as attributes of S.
• Step 6: For each multivalued attribute A, create a new relation R that includes an attribute corresponding to A plus the primary key attribute K (as a foreign key in R) of the relation that represents the entity type or relationship type that has A as an attribute. The primary key of R is the combination of A and K. IF the multivalued attribute is composite, we include the simple components.
CS319 9/19/19
• Step 7: For each n-ary relationship type R, n > 2, create a new relation S to represent R. Include as foreign key in S the primary keys of the relations that represent the participating entity types; their combination will form the primary key of S. Also include any simple attributes (or simple components of composite attributes) of the n-ary relationship type as attributes of S.
TAKING OUR CASE STUDY FROM AN ER DIAGRAM TO A RELATIONAL DATABASE
STUDENT OBJECTIVES
• Upon completion of this video, you should be able to:
• Use the 7 rules to map ANY ER diagram to a relational database
OUR CASE STUDY AS AN ER DIAGRAM
USING THE 7 RULES AND MS ACCESS, LET’S MAP OUR ER DIAGRAM TO A RELATIONAL MODEL!
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com