SADM 6/Ed - CASE STUDY 1 CRS - Milestone 5: Data Model Normalization Page: 5-1

SADM 6/Ed - CASE STUDY 1 CRS - Milestone 5: Data Model Normalization Page: 5-1

SADM 6/ed - CASE STUDY 1 CRS - Milestone 5: Data Model NormalizationPage: 5-1

MILESTONE 5 – DATA MODEL NORMALIZATION

Synopsis

I

n this milestone you will normalize the data model created in Milestone 3, along with additional data requirements, to be in third normal form.

Objectives

After completing this milestone, you should be able to:

Normalize a logical data model to remove impurities that can make a database unstable, inflexible, and non-scalable.

Prerequisites

  1. Data analysis - Chapter 8

Assignment

The goal of this project is to normalize our logical data model to remove impurities that can make a database unstable, inflexible, and non-scalable.

Activities

  1. To construct an Entity Relationship Diagram to be in 3rdNormal form, follow the normalization procedure outlined in Chapter 8 of the SADM 7th ed. textbook. Use the data dictionary provided at the end of this milestone to prepare the new data model.
  2. Specify all data types.
  3. Specify all primary and foreign keys.
  4. Specify which attributes are required (not nullable).
  5. (Optional) Add and normalize additional entities and attributes. Make assumptions where necessary.
  6. Please email the deliverables to the TA and the instructor

References:

Customer Response System Data Attribute Dictionary

Provided at the end of this milestone

Refer to a Copy of Your Fully Attributed Data Model Created for Milestone 4.

Deliverables:

Logical Data Model in 3rdNormal Form:Due: __/__/__Time:______

Milestone’s Point Value:______

Customer Response System Data Attribute Dictionary

Below is a Data Attribute Dictionary that contains all the attributes and definitions. Note: the attributes below are listed in alphabetical order, not necessarily by entity.

AccountCode / 10-character alphanumeric identifier for the accounting code used for each WorkType.
Address / 30-character alphanumeric field holding the client’s street or box address.
ChargeAmount / Currency amount of the charge on an other charge record.
ChargeNum / A system-generated large integer numeric value unique to each other charge record.
City / 20-character alphanumeric field holding the name of the client’s city.
ClientID / A system-generated large integer numeric value unique to each client.
ClientName / 50-character alphanumeric field holding the client’s company name.
ComponentID / 10-character alphanumeric code describing a type of component.
ContactFirstName / 20-character alphanumeric field consisting of the first name of the client contact person.
ContactLastName / 30-character alphanumeric field consisting of last name of the client contact person.
ChargeDate / Date field consisting of the date an other charge is accrued.
DateInstalled / Date field consisting of the date a component was installed into a piece of equipment.
DateRemoved / Date field consisting of the date a component was removed from a piece of equipment.
Description / A large alphanumeric field describing the work done on a work record.
Description / A large alphanumeric field describing an other charge.
Discount / A numeric discount factor applied to all of a client’s work records.
Email / 70-character alphanumeric field consisting of the client contact’s e-mail address.
EquipName / 15-character alphanumeric field holding the identifying name for the piece of equipment
EquipNum / A system-generated numeric value unique to each piece of equipment.
EquipType / 10-character alphanumeric field identifying the equipment as PC, Printer, Scanner, etc.
InServiceDate / Date field consisting of the date the piece of equipment was placed in service.
Phone / 10-digit field consisting of the client contact’s telephone number.
ProblemDescription / A large alphanumeric field describing the problem relating to a service request.
Quantity / A numeric field specifying the quantity of items installed as an EquipmentComponent. This is always a whole number. An inspection of past forms shows no quantity exceeding 50.
Rate / Currency rate to be charged for a type of work.
ReportDate / Date field holding the date a service request was reported.
ReportedBy / 50-character alphanumeric field holding the first and last name of the person reporting a service request.
ReqNum / A system-generated large integer numeric value unique to each service request.
ResolutionDate / Date field holding the date a service request was resolved.
SerialNum / 20-character alphanumeric field containing the serial number of an installed component.
State / 2-character abbreviation for the client’s state.
TechFirstName / 20-character alphanumeric field consisting of technician’s first name.
TechID / A 2-digit integer numeric value unique to each of the seven technicians.
TechLastName / 30-character alphanumeric field consisting of technician’s last name.
TimeBegin / The starting time of work done on a work record.
TimeEnd / The ending time of work done on a work record.
WorkDate / Date field consisting of the date of a work record.
WorkNum / A system-generated large integer numeric value unique to each work record.
WorkType / 10-character alphanumeric code for the type of work being done.
Zip / 10-character alphanumeric postal code of a client.

Prepared by Gary B. Randolph for

Systems Analysis & Design Methods 6ed

by J. L. Whitten, L. D. Bentley, & K. C. DittmanCopyright Irwin/McGraw-Hill 2004

Top View