Creating a Relational Database (SQL)
We will be using SQL to create our relational Databases. The following thumbnail videos will go through creating the furniture and designer database that we have used earlier in the site (link).
1 - Copying the DB.mp41 - Creating the database
This video will make a blank database
2 - Creating Designer Table.mp42 - Designer Table Field Types and Sizes
This video will create the designer table and set the field types and sizes.
4 - Adding Designer validation.mp43 - Setting the Designer Table Validation
This video will show you how to set the appropriate validation checks in the table (length check).
3 - Creating Item Table.mp44 - Setting Item Table types and sizes
This video will create the Item Table setting the field names and types.
5 - Adding Item validation.mp45 - Item Table Validation
This table will add set the Primary and Foreign Keys and then add the validation rules (length, range and restricted choices).
6 - Testing Designer Validation.mp46 - Testing the Designer Table
We will now test the Designer Table to test that the validation rules we added.
7 - Testing Item Validation.mp47 - Testing the Item Table
We will now test the Item Table to test that the validation rules we added.
8 - Dates time and Boolean.mp48 - Dates, times and Boolean fields
This video looks at how to use Date, Time and Boolean fields.
Creating a Database Table in SQL
Although covered in the videos the code for creating a SQL database is shown below:
The CREATE TABLE statement allows you to:
Create a new table
Define the fields
Set any primary/foreign keys
Add any validation
Example Create Statement (without validation)
SQL Data Types
Tthe SQL field types you are expected to use a quick reminder is shown below.
TEXT (or VARCHAR)- used to store text
INT - stores integer values (use this for a boolean field - 0 means false and 1 means 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
Setting the Primary Key
This specifies that a primary key of one field (or more) has to be unique:
fieldName dataType PRIMARY KEY
Validation
When creating a database you can add suitable validation to ensure your data is correct.
Validation - Presence Check
This is specified in the field declaration and ensures that a field contains a value and cannot be empty.
fieldName dataType NOT NULL
An example would be:
Validation - Restricted Choice
The SQL code below would implement a restricted choice ensuring that the option entered is a valid one contained in the specified values.
Validation - Range Check
The SQL code below would implement a range check.
Validation - Length Check
Foreign Key
The Foreign Key constraint ensures that a foreign key is a non null primary key in another table (referential integrity).
When creating a field you can declare the field type and then assign it as shown below.
fieldName dataType FOREIGN KEY REFERENCES tableName (fieldName)
Or at the end of the CREATE statement you can list the foreign key(s).