DROP TABLE marco;
DROP TABLE ventas;
DROP TABLE distribucion;
DROP TABLE clientes;
DROP TABLE concesionarios;
DROP TABLE coches;
DROP TABLE marca;
 
 
 
CREATE TABLE MARCA
(
Cifm INT PRIMARY KEY NOT NULL,
Nombre VARCHAR(20) NOT NULL,
Ciudad VARCHAR(20) NOT NULL
);
 
INSERT INTO MARCA VALUES(0001,'seat','Madrid');
INSERT INTO MARCA VALUES(0002,'renault','Barcelona');
INSERT INTO MARCA VALUES(0003,'citroen','Valencia');
INSERT INTO MARCA VALUES(0004,'audi','Madrid');
INSERT INTO MARCA VALUES(0005,'opel','Bilbao');
INSERT INTO MARCA VALUES(0006,'bmw','Barcelona');
 
CREATE TABLE COCHES
(
Codcoche INT PRIMARY KEY NOT NULL,
Nombre VARCHAR(20) NOT NULL,
Modelo VARCHAR(10) NOT NULL
);
 
INSERT INTO COCHES VALUES (0001,'ibiza','glx');
INSERT INTO COCHES VALUES (0002,'ibiza','gti');
INSERT INTO COCHES VALUES (0003,'ibiza','gtd');
INSERT INTO COCHES VALUES (0004,'toledo','gtd');
INSERT INTO COCHES VALUES (0005,'cordoba','gti');
INSERT INTO COCHES VALUES (0006,'megane','1.6');
INSERT INTO COCHES VALUES (0007,'megane','gti');
INSERT INTO COCHES VALUES (0008,'laguna','gtd');
INSERT INTO COCHES VALUES (0009,'laguna','td');
INSERT INTO COCHES VALUES (0010,'zx','16V');
INSERT INTO COCHES VALUES (0011,'zx','td');
INSERT INTO COCHES VALUES (0012,'xantia','gtd');
INSERT INTO COCHES VALUES (0013,'a4','1.8');
INSERT INTO COCHES VALUES (0014,'a4','2.8');
INSERT INTO COCHES VALUES (0015,'astra','caravan');
INSERT INTO COCHES VALUES (0016,'astra','gti');
INSERT INTO COCHES VALUES (0017,'corsa','1.4');
INSERT INTO COCHES VALUES (0018,'300','316i');
INSERT INTO COCHES VALUES (0019,'500','525i');
INSERT INTO COCHES VALUES (0020,'700','750i');
 
 
CREATE TABLE CONCESIONARIOS
(
Cifc INT PRIMARY KEY NOT NULL,
Nombre VARCHAR(20) NOT NULL,
Ciudad VARCHAR(20) NOT NULL
);
 
INSERT INTO CONCESIONARIOS VALUES(0001,'acar','Madrid');
INSERT INTO CONCESIONARIOS VALUES(0002,'bcar','Madrid');
INSERT INTO CONCESIONARIOS VALUES(0003,'ccar','Barcelona');
INSERT INTO CONCESIONARIOS VALUES(0004,'dcar','Valencia');
INSERT INTO CONCESIONARIOS VALUES(0005,'ecar','Bilbao');
 
CREATE TABLE CLIENTES
(
DNI VARCHAR(10) PRIMARY KEY NOT NULL,
Nombre VARCHAR(20) NOT NULL,
Apellidos VARCHAR(30) NOT NULL,
Ciudad VARCHAR (20) NOT NULL
);
 
INSERT INTO CLIENTES VALUES('0001','Luis','Garcia','Madrid');
INSERT INTO CLIENTES VALUES('0002','Antonio','Lopez','Valencia');
INSERT INTO CLIENTES VALUES('0003','Juan','Martin','Madrid');
INSERT INTO CLIENTES VALUES('0004','Maria','Garcia','Madrid');
 
 
CREATE TABLE DISTRIBUCION
(
Cifc INT NOT NULL,
Codcoche INT NOT NULL,
Cantidad INT NOT NULL,
CONSTRAINT pk_clavecifc PRIMARY KEY(Cifc,Codcoche),
CONSTRAINT fk_clavecoche FOREIGN KEY (Codcoche) REFERENCES COCHES,
CONSTRAINT fk_clavedis FOREIGN KEY (Cifc) REFERENCES CONCESIONARIOS
);
 
