Database Design: Creating Tables for Student, Course, and Departments

This assignment presents a comprehensive database design solution, focusing on the creation of several key tables: Student, Course, Department, Instructor, Section, Room, and College. The solution includes the SQL code for creating each table, specifying data types, primary keys, foreign keys, and various constraints to ensure data integrity. Constraints such as CHECK, UNIQUE, and NOT NULL are used to validate data entries and enforce relationships between tables. The assignment also addresses potential issues, such as overlapping course registration and course limits, and provides a basic structure for handling these scenarios. Overall, the solution demonstrates a practical understanding of relational database design principles and SQL implementation.

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?