Case3-4DataModelNormalization

1、SADM 5/ed -CASE STUDY 3 -Milestone 4: Data Model NormalizationPage: 4-lIMILESTONE 4- DATA MODEL NORMALIZATIONSynop sisn this milest one you will no rmalize the data model created in Milest one 3 to be in third no rmal form and defi ne the data types of all attributes. ObjectivesAfter comp let ing th

2、is milest one, you should be able to:= Normalize a logical data model to remove imp urities that can make a database un stable, in flexible, and non-scalable.P rerequisites1. Data analysis -Chapter 72. Milestone 3 SolutionAssig nmentThe goal of this pr oject is to no rmalize our logical data model t

3、o remove imp urities that can make a database un stable, in flexible, and non-scalable.Activities1. To con struct an En tity Relati on shi p Diagram to be in third no rmal form, follow the no rmalizati on p rocedure outl ined in Chap ter 7 of the SADM 5h ed. textbook. Use the ERD soluti on of Milest

4、 one 3, as well as the data dictio nary pro vided at the end of this milest one to prepare the new data model. Make assu mp ti ons where n ecessary.Deliverable format and software to be used are according to your instructr' s sp ecificati ons. Deliverables should be n eatly p ackaged in a bin de

5、r, sep arated with a tab divider labeled“ Milest one 4.”References:Milestone 3 SolutionProvided by your in structorP innacle P ublishing Data Attribute DictionaryProvided at the end of this milest oneDue: Time:Deliverables:Logical Data Model in 3rd Normal Form:Milestones Po int Value:P innacle P ubl

6、ishing Data Attribute DictionaryBelow is a Data Attribute Dictio nary that contains all the attributes and defi niti ons. Most of the attributes in the dicti onary were in eluded in the fully-attributed data model from Milest one 3. However, Cindy Natale discovered other attributes duri ng her fact-

7、fi nding efforts.Author IDA uni que, automatically-i ncreme nti ng n umeric field that the system will assig n to each author.Author First NameField consisting of an author' s first name.Author Last NameField consisting of an author' s last name.Author Address 1Field consisting of an author&

8、#39; s slreet addresAuthor Address 2Field consisting of the second line of an author' s street acAuthor CityField con sist ing of the author' s city of reside nee.Author StateField consisting of the abbreviation for the author' s stateAuthor Zip CodeField con sist ing of the zip or p ost

9、al code for the authorAuthor E-mail AddressField con sist ing of author-mail addressAuthor Social Security Number9-digit n umeric field uni que to each author used for tax reporting purp oses and collected whe n book beg ins to be sold.Best Time to CallField con sisti ng of best time of day/week to

10、reach author by tele phone.Author Phone Num10-digit field con sisti ng of an author' s tele phone n umber.Author Phone Descri pti onField describing the author' s bho nieen iWork, Home, Cell, Pager,etc.)Emplo yee ID3-digit alphan umeric field con sisti ng of a sales person' s fand (if n

11、eeded for uni que ness) a 1-digit n umber.Emplo yee NameField consisting of a salesperson' s first and last name.Base Salary6-digit field, in cludi ng two decimal poin ts, con sist ing of the weekly base salary for the sales personP ublicati on Commissio n6-digit field, in cludi ng two decimal p

12、oin ts, con sist ing of the commissi on amou nt p aid to the sales person for each p ublicati on sale.Pro moti on Commissi on6-digit field, in cludi ng two decimal poin ts, con sist ing of the commissi on amou nt p aid to the sales person for each pro moti on sale.Call IDA uni que, automatically-i n

13、creme nti ng n umeric field that the system will assig n to each call.Call Date8-digit numeric field in MM/DD/YYYY format con sisti ng of the date of the call.Call Time4-digit n umeric field in HH:MM format (us ing 24-hour military time) con sist ing of the time of day of the call.Call NotesField co

14、n sist ing of the sales person' s no tes on the callBook ID5-digit alphan umeric field uni quely ide ntify ing each book. The ID con sists of two letters ide ntify ing a book type code followed by a 3-digit n umber.Book TitleField consisting of the book' s titleBook Sub TitleField consisting

15、 of the book-title s subNum P agesField con sist ing of the n umber of p ages in the book man uscri pt.ISBN20-digit alphanumeric field consisting of the book' s ISBN.be formatted to begi n with the characters ISBN followed by a sp ace and the n umber.Task ID3-character alpha numeric abbreviati o

16、n for a task n ame.TaskField con sist ing of the n ame of a task.dress.rst and last in itialsThe data shouldof reside nee.Web NameField con sisti ng of how the task should be described whe n the in formati on is p osted to the author self-service web site.Days to Compl eteField con sisti ng of the n

17、o rmal n umber of days it should take to comp lete each task. This was added by Joh n Bridgham for po ssible later use in track ing p ast-due tasks. Joh n states that every task should be compl eted in less tha n 45 days.Sales Quarter6-character alphan umeric field in Q/YYYY format con sist ing of t

18、he quarter and year in which a sales n umber is being rep ortedSourceField con sisti ng of the ven dor rep ort ing the sale.Net Sales8-digit field, in cludi ng two decimal poin ts, con sist ing of the amou nt of sales from the source for the quarter.P ublicati on Sale Date8-digit numeric field in MM/DD/YYYY format con sisti ng of the date the sales person sold the author on p ublicati on services.Pro moti on Sale Date8-digit numeric field in MM/DD/YYYY format con sisti ng of the date the sales per