N5 Revision

When we are designing the database we refer to entities and attributes. An entity is a collection of information about a particular object, person or thing. When we create a database an entity will usually become a table and attributes will become fields.

But before we can create the database we need to think about the data that we are going to store, this will allow us to create a data dictionary. This will allow us to design the characteristics that each attribute has so that we can make them into tables and fields.

A data dictionary will look at the following characteristics.

• Entity Name

• Attribute Type and Size

• Key

• Validation

Entity Name

When we design the entity we need to give it a descriptive name. Such as Dog, Customer, Item. It will usually be a singular term as opposed to plural.

Attribute Types

We have to describe the type of data that is being held for each attribute. The list below to demonstrates the types of attributes you were familiar with at National 5.

• Text

• Number

• Date

• Time

• Boolean

Attribute Size

When storing text you are required to tell the database how many characters of text you are going to store. This amount of space will usually be reserved for each record, so you want to store enough but not an excessive amount.

Key

As discussed earlier each entity will require a primary key and if it is part of a relationship it may also require a foreign key.

Ever noticed that when you are filling an online form that:

• Certain pieces of information can’t be left blank?

• Password has to be a certain length?

• Month has to be in a certain range?

• Or you can only choose your title/gender/country from a list?

These are all forms of validation. This is when we are trying to ensure that data is sensible and in the format that we expect.

The forms of validation we will look at are:

1. Presence Check

2. Restricted Choice

3. Length Check

4. Range Check

Presence Check

A presence check is when you are required to enter information into a field and it cannot be left blank. Such as the username when signing up for a new account.

Restricted Choice

Restricted choice validation is when you force the user to select from a list of pre-selected values.

• This ensures that the user can only enter data you wish them to choose from.

• Useful for genders or countries or any data that only had specific values.

Length Check

A length check can be used when a value (usually text or ID numbers) has to be a particular length. Can be useful for passwords etc.

Range Check

A range check is when a numerical or date field has to be in a particular range.

• Such as Age >=18

• Or to be born in 2001 the range would be: Age >= 1/1/2001 AND Age <=31/12/2001