MILESTONE 5 – DATA MODEL NORMALIZATION
:Synopsis
n this milestone you will normalize the data model created in Milestone 4, along with additional data requirements, to be in third normal form.
: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.
:Deliverables
a. MS Word document with your reply to the following activities:
1. Milestone 4 (#1). (Please see Milestone 4.)
2. Milestone 4 (#2). (Please see Milestone 4.)
3. Milestone 4 (#3). (Please see Milestone 4.)
4. Milestone 4 (#4). (Please see Milestone 4.)
5. Milestone 5 (#1). To construct an Entity Relationship Diagram to be in 3rd Normal form, follow the normalization procedure outlined in Chapter 8 of the SADM 7th ed. textbook. Use feedback from your the ERD solution of Milestone 4 as well as the data dictionary provided at the end of this milestone to prepare the new data model. Copy / paste the resulting diagram into the MS Word document for grading.
6. Milestone 5 (#2). Specify all data types (data types: integer (numeric), floating point (numeric), string, Boolean, and date-time.)
7. Milestone 5 (#3). Specify all primary and foreign keys.
8. Milestone 5 (#4). Specify which attributes are required (not nullable).
b. Aim to include about 1000 words overall in the document, but not more than 1300. (MS Word provides an easy-to-use way to count words: Tools – Word Count.)
c. This part of the project has a weight of 10% of your overall grade.
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 by entity.
Address
|
A 30-character alphanumeric field holding the client’s street or box address.
|
Barcode
|
A 20-character alphanumeric barcode ID that is either pre-printed on each component package or added via a label at Coastline Consulting.
|
City
|
A 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
|
A 50-character alphanumeric field holding the client’s company name.
|
ComponentType
|
A 10-character alphanumeric classification of the type of component.
|
ConfigurationID
|
A system-generated large integer numeric value unique to each software configuration record.
|
ContactFirstName
|
A 20-character alphanumeric field consisting of the first name of the client contact person.
|
ContactLastName
|
A 30-character alphanumeric field consisting of last name of the client contact person.
|
DateInstalled
|
Date field consisting of the date a component was installed into a piece of equipment.
|
DatePurch
|
A date field representing the date a component was purchased.
|
DateRemoved
|
Date field consisting of the date a component was removed from a piece of equipment.
|
Description
|
A 30-character description of the component.
|
Email
|
A 70-character alphanumeric field consisting of the client contact’s e-mail address.
|
Equipment
|
A 20-character alphanumeric description of the piece of equipment, if any, for a software configuration record.
|
EquipName
|
A 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
|
A 10-character alphanumeric field identifying the equipment as PC, Printer, Scanner, etc.
|
FinishTime
|
A time field holding the time a technician finished work on a request.
|
InfoName
|
A 20-character alphanumeric description of the information stored in a software configuration record.
|
InfoValues
|
A 50-character alphanumeric information value stored in a software configuration record.
|
InServiceDate
|
A date field consisting of the date the piece of equipment was placed in service.
|
ModelNum
|
A 10-character alphanumeric identifier of the model number of the component.
|
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.
|
ReportDate
|
A date field holding the date a service request was reported.
|
ReportedBy
|
A 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
|
A date field holding the date a service request was resolved.
|
StartTime
|
A time field holding the time a technician began work on a request.
|
State
|
A 2-character abbreviation for the client’s state.
|
TechInitials
|
A 3-character alphanumeric field consisting of the initials of the technician performing work.
|
Vendor
|
A 30-character field holding the name of the vendor from whom a component was purchased.
|
WorkDate
|
A date field consisting of the date a technician did work on a request.
|
WorkDescription
|
A large alphanumeric field describing the work done on a work record.
|
WorkNum
|
A system-generated large integer numeric value unique to each work record.
|
Zip
|
A 10-character alphanumeric postal code of a client.
|