354.3-08.Assignment4

354.3-08.Assignment4 - b For all Parts no other Supplier...

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
CMPT-354 D1 Fall 2008 Database Systems I Instructor: Martin Ester TA: Gustavo Frigo Assignment 4 Total marks: 100 (10 % of the assignments) Due date: October 10, 2008 Assignment 4.1 Consider the following schema of a product database : Parts(pid: integer , pname: string, color: string) Suppliers(sid: integer , sname: string, address: string) Catalog(sid: integer, pid:integer , price: real) The Catalog records that some Supplier sid supplies Part pid at a given price. Formulate each of the following integrity constraints as an SQL assertion: a) No Supplier may supply red and green Parts. (25 marks)
Background image of page 1
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: b) For all Parts, no other Supplier has a lower price than the Supplier with “sid” = 1. (25 marks) Assignment 4.2 Consider again the product database from Assignment 4.1 Formulate the following integrity constraint as a set of SQL triggers: No Supplier may supply red and green Parts. (50 marks) Make sure that you formulate one trigger for each of the DB modifications that can potentially violate the integrity constraint. Your trigger(s) should explicitly undo the database modification that violated the integrity constraint....
View Full Document

{[ snackBarMessage ]}

Ask a homework question - tutors are online