Chapter 13: Project

 

Great! We’ve covered all the fundamental concepts of MySQL. We are now ready to start working on our project. You are strongly encouraged to work through this project to help you gain a stronger grasp of the concepts covered.

 

The source code for the project can be downloaded at https://www.learncodingfast.com/sql.

 

Ready? Let’s do it!

 

About the Project

 

This project requires us to build a simple database to help us manage the booking process of a sports complex. The sports complex has the following facilities: 2 tennis courts, 2 badminton courts, 2 multi-purpose fields and 1 archery range. Each facility can be booked for a duration of one hour.

 

Only registered users are allowed to make a booking. After booking, the complex allows users to cancel their bookings latest by the day prior to the booked date. Cancellation is free. However, if this is the third (or more) consecutive cancellations, the complex imposes a $10 fine.

 

The database that we build should have the following elements:

 

Tables

 

members

pending_terminations

rooms

bookings

 

View

 

member_bookings

 

Stored Procedures

 

insert_new_member

delete_member

update_member_password

update_member_email

make_booking

update_payment

view_bookings

search_room

cancel_booking

 

Trigger...

Creating the Database

 

The first thing that we need to do is create a database for our project.

 

Launch MySQL Workbench and create a new file by clicking on File > New Query Tab. Save this file as sportsDB.sql (File > Save Script As... ).

 

We are going to create a database called sports_booking.

 

Enter the line

 

CREATE DATABASE sports_booking;

 

into sportsDB.sql and execute it.

 

If all goes well, you should see the message

 

1 row (s) affected

 

in the output window.

 

As we move forward with the project, you’ll be asked to perform various SQL tasks. Do remember to click on the ‘Execute Statement' button to execute your SQL statements after each task. This will allow you to check and correct any mistake as you go along.

 

In addition, you can also create another SQL file called drop.sql. If you make any mistake in your SQL code and need to make amendments, you can drop the...

Using the Database

 

Once you have created the sports_booking database, you need to tell MySQL that you want to use this database. Try doing that yourself (Hint: Refer to Chapter 2).

 

Adding Tables

 

Now, we are ready to add tables to our database. We need to add four tables: members, pending_terminations, rooms and bookings.

 

members

 

The members table has five columns:

 

id

This column stores the id of each member. The id is alphanumeric (VARCHAR(255) will be a good choice) and uniquely identifies each member (in other words, it is a primary key).

 

password

This column stores the password of each member. It is alphanumeric and cannot be null.

 

email

This column stores the email of each member. It is also alphanumeric and cannot be null.

 

member_since

This column stores the timestamp (consisting of the date and time) that a particular member is added to the table. It cannot be null and uses the NOW() function to get the current date and time as the DEFAULT value.

 

payment_due

This column stores the amount of balance that a member has to pay. The amount is in dollars and cents (e.g. 12.50). The column...

Inserting Data

 

Now, we need to add some values to our tables so that we have data to work with in the project later.

 

A PDF file containing data for these tables can be downloaded at https://www.learncodingfast.com/sql. The tables can also be found in Appendix B for easy reference.

 

members

 

For the members table, we have the following set of data:

 

 

 

Try inserting these data yourself. For instance, for the first row, the code would be

 

INSERT INTO members (id, password, email, member_since, payment_due) VALUES

('afeil', 'feil1988<3', '[e-mail]', '2017-04-15 12:10:13', 0);

 

Although member_since and payment_due both have default values, we’ll overwrite them here so that we have some variety in our data to play with later. In addition, note that we enclosed the member_since value in quotation marks. In general, we need to do that for TIMESTAMP, DATETIME...

View

 

Now that we have created the tables and inserted some data, we are ready to select data from our tables.

 

Specifically, we’ll create a view that shows us all the booking details of a booking.

 

If you refer to the bookings table created previously, you can see that it lists the id, room_id, booked_date, booked_time, member_id, datetime_of_booking and payment_status of each booking.

 

What if in addition to the information above, we are also interested to know what each of the room ids stand for? For instance, we may want to know what AR stands for. In addition, what if we also want to know the price of each room?

 

In order to obtain these information, we have to refer to the rooms table.

 

To simplify this process, we are now going to write a SELECT statement to combine the two tables into a single view. This view displays the id (from the bookings table), room_id, room_type, booked_date, booked_time, member_id, datetime_of_booking...

Stored Procedures

 

In this exercise, we will create a total of nine stored procedures. Before we start coding them, let’s first change the delimiter by adding the line

 

DELIMITER $$

 

to our sportsDB.sql file.

 

We’ll code all the stored procedures after this line; there is no need to change the delimiter back to a semi-colon after each procedure. We’ll change the delimiter back after we finish coding all the procedures and functions.

 

Ready?

 

insert_new_member

 

The first stored procedure is for inserting a new member into the members table.

 

If you study the structure of the members table, you can see that it has a total of 5 columns: id, password, email, member_since and payment_due.

 

As the last two columns have default values, we only need to provide values for the first three columns when inserting a new member.

 

To do that, let’s create a stored procedure called insert_new_member...

Trigger

 

Now that we have finished coding our stored procedures, let’s move on to triggers. We’ll only be coding one trigger - payment_check.

 

This trigger checks the outstanding balance of a member, which is recorded in the payment_due column of the members table.

 

If payment_due is more than $0 and the member terminates his/her account, we’ll transfer the data to the pending_terminations table. This table records all the termination requests that are pending due to an outstanding payment.

 

Let’s first declare the trigger as follows:

 

CREATE TRIGGER payment_check BEFORE DELETE ON members FOR EACH ROW

 

As you can see, this trigger is activated when we try to delete a record from the members table.

 

Next, between the BEGIN and END $$ markers, we need to do a few things:

 

First, we need to declare a local variable called v_payment_due. The data type of v_payment_due matches that of the payment_due...

Stored Function

 

The final thing that we need to code is the check_cancellation function.

 

This function checks the number of consecutive cancellations made by the member who's trying to cancel a booking. It has one parameter p_booking_id whose data type matches that of the id column in the bookings table. In addition, it returns an integer and is deterministic.

 

Try declaring this function yourself. You can refer to Chapter 10 for reference on declaring a function.

 

Within the function (between the BEGIN and END $$ markers), we need to use a cursor to loop through the bookings table vertically. To begin, let’s first declare three local variables called v_done, v_cancellation and v_current_payment_status.

 

Both v_done and v_cancellation are of INT type.

 

v_current_payment_status, on the other hand, has a data type that matches the data type of the payment_status column in the bookings table.

 

Next, we need to use...

Testing the Database

 

To test our database, let’s create a new SQL file called test.sql. We’ll do all our testing on this file.

 

First, we’ll check if our tables are created correctly. Try executing the following statements to check if the tables are correct:

 

SELECT * FROM members;

SELECT * FROM pending_terminations;

SELECT * FROM bookings;

SELECT * FROM rooms;

 

members, bookings and rooms should contain the same information as the respective tables shown in Appendix B.

 

pending_terminations, on the other hand, should be empty.

 

Got it? Good!

 

Next, let’s try the insert_new_member procedure. Try executing the following statements:

 

CALL insert_new_member ('angelolott', '1234abcd', '[e-mail]');

 

SELECT * FROM members ORDER BY member_since DESC;

 

You should get the table below. The new member added is shown on the first row of the table...