Project

General

Profile

scriptMySQL.txt

João Gonçalo Peixe Ribeiro, 06/10/2016 07:35 PM

Download (5.38 KB)

 
1
CREATE TABLE CLIENT(
2
   C_id             INT            NOT NULL    AUTO_INCREMENT,
3
   C_username       VARCHAR(50)    NOT NULL,
4
   C_password       VARCHAR(40)    NOT NULL,
5
   C_email          VARCHAR(50),
6
   PRIMARY KEY ( C_id )
7
);
8

    
9
CREATE TABLE PRODUCT(
10
   P_id             INT               NOT NULL    AUTO_INCREMENT,
11
   P_name           VARCHAR(50)       NOT NULL,
12
   P_price          DECIMAL (5,2)      NOT NULL,   
13
   P_volume         INT(4)            NOT NULL,
14
   P_stock          BIGINT(225)    NOT NULL,
15
   P_description    VARCHAR(50),
16
   V_id             INT,
17
   PRIMARY KEY ( P_id )
18
   FOREIGN KEY ( V_id ) REFERENCES VENDOR (V_id)
19
   );
20

    
21
CREATE TABLE VENDOR(
22
   V_id      INT               NOT NULL    AUTO_INCREMENT,
23
   V_name    VARCHAR(50)       NOT NULL,
24
   V_email   VARCHAR(50),
25
   PRIMARY KEY ( V_id )
26
);
27

    
28
CREATE TABLE SUPPLIER(
29
   S_id      	INT               NOT NULL    AUTO_INCREMENT,
30
   S_name    	VARCHAR(50)       NOT NULL,
31
   S_email   	VARCHAR(50)	  NOT NULL,
32
   S_password   VARCHAR(50)       NOT NULL,
33
   PRIMARY KEY ( S_id )
34
);
35

    
36
CREATE TABLE ORDERS(
37
   O_id          INT               NOT NULL    AUTO_INCREMENT,
38
   O_quantity    VARCHAR(50)       NOT NULL,
39
   O_delivered   TINYINT (1)	   NOT NULL,
40
   PRIMARY KEY ( O_id )
41
);
42

    
43
CREATE TABLE ORDERS_CLIENT(
44
   OC_id   INT		NOT NULL    AUTO_INCREMENT,
45
   C_id 	INT,
46
   O_id	INT,
47
   FOREIGN KEY ( C_id ) REFERENCES CLIENT (C_id)
48
   FOREIGN KEY ( O_id ) REFERENCES ORDERS (O_id)
49

    
50
);
51

    
52
CREATE TABLE ORDERS_PRODUT(
53
   OP_id   INT		NOT NULL    AUTO_INCREMENT,
54
   P_id 	INT,
55
   O_id	INT,
56
   FOREIGN KEY ( P_id ) REFERENCES PRODUCT (P_id)
57
   FOREIGN KEY ( O_id ) REFERENCES ORDERS (O_id)
58
);
59

    
60
CREATE TABLE PRODUCT_SUPLLIER(
61
   PS_id   INT		NOT NULL    AUTO_INCREMENT,
62
   P_id 	INT,
63
   S_id	INT,
64
   FOREIGN KEY ( S_id ) REFERENCES SUPPLIER (S_id)
65
   FOREIGN KEY ( P_id ) REFERENCES PRODUCT (P_id)
66
);
67

    
68

    
69
INSERT into CLIENTS (C_id, C_username, C_password, C_email) values (101, "Joao", "passJoao", "mailJoao");
70
INSERT into CLIENTS (C_id, C_username, C_password, C_email) values (102, "Tiago", "passTiago", "mailTiago"); 
71
INSERT into CLIENTS (C_id, C_username, C_password, C_email) values (103, "Rafa", "passRafa", "mailRafa"); 
72
INSERT into CLIENTS (C_id, C_username, C_password, C_email) values (104, "Catarina", "passCatarina", "mailCatarina"); 
73
INSERT into CLIENTS (C_id, C_username, C_password, C_email) values (105, "Monica", "passMonica", "mailMonica"); 
74
 
