This preview shows page 1. Sign up to view the full content.
Unformatted text preview: 2 = {B, C}, Key 3 = {B, D}
(b) Consider now an instance of the relation R in (a). Is this a valid instance?
Briefly justify your answer.
A B C D 1 11 5 5 2 22 5 5 3 33 5 5 4 44 5 5 5 55 5 5 6 66 5 5 Solution: Yes, this is a valid instance. The first candidate key requires that all
values in column A be unique, and this is the case. The second candidate key
requires that all combinations of values in columns B and C be unique, and this
also holds. This is because, while all values in column C are the same (C=5 in
all tuples), the values in B are all different from each other. A similar argument
as for {B, C} can be made about the third candidate key, {B, D}. (c) Consider an instance of relation S below. Give two different create table
statements for which this would be a valid instance. A B 1
1
3
3
5
5 C 11
22
33
44
55
66 106
105
104
103
102
101 Solution: There are lots of different options here, some of which are listed below.
Importantly, if you give two solutions that differ only in the order in which columns
are listed – these do not qualify as different solutions.
create table R (
A
number,
B
number primary key,
C
number
); create table R (
A
number,
B
number,
C
number primary key
);
create table R (
A
number not null,
B
number primary key,
C
number unique
);
create table R (
A
number,
B
number primary key,
C
number unique not null
); Part 2 (20 points): Entity relationship modeling (a) Consider create table statements below. Draw and ER diagram from which
these create table statements could have been derived. Be sure to mark any key
and participation constraints. create t able Shelf (
A
number p rimary k ey ,
B
number n ot n ull u nique ,
C
number
);
c reate t able Bag (
D
number p rimary k ey ,
A
number n ot n ull ,
f oreign k ey (A) r eferences Shelf(A)
); Solution: Note that this explanation, and the explanation in (b) below, is
for your benefit, and was not required for this problem, all you had to do is
draw a diagram.
One possible solution is given below. There is another correct solution, in which
B is the key of the entity set Shelf. Note that a key constraint holds over the
entity set Bag (because D is the prima...
View
Full
Document
 Winter '13
 Stoy

Click to edit the document details