354 lines
8.9 KiB
SQL
354 lines
8.9 KiB
SQL
-- phpMyAdmin SQL Dump
|
|
-- version 5.2.1
|
|
-- https://www.phpmyadmin.net/
|
|
--
|
|
-- Host: 127.0.0.1
|
|
-- Generation Time: Apr 09, 2025 at 03:09 PM
|
|
-- Server version: 10.4.32-MariaDB
|
|
-- PHP Version: 8.2.12
|
|
|
|
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
|
|
START TRANSACTION;
|
|
SET time_zone = "+00:00";
|
|
|
|
|
|
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
|
|
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
|
|
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
|
|
/*!40101 SET NAMES utf8mb4 */;
|
|
|
|
--
|
|
-- Database: `tiny_college`
|
|
--
|
|
|
|
-- --------------------------------------------------------
|
|
|
|
--
|
|
-- Table structure for table `building`
|
|
--
|
|
|
|
CREATE TABLE `building` (
|
|
`BLDG_CODE` int(11) NOT NULL,
|
|
`BLDG_NAME` varchar(23) DEFAULT NULL,
|
|
`BLDG_LOCATION` text DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
|
|
-- --------------------------------------------------------
|
|
|
|
--
|
|
-- Table structure for table `class`
|
|
--
|
|
|
|
CREATE TABLE `class` (
|
|
`CLASS_CODE` int(11) NOT NULL,
|
|
`CLASS_SECTION` varchar(20) DEFAULT NULL,
|
|
`CLASS_TIME` varchar(20) DEFAULT NULL,
|
|
`CRS_CODE` int(11) DEFAULT NULL,
|
|
`SEMESTER_CODE` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
|
|
-- --------------------------------------------------------
|
|
|
|
--
|
|
-- Table structure for table `course`
|
|
--
|
|
|
|
CREATE TABLE `course` (
|
|
`CRS_CODE` int(11) NOT NULL,
|
|
`DEPT_CODE` int(11) DEFAULT NULL,
|
|
`CRS_TITLE` varchar(20) DEFAULT NULL,
|
|
`CRS_DESCRIPTION` varchar(50) DEFAULT NULL,
|
|
`CRS_CREDIT` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
|
|
-- --------------------------------------------------------
|
|
|
|
--
|
|
-- Table structure for table `department`
|
|
--
|
|
|
|
CREATE TABLE `department` (
|
|
`DEPT_CODE` int(11) NOT NULL,
|
|
`DEPT_NAME` varchar(30) DEFAULT NULL,
|
|
`SCHOOL_CODE` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
|
|
-- --------------------------------------------------------
|
|
|
|
--
|
|
-- Table structure for table `enroll`
|
|
--
|
|
|
|
CREATE TABLE `enroll` (
|
|
`CLASS_CODE` int(11) DEFAULT NULL,
|
|
`STU_NUM` int(11) DEFAULT NULL,
|
|
`ENROLL_DATE` date DEFAULT NULL,
|
|
`ENROLL_GRADE` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
|
|
-- --------------------------------------------------------
|
|
|
|
--
|
|
-- Table structure for table `professor`
|
|
--
|
|
|
|
CREATE TABLE `professor` (
|
|
`PROF_NUM` int(11) NOT NULL,
|
|
`SCHOOL_CODE` int(11) DEFAULT NULL,
|
|
`DEPT_CODE` int(11) DEFAULT NULL,
|
|
`PROF_SPECIALTY` varchar(10) DEFAULT NULL,
|
|
`PROF_RANK` varchar(50) DEFAULT NULL,
|
|
`PROF_LNAME` varchar(255) DEFAULT NULL,
|
|
`PROF_FNAME` varchar(255) DEFAULT NULL,
|
|
`PROF_INITIAL` varchar(4) DEFAULT NULL,
|
|
`PROF_EMAIL` text DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
|
|
-- --------------------------------------------------------
|
|
|
|
--
|
|
-- Table structure for table `room`
|
|
--
|
|
|
|
CREATE TABLE `room` (
|
|
`ROOM_CODE` int(11) NOT NULL,
|
|
`ROOM_TYPE` varchar(23) DEFAULT NULL,
|
|
`BLDG_CODE` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
|
|
-- --------------------------------------------------------
|
|
|
|
--
|
|
-- Table structure for table `school`
|
|
--
|
|
|
|
CREATE TABLE `school` (
|
|
`SCHOOL_CODE` int(11) NOT NULL,
|
|
`SCHOOL_NAME` varchar(100) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
|
|
--
|
|
-- Dumping data for table `school`
|
|
--
|
|
|
|
INSERT INTO `school` (`SCHOOL_CODE`, `SCHOOL_NAME`) VALUES
|
|
(1, 'Liliw National High School'),
|
|
(2, 'Pamantasan ng Lungsod ng San Pablo');
|
|
|
|
-- --------------------------------------------------------
|
|
|
|
--
|
|
-- Table structure for table `semester`
|
|
--
|
|
|
|
CREATE TABLE `semester` (
|
|
`SEMESTER_CODE` int(11) NOT NULL,
|
|
`SEMESTER_YEAR` int(11) DEFAULT NULL,
|
|
`SEMESTER_TERM` int(11) DEFAULT NULL,
|
|
`SEMESTER_START_DATE` int(11) DEFAULT NULL,
|
|
`SEMESTER_END_DATE` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
|
|
-- --------------------------------------------------------
|
|
|
|
--
|
|
-- Table structure for table `student`
|
|
--
|
|
|
|
CREATE TABLE `student` (
|
|
`STU_NUM` int(11) NOT NULL,
|
|
`DEPT_CODE` int(11) DEFAULT NULL,
|
|
`STU_LNAME` varchar(255) DEFAULT NULL,
|
|
`STU_FNAME` varchar(255) DEFAULT NULL,
|
|
`STU_INITIAL` varchar(4) DEFAULT NULL,
|
|
`STU_EMAIL` text DEFAULT NULL,
|
|
`PROF_NUM` int(11) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
|
|
--
|
|
-- Indexes for dumped tables
|
|
--
|
|
|
|
--
|
|
-- Indexes for table `building`
|
|
--
|
|
ALTER TABLE `building`
|
|
ADD PRIMARY KEY (`BLDG_CODE`);
|
|
|
|
--
|
|
-- Indexes for table `class`
|
|
--
|
|
ALTER TABLE `class`
|
|
ADD PRIMARY KEY (`CLASS_CODE`),
|
|
ADD KEY `CRS_CODE` (`CRS_CODE`),
|
|
ADD KEY `SEMESTER_CODE` (`SEMESTER_CODE`);
|
|
|
|
--
|
|
-- Indexes for table `course`
|
|
--
|
|
ALTER TABLE `course`
|
|
ADD PRIMARY KEY (`CRS_CODE`),
|
|
ADD KEY `DEPT_CODE` (`DEPT_CODE`);
|
|
|
|
--
|
|
-- Indexes for table `department`
|
|
--
|
|
ALTER TABLE `department`
|
|
ADD PRIMARY KEY (`DEPT_CODE`),
|
|
ADD KEY `SCHOOL_CODE` (`SCHOOL_CODE`);
|
|
|
|
--
|
|
-- Indexes for table `enroll`
|
|
--
|
|
ALTER TABLE `enroll`
|
|
ADD KEY `STU_NUM` (`STU_NUM`),
|
|
ADD KEY `CLASS_CODE` (`CLASS_CODE`);
|
|
|
|
--
|
|
-- Indexes for table `professor`
|
|
--
|
|
ALTER TABLE `professor`
|
|
ADD PRIMARY KEY (`PROF_NUM`),
|
|
ADD KEY `SCHOOL_CODE` (`SCHOOL_CODE`),
|
|
ADD KEY `DEPT_CODE` (`DEPT_CODE`);
|
|
|
|
--
|
|
-- Indexes for table `room`
|
|
--
|
|
ALTER TABLE `room`
|
|
ADD PRIMARY KEY (`ROOM_CODE`),
|
|
ADD KEY `BLDG_CODE` (`BLDG_CODE`);
|
|
|
|
--
|
|
-- Indexes for table `school`
|
|
--
|
|
ALTER TABLE `school`
|
|
ADD PRIMARY KEY (`SCHOOL_CODE`);
|
|
|
|
--
|
|
-- Indexes for table `semester`
|
|
--
|
|
ALTER TABLE `semester`
|
|
ADD PRIMARY KEY (`SEMESTER_CODE`);
|
|
|
|
--
|
|
-- Indexes for table `student`
|
|
--
|
|
ALTER TABLE `student`
|
|
ADD PRIMARY KEY (`STU_NUM`),
|
|
ADD KEY `DEPT_CODE` (`DEPT_CODE`),
|
|
ADD KEY `PROF_NUM` (`PROF_NUM`);
|
|
|
|
--
|
|
-- AUTO_INCREMENT for dumped tables
|
|
--
|
|
|
|
--
|
|
-- AUTO_INCREMENT for table `building`
|
|
--
|
|
ALTER TABLE `building`
|
|
MODIFY `BLDG_CODE` int(11) NOT NULL AUTO_INCREMENT;
|
|
|
|
--
|
|
-- AUTO_INCREMENT for table `class`
|
|
--
|
|
ALTER TABLE `class`
|
|
MODIFY `CLASS_CODE` int(11) NOT NULL AUTO_INCREMENT;
|
|
|
|
--
|
|
-- AUTO_INCREMENT for table `course`
|
|
--
|
|
ALTER TABLE `course`
|
|
MODIFY `CRS_CODE` int(11) NOT NULL AUTO_INCREMENT;
|
|
|
|
--
|
|
-- AUTO_INCREMENT for table `department`
|
|
--
|
|
ALTER TABLE `department`
|
|
MODIFY `DEPT_CODE` int(11) NOT NULL AUTO_INCREMENT;
|
|
|
|
--
|
|
-- AUTO_INCREMENT for table `professor`
|
|
--
|
|
ALTER TABLE `professor`
|
|
MODIFY `PROF_NUM` int(11) NOT NULL AUTO_INCREMENT;
|
|
|
|
--
|
|
-- AUTO_INCREMENT for table `room`
|
|
--
|
|
ALTER TABLE `room`
|
|
MODIFY `ROOM_CODE` int(11) NOT NULL AUTO_INCREMENT;
|
|
|
|
--
|
|
-- AUTO_INCREMENT for table `school`
|
|
--
|
|
ALTER TABLE `school`
|
|
MODIFY `SCHOOL_CODE` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
|
|
|
|
--
|
|
-- AUTO_INCREMENT for table `semester`
|
|
--
|
|
ALTER TABLE `semester`
|
|
MODIFY `SEMESTER_CODE` int(11) NOT NULL AUTO_INCREMENT;
|
|
|
|
--
|
|
-- AUTO_INCREMENT for table `student`
|
|
--
|
|
ALTER TABLE `student`
|
|
MODIFY `STU_NUM` int(11) NOT NULL AUTO_INCREMENT;
|
|
|
|
--
|
|
-- Constraints for dumped tables
|
|
--
|
|
|
|
--
|
|
-- Constraints for table `class`
|
|
--
|
|
ALTER TABLE `class`
|
|
ADD CONSTRAINT `class_ibfk_1` FOREIGN KEY (`CRS_CODE`) REFERENCES `course` (`CRS_CODE`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
ADD CONSTRAINT `class_ibfk_2` FOREIGN KEY (`SEMESTER_CODE`) REFERENCES `semester` (`SEMESTER_CODE`) ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
--
|
|
-- Constraints for table `course`
|
|
--
|
|
ALTER TABLE `course`
|
|
ADD CONSTRAINT `course_ibfk_1` FOREIGN KEY (`DEPT_CODE`) REFERENCES `department` (`DEPT_CODE`) ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
--
|
|
-- Constraints for table `department`
|
|
--
|
|
ALTER TABLE `department`
|
|
ADD CONSTRAINT `department_ibfk_1` FOREIGN KEY (`SCHOOL_CODE`) REFERENCES `school` (`SCHOOL_CODE`) ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
--
|
|
-- Constraints for table `enroll`
|
|
--
|
|
ALTER TABLE `enroll`
|
|
ADD CONSTRAINT `enroll_ibfk_1` FOREIGN KEY (`STU_NUM`) REFERENCES `student` (`STU_NUM`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
ADD CONSTRAINT `enroll_ibfk_2` FOREIGN KEY (`CLASS_CODE`) REFERENCES `class` (`CLASS_CODE`) ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
--
|
|
-- Constraints for table `professor`
|
|
--
|
|
ALTER TABLE `professor`
|
|
ADD CONSTRAINT `professor_ibfk_1` FOREIGN KEY (`SCHOOL_CODE`) REFERENCES `school` (`SCHOOL_CODE`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
ADD CONSTRAINT `professor_ibfk_2` FOREIGN KEY (`DEPT_CODE`) REFERENCES `department` (`DEPT_CODE`) ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
--
|
|
-- Constraints for table `room`
|
|
--
|
|
ALTER TABLE `room`
|
|
ADD CONSTRAINT `room_ibfk_1` FOREIGN KEY (`BLDG_CODE`) REFERENCES `building` (`BLDG_CODE`) ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
--
|
|
-- Constraints for table `student`
|
|
--
|
|
ALTER TABLE `student`
|
|
ADD CONSTRAINT `student_ibfk_1` FOREIGN KEY (`DEPT_CODE`) REFERENCES `department` (`DEPT_CODE`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
ADD CONSTRAINT `student_ibfk_2` FOREIGN KEY (`PROF_NUM`) REFERENCES `professor` (`PROF_NUM`) ON DELETE CASCADE ON UPDATE CASCADE;
|
|
COMMIT;
|
|
|
|
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
|
|
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
|
|
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
|