Theory Practice Methodology of Relational Database Design and

Theory, Practice & Methodology of Relational Database Design and Programming Copyright © Ellis Cohen 2002 -2008 M: N Relationships & Bridge Classes These slides are licensed under a Creative Commons Attribution-Non. Commercial-Share. Alike 2. 5 License. For more information on how you may use them, please see http: //www. openlineconsult. com/db © Ellis Cohen 2001 -2008

Overview of Lecture M: N Relationships and Cascading Delete Joins Involving M: N Relationships Bridge Entity Classes Bridge Class Attributes Partial Associations Using Dependent & Identifying Relationships Uniqueness Weak Bridge Classes with Attributes Weak Bridge Classes with Participation & Cardinality Discriminated Weak Bridge Classes Relationships with Bridge Entity Classes © Ellis Cohen 2001 -2008 2

M: N Relationships © Ellis Cohen 2001 -2008 3

M: N Relationships Visual Conceptual Model (Crow Magnum) Employee assigned to Project Each employee may be assigned to a number of projects Each project may have a number of employees Textual Conceptual Model (Brief Con. Text) Assignment: (*) Employee assigned to (*) Project © Ellis Cohen 2001 -2008 4

M: N Relationship Notations Chen assigned to Employee Project No key constraints! UML Employee Crow Magnum Employee Easy Crow Magnum Employee * assigned to © Ellis Cohen 2001 -2008 * Project 5

Simple Relational Mapping 1. Make each entity class a table Use singular for entity class Use plural for table 2. Make each relationship a table (called a bridge table) © Ellis Cohen 2001 -2008 6

Entity Classes Tables Make each entity class a table • Each attribute of the entity class becomes an attribute (i. e. a column) of the table • Define a primary key for the entity class if necessary • The primary key of the entity class becomes the primary key of the table Use singular for entity class Emps empno ename Use plural for table address 7499 ALLEN 12 Lehigh … 7654 MARTIN 7698 BLAKE … 2618 Payroll 7839 KING … 2621 Bridge Design 7844 TURNER … 7986 STERN … 2622 Update Reqs 1400 … Projs pno © Ellis Cohen 2001 -2008 pname … 7

M: N Related Instances assigned to Employee Project empno ename pno address 7499 ALLEN . . . 7654 MARTIN . . . 7698 BLAKE . . . 7839 KING . . . 7844 TURNER . . . 7986 STERN . . . pno pname … 2618 … 2621 … 2622 … © Ellis Cohen 2001 -2008 8

Relationships Tables assigned to Employee Project empno … Composite primary key Emps Asns empno ename address empno 7499 ALLEN . . . 7654 2621 7654 MARTIN … 7698 2618 7698 BLAKE … 7698 2622 7839 KING … 7844 2618 … 7844 TURNER … 7844 2622 2621 … 7986 STERN … 7986 2622 … Projs pno pname … Bridge Table © Ellis Cohen 2001 -2008 9

Tables & Relational Schema Emps Asns empno ename address empno 7499 ALLEN . . . 7654 2621 7654 MARTIN … 7698 2618 7698 BLAKE … 7698 2622 7839 KING … 7844 2618 … 7844 TURNER … 7844 2622 2621 … 7986 STERN … 7986 2622 … Projs pno pname … Visual Relational Model (Relational Schema) Emps empno ename Asns empno Note cascading deletes © Ellis Cohen 2001 -2008 Projs pno pname budget 10

M: N Relationships and Cascading Delete © Ellis Cohen 2001 -2008 11

M: N Related Instances assigned to Employee Project empno ename pno address 7499 ALLEN . . . 7654 MARTIN . . . 7698 BLAKE . . . 7839 KING . . . 7844 TURNER . . . 7986 STERN . . . If a project is deleted, what happens to its assignment links? pno pname … 2618 … 2621 … 2622 … © Ellis Cohen 2001 -2008 12

No Dangling Links assigned to Employee Project empno ename pno address 7499 ALLEN . . . 7654 MARTIN . . . 7698 BLAKE . . . 7839 KING . . . 7844 TURNER . . . 7986 STERN . . . Links that don't connect to an entity instance are not allowed! 2621 … 2622 … © Ellis Cohen 2001 -2008 13

