Assignment_2_FA23_CSGY6083B
docx
keyboard_arrow_up
School
New York University *
*We aren’t endorsed by this school
Course
6083
Subject
English
Date
Jan 9, 2024
Type
docx
Pages
4
Uploaded by DrDugong3665
NYU – TANDON SCHOOL OF ENGINEERING
CS-GY 6083B, Fall 2023
Principles of Database Systems
Assignment: 2 [100 points]
Please submit your assignment to NYU Brightspace with a single PDF document attachment.
Please mention Student ID, Name, Course, Section Number, and date of submission on the
first page of your submission. Insert picture of ERD diagrams in the same PDF document
.
For
each entity in your database design use your initial as prefix, e.g., AP_CUSTOMER where AP
is the initial of the student.
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
.
1
If an individual is an Alumnus, CLEAR will s
tore 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.
2
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.
PERSON:CUSTOMER PLACE:ADDRESS OBJECT:PRODUCT EVENT:ORDER CONCEPT:PRICE,
QUANTITY, ID, NAME
ii) What is the insertion anomaly in this data set?
In the above table, the primary key is a composite key (by design): OrderID + ProductID
the user must supply the values for both of the key fields OrderID and ProductID, however in a
normal business situation, the products are known after the customer order it.
iii) What is the update anomaly in this data set?
If the customer changes his address, we need to update all rows referring to that branch.
iv) What is the deletion anomaly in this data set?
If the user delete the OrderID or ProductID, for example, the customer cancel his order, the
rest of information will be deleted as well.
v) Is this dataset normalized? Why or why not?
No, this dataset isn’t well-normalized. Because it has the update, deletion and insertion
anomaly. Also the data redundancy is present in this dataset.
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.
3
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
4
THi s