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
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
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}
|
