Relational Database Concepts

Question 1

What is a table made up of?

Answer 1

Records

Question 2

What are records made up of?

Answer 2

Fields

Question 3

What are fields made up of?

Answer 3

Characters

Question 4

Describe a table in a database.

Answer 4

Tables contain data such as students, orders, events, customers, suppliers, etc. A table is a data structure made up of rows and columns that contains data about the items.

Question 5

What requirements must be met for the data structure to be called a table?

Answer 5

The table must have a unique name. Each field/column must have a unique name. Each record/row must be unique. Each data item within a field must contain only a single data item. The order of the records and fields does not matter.

Question 6

Describe a record in a database.

Answer 6

A record is a single row within a table. It is a collection of data about a single item/event. A record might be about a customer, an order or an item, etc.

Question 7

In a table, each record must be ______.

Answer 7

Unique

Question 8

Describe a field in a database.

Answer 8

A field is an individual item within a record. Each field within a record should have a unique name. A field should only contain a single data item. Fields have individual data types and can have their own validation.

Question 9

What is a primary key?

Answer 9

This is a field, in the table, that allows each record to be uniquely identified. Every value of the primary key must be unique.

Question 10

What are the two types of primary key?

Answer 10

Simple or compound/composite.

Question 11

What is a simple primary key?

Answer 11

One which is made up of a single field only, like ID.

Question 12

What is a compound primary key?

Answer 12

One which combines more than one field to make a unique value.

Question 13

An example of a compound primary key:

Answer 13

If we had a table showing a student, a date/time and if they are present - a student can only be in one place at a time. Therefore, combining the student name, data and period gives a unique value. These three fields could be combined to make a composite primary key.

Question 14

What is a secondary key?

Answer 14

A secondary key is a field that is identified as being suitable for indexing the data. It is used to sort the data in a different order to the primary key. A table can have many secondary keys (every field could be a secondary key).

Question 15

What is a foreign key?

Answer 15

A foreign key is used to link tables together. A foreign key is a field in one table that is linked to a primary key in another table.

Question 16

What must be the same between a foreign key and a primary key in two tables being linked together?

Answer 16

The data types.

Question 17

What is an entity?

Answer 17

Put simply, a thing.

Question 18

Some observations on entities:

Answer 18

The world is made up of entities. Entities can be linked to each other by means of a relationship.

Question 19

What are the three types of relationship?

Answer 19

One to one, one to many, many to many.

Question 20

Describe a one to one relationship.

Answer 20

A school can only have one head teacher and a head teacher can only be so at one school. We discount history, so ignore the fact that the school will have had many head teachers. They currently have one. One to one’s are an unlikely relationship.

Question 21

Some examples of one to one relationships:

Answer 21

Husband to wife. Car to tax disc. Person to National Insurance Number.

Question 22

Describe a one to many relationship.

Answer 22

The most common type of relationship between entities. A library member could have several fines at the same time for different books, however each fine is owed by the same member. A mother can have several children.

Question 23

Some examples of one to many relationships:

Answer 23

Library member to fine. Mother to children. Company to employees.

Question 24

Describe a many to many relationship.

Answer 24

Many instances of an entity can be associated with many instances of another entity.

Question 25

What do many to many relationships do when normalised?

Answer 25

They break the rules of normalisation. There should be no many to many relationships in a normalised database.

Question 26

What is a link entity in a many to many relationship?

Answer 26

This is an entity that sits between the two current entities and has a one to many relationship with each. One actor is a part of many people in the cast. Many people in the cast are a part of one film.

Question 27

Some examples of many to many relationships:

Answer 27

Course to registration to student. Customer to order to product. Actor to cast to film.

Question 28

NOTE:

Answer 28

When resolving many to many relationships, the foreign key is on the many side and the primary key is on the one side.

Question 29

If ‘Customer’ was an entity, what would the attributes be?

Answer 29

Forename, Surname, Company name, Address line, etc.

Question 30

NOTE FOR DATABASES:

Answer 30

Read pages 112 - 116 in the book for a good example.