CMPT-354:Midterm 1998-2

CMPT 354 (Summer, 1998) -- Midterm Exam (60 minutes)

Exam date: Thursday, July 2 Total scores: 100

(30 points) (Entity-relationship data model).

In a company database, you need to store information about employees (identified by social insurance number, with salary and phone number as attributes), departments (identified by department number, with department name 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 can only be identified uniquely (by name) when the parent (who is an employee) is known. Assume that only one parent works for the company. We are not interested in information about a child once the parent leaves the company.

a- 16

Draw an ER diagram that captures the above information.

Answer

b- 8

Using SQL DDL, create relations to hold the same information as the ER model including the primary key(s), foreign key(s), and referential integrity constraints, if any.

Answer

CREATE TABLE employees (SIN char(11),
salary numeric(8,2),
phone char(13),
PRIMARY KEY (SIN))
CREATE TABLE departments (dno integer,
dname char(20),
budget numeric(10,2),
PRIMARY KEY (dno))

CREATE TABLE works_in (SIN char(11),
dno integer,
PRIMARY KEY (SIN,dno),
FOREIGN KEY (SIN) REFERENCES employees
ON DELETE CASCADE,
FOREIGN KEY (dno) REFERENCES departments
ON DELETE CASCADE )

CREATE TABLE children (SIN char(11) NOT NULL,
name char(30) NOT NULL,
age integer,
PRIMARY KEY (SIN,NAME),
FOREIGN KEY (SIN) REFERENCES employees
ON DELETE CASCADE )

c- 6

Paul Smith, with the social insurance number 321 654 987, left the company. Write an SQL statement to delete Paul Smith from the company database.

Answer
DELETE * FROM employees WHERE SIN="321 654 987"

There is no need to delete tuples from Children, works_in or manages because of "ON DELETE CASCADE" clause.

(20 points) (Relational algebra and calculus) Consider the following relational database:
```
Suppliers(sid, sname, city)

Parts(pid, pname, colour, weight)

Catalog(sid, pid, cost) 
```
The Catalog relation lists the prices charged for parts by the suppliers.

a- 6
Using Relational Algebra, find the name of suppliers who supply some red parts.
b- 7
Using Tuple Relational Calculus, find the supplier's id (sid) of suppliers who supply some red or green parts.
c- 7
Using Domain Relational Calculus, find the supplier's id (sid) of suppliers who supply red parts and green parts.
(30 points) (SQL and QBE) Consider the relational database in the previous question.
Express the following queries in SQL:
a- 5
Print the name of parts that have a supplier.
Answer
```
     SELECT P.pname
     FROM Parts P, Catalogue C
     WHERE P.pid = C.pid
```
b- 5
Print the name of the suppliers that supply every (all) known parts.
Answer
```
     SELECT S.sname
     FROM Suppliers S
     WHERE NOT EXIST ((SELECT P.pid
                       FROM Parts P)
                      EXCEPT
                      (SELECT C.pid
                       FROM Catalogue C
                       WHERE C.sid=S.sid))
```
c- 5
Print the supplier's id (sid) of suppliers who charge more for some part that the average cost of that part (averaged over all the suppliers who supply that part).
Answer
```
     SELECT DISTINCT C.sid
     FROM Catalogue C
     WHERE C.cost > (SELECT Avg(K.cost)
                     FROM Catalogue K
                     WHERE K.pid=C.pid)
```
d- 5
Print the supplier's id (sid) of suppliers who supply a red part or a green part.
Answer
```
     SELECT DISTINCT C.sid
     FROM Catalogue C, Parts P
     WHERE C.pid = P.pid AND P.colour="Red"
     UNION
     SELECT DISTINCT K.sid
     FROM Catalogue K, Parts R
     WHERE K.pid = R.pid AND R.colour="Green"
```
Express the following queries in QBE:
a- 5
Print available information about suppliers from Vancouver that supply green parts.
Answer

Suppliers sid sname city

P. _S Vancouver

Parts pid pname colour weight

_P Green

Catalogue sid pid cost

_S _P

b- 5
Print the Name and colour of parts supplied both by a Burnaby supplier and by a Vancouver supplier.
Answer

Suppliers sid sname city

_S1
_S2 Vancouver
Burnaby

Parts pid pname colour weight

_P P. P.

Catalogue sid pid cost

_S1
_S2 _P
_P
(18 points) (Functional Dependencies and Normalization) Consider a relation R with five attributes ABCDE. You are given the following dependencies:
A -> B
BC -> E
ED -> A

a-
List all keys for R.
Answer
BCD, ACD, and ECD are keys of R.

b-
Is R in 3NF?
Answer
Yes, R is in 3NF because B, E, and A are all parts of keys.

b-
Is R in BCNF?
Answer
No, R is in not in BCNF because none of A, BC and ED contain a key.

Suppliers	sid	sname	city
P.	_S		Vancouver

Parts	pid	pname	colour	weight
	_P		Green

Catalogue	sid	pid	cost
	_S	_P

Suppliers	sid	sname	city
	_S1 _S2		Vancouver Burnaby

Parts	pid	pname	colour	weight
	_P	P.	P.

Catalogue	sid	pid	cost
	_S1 _S2	_P _P

(2 points) (Opinion).

I like the exams this style     66%
I dislike the exams this style  34%

I prefer one big problem to solve   5%
I prefer some separate questions   40%
I prefer multiple small questions  55%

The questions are too hard    62%
The questions are too easy     0%
The questions are just right  38%

I have plenty of time to finish the questions        5%
I have just enough time to finish the questions     11%
I do not have enough time to finish the questions   84%

Osmar Zaiane (1998)

CREATE TABLE employees	(SIN char(11), salary numeric(8,2), phone char(13), PRIMARY KEY (SIN))
CREATE TABLE departments	(dno integer, dname char(20), budget numeric(10,2), PRIMARY KEY (dno))
CREATE TABLE works_in	(SIN char(11), dno integer, PRIMARY KEY (SIN,dno), FOREIGN KEY (SIN) REFERENCES employees ON DELETE CASCADE, FOREIGN KEY (dno) REFERENCES departments ON DELETE CASCADE )
CREATE TABLE children	(SIN char(11) NOT NULL, name char(30) NOT NULL, age integer, PRIMARY KEY (SIN,NAME), FOREIGN KEY (SIN) REFERENCES employees ON DELETE CASCADE )