VALLEY FURNITURE DELIVERY DATA MODELING ASSIGNMENT

MGMT 4013 TC1, Spring 2018

Your assignment is to create a data model based on the scenario and data below. For your convenience, I have included the steps in the process of creating a data model at the end of this document.

Susan is the manager of a delivery department of a large home furnishings store. Valley Furniture delivers large items such as sofas, bedroom suits, dining suits, and large appliances in the area of a 300-mile radius. The fee for this service is determined by the zone in which the customer wants the items delivered. She has a fleet of delivery vans and trucks of various sizes. She also supervises the employees who make the deliveries.

Susan has been keeping delivery records for her department in a Word document (delivery zone charges and customer information) and Excel spreadsheets (delivery records). See screen shot samples below. She has heard that she could learn more about the patterns in her operations and make them more efficient if these records were stored in a database. You are interning with her company, and she thinks your up-to-date technology skills would be very useful on this project.

You know you will need to know about business rules and management policies to complete a good data model. So, you ask her several questions about how her department operates. Susan tells you that she already has a database for tracking delivery vehicles and scheduling, and she does not want you to include them in this database. She tells you that an inventory database and a customer database already exist in other departments, too, and you should not include them in this database either. She stresses that wants you to focus on delivery data only.

Susan indicates that the delivery charge, payment method, and card information (card number and expiration date) are included with each delivery, because a customer might pay for different purchases with different payment methods. She says that most deliveries are uneventful and result in happy customers. Occasionally, employees reach the wrong address and must contact the customer for better directions to the right location. You realize the data model must include a delivery address, directions, and a customer phone number. You realize you need a delivery event entity. Susan stresses that the customer must sign off that the delivery has occurred and whether he or she is satisfied with any adjustments made with regard to returned items. You understand that while your data model does not have to include this actual signature, it must include an indication of whether the customer has, in fact, signed off.

In a description of the delivery process, she tells you that a delivery must have at least one employee, obviously, but may also include several additional employees on the larger shipments to speed up the load/unload process. In that case, she usually designates the role of leader to one of the employees on that delivery team to minimize confusion and miscommunication. You realize that if you dont know in advance how many employees will be assigned to a delivery, you cant determine how many attributes need to be set up in the delivery entity. You remember that in this situation, you need a separate entity in which you can just add another record for each employee assigned to the delivery.

You ask whether she would like to include employees in this database. She indicates that HR has a database on employees, but she would like to keep a little bit of data in her delivery database, including first and last name and cell phone number. You warn her that best practices are to keep all data about the same thing in the same database, but she says her employees like to use nicknames that are not in the HR database, and the HR database does not always have the employees cell phone number. Shes the boss, so you will do as she asks. You will create an employee entity with a restricted set of attributes.

Susan tells you that occasionally an item is defective or is damaged while being placed in the customers home. These items are brought back to the store immediately on the same delivery vehicle. You realize you must keep track of items returned to the store and why they were returned.

You remember from your MIS course that whenever a situation involves multiple themes, you need a database rather than a spreadsheet. Your task is to create a simple data model for a delivery database to enable Susan to analyze her delivery operations.

You inform Susan that your database must interface correctly with all of these other databases she has mentioned. You will need to know the names, data types, and sizes for the linking fields. Susan says she is willing to contact the stores IS department to get that information. The next day, she gives you the following:

The primary key in the vehicle database is Vehicle ID, which is text and 5 characters long.

The primary key in the employee database is Empl ID, which is text and 4 characters long.

The primary key in the inventory database is Product ID, which is text and 15 characters long.

The primary key in the Customer database is Cust ID, which is text and 10 characters long.

You realize these primary keys will probably become foreign keys in some of the entities in your delivery data model.

You can use some of Susans data, but you will need to improve its structure. Some data is below and some is in the attached spreadsheet file.

Delivery Zone Charges

1	$50
2	$100
3	$150
4	$200
5	$250

Customer Information

JOHN SMITH

981 KINGS DR

