UD05 · Pràctiques

-- Pràctica 5.1 · Botiga · Nom Cognoms
SPOOL p5_1_salida.txt

CREATE TABLE cliente (
    id_cliente  NUMBER(6)      CONSTRAINT pk_cliente PRIMARY KEY,
    nombre      VARCHAR2(100)  CONSTRAINT nn_cliente_nombre NOT NULL,
    email       VARCHAR2(254)  CONSTRAINT nn_cliente_email NOT NULL,
    fecha_alta  DATE           DEFAULT SYSDATE NOT NULL,
    CONSTRAINT uq_cliente_email UNIQUE (email)
);

CREATE TABLE pedido (
    num_pedido    NUMBER(8)    CONSTRAINT pk_pedido PRIMARY KEY,
    fecha_pedido  DATE         DEFAULT SYSDATE CONSTRAINT nn_pedido_fecha NOT NULL,
    importe       NUMBER(10,2) CONSTRAINT nn_pedido_importe NOT NULL,
    estado        VARCHAR2(10) DEFAULT 'PENDIENTE' NOT NULL,
    id_cliente    NUMBER(6)    CONSTRAINT nn_pedido_cliente NOT NULL,
    CONSTRAINT ck_pedido_importe CHECK (importe >= 0),
    CONSTRAINT ck_pedido_estado  CHECK (estado IN ('PENDIENTE', 'ENVIADO', 'ENTREGADO', 'CANCELADO')),
    CONSTRAINT fk_pedido_cliente FOREIGN KEY (id_cliente) REFERENCES cliente (id_cliente)
);
CREATE INDEX ix_pedido_cliente ON pedido (id_cliente);

DESC pedido

SELECT constraint_name, constraint_type, search_condition_vc, r_constraint_name
FROM   user_constraints
WHERE  table_name IN ('CLIENTE', 'PEDIDO')
ORDER  BY table_name, constraint_type;

INSERT INTO cliente (id_cliente, nombre, email) VALUES (1, 'Ana Ruiz', 'ana@mail.com');
INSERT INTO cliente (id_cliente, nombre, email) VALUES (2, 'Luis Gil', 'luis@mail.com');
INSERT INTO pedido (num_pedido, importe, id_cliente) VALUES (1001, 59.90, 1);
COMMIT;

-- a) Clau primària repetida
INSERT INTO cliente (id_cliente, nombre, email) VALUES (1, 'Otro', 'otro@mail.com');
-- b) Email repetit
INSERT INTO cliente (id_cliente, nombre, email) VALUES (3, 'Eva', 'ana@mail.com');
-- c) Camp obligatori buit (recorda: en Oracle '' és NULL)
INSERT INTO cliente (id_cliente, nombre, email) VALUES (4, '', 'eva@mail.com');
-- d) Import negatiu
INSERT INTO pedido (num_pedido, importe, id_cliente) VALUES (1002, -5, 1);
-- e) Estat no permés
INSERT INTO pedido (num_pedido, importe, estado, id_cliente) VALUES (1003, 10, 'PERDIDO', 1);
-- f) Client inexistent
INSERT INTO pedido (num_pedido, importe, id_cliente) VALUES (1004, 10, 99);
-- g) Esborrar un client amb comandes
DELETE FROM cliente WHERE id_cliente = 1;
-- h) Esborrar la taula pare
DROP TABLE cliente;

DROP TABLE prestamo  CASCADE CONSTRAINTS PURGE;
DROP TABLE ejemplar  CASCADE CONSTRAINTS PURGE;
DROP TABLE escribe   CASCADE CONSTRAINTS PURGE;
DROP TABLE socio     CASCADE CONSTRAINTS PURGE;
DROP TABLE libro     CASCADE CONSTRAINTS PURGE;
DROP TABLE autor     CASCADE CONSTRAINTS PURGE;

CREATE TABLE autor (
    id_autor  NUMBER(6)     GENERATED BY DEFAULT ON NULL AS IDENTITY CONSTRAINT pk_autor PRIMARY KEY,
    nombre    VARCHAR2(120) CONSTRAINT nn_autor_nombre NOT NULL,
    nacionalidad VARCHAR2(60)
);

CREATE TABLE libro (
    isbn              CHAR(13)      CONSTRAINT pk_libro PRIMARY KEY,
    titulo            VARCHAR2(200) CONSTRAINT nn_libro_titulo NOT NULL,
    anio_publicacion  NUMBER(4),
    editorial         VARCHAR2(80),
    CONSTRAINT ck_libro_isbn CHECK (REGEXP_LIKE(isbn, '^[0-9]{13}$')),
    CONSTRAINT ck_libro_anio CHECK (anio_publicacion BETWEEN 1450 AND 2100)
);

