2275229_1_dba2Spec

COIT20247 Database Design and Development (T1 2017) Assessment item 2—Assignment 2 Due date

11:45pm 11:45pm on Friday of Week 10 (May 19, 2017)

Weigting

35%

!engt

Not appi!a"e

Ob"e#tives #$i aement item reate to t$e &nit earnin' o&t!ome n&m"er 1, 2 and 3 a tated in t$e &nit profie

Int$odu#tion #$e ampe o&tion of ai'nment 1 i "e reeaed on t$e &nit e"ite appro*imatey to (2) eek eek after after t$e t$e ai'n ai'nmen mentt 1 d&e date +o& must use tat sample solution o%  assignment 1 onl& for t$e fooin' tak:

Assessment tas' 1

o$maliation

a) Map t$e t$e -., -., from t$e t$e ampe ampe o&t o&tion ion,, into a set o% $elations in at eat #$ird Norma Form (*+) +o& +o& m&t en&re t$at yo&r reation meet 3NF #$ere i no need to $o yo&r orkin' ") /ee!t any to (2) of yo&r yo&r reation reation from t$e t$e preio& preio& tep, 1a), 1a), and perform perform t$e fooin' fooin' for ea!$ of t$oe to reation: •

it a t$e %un#tional dependen#ies e*it in t$e reation

•

.emontrate t$at t$e reation meet Ti$d o$mal +o$m (*+),

For yo&r $ep, an e*ampe for a /t&dent reation 3NF &tifi!ation $a "een proided "eo: /#.N# (/t&dent., Name, 6ddre, .atef8irt$) i #$e primary primary key key i /t&dent. /t&dent. $i!$ $i!$ identifie identifie a t&dent t&dent #$ere i i "e "e one t&dent t&dent name name per /t&dent . "e!a&e ony one name i re!orded for ea!$ t&dent en t$o&'$ a t&dent may $ae m&tipe addree (e' a $ome addre and a ork addre), t$e !ae t&dy tate t$at t$ere i a need to re!ord t$e $ome addre ony and $en!e t$ere i "e ony one addre for ea!$ t&dent #$ere i "e ony one date of "irt$ for ea!$ t&dent #$&, t$ere t$ere are no repeatin' 'ro&p en!e, t$i reation i in 1NF 1NF ii #$e primary primary key key i /t&dent. /t&dent. #$i i t$e ony !andidat !andidate e key in!e in!e Name Name i not '&aranteed to "e &ni&e (to different t&dent t&dent may $ae t$e ame name) 6ddre i ao not '&aranteed to "e &ni&e #$i mean t$at /t&dent . f&n!tionay determine eery ot$er attri"&te in t$e ta"e #$ere i a in'e a&ed impe !andidate key (/t&dentd) and t$erefore no partia dependen!ie are poi"e poi"e en!e, t$e reation i in 2NF

iii Name !annot "e &ed to f&n!tionay determine any ot$er attri"&te in t$e ta"e in!e to different t&dent may $ae t$e ame name; ikeie for addre and date of "irt$ #$erefore t$ere are no tranitie dependen!ie in t$e ta"e #$erefore it meet t$e re&irement of firt, e!ond and t$ird norma form 2

-elational database implementation

Note:  Use

the relations that you created in the above step 1-Normalization to create a database. The recommended tool for this work is Microsoft Access version !"1" or later. #ne reason for recommendin$ Microsoft Access is that your unit lecturer%tutor will be able to  provide ade&uate support to you in the use of this tool. 'f you choose to use a different  ()M* then there is no $uarantee that your lecturer%tutor will be able to provide enou$h support to you. The remainder of this document assumes that you are usin$ M*-Access.

n impementin' t$e - mode proided, yo& m&t !ompete t$e fooin' tak:

•

•

•

•

op&ate t$e data"ae it$ sample data o% &ou$ on +o& m&t in!&de &ffi!ient ampe data to tet yo&r &erie and report +lease note that the e,pected result of &uery &uestions may depend on the actual sample data populated.

Data integ$it&

+o& are re&ired to impement inte'rity !ontraint it$in t$e data"ae to enfor!e t$e fooin' re&irement:

•

Fee=<$ar'e $o&d "e poitie !&rren!y and e*preed in 2 de!ima ony

•

.ate a&e $o&d not "e predated

•

Name of patient=medi!a pe!iait $o&d not "e n&

•

/tate a&e $o&d "e imited to 6&traian /tate=#erritorie ony

•

/ki and e*perien!e a&e $o&d not "e n&

•

.i!o&nt amo&nt $o&d "e poitie and !annot e*!eed ?50

In%o$mation $euests


appointment 2, .ipay t$e detai of appointment, name of t$e patient and medi!a pe!iait inoed in

t$e mot e*penie appointment *, For ea!$ of t$e medi!a pe!iait, dipay $i=$er name and t$e n&m"er of

appointment !ompeted o far 4, For ea!$ of t$e appointment !ompeted, dipay t$e date, (tart) time, name of t$e

patient and tota fee=!$ar'e of t$e appointment, di!o&nt amo&nt and fina amo&nt paya"e (Fina amo&nt paya"e C tota appointment !$ar'e A di!o&nt amo&nt ) 6, W$i!$ patient $ae !on&ted more t$an one pe!iaitD .ipay t$e detai of &!$

patient ony , .ipay t$e detai of t$e patient() $oe name !ontain t$e ord Emore /$o t$e

re&t in a!endin' order of t$e name Note: The

word name is a reserved word in M* Access. /herever you use it for *0 &uery you need to put a bracket like 2name3. 4ollow the same techni&ue for other reserved words too. -epo$t


• 

• 

6reate a &uery that lists all details needed for a report. 'f you are unable to formulate this &uery then create a report that is based directly on the table7s8. 2Note5 you will not be able to obtain full marks for the report creation if you base your report directly on the table7s8 and some criteria are not met in your reports.3  Use the report wizard to $enerate a basic report based upon your &uery 2or the table7s8 if you were unable to formulate the &uery3. #nce the report wizard finishes switch t o desi$n view and rename the labels appropriately9 for e,ample chan$e the label 6onsultation(ate to 6onsultation (ate.

Implementation -epo$t  8ou must also p$ovide a so$t implementation report de!ri"in' yo&r e*perien!e it$

t$i ai'nment +o& may di!& any of t$e fooin': 1 6ny to interetin' t$in'=pro!ed&re yo& earnt "y doin' t$i ai'nment 2 6ny one re&irement of t$e ai'nment t$at a reatiey diffi!&t = !ompe* to oe "&t &!!ef&y !ompeted "y yo& e t$e impementation report to demontrate t$at yo& $ae t$o&'$t !aref&y a"o&t t$e i&e t$at arie $en impementin' a reationa data"ae

Wat &ou sould submit

Mi!rooft 6!!e data"ae fie !ontainin': 1

#a"e it$ ampe data

2

-eation$ip

3

@&erie

4

nte'rity !ontraint

5

-eport (o"e!t)

 6 Mi!rooft Word do!&ment !ontainin': 1

#o reation it$ f&n!tiona dependen!ie and demontration of normaiGation

2

mpementation report

+o&r do!&ment $o&d !ontain appropriate identifi!ation information (yo&r t&dent n&m"er, name, &nit n&m"er, et!) and $o&d !ontain t$e reeant $eadin' for ea!$ e!tion Impo$tant points •

•

•

•

•

•

•

•

+o& m&t ork indiid&ay on t$i ai'nment #$e mode o&tion to t$i ai'nment i "e reeaed appro*imatey to (2) eek after t$e d&e date  6i'nment t$at are &"mitted after t$e reeae of t$e mode o&tion i re!eie Gero mark +o& m&t tate in yo&r ord do!&ment t$e detai of a materia and o&r!e t$at yo& &ed to !ompete t$i ai'nment #$i may "e a impe a H#$i ai'nment a !ompeted &in' ony t$e <# 20247 &nit materiaH *tenion re&et ("y fiin' in an onine form) $o&d "e &"mitted "efore t$e ai'nment d&e date 8efore &"mittin' yo&r ai'nment, reie t$e markin' !riteria proided on t$e at pa'e of t$i do!&ment n&re t$at a t$e !riteria are addreed in yo&r &"miion >enatie reated to ate &"miion and pa'iarim i "e appied a per nierity poi!y

Assessment #$ite$ia COIT20247 Assignment 2 .a$'ing 9eet 1 -elational database implementation (12 ma$'s)

Available ma$'s

/et of reation !orre!ty mapped from -.

3

F&n!tiona dependen!ie !orre!ty ited for to of t$e reation

2

NormaiGation !orre!ty ited for to of t$e reation

2

-eation$ip et &p !orre!ty, referentia inte'rity enfor!ed, appropriate &e of !a!ade &pdate=deete

2

.ata type B iGe ee!ted appropriatey

2

nde*e A nde*e are appropriate 2 Data integ$it& (* ma$'s) .ata inte'rity !orre!ty impemented 3 :ue$ies (12 ma$'s) @&ery 1 @&ery 2 @&ery 3 @&ery 4 @&ery 5 @&ery I 4 -epo$t (4 ma$'s) nderyin' &ery=&erie $a=$ae "een !reated and are !orre!t -eport ayo&t and $eadin' are appropriate; 'ro&pin' and ortin' are appropriate 5 Implementation $epo$t (4 ma$'s) mpementation report di!&e appropriate i&e 9ub;total be%o$e an& possible dedu#tions ate penaty (enter as te numbe$ o% da&s late) Assignment 2 total

1

3

2 2 2 2 2 2 2 2 4 *6 3*6