SQL> @creaBD_UZ.sql SQL> /* @creaBD_UZ.sql */ SQL> SET ECHO ON SQL> SQL> /* Crear las tablas y añadir algunas restricciones, de varias maneras */ SQL> SQL> CREATE TABLE Departamento ( 2 clvDpto NUMBER(3) CONSTRAINT PK_clvDpto PRIMARY KEY, 3 codDpto CHAR(10) CONSTRAINT UN_codDpto UNIQUE, 4 nombDpto CHAR(20) CONSTRAINT NN_nombDpto NOT NULL); Table created. SQL> SQL> CREATE TABLE AreaConoc ( 2 clvArea NUMBER(3) CONSTRAINT PK_clvArea PRIMARY KEY, 3 codArea CHAR(10) CONSTRAINT UN_codArea UNIQUE, 4 nombArea CHAR(30) CONSTRAINT NN_nombArea NOT NULL, 5 clvDpto NUMBER(3), 6 CONSTRAINT FK_clvDptoArea FOREIGN KEY (clvDpto) REFERENCES Departamento(clvDpto) ); Table created. SQL> SQL> CREATE TABLE Profesor ( 2 clvProf NUMBER(3) CONSTRAINT PK_clvProf PRIMARY KEY, 3 codProf CHAR(10) CONSTRAINT UN_codProf UNIQUE, 4 nombProf CHAR(30) CONSTRAINT NN_nombProf NOT NULL, 5 clvArea NUMBER(3), 6 CONSTRAINT FK_clvAreaProf FOREIGN KEY (clvArea) REFERENCES AreaConoc(clvArea) ); Table created. SQL> SQL> CREATE TABLE Asignatura ( 2 clvAsign NUMBER(3) CONSTRAINT PK_clvAsign PRIMARY KEY, 3 codAsign CHAR(10) CONSTRAINT UN_codAsign UNIQUE, 4 nombAsign CHAR(30) CONSTRAINT NN_nombAsign NOT NULL, 5 tt_HT NUMBER(3), 6 tt_HP NUMBER(3), 7 clvArea NUMBER(3), 8 CONSTRAINT FK_clvAreaAsig FOREIGN KEY (clvArea) REFERENCES AreaConoc(clvArea) ); Table created. SQL> SQL> CREATE TABLE Titulacion ( 2 clvTitulo NUMBER(3) CONSTRAINT PK_clvTitulo PRIMARY KEY, 3 codTitulo CHAR(10) CONSTRAINT UN_codTitulo UNIQUE, 4 nombTitulo CHAR(20) CONSTRAINT NN_nombTitulo NOT NULL); Table created. SQL> SQL> CREATE TABLE ImparteAsign ( 2 clvProf NUMBER(3), 3 clvAsign NUMBER(3), 4 HT NUMBER(4), 5 HP NUMBER(4), 6 CONSTRAINT FK_clvProfImpAsg FOREIGN KEY (clvProf) REFERENCES Profesor(clvProf), 7 CONSTRAINT FK_clvAsigImpAsg FOREIGN KEY (clvAsign) REFERENCES Asignatura(clvAsign) ); Table created. SQL> SQL> CREATE TABLE AsignTitulo ( 2 clvAsign NUMBER(3), 3 clvTitulo NUMBER(3), 4 CONSTRAINT FK_clvAsgAsgTit FOREIGN KEY (clvAsign) REFERENCES Asignatura(clvAsign), 5 CONSTRAINT FK_clvTitAsgTit FOREIGN KEY (clvTitulo) REFERENCES Titulacion(clvTitulo) ); Table created. SQL> SQL> SELECT * FROM cat; TABLE_NAME TABLE_TYPE ------------------------------ ----------- AREACONOC TABLE ASIGNATURA TABLE ASIGNTITULO TABLE DEPARTAMENTO TABLE IMPARTEASIGN TABLE PROFESOR TABLE TITULACION TABLE 7 rows selected. SQL> SQL> DESCRIBE Departamento; Name Null? Type ----------------------------------------- -------- ---------------------------- CLVDPTO NOT NULL NUMBER(3) CODDPTO CHAR(10) NOMBDPTO NOT NULL CHAR(20) SQL> DESCRIBE AreaConoc; Name Null? Type ----------------------------------------- -------- ---------------------------- CLVAREA NOT NULL NUMBER(3) CODAREA CHAR(10) NOMBAREA NOT NULL CHAR(30) CLVDPTO NUMBER(3) SQL> DESCRIBE Profesor; Name Null? Type ----------------------------------------- -------- ---------------------------- CLVPROF NOT NULL NUMBER(3) CODPROF CHAR(10) NOMBPROF NOT NULL CHAR(30) CLVAREA NUMBER(3) SQL> DESCRIBE Asignatura; Name Null? Type ----------------------------------------- -------- ---------------------------- CLVASIGN NOT NULL NUMBER(3) CODASIGN CHAR(10) NOMBASIGN NOT NULL CHAR(30) TT_HT NUMBER(3) TT_HP NUMBER(3) CLVAREA NUMBER(3) SQL> DESCRIBE Titulacion; Name Null? Type ----------------------------------------- -------- ---------------------------- CLVTITULO NOT NULL NUMBER(3) CODTITULO CHAR(10) NOMBTITULO NOT NULL CHAR(20) SQL> DESCRIBE ImparteAsign; Name Null? Type ----------------------------------------- -------- ---------------------------- CLVPROF NUMBER(3) CLVASIGN NUMBER(3) HT NUMBER(4) HP NUMBER(4) SQL> DESCRIBE AsignTitulo; Name Null? Type ----------------------------------------- -------- ---------------------------- CLVASIGN NUMBER(3) CLVTITULO NUMBER(3) SQL> @infoBD_UZ.sql SQL> /* @infoBD_UZ.sql */ SQL> SET ECHO ON SQL> SQL> DESCRIBE Departamento; Name Null? Type ----------------------------------------- -------- ---------------------------- CLVDPTO NOT NULL NUMBER(3) CODDPTO CHAR(10) NOMBDPTO NOT NULL CHAR(20) SQL> DESCRIBE AreaConoc; Name Null? Type ----------------------------------------- -------- ---------------------------- CLVAREA NOT NULL NUMBER(3) CODAREA CHAR(10) NOMBAREA NOT NULL CHAR(30) CLVDPTO NUMBER(3) SQL> DESCRIBE Profesor; Name Null? Type ----------------------------------------- -------- ---------------------------- CLVPROF NOT NULL NUMBER(3) CODPROF CHAR(10) NOMBPROF NOT NULL CHAR(30) CLVAREA NUMBER(3) SQL> DESCRIBE Asignatura; Name Null? Type ----------------------------------------- -------- ---------------------------- CLVASIGN NOT NULL NUMBER(3) CODASIGN CHAR(10) NOMBASIGN NOT NULL CHAR(30) TT_HT NUMBER(3) TT_HP NUMBER(3) CLVAREA NUMBER(3) SQL> DESCRIBE Titulacion; Name Null? Type ----------------------------------------- -------- ---------------------------- CLVTITULO NOT NULL NUMBER(3) CODTITULO CHAR(10) NOMBTITULO NOT NULL CHAR(20) SQL> DESCRIBE ImparteAsign; Name Null? Type ----------------------------------------- -------- ---------------------------- CLVPROF NUMBER(3) CLVASIGN NUMBER(3) HT NUMBER(4) HP NUMBER(4) SQL> DESCRIBE AsignTitulo; Name Null? Type ----------------------------------------- -------- ---------------------------- CLVASIGN NUMBER(3) CLVTITULO NUMBER(3) SQL> SQL> SET LINESIZE 132 SQL> SQL> column OWNER heading "dueño" format a10; SQL> column TABLE_NAME heading "tabla" format a14; SQL> column CONSTRAINT_NAME heading "namRestric" format a16; SQL> column SEARCH_CONDITION heading "cond.busq." format a24; SQL> column R_OWNER heading "dueñoRef" format a10; SQL> column R_CONSTRAINT_NAME heading "namRestricRef" format a16; SQL> SQL> SELECT OWNER, TABLE_NAME, CONSTRAINT_NAME, SEARCH_CONDITION, R_OWNER, R_CONSTRAINT_NAME 2 FROM user_constraints 3 WHERE INITCAP(table_name) IN ('Departamento', 'Areaconoc', 'Profesor', 'Asignatura', 4 'Titulacion', 'Imparteasign', 'Asigntitulo'); dueño tabla namRestric cond.busq. dueñoRef namRestricRef ---------- -------------- ---------------- ------------------------ ---------- ---------------- SANTIAGO AREACONOC NN_NOMBAREA "NOMBAREA" IS NOT NULL SANTIAGO AREACONOC PK_CLVAREA SANTIAGO AREACONOC UN_CODAREA SANTIAGO AREACONOC FK_CLVDPTOAREA SANTIAGO PK_CLVDPTO SANTIAGO ASIGNATURA NN_NOMBASIGN "NOMBASIGN" IS NOT NULL SANTIAGO ASIGNATURA PK_CLVASIGN SANTIAGO ASIGNATURA UN_CODASIGN SANTIAGO ASIGNATURA FK_CLVAREAASIG SANTIAGO PK_CLVAREA SANTIAGO ASIGNTITULO FK_CLVASGASGTIT SANTIAGO PK_CLVASIGN SANTIAGO ASIGNTITULO FK_CLVTITASGTIT SANTIAGO PK_CLVTITULO SANTIAGO DEPARTAMENTO NN_NOMBDPTO "NOMBDPTO" IS NOT NULL dueño tabla namRestric cond.busq. dueñoRef namRestricRef ---------- -------------- ---------------- ------------------------ ---------- ---------------- SANTIAGO DEPARTAMENTO PK_CLVDPTO SANTIAGO DEPARTAMENTO UN_CODDPTO SANTIAGO IMPARTEASIGN FK_CLVPROFIMPASG SANTIAGO PK_CLVPROF SANTIAGO IMPARTEASIGN FK_CLVASIGIMPASG SANTIAGO PK_CLVASIGN SANTIAGO PROFESOR NN_NOMBPROF "NOMBPROF" IS NOT NULL SANTIAGO PROFESOR PK_CLVPROF SANTIAGO PROFESOR UN_CODPROF SANTIAGO PROFESOR FK_CLVAREAPROF SANTIAGO PK_CLVAREA SANTIAGO TITULACION NN_NOMBTITULO "NOMBTITULO" IS NOT NULL SANTIAGO TITULACION PK_CLVTITULO SANTIAGO TITULACION UN_CODTITULO 22 rows selected. SQL> @ponDat_UZ.sql SQL> /* @ponDat_UZ.sql */ SQL> SET ECHO ON SQL> SQL> DELETE FROM ImparteAsign; 0 rows deleted. SQL> DELETE FROM AsignTitulo; 0 rows deleted. SQL> DELETE FROM Asignatura; 0 rows deleted. SQL> DELETE FROM Profesor; 0 rows deleted. SQL> DELETE FROM AreaConoc; 0 rows deleted. SQL> DELETE FROM Titulacion; 0 rows deleted. SQL> DELETE FROM Departamento; 0 rows deleted. SQL> SQL> SQL> INSERT INTO Departamento VALUES ( 1, '284D', 'DIEI'); 1 row created. SQL> INSERT INTO Departamento VALUES ( 2, '285D', 'ING.MEC.'); 1 row created. SQL> INSERT INTO Departamento VALUES ( 3, '286D', 'MAT.APL.'); 1 row created. SQL> SQL> INSERT INTO AreaConoc VALUES ( 1, 'AC001', 'LENG. Y SIST. INFORM.', 1); 1 row created. SQL> INSERT INTO AreaConoc VALUES ( 2, 'AC002', 'TECN. ELECTRONICA', 1); 1 row created. SQL> INSERT INTO AreaConoc VALUES ( 3, 'AC003', 'ING. ELECTRICA', 1); 1 row created. SQL> INSERT INTO AreaConoc VALUES ( 4, 'AC004', 'ING. DE TRANSPORTES', 2); 1 row created. SQL> INSERT INTO AreaConoc VALUES ( 5, 'AC005', 'EXPR. GRAFICA', 2); 1 row created. SQL> INSERT INTO AreaConoc VALUES ( 6, 'AC006', 'MATEM. APLICADA', 3); 1 row created. SQL> INSERT INTO AreaConoc VALUES ( 7, 'AC007', 'TECN. MATEMATICA', 3); 1 row created. SQL> SQL> INSERT INTO Profesor VALUES ( 1, 'PRF001', 'S. VELILLA', 1); 1 row created. SQL> INSERT INTO Profesor VALUES ( 2, 'PRF002', 'J. CAMPOS', 1); 1 row created. SQL> INSERT INTO Profesor VALUES ( 3, 'PRF003', 'J. NAVARRO', 2); 1 row created. SQL> INSERT INTO Profesor VALUES ( 4, 'PRF004', 'A. MTNEZ.', 2); 1 row created. SQL> INSERT INTO Profesor VALUES ( 5, 'PRF005', 'I. RAMIREZ', 3); 1 row created. SQL> INSERT INTO Profesor VALUES ( 6, 'PRF006', 'E. CAROD', 3); 1 row created. SQL> INSERT INTO Profesor VALUES ( 7, 'PRF007', 'A. MIRAVETE', 4); 1 row created. SQL> INSERT INTO Profesor VALUES ( 8, 'PRF008', 'J. ORTAS', 4); 1 row created. SQL> INSERT INTO Profesor VALUES ( 9, 'PRF009', 'E. ZUBIAURRE', 5); 1 row created. SQL> INSERT INTO Profesor VALUES ( 10, 'PRF010', 'G. TRAVER', 5); 1 row created. SQL> INSERT INTO Profesor VALUES ( 11, 'PRF011', 'V. CAMARENA', 6); 1 row created. SQL> INSERT INTO Profesor VALUES ( 12, 'PRF012', 'C. BUDRIA', 6); 1 row created. SQL> INSERT INTO Profesor VALUES ( 13, 'PRF013', 'J.M. CORREAS', 7); 1 row created. SQL> INSERT INTO Profesor VALUES ( 14, 'PRF014', 'M. AGUADO', 7); 1 row created. SQL> SQL> INSERT INTO Asignatura VALUES ( 1, 'ASG001', 'INFORMATICA', 90, 30, 1); 1 row created. SQL> INSERT INTO Asignatura VALUES ( 2, 'ASG002', 'FICH. y B.DATOS', 45, 20, 1); 1 row created. SQL> INSERT INTO Asignatura VALUES ( 3, 'ASG003', 'ELECTRONICA DIGITAL', 60, 35, 2); 1 row created. SQL> INSERT INTO Asignatura VALUES ( 4, 'ASG004', 'MICROELECTRONICA', 20, 10, 2); 1 row created. SQL> INSERT INTO Asignatura VALUES ( 5, 'ASG005', 'ELECTROTECNIA', 30, 30, 3); 1 row created. SQL> INSERT INTO Asignatura VALUES ( 6, 'ASG006', 'LINEAS Y REDES', 25, 15, 3); 1 row created. SQL> INSERT INTO Asignatura VALUES ( 7, 'ASG007', 'FERROCARRILES', 10, 10, 4); 1 row created. SQL> INSERT INTO Asignatura VALUES ( 8, 'ASG008', 'TRACCION ELECTR.', 20, 20, 4); 1 row created. SQL> INSERT INTO Asignatura VALUES ( 9, 'ASG009', 'DIBUJO I', 40, 20, 5); 1 row created. SQL> INSERT INTO Asignatura VALUES ( 10, 'ASG010', 'GEOM. DESCRIPTIVA', 40, 10, 5); 1 row created. SQL> INSERT INTO Asignatura VALUES ( 11, 'ASG011', 'ALGEBRA', 60, 0, 6); 1 row created. SQL> INSERT INTO Asignatura VALUES ( 12, 'ASG012', 'GEOM. DIFERENCIAL', 40, 0, 6); 1 row created. SQL> INSERT INTO Asignatura VALUES ( 13, 'ASG013', 'CALCULO', 20, 10, 7); 1 row created. SQL> INSERT INTO Asignatura VALUES ( 14, 'ASG014', 'CALCULO NUMERICO', 40, 20, 7); 1 row created. SQL> SQL> INSERT INTO Titulacion VALUES ( 1, 'TIT001', 'ING. INFORMATICA'); 1 row created. SQL> INSERT INTO Titulacion VALUES ( 2, 'TIT002', 'ING. TELECOMUNICAC.'); 1 row created. SQL> INSERT INTO Titulacion VALUES ( 3, 'TIT003', 'ING. INDUSTRIAL'); 1 row created. SQL> INSERT INTO Titulacion VALUES ( 4, 'TIT004', 'ING. TECN. ELECTRON.'); 1 row created. SQL> SQL> INSERT INTO ImparteAsign VALUES ( 1, 1, 3, 4); 1 row created. SQL> INSERT INTO ImparteAsign VALUES ( 1, 2, 23, 44); 1 row created. SQL> INSERT INTO ImparteAsign VALUES ( 14, 14, 13, 0); 1 row created. SQL> SQL> INSERT INTO AsignTitulo VALUES ( 1, 1); 1 row created. SQL> INSERT INTO AsignTitulo VALUES ( 1, 2); 1 row created. SQL> INSERT INTO AsignTitulo VALUES ( 1, 3); 1 row created. SQL> INSERT INTO AsignTitulo VALUES ( 1, 4); 1 row created. SQL> INSERT INTO AsignTitulo VALUES ( 2, 1); 1 row created. SQL> INSERT INTO AsignTitulo VALUES ( 2, 2); 1 row created. SQL> INSERT INTO AsignTitulo VALUES ( 2, 3); 1 row created. SQL> INSERT INTO AsignTitulo VALUES ( 2, 4); 1 row created. SQL> @lstDat_UZ.sql SQL> /* @lstDat_UZ.sql */ SQL> SET ECHO ON SQL> SQL> SELECT COUNT(*) "nº Dptos" FROM Departamento; nº Dptos ---------- 3 SQL> SELECT * FROM Departamento; CLVDPTO CODDPTO NOMBDPTO ---------- ---------- -------------------- 1 284D DIEI 2 285D ING.MEC. 3 286D MAT.APL. SQL> SQL> SELECT COUNT(*) "nº aCon" FROM AreaConoc; nº aCon ---------- 7 SQL> SELECT * FROM AreaConoc; CLVAREA CODAREA NOMBAREA CLVDPTO ---------- ---------- ------------------------------ ---------- 1 AC001 LENG. Y SIST. INFORM. 1 2 AC002 TECN. ELECTRONICA 1 3 AC003 ING. ELECTRICA 1 4 AC004 ING. DE TRANSPORTES 2 5 AC005 EXPR. GRAFICA 2 6 AC006 MATEM. APLICADA 3 7 AC007 TECN. MATEMATICA 3 7 rows selected. SQL> SQL> SELECT COUNT(*) "nº Prof" FROM Profesor; nº Prof ---------- 14 SQL> SELECT * FROM Profesor; CLVPROF CODPROF NOMBPROF CLVAREA ---------- ---------- ------------------------------ ---------- 1 PRF001 S. VELILLA 1 2 PRF002 J. CAMPOS 1 3 PRF003 J. NAVARRO 2 4 PRF004 A. MTNEZ. 2 5 PRF005 I. RAMIREZ 3 6 PRF006 E. CAROD 3 7 PRF007 A. MIRAVETE 4 8 PRF008 J. ORTAS 4 9 PRF009 E. ZUBIAURRE 5 10 PRF010 G. TRAVER 5 11 PRF011 V. CAMARENA 6 CLVPROF CODPROF NOMBPROF CLVAREA ---------- ---------- ------------------------------ ---------- 12 PRF012 C. BUDRIA 6 13 PRF013 J.M. CORREAS 7 14 PRF014 M. AGUADO 7 14 rows selected. SQL> SQL> SELECT COUNT(*) "nº Asign" FROM Asignatura; nº Asign ---------- 14 SQL> SELECT * FROM Asignatura; CLVASIGN CODASIGN NOMBASIGN TT_HT TT_HP CLVAREA ---------- ---------- ------------------------------ ---------- ---------- ---------- 1 ASG001 INFORMATICA 90 30 1 2 ASG002 FICH. y B.DATOS 45 20 1 3 ASG003 ELECTRONICA DIGITAL 60 35 2 4 ASG004 MICROELECTRONICA 20 10 2 5 ASG005 ELECTROTECNIA 30 30 3 6 ASG006 LINEAS Y REDES 25 15 3 7 ASG007 FERROCARRILES 10 10 4 8 ASG008 TRACCION ELECTR. 20 20 4 9 ASG009 DIBUJO I 40 20 5 10 ASG010 GEOM. DESCRIPTIVA 40 10 5 11 ASG011 ALGEBRA 60 0 6 CLVASIGN CODASIGN NOMBASIGN TT_HT TT_HP CLVAREA ---------- ---------- ------------------------------ ---------- ---------- ---------- 12 ASG012 GEOM. DIFERENCIAL 40 0 6 13 ASG013 CALCULO 20 10 7 14 ASG014 CALCULO NUMERICO 40 20 7 14 rows selected. SQL> SQL> SELECT COUNT(*) "nº Titul" FROM Titulacion; nº Titul ---------- 4 SQL> SELECT * FROM Titulacion; CLVTITULO CODTITULO NOMBTITULO ---------- ---------- -------------------- 1 TIT001 ING. INFORMATICA 2 TIT002 ING. TELECOMUNICAC. 3 TIT003 ING. INDUSTRIAL 4 TIT004 ING. TECN. ELECTRON. SQL> SQL> SELECT COUNT(*) "nº ImpAsg" FROM ImparteAsign; nº ImpAsg ---------- 3 SQL> SELECT * FROM ImparteAsign; CLVPROF CLVASIGN HT HP ---------- ---------- ---------- ---------- 1 1 3 4 1 2 23 44 14 14 13 0 SQL> SQL> SELECT COUNT(*) "nº AsgTit" FROM AsignTitulo; nº AsgTit ---------- 8 SQL> SELECT * FROM AsignTitulo; CLVASIGN CLVTITULO ---------- ---------- 1 1 1 2 1 3 1 4 2 1 2 2 2 3 2 4 8 rows selected. SQL> @elimDat_UZ.sql SQL> /* @elimDat_UZ.sql */ SQL> SET ECHO ON SQL> SQL> DELETE FROM ImparteAsign; 3 rows deleted. SQL> DELETE FROM AsignTitulo; 8 rows deleted. SQL> DELETE FROM Titulacion; 4 rows deleted. SQL> DELETE FROM Profesor; 14 rows deleted. SQL> DELETE FROM Asignatura; 14 rows deleted. SQL> DELETE FROM AreaConoc; 7 rows deleted. SQL> DELETE FROM Departamento; 3 rows deleted. SQL> @elimBD_UZ.sql SQL> /* @elimBD_UZ.sql */ SQL> SET ECHO ON SQL> SQL> /* Eliminar las tablas y todas las restricciones ligadas, de tal modo que DOC> solo se borre una tabla cuando sus atributos no son referenciados DOC> */ SQL> ALTER TABLE AreaConoc DROP CONSTRAINT FK_clvDptoArea; Table altered. SQL> ALTER TABLE Profesor DROP CONSTRAINT FK_clvAreaProf; Table altered. SQL> ALTER TABLE Asignatura DROP CONSTRAINT FK_clvAreaAsig; Table altered. SQL> ALTER TABLE ImparteAsign DROP CONSTRAINT FK_clvProfImpAsg; Table altered. SQL> ALTER TABLE ImparteAsign DROP CONSTRAINT FK_clvAsigImpAsg; Table altered. SQL> ALTER TABLE AsignTitulo DROP CONSTRAINT FK_clvAsgAsgTit; Table altered. SQL> ALTER TABLE AsignTitulo DROP CONSTRAINT FK_clvTitAsgTit; Table altered. SQL> SQL> DROP TABLE ImparteAsign; Table dropped. SQL> DROP TABLE AsignTitulo; Table dropped. SQL> DROP TABLE Asignatura; Table dropped. SQL> DROP TABLE Profesor; Table dropped. SQL> DROP TABLE AreaConoc; Table dropped. SQL> DROP TABLE Departamento; Table dropped. SQL> DROP TABLE Titulacion; Table dropped. SQL> SQL> SELECT * FROM cat; no rows selected SQL> spool off