SQL Queries

create_tables.sql

-- Branches table
CREATE TABLE tblBranch (
  branchId VARCHAR(255) PRIMARY KEY,
  branchName VARCHAR(255) NOT NULL,
  building VARCHAR(255),
  other VARCHAR(255)
);
-- Students Table
CREATE TABLE tblStudent (
  studentId INT PRIMARY KEY,
  firstName VARCHAR(255) NOT NULL,
  middleName VARCHAR(255),
  lastName VARCHAR(255),
  city VARCHAR(255),
  state VARCHAR(255),
  country VARCHAR(255),
  birthDate DATE,
  gender VARCHAR(255) NOT NULL,
  registrationNumber VARCHAR(255),
  branchId VARCHAR(255),
  admissionYear INT,
  FOREIGN KEY (branchId) REFERENCES tblBranch(branchId)
);
-- Subjects table
CREATE TABLE tblSubject (
  subjectId VARCHAR(255) PRIMARY KEY,
  subjectName VARCHAR(255) NOT NULL,
  theoryHours INT,
  tutorialHours INT,
  practicalHours INT,
  credit INT
);
-- Faculty Table
CREATE TABLE tblFaculty (
  facultyId VARCHAR(255) PRIMARY KEY,
  firstName VARCHAR(255) NOT NULL,
  middleName VARCHAR(255),
  lastName VARCHAR(255),
  address VARCHAR(255),
  city VARCHAR(255),
  state VARCHAR(255),
  country VARCHAR(255),
  joinDate DATE,
  gender VARCHAR(255) NOT NULL,
  qualification VARCHAR(255),
  designation VARCHAR(255),
  departmentId VARCHAR(255) NOT NULL,
  additionalDuties VARCHAR(255)
  FOREIGN KEY (departmentId) REFERENCES tblBranch(branchId)
);
-- Courses table
CREATE TABLE tblCourse (
  courseId VARCHAR(255) PRIMARY KEY,
  subjectId VARCHAR(255) NOT NULL,
  facultyId VARCHAR(255) NOT NULL,
  branchId VARCHAR(255) NOT NULL,
  semester INT NOT NULL,
  FOREIGN KEY (subjectId) REFERENCES tblSubject(subjectId),
  FOREIGN KEY (facultyId) REFERENCES tblFaculty(facultyId),
  FOREIGN KEY (branchId) REFERENCES tblBranch(branchId)
);
-- Students & Courses Connecting Table
CREATE TABLE tblStudentCourse (
  studentCourseId VARCHAR(255) PRIMARY KEY,
  studentId INT NOT NULL,
  courseId VARCHAR(255) NOT NULL,
  month VARCHAR(255),
  year INT,
  FOREIGN KEY (studentId) REFERENCES tblStudent(studentId),
  FOREIGN KEY (courseId) REFERENCES tblCourse(courseId)
);
-- Students & Results Connecting Table
CREATE TABLE tblStudentResult (
  resultId VARCHAR(255) PRIMARY KEY,
  studentCourseId VARCHAR(255) NOT NULL,
  creditsObtained INT NOT NULL,
  isCleared INT,
  yearAppeared INT,
  FOREIGN KEY (studentCourseId) REFERENCES tblStudentCourse(studentCourseId)
);
-- One Time Fees Table
CREATE TABLE tblOneTimeFees (
  feeId VARCHAR(255) PRIMARY KEY,
  studentId INT NOT NULL,
  particular VARCHAR(255),
  amount INT,
  dateSubmitted DATE,
  isRefundable INT,
  FOREIGN KEY (studentId) REFERENCES tblStudent(studentId)
);
-- Tuition Fees Table
CREATE TABLE tblTuitionFees (
  feeId VARCHAR(255) PRIMARY KEY,
  studentId INT NOT NULL,
  semester INT,
  amount INT,
  dateSubmitted DATE,
  FOREIGN KEY (studentId) REFERENCES tblStudent(studentId)
);
-- Exam Fees Table
CREATE TABLE tblExamFee (
  feeId VARCHAR(255) PRIMARY KEY,
  studentId INT NOT NULL,
  courseId VARCHAR(255) NOT NULL,
  amount INT,
  dateSubmitted DATE,
  FOREIGN KEY (studentId) REFERENCES tblStudent(studentId),
  FOREIGN KEY (courseId) REFERENCES tblCourse(courseId)
);