Transcript Chapter 3 - UCF Computer Science

The Relational Model
Chapter 3
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
1
Why Study the Relational Model?
 Most widely

Vendors: IBM, Informix, Microsoft, Oracle, Sybase, etc.
 “Legacy

used model
systems” in older models
Examples: IBM’s IMS
 Recent competitor:


object-oriented model
ObjectStore, Versant, Ontos
A synthesis emerging: object-relational model
• Informix Universal Server, UniSQL, O2, Oracle, DB2
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
2
Relational Database: Definitions
 Relational
 Relation:

database: a set of relations
made up of 2 parts:
Schema: specifies name of relation, plus name
and type of each column.
• Example: Students(sid: string, name: string,
login: string, age: integer, gpa: real).

Instance: a table, with rows and columns.
#Rows = cardinality, #fields = degree/arity.
 Can
think of a relation as a set of rows or
tuples (i.e., all rows are distinct).
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
3
Example Instance of “Students” Relation
sid
53666
53688
53650
name
login
Jones jones@cs
Smith smith@eecs
Smith smith@math
age
18
18
19
gpa
3.4
3.2
3.8
Cardinality = 3, degree = 5, all rows distinct*
*In practice, commercial systems allows duplicate rows
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
4
Relational Query Languages
A
major strength of the relational model:
supports simple, powerful querying of data.
 Queries can
be written intuitively, and the
DBMS is responsible for efficient evaluation.

The key: precise semantics for relational queries.

Allows the optimizer to extensively re-order
operations, and still ensure that the answer does
not change.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
5
The SQL Query Language
 Developed
by IBM (system R) in the 1970s
 Need for a standard since it is used by
many vendors
 Standards:






SQL-86
SQL-89 (minor revision)
SQL-92 (major revision)
SQL-99 (major extensions)
SQL-03 (includes XML-related features)
SQL-08 (Data compression/encryption, etc.)
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
6
The SQL Query Language
 To
find all 18 year old students, we can
SELECT *
FROM Students S
WHERE S.age=18
sid
name
53666 Jones
login
jones@cs
age gpa
18
3.4
53688 Smith smith@ee 18
3.2
 To
find just names and logins, replace the
first line:
SELECT S.name, S.login
FROM Students S
WHERE S.age=18
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
sid
login
53666
jones@cs
53688
smith@ee
7
Querying Multiple Relations (1)
What does the following query compute?
Enrolled
sid
53831
53831
53650
53666
cid
grade
Carnatic101
C
Reggae203
B
Topology112
A
History105
B
SELECT S.name, E.cid
FROM Students S, Enrolled E
WHERE S.sid=E.sid AND E.grade=“A”
A student with
“sid” has an
entry in Enrolled
Students
sid
name
login
age
gpa
53831
Zhang
zhang@ee
19
3.2
53650
Smith
smith@cs
21
3.9
53666
Jones
jones@cs
20
3.5
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
The Enrolled
entry has a
grade of “A”
Retrieve names of students
and the courses they
received an “A” grade
8
Querying Multiple Relations (2)
Enrolled
sid
53831
53831
53650
53666
cid
grade
Carnatic101
C
Reggae203
B
Topology112
A
History105
B
Students
sid
name
login
age
gpa
53831
Zhang
zhang@ee
19
3.2
53650
Smith
smith@cs
21
3.9
53666
Jones
jones@cs
20
3.5
SELECT S.name, E.cid
FROM Students S, Enrolled E
WHERE S.sid=E.sid AND E.grade=“A”
S.name
E.cid
Smith
Topology112
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
9
Creating Relations in SQL
CREATE TABLE Students
(sid: CHAR(20),
name: CHAR(20),
login: CHAR(10),
age: INTEGER,
gpa: REAL)
A data field
Relation “Students”
has five columns
(data fields)
sid
name
login
age
gpa
53831
Zhang
zhang@ee
19
3.2
53650
Smith
smith@cs
21
3.9
53666
Jones
jones@cs
20
3.5
Domain (type) of the
field – enforced by
DBMS
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
10
Creating Relations in SQL
As another example, the Enrolled table holds
information about courses that students take.
CREATE TABLE Enrolled
(sid: CHAR(20),
cid: CHAR(20),
grade: CHAR(2))
sid
53831
53831
53650
53666
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
cid
grade
Carnatic101
C
Reggae203
B
Topology112
A
History105
B
11
Destroying and Altering Relations
DROP TABLE Students
Destroys the relation Students. The schema
information and the tuples are deleted.
ALTER TABLE Students
ADD COLUMN firstyear: integer
•
•
The schema of Students is altered by adding
a new field
Every tuple in the current instance is
extended with a null value in the new field.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
12
Adding and Deleting Tuples
 Can
