Ask a question to Desklib · AI bot


SEO Expert for Desklib

5 Pages986 Words216 Views

Added on  2019-10-18

About This Document

This page provides information about the foreign keys in the given relations, and answers questions related to entity and referential integrity. It also includes SQL queries for retrieving data from the database.
BookmarkShareRelated Documents
QUESTION 2The following relations form part of a database held in a relational DBMS:Product (productNo, productName, price, quantity, supplierNo): productNo is a primary key.productNoproductNamepricequantitysupplierNos1A4 paper$4050s1s2B1 paper$4540s2s3Legal Paper$10050s3Supplier (supplierNo, SupplierName, Address, Contact): supplierNo is a primary key.supplierNoSupplierNameAddressContacts1BMCC199 Chamber St, NYC, NYDon Weis2Hunter695 Park AveNew York, NY 10065Jochen Albrets3City Tech300 Jay St, Brooklyn, NY 11201Hong LiOrder (orderNo,orderDate, paymentMethod): orderNo is a primary key.orderNoorderDatepaymentMethodCustomero11/1/2015CashTina Smitho21/2/2015CashEric Nicholaso33/3/2015CreditMary DavisPurchase (orderNo,productNo, quantity): (orderNo,productNo)is a primary key.orderNoproductNoquantityo1p15o1p24o2p110
SEO Expert for Desklib_1
Question 2 contIdentify and describe all the foreign keys for the above relations.(15pts)Note:Foreign Key: Attribute, or set of attributes, within one relation that matches candidatekey (or primary key) of some (possibly same) relation.There are three foreign keys.You should describe the foreign key in the format of "The foreign keyattributeNameinrelationnamereferences to theattributeNamein relation name."QUESTION 3Continue from question 1 to answer the following questions. Be noted that those question are independent and NOT a sequence of operations.Entity Integrity: In a base relation, noattribute of a primary key can be null.Referential Integrity: Ifforeign key exists in a relation, either foreign key value must match a candidate key value of some tuple in its homerelation or foreign key value must be wholly null.Of course, primary key values can't be duplicated.a) If one tries to insert a new row intoProductrelation with ('p3', ‘Red Pencil’, 30, 100, ‘s2’). Does this insertion operation cause violation of any integrity constraint? Why? (5 pts)b) If one tries to insert a new row intoOrderrelation with ('o4', '1/1/2015', 'Cash', ‘Tina Smith’). Does this insertion operation cause any violation of any integrity rule? Why? (5 pts)(c) If one tries to insert a new row into Purchase relation with ('o4','p2', 5). Does this insertion operation cause any violation of any integrity rule? Why? (5 pts)
SEO Expert for Desklib_2

Found this document preview useful?