-- 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 */;