Oracle SQL: Creación, Modificación de Tablas y Permisos

Creación de Tablas con Restricciones

A continuación se detallan las sentencias DDL para crear las tablas EB_EMPLEADOS y EB_BECASCONCEDIDAS en el tablespace TBLLEARNING:

CREATE TABLE EB_EMPLEADOS ( DNIE VARCHAR2(9) CONSTRAINT PK_EB_EMPLEADOS PRIMARY KEY, NOMBREE VARCHAR2(80) CONSTRAINT UQ_EB_EMPLEADOS_NOMBREE UNIQUE NOT NULL, DIRECCION VARCHAR2(80), POBLACION VARCHAR2(50), PROVINCIA VARCHAR2(30) DEFAULT 'CIUDAD REAL', MOVIL NUMBER(9), SEXO CHAR(1) CONSTRAINT CH_EB_EMPLEADOS_SEXO CHECK (SEXO IN ('H', 'M')), FECHAALTA DATE CONSTRAINT CH_EB_EMPLEADOS_FECHAALTA CHECK (FECHAALTA BETWEEN DATE '2005-04-01' AND DATE '2220-01-01'), NROSEGSOC VARCHAR2(22), DNIDIR VARCHAR2(9), TIPOEMPLEADO CHAR(1) CONSTRAINT CH_EB_EMPLEADOS_TIPOEMPLEADO CHECK (TIPOEMPLEADO IN ('R', 'I')), NRO_HIJOS NUMBER(2) CONSTRAINT CH_EB_EMPLEADOS_NRO_HIJOS CHECK (NRO_HIJOS BETWEEN 0 AND 25), IDNIVEL NUMBER(3) CONSTRAINT FK_EB_EMPLEADOS_IDNIVEL REFERENCES EB_NIVELES(IDNIVEL), CONSTRAINT FK_EB_EMPLEADOS_DNIDIR FOREIGN KEY (DNIDIR) REFERENCES EB_EMPLEADOS(DNIE) ) TABLESPACE TBLLEARNING;

CREATE TABLE EB_BECASCONCEDIDAS ( DNIH VARCHAR2(9), IDBECA NUMBER(6), FECHACONCESION DATE CONSTRAINT CH_EB_BECASCONCEDIDAS_FECHACONCESION CHECK (FECHACONCESION BETWEEN DATE '2005-02-12' AND DATE '2200-01-01'), COBRADA CHAR(1) CONSTRAINT CH_EB_BECASCONCEDIDAS_COBRADA CHECK (COBRADA IN ('S', 'N')), CONSTRAINT PK_EB_BECASCONCEDIDAS PRIMARY KEY (DNIH, IDBECA, FECHACONCESION), CONSTRAINT FK_EB_BECASCONCEDIDAS_DNIH FOREIGN KEY (DNIH) REFERENCES EB_HIJOS(DNIH), CONSTRAINT FK_EB_BECASCONCEDIDAS_IDBECA FOREIGN KEY (IDBECA) REFERENCES EB_BECAS(IDBECA) ) TABLESPACE TBLLEARNING;

Modificación de la Tabla EB_NIVELES

Agregado de una nueva columna y su respectiva restricción de tipo CHECK:

ALTER TABLE EB_NIVELES ADD TIPONIVEL CHAR(1);

ALTER TABLE EB_NIVELES ADD CONSTRAINT CH_EB_NIVELES_TIPONIVEL CHECK (TIPONIVEL IN ('L', 'G', 'J', 'H'));

Reestructuración de Claves e Identificadores

Modificación del modelo para incorporar y propagar el nuevo identificador IDEMPLEADO:

ALTER TABLE EB_EMPLEADOS ADD IDEMPLEADO NUMBER(10); ALTER TABLE EB_HIJOS ADD IDEMPLEADO NUMBER(10); UPDATE EB_HIJOS H SET IDEMPLEADO = (SELECT E.IDEMPLEADO FROM EB_EMPLEADOS E WHERE E.DNIE = H.DNIE); ALTER TABLE EB_HIJOS DROP CONSTRAINT FK_EB_HIJOS_DNIE; ALTER TABLE EB_HIJOS ADD CONSTRAINT FK_EB_HIJOS_IDEMPLEADO FOREIGN KEY (IDEMPLEADO) REFERENCES EB_EMPLEADOS(IDEMPLEADO); ALTER TABLE EB_HIJOS DROP COLUMN DNIE; ALTER TABLE EB_BECASCONCEDIDAS ADD IDEMPLEADO NUMBER(10); UPDATE EB_BECASCONCEDIDAS BC SET IDEMPLEADO = (SELECT E.IDEMPLEADO FROM EB_EMPLEADOS E WHERE E.DNIE = (SELECT H.DNIE FROM EB_HIJOS H WHERE H.DNIH = BC.DNIH)); ALTER TABLE EB_BECASCONCEDIDAS DROP CONSTRAINT FK_EB_BECASCONCEDIDAS_DNIH; ALTER TABLE EB_BECASCONCEDIDAS ADD CONSTRAINT FK_EB_BECASCONCEDIDAS_IDEMPLEADO FOREIGN KEY (IDEMPLEADO) REFERENCES EB_EMPLEADOS(IDEMPLEADO);

ALTER TABLE EB_BECASCONCEDIDAS DROP COLUMN DNIH; ALTER TABLE EB_EMPLEADOS DROP CONSTRAINT PK_EB_EMPLEADOS; ALTER TABLE EB_EMPLEADOS ADD CONSTRAINT PK_EB_EMPLEADOS PRIMARY KEY (IDEMPLEADO);

Creación del Usuario y Conexión Inicial

Definición del usuario LEARN1 con cuota de almacenamiento:

CREATE USER LEARN1 IDENTIFIED BY Ex2019 DEFAULT TABLESPACE TBLLEARNING TEMPORARY TABLESPACE TEMP QUOTA 600M ON TBLLEARNING;

GRANT CONNECT TO LEARN1;

Gestión y Revocación de Privilegios

Asignación de Privilegios

GRANT CONNECT, RESOURCE TO LEARN1;

GRANT CREATE USER, DROP USER TO LEARN1;

GRANT CREATE VIEW, DROP VIEW TO LEARN1;

GRANT SELECT, INSERT, UPDATE ON EB_EMPLEADOS TO LEARN1;

GRANT SELECT, INSERT, UPDATE ON EB_BECASCONCEDIDAS TO LEARN1;

Revocación de Permisos

-- Para revocar permisos:

REVOKE CREATE USER, DROP USER FROM LEARN1;

REVOKE CREATE VIEW, DROP VIEW FROM LEARN1; REVOKE SELECT, INSERT, UPDATE ON EB_EMPLEADOS FROM LEARN1; REVOKE SELECT, INSERT, UPDATE ON EB_BECASCONCEDIDAS FROM LEARN1; REVOKE CONNECT, RESOURCE FROM LEARN1;