· 7 years ago · Dec 18, 2018, 03:52 PM
1BEGIN;
2
3DROP TABLE IF EXISTS klienci;
4DROP TABLE IF EXISTS kompozycje;
5DROP TABLE IF EXISTS odbiorcy;
6DROP TABLE IF EXISTS zamowienia;
7DROP TABLE IF EXISTS klienci;
8
9CREATE TABLE klienci (
10 idklienta VARCHAR(10) PRIMARY KEY,
11 haslo VARCHAR(10) NOT NULL,
12 nazwa VARCHAR(40) NOT NULL,
13 miasto VARCHAR(40) NOT NULL,
14 kod CHAR(6) NOT NULL,
15 adres VARCHAR(40) NOT NULL,
16 email VARCHAR(40),
17 telefon VARCHAR(16) NOT NULL,
18 fax VARCHAR(16),
19 nip CHAR(13),
20 regon CHAR(9),
21 CHECK (LENGTH(haslo)>=4)
22) ENGINE=InnoDB DEFAULT CHARSET=utf8;
23
24CREATE TABLE kompozycje (
25 idkompozycji CHAR(5) PRIMARY KEY,
26 nazwa VARCHAR(40) NOT NULL,
27 opis VARCHAR(100),
28 cena NUMERIC(8,2),
29 minimum INT,
30 stan INT,
31 CHECK (cena>=40.00)
32) ENGINE=InnoDB DEFAULT CHARSET=utf8;
33
34CREATE TABLE odbiorcy (
35 idodbiorcy SERIAL PRIMARY KEY,
36 nazwa VARCHAR(40) NOT NULL,
37 miasto VARCHAR(40) NOT NULL,
38 kod CHAR(6) NOT NULL,
39 adres VARCHAR(40) NOT NULL
40) ENGINE=InnoDB DEFAULT CHARSET=utf8;
41
42CREATE TABLE zamowienia (
43 idzamowienia INT PRIMARY KEY,
44 idklienta VARCHAR(10) NOT NULL,
45 idodbiorcy SERIAL NOT NULL,
46 idkompozycji CHAR(5) NOT NULL,
47 termin DATE NOT NULL,
48 cena NUMERIC(8,2),
49 zaplacone BOOLEAN,
50 uwagi VARCHAR(200),
51 FOREIGN KEY(idklienta) REFERENCES klienci(idklienta),
52 FOREIGN KEY(idodbiorcy) REFERENCES odbiorcy(idodbiorcy),
53 FOREIGN KEY(idkompozycji) REFERENCES kompozycje(idkompozycji)
54) ENGINE=InnoDB DEFAULT CHARSET=utf8;
55
56CREATE TABLE zapotrzebowanie (
57 idzamowienia INT PRIMARY KEY,
58 termin DATE NOT NULL,
59 FOREIGN KEY(idzamowienia) REFERENCES zamowienia(idzamowienia)
60) ENGINE=InnoDB DEFAULT CHARSET=utf8;
61
62INSERT INTO klienci (idklienta,haslo,nazwa,miasto,kod,adres,email,telefon,fax,nip,regon) VALUES
63('C1','password','Lorem Ipsum','Kraków','30-059','al. Mickiewicza 30','','123456789','','',''),
64('C2','password','Lorem Ipsum','Kraków','30-059','al. Mickiewicza 30','email@example.com','123456789','','',''),
65('C3','password','Lorem Ipsum','Kraków','30-059','al. Mickiewicza 30','','123456789','','',''),
66('C4','password','Lorem Ipsum','Kraków','30-059','al. Mickiewicza 30','email@example.com','123456789','','',''),
67('C5','password','Lorem Ipsum','Kraków','30-059','al. Mickiewicza 30','email@example.com','123456789','','',''),
68('C6','password','Lorem Ipsum','Kraków','30-059','al. Mickiewicza 30','email@example.com','123456789','','',''),
69('C7','password','Lorem Ipsum','Kraków','30-059','al. Mickiewicza 30','','123456789','','',''),
70('C8','password','Lorem Ipsum','Kraków','30-059','al. Mickiewicza 30','','123456789','','',''),
71('C9','password','Lorem Ipsum','Kraków','30-059','al. Mickiewicza 30','email@example.com','123456789','','',''),
72('C10','password','Lorem Ipsum','Kraków','30-059','al. Mickiewicza 30','','123456789','','',''),
73('C11','password','Lorem Ipsum','Kraków','30-059','al. Mickiewicza 30','email@example.com','123456789','','',''),
74('C12','password','Lorem Ipsum','Kraków','30-059','al. Mickiewicza 30','email@example.com','123456789','','',''),
75('C13','password','Lorem Ipsum','Kraków','30-059','al. Mickiewicza 30','','123456789','','',''),
76('C14','password','Lorem Ipsum','Kraków','30-059','al. Mickiewicza 30','','123456789','','',''),
77('C15','password','Lorem Ipsum','Kraków','30-059','al. Mickiewicza 30','email@example.com','123456789','','','');
78
79INSERT INTO kompozycje (idkompozycji,nazwa,opis,cena,minimum,stan) VALUES
80('K0001','Lorem Ipsum','Lorem ipsum dolor sit amet, consectetur adipiscing elit. Duis vel rhoncus mauris, quis placerat enim.',45.00,3,20),
81('K0002','Lorem Ipsum','Lorem ipsum dolor sit amet, consectetur adipiscing elit. Duis vel rhoncus mauris, quis placerat enim.',45.00,4,13),
82('K0003','Lorem Ipsum','Lorem ipsum dolor sit amet, consectetur adipiscing elit. Duis vel rhoncus mauris, quis placerat enim.',45.00,7,15),
83('K0004','Lorem Ipsum','Lorem ipsum dolor sit amet, consectetur adipiscing elit. Duis vel rhoncus mauris, quis placerat enim.',45.00,1,55),
84('K0005','Lorem Ipsum','Lorem ipsum dolor sit amet, consectetur adipiscing elit. Duis vel rhoncus mauris, quis placerat enim.',45.00,14,74),
85('K0006','Lorem Ipsum','Lorem ipsum dolor sit amet, consectetur adipiscing elit. Duis vel rhoncus mauris, quis placerat enim.',45.00,2,124),
86('K0007','Lorem Ipsum','Lorem ipsum dolor sit amet, consectetur adipiscing elit. Duis vel rhoncus mauris, quis placerat enim.',45.00,7,11),
87('K0008','Lorem Ipsum','Lorem ipsum dolor sit amet, consectetur adipiscing elit. Duis vel rhoncus mauris, quis placerat enim.',45.00,4,256),
88('K0009','Lorem Ipsum','Lorem ipsum dolor sit amet, consectetur adipiscing elit. Duis vel rhoncus mauris, quis placerat enim.',45.00,2,66),
89('K0010','Lorem Ipsum','Lorem ipsum dolor sit amet, consectetur adipiscing elit. Duis vel rhoncus mauris, quis placerat enim.',45.00,8,52),
90('K0011','Lorem Ipsum','Lorem ipsum dolor sit amet, consectetur adipiscing elit. Duis vel rhoncus mauris, quis placerat enim.',45.00,9,35),
91('K0012','Lorem Ipsum','Lorem ipsum dolor sit amet, consectetur adipiscing elit. Duis vel rhoncus mauris, quis placerat enim.',45.00,10,11),
92('K0013','Lorem Ipsum','Lorem ipsum dolor sit amet, consectetur adipiscing elit. Duis vel rhoncus mauris, quis placerat enim.',45.00,11,15),
93('K0014','Lorem Ipsum','Lorem ipsum dolor sit amet, consectetur adipiscing elit. Duis vel rhoncus mauris, quis placerat enim.',45.00,3,66),
94('K0015','Lorem Ipsum','Lorem ipsum dolor sit amet, consectetur adipiscing elit. Duis vel rhoncus mauris, quis placerat enim.',45.00,2,18);
95
96INSERT INTO odbiorcy (nazwa,miasto,kod,adres) VALUES
97('Lorem Ipsum','Kraków','30-059','al. Mickiewicza 30'),
98('Lorem Ipsum','Kraków','30-059','al. Mickiewicza 30'),
99('Lorem Ipsum','Kraków','30-059','al. Mickiewicza 30'),
100('Lorem Ipsum','Kraków','30-059','al. Mickiewicza 30'),
101('Lorem Ipsum','Kraków','30-059','al. Mickiewicza 30'),
102('Lorem Ipsum','Kraków','30-059','al. Mickiewicza 30'),
103('Lorem Ipsum','Kraków','30-059','al. Mickiewicza 30'),
104('Lorem Ipsum','Kraków','30-059','al. Mickiewicza 30'),
105('Lorem Ipsum','Kraków','30-059','al. Mickiewicza 30'),
106('Lorem Ipsum','Kraków','30-059','al. Mickiewicza 30'),
107('Lorem Ipsum','Kraków','30-059','al. Mickiewicza 30'),
108('Lorem Ipsum','Kraków','30-059','al. Mickiewicza 30'),
109('Lorem Ipsum','Kraków','30-059','al. Mickiewicza 30'),
110('Lorem Ipsum','Kraków','30-059','al. Mickiewicza 30'),
111('Lorem Ipsum','Kraków','30-059','al. Mickiewicza 30');
112
113INSERT INTO zamowienia (idzamowienia,idklienta,idodbiorcy,idkompozycji,termin,cena,zaplacone,uwagi) VALUES
114(1,'C1',1,'K0001','2018-12-18',45.00,1,''),
115(2,'C2',2,'K0002','2018-12-18',45.00,1,''),
116(3,'C3',3,'K0003','2018-12-18',45.00,0,''),
117(4,'C4',4,'K0004','2018-12-18',45.00,1,''),
118(5,'C5',5,'K0005','2018-12-18',45.00,0,''),
119(6,'C6',6,'K0006','2018-12-18',45.00,0,''),
120(7,'C7',7,'K0007','2018-12-18',45.00,1,''),
121(8,'C8',8,'K0008','2018-12-18',45.00,1,''),
122(9,'C9',9,'K0009','2018-12-18',45.00,1,''),
123(10,'C10',10,'K0010','2018-12-18',45.00,0,''),
124(11,'C11',11,'K0011','2018-12-18',45.00,1,''),
125(12,'C12',12,'K0012','2018-12-18',45.00,0,''),
126(13,'C13',13,'K0013','2018-12-18',45.00,1,''),
127(14,'C14',14,'K0014','2018-12-18',45.00,1,''),
128(15,'C15',15,'K0015','2018-12-18',45.00,1,'');
129
130INSERT INTO zapotrzebowanie (idzamowienia,termin) VALUES
131(1,'2018-12-18'),
132(2,'2018-12-18'),
133(3,'2018-12-18'),
134(4,'2018-12-18'),
135(5,'2018-12-18'),
136(6,'2018-12-18'),
137(7,'2018-12-18'),
138(8,'2018-12-18'),
139(9,'2018-12-18'),
140(10,'2018-12-18'),
141(11,'2018-12-18'),
142(12,'2018-12-18'),
143(13,'2018-12-18'),
144(14,'2018-12-18'),
145(15,'2018-12-18');
146
147COMMIT;