Assignment 1

IMPORTANTINFORMATION

You must submitthe assignmentelectronicallybythe duedate viathe onlinesubmission link on the CIS2002 StudyDesk.

You must usethe officialUSQ datamodelling andnormalization methodology. This methodologyis based onClive Finkelstein’s techniques (Readings 2.1 and 2.2) and allthe examples in thelectures,studybook and the tutorials usethis methodology.

Ifyou do not use theUSQ methodology,you willprobablybeawarded amark of zero.

It is perfectlyacceptableifyou submitneat hand-drawn ERD’s (whichyoucan scanand

paste intoyourassignment).Alternatively,you might wish to useWordoranyother software to draw thediagrams.Ifyou useaCASE or drawingtool,you must adapt thedrawingto conform to the USQmethodologies.

SECTION A(APPLIED DATABASETHEORY) (20marks)

BTC AU is thesecond largest Bitcoin exchange companyin Australia.Whileitfocused on keepingup with its corebusiness in anew andemerging environmentBTC AU paid scant attention to managingtherest of its rapidlyexpandingbusiness operations.Integration ofits business processes withIT became oneof the priorities of BTC AU.

The current approach toitsbackroom data handlingmanagementhas beenad hoc and piecemeal. There was nooverallmap, plan, ormodel guidedapproach tothe evolution of its systems. Records ofpastbusiness performance havebeen hard to elicitfrom thetraditional fileprocessingsystems leavingthe companywith an inabilitytogain insights todrive business planningforward.

TheCIO ofthecompanySamNakamotowants to look at adatabase approach to integrating the various applications currentlyrunningwithin the organizationwith thehopeofultimately usingthe data to supportdecision makingand developingnew insightsformanagement in order togain competitive advantage.

As abusiness consultantto BTC AU,you are asked to develop a short brieftomanagement, in theform of an essay(around1000 words), highlightingthefollowing:

1.   Theexistingandpotentialproblems with thecurrent data processingapproach.

2.   Theneedforadatabaseapproachtosatisfytheirorganizational anddecisionmaking demandsof thecompany.

3.   A suggested databasedevelopmental approach

4.   Theprimaryactivitiesassociatedwith databasedesignprocess.

You are requiredtoextensively researchonthe relevant topics and present concise and workable solution.

Thereis no requirement forformal in-text referencing inthis question.However, it is recommendedyou put a list ofreferences at the endofthe memo showing the published materials that you researchedwhileansweringthis question.

SECTION B (SQL)(40 marks)

Foreach question,marks will beawardedfortheSQLandforthe correct output.

ThefollowingE-R diagram represents a CarHire database.

I_Customer                         I_Booking

I_Car                                  I_Model

I_CarGroup

In this question, you willuse the CAR HIRE database.TheCARHIRE database including appropriate data willbe madeavailable onthe USQ Oracleserver. You mustuse this data.

Ifyou areusingOracle onyour own computerandareunable to accessthe USQ server, email the courseleaderfor ascriptfile that willcreate thetablesfor you.Due tocopyrightissues, you willneedto insert the data yourselfbutyouwillbeprovidedwitha template.

Thetabledescriptions appear below, includingthecolumn names anddata types.

I_CAR

I_CARGROUP

I_MODEL

I_CUSTOMER

I_BOOKING

WriteSQL queriesto solvethe following specifications. Includethe query AND THE OUTPUT.  A screen dumpofthe output is acceptable. Showas many rows as youcan. A screendumpis usually donevia the ALT +PRNT SCRN commandsequence.

Whilethe output helpsto understandyoursolution, youshould notbeanalysing the output ofthe query indetail. As long as youare confident that yourquery corresponds to thequestioncompletely, youroutput may not be significant. Youcan haveanoutput that says NO ROWSFOUND andit could bea perfectly validoutputas long as your query fulfils the requirement.

1.   Displaystructureof the i_modeltable.  Displayall theinformation stored in the

i_model tablebelongingto cargroup‘A4’.(3marks)

2.   Displaythecarregistration, current mileage(miles_to_date column) and purchase date (date_bought column) for all cars. Order bycurrent mileagein descendingorder. (3marks)

3.   Displayallthecarsregistration, miles to date andstatus for allthe cars thathavenot

had aservicebut are available for hire.  Order thelist in ascendingorder ofmiles to date. (3marks)

