Transcript COP4540 Database Management System Midterm Review

COMP3030 Database
Management System
Final Review
AGENDA
Ch1. Introduction
 Ch2. Relational Model
 Ch3. SQL
 Ch4. Advanced SQL
 Ch6. Database Design and the ER model
 Ch7. Relational Database Design
 Ch10. XML

CH2 EXERCISES

Given two relations R1 and R2, where R1 contains N1 tuples,
R2 contains N2 tuples, and N2 > N1 > 0, give the min and max
possible sizes for the resulting relational algebra expressions:
(1) R1UR2, (2) R1∩R2, (3) R1−R2, (4) R1×R2, (5) σa=5(R1), and (6)
πa(R1)
CH2 EXERCISES

Consider the Supplier-Parts-Catalog schema from the previous
question. State what the following queries compute:

Find the Supplier names of the suppliers who supply a red part that costs
less than 100 dollars.

This Relational Algebra statement does not return anything

Find the Supplier names of the suppliers who supply a red part that costs
less than 100 dollars and a green part that costs less than 100 dollars.
Ch6 EXERCISES
Consider the Supplier-Parts-Catalog schema from the previous
question. State what the following queries compute:


Find the Supplier ids of the suppliers who supply a red part that
costs less than 100 dollars and a green part that costs less than 100
dollars.
AGENDA
Ch1. Introduction
 Ch2. Relational Model
 Ch3. SQL
 Ch4. Advanced SQL
 Ch6. Database Design and the ER model
 Ch7. Relational Database Design
 Ch10. XML

CH3 EXERCISES

What is the difference between a
candidate key and the primary key for a
given relation? What is a superkey?
 The
primary key is the key selected by the
DBA from among the group of candidate keys,
all of which uniquely identify a tuple. A
superkey is a set of attributes that contains a
key.
CH3 EXERCISES

Answer each of the following questions briefly. The questions
are based on the following relational schema:
Emp(eid: integer, ename: string, age: integer, salary: real)
Works(eid: integer, did: integer, pcttime: integer)
Dept(did: integer, dname: string, budget: real, managerid:
integer)
1. Give an example of a foreign key constraint that involves the
Dept relation. What are the options for enforcing this constraint
when a user attempts to delete a Dept tuple?

Consider the following example. It is natural to require that the did field
of Works should be a foreign key, and refer to Dept.
CREATE TABLE Works ( eid INTEGER NOT NULL ,
did INTEGER NOT NULL ,
pcttime INTEGER,
PRIMARY KEY (eid, did),
FOREIGN KEY (did) REFERENCES Dept )
CH3 EXERCISES

2. Write the SQL statements required to create the
preceding relations, including appropriate versions
of all primary and foreign key integrity constraints.
CREATE TABLE Emp (
eid INTEGER,
ename CHAR(10),
age INTEGER,
salary REAL,
PRIMARY KEY (eid)
)
CREATE TABLE Works (
eid INTEGER,
did INTEGER,
pcttime INTEGER,
CREATE TABLE Dept (
did INTEGER,
budget REAL,
managerid INTEGER ,
PRIMARY KEY (eid, did),
FOREIGN KEY (did)
REFERENCES Dept,
FOREIGN KEY (eid)
REFERENCES Emp,
ON DELETE CASCADE
)
PRIMARY KEY (did),
FOREIGN KEY (managerid)
REFERENCES Emp,
ON DELETE SET NULL
)
CH3 EXERCISES

3. Define the Dept relation in SQL so that
every department is guaranteed to have a
manager.
 CREATE
TABLE Dept (
did INTEGER,
budget REAL,
managerid INTEGER NOT NULL ,
PRIMARY KEY (did),
FOREIGN KEY (managerid) REFERENCES Emp
)
CH3 EXERCISES

4. Write an SQL statement to add John
Doe as an employee with eid = 101,
age = 32 and salary = 15, 000.
 INSERT
INTO Emp (eid, ename, age, salary)
VALUES (101, ’John Doe’, 32, 15000)
CH3 EXERCISES

5. Write an SQL statement to give every
employee a 10 percent raise.
 UPDATE
Emp E
SET E.salary = E.salary * 1.10
CH3 EXERCISES

6. Write an SQL statement to delete the
Toy department. Given the referential
integrity constraints you chose for this
schema, explain what happens when
this statement is executed.
 DELETE
FROM Dept D
WHERE D.dname = ’Toy’
CH3 EXERCISES

Consider the following schema:
Suppliers(sid: integer, sname: string, address: string)
Parts(pid: integer, pname: string, color: string)
Catalog(sid: integer, pid: integer, cost: real)
The Catalog relation lists the prices charged for parts by
Suppliers. Write the following queries in SQL:
1. Find the pnames of parts for which there is some supplier.

SELECT DISTINCT P.pname
FROM Parts P, Catalog C
WHERE P.pid = C.pid
5. Find the sids of suppliers who charge more for some part than
the average cost of that part (averaged over all the suppliers
who supply that part).

