Data Dictionary

The only difference from Data Dictionaries already covered at N5 and Higher are that we will look at using SQL data types.

When defining the table structure through SQL you are expected to be able to use the following SQL data types:

• VARCHAR - used to store text

• INT - stores integer values

  • There is a TINYINT type although not in the course that can be used to signify a Boolean value ( 0 False 1 True)

• FLOAT - used to store real numbers

• DATE - stores a date in the format YYYY-MM-DD

• TIME - stores a time in the format hh:mm:ss

  • There is also a DATETIME type although not in the course which is in the format YYYY-MM-DD HH:MM:SS

VARCHAR is a variable length string of a maximum of 65,535 bytes.

Example

username VARCHAR(35);

Would define a field called username with a maximum length of 35 characters.

There are two numeric SQL fields we will use:

• int

• float

Examples: 

age int ;

average float;

averagePrice float(4,2) 

• The first parameter is the total number of digits displayed and the second is the number of digits after the decimal point

As the Hotel and Booking tables contain Foreign Keys these tables will be shown last.

If there is no field that can be unique for every record then you can create a key.

For example consider the following fields in a pupil table:

• Firstname

• Surname

• Form Class

• Form teacher

• Date of birth

• Primary school

There is no unique field, it is even possible that there is a pupil with the same forename, surname and date of birth, so we may end up needing to use a compound key of more than 3 fields. 

As none of those fields are unique so we would create a new field to use as a primary key. Usually an ID number such as PupilID. This is known as a surrogate key.

This surrogate key has no real life meaning but is there purely to act as a primary key. It will usually be a numeric/alphanumeric field and surrogate keys will often use an auto increment value to ensure there is no duplication to ensure entity integrity.