-- A5

-- brands:cars; 1 brands:N cars; 1 cars:1 brands => 1:N
-- brands:extras; NO RELATION
-- cars:extras; 1 cars:N extras; 1 extras:N cars => N:M
-- brands, cars, extras, conn_cars_extras
-- references: cars->brands, conn->cars, conn->extras
-- CREATE: brands, cars, extras, conn
-- DROP: conn, extras, cars, brands

IF object_id('conn_cars_extras', 'U') is not null DROP TABLE conn_cars_extras;
IF object_id('extras', 'U') is not null DROP TABLE extras;
IF object_id('cars', 'U') is not null DROP TABLE cars;
IF object_id('brands', 'U') is not null DROP TABLE brands;
GO

CREATE TABLE brands (
	brand_id int primary key,
	brand_name nvarchar(100) -- +column constraints
	-- table constraints
	-- CONSTRAINT pk_brand PRIMARY KEY (brand_id, X)
);
CREATE TABLE cars (
	car_id int primary key,
	car_brand int NOT NULL references brands(brand_id),
	car_model nvarchar(100) NOT NULL,
	car_baseprice int NOT NULL
	-- CONSTRAINT fk_car_brand FOREIGN KEY (car_brand, X) REFERENCES brands(brand_id, Y)
);
CREATE TABLE extras (
	ext_id int primary key,
	ext_name nvarchar(100),
	ext_price int,
	CONSTRAINT chk_price CHECK (ext_price > 1000)
);
CREATE TABLE conn_cars_extras (
	conn_id int identity primary key,
	conn_car int references cars(car_id),
	conn_extra int references extras(ext_id),
	CONSTRAINT uq_conn_cars_extras UNIQUE (conn_car, conn_extra)
);
-- IDENTITY_INSERT
GO

INSERT INTO brands VALUES (1, 'BMW');
INSERT INTO brands VALUES (2, 'Audi');
INSERT INTO brands VALUES (3, 'Suzuki');

INSERT INTO cars VALUES (1, 1, '116d', 30000);
INSERT INTO cars VALUES (2, 1, 'X5', 60000);
INSERT INTO cars VALUES (3, 1, 'i8', 80000);
INSERT INTO cars VALUES (4, 2, 'A2', 40000);
INSERT INTO cars VALUES (5, 2, 'S4', 50000);
INSERT INTO cars VALUES (6, 3, 'Swift', 10000);
-- 3 brands, 6 cars, 6 extras ....
INSERT INTO extras VALUES (1, 'Sunroof', 2000),
							(2, 'Turbo engine', 6000),
							(3, 'Leather seats', 4000),
							(4, 'GPS Tracking', 1100),
							(5, 'Sports gearbox', 5000),
							(6, 'Super Insurance', 1001);
-- Swift => without extras, average number of extras => float
INSERT INTO conn_cars_extras (conn_car, conn_extra) VALUES 
	(1, 1), (1, 3), (1, 5), (2, 4), (3, 6), (4, 3), (4, 4), (5, 5);
GO

-- P3
SELECT brand_name, car_model, car_baseprice,
	isnull(ext_name, '= NO EXTRA =') as extra_name,
	isnull(convert(varchar, ext_price), '= NO EXTRA =') as extra_price
FROM cars 
	INNER JOIN brands ON (car_brand = brand_id)
	LEFT JOIN conn_cars_extras ON (conn_car = car_id)
	LEFT JOIN extras ON (conn_extra = ext_id);
-- decomposition, normalization
-- big table (redundancy+anomalies) => smaller tables (consistency)

-- P1 / d,c,e

BEGIN tran;

PRINT 'BEFORE UPDATE';

UPDATE extras 
	SET ext_price = 2*ext_price
	WHERE ext_price = (SELECT MIN(ext_price) FROM extras); 

UPDATE extras 
    SET ext_price = 2*ext_price 
    FROM extras INNER JOIN (SELECT min(ext_price) as minprice FROM extras) sub ON (extras.ext_price = minprice);

/*
UPDATE extras 
SET ext_price = 2*ext_price
FROM extras, (SELECT MIN(ext_price) as minPrice FROM extras) subQ
WHERE ext_price = minPrice;
*/

PRINT 'AFTER UPDATE';

DELETE FROM conn_cars_extras
	WHERE conn_car IN ( -- NOT =
		SELECT car_id
		FROM cars
		WHERE car_baseprice = (SELECT max(car_baseprice) FROM cars)
	);

DELETE FROM cars
	WHERE car_baseprice = (SELECT max(car_baseprice) FROM cars); 

PRINT 'AFTER DELETE';

-- SELECT * FROM cars; -- acId = Isolated
ROLLBACK;
-- SELECT * FROM cars;