-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- Database: `sutiono_schedule`
--

-- --------------------------------------------------------

--
-- Table structure for table `class_schedule2025`
--

CREATE TABLE `class_schedule2025` (
  `main_id` int(10) UNSIGNED NOT NULL,
  `date` date NOT NULL,
  `start_time` time NOT NULL,
  `end_time` time NOT NULL,
  `course_id` int(10) UNSIGNED NOT NULL,
  `type_id` int(10) UNSIGNED NOT NULL,
  `location_id` int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `class_schedule2025`
--

INSERT INTO `class_schedule2025` (`main_id`, `date`, `start_time`, `end_time`, `course_id`, `type_id`, `location_id`) VALUES
(64, '2025-09-15', '10:00:00', '11:50:00', 23, 1, 1),
(65, '2025-09-22', '10:00:00', '11:50:00', 23, 1, 1),
(66, '2025-09-29', '10:00:00', '11:50:00', 23, 1, 1),
(67, '2025-10-06', '10:00:00', '11:50:00', 23, 1, 1),
(71, '2025-09-17', '10:00:00', '11:50:00', 23, 1, 1),
(72, '2025-09-24', '10:00:00', '11:50:00', 23, 1, 1),
(73, '2025-10-01', '10:00:00', '11:50:00', 23, 1, 1),
(74, '2025-10-08', '10:00:00', '11:50:00', 23, 1, 1),
(78, '2025-09-16', '12:00:00', '13:50:00', 24, 1, 2),
(79, '2025-09-23', '12:00:00', '13:50:00', 24, 1, 2),
(80, '2025-09-30', '12:00:00', '13:50:00', 24, 1, 2),
(81, '2025-10-07', '12:00:00', '13:50:00', 24, 1, 2),
(85, '2025-09-18', '12:00:00', '13:50:00', 24, 1, 2),
(86, '2025-09-25', '12:00:00', '13:50:00', 24, 1, 2),
(87, '2025-10-02', '12:00:00', '13:50:00', 24, 1, 2),
(88, '2025-10-09', '12:00:00', '13:50:00', 24, 1, 2),
(92, '2025-09-16', '14:00:00', '15:50:00', 25, 1, 3),
(93, '2025-09-23', '14:00:00', '15:50:00', 25, 1, 3),
(94, '2025-09-30', '14:00:00', '15:50:00', 25, 1, 3),
(95, '2025-10-07', '14:00:00', '15:50:00', 25, 1, 3),
(99, '2025-09-18', '14:00:00', '15:50:00', 25, 1, 3),
(100, '2025-09-25', '14:00:00', '15:50:00', 25, 1, 3),
(101, '2025-10-02', '14:00:00', '15:50:00', 25, 1, 3),
(102, '2025-10-09', '14:00:00', '15:50:00', 25, 1, 3),
(106, '2025-09-17', '14:00:00', '15:50:00', 26, 1, 4),
(107, '2025-09-24', '14:00:00', '15:50:00', 26, 1, 4),
(108, '2025-10-01', '14:00:00', '15:50:00', 26, 1, 4),
(109, '2025-10-08', '14:00:00', '15:50:00', 26, 1, 4),
(113, '2025-09-16', '16:00:00', '17:50:00', 27, 1, 5),
(114, '2025-09-23', '16:00:00', '17:50:00', 27, 1, 5),
(115, '2025-09-30', '16:00:00', '17:50:00', 27, 1, 5),
(116, '2025-10-07', '16:00:00', '17:50:00', 27, 1, 5),
(120, '2025-09-18', '16:00:00', '17:50:00', 27, 1, 5),
(121, '2025-09-25', '16:00:00', '17:50:00', 27, 1, 5),
(122, '2025-10-02', '16:00:00', '17:50:00', 27, 1, 5),
(123, '2025-10-09', '16:00:00', '17:50:00', 27, 1, 5);

-- --------------------------------------------------------

--
-- Table structure for table `course.table`
--

CREATE TABLE `course.table` (
  `course_id` int(10) UNSIGNED NOT NULL,
  `course_code` varchar(30) NOT NULL,
  `course_name` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `course.table`
--

INSERT INTO `course.table` (`course_id`, `course_code`, `course_name`) VALUES
(23, 'ACAD 324', 'Practice of Design'),
(24, 'ACAD 230', 'Physical Computing'),
(25, 'ACAD 276', 'DEV II'),
(26, 'TAC 104', 'Web Development'),
(27, 'BUAD 304', 'Organizational Behavior and Leadership');

-- --------------------------------------------------------

--
-- Table structure for table `location_table`
--

CREATE TABLE `location_table` (
  `location_id` int(10) UNSIGNED NOT NULL,
  `building` varchar(64) NOT NULL,
  `room` varchar(32) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `location_table`
--

INSERT INTO `location_table` (`location_id`, `building`, `room`) VALUES
(1, 'IYH', '221'),
(2, 'IYH', '110'),
(3, 'IYH', '210'),
(4, 'LVL', '13'),
(5, 'JFFLL', '102');

-- --------------------------------------------------------

--
-- Stand-in structure for view `scheduleAll_view`
-- (See below for the actual view)
--
CREATE TABLE `scheduleAll_view` (
`main_id` int(10) unsigned
,`date` date
,`start_time` time
,`end_time` time
,`course_id` int(10) unsigned
,`type_id` int(10) unsigned
,`location_id` int(10) unsigned
,`course_id_course` int(10) unsigned
,`course_code` varchar(30)
,`course_name` varchar(100)
,`location_id_lookup` int(10) unsigned
,`building` varchar(64)
,`room` varchar(32)
,`type_id_lookup` int(10) unsigned
,`type_name` varchar(32)
);

-- --------------------------------------------------------

--
-- Stand-in structure for view `schedule_view`
-- (See below for the actual view)
--
CREATE TABLE `schedule_view` (
`main_id` int(10) unsigned
,`date` date
,`start_time` time
,`end_time` time
,`course_id` int(10) unsigned
,`type_id` int(10) unsigned
,`location_id` int(10) unsigned
,`course_id_course` int(10) unsigned
,`course_code` varchar(30)
,`course_name` varchar(100)
,`location_id_lookup` int(10) unsigned
,`building` varchar(64)
,`room` varchar(32)
,`type_id_lookup` int(10) unsigned
,`type_name` varchar(32)
);

-- --------------------------------------------------------

--
-- Table structure for table `type.table`
--

CREATE TABLE `type.table` (
  `type_id` int(10) UNSIGNED NOT NULL,
  `type_name` varchar(32) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `type.table`
--

INSERT INTO `type.table` (`type_id`, `type_name`) VALUES
(1, 'Lecture'),
(2, 'Lab'),
(3, 'Lecture/Lab');

-- --------------------------------------------------------

--
-- Table structure for table `weeks4`
--

CREATE TABLE `weeks4` (
  `n` tinyint(3) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `weeks4`
--

INSERT INTO `weeks4` (`n`) VALUES
(0),
(1),
(2),
(3);

-- --------------------------------------------------------

--
-- Structure for view `scheduleAll_view`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`sutiono`@`localhost` SQL SECURITY DEFINER VIEW `scheduleAll_view`  AS SELECT `schedule_view`.`main_id` AS `main_id`, `schedule_view`.`date` AS `date`, `schedule_view`.`start_time` AS `start_time`, `schedule_view`.`end_time` AS `end_time`, `schedule_view`.`course_id` AS `course_id`, `schedule_view`.`type_id` AS `type_id`, `schedule_view`.`location_id` AS `location_id`, `schedule_view`.`course_id_course` AS `course_id_course`, `schedule_view`.`course_code` AS `course_code`, `schedule_view`.`course_name` AS `course_name`, `schedule_view`.`location_id_lookup` AS `location_id_lookup`, `schedule_view`.`building` AS `building`, `schedule_view`.`room` AS `room`, `schedule_view`.`type_id_lookup` AS `type_id_lookup`, `schedule_view`.`type_name` AS `type_name` FROM `schedule_view` LIMIT 0, 40 ;

-- --------------------------------------------------------

--
-- Structure for view `schedule_view`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`sutiono`@`localhost` SQL SECURITY DEFINER VIEW `schedule_view`  AS SELECT `cs`.`main_id` AS `main_id`, `cs`.`date` AS `date`, `cs`.`start_time` AS `start_time`, `cs`.`end_time` AS `end_time`, `cs`.`course_id` AS `course_id`, `cs`.`type_id` AS `type_id`, `cs`.`location_id` AS `location_id`, `c`.`course_id` AS `course_id_course`, `c`.`course_code` AS `course_code`, `c`.`course_name` AS `course_name`, `l`.`location_id` AS `location_id_lookup`, `l`.`building` AS `building`, `l`.`room` AS `room`, `t`.`type_id` AS `type_id_lookup`, `t`.`type_name` AS `type_name` FROM (((`class_schedule2025` `cs` join `course.table` `c` on((`cs`.`course_id` = `c`.`course_id`))) join `location_table` `l` on((`cs`.`location_id` = `l`.`location_id`))) join `type.table` `t` on((`cs`.`type_id` = `t`.`type_id`))) ;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `class_schedule2025`
--
ALTER TABLE `class_schedule2025`
  ADD PRIMARY KEY (`main_id`),
  ADD KEY `idx_course_id` (`course_id`),
  ADD KEY `idx_type_id` (`type_id`),
  ADD KEY `idx_location_id` (`location_id`);

--
-- Indexes for table `course.table`
--
ALTER TABLE `course.table`
  ADD PRIMARY KEY (`course_id`);

--
-- Indexes for table `location_table`
--
ALTER TABLE `location_table`
  ADD PRIMARY KEY (`location_id`);

--
-- Indexes for table `type.table`
--
ALTER TABLE `type.table`
  ADD PRIMARY KEY (`type_id`);

--
-- Indexes for table `weeks4`
--
ALTER TABLE `weeks4`
  ADD PRIMARY KEY (`n`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `class_schedule2025`
--
ALTER TABLE `class_schedule2025`
  MODIFY `main_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=127;

--
-- AUTO_INCREMENT for table `course.table`
--
ALTER TABLE `course.table`
  MODIFY `course_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=28;

--
-- AUTO_INCREMENT for table `location_table`
--
ALTER TABLE `location_table`
  MODIFY `location_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- AUTO_INCREMENT for table `type.table`
--
ALTER TABLE `type.table`
  MODIFY `type_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `class_schedule2025`
--
ALTER TABLE `class_schedule2025`
  ADD CONSTRAINT `fk_sched_course` FOREIGN KEY (`course_id`) REFERENCES `course.table` (`course_id`),
  ADD CONSTRAINT `fk_sched_location` FOREIGN KEY (`location_id`) REFERENCES `location_table` (`location_id`),
  ADD CONSTRAINT `fk_sched_type` FOREIGN KEY (`type_id`) REFERENCES `type.table` (`type_id`);
COMMIT;

