Section 7.2. Working Examples

   

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 
   


Transact-SQL Cookbook
Transact-SQL Cookbook
ISBN: 1565927567
EAN: 2147483647
Year: 2005
Pages: 152

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net