SELECT DISTINCT C.sid
FROM Catalog C
WHERE C.cost > ( SELECT AVG (C1.cost)
FROM Catalog C1
WHERE C1.pid = C.pid )
Ch3 EXERCISES
8. Find the sids of Suppliers who supply a red parts and a green
part
SELECT DISTINCT C.sid
FROM Catalog C, Parts P
WHERE C.pid = P.pid AND P.color = ‘Red’
INTERSECT
SELECT DISTINCT C1.sid
FROM Catalog C1, Parts P1
WHERE C1.pid = P1.pid AND P1.color = ‘Green’
9. Find the sids of Suppliers who supply a red parts or a green
part
SELECT DISTINCT C.sid FROM Catalog C, Parts P
WHERE C.pid = P.pid AND P.color = ‘Red’
UNION
SELECT DISTINCT C1.sid FROM Catalog C1, Parts P1
WHERE C1.pid = P1.pid AND P1.color = ‘Green’
AGENDA
Ch1. Introduction
 Ch2. Relational Model
 Ch3. SQL
 Ch4. Advanced SQL
 Ch6. Database Design and the ER model
 Ch7. Relational Database Design
 Ch10. XML

CH6 EXERCISES

A university database contains information about
professors (id. by SSN) and courses (id. by courseid). Professors
teach courses; each of the following situations concerns the Teaches
relationship set. For each situation, draw an ER diagram that
describes it (assuming no further constraints hold).
1.
Professors can teach the same course in several
semesters, and each offering must be recorded.
2.
Professors can teach the same course in several
semesters, and only the most recent such
offering needs to be recorded. (Assume this
condition applies in all subsequent questions.)
3.
Every professor must teach
some course.
SSN
Professor
semester
Teaches
courseid
Course
4.
Every professor teaches
exactly one course (no
more, no less).
SSN
Professor
semester
Teaches
courseid
Course
5.
Every professor teaches
exactly one course (no
more, no less), and every
course must be taught by
some professor.
SSN
Professor
semester
Teaches
courseid
Course
6.
Certain courses can be
taught by a team of
professors jointly, but it is
possible that no one
professor in a team can
teach the course. Model
this situation, introducing
additional entity sets and
relationship sets if
necessary.

2.4
A company database needs to store information about
employees (identified by ssn, with salary and phone as attributes),
departments (identified by dno, with dname and budget as attributes),
and children of employees (with name and age as attributes).
Employees work in departments; each department is managed by an
employee; a child must be identified uniquely by name when the parent
(who is an employee; assume that only one parent works for the
company) is known. We are not interested in information about a child
once the parent leaves the company.
salary
SSN
dname
phone
dno
Manages
Employee
Manages
Dependent
Child
name
age
budget
Departments
Convert ER diagram to
relational schema
Employee (ssn, salary, phone, mangerid)
 Departments (dno, dname, budget)
 Child (name, age, ssn)
 Works_in (ssn, dno)

AGENDA
Ch1. Introduction
 Ch2. Relational Model
 Ch3. SQL
 Ch6. Database Design and the ER model
 Ch7. Relational Database Design
 Ch10. XML

CH7 EXERCISES
A relation R is in third normal form if for
every functional dependency of the form X
 A one of the following statements is true:
 A Є X that is, A is a trivial functional
dependency , or (1)
 X is a superkey, or (2)
 A is part of some key for R (3)
A relation R is in BCNF if (1) or (2)
CH7 EXERCISES

7.2. Consider a relation R with five attributes
ABCDE. You are given the following
dependencies:

1.
List all candidate keys for R.

2.
CDE, ACD, BCD
Is R in 3NF?

3.
A → B, BC → E, ED → A
R is in 3NF because B, E and A are all parts of
candidate keys.
Is R in BCNF?

R is not in BCNF because none of A, BC and ED
contain a key.
BCNF, 3NF decomposition
 Canonical cover
 Closure
 4NF

AGENDA
Ch1. Introduction
 Ch2. Relational Model
 Ch3. SQL
 Ch6. Database Design and the ER model
 Ch7. Relational Database Design
 Ch10. XML


Give the DTD for an XML representation of
the following nested-relational schema
 Emp
= (ename, ChildrenSet setof(Children),
SkillsSet setof(Skills))
 Children = (name, Birthday)
 Birthday = (day, month, year)
 Skills = (type, ExamsSet setof(Exams))
 Exams = (year, city)














<!DOCTYPE db [
<!ELEMENT emp (ename, children*, skills*)>
<!ELEMENT children (name, birthday)>
<!ELEMENT birthday (day, month, year)>
<!ELEMENT skills (type, exams+)>
<!ELEMENT exams (year, city)>
<!ELEMENT ename( #PCDATA )>
<!ELEMENT name( #PCDATA )>
<!ELEMENT day( #PCDATA )>
<!ELEMENT month( #PCDATA )>
<!ELEMENT year( #PCDATA )>
<!ELEMENT type( #PCDATA )>
<!ELEMENT city( #PCDATA )>
]>

a. Find the names of all employees who
have a child who has a birthday in March.
for $e in /db/emp,
$m in distinct($e/children/birthday/month)
where $m = ’March’
return $e/ename

b. Find those employees who took an
examination for the skill type “typing” in the city
“Dayton”.
for $e in /db/emp
$s in $e/skills[type=’typing’]
$exam in $s/exams
where $exam/city= ’Dayton’
return $e/ename

c. List all skill types in Emp.
for $t in /db/emp/skills/type
return $t
Final Exam Information
Ch1. Introduction
 Ch2. Relational Model 17
 Ch3. SQL 41
 Ch4. Advanced SQL
 Ch6. Database Design and the ER model 10
 Ch7. Relational Database Design 22
 Ch10. XML 10

GOOD LUCK!!