INFO20003 Database Systems
INFO20003 Database Systems 1
INFO20003 Database Systems
Lecture 04
Relational Model &
Translating ER diagrams
Semester 2 2018, Week 2
Dr Renata Borovica-Gajic
• Don’t have a study group?
• Want to develop your interpersonal skills (employers love this)?
• Want to get more practice in the subject content?
• Want to contribute to the University’s world-class research?
Visit our study group session.
You’ll work with other students on a selection of database-related learning
activities, and make new friends along the way.
Study groups for
Database Systems
Study group session for INFO20003:
EDS 3 (Old Engineering level 2)
Every Tuesday, 1-2pm
Participation in this research project is optional. There is no commitment. The study group session is
not assessed. For more information, contact Dr Rina Shvartsman, shvartsman.r@unimelb.edu.au
mailto:shvartsman.r@unimelb.edu.au
INFO20003 Database Systems 3
Coverage
• Relational Model
• Keys & Integrity Constraints
• Translating ER to Logical and Physical Model
Readings: Chapter 3, Ramakrishnan & Gehrke, Database Systems
© University of Melbourne 2018
INFO20003 Database Systems 4© University of Melbourne 2018
Relational Data Model
• Data Model allows us to translate real world things into
structures that a computer can store
• Many models: Relational, ER, O-O, Network, Hierarchical, etc.
• Relational Model:
–Rows & Columns (Tuples and Attributes/fields)
–Keys & Foreign Keys to link Relations
sid name login age gpa
53666 Jones jones@cs 18 5.4
53688 Smith smith@eecs 18 4.2
53650 Smith smith@math 19 4.8
sid cid grade
53666 Carnatic101 5
53666 Reggae203 5.5
53650 Topology112 6
53666 History105 5
Enrolled
Students
INFO20003 Database Systems 5© University of Melbourne 2018
Relational Database: Definitions
• Relational database: a set of relations.
• Relation: made up of 2 parts:
–Schema : specifies name of relation, plus name and type of
each column (attribute).
Example: Students(sid: string, name: string, login: string, age:
integer, gpa: real)
–Instance : a table, with rows and columns.
#rows = cardinality
#fields = degree (or arity)
• You can think of a relation as a set of rows or tuples.
– all rows are distinct, no order among rows
INFO20003 Database Systems 6© University of Melbourne 2018
Example Instance of Students Relation
sid name login age gpa
53666 Jones jones@cs 18 3.4
53688 Smith smith@eecs 18 3.2
53650 Smith smith@math 19 3.8
Cardinality = 3, degree (arity) = 5, all rows distinct
Students
INFO20003 Database Systems 7© University of Melbourne 2018
Logical Design: ER to Relational Model
In logical design entity set becomes a relation.
Attributes become attributes of the relation.
Logical Design:
Employees = (ssn,
name,
lot)
Employees
ssn
name
lot
Conceptual Design:
INFO20003 Database Systems 8© University of Melbourne 2018
ER to Logical to Physical
3. Physical Design:
Employees
(ssn CHAR(11),
name CHAR(20),
lot INTEGER)
Employees
ssn
name
lot
2. Logical Design:
Employees = (ssn,
name,
lot)
1. Conceptual Design:
In physical design we choose data types
INFO20003 Database Systems 9© University of Melbourne 2018
The Entire Cycle
3. Physical Design
2. Logical Design
1. Conceptual Design
4. Implementation
5. Create Instance
INFO20003 Database Systems 10© University of Melbourne 2018
The Entire Cycle
3. Physical Design:
Employees
(ssn CHAR(11),
name CHAR(20),
lot INTEGER)
Employees
ssn
name
lot
ssn name lot
0983763423 John 10
9384392483 Jane 10
3743923483 Jill 20
EMPLOYEES
2. Logical Design:
Employees = (ssn,
name,
lot)
1. Conceptual Design: 4. Implementation:
CREATE TABLE Employees
(ssn CHAR(11),
name CHAR(20),
lot INTEGER,
PRIMARY KEY (ssn))
5. Instance:
INFO20003 Database Systems 14© University of Melbourne 2018
Creating Relations in SQL
Example: Creating the Students relation.
CREATE TABLE Students
(sid CHAR(20),
name CHAR(20),
login CHAR(10),
age INTEGER,
gpa FLOAT)
The type (domain) of each field is specified, and enforced
by the DBMS whenever tuples are added or modified.
INFO20003 Database Systems 17
Relational Model
• Relational Model & SQL overview
• Keys & Integrity Constraints
• Translating ER to Logical and Physical Model
Readings: Chapter 3, Ramakrishnan & Gehrke, Database Systems
© University of Melbourne 2018
INFO20003 Database Systems 18© University of Melbourne 2018
Keys
• Keys are a way to associate tuples in different
relations
• Keys are one form of integrity constraint (IC)
• Example: Only students can be enrolled in subjects.
sid name login age gpa
53666 Jones jones@cs 18 3.4
53688 Smith smith@cs 18 3.2
53650 Smith smith@math 19 3.8
sid cid grade
53666 15-101 C
53666 18-203 B
53650 15-112 A
53666 15-105 B
Enrolled Students
PRIMARY KeyFOREIGN Key
INFO20003 Database Systems 19© University of Melbourne 2018
Primary Keys
• A set of fields is a superkey if no two distinct tuples can have
same values in all key fields
• A set of fields is a key for a relation if it is a superkey and no
subset of the fields is a superkey (minimal subset)
• Out of all keys one is chosen to be the primary key of the
relation. Other keys are called candidate keys.
• Each relation has a primary key.
• Your turn:
1. Is sid a key for Students?
2. What about name?
3. Is the set {sid, gpa} a superkey? Is the set {sid, gpa} a key?
4. Find a primary key from this set {sid, login}
INFO20003 Database Systems 20© University of Melbourne 2018
Primary and Candidate Keys in SQL
• There are possibly many candidate keys (specified using
UNIQUE), one of which is chosen as the primary key. Keys
must be chosen carefully.
Example:
For a given student and course, there is a single grade.
“Students can take only one course,
and no two students in a course
receive the same grade.”
CREATE TABLE Enrolled
(sid CHAR(20)
cid CHAR(20),
grade CHAR(2),
PRIMARY KEY (sid,cid))
CREATE TABLE Enrolled
(sid CHAR(20)
cid CHAR(20),
grade CHAR(2),
PRIMARY KEY (sid),
UNIQUE (cid, grade))
vs.
INFO20003 Database Systems 21© University of Melbourne 2018
Foreign Keys & Referential Integrity
• Foreign key : A set of fields in one relation that is
used to ‘refer’ to a tuple in another relation. Foreign
key must correspond to the primary key of the other
relation.
• If all foreign key constraints are enforced in a DBMS,
we say a referential integrity is achieved.
INFO20003 Database Systems 22© University of Melbourne 2018
Foreign Keys in SQL
Example: Only students listed in the Students relation should
be allowed to enroll in courses.
• sid is a foreign key referring to Students
CREATE TABLE Enrolled
(sid CHAR(20),
cid CHAR(20),
grade CHAR(2),
PRIMARY KEY (sid,cid),
FOREIGN KEY (sid) REFERENCES Students )
sid name login age gpa
53666 Jones jones@cs 18 3.4
53688 Smith smith@cs 18 3.2
53650 Smith smith@math 19 3.8
sid cid grade
53666 15-101 C
53666 18-203 B
53650 15-112 A
53666 15-105 B
Enrolled
Students
INFO20003 Database Systems 23© University of Melbourne 2018
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?
–(In SQL, also: Set sid in Enrolled tuples that refer to it to a
special value null, denoting `unknown’ or `inapplicable’.)
• Note: Similar issues arise if primary key of Students tuple is
updated.
INFO20003 Database Systems 24© University of Melbourne 2018
Integrity Constraints (ICs)
• IC: condition that must be true for any instance of the
database; e.g., domain constraints.
–ICs are specified when schema is defined.
–ICs are checked when relations are modified.
• A legal instance of a relation is one that satisfies all
specified ICs.
–DBMS should not allow illegal instances.
INFO20003 Database Systems 25
Relational Model
• Relational Model & SQL overview
• Keys & Integrity Constraints
• Translating ER to Logical and Physical Model
Readings: Chapter 3, Ramakrishnan & Gehrke, Database Systems
© University of Melbourne 2018
INFO20003 Database Systems 26
Multi-valued attributes in logical design
• Multi-valued attributes need to be unpacked (flattened) when converting to
logical design.
Logical Design:
Employees = (ssn,
name,
lot,
home_num,
work_num)
Employees
ssn
name
lot
Conceptual Design:
Phone num
Example:
For employees we need to capture home phone number and work phone
number.
Multi-valued attribute
INFO20003 Database Systems 27© University of Melbourne 2018
ER to Logical Design
lot
dname
budgetdid
name
DepartmentsEmployees
ssn
since
Works_In
Logical Design:
Conceptual Design:
In translating a many-to-many relationship set to a relation,
attributes of a new relation must include:
1. Keys for each participating entity set (as foreign keys). This
set of attributes forms a superkey of the relation.
2. All descriptive attributes.
INFO20003 Database Systems 28© University of Melbourne 2018
ER to Logical Design
Works_In = (ssn,
did,
since)
Employees = (ssn,
name
lot)
Departments = (did,
dname,
budget)
lot
dname
budgetdid
name
DepartmentsEmployees
ssn
since
Works_In
Note: Underline = PK,
italic and underline = FK,
underline and bold = PFK
Logical Design:
Conceptual Design:
Keys from connecting
entities become PFK
INFO20003 Database Systems 29© University of Melbourne 2018
Logical to Physical Design
Works_In(
ssn CHAR(11),
did INTEGER,
since DATE)
Employees
(ssn CHAR(11),
name CHAR(20),
lot INTEGER)
Departments
(did INTEGER,
dname CHAR(20),
budget FLOAT)
Physical Design:
Works_In = (ssn, did,since)
Employees = (ssn, name, lot)
Departments = (did, dname, budget)
Note: Underline = PK,
italic and underline = FK,
underline and bold = PFK
Logical Design:
INFO20003 Database Systems 30© University of Melbourne 2018
Implementation (Create table)
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)
CREATE TABLE Employees
(ssn CHAR(11),
name CHAR(20),
lot INTEGER,
PRIMARY KEY (ssn))
CREATE TABLE Departments
(did INTEGER,
dname CHAR(20),
budget FLOAT,
PRIMARY KEY (did))
Implementation:
Works_In = (ssn, did,since)
Employees = (ssn, name, lot)
Departments = (did, dname, budget)
Note: Underline = PK,
italic and underline = FK,
underline and bold = PFK
Logical Design:
INFO20003 Database Systems 31© University of Melbourne 2018
Example Instances
ssn name lot
0983763423 John 10
9384392483 Jane 10
3743923483 Jill 20
Employees
did dname budget
101 Sales 10K
105 Purchasing 20K
108 Databases 1000K
Departments
ssn did since
0983763423 101 1 Jan 2003
0983763423 108 2 Jan 2003
9384392483 108 1 Jun 2002
Works_In
INFO20003 Database Systems 32© University of Melbourne 2018
ER to Logical Design Example 2
name
Suppliers
id name
Departments
id
name
Parts
id
Contract
In translating a many-to-many 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.
• All descriptive attributes.
Contracts (
supplier_id,
part_id,
department_id)
Note: Underline = PK,
italic and underline = FK,
underline and bold = PFK
Logical Design:
INFO20003 Database Systems 33© University of Melbourne 2018
ER to Logical to Implementation Example 2
CREATE TABLE Contracts (
supplier_id INTEGER,
part_id INTEGER,
department_id INTEGER,
PRIMARY KEY (supplier_id, part_id, department_id),
FOREIGN KEY (supplier_id) REFERENCES Suppliers,
FOREIGN KEY (part_id) REFERENCES Parts,
FOREIGN KEY (department_id) REFERENCES Departments)
name
Suppliers
id name
Departments
id
name
Parts
id
Contract
Contracts (
supplier_id,
part_id,
department_id)
Logical Design:
Implementation:
INFO20003 Database Systems 34© University of Melbourne 2018
Review: Key Constraints in ER
• Each department has at most one manager, according to
the key constraint on Manages.
dname
budgetdid
since
lot
name
ssn
ManagesEmployees Departments
INFO20003 Database Systems 35© University of Melbourne 2018
Key Constraints: Logical design
dname
budgetdid
since
lot
name
ssn
ManagesEmployees Departments
Logical Design:
Manages = (ssn, did, since)
Note: Underline = PK,
italic and underline = FK,
underline and bold = PFK
Employees = (ssn,name, lot)
Departments = (did,dname, budget)
VS.
Employees = (ssn,name, lot)
Departments = (did,dname, budget,
ssn, since)
INFO20003 Database Systems 36© University of Melbourne 2018
Key Constraints in SQL
CREATE TABLE Employees
(ssn CHAR(11),
name CHAR(20),
lot INTEGER,
PRIMARY KEY (ssn))
CREATE TABLE Departments
(did INTEGER,
dname CHAR(20),
budget FLOAT,
ssn CHAR(11),
since DATE,
PRIMARY KEY (did)
FOREIGN KEY (ssn)
REFERENCES Employees)
CREATE TABLE Manages(
ssn CHAR(11),
did INTEGER,
since DATE,
PRIMARY KEY (ssn, did),
FOREIGN KEY (ssn)
REFERENCES Employees,
FOREIGN KEY (did)
REFERENCES Departments)
VS.
Which one is better?
dname
budgetdid
since
lot
name
ssn
ManagesEmployees Departments
Implementation:
INFO20003 Database Systems 37© University of Melbourne 2018
Key Constraints rule
• Primary key from the many side becomes a foreign key
on the one side
• This is the way to ensure that the key constraint holds
CREATE TABLE Departments
(did INTEGER,
dname CHAR(20),
budget FLOAT,
ssn CHAR(11),
since DATE,
PRIMARY KEY (did)
FOREIGN KEY (ssn)
REFERENCES Employees)
Each department will
have a single manager
INFO20003 Database Systems 38© University of Melbourne 2018
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).
lot
name dname
budgetdid
since
name dname
budgetdid
since
Manages
since
DepartmentsEmployees
ssn
Works_In
INFO20003 Database Systems 39© University of Melbourne 2018
Participation Constraints in SQL
• We specify total participation with key words NOT NULL
–NOT NULL = this field cannot be empty
CREATE TABLE Departments(
did INTEGER NOT NULL,
dname CHAR(20),
budget REAL,
ssn CHAR(11) NOT NULL,
since DATE,
PRIMARY KEY (did),
FOREIGN KEY (ssn) REFERENCES Employees,
ON DELETE NO ACTION)
INFO20003 Database Systems 40© University of Melbourne 2018
Review: Weak Entities
• A weak entity can be identified uniquely only by considering
the primary key of another (owner) entity.
lot
name
agepname
DependentsEmployees
ssn
Policy
cost
INFO20003 Database Systems 41© University of Melbourne 2018
Translating Weak Entity Sets
• Weak entity set and identifying relationship set are translated
into a single table.
–When the owner entity is deleted, all owned weak entities must also be
deleted.
Dependents = (pname, age, cost, ssn)
CREATE TABLE Dependents(
pname CHAR(20),
age INTEGER,
cost REAL,
ssn CHAR(11) NOT NULL,
PRIMARY KEY (pname, ssn),
FOREIGN KEY (ssn) REFERENCES Employees,
ON DELETE CASCADE)
Note: Underline = PK,
italic and underline = FK,
underline and bold = PFK
Logical Design:
Implementation:
INFO20003 Database Systems 42© University of Melbourne 2018
Relational Model: Summary
• A tabular representation of data.
• Simple and intuitive, currently the most widely used.
• Integrity constraints can be specified based on application
semantics. DBMS checks for violations.
–Two important ICs: primary and foreign keys
– In addition, we always have domain constraints.
• Rules to translate ER to logical design (relational model)
INFO20003 Database Systems 43© University of Melbourne 2018
What’s examinable
• Translate conceptual (ER) into logical & physical design
• Understand integrity constraints
• Use DDL of SQL to create tables with constraints
INFO20003 Database Systems 44
Next Lecture
© University of Melbourne 2018
• ER Modelling Example with MySQL Workbench
‒ You will need this for workshops/labs (and assessment)