Exam 1 S03 Key
Exam 1 S03 Key
Exam 1 S03 Key
(b) How many different ways are there to represent a relation instance if that
instance has 3 attributes and 2 tuples?
With 3 attributes, any one of them could be listed first, then
of the remaining two, either could be next. So, there’s 3×2 =
6 possibilities for the ordering of the attributes. There’s 2
ways to order the tuples. So, overall, there’s 2×6 = 12 possible
ways to represent the instance.
(5 points total. If no explanation, only 1 point partial credit.)
5. For each of the following, indicate whether the statement is always true (for
every instance of R), sometimes true (that is, you can find some instance of
a relation for which the statement holds), or always false (there is no instance
that will make the statement true).
Given a relation R(A,B,C):
(a) If A → B, then B → A.
Circle one: ALWAYS TRUE SOMETIMES TRUE ALWAYS FALSE
(3 points total, 1 point partial credit for ALWAYS FALSE)
(b) If A → B and B → C, then A → C.
Circle one: ALWAYS TRUE SOMETIMES TRUE ALWAYS FALSE
(3 points total, 1 point partial credit for SOMETIMES TRUE)
(c) If A → B is the only functional dependency, then BC is a key.
Circle one: ALWAYS TRUE SOMETIMES TRUE ALWAYS FALSE
(3 points total, 1 point partial credit for SOMETIMES TRUE)
6. For each of the following types of situations, give an example and draws its
E/R diagram:
(5 points each. No points taken off for missing arrows or keys.
-2 points for wrong example,
-2 points for missing relationship (diamond).)
name name
(b) an one-one relationship:
Person
Wife Husband
Married
7. (a) Draw an E/R diagram for the following situations. Indicate any keys, weak
entity sets, or subclasses:
Entity sets Movies, Stars and Studios. A movie has a title, year (in which
the movie was made), the length, and the film type (either “color” or
“blackAndWhite”). The other two entity sets both have the same at-
tributes: their name and their address. Stars can star in movies. Movies
are owned by studios.
(5 points. See p 26 for diagram.)
(b) Translate your E/R diagram into a relation schema. Indicate keys for
each relation as well as any functional dependencies that hold about each
relation.
(7 points. See Example 3.1 on p 67 for relation schema and Examples
3.16 and 3.17 on p 87-88 for functional dependencies.)
(c) Write the SQL statement that will create the Stars table above.
(4 points)
CREATE TABLE Stars(
name char(30),
address varchar(50),
};
(d) Write the SQL statements that add the following stars to your table that
keeps track of stars:
Harrison Ford, 123 Maple Street
Carrie Fisher, 456 Broadway
(4 points)
INSERT INTO Stars(name, address)
VALUES(’Harrison Ford’, ’123 Maple Street’);
INSERT INTO Stars(name, address)
VALUES(’Carrie Fisher’, ’456 Broadway’);
AB → C
C→D
D→A
(a) What are the keys of R? Note that any key must include B, since
there’s no way to derive B from the functional dependencies that
yield B. So, we only need to check the closure of subsets that
contain B:
A+ = A AB + = ABCD ABC + = ABCD
+
B =B BC + = ABCD ABD+ = ABCD
C + = ACD BD+ = ABCD BCD+ = ABCD
D+ = D
This gives superkeys of AB, BC, BD, ABC, ABD, BCD, and ABCD, and
keys of AB, BC, and BD.
(4 points total.
-1 point for each missing key.)
(b) Indicate all the Boyce Codd Normal Form violations. Do not forget to
consider dependencies that are not in the given set, but follow from them.
However, it is not necessary to give violations that have more than one
attribute on the right side.
C → D, D → A, C → A,
(3 points total.)
(c) Decompose the relations, as necessary, into a collection of relations that
are in Boyce Codd Normal Form.
Decomposing around the BCNF violation, C → D, gives the smaller
relations wtih functional dependencies:
R1 (C, D) R2 (C, A, B)
C→D AB → C
C→A
The key for R1 is C, so R1 is in BCNF.
The key for R2 is AB, so C → A is a BCNF violation. Decomposing
R2 gives:
R3 (C, A) R4 (C, B)
C→A
(3 points total.)
9. Given the company database used in laboratory exercises with the relations:
(2 points)
(b) gives the product codes and the minimum number order, the average num-
ber ordered, and the maximum number ordered:
(3 points)
(c) list all orders that not been paid for:
SELECT *
FROM orders
WHERE ord_paid = NULL;
(2 points)
(d) find each order and its quantity that exceeds the average order quantity
for all orders:
SELECT *
FROM orders
WHERE ord_qty > SELECT AVG(ord_qty) FROM orders;
(3 points)