CS330 Assignment 2
Due: Wednesday, June 23rd at 5:00 pm
Coverage
The questions in this assignment are based on the material covered up to and including slide 223 in
Lecture 11.
Submission Requirements
For this assignment, only electronic submissions will be acceptable. You will receive an email with
a link to Crowdmark which you use to submit your assignment. Marks will be deducted if the
submission is hard to read.
Assignments that are submitted up to 24 hours late will be accepted and graded with a 10% penalty.
Assignments submitted beyond that point will not be accepted or graded.
For Crowdmark, you can submit as many times as you want (before the deadline) and it will only
keep the last submission. After the deadline, Crowdmark will only accept one submission.
If you have not used Crowdmark before to submit an assessment, have a look at
https://crowdmark.com/help/completing-and-submitting-an-assessment/.
Submitting a PDF or Image File
For these questions you will be required to submit a PDF or image file for your answer. Crowdmark
accepts JPG, PNG and PDF format. If you are not familiar with how to submit these types of files to
Crowdmark see the section “Answering PDF/image file upload questions” in the link provide
above. Crowdmark does not accept HEIC format. That web page also describes how to convert
iPhone/iPad photos (HEIC format) to JPEG format.
Overview of BSO
The Bicycle Sellers of Ontario (BSO) was started 20 years ago when a group of eight owners of
bicycle stores, decided to form a single company in order to take advantage of their combined larger
purchasing power.
Requirements for New Database
BSO wants to create a database to track the following data. For customers they want to track CNum
(a customer number which is unique for each customer), CName, CPhone, CEmail.
They have grown to over 30 locations in Southern Ontario and the data they want to keep track of
for each location is LNum (an location number which is unique for each location), LAddress,
LPhone and LEmail.
In order to keep the database simple, a single entity call Items could either be a bicycle, a bicycle
accessory (such as a light or helmet) or a bicycle part. The data they store about each item includes
INum (an item number which is unique for each type of item), IName, WholeSalePrice (the price

BSO paid for it) and ListPrice. They also want to keep track of the quantity of each item available at
each location.
When a customer makes a purchase, BSO wants to the database to create a unique PNum (purchase
number) and also track the customer who made the purchase, the date, the location, the items the
customer purchase and the quantity of each item purchased (the customer could buy two or more of
the same item). Sometimes the salesperson will sell items below the ListPrice, so BSO also wants to
keep track of the actual price the item as sold for. For a given PNum, all items with the same INum
would be sold at the same actual price.
A customer can also bring their own bike in for repair or a tune-up which BSO refers to as Service.
The bike does not have to be bought from BSO to be serviced by BSO. In order to ensure the
customer gets their bike back (and not someone else’s by mistake), when a customer brings a bike
in for service, BSO should keep track of the customer who brought in the bike and also track the
SerialNum (serial number) the BikeName and BikeModel.
The BSO database should create a unique SNum (service number) for each bike (a customer can
bring in more than one bike for service) each time the bike is brought in for service. The database
should also keep track of the location and date the service was requested, the problem the customer
wants fixed, any items that were used to repair the bike (e.g. two new tires) and their quantity. Items
used in servicing are always sold to the customer at ListPrice. The data base should also track how
much the customer got charged for labour when the bike was serviced.
Other than what is listed and needed above, the database should not store any additional data.
Q1 ER Diagram
Create and upload a PDF or image file of an ER diagram to Crowdmark. Use the diagram below as
the starting point and add entities, relationships and cardinalities to complete the design. There is
also no need to indication partial or total participation. You may use a single line in both cases.
There is no need to show the attributes. You will be listing attributes in the next question. Even
though you are not listing attributes, your ER Diagram should be consistent with a design that is in
Boyce-Codd normal form.
In the diagram below, CBike refers a customer’s bike. I’ve uploaded a PowerPoint version of the
diagram to the Assignments section of Learn called A2 Q1 template.pptx which you may use. You
may also hand draw this diagram if you would prefer, but use the same diagram in the same
orientation as a starting point.
There should be no multivalued attributes in your design.
When naming entities or relationships use meaningful names, preferably ones that come directly
from the Requirements for New Database description. [10 marks]

Q2 Functional Dependencies
For the attributes in your design, list all the functional dependencies (as we do in slide 215, 217 and
220) that include LNum (location number) on either the left or right hand side of the dependency.
Again, your answer should be consistent with a design that is in Boyce-Codd normal form. Create
and upload a PDF or image file of your answer to Crowdmark. [7 points]
Q3 Database Tables
List the tables in your design (as we do in slides 218 and 221) that include the attribute INum (item
number). For each table identify the primary key or keys by underlining them and identify any
foreign keys by putting them in bold font. Create and upload a PDF or image file of your answer to
Crowdmark. [8 points]
Q4 Database Tables
List the tables in your design (as we do in slides 218 and 221) that do not include the attribute INum
(item number) or attribute LNum (location number). I.e. if either attribue (or both) are in the table,
do not include it in your answer. For each table you do include, identify the primary key or keys by
underlining them and identify any foreign keys by putting them in bold font. Create and upload a
PDF or image file of your answer to Crowdmark. [3 points]

Assignment 2
Solutions
Q1 ER Diagram [10 points]
Solution:





Q2 Functional Dependencies [7 points]
Solution:
LNum → LAddress, LPhone, LEmail
PNum → CNum, LNum, PDate
SNum → SerialNum, LNum, SDate, Problem, Labour
INum, LNum → LQuantity
You may have different names for the attributes in green italics.

Q3 Database Tables [8 points]
Solution:
of (PNum, INum, PQuantity, ActualPrice)
uses (SNum, INum, SQuantity)
Items (INum, IName, WholeSalePrice, ListPrice)
stored_at (INum, LNum, LQuantity)
You may have different names for the attributes in green italics.


Q4 Database Tables [3 points]
Solution:
Customer (CNum, CName, CPhone, CEmail)
CBike (SerialNum, CNum, BikeName, BikeModel)


欢迎咨询51作业君