Assignment 2: Database Design (E-R Diagrams) Solution

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.




这个作业是一个大楼的蓝图 我们需要有蓝图才能够建好一个database




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.




每个entity的关系

这个蓝图我们使用 ERdiagram的形式来表达



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.




上面的每个都是一个entity

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.





































需要新的entity？




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. product和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.



attribute data




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.

an employee manage one and only one 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.