Examen DB

.sql

drop table Ciudad;
drop table Cliente;
drop table Color;
drop table Compra;
drop table Modelo;
drop table Automovil;
drop table Marca;
drop table Revision;
drop table Telefono;
drop table TipoRevision;

CREATE TABLE Ciudad
(
	id INT,
	nombre VARCHAR(30),
	CONSTRAINT Ciudad_pkey PRIMARY KEY (id)
);

CREATE TABLE Cliente
(
	cod_interno INT,
	ciudad_id INT,
	rut VARCHAR(15),
	nombre VARCHAR(20),
	direccion VARCHAR(20),
	CONSTRAINT Cliente_pkey PRIMARY KEY (cod_interno,rut)
);

CREATE TABLE Color
(
	id INT,
	nombre VARCHAR(30),
	CONSTRAINT Color_pkey PRIMARY KEY (id)
);

CREATE TABLE Compra
(
	codigo INT,
	cliente_id INT,
	automovil_id VARCHAR(20),
	CONSTRAINT Compra_pkey PRIMARY KEY (codigo)
);

CREATE TABLE Marca
(
	id INT,
	nombre VARCHAR(30),
	CONSTRAINT Marca_pkey PRIMARY KEY (id)
);

CREATE TABLE Modelo
(
	id INT,
	nombre VARCHAR(30),
	marca_id INT,
	CONSTRAINT Modelo_pkey PRIMARY KEY (id)
);

CREATE TABLE Automovil
(
	matricula VARCHAR(20),
	modelo_id INT,
	color_id INT,
	CONSTRAINT Automovil_pkey PRIMARY KEY (matricula)
);

/*Objetos iniciales:
*/
CREATE TABLE Revision
(
	id INT,
	compra_id INT,
	nombre VARCHAR(20),
	CONSTRAINT Revision_pkey PRIMARY KEY (id)
);

CREATE TABLE Telefono
(
	id INT,
	cliente_id INT,
	numero VARCHAR(30),
	CONSTRAINT Telefono_pkey PRIMARY KEY (id)
);

/*Objetos iniciales:
*/
CREATE TABLE TipoRevision
(
	id INT,
	cliente_id VARCHAR(20),
	revision_id INT,
	CONSTRAINT TipoRevision_pkey PRIMARY KEY (id)
);

-- Añadiendo claves foraneas
ALTER TABLE Cliente ADD FOREIGN KEY (ciudad_id) REFERENCES Ciudad (id);
ALTER TABLE Compra ADD FOREIGN KEY (cliente_id) REFERENCES Cliente (cod_interno);
ALTER TABLE Compra ADD FOREIGN KEY (automovil_id) REFERENCES Automovil (matricula);
ALTER TABLE Modelo ADD FOREIGN KEY (marca_id) REFERENCES Marca (id);
ALTER TABLE Automovil ADD FOREIGN KEY (modelo_id) REFERENCES Modelo (id);
ALTER TABLE Automovil ADD FOREIGN KEY (color_id) REFERENCES Color (id);
ALTER TABLE Telefono ADD FOREIGN KEY (cliente_id) REFERENCES Cliente (cod_interno);
ALTER TABLE TipoRevision ADD FOREIGN KEY (cliente_id) REFERENCES Automovil (matricula);
ALTER TABLE TipoRevision ADD FOREIGN KEY (revision_id) REFERENCES Revision (id);

-- Select de las tablas..
select * from Telefono;
select * from Ciudad;
select * from Modelo;
select * from Color;
select * from Cliente;
select * from Automovil;
select * from Revision;
select * from Compra;
select * from TipoRevision;

insert into Telefono values(1, 1, '123456789');
insert into Telefono values(2, 1, '412341234');
insert into Telefono values(3, 1, '123244789');
insert into Telefono values(4, 2, '123456789');
insert into Telefono values(5, 3, '123456222');
insert into Telefono values(6, 3, '123456789');
insert into Telefono values(7, 4, '113456789');
insert into Telefono values(8, 5, '123456789');
insert into Telefono values(9, 6, '123456789');
insert into Telefono values(10, 8, '125555789');
insert into Telefono values(11, 9, '888886789');

insert into marca values(1, 'Chevrolet');
insert into marca values(2, 'Ford');
insert into marca values(3, 'Hyundai');
insert into marca values(4, 'KIA');
insert into marca values(5, 'Mazda');
insert into marca values(6, 'Nissan');
insert into marca values(7, 'Peugeot');
insert into marca values(8, 'Suzuki');
insert into marca values(9, 'Toyota');

insert into modelo values(1, 'Sail', 1);
insert into modelo values(2, 'Focus RS', 2);
insert into modelo values(3, 'Accent', 3);
insert into modelo values(4, 'Morning', 4);
insert into modelo values(5, 'CX7', 5);
insert into modelo values(6, 'L200', 6);
insert into modelo values(7, '3008 Action', 7);
insert into modelo values(8, 'SX4', 8);
insert into modelo values(9, 'Corolla', 9);

insert into ciudad values(1, 'Valdivia');
insert into ciudad values(2, 'Río Bueno');
insert into ciudad values(3, 'La Unión');
insert into ciudad values(4, 'Paillaco');
insert into ciudad values(5, 'Corral');
insert into ciudad values(6, 'Niebla');
insert into ciudad values(7, 'Los Lagos');
insert into ciudad values(8, 'Chaihuin');
insert into ciudad values(9, 'Osorno');
insert into ciudad values(10, 'Temuco');

insert into color values(1, 'Rojo');
insert into color values(2, 'Amarillo');
insert into color values(3, 'Verde');
insert into color values(4, 'Azul');
insert into color values(5, 'Negro');
insert into color values(6, 'Blanco');
insert into color values(7, 'Naranja');
insert into color values(8, 'Burdeo');

insert into automovil values('aa-bb-11', 1, 1);
insert into automovil values('bb-bb-11', 2, 1);
insert into automovil values('cc-bb-11', 3, 1);
insert into automovil values('dd-bb-11', 4, 1);
insert into automovil values('ee-bb-11', 5, 5);
insert into automovil values('ff-bb-11', 6, 5);
insert into automovil values('gg-bb-11', 7, 5);
insert into automovil values('hh-bb-11', 8, 5);
insert into automovil values('ii-bb-11', 9, 6);
insert into automovil values('jj-bb-11', 1, 6);
insert into automovil values('kk-bb-11', 2, 6);
insert into automovil values('mm-bb-11', 3, 6);
insert into automovil values('nn-bb-11', 4, 6);
insert into automovil values('oo-bb-11', 5, 8);
insert into automovil values('pp-bb-11', 6, 8);
insert into automovil values('qq-bb-11', 7, 8);
insert into automovil values('xx-bb-11', 8, 8);
insert into automovil values('yy-bb-11', 1, 7);
insert into automovil values('zz-bb-11', 2, 8);
insert into automovil values('bl-bb-11', 3, 1);
insert into automovil values('zf-bb-11', 4, 2);










-- Modificando la Tabla