7.2 Working ExamplesTo 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 BookstoreYou 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 RankingsEach 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 ScoresThe 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 DissertationsYou 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 |