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.

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

12
DATA AND INFORMATION MANAGEMENT
HeadOfDepartment Int NOT NULL Foreign Key
Table 12: Data dictionary of Department
Source: (created by author)
Courses
Attribute Datatype Size NULL/NOT
NULL
Key
CourseID Int NOT NULL Primary Key
Title String 30 NOT NULL
Description String 100 NOT NULL
Credits String 30 NOT NULL
DepartmentID Int NOT NULL Foreign Key
Table 13: Data dictionary of Courses
Source: (created by author)
Classes
Attribute Datatype Size NULL/NOT
NULL
Key
ClassID Int NOT NULL Primary Key
ClassTime Time NOT NULL

13
DATA AND INFORMATION MANAGEMENT
CourseID Int NOT NULL Foreign Key
RoomID Int NOT NULL Foreign Key
FacultyID Int NOT NULL Foreign Key
Table 14: Data dictionary of Classes
Source: (created by author)
Student
Attribute Datatype Size NULL/NOT
NULL
Key
StudentID Int NOT NULL Primary Key
FirstName String 30 NOT NULL
LastName String 30 NOT NULL
Email String 100 NOT NULL
DepartmentID Int NOT NULL Foreign Key
Table 15: Data dictionary of Student
Source: (created by author)
Identification of foreign keys for each entity
The foreign keys for each of the described entity is provided in the table below:
Entity Foreign Key
Admin EmpID

14
DATA AND INFORMATION MANAGEMENT
Professors EmpID
Faculties Dean
Department HedOfDepartment
Student ClassID
Classes CourseID, RoomID, FacultyID
Rooms BuildingNumber
Table 16: Table of foreign keys
Source: (created by author)
Description of one-to-one relationships
Descriptions of the One-to-One relationship are provided below:
Entity Cardinality Relationship Cardinality Entity
Professor One Dean of One Faculties
Table 17: One to one relationship in between Professor and Faculties
Source: (created by author)
The dean relationship is created in between the faculties and the professor. The
relationship is a one to one relationship and the it is to be noted that a single professor can only
be dean of a single faculty and a single faculty can have only a single dean. Hence the one to one
relationship is justified.

15
DATA AND INFORMATION MANAGEMENT
Entity Cardinality Relationship Cardinality Entity
Professor One HOD of One Department
Table 18: One to one relationship in between Professor and Department
Source: (created by author)
The head of the department relationship is created in between the Department and the
professor. The relationship is a one to one relationship and the it is to be noted that a single
professor can only be head of the department for a single Department and a single Department
can have only a single head of the department. Hence this provide the justification of the one to
one relationship in between the entities.
Entity Cardinality Relationship Cardinality Entity
Classes One Held in One Room
Table 19: One to one relationship in between Classes and Room
Source: (created by author)
The relationship named held in is created in between the Classes and the Rooms. The
relationship is a one to one relationship and an assumption has been made in scenario that a
single class can only be held in a single Room and a single Room can have only allocate a single
class at any specific time. Hence this provide the justification of the one to one relationship in
between the entities.
Entity Cardinality Relationship Cardinality Entity
Advisor One Is a One Professor
Table 17: One to one relationship in between Professor and Advisor

16
DATA AND INFORMATION MANAGEMENT
Source: (created by author)
The relationship in between the Advisor and the professor is created. The relationship is a
one to one relationship and the it is to be noted that a single professor can only be Advisor of the
Single child and one child can only have a single advisor. Hence the one to one relationship is
justified.
Redesigning of Conceptual Model
The redesigned conceptual model of the Sun Valley College Database is provided below:
Figure 2: Redesign of Conceptual Diagram of Sun Valley College Database
Source: (created by author)

17
DATA AND INFORMATION MANAGEMENT
Logical Model diagram
The logical model provides the data and the type of data to be stored in the database. The
logical model diagram for the Sun Valley College database is provided below:
Figure 3: Logical Model Diagram of Sun Valley College Database
Source: (created by author)
Bibliography
Connolly, T. and Begg, C., 2015. Database systems. Pearson Education UK.

18
DATA AND INFORMATION MANAGEMENT
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management.
Cengage Learning.
de Lima, C. and dos Santos Mello, R., 2015, December. A workload-driven logical design
approach for NoSQL document databases. In Proceedings of the 17th International Conference
on Information Integration and Web-based Applications & Services (p. 73). ACM.
Iorga, M., Feldman, L., Barton, R., Martin, M.J., Goren, N.S. and Mahmoudi, C., 2018. Fog
computing conceptual model (No. Special Publication (NIST SP)-500-325).
Malta, M.C., González-Blanco, E., Cantón, C.M. and Del Rio, G., 2017. A Common Conceptual
Model For The Study of Poetry In The Digital Humanities. In DH.
Tatomir, A., McDermott, C., Bensabat, J., Class, H., Edlmann, K., Taherdangkoo, R. and Sauter,
M., 2018. Conceptual model development using a generic Features, Events, and Processes (FEP)
database for assessing the potential impact of hydraulic fracturing on groundwater aquifers.
Advances in Geosciences, 45, pp.185-192.