\documentclass[12pt]{article}
\usepackage{amsfonts, amssymb, amsmath, comment}
\usepackage{times}
\usepackage{fancyhdr}
\usepackage{hyperref}
\usepackage{url}
\usepackage{listings}
\usepackage[normalem]{ulem}
\usepackage{tikz}
\usetikzlibrary{shapes,shapes.geometric,er,positioning,calc,arrows,decorations.markings,backgrounds}
\lstset{language=SQL}
%\usepackage{xypic}
\usepackage{epsf}
\usepackage{epsfig}
\usepackage{enumitem}
%\usepackage{comment}

\setlist[itemize]{nosep}
\setlist[enumerate]{nosep}

\setlength{\oddsidemargin}{0in}
\setlength{\evensidemargin}{0in}
\setlength{\textwidth}{6.5in}
\setlength{\textheight}{9.0in}
\setlength{\topmargin}{-0.5in}
\setlength{\headheight}{0in}
\parskip12pt
\parindent0pt
\pagestyle{fancy}
\lhead{CS 338 - Spring 2026}
\chead{CM A03}
\rhead{Due Friday, July 24, 11:59 PM EST}
\cfoot{\normalfont\medskip Copyright \copyright 2026}

\newcommand{\rmp}{\reversemarginpar\marginpar}

\providecommand{\SolutionOne}{}
\providecommand{\SolutionTwo}{}
\providecommand{\SolutionThree}{}
\providecommand{\SolutionFour}{}
\providecommand{\SolutionFive}{}
\providecommand{\SolutionSix}{}
\providecommand{\SolutionSeven}{}
\providecommand{\SolutionEight}{}
\providecommand{\SolutionNine}{}
\providecommand{\SolutionTen}{}

%\input xy
%\xyoption{all}

\begin{document}

\begin{itemize}
\item{
\textbf{Topics:} Entity Relationship Diagrams, Mapping
}
\item{
Assignments must be completed individually.
}
\item{
No late assignments will be accepted.
}
\item{
Submit your completed solutions to \textbf{Crowdmark}.
}
\item{
\textbf{Rubric:}
Each question is worth 5 points, and will be graded according to the following rubric:
\begin{itemize}
\item{
5 points: Excellent (no mistake, or only a very small mistake)
}
\item{
4 points: Good (some mistakes)
}
\item{
3 points: OK (lots of mistakes)
}
\item{
2 points: Unsatisfactory (mostly wrong but has some merit)
}
\item{
1 point: Poor (mostly wrong but has one good idea)
}
\item{
0 points: Wrong (totally off, or no answer)
}
\end{itemize}
}
\end{itemize}

\newpage

\begin{enumerate}

\item \label{QuestionOne}
Draw \rmp{[5]}an entity-relationship diagram to store the following publishing information:
\begin{enumerate}[label=(\roman*)]
\item{
Every author has a unique ID, and might have a URL pointing to their personal web site.
}
\item{
An author might write multiple publications.
}
\item{
Each publication has a unique ID (PUBID), and could be a book, a journal, a proceedings, or an article.
}
\item{
A proceedings consists of articles presented in a conference, while a journal is made up of written articles.
}
\item{
A proceedings or a journal has one and only author, who is actually the chief editor.
}
\item{
A book or an article might have multiple authors.
}
\item{
A book might or might not contain any articles.
}
\item{
An article might appear in book(s), journal(s), or proceedings.
}
\end{enumerate}
\textbf{Instructions:}
\begin{itemize}
\item{
When needed, make your own assumptions, based on your understanding of the problem.
}
\item{
Your assumptions will be graded according to their reasonableness.
}
\item{
Fill in the missing attributes like names, titles etc.
}
\item{
Make sure to also properly specify primary keys, participation constraints and cardinality constraints.
}
\end{itemize}
\SolutionOne

\newpage

\item \label{QuestionTwo}
The \rmp{[5]}rules of mapping an ERD to a relational schema are well defined and you do not need to know the semantics of the diagram to properly convert it to a relational model.
This is why I deliberately made the names meaningless in this exercise.
Map the following ER diagram into a relational schema.
Make sure to include the proper primary keys and foreign keys.

\begin{center}
\includegraphics[scale=1]{A03-Q2.png}
\end{center}

\SolutionTwo

\newpage

\item \label{QuestionThree}
Based \rmp{[5]}on your own experience about eBay.ca, draw an entity-relationship diagram to store related information about the site.
Note that you don't need to include all the functions and attributes, just the most important ones.
When needed, make your own assumptions, based on your understanding of the problem.
Your assumptions will be graded according to their reasonableness.
\SolutionThree

