Results 1 to 1 of 1
  1. #1
    Join Date
    Mar 2014
    Posts
    3

    Exclamation Assignment issue - I have problem after the normalization tables

    this scenario is milk company and 1000 workers and working we need optimize the tables and daily records

    these are the entities
    1. Employee
    2. Factory
    3. Product
    4. Dealer
    5. Chilling Center
    6. Farmer
    7. Dependent
    8. Farm
    9. Office

    Attributes
    1. Employee
    EMP_ID, EMP_NAME (EMP_FNAME, EMP_LNAME), EMP_AGE, EMP_QUALIFICATION, EMP_DOB, EMP_NIC

    2. Factory
    FTR_ID, FTR_NAME, FTR_ADDRESS (FTR_STREET, FTR_CITY, FTR_ZIP), FTR_REGNO

    3. Office
    OFFICE_ID, OFFICE_REGNO, OFFICE_ADDRESS (OFFICE_CITY, OFFICE_STREET, OFFICE_ZIP), OFFICE_TELPHONE

    4. Product
    PRD_ID, PRD_NAME, PRD_PRICE, PRD_TYPE, PRD_MFD, PRD_EXP

    5. Dealer
    DEL_ID, DEL_NAME (DEL_FNAME, DEL_LNAME), DEL_TEL, DEL_ADDRESS (DEL_STREET, DEL_CITY, DEL_ZIP)

    6. Chilling centre
    CHC_ID, CHC_NAME, CHC_ADDRESS (CHC_STREET, CHC_CITY), CHC_TEL

    7. Farmer
    FAR_ID, FAR_NAME (FAR_FNAME, FAR_LNAME), FAR_DOB, FAR_ADDRESS (FAR_STREET, FAR_CITY, FAR_ZIP)

    8. Dependent
    DEP_ID, DEP_NAME (DEP_FNAME, DEP_LNAME) , DEP_TYPE

    9. Farm
    FARM_ID, FARM_NAME FARM_ADDRESS (FARM_STREET, FARM_CITY, FARM_ZIP), FARM_REGNO

    Note
    I inserted attribute Fname for First name, LName for Last name, DOB for Date of Birth, Regno for Registration number, TEL for Telephone number, Dep_type for Dependant relationship type
    I mentioned here Far for Farmer, CHC for Chilling Center, DEL for Dealer, PRD for product, FTR for factory, EMP for Employee, DE for Dependent


    Assumptions

    1. One farmer has many farms but one farm depends with one and only one farmer.

    2. Farmer has many dependent but one dependent depend with one and only one farmer.

    3. A Chilling center has many farmers but one farmer supply milk to one and only one Chilling center.

    4. A factory has many Chilling center but one Chilling center supply milk to one and only one factory.

    5. A factory produces many products but one product depends on one and only one factory.

    6. One product sell by many dealers as well as one dealer can sell many products.

    7. Office has many employees. But one employee work in one and only one office.

    8. Factory has office but office deal with many factories.

    Relationship
    Relationship Type Cardinality Ratio
    1. Farmer has Many Farm Binary 1:M
    2. Farmer has Many Dependent Binary 1:M
    3. Chilling Center get milk from Many Farmers Binary 1:M
    4. Factory get milk from Many Chilling center Binary 1:M
    5. Factory has office Unary 1:1
    6. Factory produce Many products Binary 1:M
    7. Dealer sells Many products Ternary M:M
    8. Office has Many Employees Binary 1:M
    Figure 2.2.1.1 Relationship for Rich milk

    ER model for RICHMILK

    Figure 2.2.1.2 ER Diagram for Rich milk
    Task 2.2.2
    Propose a data dictionary for the above ER model.
    Entity Types
    Employee Strong Entity
    Farm Strong Entity
    Office Strong Entity
    Factory Strong Entity
    Product Strong Entity
    Dealer Strong Entity
    Dependent Weak Entity
    Farmer Strong Entity
    CHClling Center Strong Entity

    Figure 2.2.2.1 Data dictionary for Rich milk

    Task 2.2.3
    Generate a relational model based on above ER model
    Employee

    Emp_ID Emp_Fname Emp_LName Emp_DOB Emp_Qualification Emp_NIC Office_ID

    Office

    Office_ID Office_Regno Office_Street Office_City Office_Zip FTR_ID

    Factory
    FTR_ID FTR_Regno FTR_Name FTR_Street FTR_City FTR_Zip

    Product
    PRD_ID
    PRD_name PRD_type PRD_MFD PRD_EXP PRD_Price FTR_ID

    Chilling Center
    CHC_ID CHC_Name CHC_Street CHC_City CHC_Zip CHC_Tel FTR_ID

    Farmer
    Far_ID Far_FName Far_LName Far_Street Far_City Far_Zip Far_DOB CHC_ID

    Dependent
    DE_ID DE_Fname DE_Lname DE_Type Far_ID

    Farm
    Farm_ID Farm_Name Farm_Street Farm_City Farm_Zip Farm_asset Far_ID

    Product Dealer
    PRD_ID
    DEL_ID

    Dealer
    DEL_ID DEL_FName DEL_LName DEL_Street DEL_City DEL_Zip DEL_TEL

    ER Diagram Attached

    Task 2.3
    Apply normalization to the above relations by clearly showing the steps up to 3NF.


    1st Normal Form
    Columns contains only atomic values
    Removing the repeat groups of data

    2nd Normal Form
    Any partial dependency have to be removed
    Any functional dependency have to be removed

    3rd Normal Form
     Any Transitive dependencies have to be removed.

    ONF Tables
    All the attributes:
    RICH MILK {EMPLOYEE (EMP_ID, EMP_FNAME, EMP_LNAME, EMP_DOB, EMP_QUALIFICATION, EMP_NIC, OFFICE_ID), FACTORY(FTR_ID, FTR_REGNO, FTR_NAME, FTR_STREET, FTR_CITY, FTR_ZIP, OFFICE_ID, OFFICE_REGNO, OFFICE_STREET, OFFICE_CITY, OFFICE_ZIP), PRODUCT (PRD_ID, PRD_NAME, PRD_TYPE, PRD_MFD, PRD_EXP, PRD_PRICE, FTR_ID) CHILLING CENTER (CHC_ID, CHC_NAME, CHC_STREET, CHC_CITY, CHC_ZIP, CHC_TEL, FTR_ID), FARMER (FAR_ID, FAR_FNAME, FAR_LNAME, FAR_STREET, FAR_CITY, FAR_ZIP, FAR_DOB, CHC_ID) DEPENDANT (DE_ID, DE_TYPE, DE_FNAME, DE_LNAME, FAR_ID), FARM (FARM_ID, FARM_NAME, FARM_STREET, FARM_CITY, FARM_ZIP, FARM_ASSET, FAR_ID), PRODUCT DEALER (PRD_ID, DEL_ID), DEALER (DEL_ID, DEL_FNAME, DEL_LNAME, DEL_STREET, DEL_CITY, DEL_ZIP, DEL_TEL)}

    To change 1NF these tables are affected to change because it has multi values in the table

    Employee (Emp_ID, Emp_Fname, Emp_LName, Emp_DOB, Emp_Qualification, EMP_NIC, OFFICE_ID)

    Farm (FARM_ID, FARM_NAME, FARM_STREET, FARM_CITY, FARM_ZIP, FARM_ASSET, FAR_ID)

    First Normal Form
    The table to be in first normal form, the column value has to be atomic (Only one value)
    Multi value attributes are divided into separate table, repeating groups also need to be arranged
    Employee Table
    EMPLOYEE (EMP_ID, EMP_FNAME, EMP_LNAME, EMP_DOB, EMP_NIC, OFFICE_ID)
    EMPLOYEE_QUALIFICATION (EMP_ID, EMP_QUALIFICATION)
    Farm Table
    FARM (FARM_ID, FARM_NAME, FARM_STREET, FARM_CITY, FARM_ZIP, FAR_ID)
    FARM ASSET (FARM_ID, FARM_ASSET)

    These Tables are already in 1NF
    DEALER (DEL_ID, DEL_FNAME, DEL_LNAME, DEL_STREET, DEL_CITY, DEL_ZIP, DEL_TEL )
    FACTORY (FTR_ID, FTR_REGNO, FTR_NAME, FTR_STREET, FTR_CITY, FTR_ZIP, OFFICE_ID, OFFICE_REGNO, OFFICE_STREET, OFFICE_CITY, OFFICE_ZIP)
    FARMER (FAR_ID, FAR_FNAME, FAR_LNAME, FAR_STREET, FAR_CITY, FAR_ZIP, FAR_DOB, CHC_ID)
    PRODUCT DEALER (PRD_ID, DEL_ID)
    PRODUCT (PRD_ID, PRD_NAME, PRD_TYPE, PRD_MFD, PRD_EXP, PRD_PRICE, FTR_ID)
    CHILLING CENTER (CHC_ID, CHC_NAME, CHC_STREET, CHC_CITY, CHC_ZIP, CHC_TEL, FTR_ID)
    DEPENDANT (DE_ID, DE_TYPE, DE_FNAME, DE_LNAME, FAR_ID)
    To change in to 2NF to remove partial dependencies affected table is

    FACTORY (FTR_ID, FTR_REGNO, FTR_NAME, FTR_STREET, FTR_CITY, FTR_ZIP, OFFICE_ID, OFFICE_REGNO, OFFICE_STREET, OFFICE_CITY, OFFICE_ZIP)

    Second Normal Form
    The tables to be in second normal form extract partial key dependency affected keys.

    Office table depend with factory id and if I change the factory id than I have enter details in office details that factory and office are one to one relationship. Office depend on factory table.

    Factory Table
    FACTORY (FTR_ID, FTR_REGNO, FTR_NAME, FTR_STREET, FTR_CITY, FTR_ZIP)
    Office Table
    OFFICE (OFFICE_ID, OFFICE_REGNO, OFFICE_STREET, OFFICE_CITY, OFFICE_ZIP, FTR_ID)

    These Tables are already in 2NF
    EMPLOYEE (EMP_ID, EMP_FNAME, EMP_LNAME, EMP_DOB, EMP_NIC, OFFICE_ID)
    EMPLOYEE_QUALIFICATION (EMP_ID, EMP_QUALIFICATION)
    FARM (FARM_ID, FARM_NAME, FARM_STREET, FARM_CITY, FARM_ZIP, FAR_ID)
    FARM ASSET (FARM_ID, FARM_ASSET)
    DEALER (DEL_ID, DEL_FNAME, DEL_LNAME, DEL_STREET, DEL_CITY, DEL_ZIP, DEL_TEL)
    FARMER (FAR_ID, FAR_FNAME, FAR_LNAME, FAR_STREET, FAR_CITY, FAR_ZIP, FAR_DOB, CHC_ID)
    PRODUCT DEALER (PRD_ID, DEL_ID)
    PRODUCT (PRD_ID, PRD_NAME, , PRD_MFD, PRD_EXP, PRD_TYPE PRD_PRICE, FTR_ID)
    CHILLING CENTER (CHC_ID, CHC_NAME, CHC_STREET, CHC_CITY, CHC_ZIP, CHC_TEL, FTR_ID)
    DEPENDANT (DE_ID, DE_TYPE, DE_FNAME, DE_LNAME, FAR_ID)

    All the tables are I consider to make foreign keys to the table so all the tables are affected to Transitive Dependency.

    Third Normal Form
    The tables to be in third normal form extract transitive key dependency affected keys.I changed the table minimize data occurrences which if the name changes the other details also need to change.

    I created separate table for details the primary key can be ID, NAME, RegNo.

    FACTORY (FTR_ID, FTR_NAME)
    FACTORY DETAILS (FTR_NAME, FTR_STREET, FTR_CITY, FTR_ZIP, FTR_REGNO)
    OFFICE (OFFICE_ID, OFFICE_REGNO, FTR_ID)
    OFFICE DETAILS (OFFICE_REGNO, OFFICE_STREET, OFFICE_CITY, OFFICE_ZIP)
    EMPLOYEE (EMP_ID, EMP_FNAME , OFFICE_ID)
    EMPLOYEE DETAILS (EMP_FNAME, EMP_LNAME, EMP_DOB, EMP_AGE , EMP_NIC)
    EMPLOYEE_QUALIFICATION (EMP_ID, EMP_QUALIFICATION)
    FARM (FARM_ID, FARM_NAME, FAR_ID)
    FARM DETAILS (FARM_NAME, FARM_STREET, FARM_CITY, FARM_ZIP)
    FARM ASSET (FARM_ID, FARM_ASSET)
    DEALER (DEL_ID, DEL_FNAME)
    DEALER DETAILS (DEL_FNAME, DEL_LNAME, DEL_STREET, DEL_CITY, DEL_ZIP, DEL_TEL)
    FARMER (FAR_ID, FAR_FNAME, CHC_ID)
    FARMER DETAILS (FAR_FNAME, FAR_LNAME, FAR_STREET, FAR_CITY, FAR_ZIP, FAR_DOB)
    DEPENDANT (DE_ID, DE_FNAME, FAR_ID)
    DEPENDENT DETAILS (DE_FNAME, DE_LNAME, DE_TYPE)
    PRODUCT DEALER (PRD_ID, DEL_ID)
    PRODUCT (PRD_ID, PRD_NAME, FTR_ID)
    PRODUCT DETAILS (PRD_NAME, PRD_MFD, PRD_EXP, PRD_TYPE, PRD_PRICE)
    CHILLING CENTER (CHC_ID, CHC_NAME, FTR_ID)
    CHILLING CENTER DETAILS (CHC_NAME, CHC_STREET, CHC_CITY, CHC_ZIP, CHC_TEL)

    database attached

    I don't know how to sort it the tables, to create report, form.
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •