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