75
INSERT into PRODUCT (P_id, P_name, P_price, P_volume, P_stock, P_description) values (201, "One edt", 65.35, 200, 900, "Muito bom");
76
INSERT into PRODUCT (P_id, P_name, P_price, P_volume, P_stock, P_description) values (202, "for Her", 80.35, 500, 800, "bom"); 
77
INSERT into PRODUCT (P_id, P_name, P_price, P_volume, P_stock, P_description) values (203, "for Him", 75.00, 300, 700, "bonzito"); 
78
INSERT into PRODUCT (P_id, P_name, P_price, P_volume, P_stock, P_description) values (204, "one million", 68.50, 100, 850, "excelente"); 
79
INSERT into PRODUCT (P_id, P_name, P_price, P_volume, P_stock, P_description) values (205, "amor amor", 65.35, 120, 950, "fraguancia");  
80

    
81
INSERT into ORDERS  (O_id, O_quantity, O_delivered) values (301, 5, 0);
82
INSERT into ORDERS  (O_id, O_quantity, O_delivered) values (302, 4, 0);
83
INSERT into ORDERS  (O_id, O_quantity, O_delivered) values (303, 3, 0);
84
INSERT into ORDERS  (O_id, O_quantity, O_delivered) values (304, 2, 1);
85
INSERT into ORDERS  (O_id, O_quantity, O_delivered) values (305, 1, 0);
86

    
87
INSERT into VENDOR(V_id, V_name, V_email) values (401, "Calvin Klein", "kleinMail");
88
INSERT into VENDOR(V_id, V_name, V_email) values (402, "Paco Rabbann", "pacoMail");
89
INSERT into VENDOR(V_id, V_name, V_email) values (403, "Dolce & Gabana", "dgMail");
90
INSERT into VENDOR(V_id, V_name, V_email) values (404, "Carolina Herrerra", "chMail");
91
INSERT into VENDOR(V_id, V_name, V_email) values (405, "Giogio Armani", "armaniMail");
92
 
93
INSERT INTO SUPPLIER(S_id, S_name, S_email) values (501, "El Corte Ingles", "corteingles", "passCorte");
94
INSERT INTO SUPPLIER(S_id, S_name, S_email) values (502, "Jumbo", "jumbo", "passJumbo");
95
INSERT INTO SUPPLIER(S_id, S_name, S_email) values (503, "Perfumaria Aveiro", "paveiro","passAveiro");
96
INSERT INTO SUPPLIER(S_id, S_name, S_email) values (504, "Parfois", "parfois","passParfois");
97
INSERT INTO SUPPLIER(S_id, S_name, S_email) values (505, "Perfumaria da Ria", "pdaria","passRia");
98

    
99
INSERT INTO ORDERS_CLIENT (C_id, O_id) values (101, 301);
100
INSERT INTO ORDERS_CLIENT (C_id, O_id) values (102, 302);
101
INSERT INTO ORDERS_CLIENT (C_id, O_id) values (103, 303);
102
INSERT INTO ORDERS_CLIENT (C_id, O_id) values (104, 304);
103
INSERT INTO ORDERS_CLIENT (C_id, O_id) values (105, 305);
104

    
105
INSERT INTO ORDERS_PRODUCT (O_id, P_id) values (301, 201);
106
INSERT INTO ORDERS_PRODUCT (O_id, P_id) values (302, 202);
107
INSERT INTO ORDERS_PRODUCT (O_id, P_id) values (303, 203);
108
INSERT INTO ORDERS_PRODUCT (O_id, P_id) values (304, 204);
109
INSERT INTO ORDERS_PRODUCT (O_id, P_id) values (305, 205);
110

    
111
INSERT INTO PRODUCT_SUPLLIER (S_id, P_id) values (501, 205);
112
INSERT INTO PRODUCT_SUPLLIER (S_id, P_id) values (502, 204);
113
INSERT INTO PRODUCT_SUPLLIER (S_id, P_id) values (503, 203);
114
INSERT INTO PRODUCT_SUPLLIER (S_id, P_id) values (504, 202);
115
INSERT INTO PRODUCT_SUPLLIER (S_id, P_id) values (505, 201);
116

    
117
Select * from CLIENT;
118
Select * from PRODUCT;
119
Select * from ORDERS;
120
Select * from VENDOR;
121
Select * from SUPPLIER;