Database Design and Development Validation and Relationships Starter

Database Design and Development Validation and Relationships

Starter Questions (back of jotter) 1. List the 5 attribute types for Database fields. 2. Fill in the blank: a collection of records is called a ______. 3. An _____ is a single person, place or thing.

Learning Intention �I will learn about database field validation.

Attribute Size �We can limit the amount of information a single attribute can store. �For example, a text attribute might be maximum 20 characters in length.

Validation �Validation is the process of checking that data entered into a system is of the correct type and structure �There are 4 types of validation:

Validation (cont) �Presence check - checks that data has been entered into the field – cannot be left blank �Restricted choice – user given a list of options to choose from. e. g. drop down menu

Validation (cont) �Field length check – specifies the number of characters – e. g. telephone number must be 11 characters long �Range check – sets the range of values allowed. e. g. 0 to 100 inclusive

Practical Tasks �DB Booklet - Practical Task 3

Success Criteria �I can describe and add validation rules to a database.

Learning Intention �I will learn about database table relationships.

Table Relationships Customer ID 2635 First Last Pet Name ID Brian Davies 789 1293 Sue Towns 156 1293 Sue Towns 157 9735 Barry Grey 258 9735 Barry Grey 260 Pet Name Scruff Type of Animal Labrado r Phoeb Syrian e Hamster Jango Syrian Hamster Killer English Bulldog Psycho French Bulldog

Table Relationships Customer ID 2635 First Last Pet Name ID Brian Davies 789 1293 Sue Towns 156 1293 Sue Towns 157 9735 Barry Grey 258 9735 Barry Grey 260 Pet Name Scruff Type of Animal Labrado r Phoeb Syrian e Hamster Jango Syrian Hamster Killer English Bulldog Psycho French Bulldog

Table Relationships �Storing all the data in one table is called a flat-file database. �Problems: ◦ repeated data ◦ slower to update (because of the repeated data) ◦ risk of errors when updating (update anomalies)

Table Relationships (cont) �Splitting one table into multiple tables solves these problems. �A database with multiple related tables is called a Relational Database.

Table Relationships Customer ID 2635 First Last Pet Name ID Brian Davies 789 1293 Sue Towns 156 1293 Sue Towns 157 9735 Barry Grey 258 9735 Barry Grey 260 Pet Name Scruff Type of Animal Labrado r Phoeb Syrian e Hamster Jango Syrian Hamster Killer English Bulldog Psycho French Bulldog

Table Relationships - Keys Pets Customer ID 2635 1293 9735 First Name Brian Sue Barry Last Name Davies Towns Grey Pet Type of Customer ID Name Animal ID 789 Scruff Labrador 2635 156 Phoeb Syrian 1293 e Hamster 157 Jango Syrian 1293 Hamster 258 Killer English 9735 Bulldog 260 Psych French o Bulldog 9735

Table Relationships - Keys �A field used to link two tables is called a key field. �It is called the primary key in the table in which the key field is unique (not repeated). �It is called the foreign key in the other table.

Which key field is which? PK Customers Customer ID PK 2635 1293 9735 First Name Brian Sue Barry Last Name Davies Towns Grey Pets Pet Type of Customer ID Name Animal ID FK 789 Scruff Labrador 2635 156 Phoeb Syrian 1293 e Hamster 157 Jango Syrian 1293 Hamster 258 Killer English 9735 Bulldog 260 Psych French o Bulldog 9735

Pupils Database Example

Practical Tasks �DB Booklet - Theory Tasks 1 & 2 �DB Booklet - Practical Task 4 �DB Booklet - Theory Task 3 �DB Booklet - Practical Task 5

Practical Tasks �DB Booklet - Theory Tasks 1 & 2 �DB Booklet - Practical Task 4 �DB Booklet - Theory Task 3 �DB Booklet - Practical Task 5

Success Criteria �I can create and describe relationships between tables in terms of the key fields.