Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Preview
Start a free Courses trial
to watch this video
Find out how Jefferson Middle School structures their database!
This video doesn't have any notes.
Related Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign upRelated Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign up
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 upYou need to sign up for Treehouse in order to set up Workspace
Sign up