联系方式

  • QQ:99515681
  • 邮箱:99515681@qq.com
  • 工作时间:8:00-23:00
  • 微信:codinghelp2

您当前位置:首页 >> Java编程Java编程

日期:2021-07-15 10:00

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]


版权所有:编程辅导网 2020 All Rights Reserved 联系方式:QQ:99515681 电子信箱:99515681@qq.com
免责声明:本站部分内容从网络整理而来,只供参考!如有版权问题可联系本站删除。