gistfile1.txt

CREATE TABLE Customer (  
    customer_id SMALLINT AUTO_INCREMENT PRIMARY KEY, 
    customer_name VARCHAR(50) NOT NULL, 
    driving_license VARCHAR(50) NOT NULL, 
    address VARCHAR(50) NOT NULL, 
    postcode VARCHAR(10) NOT NULL, 
    contact_number BIGINT UNIQUE 
);   

CREATE TABLE Staff ( 
    staff_id SMALLINT PRIMARY KEY, 
    role VARCHAR(20), 
    location VARCHAR(10) 
); 

CREATE TABLE Car ( 
    car_id SMALLINT PRIMARY KEY, 
    brand VARCHAR(15), 
    model VARCHAR(15) NOT NULL, 
    type VARCHAR(10) NOT NULL, 
    rental_price DECIMAL(6,2), 
    availability CHAR(1) 
); 

CREATE TABLE Payment ( 
    payment_id INT PRIMARY KEY, 
    payment_amount DECIMAL(10,2) NOT NULL, 
    payment_method VARCHAR(10) DEFAULT "CARD", 
    payment_date DATE NOT NULL, 
    card_type VARCHAR(10), 
    card_number BIGINT, 
    card_expiry_date DATE     
); 

CREATE TABLE Reviews ( 
    review_id INT AUTO_INCREMENT PRIMARY KEY, 
    comments VARCHAR(100), 
    review_stars TINYINT, 
    customer_id SMALLINT,
    FOREIGN KEY (customer_id) REFERENCES Customer(customer_id) 
);     

CREATE TABLE Rental_Details ( 
    rental_id INT AUTO_INCREMENT PRIMARY KEY, 
    start_rental_date DATE, 
    rental_duration TINYINT NOT NULL, 
    rental_amount DECIMAL(10,2) NOT NULL, 
    customer_id SMALLINT,
    car_id SMALLINT,
    payment_id INT,
    staff_id SMALLINT,
    FOREIGN KEY (customer_id) REFERENCES Customer(customer_id), 
    FOREIGN KEY (car_id) REFERENCES Car(car_id), 
    FOREIGN KEY (payment_id) REFERENCES Payment(payment_id), 
    FOREIGN KEY (staff_id) REFERENCES Staff(staff_id)
);

ALTER TABLE Customer AUTO_INCREMENT = 5001;
ALTER TABLE Reviews AUTO_INCREMENT = 22001; 
ALTER TABLE Rental_Details AUTO_INCREMENT = 10001;