insert a single tuple using:
INSERT INTO Students (sid, name, login, age, gpa)
VALUES (53688, ‘Smith’, ‘smith@ee’, 18, 3.2)
 Can
delete all tuples satisfying some
condition (e.g., name = Smith):
DELETE
FROM Students S
WHERE S.name = ‘Smith’
* Powerful variants of these commands are available; more later!
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
13
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.
 If the
DBMS checks ICs, stored data is more
faithful to real-world meaning.

Avoids data entry errors, too!
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
14
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
• <SSN> is a key → <SSN, NAME> is a superkey

If there is more than one key for a relation,
one of the keys is chosen (by DBA) to be the
primary key.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
15
Primary Key Constraint Example
 sid
is a key for Students. (What about name?)
 The set {sid, gpa} is a superkey (i.e., set of
fields that contains a key)
023
CREATE TABLE Students
(sid: CHAR(20),
name: CHAR(20),
login: CHAR(10),
age: INTEGER,
gpa: REAL)
Key
No two
students can
have the
same sid
136
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
16
Primary and Candidate Keys in SQL
Possibly many candidate keys (specified using UNIQUE),
one of which is chosen as the primary key.
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) )
There is only one
candidate key, chosen
as the primary key
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
There are two
candidate keys
17
Primary and Candidate Keys in SQL
CREATE TABLE Enrolled
(sid CHAR(20)
cid CHAR(20),
grade CHAR(2),
PRIMARY KEY (sid,cid) )
Students can take only one
course, and receive a single
grade for that course
Further, no two students in a
course receive the same grade
For a given student
and course, there is a
single grade
CREATE TABLE Enrolled
(sid CHAR(20)
cid CHAR(20),
grade CHAR(2),
PRIMARY KEY (sid),
UNIQUE (cid, grade) )
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
18
Foreign Keys
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’ (for retrieving additional data)
Primary key
sid
name
53666 Jones
Foreign key
login
jones@cs
Students
age gpa
18
3.4
53650 Smith smith@ee 18
3.2
“logical pointer”
Enrolled
sid
53666
53650
53650
53666
cid
Carnatic101
Reggae203
Topology112
History105
grade
C
B
A
B
Retrieve information about student 53650
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
19
DBMS Ensures Referential Integrity
sid is a foreign key in Enrolled referring to Students
Primary key
sid
name
53666 Jones
Foreign key
login
jones@cs
age gpa
18
3.4
53650 Smith smith@ee 18
3.2
sid
53666
53650
53650
53666
cid
Carnatic101
Reggae203
Topology112
History105
grade
C
B
A
B
Referential Integrity: If all foreign key constraints are
enforced, referential integrity is achieved, i.e., no
dangling references.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
20
Foreign Keys in SQL
CREATE TABLE Enrolled
(sid CHAR(20), cid CHAR(20), grade CHAR(2),
PRIMARY KEY (sid,cid),
FOREIGN KEY (sid) REFERENCES Students )
Only students listed in the
Students relation should be
allowed to enroll for courses
Enrolled
sid
53666
53666
53650
53666
cid
grade
Carnatic101
C
Reggae203
B
Topology112
A
History105
B
Students
sid
53666
53688
53650
name
login
Jones jones@cs
Smith smith@eecs
Smith smith@math
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
age
18
18
19
gpa
3.4
3.2
3.8
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 nonexistent student id is inserted? (Reject it !)
Enrolled
sid
53666
53666
53650
53666
cid
grade
Carnatic101
C
Reggae203
B
Topology112
A
History105
B
12345 CS101
Students
sid
53666
53688
53650
name
login
Jones jones@cs
Smith smith@eecs
Smith smith@math
age
18
18
19
gpa
3.4
3.2
3.8
A
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
22
Enforcing Referential Integrity

