Databases

Assignments DB.1

Assignment B.0

A college requests an it system to facilitate handling classes of students. One thing they need is class lists showing names, addresses, email-addresses, etc. The admin office and the students are also keen on knowing what teacher teaches which subject in a class.

Design an ER model that contains the data necessary to do just that. Discuss briefly your deliberations. Hand in a text document with an ER diagram.

Assignment B.0 Model Solution

...

Figure B.20. Solution ER

Assignment B.1

A company has departments, employees, and sites. An employee works at a single site for a department. A department is at a single site. A site has, possibly, several departments. Create a few illustrative attributes for the entities. The model must reflect who works at what department.

Design an ER model that contains the data necessary to do just that. Discuss briefly your deliberations. Hand in a text document with an ER diagram.

Assignment B.1 Model Solution

...

Figure B.21. Solution ER

Assignment B.2

Consider a case, where a branch of a company has several cars, but a car is associated with a single branch only. The car is used by a single employee and an employee can use only a single car. Some employees are not issued a car. The model must reflect who works at what branch.

Design an ER model that contains the data necessary to do just that. Discuss briefly your deliberations. Hand in a text document with an ER diagram.

Assignment B.2 Model Solution

...

Figure B.22. Solution ER

Assignment B.3

The local office of the bureau of the census, in Danish: folkeregister, has a database holding all inhabitants. It wants to know who married whom in a given year. The display must contain the names of the newlyweds, their cpr-numbers, and the wedding date.

Design an ER model that contains the data necessary to do just that. Discuss briefly your deliberations. Hand in a text document with an ER diagram.

Assignment B.3 Model Solution

...

Figure B.23. Solution ER

Assignment B.4

Having solved the previous assignment you must extend the model so that

  1. the poor people who were incompatible may divorce. We need to know when the divorce is effective.
  2. the lucky people who have children may have their parenthood reflected in the database.
  3. people may die, and have the date of their death recorded in the database preferably in such a way that the design principle of no nulls is maintained.

Design an ER model that contains the data necessary to do just that. Discuss briefly your deliberations. Hand in a text document with an ER diagram.

Assignment B.4 Model Solution

...

Figure B.24. Solution ER

Assignments DB.2

Assignment DB.2.0

A sanitation company has made agreements with several transport companies who supply various kinds of trucks. Some trucks take gardening waste, others normal household garbage. Yet others take big items such as old sofas, refrigerators etc. You must make a model for the database of a system where the garbage disposal is done by the trucks in various neighbourhoods, each comprised of several roads. No trucker has all kinds of trucks, but some of them have more than one.

The system must hold data to produce lists of affiliated trucking companies and their possibilities regarding types of grabage they handle. The system must also be able to produce web pages for the citizens regarding what weekdays they can expect their garbage to be picked up from their neighbourhood.

Assignment DB.2.0
Example B.93. Possible Solution
Figure B.25. Sanitation Database


Assignment DB.2.1

In a class of students, each member has special qualifications in two subjects from Communication, Business, Design, and Programming (Interaction). You must model a database useful for creating student groups for project work. There may be a number of simultaneous projects. You are allowed to participate in projects only with your special quals. You can only be in one place at a time.

When a project has finished, there must be data in the database for producing a list of member contributions of hours per qualification supplied. This information of contributed hours is backed up and removed from the database after projects have ended. The information of each student's qualifications must remain in the database.

Assignment DB.2.1
Example B.94. Possible Solution
Figure B.26. Project Qualification Database


Solutions DB.3

Assignment DB.3.0

Given the following

Figure B.27. World

Ask the teacher the necessary questions for creating the database worldTest, and for implementing the tables city, country, speaks, and countrylanguage.

When this is succesful, insert your home country into the database. Insert two languages spoken in your country, and insert 4-5 cities as well.

All SQL entered into one text file db31.sql which you will test, and, when it is error free, hand in.

Assignment DB.3.0
drop database worldtest;
create database worldtest;
use worldtest;

create table country (
  code char(3) not null default '',
  name varchar(52) default null,
  continent enum('asia','europe','north america','africa',
                 'oceania','antarctica','south america') 
                    not null default 'asia',
  region varchar(26) default null,
  surfacearea float(10,2) not null default '0.00',
  indepyear smallint default null,
  population int not null default '0',
  lifeexpectancy float(3,1) default null,
  gnp float(10,2) default null,
  gnpold float(10,2) default null,
  localname varchar(45) default null,
  governmentform char(45) not null default '',
  headofstate varchar(60) default null,
  capital int default null,
  code2 char(2) not null default '',
  primary key (code)
);

create table countrylanguage (
  countrycode char(3) not null default '',
  language char(30) not null default '',
  isofficial enum('t','f') not null default 'f',
  percentage float(4,1) not null default '0.0',
  primary key (countrycode,language),
  foreign key (countrycode) references country (code)
);

create table speaks (
  countrycode char(3) not null,
  language char(30) not null,
  primary key (countrycode,language),
  foreign key (countrycode) references country (code),
  foreign key (countrycode, language) references countrylanguage(countrycode, language)
);

create table city (
  id int not null auto_increment,
  name varchar(35) default null,
  countrycode char(3) not null default '',
  district varchar(20) default null,
  population int not null default '0',
  primary key (id),
  foreign key (countrycode) references country (code)
);

insert into country values ('DNK','Denmark','Europe','Nordic Countries',43094.00,800,5330000,76.5,174099.00,169264.00,'Danmark','Constitutional Monarchy','Margrethe II',3315,'DK');
insert into city values (3315, 'København', 'DNK', 'Udkantsdanmark Ø', 500000);
insert into city values (3316, 'Aarhus', 'DNK', 'Region Midt', 300000);
insert into city values (3317, 'Odense', 'DNK', 'Region Syd', 180000);
insert into countrylanguage values ('DNK','Danish', 't', 98.1);
insert into countrylanguage values ('DNK','Swedish', 'f', 1.1);
Example B.95. On Second Thought
Figure B.28. Better Model of World

drop table if exists country;
create table country (
  code char(3) not null default '',
  name varchar(52) default null,
  continent enum('asia','europe','north america','africa',
                 'oceania','antarctica','south america') 
                    not null default 'asia',
  region varchar(26) default null,
  surfacearea float(10,2) not null default '0.00',
  indepyear smallint default null,
  population int not null default '0',
  lifeexpectancy float(3,1) default null,
  gnp float(10,2) default null,
  gnpold float(10,2) default null,
  localname varchar(45) default null,
  governmentform char(45) not null default '',
  headofstate varchar(60) default null,
  capital int default null,
  code2 char(2) not null default '',
  primary key (code)
);

