Assignment 1

Need your ASSIGNMENT done? Use our essay writing service to score better and meet your deadline.


Order a Similar Paper Order a Different Paper

 

 

 

 

I MP ORTAN TI N F ORMAT I ON

 

You must submitthe a ssi g nmente l ec t r on icallyb ythe dueda te viath e onli nesubmiss ion li nk on the C I S 2002 S tudyD e sk.

 

You must usethe of fic ialUSQ da tamodelli ng a ndnorma liz a ti on methodol og y . This methodolog yis base d onCli ve F inkelste in ’ s tec hni que s (Rea din g s 2.1 a nd 2 .2) a nd a llthe e x a mpl e s in t helec ture s,stud ybook a nd the tutori a ls us ethi s methodolog y .

 

I fy ou do not use theUS Q me thodol og y ,y ou willproba bl ybea wa rd e d amar k of ze ro.

 

 

I t i s pe r fec tl yac c e ptableify ou submitnea t hand – dra wn E RD ’ s ( whic hy ouca n sca na nd

pa ste intoy oura ssi g nm e nt).Alter na ti ve l y ,y ou mi g ht wish to useW ordora n yoth e r sof tw a re to dra w thedia g r a ms .I fy o u useaCASE or dr a wingtool ,y ou must ada pt t hedra win gto c onfor m t o the USQmethodolog ies.

 

 

 

 

S ECTIO N A(AP P LIE D DA TABASETHE O RY ) (20m a r k s)

 

B TC AU is these c ond la rge st B it c oin ex c ha n g e c ompan yin Austr a li a .W hil eitfoc use d on kee pin gup with i ts cor ebusine ss i n ane w a nde mer g ing e nvironmen tB TC AU pa id sca nt a tt e nti on to m a na g in gthere st of it s ra pidl ye x pa ndingbusine s s ope r a ti ons.Inte g r a ti on ofit s busine ss proce sses w it hIT beca me on eof the p rior it ies of B TC AU.

 

The c urr e nt appr o ac h toi tsbac kroom da ta h a ndli ngman a g e mentha s b ee na d hoc a nd piece me a l. The re wa s noove ra llmap, pla n, ormo de l g uideda pproac h tothe e volut ion of its s y stems. R ec o rds ofpa stbusine ss per for man c e h a vebee n h a rd to e li c itfr o m t hetra dit ional fileproc e ssi n gs y stems l ea vin gthe c ompan ywith an ina bil it ytog a in i nsights t odrive busine ss pl a nningfo rwa rd .

 

TheC I O ofthec ompan yS a mNa ka mot owa nts t o look at ada taba s e a ppro ac h to i nte g ra ti n g the va rious a ppli ca ti ons c urre ntl yrunn in gwithi n the or g a niz a ti onwith t hehopeofult im a tel y usingthe da ta to supportdec isi on makinga nd de v e lopi ngne w insig htsformana g e ment in orde r tog a i n c omp e ti ti ve a dva nta g e .

 

As abusine ss consultantto B TC AU ,y ou a r e a sk e d to deve lop a shor t brie ftomana ge ment, in t hefor m of a n e ss a y(around1000 wor ds), hi g hl ig hti ngth efollowing :

 

1 .    Thee xistingandpo te n tialp ro b lem s w i th thecu rren t d ata pro c essi n gapp ro a ch.

2 .    Then ee dfo rad ataba s eap p ro acht osa tisfyt h eiro rg a n iz atio n al an ddecisi o nm a kin g d em and so f t h ec o m p an y .

3 .    A sugg est ed da t ab ased e v e lo pm ental app r o ach

4 .    Thep rim arya cti v itiesass o ciatedwith datab as ed esig nproces s .

 

 

 

You are re qu ire dtoe xten sively re se ar c honthe r e levant t op ics and p r e se n t c on c ise and w or k ab le sol u tion.

 