What should be done if a Students tuple is deleted?
Option 1:
Students
sid
53666
53688
53650
Also
delete
name
login
Jones jones@cs
Smith smith@eecs
Smith smith@math
age
18
18
19
gpa
3.4
3.2
3.8
Enrolled
sid
53666
53666
53650
53666
cid
grade
Carnatic101
C
Reggae203
B
Topology112
A
History105
B
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
23
Enforcing Referential Integrity

What should be done if a Students tuple is deleted?
Option 2:
Students
sid
53666
53688
53650
name
login
Jones jones@cs
Smith smith@eecs
Smith smith@math
age
18
18
19
gpa
3.4
3.2
3.8
Enrolled
Disallow !
sid
53666
53666
53650
53666
cid
grade
Carnatic101
C
Reggae203
B
Topology112
A
History105
B
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
24
Enforcing Referential Integrity

What should be done if a Students tuple is deleted?
Option 3:
Students
sid
53666
53688
53650
name
login
Jones jones@cs
Smith smith@eecs
Smith smith@math
age
18
18
19
gpa
3.4
3.2
3.8
Enrolled
99999 sid
Default
99999
99999
sid
53666
53666
53650
53666
cid
grade
Carnatic101
C
Reggae203
B
Topology112
A
History105
B
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
25
Enforcing Referential Integrity

What should be done if a Students tuple is deleted?
Option 4:
Students
sid
53666
53688
53650
name
login
Jones jones@cs
Smith smith@eecs
Smith smith@math
age
18
18
19
gpa
3.4
3.2
3.8
Enrolled
null
null
null
sid
53666
53666
53650
53666
cid
grade
Carnatic101
C
Reggae203
B
Topology112
A
History105
B
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
26
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 nonexistent 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’.)
Similar if primary key of Students tuple is updated.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
27
Referential Integrity in SQL

SQL/92 and SQL:1999
Example:
support all 4 options on
deletes and updates.
CREATE TABLE Enrolled
 Default is NO ACTION
(sid CHAR(20),
(delete/update is
cid CHAR(20),
rejected)
grade CHAR(2),
 CASCADE (also delete all
PRIMARY
KEY
(sid,cid),
tuples that refer to
FOREIGN KEY (sid)
deleted tuple)
REFERENCES Students
 SET NULL / SET DEFAULT
ON DELETE CASCADE
(sets foreign key value of
ON UPDATE SET DEFAULT )
referencing tuple)
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
28
Where do ICs Come From?
ICs are based upon the semantics of the realworld enterprise that is being described in the
database relations.
 We can check a database instance to see if an IC
is violated, but we can NEVER infer that an IC is
true by looking at an instance.


An IC is a statement about all possible instances!
sid
53666
53688
53650
name
login
Jones jones@cs
Lee
smith@eecs
Smith smith@math
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
age
18
18
19
Name is
a key ?
gpa
3.4
3.2
3.8
29
Logical DB Design: ER to Relational
Entity sets to tables:
ssn
name
lot
Employees
Employees
CREATE TABLE Employees
(ssn CHAR(11),
name CHAR(20),
lot INTEGER,
PRIMARY KEY (ssn))
ssn
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
name
lot
30
Relationship Sets to Tables (1)
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.
All descriptive attributes.
since
name
ssn
did
lot
Employees
dname
Works_In
budget
Departments
Descriptive
attribute
Works_In
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
31
Relationship Sets to Tables (2)
since
name
ssn
did
lot
Employees
dname
Works_In
budget
Departments
CREATE TABLE Works_In(
Works_In
ssn CHAR(1),
ssn
did
did INTEGER,
since DATE,
PRIMARY KEY (ssn, did),
FOREIGN KEY (ssn) REFERENCES Employees,
FOREIGN KEY (did) REFERENCES Departments)
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
since
32
Review: Key Constraints
Each dept has at most one manager, according to
the key constraint on Manages.
Employees
Departments
since
name
ssn
dname
lot
Employees
did
Manages
budget
Departments
1-to Many
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
33
Translating ER Diagrams with Key Constraints
Map relationship
to a table:

Note that did is
the key now!

Separate tables
for Employees and
Departments.
CREATE TABLE Manages(
ssn CHAR(11),
Note that did is the key
did INTEGER,
now – Each
department
can have
since DATE,
only one manager
PRIMARY KEY (did),
FOREIGN KEY (ssn) REFERENCES Employees,
FOREIGN KEY (did) REFERENCES Departments)
name
ssn
dname
since
lot
Employees
did
Manages
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
budget
Departments
34
Translating ER Diagrams with Key Constraints
Since each
department has a
unique manager,
we could instead
combine
Manages and
Departments.
name
ssn
CREATE TABLE Dept_Mgr(
did INTEGER,
dname CHAR(20),
budget REAL,
ssn CHAR(11),
since DATE,
PRIMARY KEY (did),
FOREIGN KEY (ssn) REFERENCES Employees)
dname
since
lot
Employees
did
Manages
budget
Departments
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
Can also use
one table for
both “Manages”
and
“Departments”
Folding them
into one table
35
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).

Every did value in Departments table must appear in a row of
the Manages table (with a non-null ssn value!)
since
name
ssn
did
lot
Employees
dname
Manages
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
budget
Departments
36
Participation Constraints in SQL
since
name
ssn
did
lot
Employees
dname
Manages
budget
Departments
CREATE TABLE Dept_Mgr(
Total participation
did INTEGER,
i.e., Every
dname CHAR(20),
department must
budget REAL,
have a manager
ssn CHAR(11) NOT NULL,
since DATE,
PRIMARY KEY (did),
FOREIGN KEY (ssn) REFERENCES Employees,
ON DELETE NO ACTION)
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
37
Participation Constraints in SQL
We can capture participation constraints involving one
entity set in a binary relationship, but little else (without
resorting to CHECK constraints – discussed later).
since
name
ssn
did
lot
Employees
dname
Manages
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
budget
Departments
38
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 in a
one-to-many relationship set (1 owner, many weak entities).

Weak entity set must have total participation in this
identifying relationship set.
name
ssn
lot
Employees
cost
Policy
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
pname
age
Dependents
39
Translating Weak Entity Sets
name
ssn
lot
Employees
cost
pname
Policy
CREATE TABLE Dep_Policy (
ssn is part of
pname CHAR(20),
primary key →
Implicit NOT NULL
age INTEGER,
cost REAL,
Weak entity set
ssn CHAR(11),
PRIMARY KEY (pname, ssn),
FOREIGN KEY (ssn) REFERENCES Employees,
ON DELETE CASCADE)
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
age
Dependents
Map into
one table
When the owner
entity is deleted,
all owned weak
entities must
also be deleted
40
Review: ISA Hierarchies
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.
name

ssn
lot
Employees
hours_worked
ISA
hourly_wages
contractid
Hourly_Emps


Contract_Emps
Overlap constraints: Can Joe be an Hourly_Emps as well as a
Contract_Emps entity? (Allowed/disallowed)
Covering constraints: Does every Employees entity also have to
be an Hourly_Emps or a Contract_Emps entity? (Yes/no)
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
41
Mapping ISA to Relations
Two approaches:
 Using
three tables
 Using two tables
name
ssn
lot
Employees
hourly_wages
hours_worked
ISA
contractid
Hourly_Emps
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
Contract_Emps
42
Mapping Using Three Relations
Employees
ssn
name
lot
Every employee
is recorded in
Employees
ssn
name
lot
Employees
Contract_Emps
ssn
contractid
hours_worked
ISA
contractid
hourly_wages
Hourly_Emps
ssn Hourly_wages Hours_worked
For hourly employees, extra
information (i.e., Hourly_Wages
and Hours_worked) recorded in
Hourly_Emps
Hourly_Emps
Contract_Emps
 must delete Hourly_Emps tuple