drop table if exists countrylanguage;
create table countrylanguage (
  countrycode char(3) not null default '',
  language char(30) not null default '',
  isofficial enum('t','f') not null default 'f',
  percentage float(4,1) not null default '0.0',
  primary key (countrycode,language),
  foreign key (countrycode) references country (code)
);

drop table if exists city;
create table city (
  id int not null auto_increment,
  name varchar(35) default null,
  countrycode char(3) not null default '',
  district varchar(20) default null,
  population int not null default '0',
  primary key (id),
  foreign key (countrycode) references country (code)
);

insert into country values ('DNK','Denmark','Europe','Nordic Countries',43094.00,800,5330000,76.5,174099.00,169264.00,'Danmark','Constitutional Monarchy','Margrethe II',3315,'DK');
insert into city values (3315, 'København', 'DNK', 'Udkantsdanmark Ø', 500000);
insert into city values (3316, 'Aarhus', 'DNK', 'Region Midt', 300000);
insert into city values (3317, 'Odense', 'DNK', 'Region Syd', 180000);
insert into countrylanguage values ('DNK','Danish', 't', 98.1);
insert into countrylanguage values ('DNK','Swedish', 'f', 1.1);

Assignment DB.3.1

Take a look at the following print out from a live database:

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)
  1. Create a table t3. It will contain sales districts, re example above
  2. Create atable 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 2-3 customers must be inserted into t4.
  4. Put the create database, the create table, and the insert declarations into one text file which you will hand in.
Assignment DB.3.1
drop database if exists T34;
create database T34;
use T34;

create table t3 (
    dist int not null primary key auto_increment,
    repno int not null,
    repname varchar(16) not null
);

create table t4 (
    cust int not null primary key auto_increment,
    custname varchar(16) not null,
    district int not null,
    revenue float not null,
    foreign key (district) references t3 (dist)
);

insert into t3 values (10, 1000, 'Anderson');
insert into t3 values (11, 1001, 'Murray');
insert into t3 values (12, 1002, 'Nadal');
insert into t3 values (13, 1003, 'Federer');
insert into t3 values (14, 1004, 'Williams');

insert into t4 (custname, district, revenue) values ('Muller', 14, 200360);
insert into t4 (custname, district, revenue) values ('Larsen', 14, 35010);
insert into t4 (custname, district, revenue) values ('Thorsen', 13, 34000);
insert into t4 (custname, district, revenue) values ('Jakobsen', 13, 45700);
insert into t4 (custname, district, revenue) values ('Merkel', 12, 123000);
insert into t4 (custname, district, revenue) values ('Hollande', 12, 35000);
insert into t4 (custname, district, revenue) values ('Cameron', 11, 244000);
insert into t4 (custname, district, revenue) values ('Schipol', 11, 35120);
insert into t4 (custname, district, revenue) values ('Mahler', 10, 78400);
insert into t4 (custname, district, revenue) values ('Hölzenbein', 10, 9600);

Assignments SQL Data

Assignment Data.7.0

In the world database you will find a table countrylanguage.

  • Write an SQL statement that reads all columns from 'AFG', Afghanistan.
  • Write an SQL statement that reads all columns from 'TJK', Tajikistan
  • Write an SQL statement that reads all columns from 'UZB', Uzbekistan
  • Write an SQL statement that reads all columns from 'UZB', 'TJK', and 'AFG'
  • Now, verbally, not SQL, assuming these were the only countries in the world, how would you calculate how many persons speak each language in the world? Your data are:
    +-------------+------------+------------+------------+
    | countrycode | language   | isofficial | percentage |
    +-------------+------------+------------+------------+
    | AFG         | Balochi    | F          |        0.9 |
    | AFG         | Dari       | T          |       32.1 |
    | AFG         | Pashto     | T          |       52.4 |
    | AFG         | Turkmenian | F          |        1.9 |
    | AFG         | Uzbek      | F          |        8.8 |
    | TJK         | Russian    | F          |        9.7 |
    | TJK         | Tadzhik    | T          |       62.2 |
    | TJK         | Uzbek      | F          |       23.2 |
    | UZB         | Karakalpak | F          |        2.0 |
    | UZB         | Kazakh     | F          |        3.8 |
    | UZB         | Russian    | F          |       10.9 |
    | UZB         | Tadzhik    | F          |        4.4 |
    | UZB         | Tatar      | F          |        1.8 |
    | UZB         | Uzbek      | T          |       72.6 |
    +-------------+------------+------------+------------+
  • What actual information is missing? Where would you find it?

You must write the declarations as a text file. Execute the text file in MySQL workbench. Hand in the textfile.

Assignment Data.7.0 Model Solution
select * 
from countrylanguage
where countrycode = 'AFG';

select * 
from countrylanguage
where countrycode = 'TJK';

select *
from countrylanguage
where countrycode = 'UZB';

select * 
from countrylanguage
where countrycode = 'AFG'
or countrycode = 'TJK'
or countrycode = 'UZB';

select *
from countrylanguage
where countrycode in ('AFG', 'TJK', 'UZB');

-- for each countrylanguage row multiply percentage with country population
-- add them up per language
-- country population is missing
Assignment Data.7.1

From the table countrylanguage:

  • Write an SQL statement that reads all countries that have 'Danish' as a language.
  • Write an SQL statement that shows what languages they speak in South Africa. Order them alphabetically.
  • Write an SQL statement that shows what languages they speak in 'UZB', 'TJK', and 'AFG'. Order them in descending order by percentage.

You must write the declarations as a text file. Execute the text file in MySQL workbench. Hand in the textfile.

Assignment Data.7.1 Model Solution
select language, countrycode
from countrylanguage
where language = 'Danish';

select language, countrycode
from countrylanguage
where countrycode = 'ZAF'
order by language;

select language, countrycode, percentage
from countrylanguage
where countrycode in ('AFG', 'TJK', 'UZB')
order by percentage desc;
Assignment Data.7.2

From the table country:

  • Write an SQL statement that lists the head of state of all European countries.
  • Write an SQL statement that lists the code, name and population of countries in the region 'Southern and Central Asia'. Sort them by population.

You must write the declarations as a text file. Execute the text file in MySQL workbench. Hand in the textfile.

Assignment Data.7.2 Model Solution
select headofstate, name
from country
where continent = 'Europe';

select code, name, population
from country
where region = 'Southern and Central Asia'
order by population;

