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

START TRANSACTION;



--
-- Database: `pamelaxi_schedule`
--

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

--
-- Table structure for table `location`
--

CREATE TABLE `location` (
  `location_id` int(11) NOT NULL,
  `location_name` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `location`
--

INSERT INTO `location` (`location_id`, `location_name`) VALUES
(1, 'WAH105'),
(2, 'THH201'),
(3, 'HAR112D'),
(4, 'IYH211'),
(6, 'IYH210/211'),
(7, 'GFS213');

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

--
-- Table structure for table `name`
--

CREATE TABLE `name` (
  `name_id` int(11) NOT NULL,
  `name_name` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `name`
--

INSERT INTO `name` (`name_id`, `name_name`) VALUES
(1, 'DES302: Design III'),
(2, 'PSYC100: Introduction to Psychology'),
(3, 'DES230: 3D Design: Materials and Tools'),
(4, 'ACAD178: Digital Toolbox: Motion Graphics'),
(5, 'ACAD276: Dev II'),
(6, 'PSYC100_lab: Introduction to Psychology');

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

--
-- Table structure for table `schedule`
--

CREATE TABLE `schedule` (
  `schedule_id` int(11) NOT NULL,
  `dates` date NOT NULL,
  `start_time` time NOT NULL,
  `end_time` time NOT NULL,
  `location_id` int(11) NOT NULL,
  `type_id` int(11) NOT NULL,
  `name_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `schedule`
--

INSERT INTO `schedule` (`schedule_id`, `dates`, `start_time`, `end_time`, `location_id`, `type_id`, `name_id`) VALUES
(1, '2023-09-11', '09:00:00', '11:40:00', 1, 1, 1),
(2, '2023-09-11', '12:00:00', '01:50:00', 2, 1, 2),
(3, '2023-09-12', '09:00:00', '11:40:00', 3, 1, 3),
(4, '2023-09-12', '12:00:00', '01:20:00', 4, 1, 4),
(5, '2023-09-12', '02:00:00', '04:50:00', 6, 1, 5),
(6, '2023-09-13', '09:00:00', '11:40:00', 1, 1, 1),
(7, '2023-09-13', '12:00:00', '01:50:00', 2, 1, 2),
(8, '2023-09-14', '09:00:00', '11:40:00', 3, 1, 3),
(9, '2023-09-14', '12:00:00', '01:20:00', 4, 1, 4),
(10, '2023-09-14', '02:00:00', '04:50:00', 6, 1, 5),
(11, '2023-09-15', '10:00:00', '11:50:00', 7, 2, 6),
(12, '2023-09-18', '09:00:00', '11:40:00', 1, 1, 1),
(13, '2023-09-18', '12:00:00', '01:50:00', 2, 1, 2),
(14, '2023-09-19', '09:00:00', '11:40:00', 3, 1, 3),
(15, '2023-09-19', '12:00:00', '01:20:00', 4, 1, 4),
(16, '2023-09-19', '02:00:00', '04:50:00', 6, 1, 5),
(18, '2023-09-20', '09:00:00', '11:40:00', 1, 1, 1),
(19, '2023-09-20', '12:00:00', '01:50:00', 2, 1, 2),
(20, '2023-09-21', '09:00:00', '11:40:00', 3, 1, 3),
(21, '2023-09-21', '12:00:00', '01:20:00', 4, 1, 4),
(22, '2023-09-21', '02:00:00', '04:50:00', 6, 1, 5),
(58, '2023-09-22', '10:00:00', '11:50:00', 7, 2, 6),
(59, '2023-09-25', '09:00:00', '11:40:00', 1, 1, 1),
(60, '2023-09-25', '12:00:00', '01:50:00', 2, 1, 2),
(61, '2023-09-26', '09:00:00', '11:40:00', 3, 1, 3),
(62, '2023-09-26', '12:00:00', '01:20:00', 4, 1, 4),
(63, '2023-09-26', '02:00:00', '04:50:00', 6, 1, 5),
(64, '2023-09-27', '09:00:00', '11:40:00', 1, 1, 1),
(65, '2023-09-27', '12:00:00', '01:50:00', 2, 1, 2),
(66, '2023-09-28', '09:00:00', '11:40:00', 3, 1, 3),
(67, '2023-09-28', '12:00:00', '01:20:00', 4, 1, 4),
(68, '2023-09-28', '02:00:00', '04:50:00', 6, 1, 5),
(69, '2023-09-29', '10:00:00', '11:50:00', 7, 2, 6),
(70, '2023-10-02', '09:00:00', '11:40:00', 1, 1, 1),
(71, '2023-10-02', '12:00:00', '01:50:00', 2, 1, 2),
(72, '2023-10-03', '09:00:00', '11:40:00', 3, 1, 3),
(73, '2023-10-03', '12:00:00', '01:20:00', 4, 1, 4),
(74, '2023-10-03', '02:00:00', '04:50:00', 6, 1, 5),
(75, '2023-10-04', '09:00:00', '11:40:00', 1, 1, 1),
(76, '2023-10-04', '12:00:00', '01:50:00', 2, 1, 2),
(77, '2023-10-05', '09:00:00', '11:40:00', 3, 1, 3),
(78, '2023-10-05', '12:00:00', '01:20:00', 4, 1, 4),
(79, '2023-10-05', '02:00:00', '04:50:00', 6, 1, 5),
(80, '2023-10-06', '10:00:00', '11:50:00', 7, 2, 6);

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

--
-- Stand-in structure for view `scheduleview1`
-- (See below for the actual view)
--
CREATE TABLE `scheduleview1` (
`dates` date
,`start_time` time
,`end_time` time
,`location_name` varchar(10)
,`type_name` varchar(15)
,`name_name` varchar(50)
);

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

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

CREATE TABLE `type` (
  `type_id` int(11) NOT NULL,
  `type_name` varchar(15) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

INSERT INTO `type` (`type_id`, `type_name`) VALUES
(1, 'lecture'),
(2, 'lab'),
(3, 'lab/lecture');

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

--
-- Structure for view `scheduleview1`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`pamelaxi`@`localhost` SQL SECURITY DEFINER VIEW `scheduleview1`  AS SELECT `schedule`.`dates` AS `dates`, `schedule`.`start_time` AS `start_time`, `schedule`.`end_time` AS `end_time`, `location`.`location_name` AS `location_name`, `type`.`type_name` AS `type_name`, `name`.`name_name` AS `name_name` FROM (((`schedule` join `location`) join `name`) join `type`) WHERE ((`schedule`.`location_id` = `location`.`location_id`) AND (`schedule`.`type_id` = `type`.`type_id`) AND (`schedule`.`name_id` = `name`.`name_id`)) ;

--
-- Indexes for dumped tables
--

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

--
-- Indexes for table `name`
--
ALTER TABLE `name`
  ADD PRIMARY KEY (`name_id`);

--
-- Indexes for table `schedule`
--
ALTER TABLE `schedule`
  ADD PRIMARY KEY (`schedule_id`),
  ADD KEY `location_id` (`location_id`),
  ADD KEY `name_id` (`name_id`),
  ADD KEY `type_id` (`type_id`);

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

--
-- AUTO_INCREMENT for dumped tables
--

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

--
-- AUTO_INCREMENT for table `name`
--
ALTER TABLE `name`
  MODIFY `name_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- AUTO_INCREMENT for table `schedule`
--
ALTER TABLE `schedule`
  MODIFY `schedule_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=81;

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

--
-- Constraints for dumped tables
--

--
-- Constraints for table `schedule`
--
ALTER TABLE `schedule`
  ADD CONSTRAINT `schedule_ibfk_1` FOREIGN KEY (`location_id`) REFERENCES `location` (`location_id`),
  ADD CONSTRAINT `schedule_ibfk_2` FOREIGN KEY (`name_id`) REFERENCES `name` (`name_id`),
  ADD CONSTRAINT `schedule_ibfk_3` FOREIGN KEY (`type_id`) REFERENCES `type` (`type_id`);
COMMIT;

