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.
