Chapter 17 Relational Databases and Normalisation Flashcards
How can data be entered into a database?
- web based forms
- paper forms
- automated devices
- OCR
- OMR
What is the main advantage of having web based forms?
Can have very strong validation for data being entered
What is the main disadvantage of paper forms?
Prone to mistakes, so people are asked to enter data twice
What are some examples of automated devices?
barcode scanners, smart card readers
What does OCR stand for and what is it?
optical character recognition used for handwritten character recognition
What does OMR stand for and what is it?
Optical mark recognition, captures human entered data from forms
What are the names of files that can be used to store captured data? - data that can be in a database
- serial files
- sequential files
- indexed sequential files
- random/direct access files
What is a serial file?
Stores data in entered order, there is no order enforced
What is a sequential file?
stores data in order of the key field, order is enforced
What is a indexed sequential file?
Stores data in order of the key field with an index to allow groups of records to be accessed quickly
What is a random/direct access file?
Data entered is given a unique record number that can be used to access that data
What does DBMS stand for?
database management system
What are some examples of DBMS?
Microsoft Access
Oracle
What is a DBMS?
A software application with many features used for dealing with databases
What are the features of a DBMS?
- creates interface for user
- allows queries
- reports
- additional security
- enforces integrity of data (referential integrity)
- interact with other software applications
What additional features can a DBMS administrator do?
- provide centralised backup
- assign user access levels
- create logon and passwords for database
- updates to DBMS
Normal form of displaying a table/relation/entity?
entity(attribute one, attribute2 , attribute3)
What does it mean when in the form above something is underlined?
It means it is the primary key
What does it mean when something in this form is overlined?
It means there is more than 1 piece of data in this attribute.
What does it mean when there is an asterisk next to an attribute in this form?
Means that attribute is a foreign key.
What is data integrity in a database?
The correctness and consistency of data along the whole database
What is referential integrity?
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.
Techniques used by the concept of referential integrity?
- 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
What is normalisation?
formal process involved in the design of an efficient relational database?
Can a flat file database be normalised?
No if we want it to be a flat file database. It would turn into a relational
Normalisation means that entites are organised in such a way that:
- 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
What form of normalisation do we have to know?
1NF 2NF and 3NF
What does 1NF require?
Contains no more than one piece of data in a field under an attribute
What do we have to do to 1NF?
- 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
What is 2NF and what does is require?
2nd Normal Form - requires 1NF and requires that all key attributes are dependent on the composite key, not only partially
What do we call 2nf?
Partial dependency test
What do we do to undergo 2NF?
- seperate any attributes which are not fully dependent on composite key
What is 3NF and what does it require?
3rd normal form, requires 2NF and seperate any attributes which are dependent on other non-key attributes, foreign keys are retained tho
Overall what does 3nf mean?
All attributes are dependent on the key, the whole key and nothing but they key.
To complete 3NF we have to ?
- 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
Advantages of Normalisation?
- 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)