4.   Displaythe bookingnumber, daterent started, period ofrental and the expected end date ofeach rental (i.e. date_rent_start+rental_period) and sort the outputbythe expected end date in descendingorder.Label theexpected end date ofeachrental as:

‘EXPECTEDEND DATE’.(4marks)

5.   Displayfull details for allthe bookings wherethedistancetravelled during that booking(i.e.miles_in – miles_out) isless than 1000 miles.(3 marks)

6.   Displayrental period, bookingnumber, name oftheperson who took the reservation, the date that therental period starts and where Paid  is set to ‘N’ for allthebookings wherethecar has not been returned.  Listthe resultbythe rental period forthe bookingin descendingorder. (3marks)

7.   Displayfull details for anycar  that: (a) costsmorethan $100,000.00 orthename of the car model starts with theletter‘L’  and  (b) belongstogroup‘A4‘andhas a registration whosesecond digitis 9.(3marks)

8.   Displayfull details forall cargroups belongingto‘A1’, ‘A2’, ‘B1’ or‘B2’AND the

rate per mile is either110 or 120. Find a wayof optimising yourcodeso that the

complete SQLstatementhas onlyONElogical operator (AND, OR, NOT).(4 marks)

9.   Displayfull details for all cars that havenot beenserviced before andhavea current

mileageofgreaterthan 900.(3marks)

10. Displayfull details for anycar  that: (a) costsless than $50,000.00 orthe name ofthe car model starts with theletter‘D’  and  (b)belongs togroup‘B2‘andis currently available. (You should havea single query that completelyfulfils allof the above conditions). (4marks)

11. Displaythe name, town and usual payment method of allcustomers. Orderby payment method ascendingwithin town descending.  Your querymustNOT usethe WHERE clause. (3marks)

12. Displaythe bookingnumber, registration and thename ofthe customer who madethe booking. You mayneed to usetraditional join betweeni_booking and i_customer tables to displayallthe required columns. (4marks)

SECTION C (Data Modelling) (40marks)

PART MARKING:  Wewillonlymark TWO ofthefourquestions belowbutyou must submitanswers to allfour. Wewillchoosethe questions wemark randomlyand mark the same questions for allsubmissions.  Modelanswers will be supplyfor allquestions during the semester.

Construct datamodels for thefollowingspecifications.Include an ERD and alist of relations (entitylist). Yourrelations must show allattributes, primarykeysand foreign keys.You must use theUSQ (Finkelstein) methodologyasdescribed inyour StudyBook, the lectures and the tutorials.

Question1

A questionnaire is a research instrument consistingofaseries of questions.Weneed to develop a model to store questionnaires and its underlyingquestions. A questionnairerecords questionnairename, primarycontact name, start date and expectedend date. A questionnaire mayhavemanyquestions. Each question has aquestion number, question text and a category description forthe question.

Question 2

A mobile phonemodel consistsof anumberof variants (different styles, colours and features) whicharesoldas separate units. Weneed to develop a data model torecord different mobilephonemodelsand their variants. A mobile phonemodelrecords brand name, model name, model number (unique)and startyear.A mobile phonemodel mayhave several variants. Each varianthas a variant code, style, colour,feature,launchyearand discontinuedyear.

Question3

A Bitcoin wallet managesyourBitcoins. A walletdoes not storeBitcoins but holds the private keys of theownerof thewallet that allow him/herto access his/herbitcoin addresses and to sign transactions to allow theowner to spend his/herfunds.

Question4

An event coordinatormaybeworkingon anumber of events over time. Anevent can have multiple event coordinators workingon itbut will onlyhaveasingle event manager.Foran event coordinator, westoreauniquecoordinator identification, his/herofficenumberand areaof specialty. We also need to storethe date the event coordinator commenced workon the event and thename of the event, theevent manager’s name and other relevant event details.

Preparethefollowing forall fourquestions:

1    An ER diagram: Showallentities,relationships, cardinalities and optionalities.

Also, include allintersection entities but do not create entities that arenot specifically covered bythespecification.You must use theFinkelsteinmethodologyas perthe study bookandtutorials.

2    A list ofrelations(entity list). Produce completerelations for allentities and attributes. Show allprimaryand foreign keys.Include allattributes that are specificallymentioned and allkeyattributes. Youmayneed to create primaryand foreign keys that arenotspecificallymentionedbut do notcreateanyotheradditional attributes.