Learning the Database2:02 with Ben Deitch
Find out how Jefferson Middle School structures their database!
Before you start writing queries, 0:00 it's usually a good idea to spend a little time getting acquainted with the database. 0:01 So lets take a minute and see what tables we'll be using, and how they fit together. 0:06 The database at Jefferson Middle School contains seven tables. 0:11 The first table you'll need to know about is the STUDENTS table. 0:14 Which contains an ID primary key, and then the first and 0:17 last names of the student, followed by the student's grade. 0:20 Next is the TEACHERS table, which contains an ID primary key and 0:24 then the teacher's first and last names. 0:28 After that, we've got the SUBJECTS table, 0:31 which contains the different subjects, like math and science. 0:33 This table contains an ID primary key, 0:37 followed by the subject's name, what grade the subject is intended for, and 0:39 then a quick description of what that subject teaches. 0:43 There's also a ROOMS table, which contains one row for each room in the school. 0:46 In here, there's an ID primary key, as well as an integer 0:51 identifying the maximum number of students the room can hold. 0:55 A school day at Jefferson Middle School is made up of 7 periods, 0:57 summarized in the PERIODS table. 1:02 It's got an ID primary key, followed by the start time and duration of the period. 1:04 Also, the ID of each period represents which period it is. 1:09 So the period with an ID of one is going to represent the first period of the day. 1:14 Getting to something a bit more complicated, we've got the CLASSES table. 1:19 Each row in the CLASSES table represents one individual class. 1:23 So for each class, we've got an ID primary key followed by four foreign keys. 1:27 Telling us which subject is being taught, which period it is, 1:32 which teacher teaches it, and which room it's in. 1:36 Finally, we've got the SCHEDULE table, which contains scheduling information for 1:40 all of the students. 1:44 Each row is a pairing between a student and a class. 1:46 So, if we've scheduled everything correctly, 1:49 that means each student should have seven rows in this table. 1:51 All right, now that you know a bit more about how their database is structured, 1:56 lets get you warmed up with a few practice problems. 2:00
You need to sign up for Treehouse in order to download course files.Sign up