Database Fundamentals Consider the following database schema definition: Product

Question

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 ?

Explanation / Answer

a. Primary keys are the keys which uniquely identifies the relation and as well as it doesn't allows null values.

The primary keya in each relation are :-

Products table :- id

Customers table :- id

Suppliers table :-sid, sup_name

Orders table :- order_id

ItemsPurchased :- order_id, product_id

b. No, id is only enough to identify all the other attributes in this relation. We can identify customer address, telephone and email using only using the id attribute.

C. No, a Candidate is a key which uniquely identifies any database record. Here also only cid is information with this mail.

D. The foreign key is a relation which is used to maintain the same attribute in multiple tables.

Herw in the given scenario the foreign key relations are

Products (supplier) references suppliers (sup_name) ;

Orders (cust_id) references Customers (id) ;

ItemsPurchased (Order_id) references Order (order_id) ;

ItemsPurchased (Product_ID) references Products (id) ;

E. Violation of unique ness means if we give any record to the database then it must be unique and primary keys. The below is the tuple which violate the uniqueness constraint.

Insert into Products values (01,'cd',100, 2,'disc','pk'); if we try to save this same tuple with the same values then the exception is raised.

F.

Insert into ItemsPurchased values (100, 01,2,99.99,198);

Here in the values first we have given 100 as order_id but this id doesn't exist in the Orders table. Because it is having relationship with the Orders table and in order table it doesn't have these value. So it doesn't allows to store in ItemsPurchased table. Which ultimately raises violation of referential intigrity constraint.

G.

insert into Products values ('one', 'dvd', 20,'disc','abcsuplier');

Here we are inserting values into products table. In this we are inserting string type data into integer type attribute. So it doesn't allow this statement and gives domain constraint error.

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.