InitHEADmain

1 files changed, 90 insertions, 0 deletions

diff --git a/Homework/cs5800/Homeworks/hw-4.org b/Homework/cs5800/Homeworks/hw-4.org
new file mode 100644
index 0000000..3c6f8ba
--- /dev/null
+++ b/Homework/cs5800/Homeworks/hw-4.org
@@ -0,0 +1,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