Database Design Modification Report: College Database Design Analysis

This report presents a modified Entity Relationship Diagram (ERD) for a college database, addressing design improvements and normalization. The report details the ERD's structure, consisting of eleven entities, including student, campus, course, instructor, and room tables. The student table incorporates essential attributes like name, address, and GPA, with CampusID as a foreign key establishing a one-to-many relationship. Modifications include the creation of separate tables for student grades and phone numbers to achieve normalization and accommodate multiple entries. The report explains the many-to-many relationship between instructors and courses, implemented through an Instructor_Course entity. The ERD also illustrates relationships between rooms and campuses, and the use of a separate table for email addresses for instructors. The design adheres to normalization principles up to 3NF, ensuring efficient and scalable database operations. The report concludes with a bibliography of relevant research papers.

Running head: MODIFYING A DATABASE DESIGN
Modifying a Database Design
Name of the Student
Name of the University

1MODIFYING A DATABASE DESIGN
1. Modified Diagram:
Figure 1: The Modified ERD of College
(Source: Created by Author)
2. Design Summary:
The final ERD is consisted of eleven entities. The student table is consisting of all the
relevant entities like, name, address, contact number, address, GPA, birthdate and many more.
The CampusID is the foreign key of the campus table. Each of the student will be associated with
a single campus so the one-to-many relation between campus and student. In the previous
diagram, the student table was consisting of courseID as the foreign key. In the modified
diagram, a different table has been created to hold the studentID, courseID and grade. This
defines that the database holds the grade of the students after completion of each course. This
allows the database to store the grades for different courses for each of the student without any
anomalies. The phone numbers will be stored in a different table so that more than one mobile

2MODIFYING A DATABASE DESIGN
number of the student can be stored in easily. This allows the database to be in a normalize form
as the first rule of normalization is that each attribute must be having atomic value. The college
can allow the student to store as many numbers as possible. This is also beneficial in terms of
database design as the no space will be vacant in the table because the columns can hold zero to
several phone numbers in the table against an individual student.
The business requirement was that an instructor can teach in various courses. This also
implies that various courses can taught by various instructors. This implies that the relation
between the instructor and course is many-to-many. Instrutor_Course entity has been created in
the ERD to divide the many-to-many into one-to-many relation. The Instrutor_Course entity will
be consisting of two attributes InstructorID and CourseID. These entities will be composite
primary key. InstructorID is the foreign key of Instructor entity and the CourseID is Course.
RoomID in the Course entity as the reference key indicates that many course can be held in one
many room. The ERD clearly signifies that various rooms can be in a same campus. This has
been implemented through defining campusID foreign key in Room attribute. The email address
will be stored in a different table so that more than one mobile number of the instructor can be
stored in easily. This implies that the table maintains the normalization rules of the database, the
atomic value for each attribute. The college can allow the instructor to store as many email
addresses as possible. This is also beneficial in terms of database design as the no space will be
vacant in the table because the columns can hold zero to several email addresses in the table
against an individual student.
The ERD has completely derived all the design aspects of student and course scenario.
All the relations has been divided into proper manner so that real life implementation can be
possible. The ERD is also normalized up to 3 NF.

3MODIFYING A DATABASE DESIGN

4MODIFYING A DATABASE DESIGN
Bibliography:
Al-Btoush, A. A. S. (2015). Extracting Entity Relationship Diagram (ERD) from English
Sentences. International Journal of Database Theory and Application, 8(2), 235-244.
Al-Masree, H. K. (2015). Extracting Entity Relationship Diagram (ERD) from relational
database schema. International Journal of Database Theory and Application, 8(3), 15-26.
Dedhia, R., Jain, A., & Deulkar, K. (2015). Techniques to automatically generate Entity
Relationship Diagram.
Yang, L., & Cao, L. (2016). The Effect of MySQL Workbench in Teaching Entity-Relationship
Diagram (ERD) to Relational Schema Mapping. International Journal of Modern
Education and Computer Science, 8(7), 1.