Department of Computer Science

Assignment 3: Database Design (E-R Diagrams)

Important for macOS users: If you will be using MyVLab for this assignment, it is important that you do not save directly to the Z: drive (your local computer). You should save to the H: drive and only copy your files to the Z: drive when you are ready to submit your work or are backing up your work. Saving directly to the Z: drive can corrupt your files if your internet connection drops while saving.

All assignments must be completed individually. Do not share your work or use another student’s work in anyway. If you need assistance with this assignment please post on the course forums, e-mail a TA/instructor, or attend an office/consulting hour.

See OWL Assignment tab for assignment due date and late policy.

Part 1: Entity Relationship Diagram

Using the Dia Diagram Editor (http://dia-installer.de), create the E-R Diagram described in this assignment. You MUST submit your diagram as a .dia file.

This project requires you to create a database design. Your design will be documented in an Entity-Relationship Diagram (ERD) using Crow’s Foot notation. You should match the style. and notation used in the tutorial material and slides (including notation for mandatory/nullifiable attributes).

Your company from Assignment 1 and 2 has grown significantly from being run out of your home. You now are now a legitimate small business with a handful of employees and your own building which houses both your manufacturing plant and a small office. You currently sell your product directly to consumers via your website.

Unfortunately, your company has not invested much in their information systems and you are still using some of the same Excel sheets from when you were a one-person operation working out of your garage. This is causing problems as you are now having trouble managing your customer support services (e.g. product returns and replacements for defective products) and would like to more effectively store and process information about your customers.

To resolve this dilemma, you propose creating a central database to power your customer relations and support systems. You discuss the new system with your employees and gather their requirements for the database. Now you must turn them into an ERD.

The following is a list of the information that must be tracked and contained in your new database.

Attributes and Entities

For each Customer you do business with, the design must be able to store:

· The Email address of the customer, no two customers can have the same Email.

· The First Name and Last Name of the customer.

· The customer’s home Address.

· The customer’s Phone Number, but only if they provided one (this is optional).

· The customer’s Birthday so we can send them a birthday coupon. Customers are not required to provide a birthday (providing it is optional).

· Two additional attributes you wish to track about your customers. This can be any attributes that are reasonable and applicable to your businesses’ customers. You can decide on if they are required, unique, etc. but this should make sense for the attribute. They cannot be a key (primary or foreign).

For each Employee of your company, the design must be able to store:

· A unique User Name assigned to this employee that they use to login to your customer relation and support system.

· The employee’s First Name 

· The employee’s Last Name

· The employee's home Address.

· The employee’s SIN, no two employees can have the same SIN, it must be unique (can not be used for the primary key).

· The employee’s Phone Number, but only if we have one on record (may not have one for each employee).

· An attribute called Active which represents if this is a current employee or not (e.g. retired or moved to a different company).

For each of the Products you sell, the design must be able to store:

· The Title or name of the product (not necessarily unique).

· A short Description of the product (not necessarily unique).

· A URL to a Photo of the product hosted on your website (but only if one is available).

· The product’s Sale Price (i.e. the price you sell a unit of this product for).

· The product’s Cost to Manufacture (i.e. the cost for you to manufacture a unit of the product).

· The Quantity In Stock in your inventory for this product.

· Two additional attributes you wish to track about your products. This can be any attributes that are reasonable and applicable to all of your businesses’ products. You can decide on if they are required, unique, etc. but this should make sense for the attribute. They cannot be a key (primary or foreign).

An Invoice for each sale made via your businesses’ website, the design must be able to store:

· A unique Invoice ID that is different for each invoice created.

· The Coupon Code used for this order if one was used (not all orders will have a coupon code).

· The Total Price of the order.

· The Payment Method used (e.g. VISA, Debit, PayPal, etc.).

Customers can create support Tickets when they have an issue with your product. For each Ticket, the design must be able to store:

· A Subject that gives a quick one sentence summary of the issue with the product.

· A longer text-based Description of the issue.

· The Status of the ticket (e.g. open, resolved, investigating, etc.).

· The Severity of the issue described in the ticket (e.g. low, medium, high, critical, etc.).

If the product must be returned or replaced a Return Merchandise Authorization (RMA) must be created and approved. For each RMA, the design must be able to store:

· If the RMA has been Approved or not (this would be a True or False value).

· If the return has been Received or not. Your support team will request that the customer ship the product back to them before a replacement is sent to the customer.

· The Address to ship the replacement to.

· If the replacement has been Sent yet or not (this would be another True or False value).

All attributes are to be considered as mandatory (required) unless stated differently above or required to be optional according to the above requirements.

All attributes are to be considered as non-unique unless the design specifically requires them to not contain duplicate entries. For example, there may be products with the same title, multiple employees with both the same first and last name, and customers with the same name, address, etc. However, no two employees will have the same SIN or User Name and no two customers will have the same Email address.

Primary keys: every entity described above must have a single primary key defined. This can be a natural key (an existing attribute) or a surrogate key (adding an attribute to act as a primary key) if no existing attribute is appropriate.

Relationships

Next, you need to store the following relationships:
Each Employee is assigned to zero or more Tickets.

· Employees are assigned to tickets that they handle.

· Not all employees handle tickets, and some Employees are assigned to multiple tickets.

· Tickets are assigned to at most one Employee and some tickets are not yet assigned to any Employee (zero or one).

A Customer creates zero or more support Tickets.

· A customer can create support tickets if they have issues with a product they purchased.

· Not all customers will create support tickets.

· Each ticket is created by exactly one customer.

· Relationship Attribute: This relationship has a relationship attribute named DateCreated that stores the date when the customer created the ticket.

A Product is involved with zero or more support Tickets.

· When a ticket is created by a customer, it is associated with a product the customer is experiencing an issue with.

· Not all products will have a ticket created related to them, and a product can be involved with many tickets.

· A ticket is always associated with exactly one product.

A Ticket may request an RMA if a return is warranted.

· A ticket may request zero or one RMAs.

· An RMA is only requested if a product must be returned.

· An RMA is always requested by exactly one ticket.

An Invoice has one and only one Customer who purchased the products listed in the Invoice.

· All invoices have exactly one customer.

· A customer can have many invoices.

· You will only track customers who have made at least one purchase. As such all customers must have at least one invoice.

An Invoice includes one or more Products.

· Each invoice is for the purchase of one or more products from your online store.

· There may be products in your database that have not yet been purchased, that is, a product is included in zero or more invoices.

· Relationship Attribute: This relationship has a relationship attribute named Quantity that stores how many units of this product is included in the invoice.

· Hint: this is a many-to-many relationship, you may have to do something special in this case.

All relationship attributes are to be considered as mandatory and non-unique unless stated above.

Foreign Keys: You must add foreign keys as required to properly define each relationship. You should only do this when necessary for your database design. Hint: each relationship requires at least one foreign key.

Other Requirements

1. You must label all of your relationships with the text tool ( ).

2. You must include your full name, UWO username, and student number in your diagram using the text tool. There will be a significant mark penalty if this is missing from your diagram.

3. You must organize your entities and relationships in a way that is legible and well laid out. Relationship lines should be drawn at right angles and not overlap. Text should be clear and easy to read.

4. You must use the capitalization and naming conventions shown in the tutorials and slides for entity, relationship, and attribute names.

5. All names (of attributes, relationships, etc.) must be meaningful and clear. It should be easy for the TA grading your assignment to tell which attribute is which in your diagram.

6. You do not need to denote foreign keys as foreign keys, but they must be listed in the correct table and have the correct properties set (i.e. mandatory/nullifiable and uniqueness).

Overall Hints

Hint 1: Your diagram should contain seven (7) entities in total including associative entities (aka relationship entities/junction entities). If you are missing one, you likely forgot about a special case.

Hint 2: ERDs do not show the actual data that will be stored. For example, your Employee entity should contain a “First Name” attribute and not a “Joe” or “Dan” attribute.

Hint 3: In Dia, “Nullable” is the same as optional. Also note that the Unique property of an attribute is not displayed visually but must be set correctly.

Submission of Part 1

Your file must be named:
     youraccountname_ER_diagram.dia 
             (youraccountname is your uwo username)

For example, if your UWO account name is ibatool2, your file should be named: ibatool2_ER_diagram.dia 

NOTE: After uploading your .dia file you MUST redownload it from OWL and check that you uploaded the correct file and that it is working and not corrupted. In the past students have uploaded the wrong file, one ending in .dia~ This is an autosave file made by DIA and not your diagram. Make sure the file extension does not have ~ on the end.

Example:

   Correct file:     dservos5_ER_diagram.dia 

   Incorrect File: dservos5_ER_diagram.dia~

Part 2:  Information Systems Questions about Your Company

Create a Microsoft Word document and complete the following questions pertaining to the business you described in Assignment One. Use the same format as the word document from Assignment One.

Each answer must be comprehensive (more than one sentence). Each answer requires at least four sentences. The entire Project 2 should be at least 600 words. It is expected that some thought and explanation is included in this section.

1.) Identify which of Porter’s Five Forces might influence your company the most and why.

2.) Recall Porter’s Four Competitive Strategies from Chapter 3. What competitive strategy will your company employ? How does this strategy relate to your product and company? Overall, why will you be successful?

3.) Describe the Value Chain involved in your company’s product. What are your company’s Primary Activities in this value chain? Overall, how does your company add value to the final product? As your company is currently quite small, it is ok if this value chain consists mostly of one business process.

4.) Which position in your company are you going to hire first?

- name the position (job title)

- list what will be their duties (what exactly will they be doing as their day-to-day job)

5.) Was this decision (to hire an employee) process Structured or Unstructured (briefly explain your answer)?

6.) Was this decision (to hire an employee) Operational, Managerial or Strategic (briefly explain your answer)?

· The format of this document should be identical to the format you used in Assignment One.

· Place your name, followed by your student number, and then your company name at the top.

· Fill in the required information after.

· 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 .docx file

The name must be a combination of your Western Account Name and the name of your company just as in Assignment 1.

The file name must be youraccountname_companyname_A2.docx

· where youraccountname is your UWO username and companyname is the name of your company.

· For example, if your UWO username is dservos5 and your company is Forest City Ducks, your file should be named: ibatool2_Forest_City_Ducks_A2.docx