Th er eis n o re qu ire m e n t f orf or m al i n – te x t r e fe re n c in g inthis q u e stion .How e ver , it is rec o mme nd e dyou pu t a list ofre f ere n ce s at t h e e n dofthe m e m o sh owin g the pu b li sh e d m a te r ial s that you r e se arc h e dw h il ean s w er in gthis qu e stion .

 

 

 

 

S ECTIO N B (SQ L)( 40 m a r ks )

 

F ore ac h qu e stion ,m a rk s w i ll b ea w ar d e df ort h eS QLan df orthe c orrec t outpu t.

 

 

 

ThefollowingE – R dia g r a m re pr e se nts a CarHire da taba se .

 

 

 

 

 

I_ Cus to m er                          I_B o o k in g

 

 

 

 

I_ C ar                                   I_ Mo d el

 

 

 

 

 

I_ C ar Gro u p

 

 

 

 

I n t hi s que s t i on, y ou w i l lus e t he CA R H I R E dat abas e.T heCA RH I R E dat abas e i n cl ud i ng appr op r i at e da t a wi l lbe m adeav ai l abl e ont he US Q O r acl ese r v er . Y ou m ustus e t hi s d at a.

 

I fyou a reu si ngO ra cl e onyour o w n co m pu t erandareun ab l e t o acc es st he U SQ s e rve r , e m ai l t he cou rs el ead erf or asc r i ptf i l e t hat w il lcr eat e t het abl esf or you.D ue t ocop yri gh ti ss ue s, you w i l ln eedt o i n se r t t h e dat a yours el fbu ty ouwi l lbeprovi d edwit ha t em p l at e.

 

Thetablede s c riptions a p pea r be low, in c ludi ngthec olum n na mes a ndda ta t y p e s .

 

 

I _C A R

 

C ol u m n N a m e

Ty p ea n dS i z e

C o n st ra i nt s

D escr i pt io n

R e g i st r at i o n

V A RCH A R2 ( 7 )

NO TN U L L

R e g i st r at i o n n u m b e r o f th ec ar .T h i s i s t h e Pr i maryk e y .

M o d e l _ n ame

V A RCH A R2 ( 8 )

FK

M o d e l fo r th ec ar .F or e i g nk e yint o th eM o d e l s t a b l e .

C ar _ group _ n ame

V A RCH A R2 ( 2 )

FK G r o u pco d ed e f i ni n g t y p e o f car a n dr e nt alco st .F or e i g nk e yint o

th eC ar G ro up s t ab l e .

D at e _b o u gh t

DA T E

 

D at e t h ecar w aspu r c h ase d .

C o st

NU MB E R (8 ,2 )

 

T h e or i g in alco st o f t h ecar .

Mi l e s_t o _ d at e

NU MB E R ( 6 )

 T h ecu rr e n tm i l e ag e o f th ecaras re ad at t h ee n do fth emo s tr e ce n t

r e nt al .

Mi l e s_ l ast _ se rv i ce

NU MB E R ( 6 )

 

T h emil e ag eo f th ecar w h e ni tw as l astse rv i ce d .

S t at u s

C H A R ( 1 )

 T h ecu rr e n ts t a tu so f t h ecar . ‘ A ’ fo r a v ai l ab l e ,‘H ’ fo r o nh i r e ,‘S ’

fo r b e in g se rv i ce d ,‘X ’ fo r i nn ee do f se rv i ce o r r e p ai r .

 

 

 

 

I _C A R G R OU P

 

C ol u m n N a m e

Ty p ea n d S i z e

C o n st ra i nt s

D escr i pt io n

C ar _ group _ n ame

V A RCH A R2 ( 2 )

NO TN U L L T h ecar gro u pco d e .T h i s w i l lb e o n e o f th e foll o w in g v al u e s:‘ A 1 ’ ,

‘ A 2 ’ , ‘ A 3 ’ ,‘ A 4 ’ ,‘ B1 ’ , ‘ B 2 ’ ,‘ B3 ’ , o r ‘ B4 ’ .T h i scol u mni st h e

p r i maryk e yfo r thi s t ab l e .

R at e _ p e r _ m i l e

