database systems - questions and mark schemes

8/9/2019 Database Systems - Questions and Mark Schemes

1/19

ICT DEPARTMENT A2 WJEC ICT

4.3.6 Database Systems

This document contains questions and associated marking schemes from previous examination

papers provided by the W!" examination board.

Details Summer 12 Question Paper, Questions 15 1!#uestion

$%&

marks'

%(. !xp)ain *hat is meant by a re)ationa) database. $+'%6. , D-D enta) "ompany uses a re)ationa) database management system for

organising its )ending. /or simp)icity0 a person may on)y borro* one D-D at

a time.1art of one tab)e in the D-D enta) "ompany database cou)d be2W! $orro*er5D0 /irstame0 Surname0 orro*er Type0

,ddress%0 ........' *ith orro*er5D being the primary key.7ive t*o other suitab)e tab)es you cou)d expect to see in this database0

identifying any primary and foreign keys. $&'%&. e)ationa) databases are norma)ised to *ork e8ective)y. !xp)ain *hat is

meant by the term data norma)isation. $+'%9. Some companies no* use distributed databases. !xp)ain *hat is meant by

the term distributed databases and exp)ain their advantages and

disadvantages. $6',ns*er Question 15

Suita"le #e$nition o% a relational #ata"ase, su&' as(

, :)arge; co))ection of data items and )inks=foreign key

% mark per tab)e name = +

% mark per primary key = +

% mark for t*o foreign keys = % :5f key dup)icated ? no mark;

% mark per tab)e for + extra @e)ds = +

Question 1

Suita"le #e$nition o% #ata normalisation, su&' as(

, staged :mathematica); process :%; *hich removes repeated groups of data and

inconsistencies. :%;

r

Simp)ifying data structures :%; so that attributes in each tab)e on)y re)ate to the

entity. :%;

DATE( 2167162715 PA-E 1 )8 19


2/19



Aarks can be gained by using an examp)e of the process of going from @rst to third

form.

Do N)Taccept advantages of databases

Question 1!

2 mar:s %or #e$nition an# ; %or a#


3/19



$%&

marks'

techno)ogy.

%&. !xp)ain *hat is meant by a re)ationa) database. $+'%9. Esing appropriate examp)es0 describe the advantages of a re)ationa)

database approach over a Fat @)e approach. $6'%G. Describe *hat a data *arehouse is and give an examp)e of ho* a company

might use it. $3'+H. !xp)ain *hat is meant by data mining and give a detai)ed examp)e of ho* a

company might use it. $3'+%. Discuss in genera) terms0 the advantages and disadvantages distributed

databases give to a company. $3'

,ns*er Question 1

, re)ationa) database is a )arge co))ection of data items


4/19



data may need to be input t*ice and if data is changed in one it *i)) need to be

changed in the other. < Data *hich is repeated unnecessari)y is ca))ed redundant

data.

In#epen#en&e Data independence J the data and the app)ications


5/19



discover meaningfu) patterns. 5s )ooking for meaningfu) patterns in a )arge mass of data and presenting

resu)ts in tab)es and graphs.

E>amples 4ort' 1 mar:(

"an provide2

the company *ith a )ist of customers )ike)y to buy a certain product0 *hich

they can then use to target *ith a mai) shot. comparisons *ith competitors

usefu) L*hat ifI resu)ts from mode))ing exercises

predictions for future sa)es

ana)ysis of best sites for shops

ana)ysis of sa)es patterns

returned information can be tested for p)ausibi)ity. data if of va)ue can be processed into a report to he)p decision making.

E>amples 4ort' 2 mar:s(

/ighting shop)ifting in c)othing stores J aeger used DA to )ook at

transactions and position of item in store J found even *ith tags most items

sto)en near doors J )ed to increased ""T-0 more prosecutions and recovery

of goods. 5denti@cation of customer needs J -irgin Aedia use DA to segment and

