YEAR 1 CO1 WEEK 17-18 DATABASES AND NORMALISATION

Question 1

What is a data base?

Answer 1

Is a collection of related data stored in a logical and structured manner

Question 2

State and describe the parts of a database.

Answer 2

Table/file : collection of related records which contain the same type of information as all the other records.
Record : all of data relating to one item or thing.
Field : stores one item of data in a record.

Question 3

What is a file(table)?

Answer 3

A collection of related records which contain the same type of info as all other records
Eg info about all students in a class

Question 4

What is a record?

Answer 4

All data relating to one item or thing
Eg info about one student in a class

Question 5

What is a field?

Answer 5

Stores one item of data in a record
Eg a single characteristic about a student (DOB , surname)

Question 6

What are the key points about flat file databases?

Answer 6

Only one table,
No links between them

Question 7

What are the advantages of a flat file data base?

Answer 7

Easy to set up or understand

Question 8

What are the disadvantages of a flat file data base?

Answer 8

May have redundant data (repeated).
Potential duplicated data.
Harder to update due to data duplication.
Records are not unique.
Security poor as once gained access all data is readily available.
Harder to perform more complex queries.
Easy to setup and understand.

Question 9

What is a relational database?

Answer 9

Has more than one table which are linked together by primary and foreign keys

Question 10

State the advantages of Relational Database and the one disadvantage.

Answer 10

Data only stored once.
Data integrity is maintained.
Easy to modify the format.
Security is better as sections of data can be individually protected using authorisation.
More future proof.
Hard to set up.

Question 11

What is a primary key?

Answer 11

Unique identifier

Question 12

what is A foreign key?

Answer 12

Is a primary key in one table which is used as an attribute in another table
/ repeating attribute

Question 13

What is a compound key?

Answer 13

A combination of fields that makes a record unique

Question 14

What is a secondary key?

Answer 14

Made on a field that you would like to be indexed for faster searching
May not be unique are used for sorting or searching

Question 15

What is normalisation (databases)?

Answer 15

Data is split over multiple tables which are related / The process of refining structure of a database to minimise redundancy and improve integrity.
If not normalised potentially has repeated data or inconsistent data.

Question 16

Describe First Normal Form.

Answer 16

Where there are no repeating attributes and data is atomic.
Atomic data is information which cannot be logically broken down into smaller parts.
Eg. fullname split into forename and surname

Question 17

Describe Second Normal Form.

Answer 17

If it is in 1NF.
There is no partial key dependencies.
If there is a compound primary key likely not to be in 2NF.

Question 18

Describe Third Normal Form.

Answer 18

If it is in 2NF.
All data has to be related to the primary key.

Question 19

What are the three types of integrity(databases)?

Answer 19

Entity integrity
Referential integrity
Domain integrity

Question 20

What is entity integrity?

Answer 20

Every table must have a primary key which must be unique and not empty

Question 21

What is referential integrity?

Answer 21

foreign keys must point to the primary of another table but cannot be empty meaning there is no relationship

Question 22

What is domain integrity?

Answer 22

all attributes in the database are related to the overall domain that the database is working on

Question 23

What is data redundancy?

Answer 23

refers to the unnecessary duplication of data

Question 24

what is data consistency?

Answer 24

When data is help in more that one file it should be stored in a consistent way

Question 25

What is data independence?

Answer 25

This means that users can view data with out having to be aware of the basic structure of the database
Refers to separation of data from program that uses it.

Question 26

What does ERD stand for?

Answer 26

Entity relationship diagram

Question 27

What are entities?

Answer 27

they are tables in a database which are made up of multiple attributes

Question 28

What are Attributes?

Answer 28

Describes the facts, details and characteristics of a entity

Question 29

What are the three types of relationships?

Answer 29

1 to 1
1 to many
many to many

Question 30

What is a one to one relationship?

Answer 30

relationships that are one to another and nothing else

Question 31

What is a one to many relationship?

Answer 31

an example of a one to many is a mother and her children

Question 32

What is a many to many relationship?
(give an example)

Answer 32

an example of a many to many relationship is a book can be loaned by multiple customers and a customer can loan many books

Question 33

how can you get rid of many to many relationships?

Answer 33

they can be removed by adding another entity which acts as a link table
-creates one to many relationships
-makes a relationship into 3NF
-reduces data inconsistencies