Homework 4
Table of Contents
1. Question One
The number of super keys here is the cardinality of the set of all sets of attributes that contain at least the clientId. In this case, that's:
- (clientId)
- (clientId, name)
- (clientId, phone)
- (clientId, address)
- (clientId, name, phone)
- (clientId, name, address)
- (clientId, phone, address)
- (clientId, name, phone, address)
Therefore there are eight super keys.
2. Question Two
There is only one primary key which is the collection of (clientId, empId, packageId).
3. Question Three
Salesman has two candidate keys empId and name, if we assume that each salesperson has a unique name. If not, then empId is the only candidate key (and also in this design, the primary key!).
4. Question Four
select clientId, name, phone, address from Client where name="Peter Smith";
5. Question Five
select videoCode from Video where videoLength < 2;
6. Question Six
Making the assumption that all addresses are formatted as "<street no> <street address>". For example "12345 John Blvd":
select siteCode, type, address, phone from Site where right(address, locate(' ', address)-1) = "University Dr";
7. Question Seven
Assuming a siteCode of one:
select administrator.empId, administrator.name, administrator.gender from administers join administrator on administrator.empId=administers.empId where siteCode=1;