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

START TRANSACTION;



--
-- Database: `mparthas_schedule`
--

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

--
-- Stand-in structure for view `Class_Schedule`
-- (See below for the actual view)
--
CREATE TABLE `Class_Schedule` (
`Date` date
,`Time_Start` time
,`Time_End` time
,`Location_Room` varchar(45)
,`Type_Structure` varchar(45)
,`Name_Class` varchar(50)
);

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

--
-- Table structure for table `Class_System`
--

CREATE TABLE `Class_System` (
  `Class_SystemID` int(11) NOT NULL,
  `Name_Class` varchar(50) NOT NULL,
  `Date` date NOT NULL,
  `LocationID` int(11) NOT NULL,
  `TypeID` int(11) NOT NULL,
  `TimeID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `Class_System`
--

INSERT INTO `Class_System` (`Class_SystemID`, `Name_Class`, `Date`, `LocationID`, `TypeID`, `TimeID`) VALUES
(1, 'IDSN:521', '2024-08-26', 1, 1, 1),
(2, 'IDSN:521', '2024-09-02', 1, 1, 1),
(3, 'IDSN:521', '2024-09-09', 1, 1, 1),
(4, 'IDSN:521', '2024-09-16', 1, 1, 1),
(5, 'IDSN:524', '2024-08-27', 3, 1, 4),
(6, 'IDSN:524', '2024-09-03', 3, 1, 4),
(7, 'IDSN:524', '2024-09-10', 3, 1, 4),
(8, 'IDSN:524', '2024-09-17', 3, 1, 4),
(57, 'ACAD:324', '2024-08-27', 1, 3, 2),
(58, 'ACAD:324', '2024-08-29', 1, 3, 2),
(59, 'ACAD:324', '2024-09-03', 1, 3, 2),
(60, 'ACAD:324', '2024-09-05', 1, 3, 2),
(61, 'ACAD:324', '2024-09-10', 1, 3, 2),
(62, 'ACAD:324', '2024-09-12', 1, 3, 2),
(63, 'ACAD:324', '2024-09-17', 1, 3, 2),
(64, 'ACAD:324', '2024-09-19', 1, 3, 2),
(65, 'ACAD:276', '2024-08-27', 2, 3, 3),
(66, 'ACAD:276', '2024-08-29', 2, 3, 3),
(67, 'ACAD:276', '2024-09-03', 2, 3, 3),
(68, 'ACAD:276', '2024-09-05', 2, 3, 3),
(69, 'ACAD:276', '2024-09-10', 2, 3, 3),
(70, 'ACAD:276', '2024-09-12', 2, 3, 3),
(71, 'ACAD:276', '2024-09-17', 2, 3, 3),
(72, 'ACAD:276', '2024-09-19', 2, 3, 3),
(85, 'ACAD222', '2024-09-03', 1, 1, 1),
(86, '', '2024-09-12', 1, 1, 1),
(87, '', '2024-09-25', 1, 1, 1),
(88, 'ACAD222', '2024-09-18', 1, 1, 1),
(89, 'ACAD222', '2024-09-11', 1, 1, 1),
(90, 'ACAD222', '2024-09-12', 1, 1, 1),
(91, 'ACAD222', '2024-09-12', 1, 1, 1),
(92, 'IDSN:521', '2024-09-11', 1, 1, 1),
(93, 'IDSN:521', '2024-09-11', 1, 1, 1),
(94, 'qwerty', '2002-10-10', 1, 1, 1),
(95, 'qwerty', '2024-10-10', 1, 1, 1);

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

--
-- Table structure for table `Location`
--

CREATE TABLE `Location` (
  `LocationID` int(11) NOT NULL,
  `Location_Room` varchar(45) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `Location`
--

INSERT INTO `Location` (`LocationID`, `Location_Room`) VALUES
(1, 'IYH213'),
(2, 'IYH210'),
(3, 'IYH110');

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

--
-- Table structure for table `Time`
--

CREATE TABLE `Time` (
  `TimeID` int(11) NOT NULL,
  `Time_Start` time NOT NULL,
  `Time_End` time NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `Time`
--

INSERT INTO `Time` (`TimeID`, `Time_Start`, `Time_End`) VALUES
(1, '17:00:00', '18:50:00'),
(2, '09:00:00', '11:50:00'),
(3, '14:00:00', '16:50:00'),
(4, '17:00:00', '19:50:00');

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

--
-- Table structure for table `Type`
--

CREATE TABLE `Type` (
  `TypeID` int(11) NOT NULL,
  `Type_Structure` varchar(45) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `Type`
--

INSERT INTO `Type` (`TypeID`, `Type_Structure`) VALUES
(1, 'Lab'),
(2, 'Lecture'),
(3, 'Lab/Lecture');

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

--
-- Structure for view `Class_Schedule`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`mparthas`@`localhost` SQL SECURITY DEFINER VIEW `Class_Schedule`  AS SELECT `Class_System`.`Date` AS `Date`, `Time`.`Time_Start` AS `Time_Start`, `Time`.`Time_End` AS `Time_End`, `Location`.`Location_Room` AS `Location_Room`, `Type`.`Type_Structure` AS `Type_Structure`, `Class_System`.`Name_Class` AS `Name_Class` FROM (((`Class_System` join `Time`) join `Type`) join `Location`) WHERE ((`Class_System`.`TimeID` = `Time`.`TimeID`) AND (`Class_System`.`LocationID` = `Location`.`LocationID`) AND (`Class_System`.`TypeID` = `Type`.`TypeID`)) ;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `Class_System`
--
ALTER TABLE `Class_System`
  ADD PRIMARY KEY (`Class_SystemID`),
  ADD KEY `TypeID` (`TypeID`),
  ADD KEY `TimeID` (`TimeID`),
  ADD KEY `LocationID` (`LocationID`);

--
-- Indexes for table `Location`
--
ALTER TABLE `Location`
  ADD PRIMARY KEY (`LocationID`);

--
-- Indexes for table `Time`
--
ALTER TABLE `Time`
  ADD PRIMARY KEY (`TimeID`);

--
-- Indexes for table `Type`
--
ALTER TABLE `Type`
  ADD PRIMARY KEY (`TypeID`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `Class_System`
--
ALTER TABLE `Class_System`
  MODIFY `Class_SystemID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=96;

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

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

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

--
-- Constraints for dumped tables
--

--
-- Constraints for table `Class_System`
--
ALTER TABLE `Class_System`
  ADD CONSTRAINT `Class_System_ibfk_1` FOREIGN KEY (`TypeID`) REFERENCES `Type` (`TypeID`),
  ADD CONSTRAINT `Class_System_ibfk_2` FOREIGN KEY (`TimeID`) REFERENCES `Time` (`TimeID`),
  ADD CONSTRAINT `Class_System_ibfk_3` FOREIGN KEY (`LocationID`) REFERENCES `Location` (`LocationID`);
COMMIT;