NU MB E R ( 3 )

 

T h ech arg ep e r mil e fo r car s i n t hi s g r o u pi n ce n t s.

R at e _ p e r_ d ay

NU MB E R (5 ,2 )

 

T h e r e nt alch arg ep e r d ayfo r car s i n t hi sgro u pi n d oll ar s a n d ce n t s.

 

 

 

I _M OD E L

 

C ol u m n N a m e

Ty p ea n d S i z e

C o n st ra i nt s

D escr i pt io n

M o d e l _ n ame

V A RCH A R2 ( 8 )

NO TN U L L

T h emo d e ln ame ,a na b b r e v i ati o no f t h e f u l lmo d e ln ame .T h i s i s th ep r i maryk e yf o r thi st ab l e .

C ar _ group _ n ame

V A RCH A R2 ( 2 )

FK

T h e gro u pt o w hi c ht h i s mo d e l o f carb e lo n g s.

D e scrip t i o n

V A RCH A R2 (3 0 )

 

F u l ld e scripti o no f t h emo d e l .

M a i n t _i n t

NU MB E R ( 5 )

 

N u mb e r o f mil e s b e t w ee nse rv i ce s fo rt h i s mo d e l .

 

 

I _C US T O M ER

 

C ol u m n N a m e

Ty p ea n d S i z e

C o n st ra i nt s

D escr i pt io n

Cu s t _ n o

NU MB E R ( 5 )

NO TN U L L

T h ecu st o me r ac cou n t n u m b e r .T hi si sth ep r i m ar yk eyfo r thi st a b l e .

Cu s t _ n a me

V A RCH A R2 (2 0 )

NO TN U L L

T h en ame o f t h ecu s t o me r .

A dd r e ss

V A RCH A R2 (2 0 )

 

S t r ee tad d r e sso f th ec u st o me r .

T o w n

V A RCH A R2 (2 0 )

 

T o w nth ec u st o me r l i v e s in .

C ou nt y

V A RCH A R2 (2 0 )

 

C ou nt yth ec u st o me r l i v e s i n .D e f au l ti s A u st r al i a

Po st_ co d e

V A RCH A R2 (1 0 )

 

Po st co d e fo r th et o w n .

C o n t ac t

V A RCH A R2 (2 0 )

 

N ame o f p e r so nt o co n t act .

P ay _ me th o d

C H A R ( 1 )

 C o d et oi n d i ca t e t h eu s u alp ay me n tm e th o dfo r t hi s c u st o me r .‘ A ’

i n d i ca t e s a n a ccou n t ,‘ C ’i n d i c at e s c as ho r cr e d i tcar d ,NU L L

i n d i ca t e s u n k n o w n .

 

 

 

I _B OO K I N G

 

C ol u m n N a m e

Ty p ea n d S i z e

C o n st ra i nt s

D escr i pt io n

B oo k i n g_ n o

NU MB E R ( 5 )

NO TN U L L

Aserial n u mb e r u se dt ou n iq u e l yid e n ti f yth eb ook in g .T hi si st h e p r i maryk e yfo r thi s t ab l e .

Cu s t _ n o

NU MB E R ( 5 )

FK

Cu s t o me r n u mb e r o f t h ecu st o me r ma k i n g t h eb ook in g .

D at e _ r e se r v e d

DA T E

 

D at e o nw h i ch t h eb ook in g w as ma d e .

R e se r v e d_b y

V A RCH A R2 (1 2 )

 

N ame o f th e p e r so nw h o t oo k t h e r e serv ati o n .

D at e _ r e n t _ st ar t

DA T E

 

D at e o nw h i ch t h e r e nt alco mm e n ce s.

R e nt al _p e r i o d

NU MB E R ( 3 )

 

Le n g t ho f r e nt alp e r i o di nd ay s.

R e g i st r at i o n

V A RCH A R2 ( 7 )

FK

R e g i st r at i o no fth ecar a ct u all yr e nt e d .

M o d e l _ n ame

V A RCH A R2 ( 8 )

 

