Mr608Kj8

1#creamos la base de datos CREATE DATABASE IF NOT EXISTS base_tarea; 
2 
3#seleccionar la base de datos USE base_tarea; 
4 
5#creamos las tablas 
6 
7CREATE TABLE IF NOT EXISTS EMPLOYEES(  id_employees INT NOT NULL,     Name VARCHAR(45) NOT NULL,     LastName VARCHAR(45) NOT NULL,     Address VARCHAR(50) DEFAULT NULL,     City VARCHAR(30) DEFAULT NULL,     Phone VARCHAR(25) DEFAULT NULL,     CP CHAR(5) DEFAULT NULL,     EntryDate DATE,     Category ENUM('COOKS', 'ADMINISTRATION', 'WAITERS'), 
8    Salary DEC (6,2) NOT NULL,     PRIMARY KEY(id_employees) )ENGINE=INNODB; 
9 
10CREATE TABLE IF NOT EXISTS COOKS(  id_cooks INT NOT NULL,     Title VARCHAR(20) NOT NULL,     Speciality VARCHAR(20) NOT NULL,  PRIMARY KEY(id_cooks),     employees_id_employees INT NOT NULL,  CONSTRAINT fkcooks_employees     FOREIGN KEY(employees_id_employees)  REFERENCES employees(id_employees) )ENGINE=INNODB; 
11 
12CREATE TABLE IF NOT EXISTS ADMINISTRATION(  id_administration INT NOT NULL,     Position VARCHAR(20) NOT NULL,  PRIMARY KEY(id_administration),  employees_id_employees INT NOT NULL,  CONSTRAINT fkadministration_employees     FOREIGN KEY(employees_id_employees)  REFERENCES employees(id_employees) )ENGINE=INNODB; 
13 
14CREATE TABLE IF NOT EXISTS WAITERS(  id_waiters INT NOT NULL,      Turn VARCHAR(20) NOT NULL,  Years INT NOT NULL,  incharge_ID INT NOT NULL,  PRIMARY KEY(id_waiters),  CONSTRAINT fkwaiters_employees     FOREIGN KEY (incharge_ID)  REFERENCES employees(id_employees) )ENGINE=INNODB; 
15 
16CREATE TABLE IF NOT EXISTS DININGROOM(  CodeD INT NOT NULL,      Name VARCHAR(20) NOT NULL,  Capacity VARCHAR(20) NOT NULL,  NTables INT NOT NULL,  Location VARCHAR(20) NOT NULL,  PRIMARY KEY(CodeD) 
17)ENGINE=INNODB; 
18 
19CREATE TABLE IF NOT EXISTS TABLES(  CodeT INT NOT NULL,      CodeD INT NOT NULL,  Nseats INT NOT NULL,  waiter_ID INT NOT NULL,  PRIMARY KEY(CodeT),  CONSTRAINT fktables_employees     FOREIGN KEY (waiter_ID)  REFERENCES waiters(id_waiters),  CONSTRAINT fktables_diningroom     FOREIGN KEY (CodeD)  REFERENCES diningroom(CodeD) )ENGINE=INNODB; 
20 
21CREATE TABLE IF NOT EXISTS RESERVATIONS(  Nreservation INT NOT NULL,      Dtreservation INT NOT NULL,  Name VARCHAR(20) NOT NULL,  Dtreserved INT NOT NULL,     lunchdinner ENUM('lunch', 'dinner'),     Npeople INT NOT NULL,     Various VARCHAR(20) NOT NULL,  PRIMARY KEY(Nreservation) )ENGINE=INNODB; 
22 
23CREATE TABLE IF NOT EXISTS Make(  CodeT INT NOT NULL,      CodeD INT NOT NULL,  Nreservation INT NOT NULL,  NseatsR INT NOT NULL,  PRIMARY KEY(CodeT, CodeD, Nreservation),  CONSTRAINT fkmakes_employees     FOREIGN KEY (CodeT)  REFERENCES tables(CodeT),  CONSTRAINT fkmakes_tables     FOREIGN KEY (CodeD)  REFERENCES diningroom(CodeD),     CONSTRAINT fkmakes_diningroom     FOREIGN KEY (Nreservation)  REFERENCES reservations(Nreservation) )ENGINE=INNODB; 
24CREATE TABLE IF NOT EXISTS BILLS(  NBill INT NOT NULL,      DateB Date NOT NULL,  CodeT INT NOT NULL,  CodeD INT NOT NULL,  PRIMARY KEY(NBill),  CONSTRAINT fkbills_tables     FOREIGN KEY (CodeT)  REFERENCES tables(CodeT),  CONSTRAINT fkbills_diningroom     FOREIGN KEY (CodeD)  REFERENCES diningroom(CodeD) )ENGINE=INNODB; 
25 
26CREATE TABLE IF NOT EXISTS Include(  NBill INT NOT NULL,   CodeDs INT NOT NULL,     Units INT NOT NULL,  PRIMARY KEY(NBill, CodeDs),  CONSTRAINT fkinclude_bills     FOREIGN KEY (NBill)  REFERENCES bills(NBill),  CONSTRAINT fkinclude_diningroom     FOREIGN KEY (CodeDs)  REFERENCES diningroom(CodeD) )ENGINE=INNODB; 
27 
28CREATE TABLE IF NOT EXISTS DISHES(  CodeDs INT NOT NULL,     Name VARCHAR(20),  Description VARCHAR(40),     Type VARCHAR(20),     Price DEC(3,1),  PRIMARY KEY(CodeDs) )ENGINE=INNODB; 
29 
30CREATE TABLE IF NOT EXISTS SUPPLIERS(  CodeSp INT NOT NULL,  Address VARCHAR(30) DEFAULT NULL,     Zip CHAR(5) DEFAULT NULL,     Phone VARCHAR(25) DEFAULT NULL,     Fax VARCHAR(25) DEFAULT NULL,     Contact VARCHAR(30) DEFAULT NULL,  
31 PRIMARY KEY(CodeSp) )ENGINE=INNODB; 
32 
33CREATE TABLE IF NOT EXISTS PRODUCTS(  CodePr INT NOT NULL,  Description VARCHAR(20),     Stock INT NOT NULL,     BaseUnit INT NOT NULL,     PriceU INT NOT NULL,     Category VARCHAR(20),     CodeSp INT NOT NULL,  PRIMARY KEY(CodePr),  CONSTRAINT fkproducts_contain     FOREIGN KEY (CodeSp)  REFERENCES suppliers(CodeSp) )ENGINE=INNODB; 
34 
35CREATE TABLE IF NOT EXISTS Contain(  CodeDs INT NOT NULL,  CodePr INT NOT NULL,     Amount INT NOT NULL,  PRIMARY KEY(CodeDs, CodePr),  CONSTRAINT fkcontain_dishes     FOREIGN KEY (CodeDs)  REFERENCES dishes(CodeDs),  CONSTRAINT fkcontain_products     FOREIGN KEY (CodePr)  REFERENCES products(CodePr) )ENGINE=INNODB;