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