#+TITLE: Homework 4
#+AUTHOR: Logan Hunt
#+DATE: October 8, 20222

* Question One
The number of super keys is the cardinality of the set of tuples of attributes that contain at least the clientId. In this case, that is:

1. ~(clientId)~
2. ~(clientId, name)~
3. ~(clientId, phone)~
4. ~(clientId, address)~
5. ~(clientId, name, phone)~
6. ~(clientId, name, address)~
7. ~(clientId, phone, address)~
8. ~(clientId, name, phone, address)~

Therefore there are eight super keys.

* Question Two
There is only one primary key which is ~(clientId, empId, packageId)~.

* 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 (therefore, the primary key!).

* Question Four
#+BEGIN_SRC sql
select clientId, name, phone, address from Client where name="Peter Smith";
#+END_SRC

* Question Five
#+BEGIN_SRC sql
select videoCode from Video where videoLength < 2;
#+END_SRC

* Question Six
Making the assumption that all addresses are formatted as "<street no> <street address>". For example "12345 John Blvd":

#+BEGIN_SRC sql
select siteCode, type, address, phone from Site
         where right(address, locate(' ', address)-1) = "University Dr";
#+END_SRC

* Question Seven
#+BEGIN_SRC sql
select administrator.empId, administrator.name, administrator.gender from administers
       join administrator on administrator.empId=administers.empId
       where siteCode=111;
#+END_SRC

* Question Eight
#+BEGIN_SRC sql
select salesman.empId, client.clientId, client.name, client.phone from client
       join purchases on purchases.clientId = client.clientId
       join salesman on salesman.empId = purchases.empId
       where salesman.name="John";
#+END_SRC

* Question Nine
#+BEGIN_SRC sql
select digital_display.serialNo from digital_display
       join locates on locates.serialNo=digital_display.serialNo
       join site on site.siteCode=locates.siteCode
       where site.siteCode = 112;
#+END_SRC

* Question Ten
#+BEGIN_SRC sql
select distinct(digital_display.schedulerSystem) from digital_display
       join locates on locates.serialNo=digital_display.serialNo
       join site on site.siteCode=locates.siteCode
       where site.siteCode = 112;
#+END_SRC

* Question Eleven
#+BEGIN_SRC sql
select * from model where screenSize > 10 and weight < 3
#+END_SRC

* Question Twelve
#+BEGIN_SRC sql
select digital_display.serialNo, digitial_display.modelNo, digitial_display.schedulerSystem from digitial_display
       join locates on locates.serialNo=digital_display.serialNo
       join site on site.siteCode=locates.siteCode
       where site.type="bar";
#+END_SRC

* Question Thirteen
#+BEGIN_SRC sql
select empId, day from adm_work_hours where hours > 8;
#+END_SRC sql