CS386/586 Introduction to Databases
Fall 2011 Quarter

---

Assignment 5 Constraints, Embedded SQL

Due: Thursday, 10 November 2011 at the beginning of class

You may do this assignment individually or you may work with one partner.  That is, this assignment is to be completed by individuals or by teams of two students.  If you work with a partner, then you should turn in one assignment paper, with both of your names on the paper. You should only talk to the instructor, the TA and your partner about this assignment. You may also post questions to the course mailing list, cs386-list@cs.pdx.edu.

Please turn in your completed assignments on paper. Put your last name, first name, the assignment number in that order in the first line of your assignment.  List last name and first name for your partner, if you have one, on the second line of your assignment. (If you are working with a partner, turn in one assignment paper.)

Part I: Checking Constraints

Sometimes is it necessary or desirable for an application to check constraints on a database, for example, when the DBMS cannot enforce a particular type of constraint. Another example is a “soft constraint”, where we do not want to absolutely forbid a violation, but we want to know if one occurs.

One approach to checking a constraint C is to derive a query Q_C that will detect violations of C. Then, if Q_C returns an empty result, we know the current database satisfies the constraint.

Questions 1-5 below involve creating queries to check different kinds of constraints on the schema below, which corresponds to this (partial) ER diagram:

Emp(empID: integer, eName: string, age: integer, salary: real)

Works(empID: integer, deptID: integer, fraction: real)

Dept(deptID: integer, budget: real, managerID: integer, dName: string)

Question 1 (10 points): Give an SQL query that checks if eName is a key for Emp by finding every eName value that appears in two or more rows.

Question 2 (10 points): Give an SQL query that checks the foreign key from Works to Emp by finding any rows in Works that do not connect to some row in Emp.

Question 3 (15 points): Give an SQL query that checks the constraint that every department has at least one employee, by finding any Dept rows that are not related to at least one Emp row.

Question 4 (15 points): Give an SQL query that checks the constraint that an employee can work in at most five departments.

Question 5 (15 points): Give an SQL query to check the constraint that no employee works more than full time (that is, the fractions for that employee add up to no more than 1.0).

Part II: Injection Attacks

Questions 6 and 7 concern a PHP program that accesses a table with the following schema:

Account(custID: integer, AcctNum: integer, AType: string, Balance: real)

Consider a PHP page that makes use of two variables:

$UID – a string set to a customer ID by a login routine

$ANum – string that the user provided in a web form

Suppose we use these two strings in creating a query:

<?php

$query = “SELECT *

FROM Account

WHERE custID = $UID AND AcctNum = $ANum”

?>

This query is then submitted to the database and the results printed.

Question 6 (10 points): Explain how this scenario allows a customer of the bank to get the account balance for the account of another customer.

Question 7 (10 points): Suppose we try to ensure that the $ANum string contains a number, by using the query below. Is it still possible for a customer to access another customer’s balance?

<?php

$query = “SELECT *

FROM Account

WHERE custID = $UID AND AcctNum = ($ANum * 1)”

?>

Question 8 (10 points): Describe two ways to prevent such “SQL injection attacks” in PHP. You are free to consult any sources you want, but give the answer in your own words, and cite any document you consult.