This a continuation of Project 1.1. In this part of the project you will be exposed to the need to redesign your database as other requirements are added. You will also learn about how to encode business rules and create views and how views can be used to write queries.
Objectives
This assignment supports the following objectives:
• Create a view on a set of tables in a database
• Write functional dependency to encode a given business rule
• Identify when a database is in 3NF (without bringing in derived functional dependencies)
Details
Columbia Deli as described in Project 1.1 has a business rule: All managers get 25% discount on any item purchased from the deli, all supervisors get 20% discount, and all other employees get 15% discount. So, if the database is modified to reflect this business rule, there would be a functional dependency from Status (Manager or Supervisor or Employee) to Discount Level.
What to do:
In a single text file, please complete the following tasks:
• Modify the database of Project 1.1 (or a revised version of it) to encode information about who is eligible for what type of discount so that your revised database design has mechanisms for enforcing this functionality.
• Explain how your database design enforces this functional dependency (and the business rule that it represents) when a new employee (whatever their status) is added to the database? (If your revised database design does not have mechanisms for enforcing this functional dependency, then redo your database design so that it does have this feature.)
• Is your database in 3NF? Justify your answer.
• If your database is not in 3NF, then modify it so that it is in 3NF. Justify your claim that your modified database is in 3NF.
• Does the database of step 5 enforce this functional dependency (and the business rule that it represents) when a new employee (whatever their status) is added to the database? Explain how.
• On the database of step 5 create a view that shows for each employee their name, department, supervisor, and discount level and no other fields. Name this view “emp_discount”.
• Use “emp_discount” to write a query which outputs for each department, and the average discount for all members of the department.
Important Note: Screen shots
Students must also submit screen shots of their Postgres/pgAdmin database interactions. Students submitting without screen shots will lose 20% of the points they earned.
Assessment
Total: 25 points
• Part 1: 6 points
• Part 2: 2 points
• Part 3: 2 points
• Part 4: 3 points
• Part 5: 2 points
• Part 6: 5 points
• Part 7: 5 points