WebProjectMerchStorePub/DBCreationScript.sql at master · ClassZak/WebProjectMerchStorePub · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
CREATE DATABASE MerchStoreWebPract;
USE MerchStoreWebPract;
#DROP DATABASE MerchStoreWebPract

CREATE TABLE Manufacturer(
	Id		INT PRIMARY KEY AUTO_INCREMENT,
	Name	VARCHAR(100) NOT NULL
);
ALTER TABLE Manufacturer ADD CONSTRAINT UC_Name UNIQUE (Name);


CREATE TABLE Good(
	Id				INT PRIMARY KEY AUTO_INCREMENT,
	Name			VARCHAR(100) NOT NULL,
	Description		LONGTEXT NOT NULL,
	Image			LONGBLOB NOT NULL,
	Price			DECIMAL(10,2) NOT NULL,
    AppearanceDate	DATE,
	IdManufacturer	INT
);
ALTER TABLE Good ADD CONSTRAINT CH_Price CHECK (Price > 0);

ALTER TABLE Good
ADD CONSTRAINT ManufacturerRef
FOREIGN KEY	(IdManufacturer)
REFERENCES	Manufacturer(Id);


CREATE TABLE GoodAtStorage(
	Id			INT PRIMARY KEY AUTO_INCREMENT,
	IdGood		INT NOT NULL,
	Quantity	INT UNSIGNED NOT NULL
);
ALTER TABLE GoodAtStorage
ADD CONSTRAINT goodRef
FOREIGN KEY(IdGood)
REFERENCES Good(Id);


CREATE TABLE Bank(
	Id			INT PRIMARY KEY AUTO_INCREMENT,
	Name		VARCHAR(100) NOT NULL,
	Description	VARCHAR(100)
);
CREATE TABLE BankAccount(
	Id			INT PRIMARY KEY AUTO_INCREMENT,
	IdBank		INT,
	BankNumber	VARCHAR(100)
);
ALTER TABLE BankAccount
ADD CONSTRAINT bankRef
FOREIGN KEY(IdBank)
REFERENCES Bank(Id);


CREATE TABLE OrderTable(
	Id				INT PRIMARY KEY AUTO_INCREMENT,
	IdGood			INT,
	IdBankAccount	INT,
	Quantity		INT UNSIGNED NOT NULL,
	OrderDateTime	DATETIME
);
ALTER TABLE OrderTable
ADD CONSTRAINT bankAccountRef
FOREIGN KEY(IdBankAccount)
REFERENCES BankAccount(Id);
ALTER TABLE OrderTable
ADD CONSTRAINT goodRefForTable
FOREIGN KEY(IdGood)
REFERENCES Good(Id);