Outline Conceptual design using ER diagram Mapping
CP
Published · 62 slides · 0 views
1 / 1
Description
Outline Conceptual design using ER diagram Mapping ER diagram to relational tables The Entity-Relationship Model Entity Set: Students Entity Set: Classes Relationships: Students enroll in classes Overview of Database Design Conceptual
Related Topics
Share
Embed code
Download this presentation From Below
"Outline Conceptual design using ER diagram Mapping" is the property of its rightful owner. Permission is granted to download and print the materials on this website for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Presentation Transcript
01
Outline Conceptual design using ER diagram
Mapping ER diagram to relational tables<br>
Mapping ER diagram to relational tables<br>
02
The Entity-Relationship Model Entity Set: Students Entity Set: Classes Relationships: Students enroll in classes<br>
03
Overview of Database Design Conceptual design: (ER Model is used at this stage.)
What are the entities and relationships in the enterprise?
Students sign up for classes Entity Entity Relationship<br>
What are the entities and relationships in the enterprise?
Students sign up for classes Entity Entity Relationship<br>
04
Entity & Entity Set Entity: Real-world object distinguishable from other objects, e.g., a particular employee. An entity is described (in database) using a set of attributes.
Entity Set: A collection of similar entities, e.g., all employees.
All entities in an entity set have the same set of attributes (until we consider ISA hierarchies, anyway!) ssn name lot Entity set Attribute Key Each attribute has a domain.
Each entity set has a key – uniquely identifies an entity in the set<br>
Entity Set: A collection of similar entities, e.g., all employees.
All entities in an entity set have the same set of attributes (until we consider ISA hierarchies, anyway!) ssn name lot Entity set Attribute Key Each attribute has a domain.
Each entity set has a key – uniquely identifies an entity in the set<br>
05
Relationship Relationship: Association among two or more entities, e.g., “Jessica works in Pharmacy department.” A relationship<br>
06
A Ternary Relationship Example Each department has offices in several locations
We want to record the locations at which each employee works budget dname Departments Works_In did since Locations E1 works at two locations D1 L1 D2 L2 Ei<br>
We want to record the locations at which each employee works budget dname Departments Works_In did since Locations E1 works at two locations D1 L1 D2 L2 Ei<br>
07
Key Constraints: 1-to-Many Consider Works_In: An employee can work in many departments; a dept can have many employees (many-to-many relationship).
In contrast, each dept has at most one manager, according to the key constraint on Manages (1-to-many relationship). many-to-many 1-to-many<br>
In contrast, each dept has at most one manager, according to the key constraint on Manages (1-to-many relationship). many-to-many 1-to-many<br>
08
Key Constraints Key Constraint: Given a Departments entity, we can uniquely determine the Manages relationship in which it appears 1 Many D1 D2 E5<br>
09
Different Kinds of Relationships<br>
10
Total participation
(a participation constraint) Participation Constraints Does every department have a manager?
If so, this is a participation constraint
the participation of Departments in Manages is said to be total (vs. partial).
Every did value in Departments table must appear in a row of the Manages table (with a non-null ssn value! - more details later) Partial participation<br>
(a participation constraint) Participation Constraints Does every department have a manager?
If so, this is a participation constraint
the participation of Departments in Manages is said to be total (vs. partial).
Every did value in Departments table must appear in a row of the Manages table (with a non-null ssn value! - more details later) Partial participation<br>
11
Key constraint
+
total participation Participation Constraints Does every department have a manager?
If so, this is a participation constraint
the participation of Departments in Manages is said to be total (vs. partial).
Every did value in Departments table must appear in a row of the Manages table (with a non-null ssn value! - more details later in this course)<br>
+
total participation Participation Constraints Does every department have a manager?
If so, this is a participation constraint
the participation of Departments in Manages is said to be total (vs. partial).
Every did value in Departments table must appear in a row of the Manages table (with a non-null ssn value! - more details later in this course)<br>
12
Total vs. Partial Participation Does every department have a manager ? Manages Employees Departments Manages Employees Departments Has no manager Every department has a manager Both have key constraint<br>
13
Weak Entities A weak entity can be identified uniquely only by considering the primary key of another entity (owner). lot name age dpname Dependents Employees ssn Policy cost A weak entity set The owner entity set Partial key
Some children might have the same name Draw both with dark lines to indicate that Policy is the identifying relationship Need both ssn and dpname to
identify a “Dependents” entity Weak entity cannot exist without entity with which it has a relationship<br>
Some children might have the same name Draw both with dark lines to indicate that Policy is the identifying relationship Need both ssn and dpname to
identify a “Dependents” entity Weak entity cannot exist without entity with which it has a relationship<br>
14
Weak Entities A weak entity can be identified uniquely only by considering the primary key of another (owner) entity.
Owner entity set and weak entity set must participate in a one-to-many relationship set (one owner, many weak entities).
Weak entity set must have total participation in this identifying relationship set. lot name age dpname Dependents Employees ssn Policy cost A weak entity set The owner entity set Partial key 1-to-many relationship
Total participation<br>
Owner entity set and weak entity set must participate in a one-to-many relationship set (one owner, many weak entities).
Weak entity set must have total participation in this identifying relationship set. lot name age dpname Dependents Employees ssn Policy cost A weak entity set The owner entity set Partial key 1-to-many relationship
Total participation<br>
15
ISA (`is a’) Hierarchies Contract_Emps name ssn Employees lot hourly_wages ISA Hourly_Emps contractid hours_worked As in C++, or other PLs, attributes are inherited.
If we declare A ISA B, every A entity is also considered to be a B entity. Each “Hourly_Emps” employee has a “name”<br>
If we declare A ISA B, every A entity is also considered to be a B entity. Each “Hourly_Emps” employee has a “name”<br>
16
Reasons for Using ISA name ssn Employees lot hourly_wages ISA Hourly_Emps hours_worked To identify entities that participate in a relationship
Example: Only contract employees can be managers (i.e., participating in the Manages relationship) To add descriptive attributes specific to a subclass
Example: We only need to track hours worked for hourly employees<br>
Example: Only contract employees can be managers (i.e., participating in the Manages relationship) To add descriptive attributes specific to a subclass
Example: We only need to track hours worked for hourly employees<br>
17
Aggregation Used when we have to model a relationship involving (entity sets and) a relationship set. Aggregation vs. ternary relationship:
Monitors is a distinct relationship, with a descriptive attribute.
Also, can say that each sponsorship is monitored by at most one employee. budget did pid started_on pbudget dname Departments Projects Sponsors Monitors lot name ssn since Aggregation allows us to treat the relationship set “Sponsors” as an entity set for purpose of participation in the “Monitors” relationships<br>
Monitors is a distinct relationship, with a descriptive attribute.
Also, can say that each sponsorship is monitored by at most one employee. budget did pid started_on pbudget dname Departments Projects Sponsors Monitors lot name ssn since Aggregation allows us to treat the relationship set “Sponsors” as an entity set for purpose of participation in the “Monitors” relationships<br>
18
Exercise 11/11/2020 18<br>
19
Example: University Database We have four entity sets: Professor, Project, Graduate, Department Professor<br>
20
Example: University Database Each project is managed by one professor (principal investigator)
Professors can manage multiple projects Professor Dept Graduate Project grad<br>
Professors can manage multiple projects Professor Dept Graduate Project grad<br>
21
Example: University Database Each project is worked on by one or more professors (co-investigators)
Professors can work on multiple projects Professor Dept Graduate Project<br>
Professors can work on multiple projects Professor Dept Graduate Project<br>
22
Example: University Database Each project is worked on by one or more graduate students
Graduate students can work on multiple projects Professor Dept Graduate Project<br>
Graduate students can work on multiple projects Professor Dept Graduate Project<br>
23
Example: University Database When graduate students work on multiple projects, they must have a supervisor (professor) for each one. Professor Dept Graduate Project<br>
24
Example: University Database Departments have a professor who runs the department Professor Dept Graduate Project<br>
25
Example: University Database Professors work in one or more departments
For each department a professor works in, a time percentage is associated with their job Professor Dept Graduate Project<br>
For each department a professor works in, a time percentage is associated with their job Professor Dept Graduate Project<br>
26
Example: University Database Graduate students have one major department in which they are working on their degree Professor Dept Graduate Project<br>
27
Example: University Database Each graduate student has another, more senior graduate student who advises him or her on what courses to take Professor Dept Graduate Project<br>
28
Example: University Database Professor Dept Graduate Project senior grad<br>
29
Other Notations Also Used Various ways of representing 1-to-many relationship Use Chen notations for this class<br>
30
ER to Relational Mapping 11/11/2020 30<br>
31
The Relational Model Students 11/11/2020 31<br>
32
Logical DB Design: ER to Relational Entity sets to tables: CREATE TABLE Employees
(ssn CHAR(11),
name CHAR(20),
lot INTEGER,
PRIMARY KEY (ssn)) Employees<br>
(ssn CHAR(11),
name CHAR(20),
lot INTEGER,
PRIMARY KEY (ssn)) Employees<br>
33
Relationship Sets to Tables CREATE TABLE Works_In(
ssn CHAR(11),
did INTEGER,
since DATE,
PRIMARY KEY (ssn, did),
FOREIGN KEY (ssn) REFERENCES Employees,
FOREIGN KEY (did) REFERENCES Departments) Works_In<br>
ssn CHAR(11),
did INTEGER,
since DATE,
PRIMARY KEY (ssn, did),
FOREIGN KEY (ssn) REFERENCES Employees,
FOREIGN KEY (did) REFERENCES Departments) Works_In<br>
34
Key Constraint Mapping Manages ssn did since Option 1: Map relationship to a table:
Separate tables for Employees and Departments. Note that did is the key now – Each department can have only one manager<br>
Separate tables for Employees and Departments. Note that did is the key now – Each department can have only one manager<br>
35
Constraint Mapping in SQL Manages ssn did since CREATE TABLE Manages(
ssn CHAR(11),
did INTEGER,
since DATE,
PRIMARY KEY (did),
FOREIGN KEY (ssn) REFERENCES Employees,
FOREIGN KEY (did) REFERENCES Departments) ssn did since<br>
ssn CHAR(11),
did INTEGER,
since DATE,
PRIMARY KEY (did),
FOREIGN KEY (ssn) REFERENCES Employees,
FOREIGN KEY (did) REFERENCES Departments) ssn did since<br>
36
Translating ER Diagrams with Key Constraints Option 2: Since each department has a unique manager, we could instead combine Manages and Departments. Dept_Mgr Manages ssn did since Department dname did budget<br>
37
Translating ER Diagrams with Key Constraints Option 2: Since each department has a unique manager, we could instead combine Manages and Departments. Dept_Mgr CREATE TABLE Dept_Mgr(
did INTEGER,
dname CHAR(20),
budget REAL,
ssn CHAR(11),
since DATE,
PRIMARY KEY (did),
FOREIGN KEY (ssn) REFERENCES Employees)<br>
did INTEGER,
dname CHAR(20),
budget REAL,
ssn CHAR(11),
since DATE,
PRIMARY KEY (did),
FOREIGN KEY (ssn) REFERENCES Employees)<br>
38
Translating ER Diagrams with Key Constraints Option 2: Since each department has a unique manager, we could instead combine Manages and Departments. Can also use one table for both “Manages” and “Departments”
Folding them into one table Dept_Mgr<br>
Folding them into one table Dept_Mgr<br>
39
Participation Constraints in SQL CREATE TABLE Dept_Mgr(
did INTEGER,
dname CHAR(20),
budget REAL,
ssn CHAR(11) NOT NULL,
since DATE,
PRIMARY KEY (did),
FOREIGN KEY (ssn) REFERENCES Employees,
ON DELETE NO ACTION) Total participation
i.e., Every department must have a manager 11/11/2020 39<br>
did INTEGER,
dname CHAR(20),
budget REAL,
ssn CHAR(11) NOT NULL,
since DATE,
PRIMARY KEY (did),
FOREIGN KEY (ssn) REFERENCES Employees,
ON DELETE NO ACTION) Total participation
i.e., Every department must have a manager 11/11/2020 39<br>
40
Translating Weak Entity Sets CREATE TABLE Dep_Policy (
pname CHAR(20),
age INTEGER,
cost REAL,
ssn CHAR(11) NOT NULL,
PRIMARY KEY (pname, ssn),
FOREIGN KEY (ssn) REFERENCES Employees,
ON DELETE CASCADE) Weak entity set lot name age pname Dependents Employees ssn Policy cost Map into
one table ssn is part of a primary key
→ Implicit NOT NULL When the owner entity is deleted, all owned weak entities must also be deleted 11/11/2020 40<br>
pname CHAR(20),
age INTEGER,
cost REAL,
ssn CHAR(11) NOT NULL,
PRIMARY KEY (pname, ssn),
FOREIGN KEY (ssn) REFERENCES Employees,
ON DELETE CASCADE) Weak entity set lot name age pname Dependents Employees ssn Policy cost Map into
one table ssn is part of a primary key
→ Implicit NOT NULL When the owner entity is deleted, all owned weak entities must also be deleted 11/11/2020 40<br>
41
Mapping ISA to Relations Two approaches:
Using three tables
Using two tables<br>
Using three tables
Using two tables<br>
42
ISA: Mapping Using Three Relations Employees Contract_Emps Hourly_Emps must delete Hourly_Emps tuple if referenced Employees tuple is deleted. Every employee is recorded in Employees For hourly employees, extra information (i.e., Hourly_Wages and Hours_worked) recorded in Hourly_Emps<br>
43
ISA: Mapping Using Three Relations Employees Contract_Emps Hourly_Emps Queries involving all employees easy,
e.g., Find SSN of all Smiths
those involving just Hourly_Emps require a join to get some attributes (e.g., name)
e.g., Find Hourly_wages of all Smiths More detail on “join” later<br>
e.g., Find SSN of all Smiths
those involving just Hourly_Emps require a join to get some attributes (e.g., name)
e.g., Find Hourly_wages of all Smiths More detail on “join” later<br>
44
ISA: Mapping Using Two Relations Hourly_Emps Contract_Emps Constraint: Each employee must be in one of these two relations 11/11/2020 44<br>
45
CREATE TABLE Dependents (
pname CHAR(20),
age INTEGER,
policyid INTEGER,
PRIMARY KEY (pname, policyid),
FOREIGN KEY (policyid) REFERENCES Policies, ON DELETE CASCADE) CREATE TABLE Policies (
policyid INTEGER,
cost REAL,
ssn CHAR(11) NOT NULL,
PRIMARY KEY (policyid).
FOREIGN KEY (ssn) REFERENCES Employees,
ON DELETE CASCADE) Mapping Two Binary Relationships (1) The key constraints allow us to combine Purchaser with Policies, and Beneficiary and Dependents. age pname policyid cost 11/11/2020 45<br>
pname CHAR(20),
age INTEGER,
policyid INTEGER,
PRIMARY KEY (pname, policyid),
FOREIGN KEY (policyid) REFERENCES Policies, ON DELETE CASCADE) CREATE TABLE Policies (
policyid INTEGER,
cost REAL,
ssn CHAR(11) NOT NULL,
PRIMARY KEY (policyid).
FOREIGN KEY (ssn) REFERENCES Employees,
ON DELETE CASCADE) Mapping Two Binary Relationships (1) The key constraints allow us to combine Purchaser with Policies, and Beneficiary and Dependents. age pname policyid cost 11/11/2020 45<br>
46
Chain of Weak Entity Sets What if Policies is also a weak entity set ? age pname Dependents policyid cost Policies Purchaser Include both ssn and policyid
in primary key Add ssn to
primary key Primary key:
(pname, policyid, ssn) 11/11/2020 46<br>
in primary key Add ssn to
primary key Primary key:
(pname, policyid, ssn) 11/11/2020 46<br>
47
Chain of Identifying Relationships for Weak Entity Sets CREATE TABLE Dependents (
pname CHAR(20),
age INTEGER,
policyid INTEGER,
PRIMARY KEY (pname, policyid),
FOREIGN KEY (policyid) REFERENCES Policies,
ON DELETE CASCADE) CREATE TABLE Dependents (
pname CHAR(20),
age INTEGER,
policyid INTEGER,
ssn INTEGER,
PRIMARY KEY (pname, policyid, ssn),
FOREIGN KEY (policyid, ssn) REFERENCES Policies,
ON DELETE CASCADE) from Policies age pname Dependents policyid cost Policies Purchaser Must include also SSN from Employees 11/11/2020 47 Becomes (From Slide 58)<br>
pname CHAR(20),
age INTEGER,
policyid INTEGER,
PRIMARY KEY (pname, policyid),
FOREIGN KEY (policyid) REFERENCES Policies,
ON DELETE CASCADE) CREATE TABLE Dependents (
pname CHAR(20),
age INTEGER,
policyid INTEGER,
ssn INTEGER,
PRIMARY KEY (pname, policyid, ssn),
FOREIGN KEY (policyid, ssn) REFERENCES Policies,
ON DELETE CASCADE) from Policies age pname Dependents policyid cost Policies Purchaser Must include also SSN from Employees 11/11/2020 47 Becomes (From Slide 58)<br>
48
Summary ER to Relational Mapping 11/11/2020 48<br>
49
ER to Relational Mapping (1) 11/11/2020 49 Key constraint<br>
50
ER to Relational Mapping (2) PKEY: Partial key 11/11/2020 50 Total participation Key constraint PKEY becomes part of the composite key (PKEY, KEY)<br>
51
ER to Relational Mapping (3) 11/11/2020 51 Using Three Tables Some might not belong to either subcategory<br>
52
ER to Relational Mapping (4) 11/11/2020 52 Using Two Tables They must include all employees Not stored<br>
53
ER to Relational Mapping (5) PKEY KEY 11/11/2020 53<br>
54
ER to Relational Mapping (6) 11/11/2020 54<br>
55
Exercise 11/11/2020 55<br>
56
Mapping Example – ER Design address Telephone Place Lives name ssn albumID Producer Album title speed copyrightDate dname instrID key title songID author phone 11/11/2020 56<br>
57
Mapping Example address phone Place Lives name ssn albumID Producer Album title speed copyrightDate dname instrID key title songID author CREAT TABLE Home_Telephone
( phone CHAR(11) ,
address CHAR(30) NOT NULL,
PRIMARY KEY (phone),
FOREIGN KEY address REFERENCES Place
) 11/11/2020 57 Key constraint Total participation<br>
( phone CHAR(11) ,
address CHAR(30) NOT NULL,
PRIMARY KEY (phone),
FOREIGN KEY address REFERENCES Place
) 11/11/2020 57 Key constraint Total participation<br>
58
Mapping Example address Phone_no Telephone Place name ssn albumID Producer Album title speed copyrightDate dname instrID key title songID author CREAT TABLE Lives (
ssn CHAR(10),
phone CHAR(11),
PRIMARY KEY (ssn, phone),
FOREIGN KEY phone
REFERENCES Home_Telephone,
FOREIGN KEY ssn
REFERENCES Musicians ) 11/11/2020 58 Many-to-many<br>
ssn CHAR(10),
phone CHAR(11),
PRIMARY KEY (ssn, phone),
FOREIGN KEY phone
REFERENCES Home_Telephone,
FOREIGN KEY ssn
REFERENCES Musicians ) 11/11/2020 58 Many-to-many<br>
59
Mapping Example address Telephone Place name ssn albumID Producer Album title speed copyrightDate dname instrID key title songID author CREAT TABLE Plays (
ssn CHAR(10),
instrID INTEGER
PRIMARY KEY (ssn, instrID),
FOREIGN KEY (ssn)
REFERENCES Musicians,
FOREIGN KEY (instrID)
REFERENCES Instruments ) phone 11/11/2020 59<br>
ssn CHAR(10),
instrID INTEGER
PRIMARY KEY (ssn, instrID),
FOREIGN KEY (ssn)
REFERENCES Musicians,
FOREIGN KEY (instrID)
REFERENCES Instruments ) phone 11/11/2020 59<br>
60
Mapping Example address Telephone Place name ssn albumID title speed copyrightDate dname instrID key title songID author CREAT TABLE Album_Producer (
albumID INTEGER,
ssn CHAR(10) NOT NULL,
copyrightDate DATE,
speed INTEGER,
title CHAR(30)
PRIMARY KEY (albumID),
FOREIGN KEY (ssn) REFERENCES Musicians ) phone 11/11/2020 60 Total participation Key constraint<br>
albumID INTEGER,
ssn CHAR(10) NOT NULL,
copyrightDate DATE,
speed INTEGER,
title CHAR(30)
PRIMARY KEY (albumID),
FOREIGN KEY (ssn) REFERENCES Musicians ) phone 11/11/2020 60 Total participation Key constraint<br>
61
Mapping Example address Telephone Place name ssn albumID title speed copyrightDate dname instrID key title songID author CREAT TABLE Perform (
songID INTEGER,
ssn CHAR(10),
PRIMARY KEY (ssn, songID),
FOREIGN KEY (ssn)
REFERENCES Musicians,
FOREIGN KEY (songID)
REFERENCES Songs ) phone 11/11/2020 61<br>
songID INTEGER,
ssn CHAR(10),
PRIMARY KEY (ssn, songID),
FOREIGN KEY (ssn)
REFERENCES Musicians,
FOREIGN KEY (songID)
REFERENCES Songs ) phone 11/11/2020 61<br>
62
Mapping Example address Telephone Place name ssn albumID Producer Album title speed copyrightDate dname instrID key title songID author CREAT TABLE Songs_Appears (
songID INTEGER,
author CHAR(30),
title CHAR(30),
albumID INTEGER NOT NULL,
PRIMARY KEY (songID),
FOREIGN KEY (albumID)
REFERENCES Album_Producer ) phone 11/11/2020 62<br>
songID INTEGER,
author CHAR(30),
title CHAR(30),
albumID INTEGER NOT NULL,
PRIMARY KEY (songID),
FOREIGN KEY (albumID)
REFERENCES Album_Producer ) phone 11/11/2020 62<br>