sFhpJ0UH

1/* KURSOVA RABOTA BD */
2
3
4/****** CREATE TABLE ******/
5
6/*
7DROP DATABASE IF EXISTS cars;
8CREATE DATABASE cars;
9USE cars;
10
11CREATE TABLE countries (
12	id INT NOT NULL AUTO_INCREMENT,
13    country_name VARCHAR(84) NOT NULL UNIQUE,
14    PRIMARY KEY( id )
15);
16
17CREATE TABLE brands (
18	id INT NOT NULL AUTO_INCREMENT,
19    brand_name VARCHAR(255) NOT NULL UNIQUE,
20    country_id INT NOT NULL,
21    CONSTRAINT FOREIGN KEY ( country_id ) REFERENCES countries(id),
22    PRIMARY KEY ( id )
23);
24
25CREATE TABLE models (
26	id INT NOT NULL AUTO_INCREMENT,
27    brand_id INT NOT NULL,
28    model_name VARCHAR(255) NOT NULL UNIQUE,
29    engine_code VARCHAR(50) NOT NULL,
30    horsepower SMALLINT UNSIGNED NOT NULL,
31    fuel_type ENUM('gas','diesel','electric','hybrid'),
32	CONSTRAINT FOREIGN KEY ( brand_id ) REFERENCES brands(id),
33    PRIMARY KEY ( id )
34);
35
36CREATE TABLE cars (
37	id INT NOT NULL AUTO_INCREMENT,
38    vin CHAR(17) NOT NULL UNIQUE,
39    license_plate VARCHAR(10) NOT NULL UNIQUE,
40    miles MEDIUMINT UNSIGNED NOT NULL,
41    made SMALLINT UNSIGNED NOT NULL,
42    active BOOLEAN NOT NULL DEFAULT 1,
43    model_id INT NOT NULL,
44    CONSTRAINT FOREIGN KEY ( model_id ) REFERENCES models(id),
45	PRIMARY KEY ( id )
46);
47
48CREATE TABLE people (
49	id INT NOT NULL AUTO_INCREMENT,
50    egn VARCHAR(20) NOT NULL UNIQUE,
51    first_name VARCHAR(50) NOT NULL,
52    surname VARCHAR(50) NOT NULL,
53    last_name VARCHAR(50) NOT NULL,
54    address VARCHAR(255) NOT NULL,
55    PRIMARY KEY ( id )
56);
57
58CREATE TABLE cars_owners (
59	car_id INT NOT NULL,
60    owner_id INT NOT NULL,
61    active_owner BOOLEAN NOT NULL DEFAULT 1,
62    PRIMARY KEY( car_id, owner_id ),
63    CONSTRAINT FOREIGN KEY ( car_id ) REFERENCES cars(id),
64    CONSTRAINT FOREIGN KEY ( owner_id ) REFERENCES people(id)
65);
66
67CREATE TABLE repair_shops (
68	id INT NOT NULL AUTO_INCREMENT,
69    company_name VARCHAR(255) NOT NULL,
70    address VARCHAR(255) NOT NULL UNIQUE,
71    bulstat CHAR(9) NOT NULL UNIQUE,
72    PRIMARY KEY ( id )
73);
74
75CREATE TABLE car_repairs (
76	repair_id INT NOT NULL AUTO_INCREMENT,
77	car_id INT NOT NULL,
78    shop_id INT NOT NULL,
79    repair_date DATE NOT NULL,
80    repair_description VARCHAR(255),
81    repair_cost DECIMAL(15,2) NOT NULL,
82    CONSTRAINT FOREIGN KEY ( car_id ) REFERENCES cars(id),
83    CONSTRAINT FOREIGN KEY ( shop_id ) REFERENCES repair_shops(id),
84    UNIQUE KEY ( car_id, repair_date ),
85    PRIMARY KEY ( repair_id )
86);
87 */
88
89/****** END CREATE TABLE ******/
90
91/****** INSERT DATA ******/
92
93/*
94INSERT INTO countries ( country_name ) 
95VALUES ( 'Germany' ), ( 'Japan') , ( 'USA' ), ( 'Italy' ), ( 'France' ), ( 'Romania' ), ( 'Russian Federation' ), ( 'UK' ); 
96
97INSERT INTO brands ( country_id, brand_name )
98VALUES ( 1, 'VW' ), ( 1, 'BMW' ), ( 1, 'Mercedes-Benz' ), ( 2, 'Honda' ), ( 2, 'Toyota' ), ( 3, 'Ford' ), ( 6, 'Dacia' );
99
100INSERT INTO models ( brand_id, model_name , engine_code , horsepower , fuel_type )
101VALUES ( 1, 'GOLF TDI' , 'ARL' , 150, 'diesel' ),
102		( 1, 'GOLF 1.8T' , 'AGU' , 150, 'gas' ),
103        ( 2, 'M3' , 'S65' , 414, 'gas' ),
104        ( 5, 'Prius' , '1NZ-FXE' , 90, 'hybrid' ),
105        ( 7, 'Sandero' , 'H4Bt' , 80, 'gas' );
106        
107INSERT INTO cars ( vin , license_plate , miles, made , model_id )
108VALUES ( '1FAFP58S11A177991', 'PK6666AM' , 10000, 2010, 3 ),
109		( 'JH4DB1641NS802336', 'CO1010BH' , 100000, 2004, 1 ), 
110        ( '1G4GD5EDXBF330171', 'PB3089BA' , 60000, 2009, 4 ), 
111        ( 'KL5VM52L54B110914', 'C000000' , 300000, 1999, 5 ),
112        ( 'WDBHM36E3VF540750', 'PK9999AH' , 50000, 2003, 1 ); */