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 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’);

 

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_NameC_PhoneB_DateS_TimeS_LocationS_PCodeF_LocationF_PCodeAchDriver_ID
Campbell12345617-10-170730HomeST2WorkST311
Smith12345617-10-170800HomeST5WorkST512
Jones12345617-10-170930HomeST8WorkST411
Campbell12345617-10-171500WorkST3HomeST202
Davies12345618-10-171000HomeST15WorkST101
Hill12345618-10-171100HomeST10WorkST301

 

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