optional contribution- slide
pptx
keyboard_arrow_up
School
Northeastern University *
*We aren’t endorsed by this school
Course
3200
Subject
Information Systems
Date
Feb 20, 2024
Type
pptx
Pages
7
Uploaded by BaronPrairieDog1268
1N
F
Each cell should contain 1 value and each record should be unique (determined by keys)
To move a database to 2NF, it has to be in 1NF
Contains a primary key that is not functionally dependent on another (cannot be composite)
To move a database to 3NF, it has to be in 2NF
Cannot contain transitive functional dependencies
Transitive functional dependencies: When changing a non-
key column causes other non-key columns to change
Every prime attribute can ONLY depend on a superkey 2N
F
3N
F
BCNF
COURSE database
Course_ID,
Course_location, instructor_name,
field_of_study
department
Instructor_number
Instructor_state
Instructor_zipcode
INSTRUCTOR
Instructor_ID
Instructor_name
field_of_study
department
Instructor_state
Instructor_zipcode
COURSE
Course_ID
Course_location
Instructor_ID
INSTRUCTOR
Instructor_ID
Instructor_name
field_of_study
department
Instructor_zipcode
ADDRESS
Instructor_zipcode
Instructor_state
INSTRUCTOR
Instructor_ID
Instructor_name
field_of_study
Instructor_zipcode
DEPARTMENT
field_of_study
department
1N
F
Each cell should contain 1 value and each record should be unique (determined by keys)
course_ID
Course_location
instructor_
name
field_of_study
department
Instructor
_number
Instructor_state
Instructor
_zipcode
0100
Hawkings
Fueller
Genetics
Biology
90873424
32
NY
01320
0200
Bopkins
Harshil
Molecular Biology
Biology
93829493
23
NY
03432
2NF
To move a database to 2NF, it has to be in 1NF
Contains a primary key that is not functionally dependent on another (cannot be composite)
course_ID
Course_location
instructor_ID
0100
Hawkings
1
0200
Bopkins
2
instructor_I
D
instructor_
name
field_of_study
department
Instructor
_number
Instructor_state
Instructor
_zipcode
1
Fueller
Genetics
Biology
908734243
2
NY
01320
2
Harshil
Molecular Biology
Biology
938294932
3
NY
03432
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
3NF
To move a database to 3NF, it has to be in 2NF
Cannot contain transitive functional dependencies
Transitive functional dependencies: When changing a non-key column causes other non-key columns to change
instructor_I
D
instructor_
name
field_of_study
department
Instructor
_number
Instructor
_zipcode
1
Fueller
Genetics
Biology
908734243
2
01320
2
Harshil
Molecular Biology
Biology
938294932
3
03432
Instructor_state
Instructor
_zipcode
NY
01320
NY
03432
BCN
F
Every prime attribute can ONLY depend on a superkey instructor_ID
instructor
_name
field_of_study
Instructor
_number
Instructor
_zipcode
1
Fueller
Genetics
908734243
2
01320
2
Harshil
Molecular Biology
938294932
3
03432
field_of_study
department
Genetics
Biology
Molecular Biology
Biology
Final Database IN BCNF
instructor_ID
instructor
_name
field_of_study
Instructor
_number
Instructor
_zipcode
1
Fueller
Genetics
908734243
2
01320
2
Harshil
Molecular Biology
938294932
3
03432
field_of_stud
y
departme
nt
Genetics
Biology
Molecular Biology
Biology
Instructor_state
Instructor
_zipcode
NY
01320
NY
03432
course_ID
Course_location
instructor_ID
0100
Hawkings
1
0200
Bopkins
2
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Explanation
I found it difficult to wrap my head around the difference between 1NF, 2NF, 3NF, BCNF. I thought it was helpful to see how you can convert a table into these forms using the same example. I couldn’t find anything online that normalized the same database in each form. Thus, I created one that easily illustrated the changes.