Service Report DB

service_report.sql

/* ---------------------------------------------------- */
/*  Generated by Enterprise Architect Version 13.5 		*/
/*  Created On : 19-Apr-2024 1:28:50 PM 				*/
/*  DBMS       : MySql 						*/
/* ---------------------------------------------------- */

SET FOREIGN_KEY_CHECKS=0 
;

/* Drop Tables */

DROP TABLE IF EXISTS `customers` CASCADE
;

DROP TABLE IF EXISTS `service_reports` CASCADE
;

DROP TABLE IF EXISTS `service_requests` CASCADE
;

DROP TABLE IF EXISTS `service_technicians` CASCADE
;

/* Create Tables */

CREATE TABLE `customers`
(
	`customer_id` INT NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(100) NULL,
	`email` VARCHAR(100) NULL,
	`phone_number` VARCHAR(50) NULL,
	CONSTRAINT `PK_Customers` PRIMARY KEY (`customer_id` ASC)
)
COMMENT = 'Stores information about customers who request services.'

;

CREATE TABLE `service_reports`
(
	`report_id` INT NOT NULL AUTO_INCREMENT,
	`request_id` INT NULL,
	`technician_id` INT NULL,
	`report_date` DATE NOT NULL,
	`work_description` TEXT NULL,
	`materials_used` TEXT NULL,
	`time_spent_hours` DECIMAL(5,2) NULL,
	`customer_signature` BLOB NULL,
	CONSTRAINT `PK_service_reports` PRIMARY KEY (`report_id` ASC)
)
COMMENT = 'Stores service reports generated by technicians, including the report date, work description, materials used, time spent, and customer signature as a binary data field (BLOB).'

;

CREATE TABLE `service_requests`
(
	`request_id` INT NOT NULL AUTO_INCREMENT,
	`customer_id` INT NULL,
	`request_date` DATE NOT NULL,
	`description` TEXT NULL,
	`status` VARCHAR(20) NULL DEFAULT Pending,
	CONSTRAINT `PK_service_request` PRIMARY KEY (`request_id` ASC)
)
COMMENT = 'Tracks service requests made by customers, including the request date, description, and status.'

;

CREATE TABLE `service_technicians`
(
	`technician_id` INT NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(100) NOT NULL,
	`email` VARCHAR(100) NULL,
	`phone_number` VARCHAR(50) NULL,
	CONSTRAINT `PK_service_technicians` PRIMARY KEY (`technician_id` ASC)
)
COMMENT = 'Contains details about service technicians who perform the service work.'

;

/* Create Primary Keys, Indexes, Uniques, Checks */

ALTER TABLE `service_reports` 
 ADD INDEX `IXFK_service_reports_service_requests` (`request_id` ASC)
;

ALTER TABLE `service_reports` 
 ADD INDEX `IXFK_service_reports_service_technicians` (`technician_id` ASC)
;

ALTER TABLE `service_requests` 
 ADD INDEX `IXFK_service_requests_customers` (`customer_id` ASC)
;

/* Create Foreign Key Constraints */

ALTER TABLE `service_reports` 
 ADD CONSTRAINT `FK_service_reports_service_requests`
	FOREIGN KEY (`request_id`) REFERENCES `service_requests` (`request_id`) ON DELETE Restrict ON UPDATE Restrict
;

ALTER TABLE `service_reports` 
 ADD CONSTRAINT `FK_service_reports_service_technicians`
	FOREIGN KEY (`technician_id`) REFERENCES  () ON DELETE Restrict ON UPDATE Restrict
;

ALTER TABLE `service_requests` 
 ADD CONSTRAINT `FK_service_requests_customers`
	FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`) ON DELETE Restrict ON UPDATE Restrict
;

SET FOREIGN_KEY_CHECKS=1 
;