target customers most )ike)y to buy ne* services or upgrades. "ou)d a))o* company to @nd a previous)y unkno*n re)ationship bet*een

regions of the country and food preferences and they can then target specia)

promotions.

The di8erence here is that the *hy *i)) refer to a ne* connection bet*een the data

or a ne*

"onc)usion

Question 21

% mark each for any three points but must have at )east one advantage and one

disadvantage

Aust be in context of a company

A#


6/19



5f one server fai)s then the other servers can be used

educes net*ork traBc as )oca) queries can be performed using the data on

the )oca) server.

Disa#


7/19



Question 1!

+ x :% mark tab)e name0 % mark for primary key @e)d0 % mark for foreign key; and %

mark for + other @e)ds in each tab)e

M $ooko0 Tit)e0 ,uthor0 7enre0 5S0 etc'

K, $Koan5D0 orro*er5DN0 ookoN0 StartDate0 Kength0 etc'

Where under)ined are primary keys and N are foreign keys

5f candidates have produced a fu))y *orking rea) so)ution using more than three

tab)es fu)) marks can sti)) be a*arded.

Question 19Cierarchy of pass*ords ?? pass*ords to see separate parts $%'

Storage of data separate to programs $%'

,ccess rights to parts of the program. $%'

Question 27

ar=e0 Ar&'i


8/19



is a specu)ative process investigating potentia) patterns

invo)ves the presumption that dormant *ithin the data are undiscovered

patterns < groupings < sequences < associations.

soft*are uses comp)ex a)gorithms to search for patterns. is dri))ing do*n into the mass of data so users can understand it more ample o% one possi"le %ull mar: ans4er(

"ESTA! :"ustomer5D0 ame0 contact no0 address%0 address+0 etc.;

D!S :rder5D0 Date0 Stock5D0 "ustomer5D;

Se&tion C

Data Ware'ousin= an? t4o points

The mai) order company generates huge quantities of data stored in a

consistent order to make interrogation more productive. Data is non?vo)ati)e and time invariant :archive data;.

Esed to support organisationa) decision making.

DATE( 2167162715 PA-E 17 )8 19


11/19



Data Minin= an? t4o points

5s interrogating the data

5t is a specu)ative process < investigates potentia) patterns 1resumption is that dormant *ithin the data are undiscovered patterns 1art of a )arger question,ns*er Se&tion A

DATE( 2167162715 PA-E 15 )8 19


16/19



Shared processing across the 5nternet < net*orks < Distributed databases are

di8erent databases stored at di8erent )ocations but )inked together so they appear

to be one )arge database.

$% for de@nition'.

!.g. , hote) chain may store detai)s of guests booking on its )oca) net*ork but

because each hote) is net*orked a distributed database can be used and sta8 in

one hote) can see booking in another hote) and managers can monitor booking

across the *ho)e chain. Simi)ar)y for a chain of shops.

$% mark description of examp)e'

!xamp)e might give exp)anation of Pdistributed databaseP.

Se&tion 3

$% mark for each bene@t max of + marks'

,))o*s the )oca) processor to be used to share processing *hen not being

used for other activities

Data used )oca))y can be stored )oca))y and net*ork traBc kept to a

minimum

5f data )ost on centra) site it cou)d be redup)icated from )oca) site

,))o*s sharing of data and of the resu)ts of processing of the data.

e* )ocations can be added to the database *ithout the need for re*riting

the entire database.Details Summer 7 Question Paper, Question ;, Se&tion A#uestion

$+ marks'

Aany bank customers have on)ine bank accounts. "ustomers can comp)ete on)ine

forms to pay bi))s and transfer money bet*een their accounts using the 5nternet.

-eri@cation or va)idation procedures are used to reduce data entry errors.

:a; !xp)ain0 giving a di8erent examp)e in each case0 ho* both the bank and the

customer can minimise data entry errors *hen @))ing in the on)ine forms. $+'

