SQL Data Definition Language

SQL Data Definition Language is about creating the structure of your database. Typically, most people use the user-interface (SQL Developer) to create tables, triggers etc… However, it is very important for you to understand the SQL language which is been generated in the background as it helps you to see how the attributes, data types and relationships interact.

Creating an Entity (table)

Creating an entity is actually very simple once you understand some key principles which I will outline below. First, there is a wrap which encases all the attributes (columns) within the table, this wrap tells the database to create a table called “Bookings” and add the listed attributes.

CREATE TABLE Bookings ( Attribute list here );

The attribute list mirrors the Entity-Relation Diagram (ERD) as we can see in the example below. However, we need to consider additional factors which are not listed on the ERD such as CHECKs. I have used a table because it helps to structure your thinking and syntax.



If I now convert the above example into actual SQL code it would look the example below. Note that the “FOREIGN KEY REFERENCES Customer(Cust_ID)” component identifies the table name (Customer) and the required attribute (Cust_ID).

NumberOfChairs INT CHECK (NumberOfChairs>=1)



Alter an entity


Delete (Drop) an entity

Deleting tables is very simple but can cost you a lot if you get it wrong! If you simply use the “DROP TABLE Bookings” SQL command you will delete the table and all its data. However, it will still be in your recycle bin so you will need to add the “PURGE” command, like below. If you do not use the “PURGE” command you can recover your entity by using the “FLASHBACK TABLE“.


There is an added problem if there are other entities linked to the Booking table so we need to use the “CASCADE CONSTRAINTS“.  Need o FINISH

Add bookings to table

Business Rules:

  • The operator must be able to create a booking
  • Each booking must be linked to one driver


INSERT INTO tells the database to add (create a new record) the following data to a database table. For example:

INSERT INTOBookings(CustName, CustPhone)VALUES(‘Campbell’, ‘1234567’);


The following table explains this SQL string in plain language:

Add to tableTable nameList of table attributesData to followUser input
INSERT INTOBookings(CustName, CustPhone)VALUES(‘Campbell’, ‘1234567’);



Add the data in the table below into your database table ‘bookings’ using the INSERT INTO SQL string. For example:

  • INSERT INTO booking
  • (C_Name, C_Phone, B_Date, S_Time, S_Location, S_PCode, F_Location, F_PCode , Ach, Driver_ID)
  • (‘Campbell’, ‘123456’, ‘17-10-17’, ‘0730’, ‘Home’, ‘ST2’, ‘Work’, ‘ST3’, ‘1’, ‘1’);

Note: there is no Booking_ID within the table below as this is automatically inserted by the trigger which you created it.




From this table (bookings) we can see that there is a many-to-one (N:1) relationship between the booking table and the driver table. We know this because the same Driver_ID is repeated in the Driver_ID column. In plain English, we can see that one booking has one driver but a driver can have many bookings. Another way of thinking about this is to say, ‘one row in the driver table can have many rows in the booking table’.

Figure 1) Many to One Entity Relationship


Creating a table called Bookings

Business Rules: Each booking must have

  • Booking_ID [PK, Integer]
  • customer name [varchar, 100],
  • customer phone number [varchar, 15],
  • date of booking [char, 10],
  • booking start time [char, 4],
  • booking start location [varchar, 200],
  • Start postcode [varchar, 8],
  • booking finish location [varchar, 200],
  • finish postcode [varchar, 8],
  • achieved [Char, 1, Default 0]
  • assigned driver [FK, Integer],


  • UK phone numbers are targeted to be less than 15 digits in length
  • UK Postcodes are up to 8 characters in length
  • Date is defined as “17-10-2017”
  • Time is defined as “1020”



Designing a Relational Database

Key Terms

This list of key words are explained using every day language.

  • Schema is the structure (architecture) of the whole database
  • Entity is a table
  • Attribute is a column
  • Tuple is a row or record
  • Data Type is the type of data which will be stored in the attribute
  • Primary Key is a unique number value
  • Entity Integrity means every table has a primary key (Codd’s Rule 2)
  • Foreign Key is a primary key from a different table
  • Referential Integrity means tables are connected using primary key (Codd’s Rule 10)
  • Entity Relationship how two tables are connected
  • First Normal Form (1NF) group your attributes in a meaningful way and add a primary key
  • Second Normal From (2NF) remove all duplicate attributes and rows
  • Third Normal From (3NF) remove any attributes which can be created using a SQL query

Task One: What data do I need?

You have been asked to create a booking systems for a local taxi which will be used internally by the phone operator only.

  1. Make a list of attributes (data) which you will need
  2. Group the attributes in a logical way e.g. Booking, Drivers (1NF)
  3. Add a primary key to each group of attributes (1NF)
  4. Remove any duplicate attributes from all groups (2NF)



Introduction to SQL

Database Homepage

By working your way through all these tasks you will have a very good understanding of SQL queries and how to use them. As part of this tutorial you will create a very simple relational database, however, the tutorial can be used as a quick reference source too.


Table of Contents

  1. Database Connection
  2. Creating a table called Bookings
  3. Adding bookings to the table
  4. Search your booking table
    1. SELECT
  5. Create a table called diver
  6. Update your driver and booking data
    1. UPDATE
  7. View bookings list with the drivers details
    1. Table JOINS
  8. SQL: Single Row Functions
    1. SubStrings
    2. GroupBy
  9. Create a table called notes
  10. Three-way table joins
  11. Find a free taxi
  12. Driver completed job sheet