1. The following is an unnormalized sample data of a Room-Reservation datastore.
ID: 3840283 • Letter: 1
Question
1. The following is an unnormalized sample data of a Room-Reservation datastore.
No
Reserve ID.
Reserve Date
Guest Passport
Guest Name
Room Number
Room Type
Room Rate
Date Check-in
Nights stay
1
RSV001
01-01-09
00111222
Jimmy
3011
Deluxe
$198
15-01-09
5
3022
Deluxe
$198
15-01-09
5
2414
Superior
$180
16-01-09
4
2
RSV002
02-01-09
00333654
Henry
5001
Junior Suite
$318
16-01-09
2
3
RSV003
02-01-09
00876543
Thomas
2007
Superior
$180
17-01-09
3
2008
Superior
$180
17-01-09
3
4
RSV004
03-01-09
00991213
Lily
3024
Deluxe
$198
17-01-09
1
Using the above sample data, carry out the normalization process into its first, second and third normal form. Your answers should be presented as follows:
a. First normal form (1NF) notation and sample data.
b. Second normal form (2NF) notation and sample data.
c. Third normal form (3NF) notation and sample data.
No
Reserve ID.
Reserve Date
Guest Passport
Guest Name
Room Number
Room Type
Room Rate
Date Check-in
Nights stay
1
RSV001
01-01-09
00111222
Jimmy
3011
Deluxe
$198
15-01-09
5
3022
Deluxe
$198
15-01-09
5
2414
Superior
$180
16-01-09
4
2
RSV002
02-01-09
00333654
Henry
5001
Junior Suite
$318
16-01-09
2
3
RSV003
02-01-09
00876543
Thomas
2007
Superior
$180
17-01-09
3
2008
Superior
$180
17-01-09
3
4
RSV004
03-01-09
00991213
Lily
3024
Deluxe
$198
17-01-09
1
Explanation / Answer
c. 3NF Form
Reservation Table
ID
Reserve ID.
Reserve Date
Date Check-in
Nights Stay
1
RSV001
01-01-09
15-01-09
5
2
RSV001
01-01-09
15-01-09
5
3
RSV001
01-01-09
16-01-09
4
4
RSV002
02-01-09
16-01-09
2
5
RSV003
02-01-09
17-01-09
3
6
RSV003
02-01-09
17-01-09
3
7
RSV004
03-01-09
17-01-09
1
Room Table
ID
Room Number
Room Type
Room Rate
1
3011
Deluxe
$198
2
3022
Deluxe
$198
3
2414
Superior
$180
4
5001
Junior Suite
$318
5
2007
Superior
$180
6
2008
Superior
$180
7
3024
Deluxe
$198
Guest Table
ID
Guest Name
Guest Passport
1
Jimmy
00111222
2
Henry
00333654
3
Thomas
00876543
4
Lily
00991213
b. 2NF Form
Reservation Table
ID
Reserve ID.
Reserve Date
Date Check-in
Nights Stay
Customer ID
Room ID
1
RSV001
01-01-09
15-01-09
5
1
1
2
RSV001
01-01-09
15-01-09
5
1
2
3
RSV001
01-01-09
16-01-09
4
1
3
4
RSV002
02-01-09
16-01-09
2
2
4
5
RSV003
02-01-09
17-01-09
3
3
5
6
RSV003
02-01-09
17-01-09
3
3
6
7
RSV004
03-01-09
17-01-09
1
4
7
Room Table
ID
Room Number
Room Type
Room Rate
1
3011
Deluxe
$198
2
3022
Deluxe
$198
3
2414
Superior
$180
4
5001
Junior Suite
$318
5
2007
Superior
$180
6
2008
Superior
$180
7
3024
Deluxe
$198
Guest Table
ID
Guest Name
Guest Passport
1
Jimmy
00111222
2
Henry
00333654
3
Thomas
00876543
4
Lily
00991213
a. 1NF Form
Guest Table
ID
Guest Name
Guest Passport
1
Jimmy
00111222
2
Henry
00333654
3
Thomas
00876543
4
Lily
00991213
Room Table
ID
Room Number
Room Type
Room Rate
1
3011
Deluxe
$198
2
3022
Deluxe
$198
3
2414
Superior
$180
4
5001
Junior Suite
$318
5
2007
Superior
$180
6
2008
Superior
$180
7
3024
Deluxe
$198
Reservation table
ID
Reserve ID.
Reserve Date
Date Check-in
Nights Stay
1
RSV001
01-01-09
15-01-09
5
2
RSV001
01-01-09
15-01-09
5
3
RSV001
01-01-09
16-01-09
4
4
RSV002
02-01-09
16-01-09
2
5
RSV003
02-01-09
17-01-09
3
6
RSV003
02-01-09
17-01-09
3
7
RSV004
03-01-09
17-01-09
1
ID
Reserve ID.
Reserve Date
Date Check-in
Nights Stay
1
RSV001
01-01-09
15-01-09
5
2
RSV001
01-01-09
15-01-09
5
3
RSV001
01-01-09
16-01-09
4
4
RSV002
02-01-09
16-01-09
2
5
RSV003
02-01-09
17-01-09
3
6
RSV003
02-01-09
17-01-09
3
7
RSV004
03-01-09
17-01-09
1
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.