select name, governmentform 
from country
where governmentform like '%onarchy%'
order by governmentform, name;

Assignments SQL Schema

Assignment Data.8.0

Please write a query with country name, country population, continent, name of capital, population of capital from all countries in Oceania, Antarctica, and South America. You must include countries that do not have a capital city.

You must write the declarations as a text file. Execute the text file in MySQL workbench. Hand in the textfile.

Assignment Data.8.1

Please write a query with country name, population, language, percentage, and percentage recalculated into number of people, for all languages in the countrylanguage table.

You must write the declarations as a text file. Execute the text file in MySQL workbench. Hand in the textfile.

Assignment Data.8.2

Using the SQL query (no PHP here) from the previous assignment, augment it to group the languages.

You must write the declarations as a text file. Execute the text file in MySQL workbench. Hand in the textfile.

Assignment Data.8.3

Please write an SQL query counting how many different languages are spoken in the world.

You must write the declarations as a text file. Execute the text file in MySQL workbench. Hand in the textfile.

Assignment Data.8.n Model Solution
-- Data 8.0
select 'Data 8.0';
select co.name 'Country', co.population 'Country Pop', continent,
       ci.name 'Capital', ci.population 'Population of Capital'
from country co
left join city ci
on co.capital = id
where continent in ('Oceania', 'Antarctica', 'South America')
order by continent, co.name;

-- Data 8.1
select 'Data 8.1', name, population, language, percentage, round(percentage * population / 100) 'How Many Speak It'
from countrylanguage
join country 
on code = countrycode
order by language;

-- Data 8.2
select 'Data 8.2', language, round(sum(percentage * population / 100)) 'How Many Speak It'
from countrylanguage
join country 
on code = countrycode
group by language
order by sum(percentage * population / 100) desc;

-- Data 8.3
select 'Data 8.3', count(distinct language) "Number of Languages in World"
from countrylanguage;

Solutions Data.9.n

Assignment Data.9.0

Please write an SQL declaration that lists

  • The largest city in the world database. I would like the name of the city, the name of it's country, and the population of the city.
  • The largest city from each continent in the world database. I would like the name of the city, the name of it's country, and the population of the city.

You must write the declarations as a text file. Execute the text file in MySQL workbench. Hand in the textfile.

Assignment Data.9.1

Please write an SQL declaration the lists the largest city per country in the world. State the name of the country, name of the city, population of the city, and calculate what percentage of the country's population live in that city.

You must write the declarations as a text file. Execute the text file in MySQL workbench. Hand in the textfile.

Assignment Data.9.2
  • Please write an SQL declaration the lists the languages spoken by more people than the number of people who speak Danish.
  • Please write an SQL declaration the counts the languages spoken by more people than the number of people who speak Danish.
  • Please write an SQL declaration ranking Danish in the order of most spoken languages in the world.

You must write the declarations as a text file. Execute the text file in MySQL workbench. Hand in the textfile.

Assignment Data.9.3

Please write an SQL declaration the lists all cities in the world that have a namesake in some other country.

You must write the declarations as a text file. Execute the text file in MySQL workbench. Hand in the textfile.

Assignment Data.9.n Model Solution
-- 9.0
-- Largest city in the world by population
select c.name, co.name, c.population
from city c
join country co on code = countrycode
where c.population = (select max(population)
                    from city);

-- Largest city per continent
select c.name, co.name, c.population, continent
from city c
join country co on code = countrycode
where c.population = (select max(ic.population)
                    from city ic
                    join country on code = countrycode
                    where continent = co.continent)
group by continent
order by c.population desc;

-- 9.1
-- biggest city pop as percentage of country pop
select ci.name, max(ci.population), co.name, max(ci.population) / co.population * 100 'PCT'
from city ci
join country co on code = countrycode
group by countrycode
order by PCT desc;

-- 9.2
-- Rank Danish as language by number of people speaking it
-- 1st step How many speak Danish
select sum(population*percentage/100) as speakingDanish
from country
join countrylanguage on code = countrycode
where language = 'Danish';

-- 2nd step What languages have more speakers than speak Danish
select language, sum(population*percentage/100) as speakers, 
          (select sum(population*percentage/100)
           from country
           join countrylanguage on code = countrycode
           where language = 'Danish') as speakingDanish
from country
join countrylanguage on code = countrycode 
group by language
having speakers > speakingDanish;

-- 3rd step Count them and add 1 giving the rank
select 'Danish', count(*) + 1 rank from 
   (select language, 
          sum(population*percentage/100) as speakers, 
          (select sum(population*percentage/100)
           from country
           join countrylanguage on code = countrycode
           where language = 'Danish') as speakingDanish
    from country
    join countrylanguage on code = countrycode 
    group by language
    having speakers > speakingDanish
    order by speakers) dt;

-- 9.3
-- cities with namesakes in other countries
-- one way
select c.name, c.countrycode
from city c
where c.name in (select o.name 
               from city o
               where c.name = o.name
               and c.countrycode <> o.countrycode)
order by c.name;

-- another way
select c.name, c.countrycode
from city c
where exists (select o.name 
               from city o
               where c.name = o.name
               and c.countrycode <> o.countrycode)
order by c.name;

Solutions SQL Views (SQL Data)

Assignment View.1.0

Create a database employee. Copy the following sql code into a file employee.sql, and import it into your database from employee.sql.

drop table if exists dependent;
drop table if exists works_on;
drop table if exists employee;
drop table if exists project;
drop table if exists dept_locations;
drop table if exists department;

create table department (
	dname varchar(16) not null,
	dnumber int not null,
	mgr_ssn int not null,
	mgr_start_date date not null,
	primary key(dnumber)
)engine=innodb;

create table dept_locations (
	dnumber int not null,
	dlocation varchar(16) not null,
	primary key(dnumber, dlocation),
	foreign key(dnumber) references department(dnumber)
)engine=innodb;

create table employee (
	fname varchar(16) not null,
	minit varchar(1) not null,
	lname varchar(32) not null,
	ssn int not null,
	bdate date not null,
	address varchar(32),
	sex char(1) not null check(value in ('M', 'F')),
	salary int not null,
	super_ssn int,
	dno int not null,
	primary key(ssn),
	foreign key(dno) references department(dnumber),
	foreign key(super_ssn) references employee(ssn)
)engine=innodb;

create table dependent (
	essn int not null,
	dependent_name varchar(16) not null,
	sex char(1) not null check(value in ('M', 'F')),
	bdate date not null,
	relationship varchar(12) not null,
	primary key(essn, dependent_name),
	foreign key(essn) references employee(ssn)
)engine=innodb;

