University Database Design Report: Normalization, Schema and Diagrams

This report delves into the principles of relational database design, focusing on normalization and schema creation. It begins with a dependency diagram illustrating the relationships between database attributes. The report then meticulously explains the process of normalization, progressing through the 1st, 2nd, and 3rd Normal Forms (1NF, 2NF, 3NF), ensuring data integrity and minimizing redundancy. The student provides examples of how to apply these normal forms to the given tables. Subsequently, the report presents a relational schema, providing a visual representation of the database structure and the relationships between tables, including Student and Course tables. The report concludes with a bibliography of relevant academic sources. This assignment is designed to demonstrate a comprehensive understanding of database design, normalization, and relational schema creation, offering a practical application of these core concepts.

Running head: RELATIONAL DATABASE DESIGN
Relational Database Design
Name of the Student
Name of the University

1RELATIONAL DATABASE DESIGN
Table of Contents
1. Dependency Diagram:.................................................................................................................2
2. Normalization:.............................................................................................................................2
3. Relational Schema:......................................................................................................................6
Bibliography:...................................................................................................................................7

2RELATIONAL DATABASE DESIGN
1. Dependency Diagram:
Figure 1: Dependency Diagrams Based on Given Table
(Source: Created by Author)
2. Normalization:
1st Normal Form: Every attribute in the database need to have atomic value. Every
attribute in the following table is consist of single value.

3RELATIONAL DATABASE DESIGN
2nd Normal Form: The non-prime attributes of the table should be fully functionally
dependent on the prime attribute. The Course_ID has been used as the foreign key to relate the
tables.
Student Table: Student_ID -> (Student_Name, Campus_Address, Major, Course_ID)
Course Table: Course_ID -> (Course_title, Instructor_Name, Instructor_Location, Grade)
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

4RELATIONAL DATABASE DESIGN
Student_ID Student_Name Campus_Address Major Course_I
D
16830058 Williams Albury BIT ITC114
16830058 Williams Albury BIT ITC211
543291073 Baker Wagga Acct ITC200
543291073 Baker Wagga Acct Acc11
543291073 Baker Wagga Acct Mkg21
Course_ID Course_title Instructor_Name Instructor_Location Grade
ITC114 Database Sys Peter B104 HD
ITC211 Sys Analysis John B115 D
ITC200 Database Mgt David H310 D
Acc11 Fund Acct William A120 C
Mkg21 Intro Mkt Mike A112 P

5RELATIONAL DATABASE DESIGN
3rd Normal Form: There should not be any transitive dependency among the non-prime
attribute and key-attribute.
Student_ID Student_Name Campus_Address Course_ID
16830058 Williams Albury ITC114
16830058 Williams Albury ITC211
543291073 Baker Wagga ITC200
543291073 Baker Wagga Acc11
543291073 Baker Wagga Mkg21
Campus_Address Major
Albury BIT
Albury BIT
Wagga Acct
Wagga Acct
Wagga Acct

6RELATIONAL DATABASE DESIGN
Course_ID Course_title
Grade
Instructor_Nam
e
ITC114 Database Sys HD Peter
ITC211 Sys Analysis D John
ITC200 Database Mgt D David
Acc11 Fund Acct C William
Mkg21 Intro Mkt P Mike
Instructor_Name Instructor_Location
Peter B104
John B115
David H310
William A120
Mike A112

7RELATIONAL DATABASE DESIGN
3. Relational Schema:
Figure 2: Relational Schema
(Source: Created by Author)