Database Design for Bicycle Rental System

This document outlines the database design for a bicycle rental system. The system manages branches, staff, bicycles, customers, and reservations. The database schema includes tables for branches, staff, staff pay, bicycle categories, bicycles, customers, reservations, and reservation details. Each table has appropriate attributes to store relevant information. The design ensures data integrity and efficiency for managing the rental process.

1.Table:
CREATE TABLE tbl_branch (
branch_id int PRIMARY KEY,
branch_code varchar(100),
street varchar(100),
city varchar(100),
postcode varchar(100),
)
2.Table: tbl_staff
CREATE TABLE tbl_staff (
staff_id int PRIMARY KEY,
branch_id int,
insurance_number varchar(100),
name varchar(100) ,
address varchar(250) ,
phone_number varchar(100) ,
email varchar(100) ,
paid_rate varchar(100)
)

3.Table: tbl_staff_paid
CREATE TABLE tbl_staff_paid (
staff_paid_id int PRIMARY KEY,
staff_id int ,
working_date varchar(100),
start_time varchar(100) ,
end_time varchar(100)
)

4.Table:tbl_bicycle_category
CREATE TABLE tbl_bicycle_category (
bicycle_category_id int PRIMARY KEY,
branch_id int,
no_of_bicycle varchar(100),
bicycle_category_title varchar(100),
)
5. Table: tbl_bicycle
CREATE TABLE tbl_bicycle (
bicycle_id int PRIMARY KEY,
bicycle_category_id int,
serial_number varchar(100),
model_no_of_bicycle varchar(100),
bicycle_name varchar(100),
bicycle_condition varchar(100)
)

6.Table : tbl_customer
CREATE TABLE tbl_customer (
customer_id int PRIMARY KEY,
branch_id int,
name varchar(100),
age varchar(100),
phone_number varchar(100),
street varchar(100),
city varchar(100),
postcode varchar(100)
)

7. Table : tbl_reservation
CREATE TABLE tbl_reservation (
reservation_id int PRIMARY KEY,
receipt_number varchar(100),
customer_id int,
bicycle_id int,
hired_date varchar(100),
return_date varchar(100),
deposit_amount varchar(100)
)

8.Table : tbl_ reservation_detail
CREATE TABLE tbl_reservation_detail (
reservation_detail_id int PRIMARY KEY,
reservation_id int,
late_fee varchar(100),
damage_fee varchar(100),
total_amount varchar(100)
)