Database Design for College: ER Diagram, Summary, and Implementation Plan

This article discusses the database design for a college, including the ER diagram, summary, and implementation plan. It covers the entities and attributes involved, the role of a database, and the steps involved in implementing a database. It also includes a reflection on the relationships derived from the database design.

Database Design
Student Name :
Professor Name :
Course Title :
Date :

Documentation
Section 1: Database Design Diagram
The Entity Relationship Diagram (Elmasri, 2016) for the college database in Chen’s notation
(Bagui, 2011) is given below.
The table below list the entities and attributes of each of the entity involved in the ER-Diagram.
S. No Entities Attributes
1 Instructor Instructor ID (Primary Key)
Instructor Name
Instructor Address
Instructor Phone Number
Designation
Salary
2 Course Course ID (Primary Key)
Course Name
Course Description
Course Credit
3 Student Student ID (Primary Key)
Student Name
Student Address
Student Phone Number
Student DOB
Student Gender

Section 2: Design Summary
Role of Database:
Database management system (Molina, 2009) plays important role in storing, organizing and
managing the voluminous data of the organization. Thus, increasing the business operation
efficiency and reduce the overall cost of data maintenance.
Some of the benefits of using database (Gillenson, 2011) is as follows:
1) Avoids data inconsistency
2) Reduces data redundancy
3) Easy access to data
4) Data abstraction is achieved with the help of views
5) Provides security to the data
6) Efficient tracking of inventory
Database Implementation Plan:
Some of the steps involved while implementing the database (Gill, 2010) are:
1) Analyzing the database requirements:
This step involves analyzing the data that is to be stored in the database. For the above Scenario
the local college database involves storing the details about the course offered in the college, list
of instructors handling these courses and the list of students enrolled in these course
2) Collecting the Data:
This step involves collecting the data to be stored in the database. For the above scenario the
information about the student, instructor and course are collected
3) Modeling the Database:
Using the modeling software’s like Visio an ER diagram is constructed showing the cardinality,
and relationship between the entities (Silberschatz, 2010).
4) Choosing the Software:
After modeling the database, the specific DBMS like Oracle, MySQL, MS SQL etc. to be used is
selected based on the requirement of the system to be developed.
5) Implementing the software:
This step involves writing the SQL query to construct tables and to insert the data into the
database tables.
6) Creating reports:
After implementing the database, some of the reports that may be required by the user is
constructed based on the access privileges of the user. For the college database, report to view the

list of courses taken by the student, list of courses currently handled by the instructor are
constructed.
7) Maintaining the database:
After the database is constructed and deployed the database must be continuously monitored and
maintained for efficient performance.
Reflection:
The task of the college database is to maintain the list of courses available in the
particular college. The course information includes Course ID, Course Name, Course
Description and Number of credits earned in the course. For the Course entity the course ID is
used as the primary key to uniquely identify each course in the college. The database also
maintains the list of students enrolled in each course. The student information includes the
Student ID, Student Name, Student Address, Student Phone Number, Student Date of Birth and
finally the gender of the student. The Student ID is used as the primary key in the relation to
uniquely identify each of the student in the college. In the college each student can enroll
themselves in only one course at a time. Hence the Student ID is used to map the current course
enrolled by the student with the help of the Course ID.
The database also maintains the list of instructors working the particular college. The
instructor information includes the instructor ID, instructor name, instructor address, instructor
phone number, their designation and salary details. The instructors are appointed in the college to
handle particular courses. Each instructor may handle 0 or more course at same time. Hence
while developing the database design to working database via tool, a separate relation needs to
be constructed which maps the instructor ID to the list of course handled by the instructor
currently in the college.
Some of the relationship derived from the database design is given below
 Each instructor handles zero or more Courses
 Each course is handled by zero or more instructors.
 Each student enrolls in the particular course
 A course is taken by zero or more student
The relational schema in bracketing notation is given below.
Instructor (Instructor ID, Instructor Name, Instructor Address, Instructor Phone Number,
Designation, Salary)
Course (Course ID, Course Name, Course Description, Course Credit)
Course_Instructors (Course ID, Instructor ID)
Student (Student ID, Student Name, Student Address, Student Phone Number, Student DOB,
Student Gender, Course ID)

References:
Bagui, S. & Earp, R. (2011). Database design using entity-relationship diagrams (2nd ed.).
CRC Press Taylor & Francis Group.
Elmasri, R. & Navathe, S. (2016). Fundamentals of Database Systems (7th ed.).
Pearson/Addison Wesley.
Molina, H.G., Ullman, J. & Widom, J. (2009). Database Systems: The Complete Book (4th
ed.). Pearson Prentice Hall.
Silberschatz, A., Korth, H. F., & Sudarshan, S. (2010). Database System Concepts (6th ed.).
McGraw-Hill Education.
Gill, P. S. (2010). Database Management Systems (3rd ed.). I.K. International.
Gillenson, L. M. (2011). Fundamentals of Database Management Systems (2nd ed.) John
Wiley & Sons, Inc