Relational databases, while easy to create, can sometimes be hard to query, especially when
you have related tables. Imagine that in your database you had two tables:
Class table contains a listing of classes offered, while the
Professor table lists the professors at the university. Obviously there is a relation between these two tables: classes are taught by professors, so, when designing our database, we'd definately have a 1-to-many relationship between these two tables. (One professor can teach many classes...). Here are the table definitions of both
ProfessorID primary key in the
Professor table, and how it is related to the
While this relational design allows for scalability, it makes it a bit difficult to obtain information from both of these tables. Assume that we wanted to list all of the professors, and what classes they taught. We would have to use a
JOIN, like so:
We'd then step through our recordset displaying the professor's name and classes taught. Using some sample data, stepping through this recordset yielded:
1 Dr. Smith Analysis of Algorithms 1 Dr. Smith Artificial Intelligence 2 Dr. Johnson Distributed Systems 3 Dr. Mitchell Formal Languages & Automata Theory 3 Dr. Mitchell Study of Film 3 Dr. Mitchell Software Engineering(Yes, I entered
FirstNamefor all of the professors!) You can see from the sample data that we have three professors, Dr. Smith, Dr. Johnson, and Dr. Mitchell. Dr. Smith teaches two classes, Dr. Johnson one, and Dr. Mitchell three.
Now, assume that we wanted to list all of the classes, and what professor taught it (as opposed to listing the professors, and what classes they taught). We'd just need to modify our SQL statement, but again, we're stuck with a nasty
We'd then step through our recordset displaying the class's name and the professor that taught the class. Using our sample data, stepping through this recordset yielded:
1 Analysis of Algorithms Dr. Smith 2 Artificial Intelligence Dr. Smith 3 Distributed Systems Dr. Johnson 4 Formal Languages & Automata Theory Dr. Mitchell 5 Study of Film Dr. Mitchell 6 Software Engineering Dr. MitchellWhile using
JOINs is an acceptable method of grabbing the related data, we can use what is called Data Shaping to make our lives much easier! Part 2 describes what data shaping is and how we can use it to retrieve related data!