logo

Database Queries and Management

   

Added on  2019-09-23

17 Pages4197 Words396 Views
 | 
 | 
 | 
UL18/0464Page1of14THISPAPERISNOTTOBEREMOVEDFROMTHEEXAMINATIONHALLS
Database Queries and Management_1

Question1Acompanyhasalargecentralwarehousenearaportcity,wherebulkshipmentsoftextilesarebroughtbyship.Fromtimetotimeshipmentsoftextilesaresent outtolocalcentresindifferentpartsofthecountry,wheretheyareusedtomakegarments.Thecompany'sdatabasehastableswhichrecordallthevariouskindsoftextilewhichareinthecentralwarehouse,andalsothe textileswhichareinitslocal centresaroundthecountry.Inresponsetothearrival ofashipmentoftextilesatthecentralwarehouse,thedatabaseisupdatedtoreflecttheincreaseinthekindoftextilewhichhascomein.Whentextilesareshippedoutofthewarehousetolocalcentres,thewarehousetableisupdatedtoreflectthedecreaseintheamountoftextileheldthere.Thetextilesarelistedas'intransit'.When thetextilesarriveatalocalcentre,thetableswhichrecordhowmuchtextileeachlocalcentreisstockingareupdatedtoreflectacorrespondingincrease.Thus, thecompanyalwaysknowshowmanytextilesofeachtypeithas,andwheretheyarelocated.(1)Anewshipmentof severalkindsoftextilearrives.Thecentralwarehousetableisupdated:eachtuplecorrespondingtoaparticularkindoftextilehasits'On-Hand'fieldincreasedbytheamountofthattypeoftextilewhichhasarrived.(2)Whenalocalcentrerequestsashipmentofacertain quantityofacertainkindoftextilefromthe centralwarehouse,thattextile'stupleisaccessed,the'On-Hand'fieldisdecreasedbytheshipmentamount,andthe'In-Transit'fieldisincreasedbythesameamount.Whenthereceivinglocalcentreconfirmsthat theshipmenthasarrived,thevalueofthe'In-Transit'fieldisdecreasedandthe'On-Hand'fieldinthatkindoftextile'stupleinthe'LocalCentre'tableisupdatedtoshowtheincrease.Asampleoftherelevantattributesoftherelationsmightlooklikethis:CentralWarehousePrimaryKey:TexCode
Database Queries and Management_2

UL18/0464Page 3of14LocalCentrePrimaryKey:CentreNum+TexCodeHereisatypicalsetof SQLstatements,labelled--1to--7,thatthe company'sDatabaseAdministratormightenterduringthecourseofaday,tokeepthe databaseuptodate.I*Atelephonecallfrom theUnloadingDocktellsusthat500metresofDR888 hasjustbeenunloadedandinspectedandisbeingstoredintheCentralWarehouseUpdatethe'OnHand'fieldattheCentralwarehousetoshow this*/--1UPDATECentralWarehouseSETOn-Hand=On-Hand+500WHERETexCode ='DR888';I*Wereceiveanotefrom theShippingDepartmentinformingusthat100metresofGA612hasbeenloadedontotrucksfordeliverytoourlocalcentresinotherpartsofthecountry.Updatethe'OnHand'and 'InTransit'fieldsoftheCentralWarehousetoreflect thischange.*/--2UPDATECentralWarehouseSETOnHand=On-Hand-100,In-Transit=ln-Transit+100WHERETexCode='GA612';/*Anemailtellsusthat100metresofBA068hasarrivedatoneofthelocalcentres,453,towhichitwasshipped.Adjustthe databaseaccordingly.Updatethe 'In-Transit'fieldoftheCentralWarehouse,toindicatethat100metresofthisfabricisno longer
Database Queries and Management_3

UL18/0464Page 3of14intransit...*/--3UPDATE CentralWarehouseSETIn-Transit =In-Transit-100 WHERE TexCode='BA068';
Database Queries and Management_4

End of preview

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