INFO210_HW5_Solution

# The second candidate key requires that all

This preview shows page 1. Sign up to view the full content.

This is the end of the preview. Sign up to access the rest of the document.

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

Ask a homework question - tutors are online