Transcript 25SpL7TranslatingERDiagram - Department of Computer Science

Translation of ER-diagram into
Relational Schema
Prof. Sin-Min Lee
Department of Computer
Science
1
Learning Objectives
 Define each of the following database
terms
 Relation
 Primary key
 Functional dependency
 Foreign key
 Referential integrity
 Field
 Data type
 Null value
9.2
2
Learning Objectives
Discuss the role of designing databases in
the analysis and design of an information
system
Learn how to transform an entityrelationship (ER) Diagram into an
equivalent set of well-structured relations
9.3
3
Process of Database Design
•
9.4
Logical Design
–
Based upon the conceptual data model
–
Four key steps
1.
Develop a logical data model for each known user
interface for the application using normalization
principles.
2.
Combine normalized data requirements from all user
interfaces into one consolidated logical database model
3.
Translate the conceptual E-R data model for the
application into normalized data requirements
4.
Compare the consolidated logical database design with
the translated E-R model and produce one final logical
database model for the application
4
9.5
5
Relational Database Model
• Data represented as a set of related tables or
relations
• Relation
– A named, two-dimensional table of data. Each
relation consists of a set of named columns and
an arbitrary number of unnamed rows
– Properties
• Entries in cells are simple
• Entries in columns are from the same set of values
• Each row is unique
• The sequence of columns can be interchanged without
changing the meaning or use of the relation
9.6
• The rows may be interchanged or stored in any
sequence
6
Relational Database Model
• Well-Structured Relation
– A relation that contains a minimum
amount of redundancy and allows users
to insert, modify and delete the rows
without errors or inconsistencies
9.7
7
Transforming E-R Diagrams into
Relations
•
It is useful to transform the conceptual data
model into a set of normalized relations
•
Steps
1.
Represent entities
2. Represent relationships
3. Normalize the relations
4. Merge the relations
9.8
8
Transforming E-R Diagrams into
Relations
1.
Represent Entities
–
Each regular entity is transformed into a relation
–
The identifier of the entity type becomes the
primary key of the corresponding relation
–
The primary key must satisfy the following two
conditions
a.
The value of the key must uniquely identify every row in
the relation
b. The key should be nonredundant
9.9
9
9.10
10
Transforming E-R Diagrams into
Relations
2. Represent Relationships
–
Binary 1:N Relationships
•
Add the primary key attribute (or attributes) of
the entity on the one side of the relationship as a
foreign key in the relation on the right side
•
The one side migrates to the many side
9.11
11
9.12
12
Transforming E-R Diagrams into
Relations
– Binary or Unary 1:1
• Three possible options
a. Add the primary key of A as a foreign key of B
b.Add the primary key of B as a foreign key of A
c. Both
9.13
13
Transforming E-R Diagrams into
Relations
2. Represent Relationships (continued)
–
Binary and higher M:N relationships
•
Create another relation and include primary
keys of all relations as primary key of new
relation
9.14
14
9.15
15
Transforming E-R Diagrams into
Relations
– Unary 1:N Relationships
• Relationship between instances of a single entity type
• Utilize a recursive foreign key
– A foreign key in a relation that references the primary key
values of that same relation
– Unary M:N Relationships
• Create a separate relation
• Primary key of new relation is a composite of two attributes
that both take their values from the same primary key
9.16
16
9.17
17
9.18
18
Transforming E-R Diagrams into
Relations
3. Merging Relations (View Integration)
–
–
Purpose is to remove redundant relations
View Integration Problems
•
Synonyms
•
Homonyms
•
–
–
–
–
Two different names used for the same attribute
When merging, get agreement from users on a single,
standard name
A single attribute name that is used for two or more
different attributes
Resolved by creating a new name
Dependencies between nonkeys
–
–
Dependencies may be created as a result of view
integration
In order to resolve, the new relation must be
normalized
9.19
19
Primary Key Constraints
• A set of fields is a key for a relation if :
1. No two distinct tuples can have same values in all key
fields, and
2. This is not true for any subset of the key.
–
Part 2 false? A superkey.
–
If there’s >1 key for a relation, one of the keys is
chosen (by DBA) to be the primary key.
• E.g., sid is a key for Students. (What about
name?) The set {sid, gpa} is a superkey.
Primary key can not have null value
20
Foreign Keys, Referential
Integrity
• Foreign key : Set of fields in one relation that
is used to `refer’ to a tuple in another relation.
(Must correspond to primary key of the second
relation.) Like a `logical pointer’.
• E.g. sid is a foreign key referring to Students:
–
Enrolled(sid: string, cid: string, grade: string)
–
If all foreign key constraints are enforced,
referential integrity is achieved, i.e., no dangling
references.
–
Can you name a data model w/o referential integrity?
21
Enforcing Referential Integrity
• Consider Students and Enrolled; sid in Enrolled is
a foreign key that references Students.
• What should be done if an Enrolled tuple with a
non-existent student id is inserted? (Reject it!)
• What should be done if a Students tuple is
deleted?
–
Also delete all Enrolled tuples that refer to it.
–
Disallow deletion of a Students tuple that is referred to.
–
Set sid in Enrolled tuples that refer to it to a default
sid.
22
Logical DB Design: ER to Relational
• Entity sets to tables.
ssn
name
Employees
lot
CREATE TABLE Employees
(ssn CHAR(11),
name CHAR(20),
lot INTEGER,
PRIMARY KEY (ssn))
23
Relationship Sets to Tables
• In translating a
relationship set to a
relation, attributes of the
relation must include:
–
Keys for each
participating entity set
(as foreign keys).
• This set of
attributes forms a
superkey for the
relation.
CREATE TABLE Works_In(
ssn CHAR(1),
did INTEGER,
since DATE,
PRIMARY KEY (ssn, did),
FOREIGN KEY (ssn)
REFERENCES Employees,
FOREIGN KEY (did)
REFERENCES Departments)
24
Review: Key Constraints
since
• Each dept has
at most one
manager,
name
ssn
dname
lot
Employees
did
Manages
budget
Departments
according to the
key constraint
on Manages.
1-to-1
1-to Many
Translation to
relational model?
Many-to-1
Many-to-Many
25
Translating ER Diagrams with Key Constraints
• Map relationship to
a table:
–
Note that did is
the key now!
–
Separate tables
for Employees
and
Departments.
• Since each
department has a
unique manager, we
CREATE TABLE Manages(
ssn CHAR(11),
did INTEGER,
since DATE,
PRIMARY KEY (did),
FOREIGN KEY (ssn) REFERENCES Employees,
FOREIGN KEY (did) REFERENCES Departments)
CREATE TABLE Dept_Mgr(
did INTEGER,
dname CHAR(20),
budget REAL,
ssn CHAR(11),
since DATE,
PRIMARY KEY (did),
FOREIGN KEY (ssn) REFERENCES Employees)
26
Review: 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).
since
name
dname
ssn
did
budget
lot in Departments
• Every
did value
table must
appearEmployees
in a row ofManages
the ManagesDepartments
table (with a
non-null ssn value!)
Works_In
since
27
Participation Constraints in
SQL
• We can capture participation constraints
involving one entity set in a binary
relationship,
butDept_Mgr(
little else (without
CREATE TABLE
did INTEGER,
resorting
to CHAR(20)
CHECK, constraints).
dname
budget REAL,
ssn CHAR(11) NOT NULL,
since DATE,
PRIMARY KEY (did),
FOREIGN KEY (ssn) REFERENCES Employees,
ON DELETE NO ACTION)
28
Review: 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
name in a one-to-many relationship set (1
cost
ssn
lot entities).
owner,
many weak
–
pname
age
WeakEmployees
entity set must have
total participation
in
Policy
Dependents
this identifying relationship set.
29
Translating Weak Entity Sets
• Weak entity set and identifying
relationship set are translated into a
single table.
CREATE TABLE Dep_Policy (
pnamethe
CHAR(20)
, entity is deleted, all
– When
owner
age INTEGER,
owned
weak entities must also be
cost REAL,
ssn CHAR(11) NOT NULL,
deleted.
PRIMARY KEY (pname, ssn),
FOREIGN KEY (ssn) REFERENCES Employees,
ON DELETE CASCADE)
30
Review: Binary vs. Ternary
Relationships
name
ssn
• If each policy
is owned by
just 1
employee:
–
Key
constraint on
Policies would
mean policy
can only
cover 1
pname
lot
Employees
Dependents
Covers
Bad design
Policies
policyid
cost
name
ssn
age
pname
lot
age
Dependents
Employees
Purchaser
Better design
policyid
Beneficiary
Policies
cost
31
Binary vs. Ternary Relationships
(Contd.)
CREATE TABLE Policies (
policyid INTEGER,
cost REAL,
ssn CHAR(11) NOT NULL,
PRIMARY KEY (policyid).
FOREIGN KEY (ssn) REFERENCES Employees,
ON DELETE CASCADE)
• The key
constraints
allow us to
combine
Purchaser with
CREATE TABLE Dependents (
Policies and
pname CHAR(20),
age INTEGER,
Beneficiary
policyid INTEGER,
with
PRIMARY KEY (pname, policyid).
FOREIGN KEY (policyid) REFERENCES Policies,
Dependents.
• Participation
ON DELETE CASCADE)
32
Relational Model: Summary
• A tabular representation of data.
• Simple and intuitive, currently the most widely
used.
• Integrity constraints can be specified by the
DBA, based on application semantics. DBMS
checks for violations.
–
Two important ICs: primary and foreign keys
–
In addition, we always have domain constraints.
• Powerful and natural query languages exist.
• Rules to translate ER to relational model
33
An Example
CREATE TABLE Student (
ID
NUMBER,
Fname VARCHAR2(20),
Lname VARCHAR2(20),
);
34
Constraints in Create Table
• Adding constraints to a table enables the
database system to enforce data
integrity.
• Different types of constraints:
* Not Null
* Default Values
* Unique
* Primary Key
* Foreign Key * Check Condition
35
Not Null Constraint
CREATE TABLE Student (
ID
NUMBER,
Fname VARCHAR2(20) NOT NULL,
Lname VARCHAR2(20) NOT NULL,
);
36
Primary Key Constraint
CREATE TABLE Student (
ID
NUMBER PRIMARY KEY,
Fname VARCHAR2(20) NOT NULL,
Lname VARCHAR2(20) NOT NULL,
);
Primary Key implies: * NOT NULL * UNIQUE.
There can only be one primary key.
37
Primary Key Constraint
(Syntax 2)
CREATE TABLE Students (
ID
NUMBER,
Fname VARCHAR2(20) NOT NULL,
Lname VARCHAR2(20) NOT NULL,
PRIMARY KEY(ID)
);
Needed when the primary key is made
up of two or more fields
38
Another Table
CREATE TABLE Studies(
Course
NUMBER,
Student
NUMBER
);
What should be the primary key?
What additional constraint do we want on
Student?
39
Foreign Key Constraint
CREATE TABLE Studies(
Course
NUMBER,
Student
NUMBER,
FOREIGN KEY (Student) REFERENCES
Students(ID)
);
NOTE: ID must be unique (or primary key) in
Student
40
Translating ER-Diagrams to
Table Definitions
41
Relations vs. Tables
• We show how to translate ER-Diagrams to
table definitions
• Sometimes, people translate ER-Diagrams
to relation definitions, which is more
abstract than table definitions.
– e.g., Student(ID, Fname, Lname);
– table definitions contain, in addition,
constraints and datatypes
42
Translating Entities
id
name
birthday
Actor
address
General Rule:
• Create a table with the name of the Entity.
• There is a column for each attribute
• The key in the diagram is the primary key of the
table
43
Translating Entities
id
birthday
Actor
name
address
Relation: Actor (id, name, birthday, address)
create table Actor(id varchar2(20) primary key,
name varchar2(40),
birthday date,
address varchar2(100));
44
Translating Relationships
(without constraints)
title
birthday
id
Actor
Acted In
Film
year
name
address
salary
type
General Rule:
• Create a table with the name of the relationship
• The table has columns for all of the relationship's attributes and
for the keys of each entity participating in the relationship
• What is the primary key of the table?
• What foreign keys are needed?
45
Translating relationships
(without constraints)
birthday
id
Actor
Acted In
Film
title
year
name
address
salary
type
What would be the relation for ActedIn?
How would you define the table for ActedIn?
46
Translating Recursive Relationships
(without constraints)
manager
id
Employee
name
worker
Manages
address
Relation: Actor (worker-id, manager-id)
What would be the table definition?
47
Translating relationships
(key constraints): Option 1
id
name
Director
Directed
Film
salary
title
year
General Rule for Option 1:
• Same as without key constraints, except that
the primary key is defined differently
48
Translating relationships
(key constraints): Option 1
id
Director
Directed
name
Film
salary
title
year
create table Directed(
id varchar2(20),
title varchar2(40),
salary integer,
What primary and foreign keys are missing?
)
49
Translating relationships
(key constraints): Option 2
id
name
Director
Directed
Film
salary
title
year
General Rule for Option 2:
• Do not create a table for the relationship
• Add information columns that would have been in the
relationship's table to the table of the entity with the
key constraint
• What is the disadvantage of this method?
• What is the advantage of this method?
50
Translating relationships
(key constraints): Option 2
id
Director
Directed
name
salary
Film
title
year
create table Film(
title varchar2(40),
year integer,
primary key (title),
What 3 lines are missing?
)
51
Translating relationships
(key constraints)
A
R
B
C
• What are the different options for
translating this diagram?
52
Translating relationships
(participation constraints)
id
Director
name
Directed
Film
salary
title
year
General Rule:
• If has both participation and key constraint, use Option
2 from before.
• Add the not null constraint to ensure that there will
always be values for the key of the other entity
53
Translating relationships
(participation constraints)
id
Director
Directed
name
Film
title
year
salary
create table Film(
title varchar2(40),
year integer,
id varchar2(20),
Where should we add
NOT NULL?
salary integer,
foreign key (id) references Director,
primary key (title))
54
Translating relationships
(participation constraints)
id
name
Actor
Acted In
salary
Film
title
year
• How would we translate this?
55
Translating Weak Entity Sets
phone
number
name
create table award(
name varchar2(40),
year integer,
Organization
money number(6,2),
o_name varchar2(40),
Gives
primary key(name, year, o_name),
Award
year
foreign key (o_name) references
Organization(name)
money
on delete cascade
name
)
56
Translating ISA:
Option 1
address
id
Movie Person
name
ISA
picture
Actor
Director
create table MoviePerson( ... )
create table Actor(id varchar2(20),
picture bfile,
primary key(id),
foreign key (id) references MoviePerson))
create table Director(...)
57
Translating ISA:
Option 2
address
id
Movie Person
name
ISA
picture
Actor
Director
No table for MoviePerson!
create table Actor(id varchar2(20),
address varchar2(100),
name varchar2(20),
picture blob,
primary key(id));
create table Director(...)
58
Which Option To Choose?
• What would you choose if:
– Actor and Director DO NOT COVER
MoviePerson?
– Actor OVERLAPS Director?
59
Translating
Aggregation
phone
number
name
Organization
picture
salary
Gives
Actor
Acted In
Won
year
Award
year
name
Film
title
type
• Create table for Won using:
– key of ActedIn
– key of Award (careful, award is a weak entity)
60