create table project (
	pname varchar(16) not null,
	pnumber int not null,
	plocation varchar(16) not null,
	dnum int not null,
	primary key(pnumber),
	foreign key(dnum, plocation) references dept_locations(dnumber, dlocation)
)engine=innodb;
	
create table works_on (
	essn int not null,
	pno int not null,
	hours float(6,2),
	primary key(essn, pno),
	foreign key(essn) references employee(ssn),
	foreign key(pno) references project(pnumber)
)engine=innodb;

insert into department values('Research', 5, 333445555, '1988-05-22');
insert into department values('Administration', 4, 987654321, '1995-01-01');
insert into department values('Headquarters', 1, 888665555, '1981-06-19');
insert into dept_locations values(1, 'Houston');
insert into dept_locations values(4, 'Stafford');
insert into dept_locations values(5, 'Bellaire');
insert into dept_locations values(5, 'Sugarland');
insert into dept_locations values(5, 'Houston');
insert into project values('ProductX', 1, 'Bellaire', 5);
insert into project values('ProductY', 2, 'Sugarland', 5);
insert into project values('ProductZ', 3, 'Houston', 5);
insert into project values('Computerization', 10, 'Stafford', 4);
insert into project values('Reorganization', 20, 'Houston', 1);
insert into project values('Newbenefits', 30, 'Stafford', 4);

insert into employee values('James', 'E', 'Borg', 888665555, '1937-11-10', '450 Stone, Houston, TX', 'M', 55000, NULL, 1);
insert into employee values('Franklin', 'T', 'Wong', 333445555, '1955-12-08', '638 Voss, Houston, TX', 'M', 40000, 888665555, 5);
insert into employee values('John', 'B', 'Smith', 123456789, '1965-01-09', '731 Fondren, Houston, TX', 'M', 30000, 333445555, 5);
insert into employee values('Jennifer', 'S', 'Wallace', 987654321, '1941-06-20', '291 Berry, Bellaire, TX', 'F', 43000, 888665555, 4);
insert into employee values('Ahmad', 'V', 'Jabbar', 987987987, '1969-03-29', '980 Dallas, Houston, TX', 'M', 25000, 987654321, 4);
insert into employee values('Alicia', 'J', 'Zelaya', 999887777, '1968-01-19', '3321 Castle, Spring, TX', 'F', 25000, 987654321, 4);
insert into employee values('Ramesh', 'K', 'Narayan', 666884444, '1962-09-15', '975 Fire Oak, Humble, TX', 'M', 38000, 333445555, 5);
insert into employee values('Joyce', 'A', 'English', 453453453, '1972-07-31', '5631 Rice Houston, TX', 'F', 25000, 333445555, 5);

insert into dependent values(333445555, 'Alice', 'F', '1986-04-05', 'Daughter');
insert into dependent values(333445555, 'Theodore', 'M', '1983-10-25', 'Son');
insert into dependent values(333445555, 'Joy', 'F', '1958-05-03', 'Spouse');
insert into dependent values(987654321, 'Abner', 'M', '1942-02-28', 'Spouse');
insert into dependent values(123456789, 'Michael', 'M', '1988-01-04', 'Son');
insert into dependent values(123456789, 'Alice', 'F', '1988-12-30', 'Daughter');
insert into dependent values(123456789, 'Elizabeth', 'F', '1967-05-05', 'Spouse');

insert into works_on values(123456789, 1, 32.5);
insert into works_on values(123456789, 2, 7.5);
insert into works_on values(666884444, 3, 40.0);
insert into works_on values(453453453, 1, 20.0);
insert into works_on values(453453453, 2, 20.0);
insert into works_on values(333445555, 2, 10.0);
insert into works_on values(333445555, 3, 10.0);
insert into works_on values(333445555, 10, 10.0);
insert into works_on values(333445555, 20, 10.0);
insert into works_on values(999887777, 30, 30.0);
insert into works_on values(999887777, 10, 10.0);
insert into works_on values(987987987, 10, 35.0);
insert into works_on values(987987987, 30, 5.0);
insert into works_on values(987654321, 30, 20.0);
insert into works_on values(987654321, 20, 15.0);
insert into works_on values(888665555, 20, NULL);

Create a view that has the project name, controlling department name, number of employees, and total hours worked per week on the project for each project.

Discuss briefly the test(s) that convinced you the view works as desired.

You must write the declarations as a text file. Execute the text file in MySQL workbench. Hand in the textfile.

Assignment View.1.0
drop view assf00;

create view assf00(pname, dname, count, hours)
as 
select pname, dname, count(essn), sum(hours) 
from project
join department
on project.dnum = department.dnumber
join works_on
on project.pnumber = works_on.pno 
group by pname, dname;
Assignment View.1.1

Create a view that has the project name, controlling department name, number of employees, and total hours worked per week on the project for each project with more than one employee working on it.

Discuss briefly the test(s) that convinced you the view works as desired.

You must write the declarations as a text file. Execute the text file in MySQL workbench. Hand in the textfile.

Assignment View.1.1
drop view assf01;

create view assf01(pname, dname, count, hours)
as 
select pname, dname, count(essn), sum(hours) 
from project
join department
on project.dnum = department.dnumber
join works_on
on project.pnumber = works_on.pno 
group by pname, dname
having count(essn) > 1;

Solutions SQL Stored Procedures

Assignment SP.1.0

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 time of the 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.

Assignment SP.1.0
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
);

delimiter //

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

Look at the following scenario:

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 the following:

  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.

Hand in a testfile assSP11.sql with the generating SQL code, and a testrun.

Assignment SP.1.1
delimiter //
drop procedure if exists ass111//
    
create procedure ass111(t3count int, t4count int) 
begin
    declare j int;

    drop table if exists t4;
    drop table if exists t3;
    create table t3 (
        dist int unsigned not null, 
        repno int unsigned not null, 
        repname varchar(32) not null, 
        primary key(dist)
    );

    create table t4 (
        cust int unsigned not null primary key auto_increment,
        custname varchar(32) not null,
        district int unsigned not null,
        revenue float(10,2) not null,
        foreign key (district) references t3 (dist)
    );

    start transaction;
    while t3count > 0 do
        insert into t3 
            values (t3count, t3count, concat('Snake Oil Sales Rep ', t3count));
        set j = 0;
        while j < t4count do
            insert into t4 (custname, district, revenue) 
                values (concat('Sucker ', t3count, ' ', j),
                        t3count, 
                        round(rand() * 50000, 2)
                       );
            set j = j + 1;
        end while;
        set t3count = t3count - 1;
    end while;
    commit;
