Database Management System for a Maintenance Business

17 Pages4197 Words396 Views

Added on 2019-09-23

About This Document

This article discusses the database management system for a small business that does general maintenance work on rented flats. It covers topics such as primary keys, entity-relationship diagrams, indexing, and more.

Database Management System for a Maintenance Business

Added on 2019-09-23

Related Documents

UL18/0464Page1of14THISPAPERISNOTTOBEREMOVEDFROMTHEEXAMINATIONHALLS

Database Management System for a Maintenance Business_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 Management System for a Maintenance Business_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 Management System for a Maintenance Business_3

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

Database Management System for a Maintenance Business_4

End of preview

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