7.2 Working Examples
To demonstrate our recipes, we'll use a simplified
university system with tables for a student register, a teacher
register, a campus bookstore, student grades, and so forth.
7.2.1 Bookstore
You have a 24 x 7 online sales support system for a campus bookstore.
The bookstore has a warehouse that runs a standalone warehouse
system. The warehouse is run by an external partner, and the
warehouse system comes from a third-party vendor. The warehouse
system calculates the exact stock inventory several times a day and
needs to update the central database used by your sales support
system with that information. The central database is your database,
which is your responsibility.
Assume that your sales support system implements the following table
to keep track of the quantities of books that are available:
CREATE TABLE Bookstore(
BookId INTEGER UNIQUE,
Name CHAR(40),
Quantity INTEGER,
Price DECIMAL(10,2),
Type CHAR(20)
)
For demonstration purposes, let's fill the table
with some data:
BookId Name Quantity Price Type
----------- --------------------------- --------- ------ --------
1 Software Engineering 5 15.00 Manual
2 Modern Operating Systems 7 20.00 Reference
3 Learn SQL 15 18.00 Textbook
4 Learn Advanced SQL 1 8.00 Textbook
5 JavaScript Tutorial 5 10.00 Textbook
6 Modern Operating Systems 7 20.00 Reference
7 Learn SQL 15 18.00 Textbook
This table stores information on every book that is available in your
warehouse. Several times a day, the warehouse transmits new inventory
positions that need to be validated and loaded into this table.
7.2.2 Rankings
Each year, the student-aid
office calculates the average
ranking of students eligible for a university scholarship. The office
holds ranking information in a large spreadsheet that looks as
follows:
Id Name Y2000 Y2001 Y2002
----------- ---------- ----------- ----------- -----------
1 Joe 7 8 9
2 Anna 1 2 3
3 Billy 4 5 6
...
Workers at the student-aid office know how to export their
spreadsheet into an ASCII table, which you will ultimately load into
the following SQL table:
CREATE TABLE StudentRankings(
Id INTEGER,
Name CHAR(10),
Y2000 INTEGER,
Y2001 INTEGER,
Y2002 INTEGER
)
7.2.3 Scores
The Academic Director of the university's
School of Accounting stores the scores
of students in a SQL table named StudentScores. The table structure
is as follows:
CREATE TABLE StudentScores (
CourseId CHAR(20),
StudentName CHAR(40),
Score DECIMAL(5,2)
)
The data that you have to work with in the Scores table looks like
this:
CourseId StudentName Score
-------- ----------- -----
Accn101 Mike 78.3
Bkn1002 Mike 56.5
Tax1232 Mike 89.8
Accn101 Hannah 76.4
Bkn1002 Hannah 67.6
Tax1232 Hannah 78.8
Accn101 Andrew 45.3
Bkn1002 Andrew 45.5
Tax1232 Andrew 68.5
7.2.4 Dissertations
You have a table named StudentThesis
that holds information on Ph.D.
student dissertations and that lists the members of the dissertation
committee in front of which each student defended his thesis:
CREATE TABLE StudentThesis (
StudentId INTEGER,
Member1 INTEGER,
Member2 INTEGER,
Member3 INTEGER,
Grade CHAR(2)
)
The data in the Thesis table looks like this:
StudentId Member1 Member2 Member3 Grade
---------- -------- -------- -------- ------
1 234 322 456 A
2 456 322 344 B
3 456 455 344 A
4 322 123 455 C
The three Member columns identify the professors by their
identification numbers. These numbers can be used in a query against
the Professors table (a master table) to get each
professor's name. Here is a sample of the data in
the Professors table:
Id Name
---- -----
123 Smith, John
456 Newton, Isaac
455 Einstein, Albert
344 Base, Samuel
322 Anderson, Terry
234 Baird, Frances
Similar master records exist for the students in a Student table:
StudentId StudentName
----------- --------------------
1 Bob One
2 Mark Twain
3 Brent Thrice
4 Joe Forth
|
