They have sent us an exercise in class, and there is one thing I do not know how to do it. I put the exercise and explain you
We are going to create the tables for an Academy where different computer courses are taught. We will start creating the following tables with SQL :
Table ALUMNOS will collect information about the students: Name, Surname1, Surname2, NIF, Address, Sex, Date of Birth and Course in which they enroll.
Table CURSOS with the following fields: Name of the Course, Code of the Course that identifies it, NIF of the Teacher, Maximum number of students recommended, Start date, End date, Number of total hours of the course. Students can not combine several courses at once. .
How would you limit the students so that they can not combine several courses at the same time?
It is an exercise in Oracle, where we create tables by command in the section of SQL commands. Both the table of students and courses, I have made:
CREATE TABLE ALUMNOS (
NIF VARCHAR(9) CONSTRAINT ALU_NIF_PK PRIMARY KEY,
Nombre VARCHAR(50),
Apellido1 VARCHAR(50),
Apellido2 VARCHAR(50),
Direccion VARCHAR(200),
Sexo VARCHAR(1) CONSTRAINT ALU_SEX_CK CHECK (Sexo in ('M','H')),
FechaNacimiento DATE,
CodigoCurso VARCHAR(10) NOT NULL CONSTRAINT ALU_COD_FK REFERENCES
CURSOS );
CREATE TABLE CURSOS (
Codigo VARCHAR(10) CONSTRAINT CUR_COD_PK PRIMARY KEY,
Nombre VARCHAR(50) UNIQUE,
NIFProfesor VARCHAR(9),
MaximoAlumnos NUMBER(2),
FechaInicio DATE,
FechaFin DATE,
Horas NUMBER(4) NOT NULL,
CONSTRAINT CK_CUR_FEC CHECK (FechaInicio < FechaFin) );