M: N & Cascading Delete Visual CONCEPTUAL Model (Crow Magnum) Employee assigned to Project pno … empno … Visual RELATIONAL Model (Relational Schema) The standard relational mapping for an M: N relationship uses cascading delete Emps empno ename Asns empno © Ellis Cohen 2001 -2008 Projs pno pname budget 14

Mapping M: N to Relational Schema CONCEPTUAL Model Assignment: (*) Employee assigned to (*) Project RELATIONAL Mapping Assignment mapped to Asns( empno, pno ), with empno references Emps on cascade delete pno references Projs on cascade delete RELATIONAL Model Asns empno number(4) references Emps on cascade delete pno number(3) references Projs on cascade delete • primary key( empno, pno ) © Ellis Cohen 2001 -2008 15

M: N w Cascading Delete in SQL Emps empno ename Asns empno Projs pno pname budget CREATE TABLE Asns( empno number(5) references Emps on delete cascade, pno number(6) references Projs on delete cascade, primary key( empno, pno ) ); What would be the implication of not using cascading delete for one or both of the foreign key constraints, on operations that delete Employees or Projects? © Ellis Cohen 2001 -2008 16

Eliminating Cascading Delete Emps empno ename Asns empno + An employee assigned to a project can't be deleted Emps empno ename Asns empno + A project with employees assigned to it can't be deleted Projs pno pname budget Added business rules can eliminate cascading delete from the relational model © Ellis Cohen 2001 -2008 17

Implication of No Cascading Deletes Emps empno ename Asns empno Projs pno pname budget Since deletes do not cascade, the application must make sure that before it deletes an employee or project, it explicitly eliminates all of its assignments. For example, before deleting an employee, it must explicitly delete its assignments, perhaps in the meantime assigning a different employee to the project! © Ellis Cohen 2001 -2008 18

Incorrect M: N Relational Mapping Visual Conceptual Model (Crow Magnum) Employee assigned to empno ename Project pno pname budget What's WRONG with this relational mapping? Emps Projs empno ename pno WRONG! © Ellis Cohen 2001 -2008 pno pname budget empno 19

Incorrect M: N Relational Mapping Explanation Emps Projs empno ename pno 1 2 pno pname budget empno 1. Each employee (identified by empno) has a single associated pno. Implies there is only a single project associated with each employee! 2. Each project (identified by pno) has a single associated empno. Implies there is only a single employee associated with each project! 1: 1 relationship Employee assigned to © Ellis Cohen 2001 -2008 Project 20

Reflexive M: N Relationships likes to work with Employee empno ename What's the corresponding relational schema? © Ellis Cohen 2001 -2008 21

Reflexive M: N Mapping likes to work with Employee empno ename Emps empno ename Likes. Working. With emp 1 emp 2 © Ellis Cohen 2001 -2008 22

SQL for Reflexive M: N Mapping Emps empno ename Likes. Working. With emp 1 emp 2 CREATE TABLE Likes. Working. With( emp 1 number(5) references Emps on delete cascade, emp 2 number(6) references Emps on delete cascade, primary key( emp 1, emp 2 ) ); © Ellis Cohen 2001 -2008 23

Joins Involving M: N Relationships © Ellis Cohen 2001 -2008 24

Joins with M: N Relationships Employee Emps empno ename assigned to Asns empno Project Projs pno pname budget For each (named) project, list the names of the employees assigned to it SELECT pname, ename FROM ( (Emps NATURAL JOIN Asns) NATURAL JOIN Projs ) ORDER BY pname, ename © Ellis Cohen 2001 -2008 25

Size of M: N Joins If there are 200 tuples in Emps 5 tuples in Projs How many tuples (min and max) could there be in the NATURAL JOIN of Emps and Asns and Projs? Consider each of these 4 cases 1 2 Employee 3 Project Employee Project 4 Employee Project © Ellis Cohen 2001 -2008 26

Answer: Size of M: N Joins If there are 200 tuples in Emps 5 tuples in Projs How many tuples (min and max) could there be in the NATURAL JOIN of Emps and Asns and Projs? 1 2 Employee Project Employee 200. . 1000 3 Project 5. . 1000 4 Employee Project Employee 200. . 1000 Project 0. . 1000 © Ellis Cohen 2001 -2008 27

