cosc4606-assignment-02.sql (11943B)
1 -- MariaDB dump 10.19 Distrib 10.6.5-MariaDB, for Linux (x86_64) 2 -- 3 -- Host: localhost Database: cosc4606_assignment_02 4 -- ------------------------------------------------------ 5 -- Server version 10.6.5-MariaDB 6 7 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; 8 /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; 9 /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; 10 /*!40101 SET NAMES utf8mb4 */; 11 /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; 12 /*!40103 SET TIME_ZONE='+00:00' */; 13 /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; 14 /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; 15 /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; 16 /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; 17 18 -- 19 -- Table structure for table `CourseInfo` 20 -- 21 22 DROP TABLE IF EXISTS `CourseInfo`; 23 /*!40101 SET @saved_cs_client = @@character_set_client */; 24 /*!40101 SET character_set_client = utf8 */; 25 CREATE TABLE `CourseInfo` ( 26 `CourseName` varchar(255) NOT NULL, 27 `CourseDesc` varchar(255) DEFAULT NULL, 28 PRIMARY KEY (`CourseName`) 29 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3; 30 /*!40101 SET character_set_client = @saved_cs_client */; 31 32 -- 33 -- Dumping data for table `CourseInfo` 34 -- 35 36 LOCK TABLES `CourseInfo` WRITE; 37 /*!40000 ALTER TABLE `CourseInfo` DISABLE KEYS */; 38 INSERT INTO `CourseInfo` VALUES ('Career Skills','Introduces the student to skills needed for securing employment'),('Computer Software for Sciences','The basic software tools applicable to the Sciences are presented'),('Data Management Systems','Introduction to the design and use of databases management systems'),('Film and Modern History','Focuses on the use of film to portray modern history'),('Introduction to Cultural Anthropology','General introduction to social/cultural anthropology'),('Introduction to Environmental Science','A comprehensive introduction to the science behind the main environmental challenges facing society'),('Introduction to Statistics','Introduction to basic statistical concepts and techniques that are common to all disciplines in the Social Sciences'),('Mobile Application Development I','Introduces the student to one of the major Mobile Computing platforms'),('Techniques of Systems Analysis','Information gathering and reporting'),('Topics in Computer Science I','Treatment of a selection of advanced topics'),('Understanding the Earth','Introduction to Geology for students without a background in science'); 39 /*!40000 ALTER TABLE `CourseInfo` ENABLE KEYS */; 40 UNLOCK TABLES; 41 42 -- 43 -- Table structure for table `Courses` 44 -- 45 46 DROP TABLE IF EXISTS `Courses`; 47 /*!40101 SET @saved_cs_client = @@character_set_client */; 48 /*!40101 SET character_set_client = utf8 */; 49 CREATE TABLE `Courses` ( 50 `CourseCode` varchar(50) NOT NULL, 51 `Section` varchar(50) NOT NULL, 52 `Term` varchar(50) NOT NULL, 53 `Year` int(4) NOT NULL, 54 `CourseName` varchar(50) DEFAULT NULL, 55 PRIMARY KEY (`CourseCode`,`Section`,`Term`,`Year`), 56 KEY `courseinfocourses` (`CourseName`), 57 KEY `coursescoursename` (`CourseName`), 58 CONSTRAINT `courseinfocourses` FOREIGN KEY (`CourseName`) REFERENCES `CourseInfo` (`CourseName`) ON DELETE CASCADE ON UPDATE CASCADE 59 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3; 60 /*!40101 SET character_set_client = @saved_cs_client */; 61 62 -- 63 -- Dumping data for table `Courses` 64 -- 65 66 LOCK TABLES `Courses` WRITE; 67 /*!40000 ALTER TABLE `Courses` DISABLE KEYS */; 68 INSERT INTO `Courses` VALUES ('COOP0101','A','21F',2021,'Career Skills'),('COSC2836','A','22W',2022,'Computer Software for Sciences'),('COSC4606','A','21F',2021,'Data Management Systems'),('FILM2907','A','21F',2021,'Film and Modern History'),('ANTR1007','A','22W',2022,'Introduction to Cultural Anthropology'),('ENVS1006','A','22W',2022,'Introduction to Environmental Science'),('STAT2126','A','21F',2021,'Introduction to Statistics'),('COSC3596','A','22W',2022,'Mobile Application Development I'),('COSC3707','A','22W',2022,'Techniques of Systems Analysis'),('COSC4426','A','21F',2021,'Topics in Computer Science I'),('GEOL1021','A','21F',2021,'Understanding the Earth'); 69 /*!40000 ALTER TABLE `Courses` ENABLE KEYS */; 70 UNLOCK TABLES; 71 72 -- 73 -- Table structure for table `Enrollments` 74 -- 75 76 DROP TABLE IF EXISTS `Enrollments`; 77 /*!40101 SET @saved_cs_client = @@character_set_client */; 78 /*!40101 SET character_set_client = utf8 */; 79 CREATE TABLE `Enrollments` ( 80 `StudentID` int(9) DEFAULT 0, 81 `CourseCode` varchar(50) DEFAULT NULL, 82 `Section` varchar(50) DEFAULT NULL, 83 `Term` varchar(255) DEFAULT NULL, 84 `Year` int(4) DEFAULT 0, 85 `Mark` int(3) DEFAULT 0, 86 `Status` varchar(255) DEFAULT NULL, 87 KEY `coursesenrollments` (`CourseCode`,`Section`,`Term`,`Year`), 88 KEY `enrollmentscoursecode` (`CourseCode`), 89 KEY `enrollmentsstudentid` (`StudentID`), 90 KEY `studentsenrollments` (`StudentID`), 91 CONSTRAINT `coursesenrollments` FOREIGN KEY (`CourseCode`, `Section`, `Term`, `Year`) REFERENCES `Courses` (`CourseCode`, `Section`, `Term`, `Year`) ON DELETE CASCADE ON UPDATE CASCADE, 92 CONSTRAINT `studentsenrollments` FOREIGN KEY (`StudentID`) REFERENCES `Students` (`StudentID`) ON DELETE CASCADE ON UPDATE CASCADE 93 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3; 94 /*!40101 SET character_set_client = @saved_cs_client */; 95 96 -- 97 -- Dumping data for table `Enrollments` 98 -- 99 100 LOCK TABLES `Enrollments` WRITE; 101 /*!40000 ALTER TABLE `Enrollments` DISABLE KEYS */; 102 INSERT INTO `Enrollments` VALUES (189602990,'COSC4426','A','21F',2021,100,'Passed'),(189602990,'COSC4606','A','21F',2021,100,'Passed'),(189602990,'FILM2907','A','21F',2021,100,'Passed'),(189602990,'STAT2126','A','21F',2021,100,'Passed'),(189602990,'GEOL1021','A','21F',2021,100,'Passed'),(189602990,'COSC3707','A','22W',2022,0,'In Progress'),(189602990,'COSC3596','A','22W',2022,0,'In Progress'),(189602990,'ENVS1006','A','22W',2022,0,'In Progress'),(189602990,'COSC2836','A','22W',2022,0,'In Progress'),(189602990,'ANTR1007','A','22W',2022,0,'In Progress'),(465700130,'COSC4606','A','21F',2021,69,'Passed'),(520594330,'COSC4606','A','21F',2021,87,'Passed'),(544000434,'COSC4606','A','21F',2021,73,'Passed'),(716658445,'COSC4606','A','21F',2021,68,'Passed'); 103 /*!40000 ALTER TABLE `Enrollments` ENABLE KEYS */; 104 UNLOCK TABLES; 105 106 -- 107 -- Table structure for table `Faculty` 108 -- 109 110 DROP TABLE IF EXISTS `Faculty`; 111 /*!40101 SET @saved_cs_client = @@character_set_client */; 112 /*!40101 SET character_set_client = utf8 */; 113 CREATE TABLE `Faculty` ( 114 `FacultyID` int(11) NOT NULL DEFAULT 0, 115 `GivenName` varchar(255) DEFAULT NULL, 116 `Surname` varchar(255) DEFAULT NULL, 117 `HomePhoneNum` varchar(255) DEFAULT NULL, 118 PRIMARY KEY (`FacultyID`), 119 KEY `idx_homephonenum` (`HomePhoneNum`) 120 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3; 121 /*!40101 SET character_set_client = @saved_cs_client */; 122 123 -- 124 -- Dumping data for table `Faculty` 125 -- 126 127 LOCK TABLES `Faculty` WRITE; 128 /*!40000 ALTER TABLE `Faculty` DISABLE KEYS */; 129 INSERT INTO `Faculty` VALUES (123456789,'Klaus','Peltsch','226-628-0016'),(876543767,'John','Smith','543-876-8753'); 130 /*!40000 ALTER TABLE `Faculty` ENABLE KEYS */; 131 UNLOCK TABLES; 132 133 -- 134 -- Table structure for table `Instructors` 135 -- 136 137 DROP TABLE IF EXISTS `Instructors`; 138 /*!40101 SET @saved_cs_client = @@character_set_client */; 139 /*!40101 SET character_set_client = utf8 */; 140 CREATE TABLE `Instructors` ( 141 `FacultyID` int(9) NOT NULL DEFAULT 0, 142 `CourseCode` varchar(255) NOT NULL, 143 `Section` varchar(255) NOT NULL, 144 `Term` varchar(50) NOT NULL, 145 `Year` int(4) NOT NULL DEFAULT 0, 146 PRIMARY KEY (`FacultyID`,`CourseCode`,`Section`,`Term`,`Year`), 147 KEY `coursesinstructors` (`CourseCode`,`Section`,`Term`,`Year`), 148 KEY `facultyinstructors` (`FacultyID`), 149 KEY `instructorscoursecode` (`CourseCode`), 150 KEY `instructorsfacultyid` (`FacultyID`), 151 CONSTRAINT `coursesinstructors` FOREIGN KEY (`CourseCode`, `Section`, `Term`, `Year`) REFERENCES `Courses` (`CourseCode`, `Section`, `Term`, `Year`) ON DELETE CASCADE ON UPDATE CASCADE, 152 CONSTRAINT `facultyinstructors` FOREIGN KEY (`FacultyID`) REFERENCES `Faculty` (`FacultyID`) ON DELETE CASCADE ON UPDATE CASCADE 153 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3; 154 /*!40101 SET character_set_client = @saved_cs_client */; 155 156 -- 157 -- Dumping data for table `Instructors` 158 -- 159 160 LOCK TABLES `Instructors` WRITE; 161 /*!40000 ALTER TABLE `Instructors` DISABLE KEYS */; 162 INSERT INTO `Instructors` VALUES (123456789,'COSC2836','A','22W',2022),(123456789,'COSC3596','A','22W',2022),(123456789,'COSC3707','A','22W',2022),(123456789,'COSC4426','A','21F',2021),(123456789,'COSC4606','A','21F',2021),(123456789,'ENVS1006','A','22W',2022); 163 /*!40000 ALTER TABLE `Instructors` ENABLE KEYS */; 164 UNLOCK TABLES; 165 166 -- 167 -- Table structure for table `Students` 168 -- 169 170 DROP TABLE IF EXISTS `Students`; 171 /*!40101 SET @saved_cs_client = @@character_set_client */; 172 /*!40101 SET character_set_client = utf8 */; 173 CREATE TABLE `Students` ( 174 `StudentID` int(9) NOT NULL DEFAULT 0, 175 `GivenName` varchar(50) DEFAULT NULL, 176 `Surname` varchar(50) DEFAULT NULL, 177 `PhoneNumber` varchar(50) DEFAULT NULL, 178 `InCOOP` tinyint(1) NOT NULL DEFAULT 0, 179 `Degree` varchar(50) DEFAULT NULL, 180 PRIMARY KEY (`StudentID`) 181 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3; 182 /*!40101 SET character_set_client = @saved_cs_client */; 183 184 -- 185 -- Dumping data for table `Students` 186 -- 187 188 LOCK TABLES `Students` WRITE; 189 /*!40000 ALTER TABLE `Students` DISABLE KEYS */; 190 INSERT INTO `Students` VALUES (189602990,'Matthew','Carlson','705-249-2301',0,'BSc. Computer Science'),(465700130,'Mircea','Fortunato','506-463-6096',0,'BSc. Biology'),(475357891,'Marlon','Brando','489-653-7631',1,'BSc. Computer Science'),(520594330,'Anna','Atwood','582-400-3003',1,'BA Law and Justice'),(544000434,'Argyro','Samuelsson','204-380-0289',0,'Bachelor of Computer Science'),(716658445,'Penelope','Moen','613-737-3374',1,'BA Psychology'); 191 /*!40000 ALTER TABLE `Students` ENABLE KEYS */; 192 UNLOCK TABLES; 193 194 -- 195 -- Table structure for table `Users` 196 -- 197 198 DROP TABLE IF EXISTS `Users`; 199 /*!40101 SET @saved_cs_client = @@character_set_client */; 200 /*!40101 SET character_set_client = utf8 */; 201 CREATE TABLE `Users` ( 202 `ID` int(11) NOT NULL, 203 `UserName` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL, 204 `Password` char(61) COLLATE utf8mb4_unicode_ci NOT NULL, 205 `Role` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL, 206 PRIMARY KEY (`ID`) 207 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; 208 /*!40101 SET character_set_client = @saved_cs_client */; 209 210 -- 211 -- Dumping data for table `Users` 212 -- 213 214 LOCK TABLES `Users` WRITE; 215 /*!40000 ALTER TABLE `Users` DISABLE KEYS */; 216 INSERT INTO `Users` VALUES (123456789,'kpeltsch','$2y$10$maPq2ULzqUvC2v1Qy8kZLuY88C9S9swEKM9SCowgnJM4M8Nqf0FSG','Instructor'),(189602990,'mcarlson','$2y$10$Qjcz1nIY0G6SpLlt8m0iJOA7zWljpcLxSelwC0XcS0JbfgQkmDtwG','Student'),(465700130,'mfortunato','$2y$10$N/BIKsuyFadj04Fb6IXQT.sa5O/zP.VWoc.yoJDr3ahwbzJX0eT8S','Student'),(475357891,'mbrando','$2y$10$8JqfmhKoVsxt1j5GzKMdUuSgj3.5.HY1vy/vTLJ9jywb/8nkuqvBi','Student'),(520594330,'aatwood','$2y$10$4ie.Kuds71mYqzZlw2UHnePYXUIwFj067rbbWhI1frUtBcvwjW4DK','Student'),(544000434,'asamuelsson','$2y$10$MYzVA0T3ezXh3eid0OvKkOY1VZILMFLBQ716ZFErZH0embBP93gKe','Student'),(654247853,'admin','$2y$10$yrW42UcOPVi/E9fOtpRJMeoUit7P2LflLHqbRcmVOWq33LfsWlp1u','Admin'),(716658445,'pmoen','$2y$10$7a4kyLrOml3uPlusgmmyteWKBwOeckooalbtE0pmGBafkDL3/Xrce','Student'),(987654321,'registrar','$2y$10$0Bh.u9X6J1VgfPlIAov7MuNiVGo.w.rSltpOpkg39o.cjHzlTIgum','Registrar'); 217 /*!40000 ALTER TABLE `Users` ENABLE KEYS */; 218 UNLOCK TABLES; 219 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; 220 221 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; 222 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; 223 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; 224 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; 225 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; 226 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; 227 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; 228 229 -- Dump completed on 2021-11-23 23:23:46