Data And Information Management Case Study 2022

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.