Bridge Entity Classes © Ellis Cohen 2001 -2008 28

M: N Relationships as Entity Classes Sometimes an M: N relationship is thought of at the conceptual level as an entity class in its own right: An Assignment can be thought as an entity class representing an assignment of an employee to a project An employee may be associated with a number of assignments each one assigning that employee to a project A project may be associated with a number of assignments each one assigning an employee to that project © Ellis Cohen 2001 -2008 29

Bridge Entity Classes for M: N Relationships Visual Conceptual Model (Crow Magnum) Employee assigned via Assignment assigned via empno ename Project pno pname budget Visual Relational Model (Relational Schema) Emps empno ename Asns empno Note, no primary key and no cascading delete © Ellis Cohen 2001 -2008 Projects pno pname budget 30

Bridge Classes vs M: N Emps empno ename Asns empno Projects pno pname budget No primary key No mandatory participation (in an assignment, empno or pno can be null) No uniqueness constraint (can have multiple assignments with same empno & pno) No unique identification of assignments No cascading delete When an employee or project is deleted, its assignments are not automatically deleted © Ellis Cohen 2001 -2008 31

Uses of Bridge Entity Classes M: N Relationships are simpler and should be used whenever possible, but Bridge Entity Classes allow flexibility w. r. t. • Relationship attributes • Selective control of entity identity, mandatory participation and cascading delete © Ellis Cohen 2001 -2008 32

Uniquely Identifying Bridge Entities Using bridge classes provides choices in uniquely identifying bridge entities (e. g. assignments) – no unique identification (i. e. no primary key) – add new primary key attribute – use weak identity (e. g. number the assignments associated with each project) – make empno+pno unique / primary key © Ellis Cohen 2001 -2008 33

Mapping Bridge Entity Classes with Primary Keys Visual Conceptual Model (Crow Magnum) Employee assigned via Assignment empno ename assigned via Project pno pname budget asnid Visual Relational Model (Relational Schema) Emps empno ename Asns asnid empno ! © Ellis Cohen 2001 -2008 Projs pno pname budget 34

Bridge Class Attributes © Ellis Cohen 2001 -2008 35

Relationship Attributes Employee assigned to Project empno Suppose that when we assign an employee to a project, we want to indicate the # hrs per week they are supposed to work on the project. Can we associate that with either the Employee or the Project? © Ellis Cohen 2001 -2008 36

Bridge Classes With Attributes Employee assigned via empno ename Assignment assigned via asnid hrs. Per. Week Project pno pname budget Relationship Attribute: How many hrs per week is the employee assigned to the project © Ellis Cohen 2001 -2008 37

Mapping Bridge Entity Classes with Attributes Visual Conceptual Model (Crow Magnum) Employee assigned via Assignment empno ename assigned via Project pno pname budget asnid hrs. Per. Week Visual Relational Model (Relational Schema) Emps empno ename Asns asnid empno ! hrs. Per. Week © Ellis Cohen 2001 -2008 Projs pno pname budget 38

Attributes Example Emps Asns empno … asnid empno hrs. Per. Week 7499 . . . 1 7499 2621 12 7654 … 2 7698 2618 5 7698 … 3 7698 2622 7839 … 4 7844 2618 7844 … 5 7844 7986 … 6 7986 Projs pno … 2618 … 22 2621 … 2622 4 2622 … 2622 1 © Ellis Cohen 2001 -2008 39

Partial Associations © Ellis Cohen 2001 -2008 40

M: N Related Instances assigned to Employee Project empno ename pno address 7499 ALLEN . . . 7654 MARTIN . . . 7698 BLAKE . . . 7839 KING . . . 7844 TURNER . . . 7986 STERN . . . Links that don't connect to an entity instance are not allowed! 2621 … 2622 … © Ellis Cohen 2001 -2008 41

Standard M: N Assignments Visual Conceptual Model (Crow Magnum) Employee assigned via Assignment empno ename assigned via Project pno pname budget asnid Every assignment must have both an employee and a project Visual Relational Model (Relational Schema) Emps empno ename Asns asnid empno ! © Ellis Cohen 2001 -2008 Projects pno pname budget 42