CREATE TABLE socio (
    num_socio  NUMBER(6)     CONSTRAINT pk_socio PRIMARY KEY,
    dni        CHAR(9)       CONSTRAINT nn_socio_dni NOT NULL,
    nombre     VARCHAR2(100) CONSTRAINT nn_socio_nombre NOT NULL,
    telefono   VARCHAR2(15),
    CONSTRAINT uq_socio_dni UNIQUE (dni),
    CONSTRAINT ck_socio_dni CHECK (REGEXP_LIKE(dni, '^[0-9XYZ][0-9]{7}[A-Z]$'))
);

CREATE TABLE escribe (
    id_autor       NUMBER(6),
    isbn           CHAR(13),
    orden_autoria  NUMBER(2) CONSTRAINT nn_escribe_orden NOT NULL,
    CONSTRAINT pk_escribe PRIMARY KEY (id_autor, isbn),
    CONSTRAINT uq_escribe_orden UNIQUE (isbn, orden_autoria),
    CONSTRAINT fk_escribe_autor FOREIGN KEY (id_autor) REFERENCES autor (id_autor),
    CONSTRAINT fk_escribe_libro FOREIGN KEY (isbn) REFERENCES libro (isbn) ON DELETE CASCADE
);

CREATE TABLE ejemplar (
    isbn          CHAR(13),
    num_ejemplar  NUMBER(3),
    estado        VARCHAR2(12) DEFAULT 'DISPONIBLE' CONSTRAINT nn_ejemplar_estado NOT NULL,
    CONSTRAINT pk_ejemplar PRIMARY KEY (isbn, num_ejemplar),
    CONSTRAINT ck_ejemplar_estado CHECK (estado IN ('DISPONIBLE', 'PRESTADO', 'REPARACION', 'BAJA')),
    CONSTRAINT fk_ejemplar_libro FOREIGN KEY (isbn) REFERENCES libro (isbn)
);

CREATE TABLE prestamo (
    num_socio         NUMBER(6),
    isbn              CHAR(13),
    num_ejemplar      NUMBER(3),
    fecha_salida      DATE,
    fecha_prevista    DATE CONSTRAINT nn_prestamo_prevista NOT NULL,
    fecha_devolucion  DATE,
    CONSTRAINT pk_prestamo PRIMARY KEY (num_socio, isbn, num_ejemplar, fecha_salida),
    CONSTRAINT ck_prestamo_prevista   CHECK (fecha_prevista > fecha_salida),
    CONSTRAINT ck_prestamo_devolucion CHECK (fecha_devolucion >= fecha_salida),
    CONSTRAINT fk_prestamo_socio    FOREIGN KEY (num_socio) REFERENCES socio (num_socio),
    CONSTRAINT fk_prestamo_ejemplar FOREIGN KEY (isbn, num_ejemplar)
        REFERENCES ejemplar (isbn, num_ejemplar)
);
CREATE INDEX ix_escribe_libro     ON escribe (isbn);
CREATE INDEX ix_prestamo_ejemplar ON prestamo (isbn, num_ejemplar);

-- P01 · Inserció vàlida completa → OK
INSERT INTO autor (nombre, nacionalidad) VALUES ('Carmen Martín Gaite', 'Española');
INSERT INTO libro VALUES ('9788423342112', 'Entre visillos', 1958, 'Destino');
INSERT INTO escribe VALUES (1, '9788423342112', 1);
INSERT INTO ejemplar (isbn, num_ejemplar) VALUES ('9788423342112', 1);
INSERT INTO socio VALUES (1, '12345678Z', 'Pablo Mira', NULL);
INSERT INTO prestamo VALUES (1, '9788423342112', 1, DATE '2026-10-01', DATE '2026-10-15', NULL);

-- P02 · ISBN amb lletres → ORA-02290 CK_LIBRO_ISBN
INSERT INTO libro VALUES ('97884ABC42112', 'Prueba', 2000, NULL);

-- P03 · Préstec d'un exemplar inexistent → ORA-02291 FK_PRESTAMO_EJEMPLAR
INSERT INTO prestamo VALUES (1, '9788423342112', 9, DATE '2026-10-02', DATE '2026-10-16', NULL);

-- P04 · Devolució anterior a l'eixida → ORA-02290 CK_PRESTAMO_DEVOLUCION
-- P05 · Dos autors amb el mateix ordre en un llibre → ...
-- P06 · Esborrar un llibre amb exemplars → ...
ROLLBACK;

