Please answer assignment task 1. This lab assignment is intended to analyze the
ID: 3733047 • Letter: P
Question
Please answer assignment task 1.
This lab assignment is intended to analyze the issues surrounding the database tables provided, to identify functional dependencies in those tables and to normalize those tables into 3NF relations.
Tables in the Assignment Database
The database includes three tables as shown below:
The STUDENT table
The STUDENT table stores data about each student including name, major, academic department offering the major and academic advisor of the student. You may assume that a student has only one major and one advisor. An advisor may advise multiple students. Note that the class standing of a student is determined by the credit hours. For example, a student is considered a junior if the student has completed between 61 and 90 hours. You may assume the school has the business rule for the classification.
The INVOICE Table
The INVOICE table stores sales data of products and vendors supplying the products. You may assume that any given product is supplied by a single vendor but a vendor can supply many products.
The INVITATION table
The INVITATION table is used by the manager of a company dinner club to mail invitations to the club’s members, to plan the meals, to keep track of who attends the dinners, and so on.
You may assume the following business rules:
· Each dinner serves many members, and each member may attend many dinners.
· A member receives many invitations, and each invitation is mailed to many members.
· A dinner is based on a single entree, but an entree may be used as the basis for many dinners. For example, a dinner may be composed of a fish entree, rice, and corn. Or the dinner may be composed of a fish entree, a baked potato, and string beans.
Assignment Tasks
You are asked to analyze the design of the given tables in the assignment database and normalize those tables into 3NF tables. You should perform the following tasks for each of the given tables:
1. Determining Candidate Keys and Primary Keys
Determine all candidate keys in the table and decide which one is the primary key. (If there is only one candidate key, it is the primary key by default.)
2. Analyzing Data Redundancy and Modification Anomalies
· Discover all redundant data in the table and explain why the data is redundant (e.g., attribute names that need to be repeated).
· Identify one insertion anomaly, one update anomaly, and one deletion anomaly in the table. If you cannot identify any data anomalies for a specific operation, you need to provide an explanation. Otherwise, the analysis is considered incomplete.
3. Determining Functional Dependencies
· Identify all functional dependencies (FDs) in the table using the textual X -> Y notation.
· For each FD you identified, you must state your assumption/business rule in a sentence to support the FD. You may make your own assumptions if the business rules in the previous section are unclear or absent.
4. Normalization
· Determine if the table is in 2NF or 3NF and explain your conclusion. (The table is in 1NF by default.)
· If the table is already in 3NF, skip the rest of this task.
· If the table is not in 3NF, normalize it into 3NF relations based on the FDs you identified. (You will need to normalize into 2NF relations first and then into 3NF. If the last step is wrong, you can still receive partial credit for 2NF.)
· For each new table resulting from the normalization, determine the candidate keys and the primary key of the table.
Student Field Name Data Type Description TU NUM STU LNAME STU MAJOR DEPT CODE DEPT NAME DEPT PHONE COLLEGE NAME ADVISOR LNAME ADVISOR_OFFICE ADVISOR_BLDG ADVISOR_PHONE STU GPA STU HOURS STU CLASS Number Text Text Text Text Text Text Text Text Text Text Number Number Text Student number Student name Student major Department code Department name Department phone extension College name Advisor last name Advisor office location Advisor office building Advisor phone extension Student GPA Student credit hours accumulated Student class standing based on credit hoursExplanation / Answer
STUDENT
1. Keys
Candidate key ---- STU_NUM,DEPT_CODE,ADVISOR_PHONE
Primary Key ---- STU_NUM,DEPT_CODE,ADVISOR_PHONE
2. Redundancy and Anomalies
STU_MAJOR,DEPT_CODE,DEPT_NAME,DEPT_PHONE,ADVISOR_LNAME etc are redundant attributes in the table .
Anomalies:
Insertion --- To add the new department ,the students in that department and the advisors need also be inserted which is not practical situation.
Updation --- If a department name is changed , it needs to be updated at all redundant places otherwise inconsistency appears.
Deletion ---- If an Advisor quits his job , the details of the concerned students and departments also get deleted.
3. Functional dependencies
STU_NUM -> STU_LNAME,STU_MAJOR,STU_GPA,STU_HOURS,STU_CLASS
DEPT_CODE -> DEPT_NAME,DEPT_PHONE
ADVISOR_PHONE -> ADVISOR_LNAME,ADVISOR_OFFICE,ADVISOR_BLDG
STU_NUM,STU_HOURS -> STU_CLASS
4. Normalization
STUDENT(STU_NUM , STU_LNAME,STU_MAJOR,STU_GPA,STU_HOURS,STU_CLASS)
DEPARTMENT(DEPT_CODE , DEPT_NAME,DEPT_PHONE)
ADVISOR(ADVISOR_PHONE , ADVISOR_LNAME,ADVISOR_OFFICE,ADVISOR_BLDG)
CLASS(STU_NUM,STU_HOURS , STU_CLASS)
INVOICE
1. keys
Candidate key ---- INV_NUM,PROD_NUM,VEND_CODE
Primary Key ----- INV_NUM,PROD_NUM,VEND_CODE
2.Redundancy and Anomalies
Attributes like VEND_CODE,VEND_NAME,PROD_NUM,SALE_DATE etc are redundant.
Anomalies
Insertion ---- To insert a new product, the details of its invoice needs to be inserted also though its not sold yet.
Updation --- If the product price has been changed it need to be updated at all redundant places otherwise inconsistency appears.
Deletion --- If a product needs to be removed, its vendor details also get deleted.
3. Functional Dependencies
INV_NUM -> SALE_DATE,QUANT_SOLD
PROD_NUM -> PROD_LABEL,PROD_PRICE
VEND_CODE -> VEND_NAME
4. Normalization
INVOICE(INV_NUM , SALE_DATE,QUANT_SOLD)
PRODUCT(PROD_NUM , PROD_LABEL,PROD_PRICE)
VENDOR(VEND_CODE , VEND_NAME)
INVITATION
1. keys
Candidate key ---- MEMBER_NUM,INVITE_NUM,DINNER_CODE,ENTREE_CODE,DESERT_CODE
Primary key ---- MEMBER_NUM,INVITE_NUM,DINNER_CODE,ENTREE_CODE,DESERT_CODE
2. Redundancy and Anomalies
The attributes INVITE_NUM,DINNER_CODE,DESERT_CODE etc are redundant which can lead to anomalies.
Anomalies:
Insertion ---- To insert new dinner ,member and invitation details are need to be inserted which is not practical situation.
Updation --- If a desert name is changed , it needs to be updated at all redundant places, otherwise inconsistency appears.
Deletion --- If a desert is removed , the details of invitation also gets deleted .
3. Functional Dependencies
MEMBER_NUM -> MEMBER_NAME,MEMBER_ADDRESS,MEMBER_CITY,MEMBER_ZIPCODE
INVITE_NUM -> INVITE_DATE,ACCEPT_DATE
DINNER_CODE -> DINNER_DATE,DINNER_ATTENDED,DINNER_DESCRIPTION
ENTREE_CODE -> ENTREE_DESCRIPTION
DESERT_CODE -> DESERT_DESCRIPTION
4. Normalization
MEMBER(MEMBER_NUM , MEMBER_NAME,MEMBER_ADDRESS,MEMBER_CITY,MEMBER_ZIPCODE)
INVITATION(INVITE_NUM , INVITE_DATE,ACCEPT_DATE)
DINNER(DINNER_CODE , DINNER_DATE,DINNER_ATTENDED,DINNER_DESCRIPTION)
ENTREE(ENTREE_CODE, ENTREE_DESCRIPTION)
DESERT(DESERT_CODE, DESERT_DESCRIPTION)
underlined are primary keys
Do ask if any query. Please upvote if the result is useful.