Partial Assignments Visual Conceptual Model (Crow Magnum) Employee assigned via Assignment assigned via asnid job empno ename Bridge classes allow partial assignments: An assignment can be specified for a project, without having an employee to fill it. Project pno pname budget Visual Relational Model (Relational Schema) Emps empno ename Asns asnid empno ! job © Ellis Cohen 2001 -2008 Projects pno pname budget 43

Partial Association Example Asns asnid pno empno job 1 2613 7839 MGR 2 2618 7698 MGR 3 2618 7499 DEV 4 2622 7844 MGR 5 2622 DEV 6 2622 DEV Project 2622 needs two developers, but no employee has been assigned to those positions yet © Ellis Cohen 2001 -2008 44

Using Dependent & Identifying Relationships © Ellis Cohen 2001 -2008 45

Specifying Lifetime Dependency Visual Conceptual Model (Crow Magnum) Employee assigned via Assignment assigned via Project asnid job empno ename pno pname budget With bridge classes, lifetime dependency can be selectively added Visual Relational Model (Relational Schema) Emps empno ename Asns asnid empno ! job © Ellis Cohen 2001 -2008 Projs pno pname budget 46

Partially Dependent Bridge Class Visual Conceptual Model (Crow Magnum) Employee assigned via empno ename Assignment assigned via Project asnid job When the project is deleted, all assignment for the projects (including partial ones) are deleted pno pname budget Textual Conceptual Model (Brief Con. Text) [Employee, Project] Assignment( asnid, job ) (1) Employee assigned via (*) Assignment (1) Project assigned via (*) Assignment © Ellis Cohen 2001 -2008 47

Using an Identifying Relationship Visual Conceptual Model (Crow Magnum) Employee assigned via empno ename Assignment assigned via asndx job Project pno pname budget Textual Conceptual Model (Brief Con. Text) [Project] Assignment( asndx, job ) (1) Employee assigned via (*) Assignment (1) Project assigned via (*) Assignment What's the relational schema diagram? © Ellis Cohen 2001 -2008 48

Mapping the Identifying Relationship Visual Conceptual Model (Crow Magnum) Employee assigned via Assignment empno ename assigned via Project pno pname budget asndx job Visual Relational Model (Relational Schema) Asns Emps empno ename pno asndx empno job © Ellis Cohen 2001 -2008 Projs pno pname budget 49

Uniqueness © Ellis Cohen 2001 -2008 50

Uniqueness Issue Employee assigned via empno ename Assignment assigned via asnid job hrs. Per. Week Project pno pname budget Could an employee have more than one assignment for the same project? © Ellis Cohen 2001 -2008 51

Multiple Assignments Example Asns asnid pno empno hrs. Per. Week job 1 2613 7839 20 MGR 2 2618 7698 40 MGR 3 2618 7499 40 DEV 4 2622 7844 40 MGR 5 2622 7316 30 DEV 6 2622 7316 10 LIB Employee 7316 works • 30 hrs per week on proj 2622 as a developer • 10 hrs per week on proj 2622 as a librarian Is there a uniqueness constraint? © Ellis Cohen 2001 -2008 52

Add Uniqueness Constraint Visual Conceptual Model (Crow Magnum) Employee assigned via Assignment empno ename assigned via Project pno pname budget asnid job hrs. Per. Week + There is at most one assignment for the same employee, project and job Visual Relational Model (Relational Schema) Emps empno ename Asns asnid empno ! job hrs. Per. Week © Ellis Cohen 2001 -2008 Projects pno pname budget 53

Uniqueness Constraint in SQL Emps empno ename Asns asnid empno ! job hrs. Per. Week Projects pno pname budget CREATE TABLE Asns( asnid number(8) primary key, empno number(5) not null references Emps, pno number(6) not null references Projs, job varchar(10), hrs. Per. Week number(2), unique( empno, job ) ); © Ellis Cohen 2001 -2008 54

Stronger Uniqueness Constraint Visual Conceptual Model (Crow Magnum) Employee assigned via empno ename Assignment assigned via pno pname budget asnid job hrs. Per. Week + There is at most one assignment for the same employee and project Visual Relational Model (Relational Schema) Asns Emps empno ename Project asnid empno ! job hrs. Per. Week © Ellis Cohen 2001 -2008 Doesn’t allow a person to have two jobs on the same project Projects pno pname budget 55

Eliminate Primary Key Visual Conceptual Model (Crow Magnum) Employee assigned via empno ename Assignment No need for PK assigned via Project pno pname budget hrs. Per. Week job + There is at most one assignment for the same employee and project Visual Relational Model (Relational Schema) Emps empno ename Asns empno ! job hrs. Per. Week © Ellis Cohen 2001 -2008 Projects pno pname budget 56

Upgrade to Relational PK Visual Conceptual Model (Crow Magnum) Employee assigned via empno ename Assignment No need for PK assigned via Project pno pname budget hrs. Per. Week job + There is at most one assignment for the same employee and project Visual Relational Model (Relational Schema) Emps empno ename Asns empno job hrs. Per. Week Projects pno pname budget Since empno + pno are unique and non-null, and there is no primary key, we make empno + pno the primary key during relational mapping © Ellis Cohen 2001 -2008 57

M: N Relationships & Bridge Classes Emp Asn Proj pno empno Emp empno Asn Projs pno Proj + There is at most one assignment for the same employee and project Emps empno ! pno empno Emp Asns assigned to Asns empno Projs pno Proj pno + An employee assigned to a project can't be deleted & vice versa © Ellis Cohen 2001 -2008 58

M: N Relationships & Bridge Classes w Dependency Emps Emp Asn empno Emp Proj Asn Projs pno Proj + There is at most one assignment for the same employee and project Emps empno ! pno empno Emp Asns assigned to Asns empno Projs pno Proj pno + An employee assigned to a project can't be deleted © Ellis Cohen 2001 -2008 59

Weak Bridge Classes © Ellis Cohen 2001 -2008 60

Weak Project Assignments Suppose we only had an Employee class & an Assignments class (but no Project class) We might represent their relationship as: Employee assigned via Assignment empno ename pno An employee has many project assignments. An employee's project assignments are discriminated by the project number Emps empno ename Asns empno © Ellis Cohen 2001 -2008 61

Weak Employee Assignments Suppose we only had an Project class & an Assignments class (but no Employee class) We might represent their relationship as: assigned via Assignment Project empno pname budget A project has many employee assignments. A project's employee assignments are discriminated by the employee number Asns empno Projs pno pname budget © Ellis Cohen 2001 -2008 62

Weak Bridge Entity Classes A Weak Bridge Entity Class is a Weak Entity Class that has multiple identifying relationships Employee empno ename assigned via Assignment assigned via Project pno pname budget Assignment does not need an instance discriminator. The combination of the identities of its two identifying owners uniquely identifies an assignment Visual Relational Model (Relational Schema) Emps empno ename Asns empno Relational "fingerprint" of a Weak Bridge Entity Class (or an M: N relationship) © Ellis Cohen 2001 -2008 Projs pno pname budget 63

Weak Bridge Entity Classes and M: N Relationships assigned via Employee Assignment assigned via empno ename Project pno pname budget Identical Employee assigned to empno ename Project pno pname budget © Ellis Cohen 2001 -2008 64

M: N Relationships & Weak & Dependent Bridge Classes Emp Asn Proj pno empno + There is at most one assignment for the same employee and project Emps Emp empno Asn Proj empno Asns empno Projs pno Proj pno © Ellis Cohen 2001 -2008 65

Composite Primary Keys with Partial Dependency Emp Asn Proj pno empno + There is at most one assignment for the same employee and project Emp Asn Proj pno empno Emps empno + An employee with assignments cannot be deleted Emp empno assigned to Asns empno Projs pno Proj pno + An employee assigned to a project can't be deleted © Ellis Cohen 2001 -2008 66

Weak Bridge Classes with Attributes © Ellis Cohen 2001 -2008 67

Mapping Bridge Entity Classes with Attributes Visual Conceptual Model (Crow Magnum) Employee assigned via Assignment empno ename assigned via Project pno pname budget hrs. Per. Week Visual Relational Model (Relational Schema) Emps empno ename Asns empno hrs. Per. Week © Ellis Cohen 2001 -2008 Projs pno pname budget 68

