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

 

Add bookings to table
Rate this post

    Comments are closed.