NORRISTOWN, AR 72804

SMITH@ATU.EDU

479-999-9971

AMY JOHNSON

982 QUEENS AVE

RUSSELLVILLE, AR 72801

JOHNSON@ATU.EDU

479-999-9972

SARAH JONES

983 PRINCESS DR

NORRISTOWN, AR 72803

JONES@ATU.EDU

479-999-9973

JESSICA, ARNOLD

984 PRINCE DR APT. #5

RUSSELLVILLE, AR 72802

ARNOLD@ATU.EDU

479-999-9974

AARON ROBERTSON

985 JACK RD

NORRISTOWN, AR 72805

ROBERTSON@ATU.EDU

479-999-9975

KIM WINDMERE

986 JOKER DR

NORRISTOWN, AR 72804

WINDMERE@ATU.EDU

479-999-9976

ROBERT JACOBS

987 FIRST ST

NORRISTOWN, AR 72805

JACOBS@ATU.EDU

479-999-9977

JOSE GONZALEZ,

988 SECOND ST

NORRISTOWN, AR 72806

GONZALEZ@ATU.EDU

479-999-9978

MOHAMMED ALI

989 THIRD ST

NORRISTOWN, AR 72804

ALI@ATU.EDU

479-999-9979

LING LEE

990 FOURTH ST

DOVER, AR 72810

LEE@ATU.EDU

479-999-9980

MICHAEL JORDAN

991 FIFTH ST

CLARKSVILLE, AR 72811

JORDAN@ATU.EDU

479-999-9981

JIMMY KIMMEL

992 SIXTH ST

LONDON, AR 72812

KIMMEL@ATU.EDU

479-999-9982

ANGELICA HILL

993 SEVENTH ST

RUSSELLVILLE, AR 72813

HILL@ATU.EDU

479-999-9983

ROBERTA NEWMAN

994 EIGHTH ST

RUSSELLVILLE, AR 72802

NEWMAN@ATU.EDU

479-999-9984

TERRY BRADSHAW

995 NINTH ST

RUSSELLVILLE, AR 72815

BRADSHAW@ATU.EDU

479-999-9985

TOM BRADY

996 TENTH ST

RUSSELLVILLE, AR 72816

BRADY@ATU.EDU

479-999-9986

ALEXIS THOMPSON

997 ELEVENTH ST

RUSSELLVILLE, AR 72817

THOMPSON@ATU.EDU

479-999-9987

Project Requirements

Use this scenario, material in your textbook, and the Tegrity videos MySongs Parts 1 and 2 to guide you in creating a data model that incorporates E/R diagramming techniques. The textbook and the videos have a different scenario, but the principles are the same. You will need to adapt those examples to the data model for your scenario and complete the process for all of the entities, attributes, and relationships your scenario requires.

You have three options listed below for recording your data model in a digital format to submit at a link the Assignments area of Blackboard. My advice is to create your first model in sloppy form with paper and pencil. When you have it worked out, then transfer your data model into one of the formats below.

Use paper and pencil. Your diagram must be neatly drawn, well labeled, and easy to read. Take a very clear photo of the data model or two photos if your model is on two pages. If my assistant and I cant read something, you wont get credit for that part of the diagram. Save the photo as a graphic file (.jpg, .png, etc.) with a file name following the rules for this course (see example below).

Use Word (five bonus points). I recommend use of Shapes. You will find typical blocks and lines in the Shapes area of the menu under the Insert ribbon tab. Add the object attributes and their properties (data type, length, required or not) inside the block designated for each object (dont forget to label the primary key attribute).

Add connecting lines between objects that are directly related. Add the lines and circles for the E/R relationship attributes (mandatory/optional, cardinality) on top of the connection lines. It works best when you group the E/R symbols and place that group in the foreground and the connection line in the background.

Use Visio (10 bonus points). I will award up to 10 extra credit points for using Visio because you will probably be learning a software package that is new to you. (Students who took my Business Process Management course last spring will already know how to use it.)

