1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
|
#+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
|