end;//
delimiter ;

MariaDB [test]> call ass111(5, 10000);
Query OK, 0 rows affected (2.05 sec)

MariaDB [test]> select district, count(*) from t4 group by district;
+----------+----------+
| district | count(*) |
+----------+----------+
|        1 |    10000 |
|        2 |    10000 |
|        3 |    10000 |
|        4 |    10000 |
|        5 |    10000 |
+----------+----------+
5 rows in set (0.03 sec)

MariaDB [test]> select * from t4 where revenue between 30100 and 30130;
+-------+---------------+----------+----------+
| cust  | custname      | district | revenue  |
+-------+---------------+----------+----------+
|   200 | Sucker 5 199  |        5 | 30121.40 |
|   358 | Sucker 5 357  |        5 | 30128.69 |
|   923 | Sucker 5 922  |        5 | 30126.56 |
|  1586 | Sucker 5 1585 |        5 | 30129.00 |
|  1790 | Sucker 5 1789 |        5 | 30128.02 |
|  2614 | Sucker 5 2613 |        5 | 30113.79 |
|  3271 | Sucker 5 3270 |        5 | 30116.69 |
|  4875 | Sucker 5 4874 |        5 | 30101.02 |
|  5521 | Sucker 5 5520 |        5 | 30119.66 |
|  5547 | Sucker 5 5546 |        5 | 30125.42 |
|  6654 | Sucker 5 6653 |        5 | 30126.77 |
|  7584 | Sucker 5 7583 |        5 | 30109.91 |
|  7669 | Sucker 5 7668 |        5 | 30100.18 |
|  9350 | Sucker 5 9349 |        5 | 30116.28 |
| 10415 | Sucker 4 414  |        4 | 30124.46 |
| 11330 | Sucker 4 1329 |        4 | 30129.93 |
| 11512 | Sucker 4 1511 |        4 | 30120.06 |
| 14371 | Sucker 4 4370 |        4 | 30105.56 |
| 17929 | Sucker 4 7928 |        4 | 30101.94 |
| 23544 | Sucker 3 3543 |        3 | 30122.34 |
| 23626 | Sucker 3 3625 |        3 | 30121.20 |
| 28400 | Sucker 3 8399 |        3 | 30103.99 |
| 30488 | Sucker 2 487  |        2 | 30106.19 |
| 30791 | Sucker 2 790  |        2 | 30104.82 |
| 31168 | Sucker 2 1167 |        2 | 30123.35 |
| 34648 | Sucker 2 4647 |        2 | 30124.39 |
| 36717 | Sucker 2 6716 |        2 | 30111.34 |
| 41350 | Sucker 1 1349 |        1 | 30104.55 |
| 41538 | Sucker 1 1537 |        1 | 30120.16 |
| 44768 | Sucker 1 4767 |        1 | 30106.66 |
| 46413 | Sucker 1 6412 |        1 | 30124.62 |
| 48876 | Sucker 1 8875 |        1 | 30103.16 |
+-------+---------------+----------+----------+
32 rows in set (0.05 sec)

Solutions to Assignments, SQL Triggers

Assignment Trig.1.0

Create a test database or add the following to an already existing database.

create table student (
    firstname varchar(40),
    surname varchar(40),
    email varchar(40) NOT NULL,
    id integer NOT NULL,
    primary key(id)
);
create table course (
        course varchar(12) not null,
        ects int not null,
        primary key(course)
);
create table achieves (
    course varchar(12) not null,
    grade int check (grade in (-3, 0, 2, 4, 7, 10, 12)),
    revised datetime not null,
    initials varchar(8) not null,
    id integer not null,
    primary key(course, id),
    foreign key(id) references student(id),
    foreign key(course) references course(course)
);
insert into student values('Abel','Anderson','a@a.dk',1);
insert into student values('Bent','Bertramsen','b@a.dk',2);
insert into student values('Chuck','Czechia','c@a.dk',3);
insert into student values('Dan','Dontspell','d@a.dk',4);
insert into student values('Erwin','Either','e@a.dk',5);
insert into student values('Fran','Fantasia','f@a.dk',6);
insert into student values('Gloria','Glamour','g@a.dk',7);
insert into student values('Henny','Hello','h@a.dk',8);
insert into student values('Ignaz','Introvert','i@a.dk',9);
insert into student values('Jane','Jordan','j@a.dk',10);
insert into student values('Kelly','Krimer','k@a.dk',11);

insert into course values('dbxml', 10);
insert into course values('idefix', 5);
insert into course values('swkon', 10);

insert into achieves values('dbxml', 10, current_timestamp, 'nml', 1);
insert into achieves values('dbxml', 4, current_timestamp, 'nml', 8);
insert into achieves values('dbxml', 12, current_timestamp, 'nml', 9);
Assignment Trig.1.1

Consider the situation that the grades of students are changed due to some registration error. Create a log-table in this database with the primary key lid, serial auto incremented will do. Then course, id as a composite foreign key pointing to the key of the achieves table, a comment, and the old revised date, initials-pair as well as the new revised date, initials-pair as the regular plain attributes.

Then write a trigger that will write a row into the log whenever a student grade is changed.

Test the trigger and document the process with select sentences before and after. Collect the total sql in a text file and hand it in.

Assignment Trig.1.1
Figure B.29. An Overview

Example B.96. assE1M.sql
drop table if exists gradelog;
create table gradelog (
	sno int not null auto_increment,
	id int not null,
	course varchar(12) not null,
	comment varchar(64) not null,
	beforedate datetime not null,
	afterdate datetime not null,
	beforeinit varchar(8) not null,
	afterinit varchar(8) not null,
    beforegrade int not null,
    aftergrade int not null,
	primary key (sno),
	foreign key (course,id) references achieves (course,id)
);

drop trigger if exists achieves_au;
delimiter //
create trigger achieves_au
	after update on achieves
	for each row
	begin
		insert into gradelog values(null, new.id, new.course, 'normal',
			old.revised, new.revised, old.initials, new.initials, old.grade, new.grade);
	end;//
delimiter ;

drop trigger if exists achieves_ai;
delimiter //
create trigger achieves_ai
	after insert on achieves
	for each row
	begin
		insert into gradelog values(null, new.id, new.course, 'normal',
			new.revised, new.revised, new.initials, new.initials, new.grade, new.grade);
	end;//
