Relational Database Concepts Flashcards
What is a table made up of?
Records
What are records made up of?
Fields
What are fields made up of?
Characters
Describe a table in a database.
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.
What requirements must be met for the data structure to be called a table?
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.
Describe a record in a database.
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.
In a table, each record must be ______.
Unique
Describe a field in a database.
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.
What is a primary key?
This is a field, in the table, that allows each record to be uniquely identified. Every value of the primary key must be unique.
What are the two types of primary key?
Simple or compound/composite.
What is a simple primary key?
One which is made up of a single field only, like ID.
What is a compound primary key?
One which combines more than one field to make a unique value.
An example of a compound primary key:
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.
What is a secondary key?
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).
What is a foreign key?
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.
What must be the same between a foreign key and a primary key in two tables being linked together?
The data types.
What is an entity?
Put simply, a thing.
Some observations on entities:
The world is made up of entities. Entities can be linked to each other by means of a relationship.
What are the three types of relationship?
One to one, one to many, many to many.
Describe a one to one relationship.
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.
Some examples of one to one relationships:
Husband to wife. Car to tax disc. Person to National Insurance Number.
Describe a one to many relationship.
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.
Some examples of one to many relationships:
Library member to fine. Mother to children. Company to employees.
Describe a many to many relationship.
Many instances of an entity can be associated with many instances of another entity.
What do many to many relationships do when normalised?
They break the rules of normalisation. There should be no many to many relationships in a normalised database.
What is a link entity in a many to many relationship?
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.
Some examples of many to many relationships:
Course to registration to student. Customer to order to product. Actor to cast to film.
NOTE:
When resolving many to many relationships, the foreign key is on the many side and the primary key is on the one side.
If ‘Customer’ was an entity, what would the attributes be?
Forename, Surname, Company name, Address line, etc.
NOTE FOR DATABASES:
Read pages 112 - 116 in the book for a good example.