M o d e l o f th ec ar r e nt e d .

Mi l e s_ ou t

NU MB E R ( 6 )

 

Mi l e s o nth e o d o me t e r at t h est ar to f t h e r e nt al .

Mi l e s_ i n

NU MB E R ( 6 )

 

Mi l e s o nth e o d o me t e r at t h ee n d o f th e r e nt al .

A mo unt _ du e

NU MB E R (6 ,2 )

 

C o sto f t h e r e nt al .C al cu l at e dw h e nth ecar i sr e tu r n e d .

P ai d

C H A R ( 1 )

 Fl ag t o i nd i ca t ei ft h i s re n t al h asb ee np a i d for .‘Y ’ i f i th asb ee np a i d

an d‘N ’ i fn ot .

 

 

 

 

Wr iteS QL qu er iesto solvethe f oll owin g sp ec i fic ation s. In c lud ethe quer y AN D THE OUTP UT.  A sc ree n du m pofthe o u tpu t is acc ep table . Sh o was m an y r ow s as youc an . A scr e e nd u m pis u su all y d on evia the A LT +P RN T S CR N c o m m an ds e qu e n ce .

 

Wh il ethe o u tpu t helpsto und er standyou rsolu tion, yoush ou ld n otb ean alysin g the ou tpu t ofthe q u er y inde tail. As lon g as youar e c onf id e n t t h at yo u rq uer y c o rre sp ond s to t h equ e stionc o m p let e ly, you rou tpu t m ay n o t be sign if icant. Y ouc a n h aveanou tp u t that says NO ROWSFOUN D andit c ou ld b ea p er f ec tly validou tpu tas lon g as you r qu er y fu l f il s t h e re qu ireme n t.

 

1.   Displa ystru c tureof the i _mode ltable .  Displa ya l l t heinfor mation st ore d in t he

 

i_mode l tablebe lon g in gto ca rg roup‘A 4’.( 3m ark s)

 

2.   Displa ytheca rre g ist ra ti on, c urre nt m il e a ge( mile s_to_date c olum n) a nd p urc ha se da te ( date_bought c olum n) f or a ll c a rs. O rd e r b yc urre nt m il e a g ein d e sce n dingorder. (3m ar k s)

3.   Displa ya lltheca rsreg ist ra ti on, mi les to da te a ndstatus for a llthe c a rs tha tha venot

 

ha d ase rvi c ebut ar e a v a il a ble f or hire .   Or de r theli st i n a sce ndingord e r ofmi les to da te. (3m a r k s)

4.   Displa ythe bookin gnum be r, da tere nt st a rte d, p e ri od ofre ntal a nd th e e x pec ted e nd da te ofe ac h r e ntal ( i.e. d ate_re nt_st art+re ntal _pe riod ) a nd sort the outputb ythe e x pec ted e nd da te in d e sc e ndingor de r.L a be l t hee x pec ted e nd da te ofeac hre ntal a s:

‘E XPEC TEDEN D D AT E’ .(4m ar k s)

 

5.   Displa yfull de t a il s for a l lthe booking s whe rethedist a nc etra v e ll e d dur in g that booking(i.e .miles_in – mi les_out) isless than 1000 mil e s.(3 m a r k s)

6.   Displa yr e ntal pe riod, bo okingnumber , n a me oft hepe rson w ho took t he r e se rva ti on, the da te tha t t her e ntal pe riod star ts and w he re Pai d  is s e t t o ‘N ’ f or a llthebooking s whe rethec a r ha s not be e n re turne d.   L istthe r e sultb ythe r e ntal pe riod f o rthe bookingin desc e ndingo r de r. ( 3m a r k s)

7.   Displa yfull de t a il s for a n yc a r  that: ( a ) c ostsmorethan $100,000.00 orthena me of the ca r mode l sta rts with t heletter‘ L ’   a nd  (b ) b e l ong stog roup‘ A4‘an dha s a reg ist ra ti on whosese c on d dig itis 9.( 3m ar k s)

