MILESTONE 5 – DATA MODEL NORMALIZATION
:Synopsis
I |
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. |
|
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. |
0 comments