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.

CREATE TABLE Bookings (

);

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).

CREATE TABLE Bookings
(
Booking_ID INT NOT NULL PRIMARY KEY,
Cust_ID INT NOT NULL FOREIGN KEY REFERENCES Customer(Cust_ID),
NumberOfChairs INT CHECK (NumberOfChairs>=1)
);

 

 

Alter an entity

  • TRUNCATE
  • ALTER CONSTRANT

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“.

DROP TABLE Bookings PURGE;

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

1.1     SQL: INSERT INTO

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

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

 

The following table explains this SQL string in plain language:

Add to table Table name List of table attributes Data to follow User input
INSERT INTO Bookings (CustName, CustPhone) VALUES (‘Campbell’, ‘1234567’);

 

Task:

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)
  • VALUES
  • (‘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.

 

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
Smith 123456 17-10-17 0800 Home ST5 Work ST5 1 2
Jones 123456 17-10-17 0930 Home ST8 Work ST4 1 1
Campbell 123456 17-10-17 1500 Work ST3 Home ST2 0 2
Davies 123456 18-10-17 1000 Home ST15 Work ST1 0 1
Hill 123456 18-10-17 1100 Home ST10 Work ST3 0 1

 

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],

Notes:

  • 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”

Tutorial:

 

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
    1. INSERT INTO
  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