From: "William H. Hsu" <bhsu@CIS.KSU.EDU>
Date: March 3, 2008 6:58:31 AM CST
To: CIS560-L@LISTSERV.KSU.EDU
Subject: [CIS560-L] [PS1] Homework 1 - model solutions, Problems 4-6
Reply-To: Database System Concepts <CIS560-L@LISTSERV.KSU.EDU>

Class,

Here are model solutions for PS1-4 through PS1-6.

4. Practical Databases.  What do you think is a good server-side programming programming platform for the project, and why? That is, what would you use as the server-side programming language and database application programmer interface (DB API) mechanism?

DB Server and Interface
======================================================================

Angband: ODBC with postgresql is probably ideal, because all of the source code is in C (Lua for the latest v3 distribution) and has been more thoroughly debugged and tested under Linux.  A standalone C/C++ application developed using the platforms listed below would work best.  A server-side JSP or PHP front-end would be feasible.

GradMiner: JDBC or JSP with MySQL would work well, as would Visual C# under the Windows XP (or Vista) operating system.  This is a standalone application with its own data model and front end.  The simpler the data entry interface, the better.  Oracle support for pulling information out of SIS or ISIS would be beneficial if this interface can actually be connected to the K-State information systems.  Note that besides ISIS itself, there is a Degree Audit Reporting System (DARS) front end.

References:
DARS - http://www.k-state.edu/registrar/dars/slideshow/sld001.htm

(Note: Students of CIS 560 since spring, 2007 have completed term projects using JSP only; however, starting in spring, 2008, students may choose to use PHP, Ruby, or SQLAlchemy.)

Grad

Programming Platforms: IDE and Compiler
======================================================================

- Visual C++ 2008 Express Edition under Windows
- g++ (many IDEs, including KDeveloper) or Eclipse CDT (v4 Europa now out) under Linux, MacOS, or Windows

References:
http://dot.kde.org/1040161008/
http://www.eclipse.org

5. Query Example.  Give an example in English of a real select query that a user might submit for the project domain over a web form, and write it in relational algebra.

Grad
----------------------------------------------------------------------

Given schemas:

Student-Names (WID, lastname, firstname, middlename)
Student-GPA (WID, GPA)
Probation-GPA (threshold-GPA)

Show (the names of) all students who have been on probation.

\Pi_{lastname, firstname, middlename)
 [\sigma_{GPA < threshold-GPA} (Probation-GPA \times Student_GPA)
  \bowtie Student-Names]

Angband
----------------------------------------------------------------------

Given schemas:

Monster-Name (MONSTER_ID, name)
Monster-Display (MONSTER_ID, letter, caps, color)
Killed (PLAYER_ID, MONSTER_ID, timestamp, dungeon_level)

Show the name and display character of all monsters killed by player P past dungeon level 10.

\Pi_{name, letter, caps, color)
 [\sigma_{PLAYER_ID = P, dungeon_level > 10} (Killed)
  \bowtie Monster-Name
  \bowtie Monster-Display]

6. Relational Algebra: Queries.  Problem 2.5, parts a, d.  Consider the following relational database above, where the primary keys are underlined.  Give an expression in the relational algebra to express each of the following queries:

a. Find the names of all employees who work for First Bank Corporation.

(Using the abbreviation "FBC" for "First Bank Corporation")

\Pi_{employee-name} (sigma_{company-name = "FBC"} (works))

b. Find the names of all employees in this database who live in the same city as the company for which they work.

\Pi_{employee-name}
    (employee \bowtie works \bowtie company)

-- 
====================================================
William H. Hsu, Ph.D.         bhsu-AT-cis.ksu.edu
Associate Professor of CIS, Kansas State University
Director, Lab for Knowledge Discovery in Databases
http://www.kddresearch.org
ICQ: 28651394 / AIM/YIM/MSN Messenger: hsuwh
====================================================