delimiter ;

delete from achieves where course='dbxml' and id='2';
delete from achieves where course='dbxml' and id='4';
delete from achieves where course='dbxml' and id='10';
delete from achieves where course='dbxml' and id='11';

insert into achieves values ('dbxml', 12, current_timestamp, 'nml', 2);
insert into achieves values ('dbxml', 11, current_timestamp, 'nml', 11);
insert into achieves values ('dbxml', -3, current_timestamp, 'nml', 10);
insert into achieves values ('dbxml', 0, current_timestamp, 'nml', 4);

select * from gradelog;

Assignment Trig.1.2

Knowing that MySQL does not observe and enforce check clauses even though they are read and stored, you must write a separate trigger that checks the grades entered by insert or update. This trigger must enter "ERROR!" into the comment attribute of the log entry create by the trigger from the previous assignment.

Test the trigger and document the process with select sentences before and after. Collect the total sql in a text file and hand it in.

Assignment Trig.1.2
Example B.97. assE2M.sql
drop function if exists checkGrades;
delimiter //
create function checkGrades(grade int)
returns boolean
deterministic
    begin
        declare tf boolean;
        set tf = false;
        if grade = -3 then set tf = true;
            elseif grade = 0 then set tf = true;
            elseif grade = 2 then set tf = true;
            elseif grade = 4 then set tf = true;
            elseif grade = 7 then set tf = true;
            elseif grade = 10 then set tf = true;
            elseif grade = 12 then set tf = true;
        end if;
        return tf;
    end;//
delimiter ;

drop trigger if exists achieves_bu;
delimiter //
create trigger achieves_bu
    before update on achieves
    for each row
    begin
        set @comment = 'Normal';
        if not checkGrades(new.grade) then
            set @comment = 'ERROR!';
        end if;
    end;//
delimiter ;

drop trigger if exists achieves_bi;
delimiter //
create trigger achieves_bi
    before insert on achieves
    for each row
    begin
        set @comment = 'Normal';
        if not checkGrades(new.grade) then
            set @comment = 'ERROR!';
        end if;
    end;//
delimiter ;

drop trigger if exists achieves_au;
delimiter //
create trigger achieves_au
    after update on achieves
    for each row
    begin
        insert into gradelog values(null, new.id, new.course, @comment,
            old.revised, new.revised, old.initials, new.initials, old.grade, new.grade);
    end;//
delimiter ;

drop trigger if exists achieves_ai;
delimiter //
create trigger achieves_ai
    after insert on achieves
    for each row
    begin
        insert into gradelog values(null, new.id, new.course, @comment,
            new.revised, new.revised, new.initials, new.initials, new.grade, new.grade);
    end;//
delimiter ;

update achieves set grade = 0, revised=current_timestamp where course='dbxml' and id='2';
update achieves set grade = 4, revised=current_timestamp where course='dbxml' and id='4';
update achieves set grade = 6, revised=current_timestamp where course='dbxml' and id='10';
update achieves set grade = -3, revised=current_timestamp where course='dbxml' and id='11';

select * from gradelog;

Due to my lack of imagination, or perhaps due to lack of trying, I did NOT see the following, which from a programmer's point of view is much more elegant. So thanks to a few of my former students.

drop function if exists checkGrades;
delimiter //
create function checkGrades(grade int)
returns boolean
deterministic
        begin
                declare tf boolean;
                set tf = false;
                if grade in(-3,0,2,4,7,10,12) then set tf = true;
                end if;
                return tf;
        end;//
delimiter ;
            

Solutions

Assignment SP.2

Consider whether referential integrity could be implemented through stored procedures. If the answer is yes write the procedure and test it. Create a testcase as proof of concept.

Example B.98. 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)

  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. Time the inserts
  5. Create a myIsam table t13 it will contain sales districts. Use t3 as model
  6. Create a myIsam table t14 for customers belonging to the mentioned sales districts. Use t4 as model
  7. Insert 5 districts into t13, and belonging to each of those 5 districs 10000 customers must be inserted into t14
  8. Time the inserts
  9. Create a stored procedure refIntChk that will check for any insert into t14, whether the district exists in table t13.
  10. 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.
  11. Time the inserts
  12. Document the total proces. Comment on it intelligently, and hand the documentation and code in as a zip according to normal rules.
  13. Deadline: one week from the date of this lesson.
Example B.99. Experiment With IN and OUT Parameters
delimiter //
drop procedure if exists refintstop//
create procedure refintstop(in tbl1Num int, in tbl2Num int, out tf int)
reads sql data
comment 'Experiment related to Assignment SP.2.0'
begin
	select tf;
	set tf = 0;
	select tf;
end;//
delimiter ;
MariaDB [db5]> set @x = 42;
Query OK, 0 rows affected (0.00 sec)

MariaDB [db5]> call refintstop(1,2,@x);
+------+
| tf   |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

MariaDB [db5]> select @x;
+------+
| @x   |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

Example B.100. The Referential Integrity Check Procedure - refIntChk.sql
delimiter //
drop procedure if exists refIntChk//
create procedure refIntChk(in keyval int, out tf int)
reads sql data
comment 'Check existense of certain key in table t13'
begin
	select count(*) into tf from t13 where dist = keyval;
end;//
delimiter ;

Example B.101. The Test Setup - assign122.sql
drop table if exists t4;
drop table if exists t3;

create table t3(
	dist int not null primary key,
	repno int not null,
	repname varchar(32) not null
)engine=innodb;

create table t4(
	cust int not null primary key,
	custname varchar(32) not null,
	district int not null,
	revenue float(10,2) not null,
	foreign key(district) references t3(dist)
)engine=innodb;

drop table if exists t13;
create table t13(
	dist int not null primary key,
	repno int not null,
	repname varchar(32) not null
)engine=myisam;

drop table if exists t14;
create table t14(
	cust int not null primary key,
	custname varchar(32) not null,
	district int not null,
	revenue float(10,2) not null
)engine=myisam;

delimiter //

drop procedure if exists ass122p0//
create procedure ass122p0(IN tbl1 CHAR(64),
                          in tbl1Num int,
                          in tbl2 CHAR(64),
                          in tbl2Num int)
