QuestionQuestion

Transcribed TextTranscribed Text

NORMALIZATION Complete the two Normalization problems below (A & B). The final product must be your own work. In your solution, show the Initial Grouping, 1NF, Dependency Diagram, 2NF, and 3NF. When appropriate, be sure to identify all functional dependencies (both partial and transitive). Part A: TRIP The table structure shown in below contains many unsatisfactory components and characteristics. For example, there are several multivalued attributes, naming conventions are violated, and some attributes are not atomic. Sample TRIP Records Attribute Name Sample Value Sample Value Sample Value Sample Value TRIP 1003 1018 1019 1023 DATE 15-Jan2008 15-Jan2008 16-Jan2008 17-Jan2008 CITY STL MIA TYS ATL MILES 580 1,290 524 768 CUST_NUM 10232 10233 10234 10235 CUST_LNAME Brown Hanson Bryana Brown PASSANGERS 5 12 2 5 CARGO 235 lbs. 18,940 lbs. 348 lbs. 155 lbs. PILOT Smith Chen Henderson Smith COPILOT Henderson Smith FLT_ENGINEER O’Shaski LOAD_MASTER Davis AC_NUMBER 1234Q 3456Y 1234Q 2256W PLANE_CODE PA31-350 CV-580 PA31-350 PA31-350 PLANE_SEATS 10 38 10 10 PLANE_CHG_MILE $2.79 $23.36 $2.79 $2.79 1. Write the relational schema and list the dependencies for the table structure. Make sure that you label all dependencies. The CHAR_MILES entry is based on round-trip miles, including pickup points. (Hint: Look at the data values to determine the nature of the relationships. For example, note that employee Smith has flown two charter trips as pilot and one trip as copilot.) See example on the last page on how to write this. Relational schema: Primary Key: Dependencies: Partial Dependencies: Transitive Dependencies 2. Convert the relation to 3NF. (Hint: You might have to create a few new attributes. Also make sure that the new dependency diagrams contain attributes that meet proper design criteria; that is, make sure there are no multivalued attributes and that the naming conventions are met.) Write the corresponding relational schemas. Identify each primary key and each foreign key. 3. Draw the ER Diagram. Part B: WAREHOUSE The warehouse database stores inventory information. A warehouse has one manager who has an employee number (unique) and a name. A warehouse is identified by its warehouse name (unique) and its address. Every part in the warehouse has a part number (unique), inventory date, quantityon-hand, supplier name (one), and the delivery number, data, and quantity. One delivery could deliver several parts. The database is shown below: WAREHOUSE (manager-id, manager-name, warehouse-name, warehouse-address, partno, inventory-date, qty-on-hand, supplier-name, delivery-no, delivery-date, delivery-qty) 1. List the primary key(s) and list all transitive and/or partial dependencies in the relational schema diagram provided. See example on the last page on how to write this. Primary Key: Dependencies: Partial Dependencies: Transitive Dependencies 2. Convert the relation to 3NF. (Hint: You might have to create a few new attributes. Also make sure that the new dependency diagrams contain attributes that meet proper design criteria; that is, make sure there are no multivalued attributes and that the naming conventions are met.) Write the corresponding relational schemas. Identify each primary key and each foreign key. 3. Draw the ER Diagram.

Solution PreviewSolution Preview

This material may consist of step-by-step explanations on how to solve a problem or examples of proper writing, including the use of citations, references, bibliographies, and formatting. This material is made available for the sole purpose of studying and learning - misuse is strictly forbidden.

Database Normalization Problems
    $58.00 for this solution

    PayPal, G Pay, ApplePay, Amazon Pay, and all major credit cards accepted.

    Find A Tutor

    View available Database Development Tutors

    Get College Homework Help.

    Are you sure you don't want to upload any files?

    Fast tutor response requires as much info as possible.

    Decision:
    Upload a file
    Continue without uploading

    SUBMIT YOUR HOMEWORK
    We couldn't find that subject.
    Please select the best match from the list below.

    We'll send you an email right away. If it's not in your inbox, check your spam folder.

    • 1
    • 2
    • 3
    Live Chats