Lecture_4-edited.pdf

1 IS 220 Database Fundamentals Lecture 4 THE RELATIONAL MODEL Book: Fundamentals of Database Systems – Seventh Edition : Chapter 5: The Relational Data Model and Relational Database Constraints & Chapter 9: Relational D...

1 IS 220 Database Fundamentals Lecture 4 THE RELATIONAL MODEL Book: Fundamentals of Database Systems – Seventh Edition : Chapter 5: The Relational Data Model and Relational Database Constraints & Chapter 9: Relational Database Design by ER- and EER-to-Relational Mapping ‹#›  1 IS 220 Database Fundamentals Lecture 4 THE RELATIONAL MODEL Book: Fundamentals of Database Systems – Seventh Edition : Chapter 5: The Relational Data Model and Relational Database Constraints & Chapter 9: Relational Database Design by ER- and EER-to-Relational Mapping ‹#› Learning Objectives Relational Model Concepts Relational Model Constraints and Relational Database Schemas Update Operations and Dealing with Constraint Violations ER-to-Relational Mapping Algorithm – Step 1: Mapping of Regular Entity Types – Step 2: Mapping of Multivalued attributes. – Step 3: Mapping of Weak Entity Types – Step 4: Mapping of Binary Relationship Types. – Step 5: Mapping of Recursive Relationship Types. – Step 6: Mapping of N-ary Relationship Types. EER-to-Relational Mapping Algorithm – Step 7: Mapping of Superclass/Subclass Relationship Types. ‹#› Stages of the Database System Development Lifecycle From Database Systems- A Practical Approach to Design, Implementation, and Management / Chapter 10 ‹#› Logical Database Design The main objective of this phase is to translate the conceptual data model created in phase 1 into a logical data model of the data requirements of the enterprise. In other words, to create relations for the logical data model to represent the entities, relationships, and attributes that have been identified. The relational Model of Data is based on the concept of a Relation. ‹#› Relational Model Concepts ‹#›  Relational Model Concepts Informal Definitions: Informally, a relation looks like a table of values. A relation typically contains a set of rows. The data elements in each row represent certain facts that correspond to a real- world entity or relationship – In the formal model, rows are called tuples Each column has a column header that gives an indication of the meaning of the data items in that column – In the formal model, the column header is called an attribute Key of a Relation: – Each row has a value of a data item (or set of items) that uniquely identifies that row in the table called the key ‹#›  Relational Model Concepts Formal Definitions - Schema The Schema (or description) of a Relation: – Denoted by R is the name of the relation The attributes of the relation are Example: CUSTOMER (Cust-id, Cust-name, Address, Phone #) – CUSTOMER is the relation name – Defined over the four attributes: Cust-id, Cust-name, Address, Phone # Each attribute has a domain or a set of valid values. – For example, the domain of Cust-id is 6 digit numbers. ‹#›  Relational Model Concepts Formal Definitions - Tuple A tuple is an ordered set of values (enclosed in angled brackets Each value is derived from an appropriate domain. A row in the CUSTOMER relation is a 4-tuple and would consist of four values, for example: – – This is called a 4-tuple as it has 4 values – A tuple (row) in the CUSTOMER relation. A relation is a set of such tuples (rows) ‹#›  Relational Model Concepts Formal Definitions - Domain A domain has a logical definition: – Example: “USA_phone_numbers” are the set of 10 digit phone numbers valid in the U.S. A domain also has a data-type or a format defined for it. – The USA_phone_numbers may have a format: (ddd)ddd-dddd where each d is a decimal digit. – Dates have various formats such as year, month, date formatted as yyyy-mm- dd, or as dd mm,yyyy etc. The attribute name designates the role played by a domain in a relation: – Used to interpret the meaning of the data elements corresponding to that attribute – Example: The domain Date may be used to define two attributes named “Invoice-date” and “Payment-date” with different meanings ‹#› Example of a Relation Figure 5.1 The attributes and tuples of a relation STUDENT. Key ‹#› Definition Summary Informal Terms Formal Terms Table Relation Column Header Attribute All possible Column Values Domain Row Tuple Table Definition Schema of a Relation Populated Table State of the Relation ‹#› Characteristics of Relations Properties of Relations: The relation has a name that is distinct from all other relation names in the relational DB. Each Attribute has a distinct name Each cell of the relation should contain an exactly single value Each value in a tuple must be from the domain of the attribute Each tuple is distinct. There are no duplicate tuples The order of attributes and tuples have no significance. A special null value is used to represent values that are unknown or not available or inapplicable in certain tuples. Only foreign keys should be used to refer to other entities ‹#› Relational Integrity Constraints ‹#›  Relational Integrity Constraints Constraints:.‫حدد القيم املسموح بها وأيها غير موجودة في قاعدة البيانات‬ determine which values are permissible and which are not in the database. Constraints are conditions that must hold on all valid relation states. There are three main types of (explicit schema-based) constraints that can be expressed in the relational model: – Key constraints – Entity integrity constraints – Referential integrity constraints Another schema-based constraint is the domain constraint – Every value in a tuple must be from the domain of its attribute (or it could be null, if allowed for that attribute) ‹#›  Key Constraints Any relation has one or more attributes whose values are distinct for each tuple. If a relation has several candidate keys, one is chosen arbitrarily to be the primary key. – The primary key attributes are underlined. The primary key value is used to uniquely identify each tuple in a relation Also used to reference the tuple from another tuple – General rule: Choose as primary key the smallest of the candidate keys (in terms of size) – Not always applicable – choice is sometimes subjective ‹#›  Entity Integrity – The primary key attributes PK of each relation schema R in S cannot have null values in any tuple of r(R). ▪ This is because primary key values are used to identify the individual tuples. ▪ for any tuple t in r(R) ▪ If P K has several attributes, null is not allowed in any of these attributes. ‹#›  Referential Integrity (or Foreign Key) Constraint A constraint involving two relations – The previous constraints involve a single relation. Used to specify a relationship among tuples in two relations: – The referencing relation and the referenced relation. Tuples in the referencing relation R1 have attributes F K (called foreign key attributes) that reference the primary key attributes P K of the referenced relation R2. A referential integrity constraint can be displayed in a relational database schema as a directed arc from R1 FK to R2. ‹#›  Referential Integrity (or Foreign Key) Constraint Statement of the constraint – The value in the foreign key column (or columns) FK of the referencing relation R1 can be either: ▪ (1) a value of an existing primary key value of a corresponding primary key PK in the referenced relation R2, or ▪ (2) a null. In case (2), the FK in R1 should not be a part of its own primary key. ‹#›  Relational Database Schema Relational Database Schema: – A set S of relation schemas that belong to the same database. – S is the name of the whole database schema – and a set IC of integrity constraints. – ,, are the names of the individual relation schemas within the database S Following slide shows the COMPANY database schema with 6 relation schemas ‹#› Schema diagram for: Company Database Figure 5.5 Schema diagram for the COMPANY relational database schema. ‹#›  Relational Database State A relational database state DB of S is a set of relation states such that each ri is a state of Ri and such that the ri relation states satisfy the integrity constraints specified in IC. A relational database state is sometimes called a relational database snapshot or instance. We will not use the term instance since it also applies to single tuples. A database state that does not meet the constraints is an invalid state ‹#›  Relational Database State Each relation will have many tuples in its current relation state The relational database state is a union of all the individual relation states Whenever the database is changed, a new state arises Basic operations for changing the database: – INSERT a new tuple in a relation – DELETE an existing tuple from a relation – MODIFY an attribute of an existing tuple Next slide (Figure 5.6) shows an example state for the COMPANY database schema shown in Figure 5.5 ‹#› Relational Database State for: COMPANY Database Figure 5.6 One possible database state for the COMPANY relational database schema. ‹#›  Displaying a Relational Database Schema and Its Constraints Each relation schema can be displayed as a row of attribute names The name of the relation is written above the attribute names The primary key attribute (or attributes) will be underlined A foreign key (referential integrity) constraints is displayed as a directed arc (arrow) from the foreign key attributes to the referenced table – Can also point the primary key of the referenced relation for clarity Next slide shows the COMPANY relational schema diagram with referential integrity constraints ‹#› Referential Integrity Constraints for: COMPANY Database Figure 5.7 Referential integrity constraints displayed on the COMPANY relational database schema. ‹#› Update Operations and Dealing with Constraint Violations ‹#›  Update Operations on Relations Update Operations: – INSERT a tuple. – DELETE a tuple. – MODIFY a tuple. Integrity constraints should not be violated by the update operations. In case of integrity violation, several actions can be taken: – Cancel the operation that causes the violation (RESTRICT or REJECT option) – Perform the operation but inform the user of the violation – Trigger additional updates so the violation is corrected (CASCADE option, SET NULL option) – Execute a user-specified error-correction routine ‹#›  Possible Violations for Each Operation INSERT may violate any of the constraints: – Domain constraint: if one of the attribute values provided for the new tuple is not of the specified attribute domain – Key constraint: if the value of a key attribute in the new tuple already exists in another tuple in the relation – Referential integrity: if a foreign key value in the new tuple references a primary key value that does not exist in the referenced relation – Entity integrity: if the primary key value is null in the new tuple DELETE may violate only referential integrity: – If the primary key value of the tuple being deleted is referenced from other tuples in the database (explained later in Lecture 6) UPDATE may violate domain constraint. ‹#› Exercise Consider the following relations for a database that keeps track of student enrollment in courses and the books adopted for each course: STUDENT (SSN, Name, Major, Bdate) COURSE (Course#, Cname, Dept) ENROLL (SSN, Course#, Quarter, Grade) BOOK_ADOPTION(Course#, Quarter, Book_ISBN) TEXT (Book _ISBN, Book_Title, Publisher, Author) Draw a relational schema diagram specifying the foreign keys for this schema. ‹#› Mapping ERD to Relations ‹#›  Goals during Mapping Preserve all information mentioned in ER diagram Maintain the constraints Minimize null values Steps to convert the ERD to Logical model: We specify the name of the relation followed by a list of the relation’s simple attributes enclosed in brackets (without derived attributes). We then identify the primary key and foreign key(s) of the relation. Any derived attributes are listed together with how each one is calculated. For example: Staff (staffNo, fName, lName, position, sex, DOB) Client (clientNo, fName, lName, telNo, prefType, maxRent, staffNo) Foreign Key staffNo references Staff(staffNo) ‹#›  Mapping relations for logical data model We describe how relations are derived for the following structures that may occur in a conceptual data model: (1) Regular (strong) entity types; (2) Multi-valued attrubites; (3) Weak entity types; (4) Binary relationship types: One to many / One to one / Many to many (5) Recursive (unary) relationship types: One to many / One to one / Many to many (6) Complex (N-ary) relationship types; (7) Superclass/subclass relationship types; ‹#› ER-to-Relational Mapping Algorithm Step 1: Mapping of Regular (strong) Entity Types. – For each regular entity type E in the ER schema, create a relation R that includes all the simple attributes of E. – If composite attributes exist, only their component simple attributes are needed. – Derived attributes are usually omitted. Employee ( EmpNo, FName, MName, LName ) Course ( Cno, Cname) ‹#› ER-to-Relational Mapping Algorithm Step 2: Mapping of Multivalued attributes – For each multivalued attribute A, create a new relation R. – This relation R will include 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 of 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 its simple components. EPK E (EPK) E EM ( M , EPK) M FK : EPK references E (EPK) ‹#› ER-to-Relational Mapping Algorithm Step 2: Mapping of Multivalued attributes Example: If multivalued attribute included with other simple attributes: After Mapping according to the rule: ‹#› ER-to-Relational Mapping Algorithm Step 3: Mapping of Weak Entity Types – For each weak entity type W with owner entity type E, create a relation R & include all simple attributes of W as attributes of R. – Also, 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. M N E R E 1 2 X B A Y E1 ( A, B ) E2 (X, A, Y) FK : A references E1 (A) ‹#› ER-to-Relational Mapping Algorithm Step 3: Mapping of Weak Entity Types Example: Employee(SSN, ename, salary) Contact(SSN, name, phone) ‹#› ER-to-Relational Mapping Algorithm Step 4: Mapping of Binary Relationship Types Binary 1:N Relationship Types – For each regular binary 1:N relationship type R, identify the relation S that represent 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 one-side entity type participating in R. – Include any simple attributes of the 1:N relation type as attributes of S. 1 N E R E 1 2 E2-PK A E1-PK R1 B E1 ( E1-PK , A) E2 ( E2-PK , B , E1_PK , R1 ) FK1 : E1-PK references E1 (E1-PK) ‹#› ER-to-Relational Mapping Algorithm Step 4: Mapping of Binary Relationship Types Binary 1:N Relationship Types Example: ‹#› ER-to-Relational Mapping Algorithm Step 4: Mapping of Binary Relationship Types Binary 1:N Relationship Types Example: After Mapping according to the rule: ‹#›  ER-to-Relational Mapping Algorithm Step 4: Mapping of Binary Relationship Types Binary 1:1 Relationship Types ▪ Creating relations to represent a 1:1 relationship is slightly more complex as the cardinality is one at both sides. ▪ In this case, the participation is used to help decide how to map the relationship. ▪ There are three possible approaches: (a) mandatory participation on both sides of 1:1 relationship (b) optional participation on both sides of 1:1 relationship (c) mandatory participation on one side of 1:1 relationship ‹#›  ER-to-Relational Mapping Algorithm Step 4: Mapping of Binary Relationship Types Binary 1:1 Relationship Types In case of: (a) mandatory participation on both sides of 1:1 relationship (b) optional participation on both sides of 1:1 relationship Choose one entity and add its PK, and attributes of the relationship, to the other entity. 1 1 E R E 1 2 E2-PK A E1-PK R1 B E1 ( E1-PK , A) E1 ( E1-PK , A , E2-PK , R1 ) FK1 : E2-PK references E2 (E2-PK) E2 ( E2-PK , B , E1-PK , R1 ) FK1 : E1-PK references E1 (E1-PK) E2 ( E2-PK , B) ‹#›  ER-to-Relational Mapping Algorithm Step 4: Mapping of Binary Relationship Types Binary 1:1 Relationship Types In case of: (c) mandatory participation on one side of 1:1 relationship Add the PK attributes of the optional side entity and attributes of the relationship, to the mandatory side entity. 1 1 E1 R E2 E2-PK A E1-PK R1 B E1 ( E1-PK , A) E2 ( E2-PK , B , E1-PK , R1 ) FK1 : E1-PK references E1 (E1-PK) ‹#›  ER-to-Relational Mapping Algorithm Step 4: Mapping of Binary Relationship Types Binary 1:1 Relationship Types Example: ‹#›  ER-to-Relational Mapping Algorithm Step 4: Mapping of Binary Relationship Types Binary 1:1 Relationship Types Example: ‹#› ER-to-Relational Mapping Algorithm Step 4: Mapping of Binary Relationship Types Binary M:N Relationship Types – For each regular binary M:N relationship type R, create a new relation S to represent R. This is a relationship relation. – Include as foreign key attributes 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 of the M:N relationship type as attributes of S. ‹#› ER-to-Relational Mapping Algorithm Step 4: Mapping of Binary Relationship Types Binary M:N Relationship Types Example: ‹#› ER-to-Relational Mapping Algorithm Step 4: Mapping of Binary Relationship Types Binary M:N Relationship Types Example: ‹#› Nothing mentioned about Unary relationship mapping in the book chapter. This content is from old slides. ER-to-Relational Mapping Algorithm Step 5: Mapping of Unary Relationship Types Unary 1:M Relationship Types For a 1:N recursive relationship, represent it as a single relation with two copies of the primary key ( one needs to be renamed and treated as the FK), plus attributes of the relationship. A recursive foreign key is a foreign key in a relation that references the primary key values of that same relation. 1 M E1 R R1 A E1-PK E1 ( E1-PK , A , E1_PK_Copy , R1 ) FK1 : E1_PK_Copy references E1 (E1-PK) ‹#› ER-to-Relational Mapping Algorithm Step 5: Mapping of Unary Relationship Types Unary 1:M Relationship Types Example: Employee ( Employee_ID, Employee_Name, Employee_Address , Employee_Salary, Manager_ID) FK : Manager_ID references Employee (Employee_ID) ‹#› ER-to-Relational Mapping Algorithm Step 5: Mapping of Unary Relationship Types Unary M:N Relationship Types For a M:N recursive relationship, Two relations are created; One to represent the entity type in the relationship A new relation to represent the relationship. The new relation would include a copy of the primary key of the entity, a renamed copy of the primary key, and attributes of the relationship. The PKs of the new relation: consist of two attributes, the PK of the entity and the renamed key. The two copies of the PKs also act as foreign keys referencing the Pk in the original entity. E1 ( E1-PK , A) M E R New_E ( E1-PK , E1_PK_copy , R1 ) 1 N R1 FK1 : E1-PK references E1 (E1-PK) FK2 : E1_PK_copy references E1 (E1-PK) A E1-PK ‹#›  ER-to-Relational Mapping Algorithm Step 5: Mapping of Unary Relationship Types Unary M:N Relationship Types Example: Quantity Item_no ITEM M Name Contains N Unit_Cost ITEM (Item_No, Name, Unit_Cost) COMPONENT (Item_No, Component_No, Quantity) Team (name, coach) FK1: Item_No references ITEM (Item_No) Teamsmatches (HomeTeam, VisitorTeam, score) FK2: Component_No references ITEM (Item_No) FK1: HomeTeam references Team (name) FK2: VisitorTeam references Team (name) ‹#›  ER-to-Relational Mapping Algorithm Step 5: Mapping of Unary Relationship Types Unary 1:1 Relationship Types Mandatory participation on both sides Follow the rules of mapping 1:M unary relationship represent the recursive relationship as a single relation with two copies of the primary key. Optional on both sides Follow the rules of mapping M:N unary relationship create a new relation to represent the relationship. Mandatory on one side Follow either of the two methods above. ‹#› ER-to-Relational Mapping Algorithm Step 5: Mapping of Unary Relationship Types Unary 1:1 Relationship Types Example Date Name Husband Person 1 SSN Married To 1 Wife Person (SSN , Name) Marriage (Husband_SSN, Wife_SSN, Date) FK1: Husband_SSN reference Person(SSN) FK2: Wife_SSN reference Person(SSN) ‹#›  ER-to-Relational Mapping Algorithm Step 6: Mapping of N-ary Relationship Types – For n-ary relationship type R, where n>2, create a new relationship S to represent R. – Include as foreign key attributes in S the primary keys of the relations that represent the participating entity types. Also include any simple attributes of the n-ary relationship type as attributes of S. – The primary key of S is usually a combination of all the foreign keys that reference the relations representing the participating entity types. However, if the cardinality constraints on any of the entity types E participating in R is 1, then the primary key of S should not include the foreign key attribute that references the relation E corresponding to E. ‹#› ER-to-Relational Mapping Algorithm Step 6: Mapping of N-ary Relationship Types A E3- PK E3 E R E 1 2 E2- A E1- R1 B PK PK E1 ( E1-PK , A) E2 ( E2-PK , B) E3 ( E3-PK , A) R (E1-PK, E2-PK , E3-PK, R1 ) FK1 : E1-PK references E1 (E1-PK) FK2 : E2-PK references E2 (E2-PK) FK3: E3-PK references E3 (E3-PK) ‹#› ER-to-Relational Mapping Algorithm Example: The relationship type SUPPY in the below ER diagram. – This can be mapped to the relation SUPPLY shown in the relational schema, whose primary key is the combination of the three foreign keys {S NAME, PART N O, PROJ NAME} ‹#› Summary of Mapping Constructs and Constraints Table 9.1 Correspondence between ER and Relational Models ER Model Relational Model Entity type Entity relation 1:1 or 1:N relationship type Foreign key (or relationship relation) M:N relationship type Relationship relation and two foreign keys n-ary relationship type Relationship relation and n foreign keys Simple attribute Attribute Composite attribute Set of simple component attributes Multivalued attribute Relation and foreign key Value set Domain Key attribute Primary (or secondary) key ‹#›  Mapping COMPANY Database ER Diagram to Relational Model In the following slides, this ER diagram will be converted step by step to a Relational model ‹#›  Mapping COMPANY Database ER Diagram to Relational Model Mapping of Regular Entity Types We create the relations EMPLOYEE, DEPARTMENT, and PROJECT in the relational schema corresponding to the regular entities in the ER diagram. SSN, DNUMBER, and PNUMBER are the primary keys for the relations EMPLOYEE, DEPARTMENT, and PROJECT. Mapping of Weak Entity Types Create the relation DEPENDENT to correspond to the weak entity DEPENDENT. Include the primary key SSN of the EMPLOYEE relation as a foreign key attribute of DEPENDENT (renamed to ESSN). The primary key of the DEPENDENT relation is the combination {ESSN, DEPENDENT_NAME} because DEPENDENT_NAME is the partial key of DEPENDENT. ‹#›  Mapping COMPANY Database ER Diagram to Relational Model Mapping of Multivalued attributes The relation DEPT_LOCATIONS is created. The attribute DLOCATION represents the multivalued attribute LOCATIONS of DEPARTMENT, while DNUMBER-as foreign key-represents the primary key of the DEPARTMENT relation. The primary key of R is the combination of {DNUMBER, DLOCATION}. Binary 1:N Relationship Types 1:N relationship types WORKS_FOR, CONTROLS, and SUPERVISION in the figure. For WORKS_FOR we include the primary key DNUMBER of the DEPARTMENT relation as foreign key in the EMPLOYEE relation and call it DNO. Binary 1:1 Relationship Types 1:1 relation MANAGES is mapped by choosing the participating entity type DEPARTMENT to serve in the role of S, because its participation in the MANAGES relationship type is total. ‹#› Mapping COMPANY Database ER Diagram to Relational Model Binary M:N Relationship Types The M:N relationship type WORKS_ON from the ER diagram is mapped by creating a relation WORKS_ON in the relational database schema. – The primary keys of the PROJECT and EMPLOYEE relations are included as foreign keys in WORKS_ON and renamed PNO and ESSN, respectively. – Attribute HOURS in WORKS_ON represents the HOURS attribute of the relation type. The primary key of the WORKS_ON relation is the combination of the foreign key attributes {ESSN,PNO}. ‹#› Result of Mapping the COMPANY ER Schema into a Relational Database Schema ‹#› Result of Mapping the COMPANY ER Schema into a Relational Database Schema Employee ( SSN, Fname, Minit, Lname, Bdate, Address, Sex, Salary, Super_ssn, Dno) FK : Super_ssn references Employee (SSN) FK: Dno references Department(Dnumber) Department(Dnumber, Dname, Mgr_ssn, Mgr_start_date) Dept_Locations(Dnumber, Dlocation) FK: Dnumber references Department(Dnumber) Project(Pnumber, Pname, Plocation, Dnum) FK: Dnum references Department(Dnumber) Works_On(Essn, Pno, Hours) FK: Essn references Employee (SSN) FK: Pno references Project (Pnumber) Dependent( Essn, Dependent_name, Sex, Bdate, Relationship) FK: Essn references Employee (SSN) ‹#› Mapping of Generalization and Specialization Hierarchies to a Relational Schema ‹#›  Mapping EER Model Constructs to Relations Step 7: Mapping of Superclass/ Subclass relationship types There are various options on how to represent such a relationship as one or more relations. The selection of the most appropriate option is dependent on several factors: Disjointness and participation constraints on the superclass/subclass relationship. There are four options: 1. Mandatory / Nondisjoint 2. Optional / Nondisjoint 3. Mandatory / Disjoint 4. Optional / Disjoint ‹#›  Mandatory / NonDisjoint Suppose specialization with subclasses (S1, S2,.., Sm) & a superclass C. – Create a relation L to represent the superclass C with PK & attributes. – Include the unshared attributes for each subclass S – Add a discriminator (flag) in L to distinguish the type of each tuple. C1 C C_PK o Attribute_S1 Attribute_S2 S1 S2 L (C_PK, C1, Attribute_S1, Attribute_S2, S1_flag, S2_flag) ‹#› Mandatory / NonDisjoint Example o EMPLOYEE( Ssn, FName, Minit, LName, BirthDate, Address, JobType, TypingSpeed,TGrade, EngType, Secretary Flag, Technician Flag, Engineer Flag ) ‹#›  Mandatory / Disjoint Suppose specialization with subclasses (S1, S2,.., Sm) & a superclass C. Create a relation Li, 1 i m, to represent each combination of super/subclass. C1 C C_PK d Attribute_S1 Attribute_S2 S1 S2 L1 (C_PK, C1, Attribute_S1) L2 (C_PK, C1, Attribute_S2) ‹#› Mandatory / Disjoint Example d SECRETARY(Ssn, FName, Minit, LName, BirthDate, Address, JobType, TypingSpeed) TECHNICIAN(Ssn, FName, Minit, LName, BirthDate, Address, JobType, Tgrade) ENGINEER(Ssn, FName, Minit, LName, BirthDate, Address, JobType, EngType) ‹#›  Optional / NonDisjoint Suppose specialization with subclasses (S1, S2,.., Sm) & a superclass C. Create a relation L1 to represent C with PK & attributes. Create a relation L2 to represent all subclasses Si, 1 i m. Add a discriminator (flag) in L2 to distinguish the type of each tuple. C C1 C_PK o Attribute_S1 Attribute_S2 S1 S2 L1 (C_PK, C1) L2 (C_PK, Attribute_S1, Attribute_S2, S1_flag, S2_flag) FK: C_PK References L1 (C_PK) ‹#› Optional / NonDisjoint Example o EMPLOYEE(Ssn, FName, Minit, LName, BirthDate, Address, JobType) SUB-EMP(Ssn, TypingSpeed, TGrade, EngType, Secretary Flag, Technician Flag, Engineer Flag) FK: Ssn References EMPLOYEE (Ssn) ‹#›  Optional / Disjoint Suppose specialization with subclasses (S1, S2,.., Sm) & a superclass C. Create a relation L to represent C with PK & attributes. Create a relation Li to represent each subclass Si, 1 i m, and include the PK. C1 C C_PK d Attribute_S1 Attribute_S2 S1 S2 L (C_PK, C1) L1 (C_PK, Attribute_S1) FK: C_PK References L(C_PK) L2 (C_PK, Attribute_S2) FK: C_PK References L(C_PK) ‹#› Optional / Disjoint Example d EMPLOYEE(Ssn, FName, Minit, LName, BirthDate, Address, JobType) SECRETARY(Ssn, TypingSpeed) FK: Ssn References EMPLOYEE (Ssn) TECHNICIAN(Ssn, Tgrade) FK: Ssn References EMPLOYEE ‹#› (Ssn) Summary of Mapping Superclass/subclass relationship types ‹#› Example ‹#› Example ‹#›  Mapping Extra Exercise-1 78 Map this schema into a set of relations. sex City Bdate Adress Street SSN Fname Apt_no Person Name Lname Office d class Rank Salary Faculty Student N Belong M M Major 1 ID Department name ‹#›  Mapping Extra Exercise-1 79 Faculty(fname,Lname,SSN,bdate,sex,city,street,Apt_no, salary,Rank,Office) Student(fname,Lname,SSN,bdate,sex,city,street,Apt_no,class,PID, DID) FK1 : PID references Program(PID) FK2 : DID references Department(ID) Department (ID,Name) Program (Pname,PID) Subject (Sub_ID,Sub_name) Attempt (grade,year,SSN,Sub_ID) FK1 : SSN references student(ssn) FK2 : Sub_id references subject(sub_id) ‹#› Belong (SSN,DID) Mapping Extra Exercise-2 Map this schema into a set of relations. Figure An ER schema for a SHIP_TRACKING database. ‹#› Mapping Extra Exercise-3 Map this schema into a set of relations Figure EER Diagram for a Car Dealer ‹#›