Database Queries and Management
Added on 2019-09-23
17 Pages4197 Words396 Views
|
|
|
UL18/0464Page1of14THISPAPERISNOTTOBEREMOVEDFROMTHEEXAMINATIONHALLS
![Database Queries and Management_1](/_next/image/?url=https%3A%2F%2Fdesklib.com%2Fmedia%2Fimages%2F5feac3b7c16f461eb4d5bc468747ebe3.jpg&w=3840&q=10)
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](/_next/image/?url=https%3A%2F%2Fdesklib.com%2Fmedia%2Fimages%2F88c2ee5920f34a63a5b7090b347ef95e.jpg&w=3840&q=10)
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](/_next/image/?url=https%3A%2F%2Fdesklib.com%2Fmedia%2Fimages%2Fae333fc0caff4f65ba3aecde8fa0e028.jpg&w=3840&q=10)
UL18/0464Page 3of14intransit...*/--3UPDATE CentralWarehouseSETIn-Transit =In-Transit-100 WHERE TexCode='BA068';
![Database Queries and Management_4](/_next/image/?url=https%3A%2F%2Fdesklib.com%2Fmedia%2Fimages%2F29c6f868b4b2426eb0b676ff02f9e875.jpg&w=3840&q=10)
End of preview
Want to access all the pages? Upload your documents or become a member.