Practice Problem #1Normalize the dataset into 3NF tables. Show the normalization steps for each dataset using the format provided in the example. Example) 1NF: PROJECT (ProjectNum, Proj_Name, Start_Date, Emp_Num, Emp_Name, Job_Class) 2NF: PROJECT (ProjectNum, Proj_Name, Start_Date) EMP(Emp_Num, Emp_Name, Job_Class) 3NF: PROJECT (ProjectNum, Proj_Name, Start_Date) EMP(Emp_Num, Emp_Name, Job_Class) ** Bold and underline the primary key EMP_NUMEMP_LNAMEEMP_EDUCATIONJOB_CLASSEMP_DEPENDENTSDEPT_CODEDEPT_NAMEDEPT MANAGEREMP_TITLEEMP_DOBEMP_HIRE_DATEEMP TRAININGEMP_BASE_SALARYEMP_COMMISSION_RATE1003WillakerBBA, MBASLSGerald (spouse),Mary (daughter),John (son)MKTGMarketingJill H. MartinSales Agent23-Dec-196814-Oct-1997L1, L2$38,255.000.0151018SmithBBASLSMKTGMarketingJill H. MartinSales Agent28-Mar-197915-Jan-2006L1$30,500.000.0101019McGuireJNTJoAnne (spouse)SVCGeneral ServiceHank B. JonesJanitor18-May-198221-Apr-2003L1$19,750.001023McGuireBS, MS, Ph.D.DBAGeorge (spouse)Jill (daughter)INFSInfo. SystemsCarlos G. OrtezDB Admin20-Jul-195915-Jul-1999L1, L3, L8, L15$127,900.00

Answered: Practice Problem #1 Normalize the…

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Related questions

Concept explainers

Question

Practice Problem #1

Normalize the dataset into 3NF tables. Show the normalization steps for each dataset using the format provided in the example.

Example)

1NF:

PROJECT (ProjectNum, Proj_Name, Start_Date, Emp_Num, Emp_Name, Job_Class)

2NF:

PROJECT (ProjectNum, Proj_Name, Start_Date)

EMP(Emp_Num, Emp_Name, Job_Class)

3NF:

PROJECT (ProjectNum, Proj_Name, Start_Date)

EMP(Emp_Num, Emp_Name, Job_Class)

** Bold and underline the primary key

EMP_NUM
EMP_LNAME
EMP_EDUCATION
JOB_CLASS
EMP_DEPENDENTS
DEPT_CODE
DEPT_NAME
DEPT MANAGER
EMP_TITLE
EMP_DOB
EMP_HIRE_DATE
EMP TRAINING
EMP_BASE_SALARY
EMP_COMMISSION_RATE
1003
Willaker
BBA, MBA
SLS
Gerald (spouse),
Mary (daughter),
John (son)
MKTG
Marketing
Jill H. Martin
Sales Agent
23-Dec-1968
14-Oct-1997
L1, L2
$38,255.00
0.015
1018
Smith
BBA
SLS
MKTG
Marketing
Jill H. Martin
Sales Agent
28-Mar-1979
15-Jan-2006
L1
$30,500.00
0.010
1019
McGuire
JNT
JoAnne (spouse)
SVC
General Service
Hank B. Jones
Janitor
18-May-1982
21-Apr-2003
L1
$19,750.00
1023
McGuire
BS, MS, Ph.D.
DBA
George (spouse)
Jill (daughter)
INFS
Info. Systems
Carlos G. Ortez
DB Admin
20-Jul-1959
15-Jul-1999
L1, L3, L8, L15
$127,900.00

Expert Solution

Step 1: Step 1

DATA SET #1:

1NF:

STUDENT(Stu_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)

2NF:

STUDENT(Stu_Num, Stu_Lname, Stu_Major,Dept_Code, Advisor_Lname, Stu_Gpa, Stu_Hours, Stu_Class)

DEPT(Dept_Code,Dept_Name,Dept_phone,College_Name)

ADVISOR(Advisor_Lname, Advisor_Office, Advisor_Bldg, Advisor_Phone)

3NF:

STUDENT(Stu_Num, Stu_Lname, Stu_Major,Dept_Code, Advisor_Lname, Stu_Gpa, Stu_Hours, Stu_Class)

DEPT(Dept_Code,Dept_Name,Dept_phone,College_Name)

ADVISOR(Advisor_Lname, Advisor_Office, Advisor_Bldg, Advisor_Phone)

Step by step

Solved in 3 steps

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.

Similar questions