INSERT INTO DISTRIBUCION VALUES (0001,0001,3);
INSERT INTO DISTRIBUCION VALUES (0001,0005,7);
INSERT INTO DISTRIBUCION VALUES (0001,0006,7);
INSERT INTO DISTRIBUCION VALUES (0001,0007,5);
INSERT INTO DISTRIBUCION VALUES (0001,0008,10);
INSERT INTO DISTRIBUCION VALUES (0002,0009,10);
INSERT INTO DISTRIBUCION VALUES (0002,0010,5);
INSERT INTO DISTRIBUCION VALUES (0002,0011,3);
INSERT INTO DISTRIBUCION VALUES (0002,0012,5);
INSERT INTO DISTRIBUCION VALUES (0003,0013,10);
INSERT INTO DISTRIBUCION VALUES (0003,0014,5);
INSERT INTO DISTRIBUCION VALUES (0003,0015,10);
INSERT INTO DISTRIBUCION VALUES (0004,0016,20);
INSERT INTO DISTRIBUCION VALUES (0004,0017,8);
 
 
CREATE TABLE VENTAS
(
Cifc INT NOT NULL,
DNI VARCHAR(20) NOT NULL,
Codcoche INT NOT NULL,
Color VARCHAR(10) NOT NULL,
CONSTRAINT pk_claveventa1 PRIMARY KEY (Cifc,DNI,Codcoche),
CONSTRAINT fk_claveventa2 FOREIGN KEY (Cifc) REFERENCES Concesionarios,
CONSTRAINT fk_claveventa3 FOREIGN KEY (DNI) REFERENCES Clientes,
CONSTRAINT fk_claveventa4 FOREIGN KEY (Codcoche) REFERENCES Coches
);
 
INSERT INTO VENTAS VALUES(0001,'0001',0001,'blanco');
INSERT INTO VENTAS VALUES(0001,'0002',0005,'rojo');
INSERT INTO VENTAS VALUES(0002,'0003',0008,'blanco');
INSERT INTO VENTAS VALUES(0002,'0001',0006,'rojo');
INSERT INTO VENTAS VALUES(0003,'0004',0011,'rojo');
INSERT INTO VENTAS VALUES(0004,'0005',0014,'verde');
 
CREATE TABLE MARCO
(
Cifm INT NOT NULL,
Codcoche INT NOT NULL,
CONSTRAINT pk_clavemarco PRIMARY KEY (Cifm,Codcoche),
CONSTRAINT fk_clavemarco2 FOREIGN KEY (Cifm) REFERENCES Marca,
CONSTRAINT fk_clavemarco3 FOREIGN KEY (Codcoche) REFERENCES Coches
);
 
INSERT INTO MARCO VALUES (0001,0001);
INSERT INTO MARCO VALUES (0001,0002);
INSERT INTO MARCO VALUES (0001,0003);
INSERT INTO MARCO VALUES (0001,0004);
INSERT INTO MARCO VALUES (0001,0005);
INSERT INTO MARCO VALUES (0002,0006);
INSERT INTO MARCO VALUES (0002,0007);
INSERT INTO MARCO VALUES (0002,0008);
INSERT INTO MARCO VALUES (0003,0009);
INSERT INTO MARCO VALUES (0003,0010);
INSERT INTO MARCO VALUES (0003,0011);
INSERT INTO MARCO VALUES (0004,0012);
INSERT INTO MARCO VALUES (0004,0013);
INSERT INTO MARCO VALUES (0005,0014);
INSERT INTO MARCO VALUES (0005,0015);
INSERT INTO MARCO VALUES (0005,0016);
INSERT INTO MARCO VALUES (0005,0017);
INSERT INTO MARCO VALUES (0006,0018);
INSERT INTO MARCO VALUES (0006,0019);
INSERT INTO MARCO VALUES (0006,0020);