Title / Description
Code USE greensauna ; # Drop Tables, Stored Procedures and Views DROP TABLE IF EXISTS designs ; DROP TABLE IF EXISTS control_units ; DROP TABLE IF EXISTS facings ; DROP TABLE IF EXISTS heaters ; DROP TABLE IF EXISTS customers ; DROP TABLE IF EXISTS cabins ; DROP TABLE IF EXISTS employees ; DROP TABLE IF EXISTS assembly_teams ; DROP TABLE IF EXISTS assemblies ; DROP TABLE IF EXISTS cars ; # Create Tables CREATE TABLE designs ( id INTEGER NOT NULL AUTO_INCREMENT, name VARCHAR(30) NOT NULL, active BOOL NOT NULL DEFAULT 1, PRIMARY KEY (id), UNIQUE UQ_designs_id(id), UNIQUE UQ_designs_name(name) ) ; CREATE TABLE control_units ( id INTEGER NOT NULL AUTO_INCREMENT, name VARCHAR(30) NOT NULL, active BOOL NOT NULL DEFAULT 1, PRIMARY KEY (id), UNIQUE UQ_control_units_id(id), UNIQUE UQ_control_units_name(name) ) ; CREATE TABLE facings ( id INTEGER NOT NULL AUTO_INCREMENT, name VARCHAR(30) NOT NULL, active BOOL NOT NULL DEFAULT 1, PRIMARY KEY (id), UNIQUE UQ_facings_id(id), UNIQUE UQ_facings_name(name) ) ; CREATE TABLE heaters ( id INTEGER NOT NULL AUTO_INCREMENT, name VARCHAR(30) NOT NULL, active BOOL NOT NULL DEFAULT 1, PRIMARY KEY (id), UNIQUE UQ_heaters_id(id), UNIQUE UQ_heaters_name(name) ) ; CREATE TABLE customers ( id INTEGER NOT NULL AUTO_INCREMENT, name VARCHAR(60) NOT NULL, contact VARCHAR(255) NULL, cabins_count INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (id), UNIQUE UQ_customers_id(id), UNIQUE UQ_customers_name(name) ) ; CREATE TABLE cabins ( id INTEGER NOT NULL AUTO_INCREMENT, address VARCHAR(255) NULL, note TEXT NULL, control_unit_id INTEGER NULL, design_id INTEGER NULL, facing_in_id INTEGER NULL, facing_out_id INTEGER NULL, customer_id INTEGER NULL, heater_id INTEGER NULL, assembly_id INTEGER NULL, PRIMARY KEY (id), UNIQUE UQ_cabins_id(id), KEY (assembly_id), KEY (facing_in_id), KEY (facing_out_id), KEY (control_unit_id), KEY (design_id), KEY (customer_id), KEY (heater_id) ) ; CREATE TABLE employees ( id INTEGER NOT NULL AUTO_INCREMENT, name VARCHAR(60) NOT NULL, assembly_team_id INTEGER NULL, PRIMARY KEY (id), UNIQUE UQ_employees_id(id), UNIQUE UQ_employees_name(name), KEY (assembly_team_id) ) ; CREATE TABLE assembly_teams ( id INTEGER NOT NULL AUTO_INCREMENT, name VARCHAR(60) NOT NULL, PRIMARY KEY (id), UNIQUE UQ_assembly_teams_id(id), UNIQUE UQ_assembly_teams_name(name) ) ; CREATE TABLE assemblies ( id INTEGER NOT NULL AUTO_INCREMENT, start_at DATE NOT NULL, end_at DATE NULL, assembly_team_id INTEGER NULL, car_id INTEGER NULL, PRIMARY KEY (id), UNIQUE UQ_assemblies_id(id), KEY (assembly_team_id), KEY (car_id) ) ; CREATE TABLE cars ( id INTEGER NOT NULL AUTO_INCREMENT, license_plate VARCHAR(7) NOT NULL, note VARCHAR(255) NULL, PRIMARY KEY (id), UNIQUE UQ_cars_id(id), UNIQUE UQ_cars_license_plate(license_plate) ) ; # Create Foreign Key Constraints ALTER TABLE cabins ADD CONSTRAINT FK_cabins_assemblies FOREIGN KEY (assembly_id) REFERENCES assemblies (id) ; ALTER TABLE cabins ADD CONSTRAINT FK_cabins_facings FOREIGN KEY (facing_in_id) REFERENCES facings (id) ; ALTER TABLE cabins ADD CONSTRAINT FK_cabins_facings FOREIGN KEY (facing_out_id) REFERENCES facings (id) ; ALTER TABLE cabins ADD CONSTRAINT FK_cabins_control_units FOREIGN KEY (control_unit_id) REFERENCES control_units (id) ; ALTER TABLE cabins ADD CONSTRAINT FK_cabins_designs FOREIGN KEY (design_id) REFERENCES designs (id) ; ALTER TABLE cabins ADD CONSTRAINT FK_cabins_customers FOREIGN KEY (customer_id) REFERENCES customers (id) ; ALTER TABLE cabins ADD CONSTRAINT FK_cabins_heaters FOREIGN KEY (heater_id) REFERENCES heaters (id) ; ALTER TABLE employees ADD CONSTRAINT FK_employees_assembly_teams FOREIGN KEY (assembly_team_id) REFERENCES assembly_teams (id) ; ALTER TABLE assemblies ADD CONSTRAINT FK_assemblies_assembly_teams FOREIGN KEY (assembly_team_id) REFERENCES assembly_teams (id) ; ALTER TABLE assemblies ADD CONSTRAINT FK_assemblies_cars FOREIGN KEY (car_id) REFERENCES cars (id) ;
Author
Highlight as C C++ CSS Clojure Delphi ERb Groovy (beta) HAML HTML JSON Java JavaScript PHP Plain text Python Ruby SQL XML YAML diff code