cosc2307 Assignment 4 Solution

COSC 2307 Assignment 4

Question 1 –SQL (Constraints) (50 marks):
For this problem you will recreate your database schema, adding specifications for keys, referential integrity, and other constraints.
1.1) Modify your TABLE statement from assignment 3 as follows (15 marks). For each relation in your schema from assignment 3, if the relation has one or more keys then modify the CREATE TABLE statement to declare one PRIMARY KEY and to declare all other keys as UNIQUE.
For each referential integrity constraint that should hold in your schema, specify the constraint using a REFERENCES clause within the appropriate CREATE TABLE statement.
You may use the default option for handling referential integrity violations (violations will generate an error). We expect that everyone’s PDA should include at least one referential integrity constraint. Add at least two attribute-based CHECK constraints to relations of your database schema.
1.2) You don’t necessarily need to modify your program for generating data if it creates violations. Write data modification commands to illustrate the following seven scenarios (35 marks – each 5 marks): a) An INSERT command creating a key violation b) An UPDATE command creating a key violation c) An INSERT command creating a referential integrity violation d) A DELETE command creating a referential integrity violation e) An UPDATE command creating a referential integrity violation f) An INSERT command creating a CHECK constraint violation g) An UPDATE command creating a CHECK constraint violation
Turn in a copy of all of your SQL commands, along with a script illustrating their execution (by using paste your terminal output and save it in a file or any other way). Your script should be sufficient to convince us that your commands run successfully.
Question 2- Database design - normalization (50 marks)
Write the table schema for each case and underline the primary key. Provide the normalization steps as we do in class. If a higher level form is the same as previous one, state the reason (each table carries 25 marks):
User View 1 - Price Update List
Department Product Code
Aisle Number
Price Unit of Measure Produce 4081 1 0.35 lb Produce 4027 1 0.90 ea Produce 4108 1 1.99 lb Butcher 331100 5 1.50 lb Butcher 331105 5 2.40 lb Butcher 332110 5 5.00 lb Freezer 411100 6 1.00 ea Freezer 521101 6 1.00 ea Freezer 866503 6 5.00 ea Freezer 866504 6 5.00 ea
This report is used by the department managers to update the prices that are displayed in the grocery store for these products.
UNF
1NF
2NF
3NF

Good News Grocers
User View 2: Product Cost Report
Supplier Product
Cost Markup Price Dept Code 21 – Very Veggie 4108 – tomatoes, plum 1.89 5% 1.99 PR 32 – Fab Fruits 4081 – bananas 0.20 75% 0.35 PR 32 – Fab Fruits 4027 – grapefruit 0.45 100% 0.90 PR 32 – Fab Fruits 4851 – celery 1.00 100% 2.00 PR 08 – Meats R Us 331100 – chicken wings 0.50 300% 1.50 BU 08 – Meats R Us 331105 – lean ground beef 0.60 400% 2.40 BU 08 – Meats R Us 332110 – boneless chicken breasts 2.50 100% 5.00 BU 10 – Jerry’s Juice 411100 – orange juice 0.25 400% 1.00 FR 10 – Jerry’s Juice 521101 – apple juice 0.25 400% 1.00 FR 45 – Icey Creams 866503 – vanilla ice cream 2.50 100% 5.00 FR 45 – Icey Creams 866504 – chocolate ice cream 2.50 100% 5.00 FR
This report is used by the grocery store manager to determine the final selling price of his products.
UNF
1NF
2NF
3NF
Powered by