Bd_consultas

CREATE TABLESPACE bd_alumno datafile'C:\oraclexe\app\oracle\oradata\XE\bd_alumno.DBF' size 40m;

CREATE USER da IDENTIFIED BY 1234 DEFAULT TABLESPACE bd_alumno TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON bd_alumno;

CREATE USER da1 IDENTIFIED BY 1234 DEFAULT TABLESPACE bd_alumno TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON bd_alumno;

CREATE PROFILE perfil_admin LIMIT FAILED_LOGIN_ATTEMPTS 3;

ALTER USER da PROFILE perfil_admin;

CREATE PROFILE perfil_userr LIMIT FAILED_LOGIN_ATTEMPTS 4 IDLE_TIME 30 SESSIONS_PER_USER 3 CONNECT_TIME UNLIMITED PASSWORD_LOCK_TIME 1;

ALTER USER da1 PROFILE perfil_userr;

CREATE ROLE rol_admin;

CREATE ROLE rol_userr;

GRANT CREATE SESSION, CREATE TABLE, CREATE USER,CREATE ANY SEQUENCE,

CREATE ROLE, CREATE ROLLBACK SEGMENT, CREATE ANY PROCEDURE, CREATE TRIGGER,

CREATE PUBLIC SYNONYM, ALTER ANY SEQUENCE, ALTER ANY PROCEDURE, ALTER ANY TRIGGER,

DROP ANY TABLE, DROP ANY SEQUENCE, DROP USER, DROP ANY ROLE, INSERT ANY TABLE, UPDATE ANY TABLE, ALTER ANY TABLE TO rol_admin;

GRANT CREATE SESSION TO rol_userr;

GRANT INSERT ANY TABLE TO rol_userr;

GRANT UPDATE ANY TABLE TO rol_userr;

GRANT SELECT ANY TABLE TO rol_userr;

GRANT SELECT ANY SEQUENCE TO rol_userr;

GRANT DELETE ANY TABLE TO rol_userr;

GRANT rol_adminn TO da;

GRANT rol_userr TO da1;

CREATE TABLE Facultad (

Cod_fac VARCHAR2(4) NOT NULL,

Nomb_fac VARCHAR2(30) NOT NULL,

CONSTRAINT Facultad_Cod_fac_Facultad_PK PRIMARY KEY(Cod_fac)

);

CREATE TABLE Programa(

cod_facu varchar2(4),

cod_pro varchar2(4) primary key,

nom_pro varchar(50),

numcredito number(38),

constraint facultad_programa foreign key(Cod_facu)references Facultad(Cod_fac)

);

CREATE TABLE Etnia(

Cod_etnia VARCHAR (11) NOT NULL,

Nom_etnia VARCHAR (30) NOT NULL,

CONSTRAINT Etnia_Cod_PK PRIMARY KEY(Cod_etnia)

);

CREATE TABLE Departamento(

Id_Departamento VARCHAR (11) NOT NULL,

Nombre_Departamento VARCHAR2(50) NOT NULL,

CONSTRAINT Departamento_Id_PK PRIMARY KEY(Id_Departamento)

);

CREATE TABLE Ciudad(

Id_Ciudad VARCHAR (11) NOT NULL,

Nombre_Ciudad VARCHAR2(50) NOT NULL,

Id_Departamento VARCHAR (11) NOT NULL,

CONSTRAINT Ciudad_Id_PK PRIMARY KEY(Id_Ciudad),

CONSTRAINT Ciudad_Id_FK FOREIGN KEY(Id_Departamento) REFERENCES Departamento(Id_Departamento)

);

CREATE TABLE Profesion(

Cod_Profesion VARCHAR (11) NOT NULL,

Nom_Profesion VARCHAR (200),

CONSTRAINT Profesion_Cod_PK PRIMARY KEY(Cod_Profesion)

);

CREATE TABLE Profesor(

Cedula_Profesor VARCHAR(11) NOT NULL,

Primer_Nombre_Profesor VARCHAR2(30) NOT NULL,

Segundo_Nombre_Profesor VARCHAR2(30) ,

Primer_Apellido_Profesor VARCHAR2(30) NOT NULL,

Segundo_Apellido_Profesor VARCHAR2(30) NOT NULL,

Sexo CHAR (1) NOT NULL,

Edad NUMBER NOT NULL,

Direccion VARCHAR (50) default 'sin direccion',

Celular_Profesor NUMBER NOT NULL,

Id_Ciudad VARCHAR (11) NOT NULL,

Cod_Profesion VARCHAR (11) NOT NULL,

CONSTRAINT Profesor_Ce_PK PRIMARY KEY(Cedula_Profesor),

CONSTRAINT Profesor_Id_FK FOREIGN KEY(Id_Ciudad) REFERENCES Ciudad(Id_Ciudad),

CONSTRAINT Profesor_C_FK FOREIGN KEY(Cod_Profesion) REFERENCES Profesion(Cod_Profesion),

CONSTRAINT Profesor_Celular_CH CHECK(Celular_Profesor BETWEEN 3000000000 AND 3999999999)

);

CREATE TABLE Materia(

Id_Materia NUMBER NOT NULL,

Nombre_Materia VARCHAR(20),

Numero_Credito NUMBER (38),

CONSTRAINT Materia_Id_PK PRIMARY KEY(Id_Materia)

);

CREATE TABLE Materia_Profesor(

Id_Materia NUMBER NOT NULL,

Cedula_Profesor VARCHAR(11) NOT NULL,

CONSTRAINT Materia_FK FOREIGN KEY(Id_Materia) REFERENCES Materia(Id_Materia),

CONSTRAINT Materia_Profesor_FK FOREIGN KEY(Cedula_Profesor) REFERENCES Profesor(Cedula_Profesor)

);

...