Question

1 Approved Answer

Posted on Aug 28, 2024

Font Paragraph Voice Styles Editing III.S.IIIIIIIIIIIII The public Safety Office at Millennium College maintains a list of parking tickets issued to vehicles parked illegally on

image text in transcribed

Font Paragraph Voice Styles Editing III.S.IIIIIIIIIIIII The public Safety Office at Millennium College maintains a list of parking tickets issued to vehicles parked illegally on campus. The table below shows a portion of this list for the fall semester. (Attribute names are abbreviated to conserve space.) PARKING TICKETS Vehicle Make Vehicle PHONE NO ST UKC UIC NO Vehicle Model Tiskate DATE VIOLATION FINE STU ID 33249 972-11-2004 FL:53 BRY123 Lexus ES Silver 3564 10/17/16 $25 1 38249 82458 82453 STU NAME Brown, Thomas Brown Thomas Green, Sally Green, Sally Smith.ohn Smith, John 972-191-7804 469-391-1689 TX199 469-3911689 TX199 214123265 ALSSZ 214-23-265 ALSSZ BRY123 TRE541 TRE141 NYT162 DRRISS Lexus Ford Ford Silver White White Blue White 16017 14987 16393 F250 7850 Pathfinder Camry 11/03/16 soos '16 1/18/16 olsuz 6/62 3 1 $900 $95 595 $25 Nissan 92453 Toyota 18890 2 You have been given the following business rules: A student may have 1 or more vehicles. A License number is associated with 1 vehicle. A student may have 1 or more tickets. A violation code has a type and a set fine. Each ticket has a date and a violation code, student information and vehicle information. 1. Answer the following questions: a. What is the PK for this relation in its current state? (2 pts) b. What Normal Form is the current state of this relation? Explain your reasoning. 3 pts) Font Styles Editing Void Paragraph II.2. 2. Perform the following Normalization steps: a. Draw a dependency diagram showing all functional dependencies in the relation, based on the sample data shown. List the partial and transitive dependencies. (6 pts) Example: CLAXT NUCLENTUNA DATE CONTRACT CLASS CLASS CLASS CLASSICO COCONSUME Transitive dependencies Transitive dependencies b. List any partial and/or transitive dependencies. (4 pts) Partial Dependencies: Transitive Dependencies: C. Convert the original relation to 3NF. (Hint: You might have to create a few new attributes to make sure that the relations contain attributes that meet proper design criteria; that is, make sure there are no composite or multivalued attributes and that the naming conventions are met.) Write the corresponding relational schemas for each new relation. Identify each primary key and each foreign key. (15 pts) Example: Relational Schema: EMP(EMP_NUM, EMP_NAME, EQU_CODE) d. Using a data modeling tool such as Lucidchart, create a Crow's Foot ERD with the new set of relations and attach it as an image here. No hand-drawn images will be accepted. (10 pts) Font Paragraph Voice Styles Editing III.S.IIIIIIIIIIIII The public Safety Office at Millennium College maintains a list of parking tickets issued to vehicles parked illegally on campus. The table below shows a portion of this list for the fall semester. (Attribute names are abbreviated to conserve space.) PARKING TICKETS Vehicle Make Vehicle PHONE NO ST UKC UIC NO Vehicle Model Tiskate DATE VIOLATION FINE STU ID 33249 972-11-2004 FL:53 BRY123 Lexus ES Silver 3564 10/17/16 $25 1 38249 82458 82453 STU NAME Brown, Thomas Brown Thomas Green, Sally Green, Sally Smith.ohn Smith, John 972-191-7804 469-391-1689 TX199 469-3911689 TX199 214123265 ALSSZ 214-23-265 ALSSZ BRY123 TRE541 TRE141 NYT162 DRRISS Lexus Ford Ford Silver White White Blue White 16017 14987 16393 F250 7850 Pathfinder Camry 11/03/16 soos '16 1/18/16 olsuz 6/62 3 1 $900 $95 595 $25 Nissan 92453 Toyota 18890 2 You have been given the following business rules: A student may have 1 or more vehicles. A License number is associated with 1 vehicle. A student may have 1 or more tickets. A violation code has a type and a set fine. Each ticket has a date and a violation code, student information and vehicle information. 1. Answer the following questions: a. What is the PK for this relation in its current state? (2 pts) b. What Normal Form is the current state of this relation? Explain your reasoning. 3 pts) Font Styles Editing Void Paragraph II.2. 2. Perform the following Normalization steps: a. Draw a dependency diagram showing all functional dependencies in the relation, based on the sample data shown. List the partial and transitive dependencies. (6 pts) Example: CLAXT NUCLENTUNA DATE CONTRACT CLASS CLASS CLASS CLASSICO COCONSUME Transitive dependencies Transitive dependencies b. List any partial and/or transitive dependencies. (4 pts) Partial Dependencies: Transitive Dependencies: C. Convert the original relation to 3NF. (Hint: You might have to create a few new attributes to make sure that the relations contain attributes that meet proper design criteria; that is, make sure there are no composite or multivalued attributes and that the naming conventions are met.) Write the corresponding relational schemas for each new relation. Identify each primary key and each foreign key. (15 pts) Example: Relational Schema: EMP(EMP_NUM, EMP_NAME, EQU_CODE) d. Using a data modeling tool such as Lucidchart, create a Crow's Foot ERD with the new set of relations and attach it as an image here. No hand-drawn images will be accepted. (10 pts)