Exercises

Rental System

Peter runs a small car rental company with 10 cars and 5 trucks. He engages you to design a web portal to put his operation online.

For the initial phase, the web portal shall provide these basic functions:

  1. Maintaining the records of the vehicles and customers.
  2. Inquiring about the availability of vehicle, and
  3. Reserving a vehicle for rental.

A customer record contains his/her name, address and phone number.

A vehicle, identified by the vehicle registration number, can be rented on a daily basis. The rental rate is different for different vehicles. There is a discount of 20% for rental of 7 days or more.

A customer can rental a vehicle from a start date to an end date. A special customer discount, ranging from 0-50%, can be given to preferred customers.

Database

The initial database contains 3 tables: vehicles, customers, and rental_records. The rental_records is a junction table supporting many-to-many relationship between vehicles and customers.

DROP DATABASE IF EXISTS `rental_db`;
CREATE DATABASE `rental_db`;
USE `rental_db`;

-- Create `vehicles` table
DROP TABLE IF EXISTS `vehicles`;
CREATE TABLE `vehicles` (
   `veh_reg_no`  VARCHAR(8)    NOT NULL,
   `category`    ENUM('car', 'truck')  NOT NULL DEFAULT 'car',  
                 -- Enumeration of one of the items in the list
   `brand`       VARCHAR(30)   NOT NULL DEFAULT '',
   `desc`        VARCHAR(256)  NOT NULL DEFAULT '',
                 -- desc is a keyword (for descending) and must be back-quoted
   `photo`       BLOB          NULL,   -- binary large object of up to 64KB
                 -- to be implemented later
   `daily_rate`  DECIMAL(6,2)  NOT NULL DEFAULT 9999.99,
                 -- set default to max value
   PRIMARY KEY (`veh_reg_no`),
   INDEX (`category`)  -- Build index on this column for fast search
) ENGINE=InnoDB;
   -- MySQL provides a few ENGINEs.
   -- The InnoDB Engine supports foreign keys and transactions
DESC `vehicles`;
SHOW CREATE TABLE `vehicles` \G
SHOW INDEX FROM `vehicles` \G

-- Create `customers` table
DROP TABLE IF EXISTS `customers`;
CREATE TABLE `customers` (
   `customer_id`  INT UNSIGNED  NOT NULL AUTO_INCREMENT,
                  -- Always use INT for AUTO_INCREMENT column to avoid run-over
   `name`         VARCHAR(30)   NOT NULL DEFAULT '',
   `address`      VARCHAR(80)   NOT NULL DEFAULT '',
   `phone`        VARCHAR(15)   NOT NULL DEFAULT '',
   `discount`     DOUBLE        NOT NULL DEFAULT 0.0,
   PRIMARY KEY (`customer_id`),
   UNIQUE INDEX (`phone`),  -- Build index on this unique-value column
   INDEX (`name`)           -- Build index on this column
) ENGINE=InnoDB;
DESC `customers`;
SHOW CREATE TABLE `customers` \G
SHOW INDEX FROM `customers` \G