reads sql data
comment 'Partial solution to Assignment SP.2.0 with variable table names'
begin
	declare s0 varchar(32);
	declare s1 varchar(32);
	declare i int;
	declare j int;
	declare k int;
	set s0 = 'Snake Oil Sales Rep';
	set s1 = 'Sucker ';
	set i = 0;
	set k = 0;

	set @s = concat('delete from ', tbl2, ';');
	prepare stmt from @s;
	execute stmt;

	set @s = concat('delete from ', tbl1, ';');
	prepare stmt from @s;
	execute stmt;

	select current_time into @timestart;

	while i < tbl1Num do
		set @s = concat('insert into ', tbl1, '(dist, repno, repname)
                         values(', i, ', ', i + 1000, ', ''', s0, ''')');
		prepare stmt from @s;
		execute stmt;
		set j = 0;
		while j < tbl2Num do
		    set @c = concat(s1, k);
			set @s = concat('insert into ', tbl2, '(cust, custname, district, revenue)
                             values(', k, ', ''', @c, ''', ', i, ', ', rand() * 1000000, ')');
			prepare stmt from @s;
			execute stmt;
			set j = j + 1;
			set k = k + 1;
		end while;
		set i = i + 1;
	end while;

	select current_time into @timestop;
	select timediff(@timestop, @timestart) into @elapsed;
	set @s = concat('select ''', tbl1, ''' as Tabel_1, ''', tbl2, ''' as Tabel_2,
                    ''', @elapsed, ''' as TID;');
	prepare stmt from @s;
	execute stmt;

end;
//

drop procedure if exists ass122p1//
create procedure ass122p1(in tbl1Num int, in tbl2Num int)
reads sql data
comment 'Partial solution to Assignment SP.2.0 with fixed table names'
begin
	declare s0 varchar(32);
	declare s1 varchar(32);
	declare i int;
	declare j int;
	declare k int;
	declare boole boolean;
	set boole = false;
	set s0 = 'Snake Oil Sales Rep';
	set s1 = 'Sucker ';
	set i = 0;
	set k = 0;

	set @s = 'delete from t14';
	prepare stmt from @s;
	execute stmt;

	set @s = 'delete from t13';
	prepare stmt from @s;
	execute stmt;

	select current_time into @timestart;

	while i < tbl1Num do
        set @s = concat('insert into t13 (dist, repno, repname)
                         values(', i, ', ', i + 1000, ', ''', s0, ''')');
                         prepare stmt from @s;
		execute stmt;
		set j = 0;
		while j < tbl2Num do
	        set @c = concat(s1, k);
			call refIntChk(i, boole);
			if boole is true then
				set @s = concat('insert into t14
                                 values(', k, ', ''', @c, ''', ', i, ', ', rand() * 1000000, ')');
			else
				set @s = concat('drop table `insert fail for district ` ', i);
			end if;
			prepare stmt from @s;
			execute stmt;
			set j = j + 1;
			set k = k + 1;
		end while;
		set i = i + 1;
	end while;

	select current_time into @timestop;
	select timediff(@timestop, @timestart) into @elapsed;
	set @s = concat('select ''t13'' as Tabel_1,
                    ''t14'' as Tabel_2, ''',
                    @elapsed, ''' as TID;');
	prepare stmt from @s;
	execute stmt;

end;
//
delimiter ;

select 'autocommit off';
set autocommit=0;
call ass122p0('t3',5,'t4',10000);
commit;
call ass122p0('t13',5,'t14',10000);
commit;
call ass122p1(5,10000);
commit;

select 'autocommit on';
set autocommit=1;
call ass122p0('t3',5,'t4',10000);
call ass122p0('t13',5,'t14',10000);
call ass122p1(5,10000);

Example B.102. The Results - Mac 2,3 GHz
MariaDB [db5]> \. ass122.sql
Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.06 sec)

Query OK, 0 rows affected (0.06 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+----------------+
| autocommit off |
+----------------+
| autocommit off |
+----------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+---------+---------+----------+
| Tabel_1 | Tabel_2 | TID      |
+---------+---------+----------+
| t3      | t4      | 00:00:06 |
+---------+---------+----------+
1 row in set (5.99 sec)

Query OK, 0 rows affected (5.99 sec)

Query OK, 0 rows affected (0.03 sec)

+---------+---------+----------+
| Tabel_1 | Tabel_2 | TID      |
+---------+---------+----------+
| t13     | t14     | 00:00:06 |
+---------+---------+----------+
1 row in set (5.80 sec)

Query OK, 0 rows affected (5.80 sec)

Query OK, 0 rows affected (0.00 sec)

+---------+---------+----------+
| Tabel_1 | Tabel_2 | TID      |
+---------+---------+----------+
| t13     | t14     | 00:00:09 |
+---------+---------+----------+
1 row in set (8.84 sec)

Query OK, 0 rows affected (8.84 sec)

Query OK, 0 rows affected (0.00 sec)

+---------------+
| autocommit on |
+---------------+
| autocommit on |
+---------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+---------+---------+----------+
| Tabel_1 | Tabel_2 | TID      |
+---------+---------+----------+
| t3      | t4      | 00:05:58 |
+---------+---------+----------+
1 row in set (5 min 58.41 sec)

Query OK, 0 rows affected (5 min 58.41 sec)

+---------+---------+----------+
| Tabel_1 | Tabel_2 | TID      |
+---------+---------+----------+
| t13     | t14     | 00:00:06 |
+---------+---------+----------+
1 row in set (6.14 sec)

Query OK, 0 rows affected (6.14 sec)

+---------+---------+----------+
| Tabel_1 | Tabel_2 | TID      |
+---------+---------+----------+
| t13     | t14     | 00:00:10 |
+---------+---------+----------+
1 row in set (9.53 sec)

Query OK, 0 rows affected (9.53 sec)

MariaDB [db5]> 

Example B.103. Different Results - Mac 2,3 GHz
MariaDB [ass122]> select 'autocommit off';
+----------------+
| autocommit off |
+----------------+
| autocommit off |
+----------------+
1 row in set (0.00 sec)

MariaDB [ass122]> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

MariaDB [ass122]> call ass122p0('t3',5,'t4',10000);
+---------+---------+----------+
| Tabel_1 | Tabel_2 | TID      |
+---------+---------+----------+
| t3      | t4      | 00:00:05 |
+---------+---------+----------+
1 row in set (5.57 sec)

Query OK, 0 rows affected (5.57 sec)

MariaDB [ass122]> commit;
Query OK, 0 rows affected (0.07 sec)

MariaDB [ass122]> call ass122p0('t13',5,'t14',10000);
+---------+---------+----------+
| Tabel_1 | Tabel_2 | TID      |
+---------+---------+----------+
| t13     | t14     | 00:00:05 |
+---------+---------+----------+
1 row in set (5.51 sec)

Query OK, 0 rows affected (5.51 sec)

MariaDB [ass122]> commit;
Query OK, 0 rows affected (0.00 sec)

MariaDB [ass122]> call ass122p1(5,10000);
+---------+---------+----------+
| Tabel_1 | Tabel_2 | TID      |
+---------+---------+----------+
| t13     | t14     | 00:00:08 |
+---------+---------+----------+
1 row in set (8.08 sec)

Query OK, 0 rows affected (8.08 sec)

MariaDB [ass122]> commit;
Query OK, 0 rows affected (0.00 sec)



MariaDB [ass122]> select 'autocommit on';
+---------------+
| autocommit on |
+---------------+
| autocommit on |
+---------------+
1 row in set (0.00 sec)

MariaDB [ass122]> set autocommit=1;
Query OK, 0 rows affected (0.00 sec)

MariaDB [ass122]> call ass122p0('t3',5,'t4',10000);
+---------+---------+----------+
| Tabel_1 | Tabel_2 | TID      |
+---------+---------+----------+
| t3      | t4      | 00:37:35 |
+---------+---------+----------+
1 row in set (37 min 35.83 sec)

Query OK, 0 rows affected (37 min 35.83 sec)

MariaDB [ass122]> call ass122p0('t13',5,'t14',10000);
+---------+---------+----------+
| Tabel_1 | Tabel_2 | TID      |
+---------+---------+----------+
| t13     | t14     | 00:00:06 |
+---------+---------+----------+
1 row in set (5.55 sec)

Query OK, 0 rows affected (5.55 sec)

MariaDB [ass122]> call ass122p1(5,10000);
+---------+---------+----------+
| Tabel_1 | Tabel_2 | TID      |
+---------+---------+----------+
| t13     | t14     | 00:00:08 |
+---------+---------+----------+
1 row in set (8.13 sec)

Solutions PSM Functions

Assignment Func.1.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.

Assignment Func.1.1

Create a function fibonacci(n int) that calculates the n-th Fibonacci number. Demonstrate it's usage with some sql statements. Make a comment in the function telling us whether your series starts with 0 or 1.

Assignment Func.1.2

The variable prime holds an amount of money loaned or placed into a savings account in a bank. The variable n holds a number of terms, an integer of course. The variable r is an interest rate. An interest rate means a percentage / 100, eg 5% becomes 5 / 100 yielding 0.05. Create a function accumulator(prime decimal, n int, r decimal) that calculates accumulated value of the prime after n terms given the prime, and the interest rate r. Demonstrate it's usage with some sql statements.

Assignment Func.1.3

Create a function distance(x0 int, y0 int, x1 int, y1 int) that calculates the distance in the plane between two objects identfied by their coordinates (x0, y0) and (x1, y1). Demonstrate it's usage with some sql statements.

Assignment Func.1.4

Create a function distancegc(lat0 float, lon0 float, lat1 float, lon1 float) that calculates the distance along a great circle between two geographical objects given their longitude/latitude coordinates (lon0, lat0) and (lon1, lat1) . Demonstrate it's usage with some sql statements.

For hints take a peek at: http://en.wikipedia.org/wiki/Great-circle_distance.

Solutions
Assignment Func.1.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.

Example B.104. Function isPrime(n int)
delimiter //

drop function if exists isPrime0//
create function isPrime0 (n bigint)
    returns boolean
    deterministic
    begin
        declare divisor bigint default 2;
        declare lim double(12,0);
        
        set lim = sqrt(n);
        while divisor <= lim do
            if n % divisor = 0
                then return false;
            end if;
            set divisor = divisor + 1;
        end while;
        
        return true;
    end; //



drop function if exists isPrime1//
create function isPrime1 (n bigint)
    returns boolean
    deterministic
    begin
        declare divisor bigint default 3;
        declare lim double(12,0);

        set lim = sqrt(n);
        if n = 2 then
            return true;
        end if;
        if n % 2 = 0 then
            return false;
        end if;
        while divisor <= lim do
            if n % divisor = 0
                then return false;
            end if;
            set divisor = divisor + 2;
        end while;
        
        return true;
    end; //



drop function if exists isPrime//
create function isPrime (n bigint)
    returns int
    deterministic
    begin
        declare i bigint default 1;
        declare lim double(12,0);
        declare divm1 bigint;
        declare divp1 bigint;

        if n = 2 or n = 3 then 
            return true;
        end if;
        if n % 2 = 0 or n % 3 = 0 then 
            return false; 
        end if;
        
        set lim = sqrt(n);
        set divm1 = 6 * i - 1;
        set divp1 = 6 * i + 1;
        
        while divm1 <= lim or divp1 <= lim do
            if n % divm1 = 0 or n % divp1 = 0
                then return false;
            end if;
            set i = i + 1;
            set divm1 = 6 * i - 1;
            set divp1 = 6 * i + 1;
        end while;
        
        return true;
    end; //

delimiter ;
select isPrime0(2);
select isPrime1(2);
select isPrime(2);
select isPrime0(3);
select isPrime1(3);
select isPrime(3);
select if(isPrime0(257), 'true', 'false');
select if(isPrime1(257), 'true', 'false');
select if(isPrime(257), 'true', 'false');

-- highest int
select if(isPrime0(2147483647), 'true', 'false');
select if(isPrime1(2147483647), 'true', 'false');
select if(isPrime(2147483647), 'true', 'false');

-- highest bigint
select if(isPrime0(9223372036854775807), 'true', 'false');
select if(isPrime1(9223372036854775807), 'true', 'false');
select if(isPrime(9223372036854775807), 'true', 'false');

-- 2**61-1 true
select if(isPrime(2305843009213693951), 'true', 'false');
-- (2**31-1)**2 false, 2**31-1 is prime
select if(isPrime(4611686014132420609), 'true', 'false');


            
MariaDB [db5]> -- 2**61-1
select if(isPrime(2305843009213693951), 'true', 'false');
+---------------------------------------------------+
| if(isPrime(2305843009213693951), 'true', 'false') |
+---------------------------------------------------+
| true                                              |
+---------------------------------------------------+
1 row in set (11 min 11.32 sec)

MariaDB [db5]> select if(isPrime(4611686014132420609), 'true', 'false');
+---------------------------------------------------+
| if(isPrime(4611686014132420609), 'true', 'false') |
+---------------------------------------------------+
| false                                             |
+---------------------------------------------------+
1 row in set (15 min 48.30 sec)