Database Design for University

The provided content is a set of SQL commands that create several tables in a database. The tables are: Student, Semester, Course, Departement, Instructor, Section, and Room. Each table represents a different entity or relationship in an educational institution. The tables have various columns with data types such as number, varchar2, date, etc. Some of the constraints include primary keys, foreign keys, check constraints, and unique constraints.

CREATE TABLE Student(
Student_ID Number(8) PRIMARY KEY,
Name Varchar2(15) NOT NULL,
Mobile Varchar2(8),
Birth_date Date,
Dept_ID char(4) NOT NULL,
Major Varchar2(3),
Nationality Varchar2(20) NOT NULL,
Course_ID Varchar2(8) NOT NULL,
Grades Varchar2(2),
Gender Char(1) NOT NULL,
CONSTRAINT student_gender_ck CHECK(Gender='M' OR Gender='F'),
CONSTRAINT student_deptid_fk FOREIGN KEY(Dept_ID) REFERENCES Departement(Dept_ID),
CONSTRAINT student_courseid_fk FOREIGN KEY(Course) REFERENCES Course(Course_ID)
);
CREATE TABLE Semester(
Semester_ID Varchar2(8) NOT NULL,
Course_ID Varchar2(8) NOT NULL,
CONSTRAINT semester_id_pk PRIMARY KEY(Semster_ID),
CONSTRAINT semester_courseid_fk FOREIGN KEY (Course_ID) REFERENCES Course(Course_ID)
);
CREATE TABLE Course(
Course_ID Varchar2(8) PRIMARY KEY,
Course_name varchar2(10),
Crdt_hrs varchar(3),
Prerequisite varchar(10),
CONSTRAINT course_prereq_ck CHECK(count(Prerequisite)<=3) --CHECK!
--CONSTRAINT course_m CHECK PASSED PREQUISITES!
);
CREATE TABLE Departement(
Dept_ID char(4) PRIMARY KEY,
Dept_name Varchar2(20) NOT NULL,
College_ID Varchar2(3),
CONSTRAINT departement_collegeid_fk FOREIGN KEY (College_ID) REFERENCES College(College_ID)
);

CREATE TABLE Instructor(
Inst_ID Number(8) PRIMARY KEY,
Inst_name Varchar2(15) NOT NULL,
Dept_ID char(4),
CONSTRAINT instructor_deptid_fk FOREIGN KEY (Dept_ID) REFERENCES Departement(Dept_ID)
);
CREATE TABLE Section(
Section_ID Number(5) PRIMARY KEY,
sTime Time NOT NULL,
Room_ID Number(3) NOT NULL,
Dept_ID char(4),
Section_limit Number(2) NOT NULL,
CONSTRAINT section_deptid_fk FOREIGN KEY (Dept_ID) REFERENCES Departement(Dept_ID),
CONSTRAINT section_roomid_fk FOREIGN KEY (Room_ID) REFERENCES Room(Room_ID),
CONSTRAINT section_overlap_uq UNIQUE(sTime and Room_ID) --CHECK iF CORRECT!
);
CREATE TABLE Room(
Room_ID Number(3) PRIMARY KEY,
Building_ID varchar2(3) NOT NULL,
Capacity Number(2),
CONSTRAINT room_buildingid_uq UNIQUE(Building_ID),
CONSTRAINT room_capacity_ck CHECK(Capcity>=Section.Section_limit) --CHECK if it is coreect
);
CREATE TABLE College(
College_name Varchar2(15),
College_ID Varchar2(3) PRIMARY KEY
);
--18 hour limit how??
--student cannot register overlapping course (time)
--drop course with no grade
--hold on the student?