--
-- Host: localhost    Database: students
-- ------------------------------------------------------


--
-- Table structure for table `calendar`
--

CREATE TABLE `calendar` (
  `key` int NOT NULL,
  `school_year` char(9) NOT NULL,
  `semester` varchar(6) NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `calendar`
--

INSERT INTO `calendar` VALUES (1,'2022-2023','fall','2022-08-19','2022-12-19'),(2,'2022-2023','spring','2023-01-25','2023-05-26'),(3,'2023-2024','fall','2023-08-21','2023-12-21');

--
-- Table structure for table `classes`
--

CREATE TABLE `classes` (
  `key` int NOT NULL,
  `school` varchar(32) NOT NULL,
  `department` varchar(32) NOT NULL,
  `level` varchar(16) NOT NULL,
  `class` varchar(8) NOT NULL,
  `name` varchar(32) NOT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `classes`
--

INSERT INTO `classes` VALUES (1,'Information','Data Science','graduate','DATA 200','Python Programming'),(2,'Information','Data Science','graduate','DATA 220','Mathematical Models'),(3,'Information','Data Science','graduate','DATA 225','Database programming'),(4,'Science','Computer Science','undergraduate','CS 153','Compiler Construction'),(5,'Science','Computer Science','undergraduate','CS 149','Operating Systems'),(6,'Science','Computer Science','graduate','CS 254','Computation Theory'),(7,'Science','Computer Science','graduate','CS 246','Advanced Algorithms'),(8,'Engineering','Electrical Engineering','undergraduate','EE 118','Circuit Design'),(9,'Engineering','Electrical Engineering','undergraduate','EE 120','Microprocessor Design'),(10,'Engineering','Electrical Engineering','graduate','EE 210','System Theory'),(11,'Engineering','Electrical Engineering','graduate','EE 297','Masters Project'),(12,'Science','Physics and Astronomy','undergraduate','PHYS 49','Physics Introduction'),(13,'Science','Physics and Astronomy','undergraduate','PHYS 112','Modern Physics');

--
-- Table structure for table `counts`
--

CREATE TABLE `counts` (
  `when` int NOT NULL,
  `which` int NOT NULL,
  `count` int NOT NULL,
  PRIMARY KEY (`when`,`which`),
  KEY `fk_classes_idx` (`which`),
  CONSTRAINT `fk_calendar` FOREIGN KEY (`when`) REFERENCES `calendar` (`key`),
  CONSTRAINT `fk_classes` FOREIGN KEY (`which`) REFERENCES `classes` (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `counts`
--

INSERT INTO `counts` VALUES (1,1,35),(1,2,40),(1,3,28),(1,4,23),(1,5,26),(1,6,12),(1,7,15),(1,8,25),(1,9,22),(1,10,24),(1,11,17),(1,12,50),(1,13,35),(2,1,31),(2,2,36),(2,3,32),(2,4,27),(2,5,33),(2,6,14),(2,8,24),(2,9,27),(2,10,23),(2,11,15),(2,12,43),(2,13,39),(3,1,25),(3,2,20),(3,3,18),(3,4,19),(3,5,22),(3,6,26),(3,7,18),(3,8,15),(3,9,20),(3,10,21),(3,11,23),(3,12,24),(3,13,20);


