ddl.sql - master - Apathy21/company

ddl.sql
83 строки3 KB
alexeev509
Skeletop company app realization
13 сен 2024, 19:21
13 сен 2024, 19:212a02e11
--две компании могут располагаться по одному и тому же адресу.
--может ли энам быть равен null ? - да

--docker run --name mysql -e MYSQL_ROOT_PASSWORD=594012 -p 3306:3306 -d f958d2869db0ad05333c6eb75ddd94a397846e65e841dc37c93120837ba3c769

--CREATE DATABASE company_db;
--USE company_db;
--mysql -u root -p

    -- if delete company -> branch_office mus be deleted;
ALTER DATABASE company_db CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE address (
	id BIGINT NOT NULL AUTO_INCREMENT,
	post_code INT(11),
	city VARCHAR(30) NOT NULL,
	avenue VARCHAR(70) NOT NULL,
	house TINYINT UNSIGNED NOT NULL,
	apartment INT(4),
	PRIMARY KEY (id)
);


CREATE TABLE company (
	id BIGINT NOT NULL AUTO_INCREMENT,
	address_id BIGINT,
	company_name VARCHAR(120) NOT NULL,
	legal_form ENUM('OOO', 'IP', 'AO'),
	PRIMARY KEY (id),
	FOREIGN KEY (address_id)
        REFERENCES address(id)
);


CREATE TABLE branch_office (
	id BIGINT NOT NULL AUTO_INCREMENT,
	bo_name VARCHAR(120) NOT NULL,
	address_id BIGINT NOT NULL,
	company_id BIGINT NOT NULL,
	PRIMARY KEY (id),
	FOREIGN KEY (address_id)
        REFERENCES address(id),
	FOREIGN KEY (company_id)
        REFERENCES company(id)
        ON DELETE CASCADE
);

--Добавление адресов
INSERT INTO address (post_code, city, avenue, house, apartment) VALUES 
(194017, "SPB", "Engelsa", 63, 3), (194017, "SPB", "Toreza", 92, null),(null, "SPB", "Enotaevskaia", 3, 2);

INSERT INTO address (post_code, city, avenue, house, apartment) VALUES 
(144017, "SPB", "Ozernaya", 44, 3), (144017, "SPB", "Politechnicheskaya", 25, 33),(144017, "SPB", "Lenina", 3, 2);

INSERT INTO address (post_code, city, avenue, house, apartment) VALUES 
(133566, "SPB", "Sovetskaya", 16, 66), (173566, "SPB", "Nevskii", 51, null),(103566, "SPB", "Nevskii", 52, 2);

INSERT INTO address (post_code, city, avenue, house, apartment) VALUES 
(194017, "SPB", "Engelsa", 61, 44), (666781, "SPB", "Moskovskaya avenue", 77, 2),(191234, "SPB", "Lomonosova", 3, 2);




-- Добавление компаний
INSERT INTO company (address_id, company_name, legal_form) VALUES 
(1, "Google", "OOO"), (2, "Amazon", "IP"),(2, "Microsoft", "OOO");

INSERT INTO company (address_id, company_name, legal_form) VALUES 
(3, "EPAM", "OOO"), (4, "Yandex", "IP"),(5, "DataArt", "OOO");

INSERT INTO company (address_id, company_name, legal_form) VALUES 
(6, "CRAZY_COMPANY", null), (7, "UNICREDIT", "OOO"),(8, "Telda", "AO");


-- Добавление филиалов
INSERT INTO branch_office (bo_name, address_id, company_id) VALUES 
("Google_1", 1, 1), ("Google_2", 9, 1), ("Google_3", 10, 1);

INSERT INTO branch_office (bo_name, address_id, company_id) VALUES 
("Amazon_1", 2, 2), ("Amazon_2", 11, 2), ("Amazon_3", 12, 2);

INSERT INTO branch_office (bo_name, address_id, company_id) VALUES 
("EPAM_1", 3, 3), ("DataArt_1", 2, 6);