Приложения

Приложение А. SQL – Скрипт создания базы данных.

/*

Created: 02.11.2015

Modified: 05.11.2015

Model: kurs

Database: MySQL 5.5

*/

-- Create tables section -------------------------------------------------

-- Table Services

CREATE TABLE `Services`

(

`ID` Int NOT NULL AUTO_INCREMENT,

`Name` Varchar(30) NOT NULL,

`Price` Int NOT NULL,

PRIMARY KEY (`ID`)

)

;

-- Table Clients

CREATE TABLE `Clients`

(

`ID` Int NOT NULL AUTO_INCREMENT,

`Firstname` Varchar(30) NOT NULL,

`Secondname` Varchar(30) NOT NULL,

`Middlename` Varchar(30),

`Birthday` Date NOT NULL,

`Registration` Varchar(100),

PRIMARY KEY (`ID`)

)

;

-- Table Auto

CREATE TABLE `Auto`

(

`ID` Int NOT NULL AUTO_INCREMENT,

`Brand` Varchar(30) NOT NULL,

`Color` Varchar(30) NOT NULL,

`Year` Int NOT NULL,

`Number` Varchar(10) NOT NULL,

`Owner` Int NOT NULL,

PRIMARY KEY (`ID`)

)

;

CREATE INDEX `IX_Relationship7` ON `Auto` (`Owner`)

;

-- Table Workers

CREATE TABLE `Workers`

(

`ID` Int NOT NULL AUTO_INCREMENT,

`Firstname` Varchar(30) NOT NULL,

`Secondname` Varchar(30) NOT NULL,

`Middlename` Varchar(30),

`Speciality` Varchar(30) NOT NULL,

`Rate` Int,

`Experience` Int,

PRIMARY KEY (`ID`)

)

;

-- Table Repair

CREATE TABLE `Repair`

(

`ID` Int NOT NULL AUTO_INCREMENT,

`Repaired` Varchar(300),

`Startdate` Datetime NOT NULL,

`Defect` Varchar(150) NOT NULL,

`Enddate` Datetime,

`Costofwork` Int,

`Costofparts` Int,

`Returndate` Datetime,

`Comment` Longtext,

`AutoID` Int NOT NULL,

`WorkerID` Int NOT NULL,

`ClientID` Int NOT NULL,

PRIMARY KEY (`ID`),

UNIQUE `ID` (`ID`)

)

;

CREATE INDEX `IX_Relationship2` ON `Repair` (`AutoID`)

;

CREATE INDEX `IX_Relationship4` ON `Repair` (`WorkerID`)

;

CREATE INDEX `IX_Relationship5` ON `Repair` (`ClientID`)

;

-- Table Service

CREATE TABLE `Service`

(

`ServiceID` Int,

`RepairID` Int,

`Time` Bigint NOT NULL DEFAULT 1

)

;

CREATE INDEX `IX_Relationship8` ON `Service` (`ServiceID`)

;

CREATE INDEX `IX_Relationship12` ON `Service` (`RepairID`)

;

-- Table Part

CREATE TABLE `Part`

(

`ID` Int NOT NULL AUTO_INCREMENT,

`Name` Varchar(100) NOT NULL,

`Price` Int,

PRIMARY KEY (`ID`),

UNIQUE `ID` (`ID`)

)

;

-- Table Parts

CREATE TABLE `Parts`

(

`RepairID` Int,

`PartID` Int,

`amount` Bigint NOT NULL DEFAULT 1

)

;

CREATE INDEX `IX_Relationship15` ON `Parts` (`RepairID`)

;

CREATE INDEX `IX_Relationship16` ON `Parts` (`PartID`)

;

-- Create relationships section -------------------------------------------------

ALTER TABLE `Repair` ADD CONSTRAINT `Relationship2` FOREIGN KEY (`AutoID`) REFERENCES `Auto` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION

;

ALTER TABLE `Repair` ADD CONSTRAINT `Relationship4` FOREIGN KEY (`WorkerID`) REFERENCES `Workers` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION

;

ALTER TABLE `Repair` ADD CONSTRAINT `Relationship5` FOREIGN KEY (`ClientID`) REFERENCES `Clients` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION

;

ALTER TABLE `Auto` ADD CONSTRAINT `Relationship7` FOREIGN KEY (`Owner`) REFERENCES `Clients` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION

;

ALTER TABLE `Service` ADD CONSTRAINT `Relationship8` FOREIGN KEY (`ServiceID`) REFERENCES `Services` (`ID`) ON DELETE CASCADE ON UPDATE NO ACTION

;

ALTER TABLE `Service` ADD CONSTRAINT `Relationship12` FOREIGN KEY (`RepairID`) REFERENCES `Repair` (`ID`) ON DELETE CASCADE ON UPDATE NO ACTION

;

ALTER TABLE `Parts` ADD CONSTRAINT `Relationship15` FOREIGN KEY (`RepairID`) REFERENCES `Repair` (`ID`) ON DELETE CASCADE ON UPDATE NO ACTION

;

ALTER TABLE `Parts` ADD CONSTRAINT `Relationship16` FOREIGN KEY (`PartID`) REFERENCES `Part` (`ID`) ON DELETE CASCADE ON UPDATE NO ACTION

;

Приложение Б. Исходный код программы

В виду большого объема исходного кода, исходный код приложен в электронном виде, в виде проекта NetBeans.