Attribute Example Emps Asns empno ename address empno hrs. Per. Week 7499 ALLEN . . . 7654 2621 12 7654 MARTIN … 7698 2618 5 7698 BLAKE … 7698 2622 7839 KING … 7844 2618 22 2618 … 7844 TURNER … 7844 2622 4 2621 … 7986 STERN … 7986 2622 1 2622 … Projs pno © Ellis Cohen 2001 -2008 69 …

Weak Bridge Entity Class Models Visual Conceptual Model (Crow Magnum) Employee assigned via empno ename Assignment assigned via hrs. Per. Week just an ordinary class attribute Project pno pname budget Textual Conceptual Model (Brief Con. Text) Entity Classes Employee( empno, ename ) Project( pno, pname, budget ) [Employee, Project] Assignment( hrs. Per. Week ) Relationships Emp. Assign: (1) Employee assigned via (*) Assignment Proj. Assign: (1) Project assigned via (*) Assignment © Ellis Cohen 2001 -2008 70

M: N Relationships with Attributes Crow Magnum Employee assigned via Assignment assigned via Project hrs. Per. Week Easy Crow Magnum UML Employee assigned via Assignment assigned via Project hrs. Per. Week Employee * * Project Assignment hrs. Per. Week Chen Employee assigned to © Ellis Cohen 2001 -2008 Association Class Project 71

No Dependent Bridge Classes in UML * Employee PK empno Assignment * Project hrs. Per. Week PK pno Dependent bridge entity classes cannot be used in UML. Composition implies containment in UML, and it's not possible for Assignment to be contained in both the Employee and the Project classes UML Employee PK * * empno Assignment Association Class Project PK pno hrs. Per. Week © Ellis Cohen 2001 -2008 72

UML Qualifying Relationships Employee PK pno 0. . 1 empno assigned via 1 Assignment assigned via Project PK empno 1 hrs. Per. Week 0. . 1 pno Bridge classes could potentially be modeled in UML just using qualifying relationships, which captures weak identity, but not lifetime dependency. BUT: Use Association Classes instead © Ellis Cohen 2001 -2008 73

Weak Bridge Classes with Participation and Cardinality © Ellis Cohen 2001 -2008 74

Bridge Entity Classes & M: N Participation assigned to Employee Project empno Identical Employee assigned via Assignment assigned via Project empno © Ellis Cohen 2001 -2008 75

Assertions for Mandatory Participation Employee assigned to Project empno Every project has at least one employee Every project has at least one assignment Emps empno ename Asns empno Projs pno pname budget (SELECT count(*) FROM Projs) = (SELECT count(DISTINCT pno) FROM Asns) © Ellis Cohen 2001 -2008 76

Bridge Entity Classes & M: N Cardinality 3. . 20 Employee 0. . 4 Project assigned to empno Essentially Identical Employee assigned via empno 0. . 4 Assignment assigned via 3. . 20 © Ellis Cohen 2001 -2008 Project pno 77

M: N Related Relationships A laboratory has a number of pieces of very expensive equipment. A database application keeps track of which researchers are permitted to use which piece of equipment. Also, each piece of equipment has a single manager – a researcher who is responsible for that piece of equipment (and who, of course, is permitted to use it) What's the best conceptual model? © Ellis Cohen 2001 -2008 78

Modeling 1: M/M: N Related Relationships manages Researcher Why? Equipment permitted to use Which conceptual state constraint is also required? © Ellis Cohen 2001 -2008 79

1: M/M: N w State Constraint + State Constraint: If a researcher manages a piece of equipment, then that researcher must also be permitted to use it manages Mandatory participation implied by state constraint Researcher Equipment permitted to use What's the corresponding relational model? © Ellis Cohen 2001 -2008 80

1: M/M: N Relational Models Researchers rschid rname Permissions rschid eqpid Equipment eqpid eqpnam mgr ! If a researcher manages a piece of equipment, then that researcher must also be permitted to use it (SELECT count(*) FROM Equipment) = (SELECT count(*) FROM (Equipment e JOIN Permissions p ON e. eqpid = p. eqpid AND rschid = mgr)) © Ellis Cohen 2001 -2008 81

Bridge Class Representation manages Researcher Equipment Permission Is it possible to eliminate the manages relationship? © Ellis Cohen 2001 -2008 82

