Relational Databases. In this video we're going to look at our database one that
holds student information and classes information, so I'll have a list of
students and a list of classes and we'll have to combine that data in some way.
For the students we might have information such as a student ID, a
student name, and what operating system they're using. And of course we might
have a lot of other information: their address, all sorts of things for the
class, we can have a class ID, a class name, an instructor, and again all sorts
of other information. If we look at the Student Table itself we might have a
table that looks something like this with the student ID the name in the
operating system and Student 1 listed there is Fred Fisher who uses Windows as
an operating system, and student 1002, Ralph Squid, who uses OS X and student
1003 and 1004. All of that information about the students. Now with this, if
we're trying to refer to one of these records (one of these students) we can't
do it by name because we might have two students with the same name. We can't do
it by operating system because we'll definitely have students with the same
operating system. And that would be true of most information, but if we look at
the student ID that we can make unique. So we always have one key field which is
unique that we set up and that's the purpose of having a student ID. Similar
idea with classes: if we have a table here with the class ID, the class name,
and the instructor, we might have class 1,001, Intro to Computers by Bilby Bear,
class 1002, web-design by Michael Glasser, and then class 1003 and class
1004, Database Design and User Interfaces by Bill Gates and Steve Jobs.
Again, you could have class names that are same; one instructor might teach more
than one classroom you might have instructors with the same name. But if we
look at the class ID that we can make unique and so we know that is unique. If
we look at those two tables and we want to be able to relate the information
between them, one way that we can do that is just focus just on those unique
pieces of information. We're not removing the rest of the information from the
table but just to simplify things we're just referring to those records by their
ID. And when we do that we want to connect those -- make
connections between tables -- we can go ahead and have a table that connects the
two that has the student ID and the class ID. What I mean by this is if I
have student 1, who is taking three classes: class 1001, 1002, and 1003, I can
create in that middle table three records that connect those. And I could
do the same thing for student 1002: he's taking two classes and they're listed
there. Student 1003 with the four classes, student 1004 and you can go ahead add as
many students as you want and every time a student enrolls in a class they have a
new record put into that middle table right there which goes ahead and lets
you know which student is in which class. This can become fairly complex. Right
here we have simply four classes and four students. If I had 100 students in
100 classes just imagine how confusing this table can be, but this is a
simplified look. One advantage that this gives us not only can we look at a
student and figure out what classes they're in, if I look at class one, using
the exact same data, I could figure out that there are three students, and it's
students 1001, 1002, and 1003. I could do the same thing with class 1002,
1003, and 1004. I know exactly what student is in
each class. If we go ahead and take a look at our initial tables, the student
table and the class table with their information, we can go ahead add another
table in between them and maybe call it enrollments. This is the table where we
would have a student ID, the class ID -- we can also have more information. This is
enough information to let us know what students are in what class, or when
looking at a class what students are in that class. We also might want to have
grade information or other information, so it's not just tying those two
together it could be giving us key information that we need. With this table
the student ID would be tied to the student ID in enrollments, and in the
classes table, the class ID would be tied to the class ID in enrollments. We would
have one student ID in the student table but in the enrollment we could
that ID showing up many times. Same idea with the classes table. I have one time
that that class ID (it is unique). In the enrollments table it could show up many
times. What we don't have is a unique identification for that enrollment so we
really should add that. So I will go ahead add the enrollment ID and what
this allows us to do is have a student ID that is always unique for that table.
It doesn't have to be unique in other tables but every table has to have one
unique key, and we have that in classes (the class ID) and now we just also have
that in the enrollment ID. You can have far more than three tables. You could
have hundreds of tables, each table with hundreds of records, each record with
hundreds of fields. And you could actually have a whole lot of data in here, but
this just gives an overview on how does all of that data come together and work
together in a relational database.