Data and Information Management Report: Conceptual and Logical Model

This report analyzes data and information management, focusing on the development of a database for Sun Valley College. It begins with the identification of entities such as Employees, Staff, Administrators, Professors, Faculties, Departments, Courses, Students, Classes, Rooms, Buildings, and Advisors, detailing their attributes and relationships. The conceptual model, represented through an Entity-Relationship diagram, illustrates the structure and relationships between entities. The report then transitions to the logical model, specifying data attributes, their types, and foreign keys for each entity. It also describes one-to-one relationships and provides redesigned conceptual and logical model diagrams. The report includes detailed tables outlining entity relationships, cardinalities, primary, candidate, and surrogate keys, and data dictionaries for each entity's attributes. The assignment provides a comprehensive overview of database design principles, model creation, and the practical application of these concepts in a real-world scenario.

Running head: DATA AND INFORMATION MANAGEMENT
Data and Information Management
Name of the Student:
Name of the University:
Author Note

1
DATA AND INFORMATION MANAGEMENT
Table of Contents
Part 1. Conceptual Model................................................................................................................2
Identification of Entities..............................................................................................................2
Identification of entity super types and subtypes and their types, inheritance, and
discriminators..............................................................................................................................3
Relationships between entities and their cardinalities.................................................................4
Identification of entity primary, candidate, and surrogate keys..................................................5
Conceptual Model Entity-Relationship diagram.........................................................................6
Part 2. Logical Model......................................................................................................................7
Data attributes and their types for each entity.............................................................................7
Identification of foreign keys for each entity............................................................................13
Description of one-to-one relationships.....................................................................................14
Redesigning of Conceptual Model............................................................................................16
Logical Model diagram..............................................................................................................17
Bibliography..................................................................................................................................18

2
DATA AND INFORMATION MANAGEMENT
Part 1. Conceptual Model
Identification of Entities
An entity in Database design is usually a table which had a primary key that references
the ID of “entity” group, and with all other it’s attribute. an attribute which any other tables
reference to its. The main entities of the system identified for the development of the system for
the Sun valley college database are listed below:
 Employees: The details of the employees are to be stored by the entity consisting of the
EmpID, name and date of hiring.
 Staffs: Staffs details include the details of the employees as a staff is also an employee of
the company.
 Administrators: The administrator is also an employee to the company however having
special privileges that the others staffs in the company would not be having.
 Professors: The professor is also one of the employees of the company and professor has
a specialization and also some rank is assigned to the professors.
 Faculties: The faculties are part of the university as well and the and they are the ones
who take up the classes for the university.
 Departments: There are a number of departments in the university and the departments
are provided with an ID and department name
 Courses: There are a number of courses in the university under each department in the
organization which are identified by the courseID and grouped under the departmentID.
 Students: The student details are available in the system with student id and the student
names and email being the attributes of the entity.

3
DATA AND INFORMATION MANAGEMENT
 Class: The class are made up of a course and room number and students participating in
it. In addition to this, there is faculty who would be taking up the classes.
 Room: The details of the room are being stored as an entity in the system and the
attributes being the RoomID and the type of the room.
 Buildings: The Building details are being stored in the system and stored as an entity in
database.
 Advisor: The advisors are the professors who are interacting with the students of the
university.
 Level 1 Staffs: The staffs can categorize as per their level and the level 1 staffs are stored
in the Level 1 entity in the database.
 Level 2 Staffs: The staffs can categorize as per their level and the level 2 staffs are stored
in the Level 2 entity in the database.
Identification of entity super types and subtypes and their types, inheritance, and
discriminators
The super type entities are Employees. The subtype entity of employees is Staff,
administrator and professors. The staff subtype is further sub typed as advisors. The main
inheritance takes place with the attribute EmployeeID in the Employees which is being inherited
by the all the subtypes Staff, administration and professors. The main discriminators among the
entities are the StaffID in staff, AdminID in Administrator and the identification of the
professors. These ID are different for the different type of entities of the systems. Additionally,
the Level 1 Staffs and the Level 2 Staffs inherit the staffID from the staff entity in the system.
They are again discriminated by Level 1 ID and Level 2 ID.

4
DATA AND INFORMATION MANAGEMENT
Relationships between entities and their cardinalities
Cardinality refers to the quantity of elements in a set. In relational database terms, it
typically refers to the number of tuples in a relation. In SQL database terms, it typically refers to
the number of rows in a table or query result. The relationship in between each of the entities are
described with the help of the table provided below:
Entity Cardinality Relationship Cardinality Entity
Employees One Is a Many Staff
Employees One Is a Many Administrator
Employees One Is a Many Professors
Staff One Is a Many Level 1
Staff One Is a Many Level 2
Professor One Dean of One Faculties
Professor One HOD of One Department
Student One Enrolled In Many Classes
Department One Consists of Many Courses
Classes One Held in One Room
Classes Many Held for One Course
Building One Has Many Room
Advisor One Advises Many Students
Advisor One Is a One Professor
Faculties One Take Many Classes
Table 1: Table of relationships

