07-mysql

CREATE TABLE `employees` (
`id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
`first_name` VARCHAR(45) NOT NULL,
`last_name` VARCHAR(45) NOT NULL,
`title` VARCHAR(45) NOT NULL,
`notes` VARCHAR(45) NULL);

CREATE TABLE `customers` (
`account_number` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
`first_name` VARCHAR(45) NOT NULL,
`last_name` VARCHAR(45) NOT NULL,
`phone_number` VARCHAR(45) NOT NULL,
`emergency_name` VARCHAR(45) NOT NULL,
`emergency_number` INT NOT NULL,
`notes` VARCHAR(45) NULL);

CREATE TABLE `room_status` (
`room_status` TINYINT PRIMARY KEY NOT NULL,
`notes` VARCHAR(45) NULL);

CREATE TABLE `room_types` (
`room_type` VARCHAR(20) PRIMARY KEY NOT NULL,
`notes` VARCHAR(45) NULL);

CREATE TABLE `bed_types` (
`bed_type` VARCHAR(20) PRIMARY KEY NOT NULL,
`notes` VARCHAR(45) NULL);

CREATE TABLE `rooms` (
`room_number` INT PRIMARY KEY NOT NULL,
`room_type` VARCHAR(45) NOT NULL,
`bed_type` VARCHAR(45) NOT NULL,
`rate` DECIMAL NOT NULL,
`room_status` TINYINT NOT NULL,
`note` VARCHAR(45) NULL);

CREATE TABLE `payments` (
`id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
`employee_id` INT NOT NULL,
`payment_date` DATE NOT NULL,
`account_number` INT NOT NULL,
`first_date_occupied` DATE NOT NULL,
`last_date_occupied` DATE NOT NULL,
`total_days` INT NOT NULL,
`amount_charged` DECIMAL NOT NULL,
`tax_rate` DECIMAL NOT NULL,
`tax_amount` DECIMAL NOT NULL,
`payment_total` DECIMAL NOT NULL,
`notes` VARCHAR(45) NULL);

CREATE TABLE `occupancies` (
`id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
`employee_id` INT NOT NULL,
`date_occupied` DATE NOT NULL,
`account_number` INT NOT NULL,
`room_number` INT NOT NULL,
`rate_applied` DECIMAL NOT NULL,
`phone_charge` DECIMAL NOT NULL,
`notes` VARCHAR(45) NULL);

INSERT INTO `employees` (`id`, `first_name`, `last_name`, `title`) VALUES
('1', 'ab', 'ab', 'ab'),
('2', 'ab', 'ab', 'ab'),
('3', 'ab', 'ab', 'ab');

INSERT INTO `customers` (`account_number`, `first_name`, `last_name`, `phone_number`, `emergency_name`, `emergency_number`) VALUES
('1', 'ab', 'ab', 'ab', 'ab', '12'),
('2', 'ab', 'ab', 'ab', 'ab', '12'),
('3', 'ab', 'ab', 'ab', 'ab', '12');

INSERT INTO `room_status` (`room_status`) VALUES
('1'),
('0');

INSERT INTO `room_types` (`room_type`) VALUES
('ab'),
('bc');

INSERT INTO `bed_types` (`bed_type`, `notes`) VALUES
('ab', 'ab'),
('bc', 'ab');

INSERT INTO `rooms` (`room_number`, `room_type`, `bed_type`, `rate`, `room_status`) VALUES
('1', 'ab', 'ab', '1.2', '1'),
('2', 'ab', 'ab', '1.2', '1'),
('3', 'ab', 'ab', '1.2', '1');

INSERT INTO `payments` (`id`, `employee_id`, `payment_date`, `account_number`, `first_date_occupied`, `last_date_occupied`, `total_days`, `amount_charged`, `tax_rate`, `tax_amount`, `payment_total`) VALUES
('1', '12', '1999-1-1', '12', '1999-1-1', '1999-1-1', '12', '1.2', '1.2', '1.2', '1.2'),
('2', '12', '1999-1-1', '12', '1999-1-1', '1999-1-1', '12', '1.2', '1.2', '1.2', '1.2'),
('3', '12', '1999-1-1', '12', '1999-1-1', '1999-1-1', '12', '1.2', '1.2', '1.2', '1.2');

INSERT INTO `occupancies` (`id`, `employee_id`, `date_occupied`, `account_number`, `room_number`, `rate_applied`, `phone_charge`) VALUES
('1', '12', '1999-1-1', '12', '12', '1.2', '1.2'),
('2', '13', '1999-1-1', '12', '12', '1.2', '1.2'),
('3', '14', '1999-1-1', '12', '12', '1.2', '1.2');