3Computer Science_10_Revision_Information Systems

formulate definitions of terms: field, record, index;
define primary key in database;
define data types in database (SQL);
create a single-table database (SQL);
create multi-table database (SQL);
create a form for data entry (SQL);
create queries using extracted data (SQL);
generate reports using extracted data (SQL);

Lesson Objectives

What is a database?
Where do we use them?
Why do we use them?
What are the different types of database?

Success criteria

Learner can:

Describe the terms: database, DBMS, entity, record, field, primary key, composite key, foreign key
Correctly determine the types of data
Explain the basic concepts in a relational database

Let’s remember…

What is a database?
Which model(s) of the database do you know? Give examples.
What is a DBMS?

Which kind of organisations do need to store data?
What data do they need to store.
Give a few examples.

attribute,
field,
primary key,
composite key,
foreign key.

Each entity is represented by one table.
Each row of the table (one record) corresponds to one entity instance, and each field describes a certain property (attribute).
For example: entity - books, attributes - name, publisher, year, etc.

When we talk about the properties of an entity (object), mean that each particular property (in the table - the record field) takes values from a certain set.

The specified set is called the data type.

Primary key is a field in a table that contains unique data. We use primary keys to make records unique and identify it.

Composite key - Collection of attributes uniquely identify a tuple rather than just one

Foreign Key An attribute in one table that is a Primary key in another table, often there to reference to said table later

Each instance of an entity can have one, and only one, related instance of another entity.

Each instance of an entity can have any number of related instances of another entity, however those instances are only have one related instance of the first entity.

Each instance of an entity can have any number of related instances of another entity, and each of those instances can have many related instances of the first entity. This is to be avoided within proper database design.

One to One
E.g. A husband and wife. A husband can only have one wife and a wife can only have one husband

One to Many
E.g. A football team and players. A club team has many players but a player only has one club team

Many to Many
E.g. Product and Component. A product has many components and a component can be used in many products

Diagrammatically…

Husband

Wife

Player

Product

Component

Team

One to One

One to Many

Many to Many

Note:

Many to many relationships are generally bad and if you have 2 in a database then the database CANNOT be properly designed or functional


Pair up the relationships!

Task

Normalisation

Normalisation is the process used to design a database in the most efficient way

Its mainly common sense!

Functional Dependency

A Functional Dependant
A field reliant upon another field for meaning

Example

Price is meaningless without it’s Item, it cannot “function” without it

Match up the functional dependents

Winter Boots

Halo 3

Joe Bloggs

Classroom 341

Pepsi

Age - 23

Sugar - 37%

Size - 37

Age Rating - 15

Number of desks - 12

1. Remove repeating groups to create a new entity
2. Create a relationship using one of the attributes that are left [Usually the primary key]

3.‘Check out’ entities with concatenated keys. If any attribute is not fully dependent on both parts of the primary key remove it to create a new entity.
4. Create a relationship using one of the attributes that are left [Usually the primary key]

5.‘Check out’ every entity. If any attribute is dependent on any attribute other than the primary key, remove it into a new entity.
6. Create a relationship using one of the attributes

How to normalise a database in 6 steps

1st normal form

A database in first normal form must not contain repeating attributes

The table must also have a primary key

1st step…

Add a foreign key…

Add a primary key…

2nd normal form

Don’t include attributes in that table that are not fully functionally dependent upon both parts of a composite key

A table needs to be in first normal form and no column that is not part of a primary key, is dependant on only one portion of the primary key.

Example

Check each attribute is dependent on the composite key…

Add a foreign key…

3rd normal form

A table is in 3rd normal form if it contains no ‘non-key’ dependencies.
Remove any attributes that isn’t functionally dependant on the primary key

Test the attributes…

Add Primary key…

Add Foreign key…

All together now…

Customer

Orders

Qty

Items

1. Remove repeating groups to create a new entity
2. Create a relationship using one of the attributes that are left [Usually the primary key]

3.‘Check out’ entities with concatenated keys. If any attribute is not fully dependent on both parts of the primary key remove it to create a new entity.
4. Create a relationship using one of the attributes that are left [Usually the primary key]

5.‘Check out’ every entity. If any attribute is dependent on any attribute other than the primary key, remove it into a new entity.
6. Create a relationship using one of the attributes

6 steps…

Task…

Normalise this flat file database!

What is a relational database?
What are the types of data in the database?
What are the different types of links in the database?
What is the primary key?

Determine the entities and attributes
Which will be the relationship between the tables?
Define the key fields for each table
Explain the purpose of foreign keys

Individual work

- What did you learn in the lesson?
- What do you consider necessary to remember?
- What else do you need to work on?