-- Create `rental_records` table
DROP TABLE IF EXISTS `rental_records`;
CREATE TABLE `rental_records` (
   `rental_id`    INT UNSIGNED  NOT NULL AUTO_INCREMENT,
   `veh_reg_no`   VARCHAR(8)    NOT NULL, 
   `customer_id`  INT UNSIGNED  NOT NULL,
   `start_date`   DATE          NOT NULL DEFAULT '0000-00-00',
   `end_date`     DATE          NOT NULL DEFAULT '0000-00-00',
   `lastUpdated`  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      -- Keep the created and last updated timestamp for auditing and security
   PRIMARY KEY (`rental_id`),
   FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`)
      ON DELETE RESTRICT ON UPDATE CASCADE,
      -- Disallow deletion of parent record if there are matching records here
      -- If parent record (customer_id) changes, update the matching records here
   FOREIGN KEY (`veh_reg_no`) REFERENCES `vehicles` (`veh_reg_no`)
      ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;
DESC `rental_records`;
SHOW CREATE TABLE `rental_records` \G
SHOW INDEX FROM `rental_records` \G

-- Inserting test records
INSERT INTO `vehicles` VALUES
   ('SBA1111A', 'car', 'NISSAN SUNNY 1.6L', '4 Door Saloon, Automatic', NULL, 99.99),
   ('SBB2222B', 'car', 'TOYOTA ALTIS 1.6L', '4 Door Saloon, Automatic', NULL, 99.99),
   ('SBC3333C', 'car', 'HONDA CIVIC 1.8L',  '4 Door Saloon, Automatic', NULL, 119.99),
   ('GA5555E', 'truck', 'NISSAN CABSTAR 3.0L',  'Lorry, Manual ', NULL, 89.99),
   ('GA6666F', 'truck', 'OPEL COMBO 1.6L',  'Van, Manual', NULL, 69.99);
   -- No photo yet, set to NULL
SELECT * FROM `vehicles`;

INSERT INTO `customers` VALUES
   (1001, 'Tan Ah Teck', '8 Happy Ave', '88888888', 0.1),
   (NULL, 'Mohammed Ali', '1 Kg Java', '99999999', 0.15),
   (NULL, 'Kumar', '5 Serangoon Road', '55555555', 0),
   (NULL, 'Kevin Jones', '2 Sunset boulevard', '22222222', 0.2);
SELECT * FROM `customers`;

INSERT INTO `rental_records` VALUES
  (NULL, 'SBA1111A', 1001, '2012-01-01', '2012-01-21', NULL),
  (NULL, 'SBA1111A', 1001, '2012-02-01', '2012-02-05', NULL),
  (NULL, 'GA5555E',  1003, '2012-01-05', '2012-01-31', NULL),
  (NULL, 'GA6666F',  1004, '2012-01-20', '2012-02-20', NULL);
SELECT * FROM `rental_records`;
Exercises

  1. Customer 'Tan Ah Teck' has rented 'SBA1111A' from today for 10 days. (Hint: You need to insert a rental record. Use a SELECT subquery to get the customer_id. Use CURDATE() (or NOW()) for today; and DATE_ADD(CURDATE(), INTERVAL x unit) to compute a future date.)

    INSERT INTO rental_records VALUES
   (NULL,
    'SBA1111A', 
    (SELECT customer_id FROM customers WHERE name='Tan Ah Teck'),
    CURDATE(),
    DATE_ADD(CURDATE(), INTERVAL 10 DAY),
    NULL);

  2. Customer 'Kumar' has rented 'GA5555E' from tomorrow for 3 months.

  3. List all rental records (start date, end date) with vehicle's registration number, brand, and customer name, sorted by vehicle's categories followed by start date.

    SELECT
   r.start_date  AS `Start Date`,
   r.end_date    AS `End Date`,
   r.veh_reg_no  AS `Vehicle No`,
   v.brand       AS `Vehicle Brand`,
   c.name        AS `Customer Name`
FROM rental_records AS r
   INNER JOIN vehicles  AS v USING (veh_reg_no)
   INNER JOIN customers AS c USING (customer_id)
ORDER BY v.category, start_date;

  4. List all the expired rental records (end_date before CURDATE()).

  5. List the vehicles rented out on '2012-01-10' (not available for rental), in columns of vehicle registration no, customer name, start date and end date. (Hint: the given date is in between the start_date and end_date.)
  6. List all vehicles rented out today, in columns registration number, customer name, start date, end date.
  7. Similarly, list the vehicles rented out (not available for rental) for the period from '2012-01-03' to '2012-01-18'. (Hint: start_date is inside the range; or end_date is inside the range; or start_date is before the range and end_date is beyond the range.)
  8. List the vehicles (registration number, brand and description) available for rental (not rented out) on '2012-01-10' (Hint: You could use a subquery based on a earlier query).
  9. Similarly, list the vehicles available for rental for the period from '2012-01-03' to '2012-01-18'.
  10. Similarly, list the vehicles available for rental from today for 10 days.
  11. Foreign Key Test:
    1. Try deleting a parent row with matching row(s) in child table(s), e.g., delete 'GA6666F' from vehicles table (ON DELETE RESTRICT).
    2. Try updating a parent row with matching row(s) in child table(s), e.g., rename 'GA6666F' to 'GA9999F' in vehicles table. Check the effects on the child table rental_records (ON UPDATE CASCADE).
    3. Remove 'GA6666F' from the database (Hints: Remove it from child table rental_records; then parent table vehicles.)

  12. Payments: A rental could be paid over a number of payments (e.g., deposit, installments, full payment). Each payment is for one rental. Create a new table called payments. Need to create columns to facilitate proper audit check (such as create_date, create_by, last_update_date, last_update_by, etc.)

    DROP TABLE IF EXISTS `payments`;
CREATE TABLE payments (
  `payment_id`  INT UNSIGNED  NOT NULL AUTO_INCREMENT,
  `rental_id`   INT UNSIGNED  NOT NULL,
  `amount`      DECIMAL(8,2)  NOT NULL DEFAULT 0,
  `mode`        ENUM('cash', 'credit card', 'check'),
  `type`        ENUM('deposit', 'partial', 'full') NOT NULL DEFAULT 'full',
  `remark`      VARCHAR(255),
  `created_date`       DATETIME      NOT NULL,
  `created_by`         INT UNSIGNED  NOT NULL,  -- staff_id
                      -- Use a trigger to update create_date and create_by automatically
  `last_updated_date`  TIMESTAMP     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                      -- Updated by the system automatically
  `last_updated_by`    INT UNSIGNED  NOT NULL,
                      -- Use a trigger to update created_by
  PRIMARY KEY (`payment_id`),
  INDEX       (`rental_id`),
  FOREIGN KEY (`rental_id`) REFERENCES rental_records (`rental_id`)
) ENGINE=InnoDB;
DESC `payments`;
SHOW CREATE TABLE `payments` \G
SHOW INDEX FROM `payments` \G

  13. Staff: Keeping track of staff serving the customers. Create a new staff table. Assume that each transaction is handled by one staff, we can add a new column called staff_id in the rental_records table,

    DROP TABLE IF EXISTS `staff`;
CREATE TABLE `staff` (
  `staff_id`   INT UNSIGNED  NOT NULL AUTO_INCREMENT, 
               -- Always use INT for AUTO_INCREMENT column to prvent run-over
  `name`       VARCHAR(30)   NOT NULL DEFAULT '',
  `title`      VARCHAR(30)   NOT NULL DEFAULT '',
  `address`    VARCHAR(80)   NOT NULL DEFAULT '',
  `phone`      VARCHAR(15)   NOT NULL DEFAULT '',
  `report_to`  INT UNSIGNED  NOT NULL,
               -- Reports to manager staff_id. Boss reports to himself
  PRIMARY KEY  (`staff_id`),
  UNIQUE INDEX (`phone`),  -- Build index on this unique-value column
  INDEX        (`name`),   -- Build index on this column
  FOREIGN KEY  (`report_to`) REFERENCES `staff` (`staff_id`)
     -- Reference itself
) ENGINE=InnoDB;
DESC `staff`;
SHOW INDEX FROM `staff` \G

INSERT INTO staff VALUE (8001, 'Peter Johns', 'Managing Director', '1 Happy Ave', '12345678', 8001);
SELECT * FROM staff;

-- Add a new column to rental_records table
ALTER TABLE `rental_records` ADD COLUMN `staff_id`  INT UNSIGNED  NOT NULL;
-- Need to set to a valid value, before adding the foreign key
UPDATE `rental_records` SET `staff_id` = 8001;
ALTER TABLE `rental_records` ADD FOREIGN KEY (`staff_id`) REFERENCES staff (`staff_id`) 
  ON DELETE RESTRICT ON UPDATE CASCADE;

SHOW CREATE TABLE `rental_records` \G
SHOW INDEX FROM `rental_records` \G

-- Also Add a new column to payments table
ALTER TABLE `payments` ADD COLUMN `staff_id`  INT UNSIGNED  NOT NULL;
-- Need to set to a valid value, before adding the foreign key
UPDATE `payments` SET `staff_id` = 8001;
ALTER TABLE `payments` ADD FOREIGN KEY (`staff_id`) REFERENCES staff (`staff_id`) 
  ON DELETE RESTRICT ON UPDATE CASCADE;

SHOW CREATE TABLE `payments` \G
SHOW INDEX FROM `payments` \G
Advanced Exercises

  1. Adding Photo: We could store photo in MySQL using data type of BLOB (Binary Large Object) (up to 64KB), MEDIUMBLOB (up to 16MBytes), LONGBOLB (up to 4GBytes). For example,

    -- Use function LOAD_FILE to load a picture file into a BLOB field
UPDATE vehicles SET photo=LOAD_FILE('d:/temp/car.jpg') WHERE veh_reg_no = 'SBA1111A';
SELECT * FROM vehicles WHERE veh_reg_no = 'SBA1111A' \G

    You can conveniently load and view the photo via graphical tools such as MySQL Workbench. To load a image in MySQL Workbench ⇒ right-click on the cell ⇒ Load Value From File ⇒ Select the image file. To view the image ⇒ right-click on the BLOB cell ⇒ Open Value in Editor ⇒ choose "Image" pane. I also include a Java program for reading and writing image BLOB from/to the database, based on this example: "TestImageBLOB.java".

  2. VIEW: Create a VIEW called rental_prices on the rental_records with an additional column called price. Show all the records of the VIEW.

    DROP VIEW IF EXISTS rental_prices;
CREATE VIEW **rental_prices**
AS
SELECT
   v.veh_reg_no    AS `Vehicle No`,
   v.daily_rate    AS `Daily Rate`,
   c.name          AS `Customer Name`,
   c.discount*100  AS `Customer Discount (%)`,
   r.start_date    AS `Start Date`,
   r.end_date      AS `End Date`,
   DATEDIFF(r.end_date, r.start_date) AS `Duration`,
   -- Compute the rental price
   -- Preferred customer has discount, 20% discount for 7 or more days
   -- CAST the result from DOUBLE to DECIMAL(8,2)
   CAST(
      IF (DATEDIFF(r.end_date, r.start_date) < 7,
          DATEDIFF(r.end_date, r.start_date)*daily_rate*(1-discount),
          DATEDIFF(r.end_date, r.start_date)*daily_rate*(1-discount)*0.8)
      AS DECIMAL(8,2)) AS price
FROM rental_records AS r
   INNER JOIN vehicles  AS v USING (veh_reg_no)
   INNER JOIN customers AS c USING (customer_id);

DESC `rental_prices`;
SHOW CREATE VIEW `rental_prices` \G

-- Try selecting all rows
SELECT * FROM `rental_prices`;

    It is probably easier to compute the price using a program/procedure, instead of inside the view.

  3. From the payments table, create a view to show the outstanding balance.
  4. Define more views.
  5. FUNCTION: Write a function to compute the rental price.
  6. Define more procedures and functions.
  7. TRIGGER: Write a trigger for the created_date and created_by columns of the payments table.
  8. Define more triggers.

  9. Implement discount on weekday (Monday to Friday, except public holiday): Need to set up a new table called public_hoilday with columns date and description. Use function DAYOFWEEK (1=Sunday, …, 7=Saturday) to check for weekday or weekend.

    -- pseudocode for calculating rental price
price = 0;
for each date from start_date to end_date {
   if date is weekend or public_holiday, price += daily_rate;
   else price += daily_rate*(1-discount);
}
if (duration >= 7) price *= (1 - long_duration_discount);
price *= (1 - perferred_customer_discount);

Product Sales Database

Database diagram

[TODO] Explanation

Link to MySQL References & Resources

Latest version tested: MySQL Community Server 5.6.20 Last modified: September, 2014

results matching ""

    No results matching ""