8.   Displa yfull de t a il s foral l ca rg roups b e long in gto‘A 1’, ‘ A2 ’, ‘ B 1’ or‘B 2 ’AN D the

 

ra te pe r mile is either11 0 or 1 20. F ind a w a yof o pti mi sing y ourc odeso th a t t he

 

 

 

 

c ompl e te SQ Lst a teme ntha s onl yO NElog i ca l op e ra tor ( AN D, O R, N OT ).(4 m a r k s)

9.   Displa yfull de t a il s for a l l ca rs tha t hav enot bee nse rvice d b e for e a ndha v ea c urr e nt

 

mi lea geofg r ea terth a n 9 00.(3m ar k s)

 

10. Displa yfull de t a il s for a n yc a r  tha t : ( a ) c ostsless t ha n $50,000.00 orthe na me ofthe ca r mode l st a rts with t heletter‘ D’   a nd  (b)b e long s tog roup‘ B 2‘an dis c urre ntl y a va il a ble. (Y ou should h a vea sin gle qu er y that c ompl e tel yfulf il s a llof th e a bove c ondit ions). (4m ar k s)

11. Displa ythe n a me, town a nd usua l pa y m e nt m e thod of a llc ustom e rs. O rd e rb y pa y m e nt m e thod asce ndi ngwithi n town de s c e nding .  You r que r ymustNO T usethe W HE RE c lause . ( 3m ar k s)

12. Displa ythe bookin gnum be r, r e g istr a ti on a nd thena me ofthe c ustome r w h o madethe booking . You ma yn ee d t o usetra dit ional joi n be twee ni_booking a nd i_ c u stome r table s to displa ya llthe r e quire d c olum ns . (4m a rk s)

 

 

 

 

S ECTIO N C ( Dat a M o d e ll in g) ( 40m ar k s)

 

P ART MARK I NG :  W ewillonl ym a rk T W O oft hefourque sti ons be lowbuty ou must submita nswe rs to a llfour . W ewillc hoosethe que sti ons wemar k ra ndoml ya nd mar k the sa me que sti ons for a llsu bmi ssi ons .  Mode la nswe rs w il l be suppl yfo r a llque sti ons during the se meste r.

 

Construc t datamodel s fo r thefollowingsp ec ific a t ion s.I nc lud e a n ERD a n d ali st of re lations (e nti t yli st) . Yourr e latio ns must s how a lla tt ribut e s, primar yke y sa nd fo re ig n ke y s.You m u st use th eUSQ ( F ink e lste in ) me thodol og ya sde sc ribe d iny our Stud yB ook, the lec tu re s a nd the tutoria ls.

 

Qu e stion1

 

A que sti onna ire is a r e se a rc h inst rume nt consist ingofase rie s o f que sti ons.W enee d to de ve lop a mode l t o store que sti onna ire s a nd it s un de rl y in gqu e sti ons. A que sti onna irerec ords que sti onna irena me, p rima r yc onta c t name , star t d a te a nd e x pec tede nd da t e . A que sti onna ire ma yh a veman yqu e sti ons. Eac h que sti on ha s aqu e sti on number , que sti on tex t and a ca te g or y de sc ription forthe qu e sti on.

 

Qu e stion 2

 

A mobi le phonemodel c onsistsof anumberof varia nts (diff e re nt st y l e s , c o lours a nd fe a tur e s ) w hicha resolda s sepa r a te units . W ene e d to deve lop a da ta mod e l t ore c ord diffe re nt m obil ephonem ode lsa nd their va ri a nts . A mobi le phonemodelrec ords bra nd na me, model na me, mod e l num be r (unique )a nd s tar ty e a r.A mobi le phonemodel ma yha ve se ve ra l v a ria nts . Ea c h va ria ntha s a va ria nt code , s t y le, c olour,fea ture ,laun c hy e a ra nd discontinue dy e a r.

 

Qu e stion3

 

