Create a table t2 with an auto_incremented key pk1, an attribute visited of type date, an attribute name to hold a name, and an attribute to hold a random number.

Then create a procedure to insert one row in the table, the name must be given as a parameter, the rest of the values must be given by the DBMS at the tiime of thhe insert.

Hand in as one (1) file assign100.sql. The file must hold the create table declaration, the create procedure declaration, followed by at least 5 call declarations illustrating usage.

Create two tables as follows. The attribute district in t4 is a foreign key, designating a district from t3. Don't forget to use type=innodb on the two tables.

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)

Then create a procedure to create a test database consisting of five (5) districts with 10 customers each. The procedure must start with checking if there is content in the tables and then delete it.

Hand in as one (1) file assign101.sql containing the two create table declarations, the create procedure sentence, and a testrun of the two former.

Skriv en funktion isPrime(n int) som returnerer true (1) hvis n er et primtal, ellers returneres false (0). Demonstrer med nogle sql deklarationer.

Skriv en funktion fibonacci(n int) der beregner de n-te Fibonacci tal. Demonstrer med nogle sql deklarationer.

Overvej om referentiel integritet kan implementeres ved hjælp af stored procedures. Hvis det kan, så skriver du en procedure, der gør det. Skab derefter en test, der dokumenterer at det kan lade sig gøre.