MIS 301 – Introduction to Business Information Systems – Spring 2014

Section 007 (Thursday7:20AM – 10:00PM)

Homework #4 – Database Design (Total 100 points)

Due on Thursday, April 10th, 11:59 PM EST

Read all questions and instructions carefully.

On-the-Vine Vineyard

(Based on Plug-In T5, Making Business Decisions #4, Page 272)

AT THE END OF THIS DOUCUMENT

On-the-Vine Vineyard, Inc., is a small, family-owned winery, one of California’s largest in Sonoma Valley, specializing in limited production of premium quality wine.

The Employees: On-the-Vine currently employs over 12 full-time employees, with positions ranging from administrative assistant to winemaker. Each employee is assigned a unique identification number. In addition to the employee’s name, position, and identification number, the company also records each employee’s Social Security number, address, phone number, and emergency contact.

The Vineyards: On-the-Vine owns several vineyards, each in separate locations. Each vineyard is managed by a single employee and represented by unique number, name, address, and size. No employee manages more than one vineyard.

The Wine: Each bottle of wine produced is given a unique identification number in addition to its name. Other information recorded for each wine includes its vintage year, category (e.g., dry red, dessert, etc.), vineyard where the wine is produced, price, and percent alcohol. Also recorded is the winemaker (employee) in charge of making that bottle of wine. A winemaker may be responsible for producing more than one bottle of wine.

The Customers: On-the-Vine sells its wine to individuals via the Internet. All customers are assigned a unique customer identification number, and this number is recorded along with their first name, last name, address, phone number, and date of birth.

The Orders: All customers obtain their products by placing orders directly with On-the-Vine. A customer can purchase more than one bottle of wine at one order. Each order is assigned a unique order number, and the date of order, the date of shipping, and the shipping status are recorded as well.

Q1. Carefully read the above business description and draw an Entity-Relationship Diagram (ERD) for the database that manages information on employees, customers, and so on.

• Your ERD should include all necessary entities, attributes, and relationships.
• You need to specify relationship names and types (1:1, 1:M, or M:N) as well.
• You do not need to include cardinalities.
• A relationship should have a name with a verb.

PLEASE FIRST READ Plug-In T5 (textbook p. 254-263), and ERD examples on Blackboard. Do not jump into the homework too fast without studying.

Grading Rubrics

• One missing entity: -10 points
• One redundant entity: -4 points
• One missing attribute: -4 points
• One redundant attribute: -3 points
• An entity identifier is not specified in an entity: -2 points
• One missing relationship: -8 points
• One missing relationship name: -2 points
• One missing relationship type (1:1, 1:M, or M:N): -3 points
• One incorrect relationship type: -2 points

Submission Instruction

• Submit your homework as a Word or PowerPoint file into Blackboard (http://mymason.gmu.edu/) by Thursday, Apr,3rd, 11:59PM (Eastern Standard Time).
• This is an individual assignment. However, if you’d like, you may collaborate with no more than one classmate. Still, each should create and submit a separate file individually. In addition, both of the two students must mention whom they work with in the file. Otherwise, it will be considered an Honor Code violation and reported to Office for Academic Integrity immediately.
• Late submission is allowed, but there will be 10% penalty per each 12 hours.