\newpage

\item \label{QuestionFour}
Great \rmp{[5]}West Life has been providing outstanding service to University of Waterloo since 1994.
They have been storing the claim information in files.
Now they want to modernize their practice.
You have been requested to design a database to store information on health claims.
A health claim form is attached on the last page of this assignment.
Based on the form, identify the entities and attributes.
Then draw a complete entity-relationship diagram to model the data in the claim form.
Make sure to include proper primary keys, relationships, cardinality constraints, and participation constraints.
When needed, make your own assumptions, based on your understanding of the problem.
Your assumptions will be graded according to their reasonableness.
\SolutionFour

\newpage

\item \label{QuestionFive}
Consider \rmp{[5]}this ERD.

\begin{tikzpicture}[scale=0.8]
\tikzstyle{every entity}=[style={draw, thick, fill=white}]
\tikzstyle{every attribute}=[style={draw, thick}]
\tikzstyle{every relationship}=[style={draw, thick, fill=white}]
\node[attribute] (EID) at (14.5,13) {\underline{EID}};
\node[attribute] (SName) at (-1,11) {SName};
\node[attribute] (SID) at (1,12) {\underline{SID}};
\node[attribute] (Program) at (3,11) {Program};
\node[attribute] (Grade) at (6,11) {Grade};
\node[attribute] (Time) at (8,10) {Time};
\node[attribute] (Location) at (8.5,12) {Location};
\node[attribute] (Year) at (10,11) {\dashuline{Year}};
\node[attribute] (Cap) at (12,10) {Cap};
\node[attribute] (PName) at (14,11) {PName};
\node[attribute] (Department) at (17.5,12) {Department};
\node[attribute] (Title) at (19,11) {Title};
\node[entity] (Student) at (1,8) {Student};
\node[relationship] (Takes) at (6,8) {Takes};
\node[entity] [style=double] (Offering) at (10,8) {Offering};
\node[relationship] (Teaches) at (14,8) {Teaches};
\node[entity] (Prof) at (17,8) {Prof};
\path (Student) edge (SName);
\path (Student) edge (SID);
\path (Student) edge (Program);
\path (Takes) edge (Grade);
\path (Offering) edge (Time);
\path (Offering) edge (Location);
\path (Offering) edge (Year);
\path (Offering) edge (Cap);
\path (Prof) edge (PName);
\path (Prof) edge (EID);
\path (Prof) edge (Department);
\path (Prof) edge (Title);
\path (Student) edge node[pos=0.8, above] {M} (Takes);
\path (Offering) edge [style=double] node[pos=0.8, above] {N} (Takes);
\path (Offering) edge [style=double] node[pos=0.8, above] {M} (Teaches);
\path (Prof) edge node[pos=0.8, above] {1} (Teaches);
\node[attribute] (Date) at (-1,5) {Date};
\node[relationship] (Coop) at (1,5) {Coop};
\node[relationship] (TA) at (4,5) {TA};
\node[attribute] (Term) at (6,5) {\dashuline{Term}};
\node[relationship] [style=double] (OfferedOf) at (10,5) {OfferedOf};
\node[attribute] (SectionNumber) at (15,5) {\dashuline{Section\#}};
\path (Coop) edge (Date);
\path (Student) edge node[pos=0.8, above] {N} (TA);
\path (Offering) edge node[pos=0.8, above] {1} (TA);
\path (Offering) edge (Term);
\path (Offering) edge (SectionNumber);
\path (Offering) edge [style=double] node[pos=0.8, right] {M} (OfferedOf);
\node[entity] (Company) at (1,2) {Company};
\node[relationship] (Requires) at (4,2) {Requires};
\node[entity] (Course) at (11,2) {Course};
\node[relationship] (Develop) at (17,2) {Develop};
\path (Student) edge node[pos=0.8, left] {M} (Coop);
\path (Company) edge node[pos=0.8, left] {1} (Coop);
\path (Course) edge node[pos=0.8, right] {N} (OfferedOf);
\path (Prof) edge node[pos=0.8, right] {N} (Develop);
\path (Course) edge [style=double] node[pos=0.8, above] {M} (Develop);
\path (Requires) edge[bend left=20] node[pos=0.3, above] {prerequisite} node[pos=0.8, above] {M} (Course);
\path (Requires) edge[bend right=20] node[pos=0.3, above] {prerequisite\_of} node[pos=0.8, above] {N} (Course);
\node[attribute] (CompanyName) at (0,0) {\underline{CName}};
\node[attribute] (Phone) at (3,0) {Phone};
\node[attribute] (CID) at (8,0) {\underline{CID}};
\node[attribute] (CourseName) at (11,0) {CName};
\node[attribute] (Credit) at (14,0) {Credit};
\path (Company) edge (CompanyName);
\path (Company) edge (Phone);
\path (Course) edge (CourseName);
\path (Course) edge (CID);
\path (Course) edge (Credit);
\end{tikzpicture}

Map the provided ERD into a Relational Database schema.
Make sure to include the proper primary keys and foreign keys.
\SolutionFive

\newpage

\item \label{QuestionSix}
Consider \rmp{[5]}this ERD.

\begin{tikzpicture}%[scale=0.9]
\tikzstyle{every entity}=[style={draw, thick, fill=white}]
\tikzstyle{every attribute}=[style={draw, thick}]
\tikzstyle{every relationship}=[style={draw, thick, fill=white}]
\node[attribute] (Content) at (14,12) {Content};
\node[attribute] (FTitle) at (0,11) {\underline{FTitle}};
\node[attribute] (CourseNumber) at (3,11) {\underline{Course\#}};
\node[attribute] (TTitle) at (6,11) {\dashuline{TTitle}};
\node[attribute] (TDescription) at (9,11) {TDescription};
\node[attribute] (Subject) at (12,11) {Subject};
\node[attribute] (TID) at (15,11) {\underline{TID}};
\node[entity] (Forum) at (1,8) {Forum};
\path (Forum) edge (FTitle);
\path (Forum) edge (CourseNumber);
\node[relationship] (Contain) [style=double] at (4,8) {Contain};
\path (Forum) edge node[pos=0.8, above] {1} (Contain);
\node[entity] (Topic) [style=double] at (7,8) {Topic};
\path (Topic) edge [style=double] node[pos=0.8, above] {N} (Contain);
\path (Topic) edge (TTitle);
\path (Topic) edge (TDescription);
\node[relationship] (Have) at (10,8) {Have};
\path (Topic) edge node[pos=0.8, above] {1} (Have);
\node[entity] (Thread) at (14,8) {Thread};
\path (Thread) edge [style=double] node[pos=0.8, above] {N} (Have);
\path (Thread) edge (Subject);
\path (Thread) edge (Content);
\path (Thread) edge (TID);
\node[attribute] (FDescription) at (0,6) {FDescription};
\path (Forum) edge (FDescription);
\node[attribute] (Threads) [style=dashed] at (5,6) {\#Threads};
\node[attribute] (LastPost) [style=dashed] at (8,6) {LastPost};
\node[attribute] (RWhen) at (11,6) {When};
\node[attribute] (Posts) [style=dashed] at (6,4) {\#Posts};
\path (Topic) edge (Threads);
\path (Topic) edge (LastPost);
\path (Topic) edge (Posts);
\node[relationship] (Post) at (9,4) {Post};
\path (Thread) edge[bend right=40] [style=double] node[pos=0.8, left] {N} (Post);
\node[attribute] (PWhen) at (9,2) {When};
\node[relationship] (Reply) at (11,4) {Reply};
\node[relationship] (Read) at (13,4) {Read};
\node[relationship] (Subscribe) at (15,4) {Subscribe};
\path (Reply) edge (RWhen);
\path (Post) edge (PWhen);
\node[entity] (User) at (11,2) {User};
\path (User) edge node[pos=0.8, left] {1} (Post);
\path (User) edge node[pos=0.8, left] {N} (Reply);
\path (User) edge node[pos=0.8, left] {M} (Read);
\path (User) edge node[pos=0.8, left] {N} (Subscribe);
\path (Thread) edge node[pos=0.8, left] {M} (Reply);
\path (Thread) edge node[pos=0.8, left] {N} (Read);
\path (Thread) edge node[pos=0.8, left] {M} (Subscribe);
\node[attribute] (UID) at (10,0) {\underline{UID}};
\node[attribute] (UName) at (12,0) {Name};
\path (User) edge (UID);
\path (User) edge (UName);
\end{tikzpicture}

Map the provided ERD into a Relational Database schema.
Make sure to include the proper primary keys and foreign keys.
\SolutionSix

\newpage

\item \label{QuestionSeven}
Consider \rmp{[5]}the provided \textbf{Sales} Relational Database model:

\begin{tikzpicture}[relation/.style={rectangle split, rectangle split parts=#1, rectangle split part align=base, draw, anchor=center, align=center, text height=3mm, text centered}]\hspace*{-2.0cm}

% RELATIONS

\node (customertitle) {\textbf{Customer}};

\node [relation=5, rectangle split horizontal, anchor=north west, below=0.6cm of customertitle.west, anchor=west] (customer)
{\underline{CID}%
\nodepart{two} Name
\nodepart{three} Address
\nodepart{four} Contact
\nodepart{five} Credit\_Limit};

\node [below=2.0cm of customer.west, anchor=west] (ordertitle) {\textbf{Order}};

\node [relation=4, rectangle split horizontal, below=0.6cm of ordertitle.west, anchor=west] (order)
{\underline{OID}%
\nodepart{two} Customer\_ID
\nodepart{three} Reference
\nodepart{four} Purchase\_Date};

\node [below=1.1cm of customer.east, anchor=west] (manufacturertitle) {\textbf{Manufacturer}};

\node [relation=4, rectangle split horizontal, anchor=north west, below=0.6cm of manufacturertitle.west, anchor=west] (manufacturer)
{\underline{MID}%
\nodepart{two} Manufacturer\_Name
\nodepart{three} Address
\nodepart{four} Contact};

\node [below=2.0cm of manufacturer.west, anchor=west] (producttitle) {\textbf{Product}};

\node [relation=5, rectangle split horizontal, anchor=north west, below=0.6cm of producttitle.west, anchor=west] (product)
{\underline{PID}%
\nodepart{two} Name
\nodepart{three} Colour
\nodepart{four} Manufactured\_Date
\nodepart{five} Manufacturer\_ID};

\node [below=2.5cm of order.west, anchor=west] (orderitemstitle) {\textbf{Order-Items}};

\node [relation=5, rectangle split horizontal, anchor=north west, below=0.6cm of orderitemstitle.west, anchor=west] (orderitems)
{\underline{OID}%
\nodepart{two} \underline{Product\_ID}
\nodepart{three} Qty
\nodepart{four} Warranty\#
\nodepart{five} Unit\_Price
};

% FOREIGN KEYS

\draw[-latex] (order.two north) --  ++(0,+1.0) -| ($(customer.one south) + (0.25,0)$);

\draw[-latex] (orderitems.one west) -| ++(-1.0,0) |- ($(order.one west) + (0,0)$);

\draw[-latex] (orderitems.two south) |- ++(0,-0.60) -| ($(product.one south) + (0.00,0)$);

\draw[-latex] (product.five north) |- ++(0,1.0) -| ($(manufacturer.one south) + (-0.0,0)$);

\end{tikzpicture}

\noindent
The database involves the displayed relations, where as usual primary keys are underlined, and arrows indicate foreign keys.

\noindent
The Sales database stores the following information.
\begin{itemize}
\item{
A customer might place some order.
}
\item{
An order contains some item(s).
}
\item{
An item is a product made by some manufacturer, who might offer a warranty for the product.
}
\end{itemize}
Reverse engineer the schema, to re-construct the ERD which created it.
\begin{itemize}
\item{
Make your own assumptions based on your understanding of the problem when needed.
}
\item{
Your assumptions will be graded by their reasonableness.
}
\item{
You can fill in the missing attributes like names, titles etc.
}
\item{
Make sure to also properly specify all primary keys, participation constraints and cardinality constraints.
}
\end{itemize}
\SolutionSeven

\newpage

\item \label{QuestionEight}
Consider \rmp{[5]}the provided \textbf{Shipping} ERD:

\begin{tikzpicture}%[scale=0.9]
\tikzstyle{every entity}=[style={draw, thick, fill=white}]
\tikzstyle{every attribute}=[style={draw, thick}]
\tikzstyle{every relationship}=[style={draw, thick, fill=white}]
\node[entity] [style=double] (SHIP_MOVEMENT) at (3,16) {SHIP\_MOVEMENT};
\node[attribute] (Timestamp) at (9,17) {\dashuline{Timestamp}};
\node[attribute] (Date) at (13,18) {Date};
\node[attribute] (Time) at (13,16) {Time};
\path (Timestamp) edge (Date);
\path (Timestamp) edge (Time);
\node[attribute] (Longitude) at (9,16) {Longitude};
\node[attribute] (Latitude) at (9,15) {Latitude};
\path (SHIP_MOVEMENT) edge (Longitude);
\path (SHIP_MOVEMENT) edge (Latitude);
\path (SHIP_MOVEMENT) edge (Timestamp);
\node[relationship] [style=double] (HISTORY) at (3,13) {HISTORY};
\path (SHIP_MOVEMENT) edge[style=double] node[pos=0.8, right] {N} (HISTORY);
\node[entity] (SHIP) at (3,10) {SHIP};
\path (SHIP) edge node[pos=0.8, right] {1} (HISTORY);
\node[attribute] (SName) at (0,11) {\underline{SName}};
\node[attribute] (Owner) at (0,9) {Owner};
\path (SHIP) edge (SName);
\path (SHIP) edge (Owner);
\node[relationship] (TYPE) at (8,10) {TYPE};
\node[entity] (SHIP_TYPE) at (13,10) {SHIP\_TYPE};
\path (SHIP) edge[style=double] node[pos=0.8, above] {N} (TYPE);
\path (SHIP_TYPE) edge node[pos=0.8, above] {1} (TYPE);
\node[attribute] (Type) at (10,12) {\underline{Type}};
\node[attribute] (Tonnage) at (13,12) {Tonnage};
\node[attribute] (Hull) at (16,12) {Hull};
\path (SHIP_TYPE) edge (Type);
\path (SHIP_TYPE) edge (Tonnage);
\path (SHIP_TYPE) edge (Hull);
\node[relationship] (HOME_PORT) at (3,6) {HOME\_PORT};
\path (SHIP) edge node[pos=0.8, right] {N} (HOME_PORT);
\node[relationship] [style=double] (VISITSR) at (6,6) {VISITS};
\node[entity] [style=double] (VISITSE) at (9,6) {VISITS};
\path (SHIP) edge node[pos=0.8, right] {N}  (VISITSR);
\node[attribute] (StartDate) at (9,8) {\dashuline{StartDate}};
\node[attribute] (EndDate) at (13,8) {EndDate};
\node[entity] (STATECOUNTRY) at (13,4) {STATE/COUNTRY};
\node[attribute] (StateName) at (12,6) {\underline{Name}};
\node[attribute] (Continent) at (15,6) {Continent};
\node[relationship] [style=double] (IN) at (8,4) {IN};
\node[entity] (SEAOCEANLAKE) at (13,0) {SEA/OCEAN/LAKE};
\node[attribute] (WName) at (13,2) {\underline{Name}};
\node[relationship] (ON) at (8,0) {ON};
\node[entity] [style=double] (PORT) at (3,2) {PORT};
\node[attribute] (PName) at (0,2) {\dashuline{PName}};
\path (PORT) edge node[pos=0.8, right] {1} (HOME_PORT);
\path (PORT) edge (PName);
\path (PORT) edge node[pos=0.8, right] {N}  (VISITSR);
\path (VISITSE) edge [style=double] node[pos=0.8, above] {1} (VISITSR);
\path (VISITSE) edge (StartDate);
\path (VISITSE) edge (EndDate);
\path (STATECOUNTRY) edge (StateName);
\path (STATECOUNTRY) edge (Continent);
\path (SEAOCEANLAKE) edge (WName);
\path (PORT) edge[style=double] node[pos=0.8, above] {N} (IN);
\path (PORT) edge[style=double] node[pos=0.8, above] {N} (ON);
\path (STATECOUNTRY) edge node[pos=0.8, above] {1} (IN);
\path (SEAOCEANLAKE) edge node[pos=0.8, above] {1} (ON);
\end{tikzpicture}

\newpage

Modify the Shipping ERD to accommodate the following changes:
\begin{itemize}
\item{
Add an ID to every port so that it can be uniquely identified on its own.
}
\item{
Due to the improved precision of GPS, ship movement can be tracked using the ship's time stamp, longitude and latitude.
}
\item{
Ship names are no longer unique as they are regulated by different countries.
\begin{itemize}
\item{
Ships registered in different countries might have the same name.
}
\item{
As such, more ship owner information needs to be included, including their names, addresses and phone numbers.
}
\end{itemize}
\item{
A ship might be owned by multiple owners and an owner must own at least one ship.
}
}
\item{
Every ship now must have a home port.
}
\item{
We need to include information about whether a state/country is near an ocean, a lake or a sea.
If it is, indicate that the country is near the appropriate body of water.
}
\item{
Also a port now could be on multiple bodies of water (sea/ocean/lake).
For example, ports near a river entrance to an ocean could be defined as being on both bodies of water.
}
\end{itemize}
\SolutionEight

\end{enumerate}

%\newpage

\begin{center}
\includegraphics[scale=0.65]{A03-Great-West-Life-Claim-Form.png}
\end{center}

\end{document}