QUESTION 2. The following relations form part of a data

Added on - Oct 2019

Trusted by 2+ million users,
1000+ happy students everyday
Showing pages 1 to 2 of 5 pages
QUESTION 2The following relations form part of a database held in arelational 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, NYDonWeis2Hunter695 Park AveNew York, NY 10065JochenAlbrets3City Tech300 Jay St, Brooklyn, NY11201HongLiOrder (orderNo,orderDate, paymentMethod):orderNois aprimary 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 onerelation that matches candidatekey (or primary key) ofsome (possibly same) relation.There are three foreign keys.You should describe the foreign key in the format of"Theforeign keyattributeNameinrelationnamereferences totheattributeNameinrelation name."QUESTION 3Continue from question 1 to answer the following questions.Be noted that those question are independent andNOTasequence 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 valuemust 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 causeviolation 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 operationcause 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 anyintegrity rule? Why? (5 pts)
Desklib Logo
You are reading a preview
Upload your documents to download or

Become a Desklib member to get access