Please help to proved the fully detailly answer(by typed) for all the 5 question
ID: 3879114 • Letter: P
Question
Please help to proved the fully detailly answer(by typed) for all the 5 question blew, thank you so much
Question:
1.
This table is un-normalized form. Convert this table from 1NF to 3NF.
Show your work.
2. What are the 1st, 2nd and 3rd NF?
Pet_id
Pet_Name
PET_Type
Pet_Age
Owner
Visit Date
Procedure
1
Brown
DOG
12
Sam
JAN 13/2002
01 - RABIES VACCINATION
MAR 27/2002
10 - EXAMINE and TREAT WOUND
APR 02/2002
05 - HEART WORM TEST
2
Tucker
DOG
2
John
JAN 21/2002
08 - TETANUS VACCINATION
MAR 10/2002
05 - HEART WORM TEST
3
Maggie
CAT
4
Sam
JAN 23/2001
01 - RABIES VACCINATION
JAN 13/2002
01 - RABIES VACCINATION
4
Tweety
BIRD
2
John
APR 30/2002
20 - ANNUAL CHECK UP
APR 30/2002
12 - EYE WASH
3. The following schema is used to register information on repayment of loans.
Repayment(borrower_id, name, address, loanamount, requestdate, repayment_date, repayment_amount)
4. Consider the below table structure that a company uses to keep track of office equipment’s.
ITEM_ID
ITEM_LABEL
ROOM_NUM
BLD_NUM
BLD_NAME
BLD_MGR
125457
Dell Desktop
224
CDM
College of Digital Media
I.M. Jackson
145287
Dell Laptop
224
CDM
College of Digital Media
I.M. Jackson
175486
Brother Printer
145
LDM
Collge of Law
R.F John
Given the above information
Write the relational schema and identify partial and transitive dependencies if any.
Normalize the schema. Show all your work.
Make the normalization to 3rd Normal Form. Show your work.
5. Normalize the below data
EMP_NUM
PROF_RANK
EMP_NAME
DEPT_ID
DEPT_NAME
PROF_OFFICE
ADVISEE
COMMITTEE_CODE
JOURNAL_CODE
147587
Professor
John
CDM
College of Digital Media
CDM 642
4578, 1452, 5987, 2360
PROMO, MEDIA, DEV, PROG
ITIM, ITSC, QTS
412547
Adjunct
Mark
CIS
College of Information SyS
CDM 641
1457,4789,3652,2541,8596,7853,9654
DEV
ITIM
234654
Asst. Prof
Mary
CDM
College of Digital Media
CDM 785
2578,7896,5896,5874,5214,5236,3258,3257
TEST,MEDIA
ITSC, QRT
789654
Asst. Prof
Joe
LAW
College of Law
CDM 478
5678.9087,2014,5630,2140
Promo, PROG
QRT
Given the below information –
Identify the multivalued, partial and transitive dependencies, if there are any.
Eliminate the dependencies and normalize the data.
Show your work.
Pet_id
Pet_Name
PET_Type
Pet_Age
Owner
Visit Date
Procedure
1
Brown
DOG
12
Sam
JAN 13/2002
01 - RABIES VACCINATION
MAR 27/2002
10 - EXAMINE and TREAT WOUND
APR 02/2002
05 - HEART WORM TEST
2
Tucker
DOG
2
John
JAN 21/2002
08 - TETANUS VACCINATION
MAR 10/2002
05 - HEART WORM TEST
3
Maggie
CAT
4
Sam
JAN 23/2001
01 - RABIES VACCINATION
JAN 13/2002
01 - RABIES VACCINATION
4
Tweety
BIRD
2
John
APR 30/2002
20 - ANNUAL CHECK UP
APR 30/2002
12 - EYE WASH
our y Rate protect T Project-T project Code rojecTEmployee Employee DepartmenT No. epartment Name Title Name Manager Buudget No. COT ensions NT 24500 S1O0 A Smith L004 System Phillips C010 Pensions C010 PC045 Salaries H Martin17400 ST0010 L Jones ensions 18.5 System Philips ensions System Phillps 24500 S21010 P Lewis 21.00 BJones System System System System System System System 18.00 PC045 Salaries atabase LO08 Salary Richards Gilbert 028 Database ewis PC064HR Lewis 12250 S10034 HR- 16.50- B JamesExplanation / Answer
Q1. For 3NF normalized form the rule for A->B where A is the superkey of table and B is the rest of columns of relation R must hold. Hence, in this table we can see that for the same value of ProjectCode, the value of ProjectTitle, Project Manger and Project Budget is same hence these all will make a table say R1. Also we can see that for same value of EmployeeNo, the value of EmployeeName, DepartmentNo, DepartmentName is same hence these all can make another table say R2. Now the hourly rate varies based on ProjectCode and EmployeeNo. hence we can make another table for mapping hourly rate based on these say R3. So, the relation in 3NF normalised form is :
R1 (ProjectCode, ProjectName, ProjectManager, ProjectBudget)
R2 (EmployeeNo, EmployeeCode, DepartmentNo, DepartmentName)
R3 (ProjetcCode, EmployeeNo, HourlyRate)
Q2. For converting given table into 1 NF we have to repeat value in every row and column like i have done below (although data is not filled completely you can do that) :
Pet_Id
Pet_Name
Pet_Type
Pet_Age
Owner
Visit_date
Procedure
1
Brown
Dog
12
Sam
JAN 13/2002
Rabbies
1
Brown
Dog
12
Sam
Mar 27/2002
Examine
1
Brown
Dog
12
Sam
Apr 02/2002
Heart worm
2
Tucker
Dog
2
John
JAN 21/2002
Tetanus
2
Tucker
Dog
2
John
March
Worm test
3
Maggie
Cat
4
Sam
jan
Rabbits
3
Maggie
Cat
4
Sam
jan
Rabbies
4
Tweety
Bird
2
John
apr
Annual check
4
Tweety
Bird
2
John
apr
Eye wash
For 2NF we have to remove partial dependency that is the dependency in which some of the table attributes can be uniquely identified from some of the primary key attributes or candidate key attributes. Now for the above table primary keys are (PetId, VisitDate) or (PetId, Procedure). while this PetId indivisually can identify attribute like PetName, PetAge, Owner Name uniquely, hence for 2 NF we need to divide relation as below:
R1 (PetId, PetName, PetType, PetAge, OwnerName)
R2 (PetId, VisitDate, Procedure)
Now as i gave definition of 3NF for Q1, for the above normalized R1 and R2 there is a primary key in both of them which acts as super key, hence above relation is in 3NF form also.
Q3. Here only table structure is provided but what to do with this is not provided. Please provide other details.
Q4. Here in this schema Item_Id is the primary key while room_num can uniquely can identify other details(like bld_num, bld_name) hence this is transitive dependency. So below highlighted is the transitive dependency :
Item_Id -> Item_Label, Room_num, bld_num, bld_name, bld_manager
Room_num -> bld_num, bld_name, bld_manager
while there is no partial dependency in this relation. So 3NF normalized form is :
R1 (Item_id, Item_Label)
R2 (Room_Num, Bld_num, Bld_name, Bld_manager)
Q5. Emp_num is the primary key of the above relation. There is no partial dependency wile there are transitive and multivalued dependency. Transitive dependency is : Dept_id -> Dept_name. while multivalued dependency is : advisee ->> committee_code. Hence for 3NF form we can divide as below:
R1 (Emp_Num, Prof_Rank, Emp_name, Prof_Office)
R2 (Dept_id, Dept_Name)
R3 (Emp_num, Dept_Id, Advisee, Committee_code).
Pet_Id
Pet_Name
Pet_Type
Pet_Age
Owner
Visit_date
Procedure
1
Brown
Dog
12
Sam
JAN 13/2002
Rabbies
1
Brown
Dog
12
Sam
Mar 27/2002
Examine
1
Brown
Dog
12
Sam
Apr 02/2002
Heart worm
2
Tucker
Dog
2
John
JAN 21/2002
Tetanus
2
Tucker
Dog
2
John
March
Worm test
3
Maggie
Cat
4
Sam
jan
Rabbits
3
Maggie
Cat
4
Sam
jan
Rabbies
4
Tweety
Bird
2
John
apr
Annual check
4
Tweety
Bird
2
John
apr
Eye wash
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.