Assignment 2: Database Design (E-R Diagrams)

Assignment 2:
Database Design (E-R Diagrams)

Project 1: Entity Relationship Diagram
Using the E-R drawing tool (Dia.exe), create the following E-R Diagram.
 (You MUST use: Dia.exe – AND you MUST submit a .dia file (do NOT submit a jpeg or an image file)
 You can NOT use any other tool to draw the diagram
 – use of any other method or tool will NOT be accepted or graded.
This project requires you to create a database design. Your design will be documented in a set of
Entity-Relationship diagrams using the representation as shown in the lecture materials. Draw a set
of Entity-Relationship diagrams to model the following scenario.
You realize that in order to run your company you will need to start tracking some basic information
that deals with your customers, your employees, your products and your invoices. You will need to
track the relationships between them, specifically, an invoice is created when a product is sold and
this invoice will contain a list of the products in each invoiced order. Also, a customer will give each
product a rating based on how much they like each product they have purchased. Finally, an
employee will be in charge of (manage) a product.
You need to track the following:
For the customer, your design must be able to store:
• the customer’s Last Name.
• the customer’s First Name.
• the customer’s street
• the customer’s city
• the customer’s province
• the customer’s postal code
• the customer’s phone number
• the customer’s payment method (only options: check –or- credit card)
note:
 - the customer’s address (street, city, province and postal code) data can be blank.
 - i.e. the customer’s listing can exist even of these fields are left unfilled. 
For the product, your design must be able to store:
• the product’s name (i.e. Smiley Pin, Comic Book Bow Tie, Niblick Soft, etc.)
• the product ‘s classification (i.e. shirt, novelty, edible, pillow, pant, shoes, etc.)
• the product’s retail cost to the consumer (price in dollars).
• the product’s size (Small, Medium or Large)
• the product’s shipping weight classification (Light, Medium or Heavy)
For the invoice (when a customer orders a product), your design must be able to store:
• the invoice date
• the invoice total amount
• the invoice status (New, Shipped or Paid)
For the employee your design must be able to store:
• the employee’s last name
• the employee’s first name
All attributes are to be considered as mandatory unless specifically stated differently above.
HINT: a field is missing in each of the above. Hopefully, it will be very obvious ….
Next, you need to store the following relationships:
When a customer orders a product, an invoice will be created. Each invoice will consist of a single
customer associated to a single invoice. Each invoice can only be created by a single customer.
• A customer must have at least one invoice associated (otherwise why would they be in the
system?). A customer can have more than one invoice if they have order a number of times
from your company. An invoice must have a customer associated (otherwise, why was it
created?). An invoice can only be associated with a single customer.
• An invoice will contain at least one product. It can contain more than one product if the
customer orders multiple items.
• An invoice must have at least one product associated with it. A product might not ever have
been order, so it is might not be associated with any invoice.
• The customer can rate the product after having received it. These products were purchased by
the customer. This rating will be saved as data and will based on a scale of whole numbers
between 0 to 10 (no fractions like 4.7 allowed).
• A customer can rate many products and a product can be rated by many customers.
• A product might not have any ratings listed (no customer has rated a product) and a customer
may never have rated any of the product(s) they have purchased.
• The date the customer rated a product will also be saved in the database. hint: This data point
is associated with the rating and not the customer or the product directly.
• An employee may be assigned to be in charge of (manage) a product in your company. This
means they will be responsible for looking after this product. A product will only be managed
by one employee only and an employee cannot look after (manage) more than one product.
Although every product must have an employee that looks after (manages) it, not all
employees will be assigned to manage a product. 
You cannot add any extra object entities beyond what is required to meet the specifications in
this assignment. You will be deducted marks if you have extraneous or unnecessary entities and
you will lose marks (obviously) if you do not have enough entities.
In other words: Your diagram must be correct and meet the specifications given.
No more and no less will be acceptable.
You will create your answer to this project using the graphic design tool Dia.exe.
You MUST follow all the standards and formats in the class notes
Arrow Size of your Lines MUST be exactly 0.70 (makes the lines easier to read).
The file must be named:
 youraccountname_ER_diagram.dia
 (where .dia indicates this is a dia created diagram)
Attach the file youraccountname_ER_diagram.dia to your submission.
You must identify yourself on the document. The TA will NOT grade the document if this is
missing. Somewhere immediately visible in the actual design file (.dia) you must include:
 - your first and last name
 - your Western ID (see below for a description of your Western ID)
 - your student number
You MUST put your identification in the diagram or you will receive a zero (0) for the Project.
No exceptions for ‘I did not see the instructions’ or for ‘I just forgot’…
hint: do NOT use a Table object for this – just use Text.
Project 2: Information Systems Questions about Your Company
Create an MS Word document and complete the following questions pertaining to the business you
described in Assignment One (1).
Each answer must be comprehensive (more than one sentence). Each answer requires at least four
sentences. The entire Project 2 should be at least approximately 500 words. It is expected that some
thought and explanation is included in this section.
1.) What Costs (money out) can you identify based on a business run out of your basement with
 just yourself as the only employee?
 - list some of the costs and the estimate of how much per month for each cost.
2.) What type of data do you think you will to track in the beginning?
 For example, you will track sales and invoices. What other things will you keep track of?
 - this should be at least a couple of paragraphs (minimum 250 words) in length.
3.) Describe why you think your product will succeed (why did you select this).
 - this should be at least a couple of paragraphs (minimum 250 words) in length.
The format of this document should be identical to format you used in Assignment One (1).
Place your name, followed by the company name at the top.
Fill in the required information after. 
At the end of the document, include your name, Student number and Western ID (the first part of your
Western email (i.e. if your email is – ibrai2328@uwo.ca your ID will be – ibrai2328)
Formatting is not important as long as the document is easy to follow:
This document must be a Word file saved and submitted as a .doc (or .docx) file
The name must be a combination of your Western Account Name and the name of your company.
The file name must be youraccountname_companyname_A2.doc (or .docx)
 - example (from above) ibrai2328_MaggicSoftware_A2.docx
Submission Instructions:
You must upload and submit, via the Assignment Section in the CS1032 Web Site in OWL:
 - Both [ two (2) ] files:
youraccountname_ER_Diagram.dia
youraccountname_yourcompanyname_A2.docx (or .doc for earlier versions)
NOTE: The E-R Diagram MUST be a DIA file (has the extension .dia).
DO NOT SUBMIT an image (.jpeg .bmp) file.
You will lose major marks if you submit anything other than a .dia file.
NOTE: Beware of the tilde (~)
 - DIA will sometimes save a backup copy of your work and add the tilde (~)
 at the end of the file extension. ( example: Assignment2.dia~ )
 - Do NOT submit this backup file. It can NOT be opened by the TAs.
 - It is YOUR responsibility to ensure the correct files are submitted.
It is your responsibility to ensure the files have been submitted in OWL.
Please check and make sure you have received the confirming email and then check that the two (2)
files (you must submit two (2) files for this assignment) have been uploaded correctly.
You must do both Projects in this assignment. This is Assignment Two, comprised of two (2) parts,
Project 1, Project 2. Both projects are to be completed and submitted. There was confusion on
Assignment One regarding what was required.