5
DATA AND INFORMATION MANAGEMENT
Source: (created by author)
Identification of entity primary, candidate, and surrogate keys
The role of a Primary Key (PK) is to provide a unique identifier to each row in a table.
That is why it cannot be null. A candidate key is a type of database constraint, i.e., a constraint
on the contents of a particular database that must be true in order for the content to be considered
correct. The table provided below has been used for the identification of the primary, candidate
and surrogate keys:
Entity Primary Key Candidate Key Surrogate Key
Employees EmpID EmpID EmpID, HireDate
Staff StaffID StaffID. EmpID EmpID, StaffID
Administrator AdminID AdminID. EmpID EmpID, AdminID,
AdminPosition
Professors ProfessorID ProfessorID. EmpID EmpID, ProfessorID
Faculties FacultyID FacultyID, Dean FacultyID, Dean,
FacultyName
Department DepartmentID DepartmentID, HOD DepartmentID,
DepartmentName
Courses CourseID CourseID, Title,
DepartmentID
CourseID, Title,
DepartmentID
Students StudentID StudentID Email
Class ClassID ClassID, ClassTime CourseID, RoomID
Room RoomID RoomID RoomID
Buildings BuildingNiumber BuildingNiumber BuildingNumber

6
DATA AND INFORMATION MANAGEMENT
Advisors AdvisorID AdvisorID StudentID,
ProfessorID
Level 1 Staffs StaffID StaffID StaffID
Level 2 Staffs StaffID StaffID StaffID
Table 2: Table of keys for each of the entity
Source: (created by author)
Conceptual Model Entity-Relationship diagram
The conceptual model provides the model of all the data which are required to be stored
in the system. The Entity relationship diagram is used for the real-world data models of the
system. The conceptual model of the Sun Valley college database is provided below:

7
DATA AND INFORMATION MANAGEMENT
Figure 1: Conceptual Diagram of Sun Valley College Database
Source: (created by author)
Part 2. Logical Model
Data attributes and their types for each entity
The attributes and the type of data for each of the entity identified in the system are
described below:

8
DATA AND INFORMATION MANAGEMENT
Employee
Attribute Datatype Size NULL/NOT
NULL
Key
EmpID Int NOT NULL Primary Key
LastName String 20 NOT NULL
FirstName String 20 NOT NULL
HireDate Date NOT NULL
Table 3: Data dictionary of Employees
Source: (created by author)
Admin
Attribute Datatype Size NULL/NOT
NULL
Key
AdminID Int NOT NULL Primary Key
EmpID Int NOT NULL Foreign Key
AdminPosition String 20 NOT NULL
Table 4: Data dictionary of Admin
Source: (created by author)
Staff
Attribute Datatype Size NULL/NOT Key

9
DATA AND INFORMATION MANAGEMENT
NULL
StaffID Int NOT NULL Primary Key
EmpID Int NOT NULL Foreign Key
StaffLevel String 20 NOT NULL
Table 5: Data dictionary of Staff
Source: (created by author)
Level 1
Attribute Datatype Size NULL/NOT
NULL
Key
StaffID Int NOT NULL Foreign Key
Table 6: Data dictionary of Level 1 staffs
Source: (created by author)
Level 2
Attribute Datatype Size NULL/NOT
NULL
Key
StaffID Int NOT NULL Foreign Key
Table 7: Data dictionary of Level 2 staffs
Source: (created by author)

10
DATA AND INFORMATION MANAGEMENT
Professors
Attribute Datatype Size NULL/NOT
NULL
Key
ProfessorID Int NOT NULL Primary Key
EmpID Int NOT NULL Foreign Key
Specialty String 20 NOT NULL
Rank String 20 NOT NULL
Table 8: Data dictionary of Professor
Source: (created by author)
Faculty
Attribute Datatype Size NULL/NOT
NULL
Key
FacultyID Int NOT NULL Primary Key
FacultyName String 20 NOT NULL
Dean Int NOT NULL Foreign Key
Table 9: Data dictionary of Faculty
Source: (created by author)
Building

11
DATA AND INFORMATION MANAGEMENT
Attribute Datatype Size NULL/NOT
NULL
Key
BuildingNumber Int NOT NULL Primary Key
Table 10: Data dictionary of Building
Source: (created by author)
Rooms
Attribute Datatype Size NULL/NOT
NULL
Key
BuildingNumber Int NOT NULL Primary Key
Type String 30 NOT NULL
BuildingNumber Int NOT NULL Foreign Key
Table 11: Data dictionary of Rooms
Source: (created by author)
Department
Attribute Datatype Size NULL/NOT
NULL
Key
DepartmentID Int NOT NULL Primary Key
DepartmentName String 30 NOT NULL