Session Objectives# 21
COULD understand the relationship between entities and tables
SHOULD explain the use of key fields to connect tables and avoid data redundancy
MUST describe methods of validating data as it is input.
Create an effective relational database with validated input methods.
GCSE Computing#BristolMet
Key Words
GCSE Computing#BristolMet
Appropriate use of validation
Scenario: You will create a flat file database to hold data
about students their courses of study. The student table will
have the following field names:
Predicted Grade
What would be a suitable data type and/or validation method for
each field??
Database developers ask these questions before building the
database. They put the information in a simple table called a
data dictionary.
GCSE Computing#BristolMet
Data Dictionary
The data dictionary ensures the completeness and accuracy of the
database and avoids complications in the development stage
Details included in data dictionary are:
• Field name – which will be used to contain the data.
• The data type required for that field
• The size of the data (storage requirement)
• Any validation that could be/should be used
• Identify which field will be used as a Primary Key
TASK: Create a data dictionary table for the school system using
the above bullets as column headings
GCSE Computing#BristolMet
Relational Databases
You will notice from the flat file database a lot of values are
repeated. This is known as redundant as it is not needed and
only uses up more storage space.
By creating another table and linking them together this
repetition or redundancy can be avoided. A database of more than
one table linked or related to each other is known as a
relational database.
Entities – Each table in a relational database contains data
about one entity i.e you could have one entity (table) about
students (called TblStudent, another about Exam Boards
(TblExamBoard) and another about courses (TblCourses).
Tables are linked together using Primary and Foreign Keys. The
Primary Key (the unique identifier field) from one table becomes
as foreign in the table it is linked with. See top of p. 98 for
an example.
GCSE Computing#BristolMet
Entity Relationship Diagram (ERD)
The relationship between tables or entities in a rdb is shown
using an ERD. These relationships are usually one to many or
many to one
For example,
each student will study many courses (one to many)
Many courses will offered by one exam board (many to one)
(There are one to one relationships but not in this example.
ERDs use a crows feet diagram to indicate the type of
relationship i.e
See pg 97 for some further illustrations
GCSE Computing#BristolMet
Put into practice
Now let’s put that theory into practice.
Include another table to separate information about subjects
from the student table. Create an attributes table and ERD of
the 2 tables.
Now lets build...
In your notes answer questions 1 – 3 on p 110 -113
GCSE Computing#BristolMet
Attributes Tables
These are used in design process to tell the develop which
fields are needed in each table, indicating the Primary Key (PK)
and Foreign Key (FK) fields in each table.
TASK: Include another table to separate information about
subjects from the student table. Create an attributes table and
ERD of the 2 tables.
Tbl Students
Tbl Subjects
Student ID PK
First name
Reg Group
Predicted Grades
Subject ID PK
Subject Title
Exam Board
Exam (%)
Student ID FK
GCSE Computing#BristolMet
Key Words
GCSE Computing#BristolMet
GCSE Computing#BristolMet

similar documents