Conceptual and Logical Database Model for Sun Valley College

This assignment presents a comprehensive database modeling project for Sun Valley College, encompassing both conceptual and logical models. The conceptual model utilizes an Entity Relationship Diagram (ERD) to visualize entities, attributes, and relationships, including generalization and various relationship types such as one-to-one and one-to-many. The logical model builds upon the conceptual model, refining relationships and incorporating foreign keys. The project includes detailed tables for employees, staff, administrators, professors, students, classes, faculties, departments, and courses, specifying attributes, data types, and key designations. The student decomposes a many-to-many relationship using an enrollment entity. The project adheres to database normalization principles and emphasizes the importance of relationships in maintaining data integrity and efficiency. The provided references support the design choices made in the assignment.

Running head: CONCEPTUAL AND LOGICAL DATABASE MODEL
Conceptual and Logical Database Model
Name of the Student
Name of the University
Author’s note:

1CONCEPTUAL AND LOGICAL DATABASE MODEL
Conceptual Model:
Figure 1: Conceptual Database Model
(Source: Created by Author)
The conceptual model visualizes the attributes, entities and relationships of a database.
The ERD is the most common and effective way of creating representing the conceptual models.
The proposed ERD holds the entities of database, attributes within the database as well as the
relationships among the database entities (Rao 2017). Only the foreign keys has been proposed

2CONCEPTUAL AND LOGICAL DATABASE MODEL
in the conceptual entity relationship model. The conceptual model also represent the
generalization. The entity can be referred to as an object which holds information about an
element of business. Taken as an example, the entity can represent person as well as business
elements like invoice, payment. Generally an entity in the database holds a primary key that
separates a row from other rows. However, database can have entities that does not have any
primary key. These database entities are called weak entities (Coronel and Morris 2016). Weak
entities can also hold primary keys but the primary key have to be borrowed from another entity.
Taken as an example, if an entity has a primary key that is primary key of another table then also
the entity becomes weak entity.
The relationships can be referred to as the situation that is associated between one or two
elements. A relationship can be implemented in a single entity. Taken as an example, if a set of
people is working under three different leaders then self-relation would be a great way a
representing it. The relation visualizes the structure of the database such as which entity will
borrow the primary key (Connolly and Begg 2015). The relationships in an entity relationship
diagrams are one-to-one, one-to-many, many-to-one and many-to-many. The main purpose of
relationship is implementing connection between the attributes in the entire database. Without
relationship every attribute has to be included in a single entity which can raise a huge data
anomaly issues. The relation is implemented in a database while applying the normalization
techniques (Hung, van Hung and Anh 2018). For creating each new entity in the normalization
process, a new relationship is invoked between the parent entity and newly created child entity.
The proposed entity relational model has 11 attributes for the conceptual approach. The
number of entities can increase in the logical model because logical models does not support
many-to-many relationship. The generalization exists between the employees and staff,

3CONCEPTUAL AND LOGICAL DATABASE MODEL
administrator and professor entities. The employee entity is the parent entity of staff,
administrator and professor entities. The primary key of employee entity is empNo. This primary
key is also referenced in all the three child entities. The case study suggested that professors can
also be administrator. That is why the empNo in administrator also refer to the empNo of
professor. Most of the relations in the conceptual model is one-to-many. The only man-to-many
relation exist between the student and class. This is because many student can enroll into many
classes and one class can have many student. As each staff provides academic support to many
student, staff and student have one-to-many relationship among those entities. One-to-one
relationship exists between the room and building entities.

4CONCEPTUAL AND LOGICAL DATABASE MODEL
Logical Model:
Figure 2: Logical Database Model
(Source: Created by Author)
The first thing done in the logical model is decomposing the many-to-many relationship
into one-to-many and many-to-one relationship. The generalization and other relationships has
been the same from conceptual diagram. In the logical mode, the foreign keys have been
included in to the child entities (Elmasri 2017). The implementation of enrollment entity between
the student and class allowed the logical model to decompose the many-to-many relationship.
This enrollment entity use both the student and class entity’s primary keys as foreign keys.

5CONCEPTUAL AND LOGICAL DATABASE MODEL
Enrollment is a weak entity because it uses the set of foreign keys as primary key. This entity
represent the grades for each class a particular student has got.
Table: Employees
Attribute Data Type Range Description Key
empNo INT 11 The primary key
of the entity
Primary
firstName Varchar 50 Given name of
employee
None
lastName Varchar 50 Maiden name of
employee
None
hireDate Date Date of joining None
Table: Staff
Attribute Data Type Range Description Key
empNo INT 11 The primary key
of the entity
Primary, Foreign
Key
level Varchar 50 Represent
whether staff is
level 1 or level 2
None
Table: Administrator

6CONCEPTUAL AND LOGICAL DATABASE MODEL
Attribute Data Type Range Description Key
empNo INT 11 The primary key
of the entity
Primary, Foreign
Key
adminPosition Varchar 50 The position of
the administrator
within the
organization
None
Table: Professor
Attribute Data Type Range Description Key
empNo INT 11 The primary key
of the entity
Primary, Foreign
Key
speciality Varchar 50 The subject that
professor is most
comfortable
None
rank INT 11 The rank of the
professor within
the university
None

7CONCEPTUAL AND LOGICAL DATABASE MODEL
Table: Student
Attribute Data Type Range Description Key
studentID INT 11 The primary key
of the entity
Primary
advisor INT 11 The subject that
professor is most
comfortable
Foreign Key
firstName Varchar 50 Given name of
student
None
lastName Varchar 50 Maiden name of
student
None
email Varchar 150 Email address of
the student
None
Table: Class
Attribute Data Type Range Description Key
classID INT 11 The primary key
of the entity
Primary
courseID INT 11 The course
number
represent under
Foreign Key

8CONCEPTUAL AND LOGICAL DATABASE MODEL
which course
this class exists
professor Varchar 50 This represents
which professor
takes the classes
Foreign Key
classTime Varchar 50 The time of start
of the class
None
Table: Faculty
Attribute Data Type Range Description Key
facultyID INT 11 The primary key
of the entity
Primary
dean INT 11 The dean of a
faculty can be
only professors
Foreign Key
facultyName Varchar 50 The name of the
faculty
None
Table: Department
Attribute Data Type Range Description Key

9CONCEPTUAL AND LOGICAL DATABASE MODEL
departmentID INT 11 The primary key
of the entity
Primary
headOfDepartment INT 11 The HOD of a
department can
be only
professors
Foreign Key
departmentName Varchar 50 The name of the
department
None
Table: Courses
Attribute Data Type Range Description Key
courseID INT 11 The primary key
of the entity
Primary
departmentID INT 11 Name of the
department
which provides
this course
Foreign Key
title Varchar 50 The title of the
course such as
Database System
None

10CONCEPTUAL AND LOGICAL DATABASE MODEL
Designing
description Varchar 50 Detailed
information of
the course
None
credits Decimal 5,2 The value of this
course
None

11CONCEPTUAL AND LOGICAL DATABASE MODEL
References:
Connolly, T. and Begg, C., 2015. Database systems. Pearson Education UK.
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management.
Cengage Learning.
Elmasri, R., 2017. Fundamentals of database systems.
Hung, N.V., van Hung, P. and Anh, B.T., 2018. Database Design For E-Governance
Applications: A Framework For The Management Information Systems Of The Vietnam
Commitee For Ethnic Minority Affairs (CEMA). International Journal of Civil Service Reform
and Practice, 3(1).
Rao, J., 2017. Design of Bilingual Course Teaching of Database Principles. In International
Conference on Education Innovation and Social Science (ICEISS 2017). Atlantis Press.