Database Design: Normalization and Relational Schema Solution

This assignment solution focuses on database design, specifically addressing the normalization of a given table. The solution begins with a dependency diagram to illustrate the relationships between attributes. It then proceeds to normalize the table through the first, second, and third normal forms (1NF, 2NF, and 3NF), explaining each step and the rationale behind it. The solution includes the identification of primary keys, functional dependencies, and transitive dependencies. Finally, it presents a relational schema, providing a structured representation of the database design. The document utilizes a given table with student and course information to demonstrate the concepts. The assignment also includes a bibliography with relevant sources. The solution is designed to provide a clear and concise understanding of database design principles.

Running head: THE DATABASE DESIGN
The Database Design
Name of the Student
Name of the University

1THE DATABASE DESIGN
Table of Contents
Given Table:....................................................................................................................................2
1. Dependency Diagram:.................................................................................................................2
2. Normalization:.............................................................................................................................3
3. Relational Schema:......................................................................................................................7
Bibliography:...................................................................................................................................8

2THE DATABASE DESIGN
Given Table:
1. Dependency Diagram:
Figure 1: Dependency Diagrams
Student
_ID
Student_N
ame
Campus_Ad
dress
Maj
or
Course
_ID
Course_
title
Instructor_
Name
Instructor_Lo
cation
Gra
de
1683005
8
Williams Albury BIT ITC114 Database
Sys
Peter B104 HD
1683005
8
Williams Albury BIT ITC211 Sys
Analysis
John B115 D
5432910
73
Baker Wagga Acct ITC200 Database
Mgt
David H310 D
5432910
73
Baker Wagga Acct Acc11 Fund
Acct
William A120 C
5432910
73
Baker Wagga Acct Mkg21 Intro Mkt Mike A112 P

3THE DATABASE DESIGN
(Source: Created by Author)
2. Normalization:
1st Normal Form: The first normal form of database defines that an attribute of a
database cannot have multiple values within it. Each and every attribute need to have atomic
value.
Student
_ID
Student_N
ame
Campus_Ad
dress
Maj
or
Course
_ID
Course_
title
Instructor_
Name
Instructor_Lo
cation
Gra
de
1683005
8
Williams Albury BIT ITC114 Database
Sys
Peter B104 HD
1683005
8
Williams Albury BIT ITC211 Sys
Analysis
John B115 D
5432910
73
Baker Wagga Acct ITC200 Database
Mgt
David H310 D
5432910
73
Baker Wagga Acct Acc11 Fund
Acct
William A120 C
5432910
73
Baker Wagga Acct Mkg21 Intro Mkt Mike A112 P

4THE DATABASE DESIGN
The table signifies that each of the attribute has atomic value therefore the table is in first
normal form.
2nd Normal Form: Each and every non-key attribute needs to be fully functionally
dependent on the prime attribute.
Student_ID Student_Name Campus_Address Major
16830058 Williams Albury BIT
16830058 Williams Albury BIT
543291073 Baker Wagga Acct
543291073 Baker Wagga Acct
543291073 Baker Wagga Acct

5THE DATABASE DESIGN
The student name and campus address is functionally dependent on student ID. Student
ID-> (student name, campus address) and Major -> (course_ID, Course_title, Instructor_name,
Instructor_Location, Grade)
3rd Normal Form: No non-key attribute must be dependent transitively on the key
attribute.
Major Course_ID Course_title Instructor_Name Instructor_Location Grade
BIT ITC114 Database Sys Peter B104 HD
BIT ITC211 Sys Analysis John B115 D
Acct ITC200 Database Mgt David H310 D
Acct Acc11 Fund Acct William A120 C
Acct Mkg21 Intro Mkt Mike A112 P

6THE DATABASE DESIGN
Major Course_ID Instructor_Name
BIT ITC114 Peter
BIT ITC211 John
Acct ITC200 David
Acct Acc11 William
Acct Mkg21 Mike
Course_ID Course_title Grade
ITC114 Database Sys HD
ITC211 Sys Analysis D
ITC200 Database Mgt D
Acc11 Fund Acct C
Mkg21 Intro Mkt P

7THE DATABASE DESIGN
Course title and instructor location both can be identified by major as well as course ID
and instructor name respectively. This it creates transitive dependency in table.
Instructor_Name Instructor_Location
Peter B104
John B115
David H310
William A120
Mike A112