Create Table Persona(
  CI int PRIMARY KEY CHECK (CI>=100000 and CI<= 99999999),
  Nombre        VARCHAR(15) not null,
  Apellido      VARCHAR(20) not null,
  email         varchar(50)
)

CREATE TABLE Profesor(
  CI_P INT foreign key references Persona ON DELETE CASCADE ON UPDATE CASCADE,
  Nacionalidad  VARCHAR(55) default 'Venezolano',
  Calle         VARCHAR(20) not null,
  Ciudad        VARCHAR(40) not null,
  Login         VARCHAR(8)  not null unique,
  Password      VARCHAR(8)  not null,   
  PRIMARY KEY (CI_P)
)

CREATE TABLE Alumno(
 CI_A  INT foreign key references Persona ON UPDATE CASCADE ON DELETE CASCADE,
 tipo  varchar(9) check(tipo='Pre-Grado' or tipo='Post-Grado')
  default 'Pre-grado' not null, 
 PRIMARY KEY (CI_A)
)

CREATE TABLE Materia(
 Cod_Mat  varchar(7) PRIMARY KEY 
   check (cod_Mat like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
 Materia  varchar(55) not null
)

CREATE TABLE Carrera(
 Cod_car  varchar(3) primary key 
   check (cod_car like '[0-9][0-9][0-9]'),
 Carrera  varchar(30)unique not null
)

CREATE TABLE Escuela(
 Cod_E   int primary key,
 Escuela varchar(40) unique
)

Create table tiene(
 Cod_E   int foreign key references Escuela ON DELETE CASCADE ON UPDATE CASCADE,
 Cod_Car varchar(3) foreign key references Carrera ON DELETE CASCADE ON UPDATE CASCADE,
 CI_P    int foreign key references Profesor ON DELETE CASCADE ON UPDATE CASCADE  
)

CREATE TABLE Historial(
 Tipo_E  varchar (20) not null,
 Descrip varchar (45) not null,
 Cuidad  varchar (50) not null,
 Pais    varchar (50) not null,
 Fecha   datetime, 
 CI_P    INT  FOREIGN KEY REFERENCES PROFESOR ON DELETE CASCADE ON UPDATE CASCADE, 
 UNIQUE (CI_P, Tipo_E,Descrip)      
)
 
CREATE TABLE CURSO(
 Tipo      varchar (10) not null 
   check(tipo='Curso'or tipo='Taller'),
 Nombre    varchar (50) not null,
 Pais      varchar (50) not null,
 cuidad    varchar (50) not null,
 Instituto varchar (30) not null,
 CI_P      int foreign key references profesor ON DELETE CASCADE ON UPDATE CASCADE,
 duracion  varchar(10) not null,
 nivel     varchar(20) default 'Unico' not null,
 unique (ci_p,nombre,nivel)
)
  
Create table buzon(
 titulo    varchar (20),
 mensaje   text not null,
 remitente varchar (30),
 CI_P      int foreign key references profesor ON DELETE CASCADE ON UPDATE CASCADE
)
     
Create table cartelera(
 fecha datetime not null,
 Mens  text not null, 
 CI_P  int foreign key references profesor ON DELETE CASCADE ON UPDATE CASCADE
)

Create table bibliografia(
 Autor   varchar (20) not null,
 Titulo  varchar (30) not null,
 Cod_mat varchar (7) foreign key references materia ON DELETE CASCADE ON UPDATE CASCADE,
 unique(Autor,titulo) 
 )

Create table foro(
  Tema    varchar (60) not null,
  Fecha_f datetime,
  Primary key(tema,fecha_f)
 )

Create table propone(
  CI    int foreign key references Persona ON DELETE CASCADE ON UPDATE CASCADE,
  Resp    text not null,
  Fecha_r datetime,
  Fecha_f datetime,
  Tema    varchar(60),
  foreign key (Tema,Fecha_f) references Foro ON DELETE CASCADE ON UPDATE CASCADE
)

Create Table Planifica(
 Cod_mat  varchar(7) foreign key references materia ON DELETE CASCADE ON UPDATE CASCADE,
 CI_P     int foreign key references Profesor ON DELETE CASCADE ON UPDATE CASCADE,
 Num_eva  tinyint     not null,
 Tipo_eva varchar(15) not null,
 puntaje  int         not null,
 fecha    datetime,    
 Sec      tinyint check(sec >=01 and sec<=99)
)

Create table Dicta(
 CI_P     int foreign key references Profesor ON DELETE CASCADE ON UPDATE CASCADE,
 Cod_mat  varchar(7) foreign key references Materia ON DELETE CASCADE ON UPDATE CASCADE,
 Periodo  char(1) not null    
   check (periodo like '1''2'or periodo like 'I''II'),
 año      int not null check (año>=1800 and año<=2504)
)

Create table Cursa (
 Cod_mat varchar(7) foreign key references Materia ON DELETE CASCADE ON UPDATE CASCADE,
 CI_A    int foreign key references Alumno ON DELETE CASCADE ON UPDATE CASCADE,
 Peri    char(1) not null check (peri like '1''2'or peri like 'I''II'),
 año     int check (año>=1800 and año<=2504),  
 Sec_a   tinyint check(sec_a >=01 and sec_a<=99)
)

Create table Evalua (
 Num     tinyint not null,
 Nota    tinyint not null check(nota>=0 and nota<=10),
 CI_A    int foreign key references Alumno,
 CI_P    int foreign key references Profesor,
 Cod_mat varchar(7) foreign key references Materia,
 unique(CI_P,CI_A,Cod_mat,Num)
)

Create table Contiene (
 cod_mat varchar(7) foreign key references Materia ON DELETE CASCADE ON UPDATE CASCADE,
 cod_Car varchar(3) foreign key references Carrera ON DELETE CASCADE ON UPDATE CASCADE
)