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.

SEO Expert for Desklib

Added on 2019-10-18

Related 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

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 3•Continue 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)

End of preview

Want to access all the pages? Upload your documents or become a member.