Visio is available in all lab computers, remotely on the ATU Virtual Desktop (for Apple computers), and can be downloaded free from Microsoft Imagine (contact Susan Morris, the Deans secretary, to get ID/password credentials and the URL of ATUs portal site; smorris@atu.edu).

Visio has stencils for E/R diagrams with all the blocks, connection lines, and E/R relationship attribute symbols needed. The object blocks have the ability to add all the attributes needed and the required properties needed for each attribute (name, data type, length, required or not). The connection lines have the crows-foot and other designations for recording relationship properties in an E/R diagram. There are some extra resources for using Visio in the Data Model folder in the Assignments area of Blackboard and in the Software Application Resources folder.

No matter which of these options you choose, make sure to put the usual identifying information (your name, course and section number, date) in a box in the upper right-hand or upper left-hand corner of the data model. If you use paper and pencil, write this the information clearly. In Word or Visio, insert a text box to add this information.

Save your file with a name that follows the conventions for this course (examples: TroboyKim_VFD_DM.jpg, or other graphic file type, TroboyKim_VFD_DM.docx (Word file), or TroboyKim_VFD_DM.vsdx (Visio). Submit your file at the link in this folder in the Assignments area of Blackboard by the due date listed on the Course Schedule.

Data Model Process

For reference, here is the process to use in creating a data model. See the Tegrity video in Blackboard for an example. The PowerPoint file developed in the video is also posted in this folder.

Identify all entities the manager wants to keep track of. Typical entities are persons, things, places, and events (like transactions).

Describe each entity. Create a list of attributes that describe each entity. These attributes will become fields or columns in your database tables. Just include attributes necessary to this scenario (just the data you will want to keep track of).

Determine the properties for each attribute:

Data type (text, numeric, date, etc.)

Length or size. This is the number of characters for text attributes, the type of number for numeric fields (like integer or long integer for values without decimals, single or double precision for values with decimals, currency, etc.)

Required. This refers to whether a value in the attribute is required or optional when a record is created. (That is, if you say a value is required, you cannot create a new record for that entity without entering a value for this attribute. For example, once the data model is implemented in a database, if the required property is set to yes for the last name field, you would not be able to create a record for a new student without entering in a value for last name.)

Identify an attribute in each entity that can act as an identifier (eventually a primary key in a table in a database).

Identify any direct relationships that might exist between various pairs of entities.

Make sure you can actually link any related entities. This means they will have one attribute in common. Typically, we place the identifier attribute (primary key) of one entity into the other entity as an additional attribute (where it becomes a foreign key). In MySongsNow, the Sales Transaction entity has Customer ID as an attribute (who the song was sold to). Customer ID is the primary key of the Customer entity but it is a foreign key in the Sales Transaction entity. Having Customer ID in both entities will eventually allow the database to connect those two tables.

Describe the properties of each relationship. This involves establishing minimum/maximum cardinality; i.e., mandatory/optional and one-to-one (1:1), one-to-many (1:M), or many-to-many (N:M).

Normalize the data model.

Remove any many-to-many (N:M) relationships by adding an intersection entity consisting of at least the primary keys of both original entities. (These two attributes combine into what is called a composite key.) Consider whether this intersection represents something in the real world that you do want to keep track of. If it does, name the intersection entity appropriately and add any necessary attributes.

Remove any derived attributes. Those are attributes whose value can be figured or otherwise established from the values of other attributes. (For example, in a sales transaction, total price for an item can always be calculated by multiplying the number bought by the price. You can calculate your GPA by using total earned hours and total quality points.)

Make sure all attributes are in the right entity. Sometimes, in the heat of designing the data model, an attribute that really belongs to a particular entity is thought of when considering a different attribute and gets added there. If an attribute does not directly describe the entity in which it is located, it needs to be moved to the attribute it does describe. (For example, you might have listed the vendor of a product in a sales transaction. Really, the vendor should be listed in the product inventory table, as it describes the product, not a sales transaction.)