Base de Datos

deletedata.sql

-- Eliminar tuplas de la base de datos

-- Eliminar tuplas de Cliente

DELETE FROM RestDB.Cliente;

deletedb.sql

DROP TABLE RestDB.Come;
DROP TABLE RestDB.Gusta;

DROP TABLE RestDB.Comida;
DROP TABLE RestDB.Restaurante;
DROP TABLE RestDB.Cliente;

\c postgres

DROP DATABASE restaurant;

loaddata.sql

-- Cargar datos de tablas primarias

INSERT INTO RestDB.Cliente VALUES (24568789,'Wilsen Hernandez');
INSERT INTO RestDB.Cliente VALUES (24420758,'Manuel Lopes');
INSERT INTO RestDB.Cliente VALUES (24885175,'Jose Perez');
INSERT INTO RestDB.Cliente VALUES (12458751,'Marco Jimenez');

newdb.sql

/* Crear DB */

CREATE DATABASE restaurant TEMPLATE template1;

/* Conectar a la base de datos */

\c restaurant

/* Crear esquema */

CREATE SCHEMA RestDB;

/* Crear dominios */

CREATE DOMAIN RestDB.CedulaCliente int NOT NULL
	CHECK (VALUE > 0);
	
CREATE DOMAIN RestDB.NombreCliente varchar(48);

CREATE DOMAIN RestDB.RIF int NOT NULL
	CHECK (VALUE > 0);
	
CREATE DOMAIN RestDB.NombreComida varchar(50) NOT NULL;

/* Crear tablas */

CREATE TABLE RestDB.Cliente (
	Cedula 		RestDB.CedulaCliente primary key,
	Nombre 		RestDB.NombreCliente
);

CREATE TABLE RestDB.Restaurante (
	RIF			RestDB.RIF primary key,
	NombreRest	RestDB.NombreCliente
);

CREATE TABLE RestDB.Comida (
	NombreCo	RestDB.NombreComida primary key,
	Proteinas	int
);

CREATE TABLE RestDB.Gusta (
	CedulaG		RestDB.CedulaCliente,
	Nombreco	RestDB.NombreComida,
	FOREIGN KEY (CedulaG) REFERENCES RestDB.Cliente
	ON UPDATE CASCADE
	ON DELETE CASCADE,
	FOREIGN KEY (Nombreco) REFERENCES RestDB.Comida
	ON UPDATE CASCADE
	ON DELETE CASCADE,
	PRIMARY KEY (CedulaG, Nombreco)
);

CREATE TABLE RestDB.Come (
	RIF			RestDB.RIF,
	Cedulac 	RestDB.CedulaCliente,
	Nombrec 	RestDB.NombreCliente,
	FOREIGN KEY (RIF) REFERENCES RestDB.Restaurante
	ON UPDATE CASCADE
	ON DELETE CASCADE,
	FOREIGN KEY (Cedulac) REFERENCES RestDB.Cliente
	ON UPDATE CASCADE
	ON DELETE CASCADE,
	FOREIGN KEY (Nombrec) REFERENCES RestDB.Comida
	ON UPDATE CASCADE
	ON DELETE CASCADE,
	PRIMARY KEY (RIF,Cedulac,Nombrec)
);