SELECT i.table_name, i.index_name, i.uniqueness, c.column_name, c.column_position
FROM   user_indexes i JOIN user_ind_columns c ON c.index_name = i.index_name
ORDER  BY i.table_name, i.index_name, c.column_position;

CREATE INDEX ix_alumno_apellidos ON alumno (apellidos, nombre);

CREATE OR REPLACE VIEW v_alumno_listado AS
    SELECT id_alumno, nia, apellidos, nombre, cod_grupo
    FROM   alumno
    WITH READ ONLY;

CREATE OR REPLACE VIEW v_matricula_detalle AS
    SELECT m.id_matricula, m.curso_academico,
           a.nia, a.apellidos || ', ' || a.nombre AS alumno,
           mo.codigo AS cod_modulo, mo.nombre AS modulo, mo.cod_ciclo,
           m.convocatoria, m.nota_final
    FROM   matricula m
           JOIN alumno a  ON a.id_alumno  = m.id_alumno
           JOIN modulo mo ON mo.id_modulo = m.id_modulo;

SELECT * FROM v_matricula_detalle WHERE nia = '10450037';

CREATE OR REPLACE VIEW v_falta_pendiente AS
    SELECT id_falta, id_matricula, fecha, horas, justificada
    FROM   falta_asistencia
    WHERE  justificada = 'N'
    WITH CHECK OPTION;

-- Prova: ha de fallar amb ORA-01402 (view WITH CHECK OPTION where-clause violation)
INSERT INTO v_falta_pendiente (id_matricula, fecha, horas, justificada)
VALUES (10001, DATE '2026-03-02', 2, 'S');
ROLLBACK;

CREATE SEQUENCE seq_certificado START WITH 1 INCREMENT BY 1 NOCACHE;
SELECT 'CERT-2026-' || LPAD(seq_certificado.NEXTVAL, 5, '0') AS num_certificado FROM dual;

CREATE ROLE rol_secretaria;
CREATE ROLE rol_profesorado;
CREATE ROLE rol_jefatura;

GRANT CREATE SESSION TO rol_secretaria, rol_profesorado, rol_jefatura;

CREATE USER sec_ana     IDENTIFIED BY "Sec_Ana_2026"     QUOTA 0 ON users;
CREATE USER prof_marta  IDENTIFIED BY "Prof_Marta_2026"  QUOTA 0 ON users;
CREATE USER jef_carmen  IDENTIFIED BY "Jef_Carmen_2026"  QUOTA 0 ON users;
CREATE USER app_edugest IDENTIFIED BY "App_EduGest_2026" QUOTA 0 ON users;

GRANT rol_secretaria  TO sec_ana, app_edugest;
GRANT rol_profesorado TO prof_marta;
GRANT rol_jefatura    TO jef_carmen;

-- Secretaria
GRANT SELECT ON alumno    TO rol_secretaria;
GRANT SELECT ON matricula TO rol_secretaria;
GRANT SELECT ON grupo     TO rol_secretaria;
GRANT SELECT ON modulo    TO rol_secretaria;
GRANT INSERT, UPDATE ON alumno TO rol_secretaria;
GRANT INSERT, UPDATE (convocatoria, fecha_matricula) ON matricula TO rol_secretaria;

-- Professorat
GRANT SELECT ON v_alumno_listado    TO rol_profesorado;
GRANT SELECT ON v_matricula_detalle TO rol_profesorado;
GRANT SELECT, UPDATE (nota_final) ON matricula TO rol_profesorado;
GRANT SELECT, INSERT, UPDATE ON falta_asistencia TO rol_profesorado;

GRANT SELECT ANY TABLE ON SCHEMA edugest TO rol_jefatura;

-- Connectat com a prof_marta
SELECT COUNT(*) FROM edugest.v_alumno_listado;                       -- 32
SELECT telefono FROM edugest.alumno;                                 -- ORA-00942
UPDATE edugest.matricula SET nota_final = 8 WHERE id_matricula = 10001;   -- 1 fila
UPDATE edugest.matricula SET convocatoria = 2 WHERE id_matricula = 10001; -- ORA-01031
ROLLBACK;

-- Com a SYSTEM
SELECT grantee, table_name, privilege FROM dba_tab_privs WHERE owner = 'EDUGEST' ORDER BY grantee;
SELECT grantee, table_name, column_name, privilege FROM dba_col_privs WHERE owner = 'EDUGEST';
SELECT grantee, granted_role FROM dba_role_privs WHERE granted_role LIKE 'ROL\_%' ESCAPE '\';