Chapter 17 Relational Databases and Normalisation

Question 1

How can data be entered into a database?

Answer 1

• web based forms
• paper forms
• automated devices
• OCR
• OMR

Question 2

What is the main advantage of having web based forms?

Answer 2

Can have very strong validation for data being entered

Question 3

What is the main disadvantage of paper forms?

Answer 3

Prone to mistakes, so people are asked to enter data twice

Question 4

What are some examples of automated devices?

Answer 4

barcode scanners, smart card readers

Question 5

What does OCR stand for and what is it?

Answer 5

optical character recognition used for handwritten character recognition

Question 6

What does OMR stand for and what is it?

Answer 6

Optical mark recognition, captures human entered data from forms

Question 7

What are the names of files that can be used to store captured data? - data that can be in a database

Answer 7

• serial files
• sequential files
• indexed sequential files
• random/direct access files

Question 8

What is a serial file?

Answer 8

Stores data in entered order, there is no order enforced

Question 9

What is a sequential file?

Answer 9

stores data in order of the key field, order is enforced

Question 10

What is a indexed sequential file?

Answer 10

Stores data in order of the key field with an index to allow groups of records to be accessed quickly

Question 11

What is a random/direct access file?

Answer 11

Data entered is given a unique record number that can be used to access that data

Question 12

What does DBMS stand for?

Answer 12

database management system

Question 13

What are some examples of DBMS?

Answer 13

Microsoft Access

Oracle

Question 14

What is a DBMS?

Answer 14

A software application with many features used for dealing with databases

Question 15

What are the features of a DBMS?

Answer 15

• creates interface for user
• allows queries
• reports
• additional security
• enforces integrity of data (referential integrity)
• interact with other software applications

Question 16

What additional features can a DBMS administrator do?

Answer 16

• provide centralised backup
• assign user access levels
• create logon and passwords for database
• updates to DBMS

Question 17

Normal form of displaying a table/relation/entity?

Answer 17

entity(attribute one, attribute2 , attribute3)

Question 18

What does it mean when in the form above something is underlined?

Answer 18

It means it is the primary key

Question 19

What does it mean when something in this form is overlined?

Answer 19

It means there is more than 1 piece of data in this attribute.

Question 20

What does it mean when there is an asterisk next to an attribute in this form?

Answer 20

Means that attribute is a foreign key.

Question 21

What is data integrity in a database?

Answer 21

The correctness and consistency of data along the whole database

Question 22

What is referential integrity?

Answer 22

A concept of a database where consistency between linked tables is kept. It enforces an action on linked table based on the action on the primary and vice versa.

Question 23

Techniques used by the concept of referential integrity?

Answer 23

• update , when a record is updated, all records pointed to by the foreign key will also be updated to the same values
• delete , when a record in primary table is deleted, all records pointed to by foreign keys in other tables will also be deleted

Question 24

What is normalisation?

Answer 24

formal process involved in the design of an efficient relational database?

Question 25

Can a flat file database be normalised?

Answer 25

No if we want it to be a flat file database. It would turn into a relational

Question 26

Normalisation means that entites are organised in such a way that:

Answer 26

- there is no data redundancy - there is no data inconsistencies (due to being a relational and no data redundancy) - complex queries can be made - structure of entites allow unlimited records to be added

Question 27

What form of normalisation do we have to know?

Answer 27

1NF 2NF and 3NF

Question 28

What does 1NF require?

Answer 28

Contains no more than one piece of data in a field under an attribute

Question 29

What do we have to do to 1NF?

Answer 29

- create new relations to seperate all attributes which have fields which have more than 1 piece of data - all single value attributes are kept wihin original relation/table/entity

Question 30

What is 2NF and what does is require?

Answer 30

2nd Normal Form - requires 1NF and requires that all key attributes are dependent on the composite key, not only partially

Question 31

What do we call 2nf?

Answer 31

Partial dependency test

Question 32

What do we do to undergo 2NF?

Answer 32

- seperate any attributes which are not fully dependent on composite key

Question 33

What is 3NF and what does it require?

Answer 33

3rd normal form, requires 2NF and seperate any attributes which are dependent on other non-key attributes, foreign keys are retained tho

Question 34

Overall what does 3nf mean?

Answer 34

All attributes are dependent on the key, the whole key and nothing but they key.

Question 35

To complete 3NF we have to ?

Answer 35

- separate any attributes which are dependent on any other non-key attributes while foreign keys are retained - check each attribute make sure it is derived from the primary key otherwise create new relation

Question 36

Advantages of Normalisation?

Answer 36

- no data redundancy - no inconsistencies - easier to maintain and induce changes - maintenance of integrity is easier (usually enforced) - smaller tables mean faster searching,sorting,indexing and querying - saved storage space (due to no redundancy)