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 system at insert time.

drop table t2;

create table t2(
	pk1 int not null primary key auto_increment,
	visited date not null,
	name varchar(32) not null,
	revenue float(8,2) not null
)engine=innodb;

delimiter //

create procedure ass110(name char(32))
begin
	insert into t2 (visited, name, revenue) 
	        values (current_timestamp, name, rand() * 10000);
end//

Create two tables as follows. The attribute district in t4 is a foreign key, designating a district from t3. Dont forget to use engine=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.

DROP TABLE IF EXISTS `t3`;
CREATE TABLE `t3` (
  `dist` int(11) NOT NULL,
  `repno` int(11) NOT NULL,
  `repname` varchar(32) NOT NULL,
  PRIMARY KEY (`dist`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `t4`;
CREATE TABLE `t4` (
  `cust` int(11) NOT NULL,
  `custname` varchar(32) NOT NULL,
  `district` int(11) NOT NULL,
  `revenue` float(10,2) NOT NULL,
  PRIMARY KEY (`cust`),
  KEY `district` (`district`),
  FOREIGN KEY (`district`) REFERENCES `t3` (`dist`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
create procedure ass111() 
begin
  declare i int;
  declare j int;
  declare k int;
  delete from t4;
  delete from t3;
  set i = 10;
  set k = 1000;
  while i < 15 do
        insert into t3 (dist, repno, repname) values (i, i, concat('Snake Oil Sales Rep ', i));
    set j = 0;
        while j < 10 do
          insert into t4 (cust, custname, district, revenue) 
                  values (k, concat('Sucker ', k), i, rand() * k * 5);
          set j = j + 1;
          set k = k + 1;
        end while;
        set i = i + 1;
  end while;
end;