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
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 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 18.104.22.168 – Relationship for Rich milk
ER model for RICHMILK
Figure 22.214.171.124 – ER Diagram for Rich milk
Propose a data dictionary for the above ER model.
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 126.96.36.199 – Data dictionary for Rich milk
Generate a relational model based on above ER model
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 (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)
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
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.