Need help with Database assignment help or Project Help? At Codersarts we offer 1:1 session with expert, Code mentorship, Course Training, and ongoing development projects. Get help from vetted Machine Learning engineers, mentors, experts, and tutors.
Problem 1:
DEAR (Department of Education and Administration Research) of a university has undertaken a project to consolidate people’s data from Human Resources and Students Affair departments. In this effort, DEAR is developing a database design with following considerations,
DEAR intends to keep the following details of each person associated with the university
Name, Address, Gender, Age, Email, Contact Number, SSN
In addition to it, DEAR has identified following business requirements: DEAR will store only one address, email and contact number for each individual. An individual may not have SSN An individual can be either Employee, Alumnus or Student. Also, an employee can be a student as well. If an individual is an Employee or Student, DEAR will store a unique Employee ID or Student ID respectively. If a Student is an Employee as well, he/she will have a unique Student ID and Employee ID.
If an individual is Employee, DEAR will store Hire Date, Salary, and Department Name An employee can be Faculty or Staff but not both. If an employee is a Faculty, DEAR will store Faculty Rank such as Adjunct Professor, Assistant Professor, Professor etc. If an employee is Staff, DEAR will store the job title such as Manager, Administrator, Director, Analyst etc.
If an individual is an Alumnus, DEAR will store the highest Degree (Year, Designation, and Date) of the individual. CS-GY 6083 - B, FALL 2021 Principles of Database Systems 2 | Page
An individual can be either Graduate or Undergrad student.
For Graduate student, DEAR will store graduate major, and undergrad major
For Undergrad student, DEAR will store undergrad major and high school diploma year
Consider EER (Enhance ER), Subtypes and Supertypes, appropriate Completeness constraints, and Disjointness constraints. Identify proper relationships among entities. Resolve composite/derived/multi-valued attributes, if any. For each attribute identify appropriate data type, size, and constraints (Mandatory/Optional). Identify proper Primary Keys and Foreign Keys. Using Oracle Data Modeler, draw EER (Logical and Relational Model). Submit a screenshot of the logical and relational model along with any assumptions you have made other than business rules defined by DEAR.
2) Data Normalization and ERD
Normalize this data set to normalized form and draw ERD of data model with proper relationships amongst entities and attributes, primary and foreign keys for each entity.
Comments