LIACS Databases Werkcollege 2

Opgaven
LIACS Databases Werkcollege 2
ER Diagrams
Remi Baar
Mark Hoekveen
18-02-2014
Remi Baar, Mark Hoekveen
LIACS Databases Werkcollege 2
Opgaven
Outline
1
Opgaven
3.12, 2.5, 3.15, 3.16, 2.7, 3.17
Optionele opgaven
Remi Baar, Mark Hoekveen
LIACS Databases Werkcollege 2
Opgaven
3.12, 2.5, 3.15, 3.16, 2.7, 3.17
Optionele opgaven
Opgave 3.12 (1)
Consider the scenario from Exercise 2.2, where you designed
an ER diagram for a university database. Write SQL
statements to create the corresponding relations and capture
as many of the constraints as possible. If you cannot capture
some constraints, explain why.
Remi Baar, Mark Hoekveen
LIACS Databases Werkcollege 2
Opgaven
3.12, 2.5, 3.15, 3.16, 2.7, 3.17
Optionele opgaven
Opgave 3.12 (1) Antwoord
CREATE TABLE Teaches ( ssn CHAR(10),
courseId INTEGER,
semester CHAR(10),
PRIMARY KEY (ssn, courseId, semester),
FOREIGN KEY (ssn) REFERENCES Professor,
FOREIGN KEY (courseId) REFERENCES Course,
FOREIGN KEY (semester) REFERENCES Semester )
CREATE TABLE Course ( courseId INTEGER,
PRIMARY KEY (courseId) )
Remi Baar, Mark Hoekveen
LIACS Databases Werkcollege 2
Opgaven
3.12, 2.5, 3.15, 3.16, 2.7, 3.17
Optionele opgaven
Opgave 3.12 (2)
Consider the scenario from Exercise 2.2, where you designed
an ER diagram for a university database. Write SQL
statements to create the corresponding relations and capture
as many of the constraints as possible. If you cannot capture
some constraints, explain why.
Remi Baar, Mark Hoekveen
LIACS Databases Werkcollege 2
Opgaven
3.12, 2.5, 3.15, 3.16, 2.7, 3.17
Optionele opgaven
Opgave 3.12 (2) Antwoord
CREATE TABLE Teaches ( ssn CHAR(10),
courseId INTEGER,
semester CHAR(10),
PRIMARY KEY (ssn, courseId),
FOREIGN KEY (ssn) REFERENCES Professor,
FOREIGN KEY (courseId) REFERENCES Course )
Remi Baar, Mark Hoekveen
LIACS Databases Werkcollege 2
Opgaven
3.12, 2.5, 3.15, 3.16, 2.7, 3.17
Optionele opgaven
Opgave 3.12 (3)
Consider the scenario from Exercise 2.2, where you designed
an ER diagram for a university database. Write SQL
statements to create the corresponding relations and capture
as many of the constraints as possible. If you cannot capture
some constraints, explain why.
Remi Baar, Mark Hoekveen
LIACS Databases Werkcollege 2
Opgaven
3.12, 2.5, 3.15, 3.16, 2.7, 3.17
Optionele opgaven
Opgave 3.12 (3) Antwoord
The tables created for the previous part to this question are the
best we can do without using check constraints or assertions in
SQL. The participation constraint cannot be captured using
only primary and foreign key constraints because we cannot
ensure that every entry in Professor has an entry in Teaches.
Remi Baar, Mark Hoekveen
LIACS Databases Werkcollege 2
Opgaven
3.12, 2.5, 3.15, 3.16, 2.7, 3.17
Optionele opgaven
Opgave 3.12 (4)
Consider the scenario from Exercise 2.2, where you designed
an ER diagram for a university database. Write SQL
statements to create the corresponding relations and capture
as many of the constraints as possible. If you cannot capture
some constraints, explain why.
Remi Baar, Mark Hoekveen
LIACS Databases Werkcollege 2
Opgaven
3.12, 2.5, 3.15, 3.16, 2.7, 3.17
Optionele opgaven
Opgave 3.12 (4) Antwoord
CREATE TABLE Professor_teaches ( ssn CHAR(10),
courseId INTEGER,
semester CHAR(10),
PRIMARY KEY (ssn),
FOREIGN KEY (courseId) REFERENCES Course )
Remi Baar, Mark Hoekveen
LIACS Databases Werkcollege 2
Opgaven
3.12, 2.5, 3.15, 3.16, 2.7, 3.17
Optionele opgaven
Opgave 3.12 (5)
Consider the scenario from Exercise 2.2, where you designed
an ER diagram for a university database. Write SQL
statements to create the corresponding relations and capture
as many of the constraints as possible. If you cannot capture
some constraints, explain why.
Remi Baar, Mark Hoekveen
LIACS Databases Werkcollege 2
Opgaven
3.12, 2.5, 3.15, 3.16, 2.7, 3.17
Optionele opgaven
Opgave 3.12 (5) Antwoord
CREATE TABLE Professor_teaches ( ssn CHAR(10),
courseId INTEGER,
semester CHAR(10),
PRIMARY KEY (ssn) )
Remi Baar, Mark Hoekveen
LIACS Databases Werkcollege 2
Opgaven
3.12, 2.5, 3.15, 3.16, 2.7, 3.17
Optionele opgaven
Opgave 3.12 (6)
Consider the scenario from Exercise 2.2, where you designed
an ER diagram for a university database. Write SQL
statements to create the corresponding relations and capture
as many of the constraints as possible. If you cannot capture
some constraints, explain why.
Remi Baar, Mark Hoekveen
LIACS Databases Werkcollege 2
Opgaven
3.12, 2.5, 3.15, 3.16, 2.7, 3.17
Optionele opgaven
Opgave 3.12 (6) Antwoord
CREATE TABLE Teaches ( gid INTEGER,
courseId INTEGER,
semester CHAR(10),
PRIMARY KEY (gid, courseID),
FOREIGN KEY (gid) REFERENCES Group,
FOREIGN KEY (courseID) REFERENCES Course )
CREATE TABLE MemberOf ( ssn CHAR(10),
gid INTEGER,
PRIMARY KEY (ssn, gid),
FOREIGN KEY (ssn) REFERENCES Professor,
FOREIGN KEY (gid) REFERENCES Group )
Remi Baar, Mark Hoekveen
LIACS Databases Werkcollege 2
Opgaven
3.12, 2.5, 3.15, 3.16, 2.7, 3.17
Optionele opgaven
Opgave 2.5 (1)
Notown Records has decided to store information about musicians who
perform on its albums (as well as other company data) in a database. The
company has wisely chosen to hire you as a database designer (at your
usual consulting fee of $2500/day).
Each musician that records at Notown has an SSN, a name, an
address, and a phone number. Poorly paid musicians often share the
same address, and no address has more than one phone.
Each instrument used in songs recorded at Notown has a unique
identification number, a name (e.g., guitar, synthesizer, flute) and a
musical key (e.g., C, B-flat, E-flat).
Each album recorded on the Notown label has a unique identification
number, a title, a copyright date, a format (e.g., CD or MC), and an
album identifier.
Each song recorded at Notown has a title and an author.
Remi Baar, Mark Hoekveen
LIACS Databases Werkcollege 2
Opgaven
3.12, 2.5, 3.15, 3.16, 2.7, 3.17
Optionele opgaven
Opgave 2.5 (2)
Each musician may play several instruments, and a given instrument
may be played by several musicians.
Each album has a number of songs on it, but no song may appear on
more than one album.
Each song is performed by one or more musicians, and a musician may
perform a number of songs.
Each album has exactly one musician who acts as its producer. A
musician may produce several albums, of course.
Remi Baar, Mark Hoekveen
LIACS Databases Werkcollege 2
Opgaven
3.12, 2.5, 3.15, 3.16, 2.7, 3.17
Optionele opgaven
Antwoord 2.5
Remi Baar, Mark Hoekveen
LIACS Databases Werkcollege 2
Opgaven
3.12, 2.5, 3.15, 3.16, 2.7, 3.17
Optionele opgaven
Opgave 3.15
Consider the Notown database from Exercise 2.5. You have
decided to recommend that Notown use a relational database
system to store company data. Show the SQL statements for
creating relations corresponding to the entity sets and
relationship sets in your design. Identify any constraints in the
ER diagram that you are unable to capture in the SQL
statements and briefly explain why you could not express them.
Remi Baar, Mark Hoekveen
LIACS Databases Werkcollege 2
Opgaven
3.12, 2.5, 3.15, 3.16, 2.7, 3.17
Optionele opgaven
Opgave 3.15 antwoord (1)
1. CREATE TABLE Musicians ( ssn CHAR(10),
name CHAR(30),
PRIMARY KEY (ssn))
2. CREATE TABLE Instruments ( instrId CHAR(10),
dname CHAR(30),
key CHAR(5),
PRIMARY KEY (instrId))
3. CREATE TABLE Plays ( ssn CHAR(10),
instrId INTEGER,
PRIMARY KEY (ssn, instrId),
FOREIGN KEY (ssn) REFERENCES Musicians,
FOREIGN KEY (instrId) REFERENCES Instruments )
Remi Baar, Mark Hoekveen
LIACS Databases Werkcollege 2
Opgaven
3.12, 2.5, 3.15, 3.16, 2.7, 3.17
Optionele opgaven
Opgave 3.15 antwoord (2)
4. CREATE TABLE Songs Appears ( songId INTEGER,
author CHAR(30),
title CHAR(30),
albumIdentifier INTEGER NOT NULL,
PRIMARY KEY (songId),
FOREIGN KEY (albumIdentifier)
References Album Producer,
5. CREATE TABLE Telephone Home ( phone CHAR(11),
address CHAR(30),
PRIMARY KEY (phone),
FOREIGN KEY (address) REFERENCES Place,
Remi Baar, Mark Hoekveen
LIACS Databases Werkcollege 2
Opgaven
3.12, 2.5, 3.15, 3.16, 2.7, 3.17
Optionele opgaven
Opgave 3.15 antwoord (3)
6. CREATE TABLE Lives ( ssn CHAR(10),
phone CHAR(11),
address CHAR(30),
The Relational Model 35
PRIMARY KEY (ssn, address),
FOREIGN KEY (phone, address)
References Telephone Home,
FOREIGN KEY (ssn) REFERENCES Musicians )
7. CREATE TABLE Place ( address CHAR(30) )
Remi Baar, Mark Hoekveen
LIACS Databases Werkcollege 2
Opgaven
3.12, 2.5, 3.15, 3.16, 2.7, 3.17
Optionele opgaven
Opgave 3.15 antwoord (4)
8. CREATE TABLE Perform ( songId INTEGER,
ssn CHAR(10),
PRIMARY KEY (ssn, songId),
FOREIGN KEY (songId) REFERENCES Songs,
FOREIGN KEY (ssn) REFERENCES Musicians )
9. CREATE TABLE Album Producer ( albumIdentifier
INTEGER,
ssn CHAR(10),
copyrightDate DATE,
speed INTEGER,
title CHAR(30),
PRIMARY KEY (albumIdentifier),
FOREIGN KEY (ssn) REFERENCES Musicians )
Remi Baar, Mark Hoekveen
LIACS Databases Werkcollege 2
Opgaven
3.12, 2.5, 3.15, 3.16, 2.7, 3.17
Optionele opgaven
Opgave 3.16
Translate your ER diagram from Exercise 2.6 into a relational
schema, and show the SQL statements needed to create the
relations, using only key and null constraints. If your translation
cannot capture any constraints in the ER diagram, explain why.
In Exercise 2.6, you also modified the ER diagram to include
the constraint that tests on a plane must be conducted by a
technician who is an expert on that model. Can you modify the
SQL statements defining the relations obtained by mapping the
ER diagram to check this constraint?
Remi Baar, Mark Hoekveen
LIACS Databases Werkcollege 2
Opgaven
3.12, 2.5, 3.15, 3.16, 2.7, 3.17
Optionele opgaven
Opgave 3.16 ER-diagram
Remi Baar, Mark Hoekveen
LIACS Databases Werkcollege 2
Opgaven
3.12, 2.5, 3.15, 3.16, 2.7, 3.17
Optionele opgaven
Opgave 3.16 Antwoord (1)
1. CREATE TABLE Expert ( ssn CHAR(11),
model no INTEGER,
PRIMARY KEY (ssn, model no),
FOREIGN KEY (ssn) REFERENCES Technician,
FOREIGN KEY (model no) REFERENCES Models )
The participation constraint cannot be captured in the table.
2. CREATE TABLE Models ( model no INTEGER,
capacity INTEGER,
weight INTEGER,
PRIMARY KEY (model no))
3. CREATE TABLE Employees ( ssn CHAR(11),
union mem no INTEGER,
PRIMARY KEY (ssn))
Remi Baar, Mark Hoekveen
LIACS Databases Werkcollege 2
Opgaven
3.12, 2.5, 3.15, 3.16, 2.7, 3.17
Optionele opgaven
Opgave 3.16 Antwoord (2)
4. CREATE TABLE Technician emp ( ssn CHAR(11),
name CHAR(20),
address CHAR(20),
phone no CHAR(14),
PRIMARY KEY (ssn),
FOREIGN KEY (ssn)
REFERENCES Employees
ON DELETE CASCADE)
5. CREATE TABLE Traffic control emp ( ssn CHAR(11),
exam date DATE,
PRIMARY KEY (ssn),
FOREIGN KEY (ssn)
REFERENCES Employees
ON DELETE CASCADE)
Remi Baar, Mark Hoekveen
LIACS Databases Werkcollege 2
Opgaven
3.12, 2.5, 3.15, 3.16, 2.7, 3.17
Optionele opgaven
Opgave 3.16 Antwoord (3)
6. CREATE TABLE Plane Type ( reg no INTEGER,
model no INTEGER,
PRIMARY KEY (reg no),
FOREIGN KEY (model no) REFERENCES Models)
7. CREATE TABLE Test info ( FFA no INTEGER,
ssn CHAR(11),
reg no INTEGER,
hours INTEGER,
date DATE,
score INTEGER,
PRIMARY KEY (ssn, reg no, FFA no),
FOREIGN KEY (reg no) REFERENCES Plane Type,
FOREIGN KEY (FAA no) REFERENCES Test,
FOREIGN KEY (ssn) REFERENCES Employees )
Remi Baar, Mark Hoekveen
LIACS Databases Werkcollege 2
Opgaven
3.12, 2.5, 3.15, 3.16, 2.7, 3.17
Optionele opgaven
Opgave 3.16 Antwoord (4)
8. The constraint that tests on a plane must be conducted by a
technician who is an expert on that model can be expressed in
SQL as follows.
CREATE TABLE Test info ( FFA no INTEGER,
ssn CHAR(11), reg no INTEGER,
hours INTEGER, date DATE, score INTEGER,
PRIMARY KEY (ssn, reg no, FFA no),
FOREIGN KEY (reg no) REFERENCES Plane Type,
FOREIGN KEY (FAA no) REFERENCES Test,
FOREIGN KEY (ssn) REFERENCES Technician emp )
CONSTRAINT MODEL
CHECK ( SELECT * FROM Expert, Type
WHERE Expert.ssn = ssn AND
Expert.model no = Type.model no AND
Type.reg no = reg no )
Remi Baar, Mark Hoekveen
LIACS Databases Werkcollege 2
Opgaven
3.12, 2.5, 3.15, 3.16, 2.7, 3.17
Optionele opgaven
Opgave 2.7 (1)
The Prescriptions-R-X chain of pharmacies has offered to give you a free
lifetime supply of medicine if you design its database. Given the rising cost of
health care, you agree. Here’s the information that you gather:
Patients are identified by an SSN, and their names, addresses, and
ages must be recorded.
Doctors are identified by an SSN. For each doctor, the name, specialty,
and years of experience must be recorded.
Each pharmaceutical company is identified by name and has a phone
number.
For each drug, the trade name and formula must be recorded. Each
drug is sold by a given pharmaceutical company, and the trade name
identifies a drug uniquely from among the products of that company. If a
pharmaceutical company is deleted, you need not keep track of its
products any longer.
Remi Baar, Mark Hoekveen
LIACS Databases Werkcollege 2
Opgaven
3.12, 2.5, 3.15, 3.16, 2.7, 3.17
Optionele opgaven
Opgave 2.7 (2)
Each pharmacy has a name, address, and phone number.
Every patient has a primary physician. Every doctor has at least one
patient.
Each pharmacy sells several drugs and has a price for each. A drug
could be sold at several pharmacies, and the price could vary from one
pharmacy to another.
Doctors prescribe drugs for patients. A doctor could prescribe one or
more drugs for several patients, and a patient could obtain prescriptions
from several doctors. Each prescription has a date and a quantity
associated with it. You can assume that, if a doctor prescribes the same
drug for the same patient more than once, only the last such
prescription needs to be stored.
Remi Baar, Mark Hoekveen
LIACS Databases Werkcollege 2
Opgaven
3.12, 2.5, 3.15, 3.16, 2.7, 3.17
Optionele opgaven
Opgave 2.7 (3)
Pharmaceutical companies have long-term contracts with pharmacies.
A pharmaceutical company can contract with several pharmacies, and a
pharmacy can contract with several pharmaceutical companies. For
each contract, you have to store a start date, an end date, and the text
of the contract.
Pharmacies appoint a supervisor for each contract. There must always
be a supervisor for each contract, but the contract supervisor can
change over the lifetime of the contract.
1
Draw an ER diagram that captures the preceding information. Identify
any constraints not captured by the ER diagram.
2
How would your design change if each drug must be sold at a fixed
price by all pharmacies?
3
How would your design change if the design requirements change as
follows: If a doctor prescribes the same drug for the same patient more
than once, several such prescriptions may have to be stored.
Remi Baar, Mark Hoekveen
LIACS Databases Werkcollege 2
Opgaven
3.12, 2.5, 3.15, 3.16, 2.7, 3.17
Optionele opgaven
Opgave 2.7 antwoord
Remi Baar, Mark Hoekveen
LIACS Databases Werkcollege 2
Opgaven
3.12, 2.5, 3.15, 3.16, 2.7, 3.17
Optionele opgaven
Opgave 3.17
Consider the ER diagram that you designed for the
Prescriptions-R-X chain of pharmacies in Exercise 2.7. Define
relations corresponding to the entity sets and relationship sets
in your design using SQL.
Remi Baar, Mark Hoekveen
LIACS Databases Werkcollege 2
Opgaven
3.12, 2.5, 3.15, 3.16, 2.7, 3.17
Optionele opgaven
Opgave 3.17 Antwoord (1)
1. CREATE TABLE Pri Phy Patient ( ssn CHAR(11),
name CHAR(20),
age INTEGER, address CHAR(20),
phy ssn CHAR(11),
PRIMARY KEY (ssn),
FOREIGN KEY (phy ssn) REFERENCES Doctor )
2. CREATE TABLE Prescription ( ssn CHAR(11),
phy ssn CHAR(11), date CHAR(11), quantity INTEGER,
trade name CHAR(20), pharm id CHAR(11),
PRIMARY KEY (ssn, phy ssn),
FOREIGN KEY (ssn) REFERENCES Patient,
FOREIGN KEY (phy ssn) REFERENCES Doctor,
FOREIGN KEY (trade name, pharm id)
References Make Drug)
Remi Baar, Mark Hoekveen
LIACS Databases Werkcollege 2
Opgaven
3.12, 2.5, 3.15, 3.16, 2.7, 3.17
Optionele opgaven
Opgave 3.17 Antwoord (2)
3. CREATE TABLE Make Drug (trade name CHAR(20),
pharm id CHAR(11),
PRIMARY KEY (trade name, pharm id),
FOREIGN KEY (trade name) REFERENCES Drug,
FOREIGN KEY (pharm id) REFERENCES Pharm co)
4. CREATE TABLE Sell ( price INTEGER,
name CHAR(10),
trade name CHAR(10),
PRIMARY KEY (name, trade name),
FOREIGN KEY (name) REFERENCES Pharmacy,
FOREIGN KEY (trade name) REFERENCES Drug)
Remi Baar, Mark Hoekveen
LIACS Databases Werkcollege 2
Opgaven
3.12, 2.5, 3.15, 3.16, 2.7, 3.17
Optionele opgaven
Opgave 3.17 Antwoord (3)
5. CREATE TABLE Contract ( name CHAR(20),
pharm id CHAR(11),
start date CHAR(11),
end date CHAR(11),
text CHAR(10000),
supervisor CHAR(20),
PRIMARY KEY (name, pharm id),
FOREIGN KEY (name) REFERENCES Pharmacy,
FOREIGN KEY (pharm id) REFERENCES Pharm co)
Remi Baar, Mark Hoekveen
LIACS Databases Werkcollege 2
Opgaven
3.12, 2.5, 3.15, 3.16, 2.7, 3.17
Optionele opgaven
Optionele opgaven in het boek
3.14 behandelt verdere translatie van weak entity sets
2.8 en 3.18, over het maken van een ER-diagram aan de
hand van tekst, en vervolgens het vertalen ervan naar
SQL.
3.1 tot 3.11 zijn goed om de meer theoretische kennis te
testen.
Remi Baar, Mark Hoekveen
LIACS Databases Werkcollege 2

Download Report