A B it c oin wa ll e t m a n a g e sy ourB it c oins. A wa ll e tdoe s not storeB it c oins b ut hol ds the priva te ke y s of theo wne rof thewa ll e t t ha t allow h im /herto acce ss hi s/herb it c oin addre sses a nd to sig n tra ns ac ti ons to a ll ow theowne r to spe nd his/herfund s.

 

Qu e stion4

 

An e ve nt coor dinatorma yb ewor kin gon anumbe r of e ve nts ov e r time . Ane ve nt ca n h a ve mul ti ple e ve nt coor dinat ors w orkin gon itbut wil l onl yha v easin g le e v e nt m a na g e r.F ora n e ve nt coor dinato r, w esto reauniquec oordina tor i de nti fica ti on, his/heroff i c enumbera nd a re ao f spe ci a lt y . W e a ls o nee d to storethe d a te t he e ve nt coo rdina tor c om mence d wor kon the e ve nt and then a me o f the e ve nt, t hee ve nt ma na ge r’ s n a me a nd othe r r e leva nt ev e nt de tails.

 

 

 

 

P r e p ar ethef oll owin g forall f ou rqu e stion s :

 

1    An E R di agr am : S howa lle nti ti e s,re lationships, ca rdina li ti e s a nd opti ona li ti e s.

Also, inc lude a llint e rsec t ion entit ies but do not c re a te e nti ti e s that a renot sp ec ifica ll y c ove re d b yth espec ifi ca t ion.You m u st use t h eFink e lste inm e thod ologyas p e rthe stud y b ooka n dtutor ial s .

2    A list ofre lation s(e n tit y li st). P roduc e c ompl e tere lations for a lle nti ti e s a nd a tt ribute s. S how a llprima r ya nd fo re i g n ke y s.I nc lude a lla tt ribute s that a re spec ifica ll ymentioned a nd a llke ya tt ribute s. Youma yn ee d to cr ea te p rima r ya nd fore i g n ke y s that a renotspec ifica ll ymentionedb ut do notc re a tea n yothera ddit ional a tt ribute s.

 

 

 

 

MAR K INGCR ITER IA SECT IO NA

 

1. S olut ion addre sses thebusine ss problem a nd pr ovides awor ka bl ere solu ti on to t he proble m.

2. S olut ion demonstra tes a pprop ria tere f e re n c eto r eleva nt sec ti ons of the s e lec ted r e a din g s, tex tbook and, if a ppli ca ble, othe r r e fere nc emate ri a l.

3. S olut ion addre sses the a ssum pti ons of theprob lem a pprop ria t e l y .

5. S olut ion i s g ra mm a ti ca ll ya nd stru c tura ll ysoun d.

6. P re se ntation i s nea t an d pro fe ssi ona l.

 

 

 

SECT IO NB

 

1. Ma rks a r e a w a rde dforeac hc orr ec t S Q Lstate m e nt andforthec orr ec t ou tput .

2. Alter na ti ve a pp roa c h e s to t hemodel a nswe r w i llofte n be a c ce pted unle s s the ydo not follow therule s / re quir e ments set out i n the spec ifica ti on, a r epoor l yopti mi se d or a repo orl y c onstruc ted ( S Q L ) .

3. P a rt ma rks ma ybea w a rde d ifa n a nswe r onl yh a sasmall prob lem or a na lt e rna ti ve solut ion i s pre se nted tha t wor ks but is not i dea ll yo pti mi se d / construc ted.

 

 

 

SECT IO NC

 

1. Entiti e s– no mi ssi nge nti ti e s, a pprop ria te na me s, no re dunda nt entit ies, e tc.

2. Ca rdina li ti e s a nd opti ona li ti e sa llshown a nd c or re c t.

3. Compl e te listof re lations, showinga lla pplic a b le a tt ribute s, primar yke y s a nd fo re i g n ke y s.

4. S ophisti ca ti on:we ll – pre se nted solut ion;g ood la y o ut;innovative a ppro a c h; cor rec t diag r a ms/ notation;soluti on ea s yto re a d a nd und e rsta nd; solut ion compre he nsive