InitHEADmain

24 files changed, 1779 insertions, 0 deletions

diff --git a/Homework/cs5800/.DS_Store b/Homework/cs5800/.DS_Store
new file mode 100644
index 0000000..2cb5055
--- /dev/null
+++ b/Homework/cs5800/.DS_Store
diff --git a/Homework/cs5800/Exams/1.org b/Homework/cs5800/Exams/1.org
new file mode 100644
index 0000000..51d2fc1
--- /dev/null
+++ b/Homework/cs5800/Exams/1.org
@@ -0,0 +1,21 @@
+* EER and relational (notations, translation, ER -> EER)
+* Languages (DML - DBA or User, DDL - , VDL, SDL)
+* DBMS system components
+User queries and DDL queries paths
+* Conceptual Data Modeling (Focus on min-max) (ER and Enhanced ER model)
+Partial participation, categorization on unions,
+convert to integrity constraints
+
+non-null is an entity integrity constraint
+* Relational model concepts
+** Keys
+(Super key, candidate key, primary key, foreign key)
+studentno is a key, {studentno, birthdate} is a superkey
+sometimes there is more than one key
+* Data Definition Language (DDL)
+** Creating table, adding constraints, table attrributes
+** DBA uses
+** Attributes, primary keys, foreign keys
+
+** Drop table, Alter table
+** Constraints: null/not null, unique, CHECK
diff --git a/Homework/cs5800/Exams/2.study b/Homework/cs5800/Exams/2.study
new file mode 100644
index 0000000..8616d0c
--- /dev/null
+++ b/Homework/cs5800/Exams/2.study
@@ -0,0 +1,27 @@
+delimiter //
+create procedure get_user_deets3(in u_ssn varchar(9), out u_email varchar(64), out u_type varchar(64), out u_name varchar(64)) 
+begin 
+  if u_ssn in (select ssn from user)
+  then
+    select email, type, name
+    into u_email, u_type, u_name
+    from user where ssn=u_ssn;
+  end if;
+end //
+
+delimiter ; 
+
+
+
+delimiter //
+create function before_date (start date)
+returns integer deterministic
+begin
+declare count_before_date integer;
+select count(*) into count_before_date
+from reservation
+where date < start;
+return count_before_date;
+end //
+delimiter ;
+
diff --git a/Homework/cs5800/Exams/thing.sql b/Homework/cs5800/Exams/thing.sql
new file mode 100644
index 0000000..56edb66
--- /dev/null
+++ b/Homework/cs5800/Exams/thing.sql
@@ -0,0 +1,156 @@
+drop table ORDERS;
+drop table PRODUCTS;
+drop table CUSTOMERS;
+drop table OFFICES;
+drop table SALESREPS;
+
+create table ORDERS(
+ORDER_NUM varchar(6),
+ORDER_DATA date,
+CUST char(4),
+REP char(3),		
+MFR char(3),
+PRODUCT varchar(10),
+QTY int, 
+AMOUNT decimal (10,2),
+constraint pk_orders primary key (ORDER_NUM)
+);
+
+create table PRODUCTS(
+MFR_ID char(3),
+PRODUCT_ID varchar(10),
+DESCRIPTION varchar(20),
+PRICE decimal (10,2),
+QTY_ON_HAND int,
+constraint pk_products primary key(MFR_ID, PRODUCT_ID)
+);
+
+create table CUSTOMERS(
+CUST_NUM char(4),
+COMPANY varchar(20),
+CUST_REP char(3),
+CREDIT_LIMIT decimal (10,2),
+constraint pk_customers primary key (CUST_NUM)
+);
+
+create table OFFICES(
+OFFICE char(2),
+CITY varchar(20),
+REGION varchar(10),
+MGR char(3),
+TARGET decimal (10,2),
+SALES decimal (10,2),
+constraint pk_offices primary key (OFFICE)
+);
+
+create table SALESREPS(
+emp_num char(3),
+name varchar(20),
+age int,
+rep_office char(2),
+title varchar(10),
+manager char(3), 
+hire_date date,
+quota decimal (10,2),
+sales decimal (10,2),
+constraint pk_salesRep primary key (emp_num)
+);
+
+INSERT INTO CUSTOMERS VALUES('2101', 'Jones Mfg.', '106', '65000');
+INSERT INTO CUSTOMERS VALUES('2102', 'First Corp.', '101', '65000');
+INSERT INTO CUSTOMERS VALUES('2103', 'Acme Mfg.', '105', '50000');
+INSERT INTO CUSTOMERS VALUES('2105', 'AAA Investments','101', '45000' );
+INSERT INTO CUSTOMERS VALUES('2106', 'Fred Lewis Corp.', '102', '65000');
+INSERT INTO CUSTOMERS VALUES('2107', 'Ace International', '110', '35000');
+INSERT INTO CUSTOMERS VALUES('2108', 'Holm & Landis', '109', '55000');
+INSERT INTO CUSTOMERS VALUES('2109', 'Chen Associates', '103', '25000');
+INSERT INTO CUSTOMERS VALUES('2111', 'JCP Inc.', '103', '50000');
+INSERT INTO CUSTOMERS VALUES('2112', 'Zetacorp', '108', '50000');
+INSERT INTO CUSTOMERS VALUES('2113', 'Ian & Schmidt', '104', '20000');
+INSERT INTO CUSTOMERS VALUES('2114', 'Orion Corp.', '102', '20000');
+INSERT INTO CUSTOMERS VALUES('2115', 'Smithson Corp.', '101', '20000');
+INSERT INTO CUSTOMERS VALUES('2117', 'J.P. Sinclair', '106', '35000');
+INSERT INTO CUSTOMERS VALUES('2118', 'Miswest Sytems', '108', '60000');
+INSERT INTO CUSTOMERS VALUES('2119', 'Solomon Inc.', '109', '25000');
+INSERT INTO CUSTOMERS VALUES('2120', 'Rico Enterprises', '102', '50000');
+INSERT INTO CUSTOMERS VALUES('2121', 'QMA Assoc.', '103', '54000');
+INSERT INTO CUSTOMERS VALUES('2122', 'Three-Way Lines', '105', '30000');
+INSERT INTO CUSTOMERS VALUES('2123', 'Carter & sons', '102', '40000');
+INSERT INTO CUSTOMERS VALUES('2124', 'Peter Brothers', '107', '40000');
+
+INSERT INTO OFFICES VALUES ('11','New York','Eastern','106','575000','692637');
+INSERT INTO OFFICES VALUES ('12','Chicago','Eastern','104','800000','735042');
+INSERT INTO OFFICES VALUES ('13','Atlanta','Eastern','105','350000','367911');
+INSERT INTO OFFICES VALUES ('21','Los Angeles','Western','108','725000','835915');
+INSERT INTO OFFICES VALUES ('22','Denver','Western','108','300000','186042');
+
+INSERT INTO ORDERS VALUES ('112961','1999-12-17','2117','106','REI','2A44L',7,'31500');
+INSERT INTO ORDERS VALUES ('112963','1999-12-17','2103','105','ACI','41004',28,'3276');
+INSERT INTO ORDERS VALUES ('112968','1999-12-10','2102','101','ACI','41004',34,'3978');
+INSERT INTO ORDERS VALUES ('112975','1999-12-10','2111','103','REI','2A44G',6,'2100');
+INSERT INTO ORDERS VALUES ('112979','1999-12-10','2114','102','ACI','4100Z',6,'15000');
+INSERT INTO ORDERS VALUES ('112983','1999-12-27','2103','105','ACI','41004',6,'702');
+INSERT INTO ORDERS VALUES ('112987','1999-12-31','2103','105','ACI','4100Y',11,'27500');
+INSERT INTO ORDERS VALUES ('112989','1900-01-03','2101','106','FEA','114',6,'1458');
+INSERT INTO ORDERS VALUES ('112992','1999-11-04','2118','108','ACI','41002',10,'760');
+INSERT INTO ORDERS VALUES ('112993','1999-01-04','2106','102','REI','2A45C',24,'1896');
+INSERT INTO ORDERS VALUES ('112997','2000-01-08','2124','107','BIC','41003',1,'652');
+INSERT INTO ORDERS VALUES ('113003','2000-01-25','2108','109','IMM','779C',3,'5625');
+INSERT INTO ORDERS VALUES ('113007','2000-01-08','2112','108','IMM','773C',3,'2925');
+INSERT INTO ORDERS VALUES ('113012','2000-01-11','2111','105','ACI','41003',35,'3745');
+INSERT INTO ORDERS VALUES ('113013','2000-01-24','2118','108','BIC','41003',1,'652');
+INSERT INTO ORDERS VALUES ('113024','2000-01-20','2114','108','QSA','XK47',20,'7100');
+INSERT INTO ORDERS VALUES ('113027','2000-01-22','2103','105','ACI','41002',54,'4104');
+INSERT INTO ORDERS VALUES ('113034','2000-01-29','2107','110','REI','2A45C',8,'632');
+INSERT INTO ORDERS VALUES ('113036','2000-01-30','2107','110','ACI','4100Z',9,'22500');
+INSERT INTO ORDERS VALUES ('113042','2000-02-02','2113','101','REI','2A44R',5,'22500');
+INSERT INTO ORDERS VALUES ('113045','2000-02-02','2112','108','REI','2A44R',10,'45000');
+INSERT INTO ORDERS VALUES ('113048','2000-10-02','2120','102','IMM','779C',2,'3750');
+INSERT INTO ORDERS VALUES ('113049','2000-10-02','2118','108','QSA','XK47',2,'776');
+INSERT INTO ORDERS VALUES ('113051','2000-10-02','2118','108','QSA','XK47',4,'1420');
+INSERT INTO ORDERS VALUES ('113055','2000-02-15','2108','101','ACI','4100X',6,'150');
+INSERT INTO ORDERS VALUES ('113057','2000-02-18','2111','103','ACI','4100X',24,'600');
+INSERT INTO ORDERS VALUES ('113058','2000-02-23','2108','109','FEA','112',10,'1480');
+INSERT INTO ORDERS VALUES ('113062','2000-02-24','2124','107','FEA','114',10,'2430');
+INSERT INTO ORDERS VALUES ('113065','2000-02-27','2106','102','QSA','XK47',6,'2130');
+INSERT INTO ORDERS VALUES ('113069','2000-03-02','2109','107','IMM','775C',22,'31350');
+
+INSERT INTO PRODUCTS VALUES ('ACI','41002','Size 2 Widget', '76',167);
+INSERT INTO PRODUCTS VALUES ('ACI','41003','Size 3 Widget','107',207);
+INSERT INTO PRODUCTS VALUES ('ACI','41004','Size 4 Widget','117',139);
+INSERT INTO PRODUCTS VALUES ('ACI','4100X','Widget Adjuster','25',37);
+INSERT INTO PRODUCTS VALUES ('ACI','4100Y','Widget Remover','2750',25);
+INSERT INTO PRODUCTS VALUES ('ACI','4100Z','Size 1 Widget','55',277);
+INSERT INTO PRODUCTS VALUES ('ACI','4101','Widget Intaller','2500',28);
+INSERT INTO PRODUCTS VALUES ('BIC','41003','Handle','652',3);
+INSERT INTO PRODUCTS VALUES ('BIC','41089','Retainer','225',78);
+INSERT INTO PRODUCTS VALUES ('BIC','41675','Plate','180',0);
+INSERT INTO PRODUCTS VALUES ('FEA','112','Housing','148',115);
+INSERT INTO PRODUCTS VALUES ('FEA','114','Motor Mount','243',5);
+INSERT INTO PRODUCTS VALUES ('IMM','773C','300-lb Brace','975',28);
+INSERT INTO PRODUCTS VALUES ('IMM','775C','500 -lb Brace','1425',5);
+INSERT INTO PRODUCTS VALUES ('IMM','779C','900 -lb Brace','1875',9);
+INSERT INTO PRODUCTS VALUES ('IMM','887H','Brace Holder','54',223);
+INSERT INTO PRODUCTS VALUES ('IMM','887P','Brace Pin','250',24);
+INSERT INTO PRODUCTS VALUES ('IMM','887X','Brace Retainer','475',32);
+INSERT INTO PRODUCTS VALUES ('QSA','XK47','Reducer','355',15);
+INSERT INTO PRODUCTS VALUES ('QSA','XK48','Reducer','134',203);
+INSERT INTO PRODUCTS VALUES ('QSA','XK48A','Reducer','177',37);
+INSERT INTO PRODUCTS VALUES ('REI','2A44G','Hinge Pin','350',14);
+INSERT INTO PRODUCTS VALUES ('REI','2A44L','Left Hinge','4500',12);
+INSERT INTO PRODUCTS VALUES ('REI','2A44R','Right Hinge','4500',12);
+INSERT INTO PRODUCTS VALUES ('REI','2A45C','Ratchet Link','79',210);
+
+INSERT INTO SALESREPS VALUES ('101','Dan Roberts',45,'12','Sales Rep', '104', '1996-10-20','300000','305673');
+INSERT INTO SALESREPS VALUES ('102','Sue Smith',48,'21','Sales Rep','108',    '1996-10-12', '350000','474050');
+INSERT INTO SALESREPS VALUES ('103','Paul Cruz',29,'12','Sales Rep','104',    '1997-03-01', '275000','286775');
+INSERT INTO SALESREPS VALUES ('104','Bob Smith',33,'12','Sales Mrg','106',    '1997-05-19', '200000','142594');
+INSERT INTO SALESREPS VALUES ('105','Bill Adams',37,'13','Sales Rep','104',   '1996-02-12', '350000','367911');
+INSERT INTO SALESREPS VALUES ('106','Sam Clark',52,'11','Vp Sales',  null,    '1998-06-14', '275000','299912');
+INSERT INTO SALESREPS VALUES ('107','Nancy Angelli',49,'22','Sales Rep','108','1998-11-14','300000','186042');
+INSERT INTO SALESREPS VALUES ('108','Larry Fitch',62,'21','Sales Mrg',  '106','1999-10-12', '350000','361865');
+INSERT INTO SALESREPS VALUES ('109','Mary Jones',31,'11','Sales Rep',   '106','1999-10-12','300000','392725');
+INSERT INTO SALESREPS VALUES ('110','Tom Snyder',41,NULL,'Sales Rep',   '101','2000-01-13', NULL,'75985');
+
+select * from SALESREPS;
+
diff --git a/Homework/cs5800/Homeworks/hw-3.org b/Homework/cs5800/Homeworks/hw-3.org
new file mode 100644
index 0000000..acd377e
--- /dev/null
+++ b/Homework/cs5800/Homeworks/hw-3.org
@@ -0,0 +1,64 @@
+#+TITLE: Homework 3
+#+AUTHOR: Logan Hunt
+#+STARTUP: fold inlineimages
+#+OPTIONS: toc:nil
+#+LATEX_HEADER: \usepackage{amsfonts} \usepackage{amssymb} \usepackage{mathtools} \usepackage{ upgreek } \usepackage{ textcomp }
+
+* Question 1 (3.21)
+[[./img/representatives.png]]
+
+The state and congress_person entities are just what was given in the requirements.
+
+The relation between them - represent - describes which congress people are representatives in a certain state. The homework assumptions include the fact that a state has at most 53 representative congress people, and at minimum one. On the other side of the relationship, a congress person can only be a representative in one state.
+
+Additionally, the bill entity was constructed from the requirements, and the relation "vote" describes, in addition with its own property "decision" which keeps the vote type from the congress person, the votes of each congress person.
+As there are $m$ bills, each congress person is required to have $m$ votes, and each bill must have 435 votes from congress people.
+
+Finally, a congress person sponsors either none or all of the bills ($m$), and each bill needs at least 1 sponsor and a maximum of 435.
+* Question 2 (3.23)
+** a) Strong Entities
++ Bank
++ Loan
++ Account
++ Customer
+
+** b) Weak Entities
++ Bank branch
+  - Partial key: Branch Number
+  - Identifying relationship: "Branches"
+
+** c) Constraints 
++ The participation of Bank-Branch in Branches is a total participation.
++ Cardinality ratio from bank to branches is 1:N.
+
+** d) Relationships
++ Bank (1,n) <=Branches=> (1,1) Bank-Branch. A bank has at least one branch and a branch is only of one bank, hence the (1,1).
++ Bank-Branch (0,n) <=Accts=> (1,1) Account. A bank branch can have none or any number of accounts opened in it, but an account can only opened in one branch.
++ Acccount (1,n) <=A-C=> (0,m) Customer. An account can be owned by at least one customer (a joint account for married individuals is an example of more than one), and a customer can own either none or any number of accounts.
++ Bank-Branch (0,n) <=Loans=> (1,1) Loan. A bank branch can have none or any number of loans taken out in it, but a loan can only be taken out in one branch.
++ Loan (1,n) <=L-C=> (0,m) Customer. 
+
+** e) Requirements
+A bank is an entity with a unique code, a name, and address. A bank must have one or more branches at some address. Together with the bank's code and branch number, a branch can be uniquely identified.
+
+Branches have the ability to create accounts of any account type, and also have a tracking balance and unique account number. Any number of customers can participate in owning the account, but it must be at least one customer. However some customers may end up with no accounts.
+
+Customers also have the ability to take out any number of loans, with an amount lent and type of loan included with a unique loan number, including zero. However, any loan that is taken out must have at least one customer owning it.
+
+** f) Further Restrictions 
+To support only letting a customer take out two loans at a time, the relation L-C should be updated: Loan (1,n) <=L-C=> (0,2) Customer. 
+
+To support only letting a branch take out a maximum of 1000 loans at a time, the relation Loans should be updated: Bank-Branch (0,1000) <=Loans=> (1,1) Loan. 
+
+* Question 3 (3.24)
+[[./img/employee-departments-phones.png]]
+
+If each of every employee's has-phone relationship is fully contained in the employee's departments then the two relationships has-phone and contains are redundant because one could query all the departments an employee works for to get that employee's "phones".
+
+* Question 4 (3.25)
+
+[[./img/teaches-course-text.png]]
+
+A ternary relationship would not fit because a text can only be used in one course and a course can only be instructed by one instructor.
+
+Adopts would have the following cardinality constraint: Instructor (0, 20) <=Adopt=> (1,1) Text - as an instructor can teach two to four courses and each course can have 0 to 5 texts, an instructor can end up adopting 0 texts, or 5 unique texts for all 4 of their courses. But, as a text can only have one course, and a course can only be taught by one teacher, a text must be adopted by one and only one teacher.
diff --git a/Homework/cs5800/Homeworks/hw-3.pdf b/Homework/cs5800/Homeworks/hw-3.pdf
new file mode 100644
index 0000000..e8df7c2
--- /dev/null
+++ b/Homework/cs5800/Homeworks/hw-3.pdf
diff --git a/Homework/cs5800/Homeworks/hw-3.tex b/Homework/cs5800/Homeworks/hw-3.tex
new file mode 100644
index 0000000..184506f
--- /dev/null
+++ b/Homework/cs5800/Homeworks/hw-3.tex
@@ -0,0 +1,114 @@
+% Created 2022-09-28 Wed 22:36
+% Intended LaTeX compiler: pdflatex
+\documentclass[11pt]{article}
+\usepackage[utf8]{inputenc}
+\usepackage[T1]{fontenc}
+\usepackage{graphicx}
+\usepackage{longtable}
+\usepackage{wrapfig}
+\usepackage{rotating}
+\usepackage[normalem]{ulem}
+\usepackage{amsmath}
+\usepackage{amssymb}
+\usepackage{capt-of}
+\usepackage{hyperref}
+\usepackage{amsfonts} \usepackage{amssymb} \usepackage{mathtools} \usepackage{ upgreek } \usepackage{ textcomp }
+\author{Logan Hunt}
+\date{\today}
+\title{Homework 3}
+\hypersetup{
+ pdfauthor={Logan Hunt},
+ pdftitle={Homework 3},
+ pdfkeywords={},
+ pdfsubject={},
+ pdfcreator={Emacs 28.2 (Org mode 9.5.5)}, 
+ pdflang={English}}
+\begin{document}
+
+\maketitle
+
+\section{Question 1 (3.21)}
+\label{sec:orge5708f1}
+\begin{center}
+\includegraphics[width=.9\linewidth]{./img/representatives.png}
+\end{center}
+
+The state and congress\textsubscript{person} entities are just what was given in the requirements.
+
+The relation between them - represent - describes which congress people are representatives in a certain state. The homework assumptions include the fact that a state has at most 53 representative congress people, and at minimum one. On the other side of the relationship, a congress person can only be a representative in one state.
+
+Additionally, the bill entity was constructed from the requirements, and the relation "vote" describes, in addition with its own property "decision" which keeps the vote type from the congress person, the votes of each congress person.
+As there are \(m\) bills, each congress person is required to have \(m\) votes, and each bill must have 435 votes from congress people.
+
+Finally, a congress person sponsors either none or all of the bills (\(m\)), and each bill needs at least 1 sponsor and a maximum of 435.
+\section{Question 2 (3.23)}
+\label{sec:orgb0cbc0f}
+\subsection{a) Strong Entities}
+\label{sec:orge85bbf3}
+\begin{itemize}
+\item Bank
+\item Loan
+\item Account
+\item Customer
+\end{itemize}
+
+\subsection{b) Weak Entities}
+\label{sec:org5ae462d}
+\begin{itemize}
+\item Bank branch
+\begin{itemize}
+\item Partial key: Branch Number
+\item Identifying relationship: "Branches"
+\end{itemize}
+\end{itemize}
+
+\subsection{c) Constraints}
+\label{sec:org7162d84}
+\begin{itemize}
+\item The participation of Bank-Branch in Branches is a total participation.
+\item Cardinality ratio from bank to branches is 1:N.
+\end{itemize}
+
+\subsection{d) Relationships}
+\label{sec:org5fbbd4e}
+\begin{itemize}
+\item Bank (1,n) <=Branches=> (1,1) Bank-Branch. A bank has at least one branch and a branch is only of one bank, hence the (1,1).
+\item Bank-Branch (0,n) <=Accts=> (1,1) Account. A bank branch can have none or any number of accounts opened in it, but an account can only opened in one branch.
+\item Acccount (1,n) <=A-C=> (0,m) Customer. An account can be owned by at least one customer (a joint account for married individuals is an example of more than one), and a customer can own either none or any number of accounts.
+\item Bank-Branch (0,n) <=Loans=> (1,1) Loan. A bank branch can have none or any number of loans taken out in it, but a loan can only be taken out in one branch.
+\item Loan (1,n) <=L-C=> (0,m) Customer.
+\end{itemize}
+
+\subsection{e) Requirements}
+\label{sec:org0cce2c4}
+A bank is an entity with a unique code, a name, and address. A bank must have one or more branches at some address. Together with the bank's code and branch number, a branch can be uniquely identified.
+
+Branches have the ability to create accounts of any account type, and also have a tracking balance and unique account number. Any number of customers can participate in owning the account, but it must be at least one customer. However some customers may end up with no accounts.
+
+Customers also have the ability to take out any number of loans, with an amount lent and type of loan included with a unique loan number, including zero. However, any loan that is taken out must have at least one customer owning it.
+
+\subsection{f) Further Restrictions}
+\label{sec:orgc3396fe}
+To support only letting a customer take out two loans at a time, the relation L-C should be updated: Loan (1,n) <=L-C=> (0,2) Customer. 
+
+To support only letting a branch take out a maximum of 1000 loans at a time, the relation Loans should be updated: Bank-Branch (0,1000) <=Loans=> (1,1) Loan. 
+
+\section{Question 3 (3.24)}
+\label{sec:org4cdca4d}
+\begin{center}
+\includegraphics[width=.9\linewidth]{./img/employee-departments-phones.png}
+\end{center}
+
+If each of every employee's has-phone relationship is fully contained in the employee's departments then the two relationships has-phone and contains are redundant because one could query all the departments an employee works for to get that employee's "phones".
+
+\section{Question 4 (3.25)}
+\label{sec:org8a15543}
+
+\begin{center}
+\includegraphics[width=.9\linewidth]{./img/teaches-course-text.png}
+\end{center}
+
+A ternary relationship would not fit because a text can only be used in one course and a course can only be instructed by one instructor.
+
+Adopts would have the following cardinality constraint: Instructor (0, 20) <=Adopt=> (1,1) Text - as an instructor can teach two to four courses and each course can have 0 to 5 texts, an instructor can end up adopting 0 texts, or 5 unique texts for all 4 of their courses. But, as a text can only have one course, and a course can only be taught by one teacher, a text must be adopted by one and only one teacher.
+\end{document}
\ No newline at end of file
diff --git a/Homework/cs5800/Homeworks/hw-4.html b/Homework/cs5800/Homeworks/hw-4.html
new file mode 100644
index 0000000..b89d2b8
--- /dev/null
+++ b/Homework/cs5800/Homeworks/hw-4.html
@@ -0,0 +1,311 @@
+<?xml version="1.0" encoding="utf-8"?>
+<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
+"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
+<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">
+<head>
+<!-- 2022-10-08 Sat 18:41 -->
+<meta http-equiv="Content-Type" content="text/html;charset=utf-8" />
+<meta name="viewport" content="width=device-width, initial-scale=1" />
+<title>Homework 4</title>
+<meta name="author" content="Logan Hunt" />
+<meta name="generator" content="Org Mode" />
+<style>
+  #content { max-width: 60em; margin: auto; }
+  .title  { text-align: center;
+             margin-bottom: .2em; }
+  .subtitle { text-align: center;
+              font-size: medium;
+              font-weight: bold;
+              margin-top:0; }
+  .todo   { font-family: monospace; color: red; }
+  .done   { font-family: monospace; color: green; }
+  .priority { font-family: monospace; color: orange; }
+  .tag    { background-color: #eee; font-family: monospace;
+            padding: 2px; font-size: 80%; font-weight: normal; }
+  .timestamp { color: #bebebe; }
+  .timestamp-kwd { color: #5f9ea0; }
+  .org-right  { margin-left: auto; margin-right: 0px;  text-align: right; }
+  .org-left   { margin-left: 0px;  margin-right: auto; text-align: left; }
+  .org-center { margin-left: auto; margin-right: auto; text-align: center; }
+  .underline { text-decoration: underline; }
+  #postamble p, #preamble p { font-size: 90%; margin: .2em; }
+  p.verse { margin-left: 3%; }
+  pre {
+    border: 1px solid #e6e6e6;
+    border-radius: 3px;
+    background-color: #f2f2f2;
+    padding: 8pt;
+    font-family: monospace;
+    overflow: auto;
+    margin: 1.2em;
+  }
+  pre.src {
+    position: relative;
+    overflow: auto;
+  }
+  pre.src:before {
+    display: none;
+    position: absolute;
+    top: -8px;
+    right: 12px;
+    padding: 3px;
+    color: #555;
+    background-color: #f2f2f299;
+  }
+  pre.src:hover:before { display: inline; margin-top: 14px;}
+  /* Languages per Org manual */
+  pre.src-asymptote:before { content: 'Asymptote'; }
+  pre.src-awk:before { content: 'Awk'; }
+  pre.src-authinfo::before { content: 'Authinfo'; }
+  pre.src-C:before { content: 'C'; }
+  /* pre.src-C++ doesn't work in CSS */
+  pre.src-clojure:before { content: 'Clojure'; }
+  pre.src-css:before { content: 'CSS'; }
+  pre.src-D:before { content: 'D'; }
+  pre.src-ditaa:before { content: 'ditaa'; }
+  pre.src-dot:before { content: 'Graphviz'; }
+  pre.src-calc:before { content: 'Emacs Calc'; }
+  pre.src-emacs-lisp:before { content: 'Emacs Lisp'; }
+  pre.src-fortran:before { content: 'Fortran'; }
+  pre.src-gnuplot:before { content: 'gnuplot'; }
+  pre.src-haskell:before { content: 'Haskell'; }
+  pre.src-hledger:before { content: 'hledger'; }
+  pre.src-java:before { content: 'Java'; }
+  pre.src-js:before { content: 'Javascript'; }
+  pre.src-latex:before { content: 'LaTeX'; }
+  pre.src-ledger:before { content: 'Ledger'; }
+  pre.src-lisp:before { content: 'Lisp'; }
+  pre.src-lilypond:before { content: 'Lilypond'; }
+  pre.src-lua:before { content: 'Lua'; }
+  pre.src-matlab:before { content: 'MATLAB'; }
+  pre.src-mscgen:before { content: 'Mscgen'; }
+  pre.src-ocaml:before { content: 'Objective Caml'; }
+  pre.src-octave:before { content: 'Octave'; }
+  pre.src-org:before { content: 'Org mode'; }
+  pre.src-oz:before { content: 'OZ'; }
+  pre.src-plantuml:before { content: 'Plantuml'; }
+  pre.src-processing:before { content: 'Processing.js'; }
+  pre.src-python:before { content: 'Python'; }
+  pre.src-R:before { content: 'R'; }
+  pre.src-ruby:before { content: 'Ruby'; }
+  pre.src-sass:before { content: 'Sass'; }
+  pre.src-scheme:before { content: 'Scheme'; }
+  pre.src-screen:before { content: 'Gnu Screen'; }
+  pre.src-sed:before { content: 'Sed'; }
+  pre.src-sh:before { content: 'shell'; }
+  pre.src-sql:before { content: 'SQL'; }
+  pre.src-sqlite:before { content: 'SQLite'; }
+  /* additional languages in org.el's org-babel-load-languages alist */
+  pre.src-forth:before { content: 'Forth'; }
+  pre.src-io:before { content: 'IO'; }
+  pre.src-J:before { content: 'J'; }
+  pre.src-makefile:before { content: 'Makefile'; }
+  pre.src-maxima:before { content: 'Maxima'; }
+  pre.src-perl:before { content: 'Perl'; }
+  pre.src-picolisp:before { content: 'Pico Lisp'; }
+  pre.src-scala:before { content: 'Scala'; }
+  pre.src-shell:before { content: 'Shell Script'; }
+  pre.src-ebnf2ps:before { content: 'ebfn2ps'; }
+  /* additional language identifiers per "defun org-babel-execute"
+       in ob-*.el */
+  pre.src-cpp:before  { content: 'C++'; }
+  pre.src-abc:before  { content: 'ABC'; }
+  pre.src-coq:before  { content: 'Coq'; }
+  pre.src-groovy:before  { content: 'Groovy'; }
+  /* additional language identifiers from org-babel-shell-names in
+     ob-shell.el: ob-shell is the only babel language using a lambda to put
+     the execution function name together. */
+  pre.src-bash:before  { content: 'bash'; }
+  pre.src-csh:before  { content: 'csh'; }
+  pre.src-ash:before  { content: 'ash'; }
+  pre.src-dash:before  { content: 'dash'; }
+  pre.src-ksh:before  { content: 'ksh'; }
+  pre.src-mksh:before  { content: 'mksh'; }
+  pre.src-posh:before  { content: 'posh'; }
+  /* Additional Emacs modes also supported by the LaTeX listings package */
+  pre.src-ada:before { content: 'Ada'; }
+  pre.src-asm:before { content: 'Assembler'; }
+  pre.src-caml:before { content: 'Caml'; }
+  pre.src-delphi:before { content: 'Delphi'; }
+  pre.src-html:before { content: 'HTML'; }
+  pre.src-idl:before { content: 'IDL'; }
+  pre.src-mercury:before { content: 'Mercury'; }
+  pre.src-metapost:before { content: 'MetaPost'; }
+  pre.src-modula-2:before { content: 'Modula-2'; }
+  pre.src-pascal:before { content: 'Pascal'; }
+  pre.src-ps:before { content: 'PostScript'; }
+  pre.src-prolog:before { content: 'Prolog'; }
+  pre.src-simula:before { content: 'Simula'; }
+  pre.src-tcl:before { content: 'tcl'; }
+  pre.src-tex:before { content: 'TeX'; }
+  pre.src-plain-tex:before { content: 'Plain TeX'; }
+  pre.src-verilog:before { content: 'Verilog'; }
+  pre.src-vhdl:before { content: 'VHDL'; }
+  pre.src-xml:before { content: 'XML'; }
+  pre.src-nxml:before { content: 'XML'; }
+  /* add a generic configuration mode; LaTeX export needs an additional
+     (add-to-list 'org-latex-listings-langs '(conf " ")) in .emacs */
+  pre.src-conf:before { content: 'Configuration File'; }
+
+  table { border-collapse:collapse; }
+  caption.t-above { caption-side: top; }
+  caption.t-bottom { caption-side: bottom; }
+  td, th { vertical-align:top;  }
+  th.org-right  { text-align: center;  }
+  th.org-left   { text-align: center;   }
+  th.org-center { text-align: center; }
+  td.org-right  { text-align: right;  }
+  td.org-left   { text-align: left;   }
+  td.org-center { text-align: center; }
+  dt { font-weight: bold; }
+  .footpara { display: inline; }
+  .footdef  { margin-bottom: 1em; }
+  .figure { padding: 1em; }
+  .figure p { text-align: center; }
+  .equation-container {
+    display: table;
+    text-align: center;
+    width: 100%;
+  }
+  .equation {
+    vertical-align: middle;
+  }
+  .equation-label {
+    display: table-cell;
+    text-align: right;
+    vertical-align: middle;
+  }
+  .inlinetask {
+    padding: 10px;
+    border: 2px solid gray;
+    margin: 10px;
+    background: #ffffcc;
+  }
+  #org-div-home-and-up
+   { text-align: right; font-size: 70%; white-space: nowrap; }
+  textarea { overflow-x: auto; }
+  .linenr { font-size: smaller }
+  .code-highlighted { background-color: #ffff00; }
+  .org-info-js_info-navigation { border-style: none; }
+  #org-info-js_console-label
+    { font-size: 10px; font-weight: bold; white-space: nowrap; }
+  .org-info-js_search-highlight
+    { background-color: #ffff00; color: #000000; font-weight: bold; }
+  .org-svg { }
+</style>
+</head>
+<body>
+<div id="content" class="content">
+<h1 class="title">Homework 4</h1>
+<div id="table-of-contents" role="doc-toc">
+<h2>Table of Contents</h2>
+<div id="text-table-of-contents" role="doc-toc">
+<ul>
+<li><a href="#org9064b3f">1. Question One</a></li>
+<li><a href="#orgb7b028b">2. Question Two</a></li>
+<li><a href="#org9ad8b30">3. Question Three</a></li>
+<li><a href="#org0b8ba7c">4. Question Four</a></li>
+<li><a href="#orgc06dbaa">5. Question Five</a></li>
+<li><a href="#org1205c21">6. Question Six</a></li>
+<li><a href="#org01c7e07">7. Question Seven</a></li>
+</ul>
+</div>
+</div>
+
+<div id="outline-container-org9064b3f" class="outline-2">
+<h2 id="org9064b3f"><span class="section-number-2">1.</span> Question One</h2>
+<div class="outline-text-2" id="text-1">
+<p>
+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:
+</p>
+
+<ol class="org-ol">
+<li>(clientId)</li>
+<li>(clientId, name)</li>
+<li>(clientId, phone)</li>
+<li>(clientId, address)</li>
+<li>(clientId, name, phone)</li>
+<li>(clientId, name, address)</li>
+<li>(clientId, phone, address)</li>
+<li>(clientId, name, phone, address)</li>
+</ol>
+
+<p>
+Therefore there are eight super keys.
+</p>
+</div>
+</div>
+
+<div id="outline-container-orgb7b028b" class="outline-2">
+<h2 id="orgb7b028b"><span class="section-number-2">2.</span> Question Two</h2>
+<div class="outline-text-2" id="text-2">
+<p>
+There is only one primary key which is the collection of (clientId, empId, packageId).
+</p>
+</div>
+</div>
+
+<div id="outline-container-org9ad8b30" class="outline-2">
+<h2 id="org9ad8b30"><span class="section-number-2">3.</span> Question Three</h2>
+<div class="outline-text-2" id="text-3">
+<p>
+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!).
+</p>
+</div>
+</div>
+
+<div id="outline-container-org0b8ba7c" class="outline-2">
+<h2 id="org0b8ba7c"><span class="section-number-2">4.</span> Question Four</h2>
+<div class="outline-text-2" id="text-4">
+<div class="org-src-container">
+<pre class="src src-sql">select clientId, name, phone, address from Client where name="Peter Smith";
+</pre>
+</div>
+</div>
+</div>
+
+<div id="outline-container-orgc06dbaa" class="outline-2">
+<h2 id="orgc06dbaa"><span class="section-number-2">5.</span> Question Five</h2>
+<div class="outline-text-2" id="text-5">
+<div class="org-src-container">
+<pre class="src src-sql">select videoCode from Video where videoLength &lt; 2;
+</pre>
+</div>
+</div>
+</div>
+
+<div id="outline-container-org1205c21" class="outline-2">
+<h2 id="org1205c21"><span class="section-number-2">6.</span> Question Six</h2>
+<div class="outline-text-2" id="text-6">
+<p>
+Making the assumption that all addresses are formatted as "&lt;street no&gt; &lt;street address&gt;". For example "12345 John Blvd":
+</p>
+
+<div class="org-src-container">
+<pre class="src src-sql">select siteCode, type, address, phone from Site where right(address, locate(' ', address)-1) = "University Dr";
+</pre>
+</div>
+</div>
+</div>
+
+<div id="outline-container-org01c7e07" class="outline-2">
+<h2 id="org01c7e07"><span class="section-number-2">7.</span> Question Seven</h2>
+<div class="outline-text-2" id="text-7">
+<p>
+Assuming a siteCode of one:
+</p>
+
+<div class="org-src-container">
+<pre class="src src-sql">select administrator.empId, administrator.name, administrator.gender from administers join administrator on administrator.empId=administers.empId where siteCode=1;
+</pre>
+</div>
+</div>
+</div>
+</div>
+<div id="postamble" class="status">
+<p class="author">Author: Logan Hunt</p>
+<p class="date">Created: 2022-10-08 Sat 18:41</p>
+<p class="validation"><a href="https://validator.w3.org/check?uri=referer">Validate</a></p>
+</div>
+</body>
+</html>
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
diff --git a/Homework/cs5800/Homeworks/hw-4.pdf b/Homework/cs5800/Homeworks/hw-4.pdf
new file mode 100644
index 0000000..d89c162
--- /dev/null
+++ b/Homework/cs5800/Homeworks/hw-4.pdf
diff --git a/Homework/cs5800/Homeworks/hw-4.tex b/Homework/cs5800/Homeworks/hw-4.tex
new file mode 100644
index 0000000..c832828
--- /dev/null
+++ b/Homework/cs5800/Homeworks/hw-4.tex
@@ -0,0 +1,131 @@
+% Created 2022-10-31 Mon 14:58
+% Intended LaTeX compiler: pdflatex
+\documentclass[11pt]{article}
+\usepackage[utf8]{inputenc}
+\usepackage[T1]{fontenc}
+\usepackage{graphicx}
+\usepackage{longtable}
+\usepackage{wrapfig}
+\usepackage{rotating}
+\usepackage[normalem]{ulem}
+\usepackage{amsmath}
+\usepackage{amssymb}
+\usepackage{capt-of}
+\usepackage{hyperref}
+\author{Logan Hunt}
+\date{\today}
+\title{Homework 4}
+\hypersetup{
+ pdfauthor={Logan Hunt},
+ pdftitle={Homework 4},
+ pdfkeywords={},
+ pdfsubject={},
+ pdfcreator={Emacs 28.2.50 (Org mode 9.5.5)}, 
+ pdflang={English}}
+\begin{document}
+
+\maketitle
+\tableofcontents
+
+
+\section{Question One}
+\label{sec:org7142899}
+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:
+
+\begin{enumerate}
+\item \texttt{(clientId)}
+\item \texttt{(clientId, name)}
+\item \texttt{(clientId, phone)}
+\item \texttt{(clientId, address)}
+\item \texttt{(clientId, name, phone)}
+\item \texttt{(clientId, name, address)}
+\item \texttt{(clientId, phone, address)}
+\item \texttt{(clientId, name, phone, address)}
+\end{enumerate}
+
+Therefore there are eight super keys.
+
+\section{Question Two}
+\label{sec:org808ee68}
+There is only one primary key which is \texttt{(clientId, empId, packageId)}.
+
+\section{Question Three}
+\label{sec:orgdde212d}
+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!).
+
+\section{Question Four}
+\label{sec:org9bc5fbb}
+\begin{verbatim}
+select clientId, name, phone, address from Client where name="Peter Smith";
+\end{verbatim}
+
+\section{Question Five}
+\label{sec:orgaaa81c5}
+\begin{verbatim}
+select videoCode from Video where videoLength < 2;
+\end{verbatim}
+
+\section{Question Six}
+\label{sec:org55e6ae2}
+Making the assumption that all addresses are formatted as "<street no> <street address>". For example "12345 John Blvd":
+
+\begin{verbatim}
+select siteCode, type, address, phone from Site where right(address, locate(' ', address)-1) = "University Dr";
+\end{verbatim}
+
+\section{Question Seven}
+\label{sec:orgd1dc577}
+\begin{verbatim}
+select administrator.empId, administrator.name, administrator.gender from administers
+       join administrator on administrator.empId=administers.empId
+       where siteCode=111;
+\end{verbatim}
+
+\section{Question Eight}
+\label{sec:orgbaea177}
+\begin{verbatim}
+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{verbatim}
+
+\section{Question Nine}
+\label{sec:org67a8ee6}
+\begin{verbatim}
+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{verbatim}
+
+\section{Question Ten}
+\label{sec:org3aed2a8}
+\begin{verbatim}
+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{verbatim}
+
+\section{Question Eleven}
+\label{sec:org8771b4d}
+\begin{verbatim}
+select * from model where screenSize > 10 and weight < 3
+\end{verbatim}
+
+\section{Question Twelve}
+\label{sec:org90164e0}
+\begin{verbatim}
+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{verbatim}
+
+\section{Question Thirteen}
+\label{sec:org522e64a}
+\#+BEGIN\textsubscript{SRC} sql
+select empId, day from adm\textsubscript{work}\textsubscript{hours} where hours > 8;
+\#+END\textsubscript{SRC} sql
+\end{document}
\ No newline at end of file
diff --git a/Homework/cs5800/Homeworks/hw-6.org b/Homework/cs5800/Homeworks/hw-6.org
new file mode 100644
index 0000000..0383c3f
--- /dev/null
+++ b/Homework/cs5800/Homeworks/hw-6.org
@@ -0,0 +1,35 @@
+#+TITLE: Homework 6
+#+AUTHOR: Logan Hunt
+#+LATEX_HEADER: \usepackage{amsfonts} \usepackage{amssymb} \usepackage{mathtools} \usepackage{ upgreek } \usepackage{ textcomp }
+#+OPTIONS: tex:t toc:nil inlineimages fold 
+
+* Question One
+\sigma_(name='Peter Smith')(Client)
+
+* Question Two
+\sigma_(frequency > 5)(AirtimePackage)
+
+* Question Three
+\pi_(videoCode)(\sigma_(siteCode='S345')(Broadcasts))
+
+* Question Four
+\pi_(serialNo)(\sigma_(type='restaurant')(DigitalDisplay \bowtie_(serialNo=serialNo) Locates \bowtie_(siteCode=siteCode) Site))
+
+* Question Five
+\pi_(empId, name)(\sigma_(modelNo='M456781')(Specializes \bowtie_(empId=empId) TechnicalSupport))
+
+* Question Six
+\pi_(modelNo)(\sigma_(name='Peter')(Specializes \bowtie_(empId=empId) TechnicalSupport))
+
+* Question Seven
+\pi_(videoCode, videoLength)(Video \bowtie_(videoCode=videoCode)(\pi_(videoCode)(\sigma_(siteCode=111)(Broadcasts)) \minus \pi_(videoCode)(\sigma_(siteCode=112)(Broadcasts)))
+
+* Question Eight
+\pi_(name)(TechnicalSupport) \cup \pi_(name)(Administrator) \cup \pi_(name)(Salesman)
+
+* Question Nine
+\pi_(empId, name)(\sigma_(modelNo='MO1' AND siteCode=111)TechnicalSupport \bowtie_(empId=empId)(Specializes) \bowtie_(modelNo=modelNo)(DigitalDisplay) \bowtie_(serialNo=serialNo)(Locates))
+
+* Question Ten
+\pi_(empId, name)(SalesMan \minus \pi_(empId)(SalesMan \bowtie_(empId=empId) (Purchases)))
+
diff --git a/Homework/cs5800/Homeworks/hw-6.pdf b/Homework/cs5800/Homeworks/hw-6.pdf
new file mode 100644
index 0000000..6320399
--- /dev/null
+++ b/Homework/cs5800/Homeworks/hw-6.pdf
diff --git a/Homework/cs5800/Homeworks/hw-6.tex b/Homework/cs5800/Homeworks/hw-6.tex
new file mode 100644
index 0000000..eec3bfe
--- /dev/null
+++ b/Homework/cs5800/Homeworks/hw-6.tex
@@ -0,0 +1,69 @@
+% Created 2022-11-26 Sat 14:04
+% Intended LaTeX compiler: pdflatex
+\documentclass[11pt]{article}
+\usepackage[utf8]{inputenc}
+\usepackage[T1]{fontenc}
+\usepackage{graphicx}
+\usepackage{longtable}
+\usepackage{wrapfig}
+\usepackage{rotating}
+\usepackage[normalem]{ulem}
+\usepackage{amsmath}
+\usepackage{amssymb}
+\usepackage{capt-of}
+\usepackage{hyperref}
+\usepackage{amsfonts} \usepackage{amssymb} \usepackage{mathtools} \usepackage{ upgreek } \usepackage{ textcomp }
+\author{Logan Hunt}
+\date{\today}
+\title{Homework 6}
+\hypersetup{
+ pdfauthor={Logan Hunt},
+ pdftitle={Homework 6},
+ pdfkeywords={},
+ pdfsubject={},
+ pdfcreator={Emacs 28.2 (Org mode 9.5.5)}, 
+ pdflang={English}}
+\begin{document}
+
+\maketitle
+
+\section{Question One}
+\label{sec:orgd978739}
+\(\sigma\)\textsubscript{(name='Peter Smith')}(Client)
+
+\section{Question Two}
+\label{sec:org8b645e7}
+\(\sigma\)\textsubscript{(frequency > 5)}(AirtimePackage)
+
+\section{Question Three}
+\label{sec:orgcc5745b}
+\(\pi\)\textsubscript{(videoCode)}(\(\sigma\)\textsubscript{(siteCode='S345')}(Broadcasts))
+
+\section{Question Four}
+\label{sec:org6ac4047}
+\(\pi\)\textsubscript{(serialNo)}(\(\sigma\)\textsubscript{(type='restaurant')}(DigitalDisplay \bowtie\textsubscript{(serialNo=serialNo)} Locates \bowtie\textsubscript{(siteCode=siteCode)} Site))
+
+\section{Question Five}
+\label{sec:orged1f22e}
+\(\pi\)\textsubscript{(empId, name)}(\(\sigma\)\textsubscript{(modelNo='M456781')}(Specializes \bowtie\textsubscript{(empId=empId)} TechnicalSupport))
+
+\section{Question Six}
+\label{sec:org779aa40}
+\(\pi\)\textsubscript{(modelNo)}(\(\sigma\)\textsubscript{(name='Peter')}(Specializes \bowtie\textsubscript{(empId=empId)} TechnicalSupport))
+
+\section{Question Seven}
+\label{sec:org1283914}
+\(\pi\)\textsubscript{(videoCode, videoLength)}(Video \bowtie\textsubscript{(videoCode=videoCode)}(\(\pi\)\textsubscript{(videoCode)}(\(\sigma\)\textsubscript{(siteCode=111)}(Broadcasts)) \(-\) \(\pi\)\textsubscript{(videoCode)}(\(\sigma\)\textsubscript{(siteCode=112)}(Broadcasts)))
+
+\section{Question Eight}
+\label{sec:org98cb969}
+\(\pi\)\textsubscript{(name)}(TechnicalSupport) \(\cup\) \(\pi\)\textsubscript{(name)}(Administrator) \(\cup\) \(\pi\)\textsubscript{(name)}(Salesman)
+
+\section{Question Nine}
+\label{sec:org1b1a92e}
+\(\pi\)\textsubscript{(empId, name)}(\(\sigma\)\textsubscript{(modelNo='MO1' AND siteCode=111)}TechnicalSupport \bowtie\textsubscript{(empId=empId)}(Specializes) \bowtie\textsubscript{(modelNo=modelNo)}(DigitalDisplay) \bowtie\textsubscript{(serialNo=serialNo)}(Locates))
+
+\section{Question Ten}
+\label{sec:orgd665945}
+\(\pi\)\textsubscript{(empId, name)}(SalesMan \(-\) \(\pi\)\textsubscript{(empId)}(SalesMan \bowtie\textsubscript{(empId=empId)} (Purchases)))
+\end{document}
\ No newline at end of file
diff --git a/Homework/cs5800/Homeworks/img/employee-departments-phones.png b/Homework/cs5800/Homeworks/img/employee-departments-phones.png
new file mode 100644
index 0000000..74ef791
--- /dev/null
+++ b/Homework/cs5800/Homeworks/img/employee-departments-phones.png
diff --git a/Homework/cs5800/Homeworks/img/representatives.png b/Homework/cs5800/Homeworks/img/representatives.png
new file mode 100644
index 0000000..8aac241
--- /dev/null
+++ b/Homework/cs5800/Homeworks/img/representatives.png
diff --git a/Homework/cs5800/Homeworks/img/teaches-course-text.png b/Homework/cs5800/Homeworks/img/teaches-course-text.png
new file mode 100644
index 0000000..258477a
--- /dev/null
+++ b/Homework/cs5800/Homeworks/img/teaches-course-text.png
diff --git a/Homework/cs5800/Notes/1.org b/Homework/cs5800/Notes/1.org
new file mode 100644
index 0000000..2fa491f
--- /dev/null
+++ b/Homework/cs5800/Notes/1.org
@@ -0,0 +1,65 @@
+#+TITLE: Purpose of database systems
+
+* Issues with Filesystem vs Database
+** Redundancy
+In two different departments, might keep the same data unnecessarily.
+
+** Inconsistency
+Data in one place might change, but does not automatically change elsewhere.
+
+* Purpose of DB systems
+** Data isolation - multiple files and formats
+How data is stored is not important to the DB user
+
+** Integrity problems
+Keeping all data within some requirements (e.g. 0 <= GPA <= 4.0)
+
+** Atomiciticy of updates
+All updates should be all or nothing:
+  + Giving friend $100
+    READ A; A -= 100; UPDATE A;
+    READ B; B += 100; UPDATE B;
+    - If fails before B updated but A is updated, should roll back
+
+** Concurrent access
+Correct order of execution on each piece of data - filesystem won't protect against 
+
+** Security problems
+Some DB users should only be able to access global/their own data - filesystem shows entire file
+
+* History
+** Earliest systems were sheets of paper kept in wooden file cabinets
+** They were trying to make it right since 60's
+*** Hierarchial Model - IBM (~1968)
+Used Trees with parent-child nodes
+            USU
+          /     \
+       Jane      Dave
+      /          /   \
+  CS5800     CS5800  CS5050 
+
+Issue: Cannot have a common parent. To find which students take a single course, must iterate over all.
+       Cannot have many-to-many relationships
+
+*** Network Model - General Electric
+Used Graphs
+            USU
+          /     \
+        Jane     Dave
+           \     /  \
+           CS5800    CS5050 
+
+Issue: Still uses pointers (difficult, need to know exactly where address is)
+       Still cannot have a node without a parent - cannot have a course without students
+
+*** Relational - IBM (Codd's Model) (~70's)
+The issue with both Network & Hierarchial is they still use "linked lists".
+
++ Provided mathematical foundation for Relational DB's
++ Big Abstraction win: Seperated physical storage of data from its conceptual representation (no pointers!)
++ Introduced high level query language
+
+*** GIS (~80's)
+*** 90's
+Multimedia databases, real-time-databases, xml, NOSQL database
+
diff --git a/Homework/cs5800/Quizzes/1.org b/Homework/cs5800/Quizzes/1.org
new file mode 100644
index 0000000..c490900
--- /dev/null
+++ b/Homework/cs5800/Quizzes/1.org
@@ -0,0 +1,24 @@
+1. True +
+2. False x
+3. False
+4. True _
+5. True X
+6. False _
+7. True _
+8. False _
+9. True +
+10. True _
+
+    Web Server used to encrypt data before sending to client
+    Database schema - includes description of the database structure
+    Internal schema - describe phuysical storage structures and access paths
+    An ER model can be transformed into a relational model
+    A databayse system is composed of the DMBS + database
+    Atomic transactions assure that the database is in a consistent state
+
+In this case a three-tier client/intermediate/server architecture would be the most well-suited for the job.
+On the client side there would be a Web Interfact written in something like HTML, CSS, and JS. The user would interact with this client, which would send requests to an intermediate web server through an API which would handle the business logic.
+For example this server might retrieve the general geolocation from the IP address of the customer's request from an external service, then make a query to the actual DBMS for hotels within a certain radius, and respond to the client with hotel objects from the database, potentially adding another step filtering the response with some more business logic.
+
+1) A procedural DML can be referred to as only the data sublanguage, and is required to be embedded in a general-purpose programming language and typically retrieves individual records from the database. A nonprocedrual or high level language, such as SQL, can also be referred to as a query language. It is also more declarative in that data is accessed via declaring *which* to access rather than *how* and can process multiple retrievals from the database.
+2) The database manager, or execution engine, is the only element in a DBMS that has access to change the DB's schema and instance. On the other hand, the run time processor - used in determining the sequence of associated steps of user commands and privileged DBA commands - has access to the schema and instance, but only through what is provided by this execution engine.
diff --git a/Homework/cs5800/Quizzes/2.org b/Homework/cs5800/Quizzes/2.org
new file mode 100644
index 0000000..a24d754
--- /dev/null
+++ b/Homework/cs5800/Quizzes/2.org
@@ -0,0 +1,62 @@
+1. False
+2. False
+3. True
+4. ?
+5. True
+6. True
+7. ?
+8. True
+9. True
+10. True
+
+    
+1. There are three foreign keys: "serial_no" is a foreign key relating "option" to a "car", "salesperson_id" is a foreign key relating "sale" to a "salesperson", and "serial_no" is a foreign key relating "sale" to a "car".
+
+2.
+
+CAR:
+
+(1, "Impala", "Chevrolet", 2000)
+
+(2, "Odyssey", "Honda", 4000)
+
+(3, "S", "Tesla", 100000)
+
+OPTION:
+
+(1, "bluetooth", 20)
+
+(2, "automatic doors", 400)
+
+(2, "leather seats", 1000)
+
+(3, "autopilot", 2000)
+
+SALESPERSON:
+
+(1, "Logan", "112-358-1321")
+
+(2, "Bob", "271-828-1828")
+
+(3, "Alice", "314-159-2654")
+
+SALE:
+
+(1, 1, "2022-10-02 15:47", 2020)
+
+(2, 3, "2022-03-04 12:34", 100000)
+
+3.
+
+SALE that would violate referential integrity because there is no salesperson with a salesperson_id of 10:
+
+(10, 2, "2022-01-01 00:01", 5000)
+
+SALE that would not violate referential integrity because there is a salesperson with a salesperson_id of 2 that did not sell car with serial number of 2:
+
+(2, 2, "2022-12-31 11:59", 5000)
+
+Weak entity type => An entity that cannot be uniquely identified by its attributes
+identifying relationship type => the relationship that relates the weak entity type to the owner
+owner entity type => is an entity type that relates to a weak entity type and identifies it
+partial key => an attribute that distinguishes instances of a weak entity type relative to a strong entity
diff --git a/Homework/cs5800/final/README.md b/Homework/cs5800/final/README.md
new file mode 100644
index 0000000..ce40259
--- /dev/null
+++ b/Homework/cs5800/final/README.md
@@ -0,0 +1,4 @@
+# Library Mini World
+This contains the create, insert, and example queries for the database implementation
+
+These three files are found under the sql folder
diff --git a/Homework/cs5800/final/sql/create.sql b/Homework/cs5800/final/sql/create.sql
new file mode 100644
index 0000000..c0cf0b7
--- /dev/null
+++ b/Homework/cs5800/final/sql/create.sql
@@ -0,0 +1,312 @@
+DROP DATABASE IF EXISTS LibraryMiniWorld;
+
+CREATE DATABASE IF NOT EXISTS LibraryMiniWorld;
+USE LibraryMiniWorld;
+
+CREATE TABLE Person (
+  PersonID INTEGER PRIMARY KEY AUTO_INCREMENT,
+  Phone VARCHAR(20) NOT NULL,
+  Name VARCHAR(50) NOT NULL,
+  Email VARCHAR(100) NOT NULL,
+  Address VARCHAR(255) NOT NULL
+);
+
+CREATE TABLE Customer (
+  PersonID INTEGER PRIMARY KEY,
+  JoinDate DATE NOT NULL,
+  FOREIGN KEY (PersonID) REFERENCES Person(PersonID)
+);
+
+CREATE TABLE LibraryCard (
+  CardNo INTEGER PRIMARY KEY AUTO_INCREMENT,
+  CustomerID INTEGER NOT NULL,
+  IssueDate DATE NOT NULL,
+  ExpiryDate DATE NOT NULL,
+  FOREIGN KEY (CustomerID) REFERENCES Customer(PersonID)
+);
+
+CREATE TABLE Employee (
+  PersonID INTEGER PRIMARY KEY,
+  StartDate DATE NOT NULL,
+  Salary INTEGER NOT NULL,
+  EmployeeType VARCHAR(20) NOT NULL, -- Either "Librarian" or "Manager"
+  FOREIGN KEY (PersonID) REFERENCES Person(PersonID)
+);
+
+CREATE TABLE Manager (
+  EmployeeID INTEGER PRIMARY KEY,
+  FOREIGN KEY (EmployeeID) REFERENCES Employee(PersonID)
+);
+
+CREATE TABLE Librarian (
+  EmployeeID INTEGER PRIMARY KEY,
+  FOREIGN KEY (EmployeeID) REFERENCES Employee(PersonID)
+);
+
+CREATE TABLE Manages (
+  ManagerID INTEGER NOT NULL,
+  LibrarianID INTEGER NOT NULL,
+  FOREIGN KEY (ManagerID) REFERENCES Manager(EmployeeID),
+  FOREIGN KEY (LibrarianID) REFERENCES Librarian(EmployeeID),
+  PRIMARY KEY (ManagerID, LibrarianID)
+);
+
+CREATE TABLE Media (
+  MediaID INTEGER PRIMARY KEY AUTO_INCREMENT,
+  DeweyNumber VARCHAR(20) NOT NULL,
+  Name VARCHAR(100) NOT NULL,
+  MediaType VARCHAR(20) NOT NULL, -- Either "Text", "Audio", or "Video"
+  CreatedYear YEAR NOT NULL
+);
+
+CREATE TABLE Genre (
+  GenreID INTEGER PRIMARY KEY AUTO_INCREMENT,
+  Name VARCHAR(50) NOT NULL UNIQUE
+);
+
+CREATE TABLE Tag (
+  TagID INTEGER PRIMARY KEY AUTO_INCREMENT,
+  Name VARCHAR(50) NOT NULL UNIQUE
+);
+
+CREATE TABLE Creator (
+  CreatorID INTEGER PRIMARY KEY AUTO_INCREMENT,
+  Name VARCHAR(50) NOT NULL
+);
+
+CREATE TABLE MediaGenre (
+  MediaID INTEGER NOT NULL,
+  GenreID INTEGER NOT NULL,
+  FOREIGN KEY (MediaID) REFERENCES Media(MediaID),
+  FOREIGN KEY (GenreID) REFERENCES Genre(GenreID),
+  PRIMARY KEY (MediaID, GenreID)
+);
+
+CREATE TABLE MediaTag (
+  MediaID INTEGER NOT NULL,
+  TagID INTEGER NOT NULL,
+  FOREIGN KEY (MediaID) REFERENCES Media(MediaID),
+  FOREIGN KEY (TagID) REFERENCES Tag(TagID),
+  PRIMARY KEY (MediaID, TagID)
+);
+
+CREATE TABLE MediaCreator (
+  MediaID INTEGER NOT NULL,
+  CreatorID INTEGER NOT NULL,
+  FOREIGN KEY (MediaID) REFERENCES Media(MediaID),
+  FOREIGN KEY (CreatorID) REFERENCES Creator(CreatorID),
+  PRIMARY KEY (MediaID, CreatorID)
+);
+
+CREATE TABLE Text (
+  MediaID INTEGER PRIMARY KEY,
+  Pages INTEGER NOT NULL,
+  FOREIGN KEY (MediaID) REFERENCES Media(MediaID)
+);
+
+CREATE TABLE Audio (
+  MediaID INTEGER PRIMARY KEY,
+  Playtime INTEGER NOT NULL,
+  FOREIGN KEY (MediaID) REFERENCES Media(MediaID)
+);
+
+CREATE TABLE Video (
+  MediaID INTEGER PRIMARY KEY,
+  Runtime INTEGER NOT NULL,
+  FOREIGN KEY (MediaID) REFERENCES Media(MediaID)
+);
+
+CREATE TABLE MediaInstance (
+  InstanceID INTEGER PRIMARY KEY AUTO_INCREMENT,
+  LibrarianID INTEGER NOT NULL,
+  MediaID INTEGER NOT NULL,
+  DateAdded DATE NOT NULL,
+  FOREIGN KEY (MediaID) REFERENCES Media(MediaID),
+  FOREIGN KEY (LibrarianID) REFERENCES Librarian(EmployeeID)
+);
+
+CREATE TABLE CustomerRate (
+  CustomerID INTEGER NOT NULL,
+  MediaID INTEGER NOT NULL,
+  Stars INTEGER NOT NULL CHECK (Stars >= 0 AND Stars <= 5),
+  Review VARCHAR(690),
+  FOREIGN KEY (CustomerID) REFERENCES Customer(PersonID),
+  FOREIGN KEY (MediaID) REFERENCES Media(MediaID),
+  PRIMARY KEY (CustomerID, MediaID)
+);
+
+CREATE TABLE Borrow (
+  LibraryCardNo INTEGER NOT NULL,
+  InstanceID INTEGER NOT NULL,
+  LibrarianID INTEGER NOT NULL,
+  IsReturned BOOLEAN NOT NULL,
+  BorrowDate DATE NOT NULL,
+  ReturnDate DATE NOT NULL,
+  CONSTRAINT BorrowDateBeforeReturn CHECK (BorrowDate <= ReturnDate),
+  FOREIGN KEY (LibraryCardNo) REFERENCES LibraryCard(CardNo),
+  FOREIGN KEY (InstanceID) REFERENCES MediaInstance(InstanceID),
+  FOREIGN KEY (LibrarianID) REFERENCES Librarian(EmployeeID),
+  PRIMARY KEY (InstanceID, BorrowDate)
+);
+
+ALTER TABLE Creator ADD FULLTEXT (Name);
+ALTER TABLE Tag ADD FULLTEXT (Name);
+ALTER TABLE Genre ADD FULLTEXT (Name);
+ALTER TABLE Media ADD FULLTEXT (Name, MediaType, DeweyNumber);
+
+
+CREATE VIEW MediaQuery
+AS 
+  SELECT me.*,
+    GROUP_CONCAT(DISTINCT(c.name)) AS Creators, 
+    GROUP_CONCAT(DISTINCT(t.name)) AS Tags, 
+    GROUP_CONCAT(DISTINCT(g.name)) AS Genres, 
+    COUNT(DISTINCT(mi.InstanceID)) AS Copies, 
+    COUNT(DISTINCT(mi.InstanceID)) - COUNT(DISTINCT(b.InstanceID)) AS AvailableCopies
+  FROM Media AS me 
+  INNER JOIN MediaInstance AS mi ON me.MediaID = mi.MediaID
+    LEFT JOIN MediaGenre AS mg ON me.MediaID = mg.MediaID
+      LEFT JOIN Genre AS g ON mg.GenreID = g.GenreID
+    LEFT JOIN MediaTag AS mt ON me.MediaID = mt.MediaID
+      LEFT JOIN Tag AS t ON mt.TagID = t.TagID
+    LEFT JOIN MediaCreator AS mc ON me.MediaID = mc.MediaID
+      LEFT JOIN Creator AS c ON mc.CreatorID = c.CreatorID
+  LEFT JOIN Borrow AS b ON b.InstanceID = mi.InstanceID AND b.IsReturned
+  GROUP BY me.MediaID;
+
+
+DELIMITER //
+CREATE TRIGGER BorrowCheck
+BEFORE INSERT ON Borrow
+FOR EACH ROW
+BEGIN
+  IF NEW.InstanceID IN 
+    (SELECT InstanceID FROM Borrow AS b 
+      WHERE New.BorrowDate <= b.ReturnDate 
+      AND New.ReturnDate >= b.BorrowDate 
+      AND NOT b.IsReturned) 
+  THEN
+    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Media instance is already borrowed';
+  END IF;
+END;
+//
+
+DELIMITER //
+CREATE TRIGGER BorrowNowOrFuture
+BEFORE INSERT ON Borrow
+FOR EACH ROW
+BEGIN
+  IF NEW.BorrowDate < CURDATE() AND NEW.ReturnDate >= CURDATE() THEN
+    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Borrow date cannot be in the past unless return date is as well'; 
+  END IF;
+END;
+//
+
+DELIMITER //
+CREATE TRIGGER CustomerCantRegisterForNewCardWhenHasOneNotExpired
+BEFORE INSERT ON LibraryCard
+FOR EACH ROW
+BEGIN
+  IF NEW.ExpiryDate >= CURDATE() AND NEW.CustomerID IN 
+    (SELECT CustomerID FROM LibraryCard WHERE ExpiryDate >= CURDATE()) 
+  THEN
+    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Customer already has an active library card';
+  END IF;
+END;
+//
+
+DELIMITER //
+CREATE TRIGGER LibraryCardNotExpired
+BEFORE INSERT ON Borrow
+FOR EACH ROW
+BEGIN
+  IF NOT EXISTS 
+    (SELECT CardNo FROM LibraryCard 
+      WHERE CardNo = NEW.LibraryCardNo 
+      AND ExpiryDate >= NEW.BorrowDate) 
+  THEN
+    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Library card is expired';
+  END IF;
+END;
+//
+
+DELIMITER //
+CREATE TRIGGER CustomerCanOnlyRateMediaTheyBorrowed
+BEFORE INSERT ON CustomerRate
+FOR EACH ROW
+BEGIN
+  IF New.CustomerID NOT IN 
+    (SELECT lc.CustomerID 
+     FROM Borrow AS b, MediaInstance AS i, Media AS m, LibraryCard AS lc 
+     WHERE b.InstanceID = i.InstanceID
+     AND b.LibraryCardNo = lc.CardNo
+     AND i.MediaID = m.MediaID 
+     AND m.MediaID = New.MediaID) 
+  THEN
+    SIGNAL SQLSTATE '45000' 
+    SET MESSAGE_TEXT = 'Customer can only rate media they borrowed';
+  END IF;
+END;
+//
+
+DELIMITER //
+CREATE PROCEDURE 
+  borrowedHistory( customerID INT )
+BEGIN  
+  SELECT mi.MediaID, me.Name, b.BorrowDate, b.ReturnDate, b.IsReturned, l.Name AS Librarian
+  FROM MediaInstance AS mi, Media AS me, Borrow AS b, Person AS l, LibraryCard AS lc
+  WHERE mi.InstanceID = b.InstanceID 
+  AND mi.MediaID = me.MediaID 
+  AND b.LibrarianID = l.PersonID
+  AND b.LibraryCardNo = lc.CardNo
+  AND lc.CustomerID = customerID;
+END;
+//
+
+DELIMITER // 
+CREATE PROCEDURE 
+  searchMedia (searchQuery VARCHAR(255))
+  BEGIN 
+    SELECT q.MediaID, q.DeweyNumber, q.Name, q.MediaType, q.CreatedYear, 
+           q.Creators, q.Tags, q.Genres, q.Copies, q.AvailableCopies 
+    FROM (
+      SELECT me.*,
+        GROUP_CONCAT(DISTINCT(c.name)) AS Creators, 
+        GROUP_CONCAT(DISTINCT(t.name)) AS Tags, 
+        GROUP_CONCAT(DISTINCT(g.name)) AS Genres, 
+        COUNT(DISTINCT(mi.InstanceID)) AS Copies, 
+        COUNT(DISTINCT(mi.InstanceID)) - COUNT(DISTINCT(b.InstanceID)) AS AvailableCopies,
+        MAX(MATCH (me.Name, me.MediaType, me.DeweyNumber) AGAINST (searchQuery)) AS NameScore,
+        MAX(MATCH (c.Name) AGAINST (searchQuery)) AS CreatorScore,
+        MAX(MATCH (t.Name) AGAINST (searchQuery)) AS TagScore,
+        MAX(MATCH (g.Name) AGAINST (searchQuery)) AS GenreScore
+      FROM Media AS me 
+      LEFT JOIN MediaInstance AS mi ON me.MediaID = mi.MediaID
+      LEFT JOIN MediaGenre AS mg ON me.MediaID = mg.MediaID
+        LEFT JOIN Genre AS g ON mg.GenreID = g.GenreID
+      LEFT JOIN MediaTag AS mt ON me.MediaID = mt.MediaID
+        LEFT JOIN Tag AS t ON mt.TagID = t.TagID
+      LEFT JOIN MediaCreator AS mc ON me.MediaID = mc.MediaID
+        LEFT JOIN Creator AS c ON mc.CreatorID = c.CreatorID
+      LEFT JOIN Borrow AS b ON b.InstanceID = mi.InstanceID AND b.IsReturned
+      GROUP BY me.MediaID) AS q
+    WHERE NameScore > 0 OR CreatorScore > 0 OR TagScore > 0 OR GenreScore > 0 
+    ORDER BY NameScore DESC, CreatorScore DESC, TagScore DESC, GenreScore DESC, AvailableCopies DESC;
+  END;
+//
+
+DELIMITER //
+CREATE PROCEDURE borrow( libraryCardNo INT, mediaID INT, librarianID INT, returnDate DATE, OUT instanceID INT )
+BEGIN
+  DECLARE instanceID INT;
+
+  SELECT mi.InstanceID INTO instanceID
+  FROM MediaInstance AS mi
+  LEFT JOIN Borrow AS b ON b.InstanceID = mi.InstanceID AND b.IsReturned
+  WHERE mi.MediaID = mediaID AND b.InstanceID IS NULL
+  LIMIT 1;
+
+  INSERT INTO Borrow (LibraryCardNo, InstanceID, LibrarianID, BorrowDate, ReturnDate, IsReturned)
+  VALUES (libraryCardNo, instanceID, librarianID, CURDATE(), returnDate, FALSE);
+END;
+//
\ No newline at end of file
diff --git a/Homework/cs5800/final/sql/insert.sql b/Homework/cs5800/final/sql/insert.sql
new file mode 100644
index 0000000..bc6ab18
--- /dev/null
+++ b/Homework/cs5800/final/sql/insert.sql
@@ -0,0 +1,191 @@
+USE LibraryMiniWorld;
+
+INSERT INTO Person (PersonID, Name, Address, Phone, Email) VALUES
+(1, 'John Pi', '314 Main St Applewood CA 31415', '314-159-2565', 'johnpi@bruh.com'),
+(2, 'Leonhard Euler', '271 Fake Cr Logan UT 27183', '271-828-1828', 'euler@math.org'),
+(3, 'Ada Lovelace', '422 Ada Ave Salt Lake City UT 38382', '121-232-3443', 'ada@programming.edu'),
+(4, 'Alan Turing', '314 Turing Rd Reno NV 89123', '123-571-1131', 'alan@computerscience.com'),
+(5, 'Grace Hopper', '771 Hopper Ct Seattle WA 22331', '223-414-4488', 'grace@computerscience.com'),
+(6, 'Charles Babbage', '314 Babbage Rd Reno NV 89123', '881-222-1818', 'bbage @computerscience.com'),
+(7, 'John von Neumann', '442 Neumann St Idaho Falls ID 48323', '881-222-1818', 'john@neumann.com'),
+(8, 'John Conway', '888 Conway Rd Reno NV 89123', '102-103-1004', 'game@oflife.fun'),
+(9, 'Stephen Hawking', '111 Hawking Ave Seattle WA 12523', '101-102-1010', 'hawking@radiation.com');
+
+INSERT INTO Employee (PersonID, StartDate, Salary, EmployeeType) VALUES
+(1, '2001-04-02', 40000, 'Manager'),
+(2, '2001-04-02', 41000, 'Manager'),
+(3, '2001-04-02', 30000, 'Librarian'),
+(4, '2010-06-25', 29000, 'Librarian'),
+(5, '2022-12-08', 25000, 'Librarian'),
+(6, '2022-11-08', 26000, 'Librarian');
+
+INSERT IGNORE INTO Manager (EmployeeID)
+SELECT PersonID
+FROM Employee
+WHERE EmployeeType = 'Manager';
+
+INSERT IGNORE INTO Librarian (EmployeeID)
+SELECT PersonID
+FROM Employee
+WHERE EmployeeType = 'Librarian';
+
+INSERT INTO Manages(ManagerID, LibrarianID) VALUES
+(1, 3),
+(2, 4),
+(2, 5),
+(2, 6);
+
+INSERT INTO Customer (PersonID, JoinDate) VALUES
+(1, '2001-12-12'),
+(3, '2011-10-25'),
+(7, '2012-10-25'),
+(8, '2013-10-25'),
+(9, '2022-12-08');
+
+INSERT INTO LibraryCard (CardNo, CustomerID, IssueDate, ExpiryDate) VALUES
+(1, 1, '2021-12-12', '2022-12-12'),
+(2, 3, '2020-12-25', '2021-12-25'),
+(3, 7, '2020-09-15', '2022-09-25'),
+(4, 8, '2022-01-25', '2023-01-25'),
+(5, 9, '2015-12-25', '2023-01-01'),
+(6, 3, '2021-12-26', '2022-12-26');
+
+-- SET sql_safe_updates=0;
+-- 
+-- UPDATE Customer INNER JOIN LibraryCard 
+-- AS lc ON lc.CustomerID = Customer.PERSONID 
+-- AND lc.ExpiryDate = 
+--   (SELECT MAX(ExpiryDate) FROM LibraryCard 
+--    GROUP BY CustomerID HAVING CustomerID=lc.CustomerID) 
+-- SET Customer.CardNo=lc.CardNo;
+-- 
+-- SET sql_safe_updates=1; (commented out because we no longer store card number on the customer)
+
+INSERT INTO Creator (CreatorID, Name) VALUES
+(1, 'Fyodor Doestoyevsky'),
+(2, 'J.K. Rowling'),
+(3, 'Brandon Sanderson'),
+(4, 'Christopher Paolini'),
+(5, 'James Clear'),
+(6, 'William Shakespeare'),
+(7, 'Jim Dale'),
+(8, 'Stefen Fangmeier'),
+(9, 'Jennifer Nielsen');
+
+INSERT INTO Media (MediaID, DeweyNumber, Name, MediaType, CreatedYear) VALUES
+(1, '891.73','Crime and Punishment', 'Text', 2003),
+(2, '822.33','Hamlet', 'Text', 2003),
+(3, '823.91','Harry Potter and the Sorcerers Stone', 'Text', 1997),
+(4, '823.91','Harry Potter and the Sorcerers Stone', 'Audio', 1997),
+(5, '813.91','Eragon', 'Text', 2002),
+(6, '813.91','Eragon', 'Video', 2006),
+(7, '813.6','Elantris', 'Text', 2005),
+(8, '155.2','Atomic Habits', 'Audio', 2018),
+(9, '813.6', 'Lines of Courage', 'Text', 2022);
+
+INSERT INTO Text (MediaID, Pages) VALUES
+(1, 492),
+(2, 104),
+(3, 223),
+(5, 509),
+(7, 496),
+(9, 400);
+
+INSERT INTO Audio (MediaID, Playtime) VALUES
+(4, 29880),
+(8, 13380);
+
+INSERT INTO Video (MediaID, Runtime) VALUES
+(6, 6240);
+
+
+INSERT INTO Genre (GenreID, Name) VALUES
+(1, 'Fantasy'),
+(2, 'Science Fiction'),
+(3, 'Romance'),
+(4, 'Drama'),
+(5, 'Non-Fiction'),
+(6, 'Fiction'),
+(7, 'War Story');
+
+INSERT INTO MediaGenre (MediaID, GenreID) VALUES
+(1, 4),
+(2, 4),
+(3, 1),
+(4, 1),
+(5, 2),
+(6, 2),
+(7, 2),
+(8, 5),
+(9, 6),
+(9, 7);
+
+INSERT INTO Tag (TagID, Name) VALUES
+(1, 'Fun'),
+(2, 'Good for Kids'),
+(3, 'Young Adult'),
+(4, 'Sad'),
+(5, 'Educational'),
+(6, 'Classic'),
+(7, 'Popular'),
+(8, 'New'),
+(9, 'Powerful');
+
+INSERT INTO MediaTag (MediaID, TagID) VALUES
+(1, 6),
+(3, 1),
+(3, 2),
+(3, 3),
+(3, 7),
+(7, 3),
+(8, 5),
+(8, 8),
+(9, 8),
+(9, 9);
+
+
+INSERT INTO MediaCreator (MediaID, CreatorID) VALUES
+(1, 1),
+(2, 6),
+(3, 2),
+(4, 2),
+(4, 7),
+(5, 4),
+(6, 4),
+(6, 8),
+(7, 3),
+(8, 5),
+(9, 9);
+
+INSERT INTO MediaInstance (InstanceID, LibrarianID, MediaID, DateAdded) VALUES
+(1,  4, 3, '2004-05-08'),
+(2,  4, 4, '2004-05-08'),
+(3,  4, 5, '2004-05-08'),
+(4,  4, 5, '2004-05-08'),
+(5,  4, 5, '2004-05-08'),
+(6,  5, 6, '2011-03-31'),
+(7,  4, 3, '2012-05-08'),
+(8,  5, 4, '2018-10-07'),
+(9,  5, 5, '2019-02-18'),
+(10, 4, 6, '2020-01-20'),
+(11, 4, 6, '2020-01-20'),
+(12, 4, 6, '2020-01-20'),
+(13, 4, 7, '2021-01-20'),
+(14, 6, 8, '2021-03-20'),
+(15, 6, 9, '2022-05-20'),
+(16, 6, 9, '2022-05-20'),
+(17, 5, 4, '2009-05-08');
+
+INSERT INTO Borrow (LibraryCardNo, InstanceID, LibrarianID, IsReturned, BorrowDate, ReturnDate) VALUES
+(1, 1, 3, 0, CURDATE(), '2022-12-24'),
+(1, 2, 3, 0, CURDATE(), '2022-12-24'),
+(2, 10, 5, 1, '2021-10-01', '2022-10-24'),
+(3, 1, 5, 1, '2022-01-01', '2022-01-24'),
+(3, 2, 6, 1, '2022-01-01', '2022-01-24'),
+(4, 15, 4, 0, '2022-10-01', '2022-10-24'),
+(4, 6, 4, 1, '2021-12-01', '2022-12-07');
+
+INSERT INTO CustomerRate (CustomerID, MediaID, Stars, Review) VALUES
+(1, 3, 5, 'This book was great!'),
+(1, 4, 5, 'The audiobook rendition was perfect too!'),
+(8, 6, 2, 'The movie did not live up to the book in my opinion.');
diff --git a/Homework/cs5800/final/sql/queries.sql b/Homework/cs5800/final/sql/queries.sql
new file mode 100644
index 0000000..286a1b4
--- /dev/null
+++ b/Homework/cs5800/final/sql/queries.sql
@@ -0,0 +1,103 @@
+-- Note that we are not using the database in a transaction, so if you run this script multiple times, you will get duplicate data.
+-- This causing errors when you try to run the queries more than once.
+
+-- If you'd like to run it more than once, just rerun the create and insert scripts as the create script will drop the database and recreate it.
+
+-- Let's create some dummy data as we go through the functional requirements!
+INSERT INTO Person (PersonID, Name, Address, Phone, Email) VALUES
+(5800, 'Soukaina Filali Boubrahimi', 'Old Main, Logan, UT 84322', '435-123-1234', 'soukaina@usu.edu');
+
+INSERT INTO Customer (PersonID, JoinDate) VALUES
+(5800, CURDATE());
+
+-- FUNCTIONAL REQUIREMENTS
+-- 1) Customers should be able to obtain a Library Card, and renew it when it expires.
+-- 2) Customers should be able to view only the borrowing history of their own reservations.
+-- 3) A Librarian should be able to mark a media as available after the customer has returned the book.
+-- 4) A Librarian should be able to view which media the library has checked out to a customer.
+-- 5) Users of the database should be able to search for media by title, creator name, tag etc.
+-- 6) Users should be able to find the availability of a piece of media and how much stock there is of it in the Library
+-- 7) A librarian should be able to renew a borrowing
+-- 8) The database should be able to track the employees of the library, including the managers and the librarians they manage.
+-- 9) Users should be able to leave a rating for the media they have checked out. 
+-- 10) The database should track the transactions overseen by each librarian and which librarian added each piece of media into the inventory.
+-- 11) A customer should be able to reserve a media item if there is one available
+
+-- 1) Registering a new library card to a customer
+INSERT INTO LibraryCard (CardNo, CustomerID, IssueDate, ExpiryDate) VALUES
+(100, 5800, CURDATE(), DATE_ADD(CURDATE(), INTERVAL 2 YEAR));
+
+-- 6) Finding the availability of a piece of media and how much stock there is of it in the Library via a view
+SELECT * FROM MediaQuery WHERE Name = "Harry Potter and the Sorcerers Stone";
+
+-- 5) Searching for media by title, creator name, tag etc., additionally satisfies 6) again 
+-- By Title
+CALL searchMedia("Harry Potter");
+CALL searchMedia("Crime");
+-- By Creator(s)
+CALL searchMedia("Rowling");
+CALL searchMedia("Rowling Dale");
+-- By Genre(s)
+CALL searchMedia("Fantasy Drama");
+-- By Tag(s)
+CALL searchMedia("Kids Classic");
+
+-- 11) Borrow an instance of Harry Potter (id 3) with our Library Card through Librarian 6
+SET @instance = NULL;
+CALL borrow(100, 3, 6, DATE_ADD(CURDATE(), INTERVAL 2 WEEK), @instance);
+-- Doing it again will cause the BorrowCheck trigger to trip - there was only one copy of Harry Potter available
+CALL borrow(100, 3, 6, DATE_ADD(CURDATE(), INTERVAL 2 WEEK), @instance);
+
+-- 2) Viewing our own reservations, also satisfies 4
+CALL borrowedHistory(5800);
+
+-- 8) We didn't enjoy our experience with Librarian 6, so we want to complain to the manager. Let's find who that is :)
+SELECT Person.Phone, Person.Name FROM Manages 
+INNER JOIN Person ON Manages.ManagerID = Person.PersonID
+WHERE LibrarianID = 6;
+
+-- 9) Leaving a rating for the media we have checked out
+INSERT INTO CustomerRate (CustomerID, MediaID, Stars, Review) VALUES
+(5800, 3, 5, "I loved the book! But for some reason the Librarian was really rude to me.");
+-- But it looks like we can't leave slanderous rating for a media we haven't checked out
+INSERT INTO CustomerRate (CustomerID, MediaID, Stars, Review) VALUES
+(5800, 1, 1, "Just awful!");
+
+-- 3) Marking a media as available after the customer has returned the book
+UPDATE Borrow SET IsReturned = 1 WHERE InstanceID = @instance AND NOT IsReturned;
+
+-- 10) Finding the media instances that a librarian has added to the inventory
+SELECT * FROM MediaInstance 
+INNER JOIN Media ON MediaInstance.MediaID = Media.MediaID
+WHERE LibrarianID = 4;
+-- Finding the borrowing transactions overseen by a librarian
+SELECT Media.Name AS MediaName, MediaInstance.InstanceID, LibraryCardNo, IsReturned, 
+       BorrowDate, CustomerPerson.Name as CustomerName, CustomerPerson.Phone as CustomerPhone, 
+       CustomerPerson.Email as CustomerEmail
+FROM Borrow 
+INNER JOIN MediaInstance ON MediaInstance.InstanceID = Borrow.InstanceID
+INNER JOIN Media ON MediaInstance.MediaID = Media.MediaID
+INNER JOIN Person AS LibraryPerson ON Borrow.LibrarianID = LibraryPerson.PersonID
+INNER JOIN LibraryCard ON Borrow.LibraryCardNo = LibraryCard.CardNo
+INNER JOIN Customer ON LibraryCard.CustomerID = Customer.PersonID
+INNER JOIN Person AS CustomerPerson ON Customer.PersonID = CustomerPerson.PersonID
+WHERE Borrow.LibrarianID = 4;
+
+-- 1b) Renewing/registering a new library card
+-- We can't register a new card if one already exists for a customer that hasn't expired. Trigger will trip.
+INSERT INTO LibraryCard (CardNo, CustomerID, IssueDate, ExpiryDate) VALUES
+(100, 5800, CURDATE(), DATE_ADD(CURDATE(), INTERVAL 2 YEAR));
+-- Simulate "expiration"
+UPDATE LibraryCard 
+SET ExpiryDate = DATE_SUB(CURDATE(), INTERVAL 1 YEAR), 
+    IssueDate =  DATE_SUB(CURDATE(), INTERVAL 2 YEAR)
+WHERE CardNo = 100;
+-- Now we can register for a new card
+INSERT INTO LibraryCard (CardNo, CustomerID, IssueDate, ExpiryDate) VALUES
+(101, 5800, CURDATE(), DATE_ADD(CURDATE(), INTERVAL 2 YEAR));
+-- Delete the card
+DELETE FROM LibraryCard WHERE CardNo = 101;
+-- Now renew the previously expired card
+UPDATE LibraryCard
+SET ExpiryDate = DATE_ADD(CURDATE(), INTERVAL 2 YEAR)
+WHERE CardNo = 1000;
\ No newline at end of file