FA23 CSGY6083B Principles of Database Systems - Assignment 2: CLEAR and FEEL
Problem 1: 50 points
CLEAR ( Continuous Learning 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, CLEAR is developing a database design with following considerations,
CLEAR 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, CLEAR has identified following business requirements:
CLEAR will store only one address, email and contact number for everyone.
An individual may not have an 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, CLEAR 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, CLEAR will store Hire Date, Salary, and Department Name
An employee can be Faculty or Staff but not both. If an employee is a Faculty, CLEAR will store Faculty Rank such as Adjunct Professor, Assistant Professor, Professor etc. If an employee is Staff, CLEAR will store the job title such as Manager, Administrator, Director, Analyst etc.
If an individual is an Alumnus, CLEAR will store the highest Degree (Year, Designation, and Date) of the individual.
An individual can be either Graduate or Undergrad student.
For Graduate students, CLEAR will store graduate major, and undergrad major.
For Undergrad students, CLEAR will store undergrad major and high school diploma year.
Consider EER (Enhance ER), Subtypes and Supertypes relationships, appropriate Completeness and Disjoint 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 1) a screenshot of the logical 2)relational model 3) DDL code 4) any assumptions you have made other than business rules defined by CLEAR.
Problem 2: 50 points
The competition in furniture manufacturing has intensified and competitors seem to progress more rapidly than FEEL (Furniture Excellence Enterprise LLC. ). FEEL manufactures very high-quality furniture and has a high reputation in the market. However, in recent years the sales have fallen drastically and there are many complaints reported by customers about errors in invoices, wrong items delivered or products not available in stock etc. A research team at FEEL has identified the root cause of all issues as their data management system. FEEL maintains all data about customers, orders, and products in a spreadsheet-based file. To address the problems and to make their data management system accurate, consistent, integrated and efficiently manageable, FEEL has decided to migrate its file system-based database to a modern relational database management system.
The following is the sample of the Customer Invoice.
Following is the sample of invoice data FEEL maintains in the spreadsheet file system.
2A) Review the sample Customer Invoice and sample invoice data and answer the following questions. [25points]
i) How many different types of entities (Person/ Place/ Object/ Event/ Concept) are there in customer invoice data? And what are they? Identify entities and give them suitable names.
ii) What is the insertion anomaly in this data set?
iii) What is the update anomaly in this data set?
iv) What is the deletion anomaly in this data set?
v) Is this dataset normalized? Why or why not?
2B) Data Normalization and ERD [25 points]
Normalize this data set and draw ERD of data model with proper relationships amongst entities and attributes, primary and foreign keys for each entity.
Submit:
Submit 1) a screenshot of the logical 2)relational model 3) DDL code 4) any assumptions you have made other than depicted in invoce.