Using Boolean Attributes Researcher permitted via Permission permitted via Equipment is. Manager could generalize this to access. Level Which conceptual state constraint is also required? © Ellis Cohen 2001 -2008 83

Required Constraint + State Constraint: Every piece of equipment is managed by a single researcher Researcher permitted via Permission permitted via Equipment is. Manager What's the corresponding relational model? © Ellis Cohen 2001 -2008 84

1: M/M: N Related Relational Models Researchers rschid rname Permissions rschid eqpid is. Manager Equipment eqpid eqpnam Every piece of equipment is managed by a single researcher (SELECT count(rschid) FROM (Permissions p RIGHT JOIN Equipment e ON p. eqpid = e. eqpid AND p. is. Manager = 'T')) ALL = 1 © Ellis Cohen 2001 -2008 85

Using Inclusion Constraints Researchers rschid rname Permissions eqpid rschid If a researcher manages a piece of equipment, then that researcher must also be permitted to use it Researchers rschid rname Equipment eqpid eqpnam mgr ! Can be represented directly in the relational model! Permissions eqpid rschid © Ellis Cohen 2001 -2008 Equipment eqpid eqpnam mgr ! 86

Discriminated Weak Bridge Classes © Ellis Cohen 2001 -2008 87

Discriminated Weak Bridge Classes Visual Conceptual Model (Crow Magnum) Employee empno ename for Assigtnment with job hrs. Per. Week Project pno pname budget An employee may have multiple assignments to the same project These are discriminated by job Textual Conceptual Model (Brief Con. Text) [Employee, Project] Assignment( job, hrs. Per. Week ) © Ellis Cohen 2001 -2008 88

Mapping Discriminated Weak Bridge Entity Classes Visual Conceptual Model (Crow Magnum) Employee for Assigtnment empno ename with job hrs. Per. Week Project pno pname budget Visual Relational Model (Relational Schema) Emps empno ename Asns empno job hrs. Per. Week © Ellis Cohen 2001 -2008 Projs pno pname budget 89

SQL for Discriminated Weak Bridge Entity Class Emps empno ename Asns empno job hrs. Per. Week Projs pno pname budget CREATE TABLE Asns( empno number(5) not null references Emps, pno number(6) not null references Projs, job varchar(10), hrs. Per. Week number(2), primary key( empno, job ) ); © Ellis Cohen 2001 -2008 90

Add Surrogate Primary Key Visual Conceptual Model (Crow Magnum) Employee assigned via Assignment empno ename assigned via Project pno pname budget asnid job hrs. Per. Week + There is at most one assignment for the same employee, project and job Add surrogate PK add uniqueness constraint Visual Relational Model (Relational Schema) Emps empno ename Asns asnid empno ! job hrs. Per. Week © Ellis Cohen 2001 -2008 Projects pno pname budget 91

Discriminated Bridge Classes Emp empno Asn job Proj pno + There is at most one assignment for the same employee, project & job Emps empno Emp empno Asn job Asns empno job Projs pno Proj pno © Ellis Cohen 2001 -2008 92

Discriminated Bridge Classes with Partial Dependency Emp empno Asn job Proj pno + There is at most one assignment for the same employee, project & job Emps empno Emp empno Asn job Asns empno ! pno job Projs pno Proj pno + An employee with assignments cannot be deleted © Ellis Cohen 2001 -2008 93

Relationships with Bridge Entity Classes © Ellis Cohen 2001 -2008 94

1: M Relationship to a Weak Bridge Entity Class Suppose that when a contractor works on a project, they work through a single agency. Any particular agency may arrange any number of assignments. Contractor cno … Assignment hrs. Per. Week Agency agencyid © Ellis Cohen 2001 -2008 Project pno … What's the relational schema? 95

Mapping 1: M Relationship to Bridge Visual Conceptual Model (Crow Magnum) Contractor Assignment Project pno … hrs. Per. Week cno … Agency agencyid Visual Relational Model (Relational Schema) Asns Agencies agencyid … cno pno hrs. Per. Week agencyid Contrs cno … Projs pno … © Ellis Cohen 2001 -2008 96