if referenced Employees tuple is
deleted.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
43
Mapping Using Three Relations
Employees
ssn
name
lot
Every employee
is recorded in
Employees
ssn
name
lot
Employees
Contract_Emps
ssn
contratid
hours_worked
ISA
contractid
hourly_wages
Hourly_Emps
ssn Hourly_wages Hours_worked
For hourly employees, extra
information (i.e., Hourly_Wages
and Hours_worked) recorded in
Hourly_Emps
Hourly_Emps
Contract_Emps
• Queries involving all employees easy,
• those involving just Hourly_Emps
require a join to get some attributes
(e.g., name).
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
44
ISA Mapping Using Two Relations
ssn
name
lot
Employees
Each employee
must be in one of
these two relations
hours_worked
ISA
contractid
hourly_wages
Hourly_Emps
Contract_Emps
Hourly_Emps
ssn
name
lot
hourly_wages
lot
Contractid
hours_worked
Contract_Emps
ssn
name
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
45
Mapping ISA Constraints
 Overlap
and covering constraints in ISA
relationships can be expressed in SQL using
assertions
 We
will discuss assertions later
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
46
Mapping Two Binary Relationships (1)
The key constraints allow us to combine Purchaser with Policies,
and Beneficiary and Dependents.
name
ssn
pname
age
CREATE TABLE Policies (
policyid INTEGER,
Dependents
Employees
cost REAL,
ssn CHAR(11) NOT NULL,
Purchaser
Beneficiary
PRIMARY KEY (policyid).
FOREIGN KEY (ssn) REFERENCES Employees,
ON DELETE CASCADE)
Policies
lot
policyid
cost
CREATE TABLE Dependents (
pname CHAR(20),
age INTEGER,
policyid INTEGER,
PRIMARY KEY (pname, policyid),
FOREIGN KEY (policyid) REFERENCES Policies, ON DELETE CASCADE)
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
47
Chain of Weak Entity Sets
What if Policies is also a weak entity set
name
ssn
pname
lot
Employees
age
Primary key:
(pname, policyid, ssn)
Dependents
Purchaser
Beneficiary
Policies
policyid
cost
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
48
Chain of Identifying Relationships for
pname
Weak Entity Sets
name
ssn
lot
age
Dependents
CREATE TABLE Dependents (
Employees
pname CHAR(20),
Beneficiary
from Policies
age INTEGER,
Purchaser
policyid INTEGER,
PRIMARY KEY (pname, policyid),
Policies
FOREIGN KEY (policyid) REFERENCES Policies,
policyid
ON DELETE CASCADE)
cost
CREATE TABLE Dependents (
pname CHAR(20),
from
age INTEGER,
Employees
policyid INTEGER,
ssn INTEGER,
What if Policies is also
PRIMARY KEY (pname, policyid, ssn),
a weak entity set ?
FOREIGN KEY (policyid, ssn) REFERENCES Policies,
ON DELETE CASCADE)
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
49
Views
A view is just a relation, but we store a definition,
rather than a set of tuples.
A relation
View 2
View 1
CREATE VIEW YoungStudents (name, grade)
AS SELECT S.name, E.grade
FROM Students S, Enrolled E
WHERE S.sid = E.sid and S.age<20
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
View
definition
is a query
50
Drop A View
Views can be dropped using the DROP VIEW command.


How to handle DROP TABLE if there’s a view on the table?
DROP TABLE command has options to let the user specify this.
 “DROP TABLE Students RESTRICT” - Removes Students
unless there are related views
 “DROP TABLE Students CASCADE” - Also removes any
referencing views
Students
View
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
51
Views and Security
Views can be used to
 present necessary information
(or a summary),
 while hiding details in underlying relation(s).
