College Wants To Keep Track Of History Of Assignments
Assignmenttiny College Wants To Keep Track Of The History Of All Admin
Assignment Tiny College wants to keep track of the history of all administrative appointments (date of appointment and date of termination). (Hint: Time variant data are at work.) The Tiny College chancellor may want to know how many deans worked in the College of Business between January 1, 1960 and January 1, 2010 or who the dean of the College of Education was in 1990. Given that information, create the complete ERD containing all primary keys, foreign keys, and main attributes. Here are the three entities referenced in the problem; the employee, the school and the professor. The professor is assigned to a school and as an employee, a professor is one of three employee types. A professor could also hold a staff or admin position as well as being a professor. The professor/school relationship where a professor could be a school dean are the entities for which current data is kept.
Paper For Above instruction
The objective of this paper is to design a comprehensive Entity-Relationship Diagram (ERD) for Tiny College that effectively models the historical administrative appointments, capturing time variant data, and representing the complex relationships among employees, schools, and professors. The ERD aims to facilitate queries like identifying all deans of a specific college within a particular period and tracking the tenure of administrative personnel, including professors, staff, and administrators.
Introduction
In managing institutional data, especially those involving appointments and roles that change over time, it is crucial to employ a data model capable of capturing historical data and the dynamics of relationships. The ERD developed for Tiny College addresses these needs by incorporating time-variant data modeling techniques alongside entities that represent employees, schools, and the different roles held by employees. This structure not only supports current data storage but also enables historical queries, such as identifying who held a specific role at a particular period.
Entities and Attributes
1. Employee: This entity captures fundamental employee details, including the employee ID (primary key), name, contact information, and employee type. Employee types include professor, staff, and admin, which can be represented via a discriminator attribute or separate subtype entities.
2. School: This entity represents the colleges or schools within Tiny College. It includes attributes such as school ID (primary key), name, location, and possibly other descriptive attributes.
3. Role (or Position): To model different roles held by employees, especially focusing on administrative and professorial roles. Attributes include role ID (primary key), role name (e.g., Dean, Professor, Staff), and descriptions.
Relationships
- Assignment of Employee to Roles: A many-to-many relationship exists between Employee and Role, mediated through an associative entity called "EmploymentHistory" or "AssignmentHistory," which records the start date and end date of each role held by an employee, capturing the time-variant nature of appointments.
- Employee and School: Employees, particularly professors or deans, are assigned to specific schools and roles, which is represented by an associative entity linking Employee, School, and Role, along with time period attributes.
- Dean of a School: To identify the dean of a particular school at a given time, the ERD models the association between Employee and School with a role of 'Dean' and includes the appointment periods.
Time-Variant Data Modeling
To support historical queries about appointments, the associative entities (e.g., EmploymentHistory) store the appointment's start and end dates, thereby enabling the system to determine who was dean at any given time, for example, between January 1, 1960, and January 1, 2010.
Primary Keys and Foreign Keys
- Employee: EmployeeID (PK)
- School: SchoolID (PK)
- Role: RoleID (PK)
- EmploymentHistory: EmploymentID (PK), EmployeeID (FK), SchoolID (FK), RoleID (FK), StartDate, EndDate
Conclusion
The ERD thus constructed allows Tiny College to maintain detailed, historical records of administrative appointments and roles, supporting complex temporal queries such as identifying all deans within a period or pinpointing the dean in 1990. It provides a scalable, robust structure to support administrative decision-making and historical research.