Database Fundamentals

Consider the following database schema definition: Products(id: integer, title: string, price: float, quantity: integer, category: string, supplier: string) Customers(id: integer, cname: string, address: string, telephone: string, email: string) Suppliers(sid: integer, sup_name: string, address: string, city: string, province: string, postal_code: string) Orders(order_id, integer, cust_id: integer, order_date: string, order_status: string, total: float) ItemsPurchased(order_id: integer, product_id: integer, quantity: integer, price: float, total: float) This schema represents an online store. The relations and attributes are self-explanatory. The Products table consists of products offered by the store; Customers table contains records of customers who are registered with the online store; Suppliers contains records of the distributors or suppliers for the products; Orders relation stores information about each order processed, keeps track of the customer associated with any order (cid refers to the customer id) and refers to the items purchased through a relationship defined using the order_id; ItemsPurchased contains list of items purchased with each order.

Notes: Every product in the Products relation has a unique value for the id attribute Every customer in the Customers relation has a unique value for the id attribute Every supplier in the Suppliers relation has a unique value for the sid attribute Every order in the Orders relation has a unique value for the order_id attribute The Orders relation captures the relationships between customers and the products. Another relationship that captures the order information for a customer and products purchased is defined by ItemsPurchased.

Example: Each order may contain one or more purchased items. That is, an order may have one or more item entries in the ItemsPurchased table. The attribute order_id in the ItemsPurchased table can be repeated multiple times but only one order_id can exist in the Orders table. For example, a customer ordered two items in an order number 8839. The items are: (1) Laptop cooling pad (product id: 12243259 and (2) iPhone 4s screen protector (product id: 45243211. The following records will appear in the ItemsPurchased table: 8839 12243259 1 39.99 39.99 8839 45243211 2 7.99 15.98.

Exercise 1: Given these relations, answer the following questions:

a. Select a primary key(s) for each of these relations?

b. Is the condition (id, cname) a key for the Customer relation?

c. Is the condition (id, cname) a candidate key for the Customer relation?

d. Define all possible foreign keys for the relations.?

e. Give an example of a tuple that the DBMS would reject because it would violate a uniqueness constraint?

f. Give an example of a tuple that the DBMS would reject because it would violate a referential integrity constraint?

g. Give an example of a tuple that the DBMS would reject because it would violate a domain constraint ?