Example: Given YoungStudents, but not Students or
Enrolled, we can find young students who have enrolled,
but not the cid’s of the courses they are enrolled in.
CREATE VIEW YoungStudents (name, grade)
AS SELECT S.name, E.grade
FROM Students S, Enrolled E
WHERE S.sid = E.sid and S.age<20
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
52
Summary: ER to Relational Mapping
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
53
ER to Relational Mapping (1)
KEY
KEY
KEY
KEY
KEY
KEY
KEY
KEY
KEY
Foreign Key
Merge
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
54
ER to Relational Mapping (2)
KEY
KEY
KEY
Foreign Key
Merge
KEY
KEY
KEY
Foreign Key
NOT NULL
Merge
KEY
PKEY
KEY
KEY
NOT NULL
PKEY: Partial key
Merge
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
55
ER to Relational Mapping (3)
KEY
Use
three
tables
KEY
OR
ISA
Use
two
tables
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
KEY
56
ER to Relational Mapping (4)
KEY
PKEY
Primary key:
KEY
PKEY
KEY
PKEY
Primary key:
KEY
PKEY
PKEY
Primary key:
KEY
PKEY
KEY
PKEY
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
57
ER to Relational Mapping (5)
KEY
KEY
KEY
KEY
KEY
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
KEY
58
Mapping Example – ER Design
address
phone
Place
ssn
name
Home
Telephone
albumID
Lives
Musicians
Plays
speed
copyrightDate
Producer
Album
Appears
Perform
Instrument
instrID dname key
title
Songs
songID
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
title
author
59
Mapping Example
address
phone
Place
ssn
name
Musicians
Plays
Home
Telephone
albumID
Lives
speed
copyrightDate
CREAT TABLE Home_Telephone
title
( phone
Producer CHAR(11) , Album
address
CHAR(30) NOT NULL,
PRIMARY KEY (phone),
Appears
FOREIGN
KEY
address
REFERENCES
Place
Perform
)
Instrument
instrID dname key
Songs
songID
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
title
author
60
Mapping Example
address
Place
ssn
name
phone
Phone_no
Home
Home_Telephone
Telephone
address
albumID
Lives
Musicians
Plays
Instrument
instrID dname key
speed
copyrightDate
title
CREAT TABLE Lives (
Producer ssn
Album
CHAR(10),
phone
CHAR(11),
PRIMARY KEY (ssn, phone),
Appears
FOREIGN
KEY
phone
Perform
REFERENCES Home_Telephone,
FOREIGN KEYSongs
ssn
REFERENCES Musicians )
songID
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
title
author
61
Mapping Example
address
phone
Home
Place
ssn
name
Telephone
albumID
Lives
Musicians
Plays
Instrument
instrID dname key
speed
copyrightDate
title
CREAT TABLE Plays (
Producer ssn
Album
CHAR(10),
instrID
INTEGER
PRIMARY KEY (ssn, instrID),
Appears
FOREIGN
KEY
(ssn)
Perform
REFERENCES Musicians,
FOREIGN KEYSongs
(instrID)
REFERENCES Instruments )
songID
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
title
author
62
Mapping Example
address
phone
Home
Place
ssn
name
Musicians
Telephone
albumID
Lives
speed
copyrightDate
Producer
title
Album
CREAT TABLE Album_Producer (
Appears
albumID
INTEGER,
Perform
ssn
CHAR(10) NOT NULL,
copyrightDate DATE, Songs
Instrument
speed
INTEGER,
title
CHAR(30)
instrID dname key
songID title author
PRIMARY KEY (albumID),
FOREIGN KEY (ssn) REFERENCES Musicians )
Plays
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
63
Mapping Example
address
phone
Place
ssn
name
Lives
Musicians
Plays
CREAT TABLE Perform (
Home
songID TelephoneINTEGER,
ssn
CHAR(10),
PRIMARY
songID),
albumID KEY (ssn,
speed
FOREIGN
KEY (ssn)
copyrightDate
title
REFERENCES Musicians,
FOREIGN KEY (songID)
Producer
Album
REFERENCES Songs )
Appears
Perform
Instrument
instrID dname key
Songs
songID
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
title
author
64
Mapping Example
address
phone
Place
ssn
name
Musicians
Home
Telephone
albumID
Lives
speed
copyrightDate
Producer
title
Album
CREAT TABLE Plays
Songs_Appears (
Appears
Perform
songID
INTEGER,
author
CHAR(30),
title Instrument CHAR(30),
Songs
albumID
INTEGER NOT NULL,
PRIMARY KEY (songID),
instrID dname key
songID title author
FOREIGN KEY (albumID)
REFERENCES Album_Producer )
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
65
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
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke
66