>1art of a )arger question,ns*er Se&tion A

% mark for customer measure and % mark for bank measure

"ustomer ? -eri@cation procedures

ead data in forms carefu))y before submitting e.g. *hen purchasing on)ine

DATE( 2167162715 PA-E 1* )8 19


17/19



Doub)e entry keying e.g. *hen creating pass*ords for accounts

ank ? -a)idation procedures

ange checks

1resence checks

"heck digits

/ormat checks

5nput masks

Details Summer 7 Question Paper, Question 5#uestion

$6 marks'

, co))ege keeps detai)s of students0 sta8 and courses in a @)e. 1art of this @)e is

sho*n be)o*. The data in this @)e is not norma)ised.

:a; Esing data from the above @)e to i))ustrate your ans*er0 describe t*o di8erent

prob)ems associated *ith data not being norma)ised. + $+'

:b; The above data can be re?organised into a norma)ised re)ationa) database *ith

tab)es )inked using primary and foreign keys.

e?organise this data into a norma)ised re)ationa) database using t*o tab)es. Rou

shou)d c)ear)y indicate the tab)e names and any primary or foreign keys that you

use. $(',ns*er Se&tion A

% mark for disadvantage and % mark for examp)e x + $4 Aarks'

Disa#ample The )ecturer name C SmithP is repeated)y stored in the above

data.

Disa#


18/19



might be mistyped as 9%6. The computer *i)) not kno* *hich

one is correct.

Se&tion 3

% mark for tab)e x +

% mark for primary key x +

% mark for foreign key in other tab)e

1rimary and foreign keys must be c)ear)y identi@ed

!xamp)es cou)d be

Student $Student o0 Student ame0 Date of birth0 7ender0 "ourse oN'

"ourse $"ourse o0 "ourse ame0 Kecturer oN0 Kecturer ame'

Details Summer 7* Question Paper, Question , Se&tions A 3#uestion

$%4

marks'

:a; , hospita) uses a re)ationa) database management system for storing patient

records. Sta8 and patients are a))ocated to *ards.

:i; !xp)ain *hat is meant by a re)ationa) database. $+'

:ii; ne tab)e in this database cou)d be

W,D :Ward5d0 umofeds0 Sta85d;

*ith Ward5d being the primary key and Sta85d the foreign key.

7ive t*o other suitab)e tab)es you cou)d expect to see in this

database0 identifying any primary or foreign keys. $6'

:iii; !xp)ain *hy re)ationa) databases are more secure than a Fat @)e

approach for storing patient records. $+'

:b; Cospita)s use distributed medica) databases. Describe the prob)ems that cou)d

arise *hen using such distributed medica) databases. $4'

>1art of a )arger question,ns*er Se&tion A

:i; Suitab)e de@nition of a re)ationa) database0 such as2

, )arge co))ection of data items and )inks bet*een them0 structured in such a *ay

that it a))o*s it to be accessed by a number of di8erent app)ications programs.

DATE( 2167162715 PA-E 1! )8 19


19/19



:ii;

ST,// : Sta8 no0 ame0 expertise0 pager no0 contact no0 Ward no;

1,T5!T : 1atient no0 ame0 i))ness0 admission date0 consu)tant0 address0 contact0

Ward no;

% mark for each tab)e name

% mark for each key @e)d

% mark for both foreign keys

% mark for t*o other re)evant @e)ds

:iii; ,ny t*o re)evant reasons2 $+ x %'

Cierarchy of pass*ords

Storage of data separate to programs

,ccess rights to parts of the program

Se&tion 3

4 separate points or + very *e)) argued points for maximum marks $4 x %' or $+ x

+'

Aore comp)ex hence more expensive to insta)) and maintain

5ncreased security risk from transfer

5f one )ocation fai)s might hinder others

"ou)d get data inconsistency

database systems - questions and mark schemes

Documents