Section 11 - Databases and Normalisation

Question 1

What is an entity?

Answer 1

A category of object, person, event or thing of interest to a organisation about which data is to be recorded

Question 2

What does every entity in a database have?

Answer 2

Attributes

Question 3

What are attributes?

Answer 3

Details of an entity

Question 4

How are attributes listed?

Answer 4

Inside the brackets

Question 5

What is a primary key?

Answer 5

A unique identifier for each record in a table

Question 6

How is a primary key shown?

Answer 6

It is underlined

Question 7

What are the 3 types of entity/table relationships?

Answer 7

One-to-one
One-to-many
Many-to-many

Question 8

What is a one-to-one relationship?

Answer 8

Where each entity can only be linked to one other entity

Question 9

What is a one-to-many relationship?

Answer 9

One table can be associated with many other tables

Question 10

What is a many-to-many relationship?

Answer 10

One entity can be associated with many other entities, and vice versa

Question 11

How are one-to-one relationships displayed?

Answer 11

A single line connecting the 2 entities

Question 12

How are one-to many relationships displayed?

Answer 12

The line will have a branch on one side (the many side)

Question 13

How are many-to-many relationships displayed?

Answer 13

There will be branches on both sides of the line

Question 14

What is a relational database?

Answer 14

A collection of tables in which relationships are modelled by shared attributes

Question 15

What is a foreign key?

Answer 15

A unique identifier that links 2 tables together. In one table the key will exist as the primary key, whilst in another table it will exist as a foreign key

Question 16

What is a composite primary key?

Answer 16

A primary key that consists of more than one attribute

Question 17

What is a secondary key?

Answer 17

A unique identifier that allows a database to be searched quickly.

Question 18

How can a record with a particular primary key be found quickly?

Answer 18

The database will automatically maintain an index of primary keys, giving the position of their respective records

Question 19

What is normalisation?

Answer 19

The process of designing the best possible layout for a relational database

Question 20

What does normalisation attempt to accomplish?

Answer 20

• No redundancy
• Consistent data throughout the linked tables
• Records can be added and removed without issues
• Complex queries can be carried out

Question 21

What is first normal form?

Answer 21

A table is in 1NF if each attribute is atomic with no repeating attribute

Question 22

What is the definition of atomic?

Answer 22

Containing a single value

Question 23

What is a relation?

Answer 23

A table in a relational database

Question 24

When is a link table needed?

Answer 24

Whenever 2 entities have a many-to-many relationship

Question 25

What is second normal form?

Answer 25

A table is in 2NF if it is in 1NF and it contains no partial dependencies

Question 26

What is a partial dependency?

Answer 26

When one or more attributes depend on only part of the primary key

Question 27

What does partial dependency occur?

Answer 27

When the primary key is a composite key

Question 28

What is third normal form?

Answer 28

A table is in 3NF if it is in 2NF and all attributes are dependant on the key, the whole key, and nothing but the key

Question 29

What are the advantages of normalisation?

Answer 29

- Maintaining and modifying the database - Faster sorting and searching - Deleting records

Question 30

How is a normalised database easier to maintain and modify?

Answer 30

- Data integrity is maintained because there is no unnecessary duplication of data. This means that when an attribute needs updating, it only needs to be changed in a single position

Question 31

How is a normalised database faster to sort and search?

Answer 31

A normalised database produces smaller tables with fewer fields, meaning there is less data and operations are quicker

Question 32

How is a normalised database advantageous for deleting records?

Answer 32

If set up correctly, a normalised database will not allow records in a table on the 'one' side of a one-to-many relationship to be deleted accidently