SQL for 1: M Relationship to Bridge Asns Agencies agencyid … cno pno hrs. Per. Week agencyid Contrs cno … Projs pno … CREATE TABLE Asns( cno number(5) not null references Contrs on delete cascade, pno number(6) not null references Projs on delete cascade, hrs. Per. Week number(2), agencyid number(5) references Agencies, primary key( cno, pno ) ); © Ellis Cohen 2001 -2008 97

UML Bridge Relationship UML Contractor PK * * Project PK cno pno Assignment hrs. Per. Week * Agency PK agencyid © Ellis Cohen 2001 -2008 98

1: M Relationship from a Weak Bridge Entity Class When a contractor works on a project, they submit multiple bills. Each bill is for only one assignment. Contractor Assignment hrs. Per. Week cno … Bill billid billdate © Ellis Cohen 2001 -2008 Project pno … What's the relational schema? 99

Mapping 1: M Relationship from Bridge Visual Conceptual Model (Crow Magnum) Project Assignment Contractor Bill billid billdate Visual Relational Model (Relational Schema) If an assignment can’t be deleted if it has any outstanding bills, what happens when an attempt is made to delete a contractor? Asns Bills billid billdate cno ! pno … hrs. Per. Week cno … cno pno hrs. Per. Week Contrs cno … Projs pno … © Ellis Cohen 2001 -2008 100

SQL for 1: M Relationship from Bridge Asns Bills billid billdate cno ! pno ! cno pno hrs. Per. Week Contrs cno … Projs pno … CREATE TABLE Bills( billid number(8) primary key, billdate, cno number(5) not null, pno number(6) not null, foreign key( cno, pno ) references Asns ); © Ellis Cohen 2001 -2008 101

Possible Representation? Contractor Assignment Project pno … hrs. Per. Week cno … Bill billid billdate Would this relational model work instead? Contrs Asns Bills billid billdate cno ! pno ! cno pno hrs. Per. Week cno … Projs pno … © Ellis Cohen 2001 -2008 102

Incorrect Representation Contractor Assignment Project pno … hrs. Per. Week cno … Bill billid billdate It ensures that the cno and the pno on the bill are both correct, but doesn't guarantee that the contractor actually worked on the project! Asns Bills billid billdate cno ! pno ! cno pno hrs. Per. Week Contrs cno … Projs pno … © Ellis Cohen 2001 -2008 103

M: N Relationship with a Weak Bridge Entity Class This system keeps track of which tools were used during an assignment (when a contractor worked on a project) Contractor Assignment hrs. Per. Week cno … Project pno … used What's the relational schema? Tool toolid © Ellis Cohen 2001 -2008 104

Mapping M: N Relationship from Bridge Visual Conceptual Model (Crow Magnum) Contractor cno … Project Assignment pno … hrs. Per. Week uses Tool toolid Visual Relational Model (Relational Schema) Uses cno pno toolid Tools toolid … Asns Contrs cno … cno pno hrs. Per. Week This represents a 3 way combination of contractors, projects & tools Projs * © Ellis Cohen 2001 -2008 pno … 105

SQL for M: N Relationship from Bridge Uses cno pno toolid Asns cno pno hrs. Per. Week Contrs cno … Projs Tools pno … toolid … CREATE TABLE Uses( cno number(5), pno number(6), toolid number(5) references Tools on delete cascade, primary key( cno, pno, toolid ), foreign key( cno, pno ) references Asns on delete cascade ); © Ellis Cohen 2001 -2008 106

Simplifying Requirements Contractor Assignment hrs. Per. Week cno … Project pno … used Tool toolid Suppose we didn't need to keep track of how many hours each contractor worked on each project, but just wanted to know which tools each contractor use on each project © Ellis Cohen 2001 -2008 107

3 -Way Weak Bridge Relationship Visual Conceptual Model (Crow Magnum) Contractor Uses Project pno … cno … Tool toolid Visual Relational Model (Relational Schema) Uses Tools toolid … cno pno toolid Contrs cno … Projs pno … © Ellis Cohen 2001 -2008 108

SQL for 3 -Way Weak Bridge Relationship Contrs Uses Tools toolid … cno pno toolid cno … Projs pno … CREATE TABLE Uses( cno number(5) references Contrs on delete cascade, pno number(6) references Projs on delete cascade, toolid number(5) references Tools on delete cascade, primary key( cno, pno, toolid ); © Ellis Cohen 2001 -2008 109