Assignments
Prev  Chapter 3. MySQL 5.0 Stored Procedures - II  Next

Assignments

A Word on Handing In Assignments

  1. All hand-ins consist of ONE, repeat ONE compressed zip/jar/tgz-archive.
  2. An archive consists of one or more text files and/or code files.
  3. Text processed files must be pdf-files.
  4. Code files must be have their native filename extension, eg. x.java, y.html, etc.
  5. Put your academy login-id into the filename, and an "_7" to number the file individually, eg: EAANILA1_7.zip
  6. Put the token dbxml somewhere in the subject, eg: Assignments dbxml
  7. Mail it to nml@acm.org

Exercise D.0

Create a function isPrime(n int) that returns true (1) if n is a prime number, otherwise it returns false (0). Demonstrate it's usage with some sql statements.

Exercise D.1

Create a function fibonacci(n int) that calculates the n-th Fibonacci number. Demonstrate it's usage with some sql statements.

Assignment D.2

Consider the following scenario. You have seen in in assignment 10.

Example 3.1. Blueprint for t3/t13 and t4/t14 

mysql> describe t3//
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| dist    | int(11)     | NO   | PRI | NULL    |       |
| repno   | int(11)     | NO   |     | NULL    |       |
| repname | varchar(16) | NO   |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> describe t4//
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| cust     | int(11)     | NO   | PRI | NULL    | auto_increment |
| custname | varchar(16) | NO   |     | NULL    |                |
| district | int(11)     | NO   | MUL | NULL    |                |
| revenue  | float       | NO   |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

Create a procedure doing:

  1. Create an innodb table t3. It will contain sales districts, re example above
  2. Create an innodb table t4 for customers belonging to the sales districts of t3. Re example of t4 above
  3. Insert 5 districts into t3, and belonging to each of those 5 districs 10000 customers must be inserted into t4.
  4. This is exactly the section called “Assignment C.1” all over again.
  5. Time the inserts. This is the new bit.

Then create a procedure doing:

  1. Create a engine=myIsam table t13 it will contain sales districts. Use t3 as model.
  2. Create a engine=myIsam table t14 for customers belonging to the mentioned sales districts. Use t4 as model.
  3. Insert 5 districts into t13, and belonging to each of those 5 districs 10000 customers must be inserted into t14
  4. This is almost exactly the section called “Assignment C.1” all over again. The difference is only table name and the engine.
  5. Time the inserts.

Then create a procedure doing:

  1. Create a stored procedure refIntChk that will check for any insert into t14, whether the district exists in table t13. This is basically doing referential integrity checking by procedure instead of letting the innodb engine do it.
  2. Insert 5 new districts into t13, and belonging to each of those 5 districs 10000 customers must be inserted into t14. The 10000 insert must use refIntChk to check for referential integrity towards t13.
  3. Time the inserts.

Hand in:

  1. You must hand in as one big document with all the sql, the test results, and your intelligent comments to the results.
Prev  Up  Next
Style  Home  Chapter 4. Triggers