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

START TRANSACTION;



--
-- Database: `rafaelv8_schedule3`
--

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

--
-- Table structure for table `courses`
--

CREATE TABLE `courses` (
  `course_id` int(11) NOT NULL,
  `code` varchar(32) NOT NULL,
  `title` varchar(128) NOT NULL,
  `section` varchar(32) NOT NULL DEFAULT '',
  `instructor` varchar(128) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `courses`
--

INSERT INTO `courses` (`course_id`, `code`, `title`, `section`, `instructor`) VALUES
(1, 'BUAD-280', 'Introduction to Financial Accounting', '20253_XXXXX', NULL),
(2, 'ACAD-276', 'Dev II', '20253_10242', NULL),
(3, 'BUAD-306', 'Business Finance', '20253_XXXXX', NULL),
(4, 'ACAD-324', 'The Practice of Design: Ideation to Innovation', '20253_XXXXX', NULL);

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

--
-- Table structure for table `locations`
--

CREATE TABLE `locations` (
  `location_id` int(11) NOT NULL,
  `building` varchar(64) NOT NULL,
  `room` varchar(32) NOT NULL,
  `campus` varchar(64) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `locations`
--

INSERT INTO `locations` (`location_id`, `building`, `room`, `campus`) VALUES
(1, 'JFF', '236', 'UPC'),
(2, 'IYH', '210', 'UPC'),
(3, 'HOH', '1', 'UPC');

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

--
-- Table structure for table `meetings`
--

CREATE TABLE `meetings` (
  `meeting_id` int(11) NOT NULL,
  `course_id` int(11) NOT NULL,
  `location_id` int(11) NOT NULL,
  `meeting_type_id` int(11) NOT NULL,
  `meeting_date` date NOT NULL,
  `start_time` time NOT NULL,
  `end_time` time NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `meetings`
--

INSERT INTO `meetings` (`meeting_id`, `course_id`, `location_id`, `meeting_type_id`, `meeting_date`, `start_time`, `end_time`) VALUES
(1, 2, 2, 3, '2025-08-26', '14:00:00', '15:50:00'),
(2, 2, 2, 3, '2025-08-28', '14:00:00', '15:50:00'),
(4, 2, 2, 3, '2025-09-02', '14:00:00', '15:50:00'),
(5, 2, 2, 3, '2025-09-04', '14:00:00', '15:50:00'),
(6, 2, 2, 3, '2025-09-09', '14:00:00', '15:50:00'),
(7, 2, 2, 3, '2025-09-11', '14:00:00', '15:50:00'),
(8, 2, 2, 3, '2025-09-16', '14:00:00', '15:50:00'),
(9, 2, 2, 3, '2025-09-18', '14:00:00', '15:50:00'),
(11, 3, 3, 1, '2025-08-26', '12:30:00', '13:50:00'),
(12, 3, 3, 1, '2025-08-28', '12:30:00', '13:50:00'),
(14, 3, 3, 1, '2025-09-02', '12:30:00', '13:50:00'),
(15, 3, 3, 1, '2025-09-04', '12:30:00', '13:50:00'),
(16, 3, 3, 1, '2025-09-09', '12:30:00', '13:50:00'),
(17, 3, 3, 1, '2025-09-11', '12:30:00', '13:50:00'),
(18, 3, 3, 1, '2025-09-16', '12:30:00', '13:50:00'),
(19, 3, 3, 1, '2025-09-18', '12:30:00', '13:50:00'),
(21, 1, 1, 1, '2025-08-25', '08:00:00', '09:20:00'),
(22, 1, 1, 1, '2025-08-27', '08:00:00', '09:20:00'),
(24, 1, 1, 1, '2025-09-01', '08:00:00', '09:20:00'),
(25, 1, 1, 1, '2025-09-03', '08:00:00', '09:20:00'),
(26, 1, 1, 1, '2025-09-08', '08:00:00', '09:20:00'),
(27, 1, 1, 1, '2025-09-10', '08:00:00', '09:20:00'),
(28, 1, 1, 1, '2025-09-15', '08:00:00', '09:20:00'),
(29, 1, 1, 1, '2025-09-17', '08:00:00', '09:20:00'),
(31, 4, 2, 1, '2025-08-25', '10:00:00', '11:50:00'),
(32, 4, 2, 1, '2025-08-27', '10:00:00', '11:50:00'),
(34, 4, 2, 1, '2025-09-01', '10:00:00', '11:50:00'),
(35, 4, 2, 1, '2025-09-03', '10:00:00', '11:50:00'),
(36, 4, 2, 1, '2025-09-08', '10:00:00', '11:50:00'),
(37, 4, 2, 1, '2025-09-10', '10:00:00', '11:50:00'),
(38, 4, 2, 1, '2025-09-15', '10:00:00', '11:50:00'),
(39, 4, 2, 1, '2025-09-17', '10:00:00', '11:50:00');

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

--
-- Table structure for table `meeting_types`
--

CREATE TABLE `meeting_types` (
  `meeting_type_id` int(11) NOT NULL,
  `name` varchar(32) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `meeting_types`
--

INSERT INTO `meeting_types` (`meeting_type_id`, `name`) VALUES
(2, 'Lab'),
(1, 'Lecture'),
(3, 'Lecture/Lab');

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

--
-- Stand-in structure for view `v_schedule_pretty2`
-- (See below for the actual view)
--
CREATE TABLE `v_schedule_pretty2` (
`meeting_id` int(11)
,`Date` date
,`Start` time
,`End` time
,`Building` varchar(64)
,`Room` varchar(32)
,`Type` varchar(32)
,`Class` varchar(128)
,`course_id` int(11)
,`location_id` int(11)
,`meeting_type_id` int(11)
);

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

--
-- Structure for view `v_schedule_pretty2`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`rafaelv8`@`localhost` SQL SECURITY DEFINER VIEW `v_schedule_pretty2`  AS SELECT `m`.`meeting_id` AS `meeting_id`, `m`.`meeting_date` AS `Date`, `m`.`start_time` AS `Start`, `m`.`end_time` AS `End`, `l`.`building` AS `Building`, `l`.`room` AS `Room`, `mt`.`name` AS `Type`, `c`.`title` AS `Class`, `m`.`course_id` AS `course_id`, `m`.`location_id` AS `location_id`, `m`.`meeting_type_id` AS `meeting_type_id` FROM (((`meetings` `m` join `courses` `c`) join `locations` `l`) join `meeting_types` `mt`) WHERE ((`m`.`course_id` = `c`.`course_id`) AND (`m`.`location_id` = `l`.`location_id`) AND (`m`.`meeting_type_id` = `mt`.`meeting_type_id`)) ;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `courses`
--
ALTER TABLE `courses`
  ADD PRIMARY KEY (`course_id`),
  ADD UNIQUE KEY `uq_course` (`code`,`section`);

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

--
-- Indexes for table `meetings`
--
ALTER TABLE `meetings`
  ADD PRIMARY KEY (`meeting_id`);

--
-- Indexes for table `meeting_types`
--
ALTER TABLE `meeting_types`
  ADD PRIMARY KEY (`meeting_type_id`),
  ADD UNIQUE KEY `uq_meeting_type` (`name`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `courses`
--
ALTER TABLE `courses`
  MODIFY `course_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `locations`
--
ALTER TABLE `locations`
  MODIFY `location_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT for table `meetings`
--
ALTER TABLE `meetings`
  MODIFY `meeting_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=41;

--
-- AUTO_INCREMENT for table `meeting_types`
--
ALTER TABLE `meeting_types`
  MODIFY `meeting_type_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
COMMIT;

