Database Development (BAIT4/INF4/IXD6/iDA8)

Exam Assignments
Hua Lu and Lu Chen
8th of June 2018

Full Name:

Study number:

E-mail
at student.aau.dk:

This exam consists of a number of exercises and there are three (3) hours to solve
them. When answering the questions write directly in the provided fields on this paper if
possible. Remember also to put your name and your study number on any additional
sheets of paper you will use.
• Read carefully the text of each exercise before solving it!
• Please make an effort to use a readable handwriting and to present your
solutions neatly. This will make it easier to understand your answers.
During the exam you are allowed to consult books and notes. The use of computer is
also permitted only for the electronic text book, slides, and notes! You are not allowed to
use any communication, i.e., WiFi MUST be switched off! You are not allowed to use
MySQL or any other software on your computer for helping solve the exercises.

*** After this cover page, there are totally thirteen (13) pages for the exam.
Null Values [8 points in total]
Dr. Strange in The Weird Lab conducts many tests for chemical samples in his daily work. Each
sample must go through three tests. The result of a test is positive (TRUE) or negative (FALSE),
but sometimes a test fails and the result is marked as NULL. All samples fall into one of two
types: synthetic or natural. The following table tests records all the samples and the tests Dr.
Strange did in May. Refer to this table to answer the questions on this page.
sample type weight test1 test2 test3
A synthetic 0.4 TRUE TRUE FALSE
B synthetic 0.5 NULL TRUE FALSE
C natural 0.4 FALSE FALSE TRUE
D natural NULL NULL NULL NULL
E synthetic 0.6 TRUE TRUE NULL
F natural 0.2 FALSE NULL FALSE

1. Write down the samples returned by each of the following SQL statements.
a. SELECT sample FROM tests WHERE test1 AND test2; [1 point]
A, E
b. SELECT sample FROM tests WHERE test2 OR test3; [1 point]
A, B, C, E
c. SELECT sample FROM tests WHERE ((test 1 OR test3) AND test2); [1 point]
A, E
2. If a sample fails all three tests, it is regarded as contaminated. Write an SQL statement to
return all samples that are regarded as contaminated. [2 points]
SELECT sample FROM tests WHERE test1 IS NULL AND test2 IS NULL AND test3 IS NULL

3. Write down the result, in a table format, of the following SQL statement. [3 points]
SELECT type, AVG(weight) AS avg_weight FROM tests GROUP BY type;

type avg_weight
synthetic 0.5
natural 0.3

1
Inner Join [7 points in total]
Refer to the following two tables, r and s respectively, and answer the subsequent questions.

r F G s G H
a b b a
b c a c
c d c d

1. What is the result of the SQL statement? (Clearly circle your choice.) [3 points]
SELECT * FROM r INNER JOIN s ON r.G = s.H;

F G H F G G H
a a c b c a c
c c d c d c d

A B

F G G H F G G H
a b b a a a b a
b c c d b c a c
b c a c c d c d

C D
2. Write down the result of the SQL statement in table format. [4 points]
SELECT F, G FROM r NATURAL JOIN s;

F G
a b
b c

2
Outer Join [7 points in total]
Refer to the following two tables, r and s respectively, and answer the subsequent questions.

r rid s sid value

1 1 10
3 2 20
7 3 30

1. What is the result of the SQL statement? (Clearly circle your choice.) [3 points]
SELECT * FROM r RIGHT OUTER JOIN s ON rid = sid;

A B C D
rid sid value rid sid value rid sid value rid sid value
1 1 10 1 1 10 1 1 10 1 1 10
3 3 30 3 3 30 NULL 2 20 NULL 2 20
7 NULL NULL 3 3 30 3 3 30
7 NULL NULL

2. What is the result of the SQL statement? (Clearly circle your choice.) [3 points]
SELECT * FROM r LEFT OUTER JOIN s ON rid = sid;

A B C D
rid sid value rid sid value rid sid value rid sid value
1 1 10 1 1 10 1 1 10 1 1 10
3 3 30 3 3 30 NULL 2 20 NULL 2 20
7 NULL NULL 3 3 30 3 3 30
7 NULL NULL

3
Set Operations [8 points in total]
Refer to the following three tables, u, v, and w respectively.

u v w
id value id value id value
1 100 1 200 1 100
2 200 3 400 2 600
3 400

1. What is the result of the SQL statement? (Clearly circle your choice.) [2 points]
SELECT value FROM u UNION SELECT value FROM v UNION SELECT value FROM w;
A B C D
value value value value
100 100 100 100
200 400 200 200
400 400 400
600 200
400
100
600

2. Write down the result of the SQL statement in the table format. [3 points]
SELECT * FROM u INTERSECT SELECT * FROM v;

id value
3 400

3. Write down the result of the SQL statement in the table format. [3 points]
SELECT * FROM u EXCEPT SELECT * FROM w;

id value
2 200
3 400

4
SQL Injection [5 points in total]
Tom has made a webpage with the following text box. On the server side, there is a database
table called users, and it contains a column called name. Tom wants to return from the table all
the records whose name is the same as the input from the text box.

Name

1. Suppse Tom’s application code for the webpage is as follows.

txtName = getRequestString("Name");
txtSQL = "SELECT * FROM users WHERE Name = ’" + txtName + "’";

Which of the following inputs may cause SQL injection? (There can be multiple correct
answers. Clearly circle the number of each of your choices.) [0.5 point per each]
A. Bob’ or ’a’ = ’a
B. Bob; drop table Users
C. Bob’; delete from table Users where ’name’=’name
D. Bob’; select * from Users where ’1’ = ’1

2. Suppose db is the connection to the server side database. To avoid possible SQL injections,
you are supposed to fill in the correct code in the underlined blanks below. [3 points]

txtName = getRequestString("Name");
txtSQL = "SELECT * FROM users WHERE Name = @0 ";
db.Execute( txtSQL, txtName );

5
View and Security [8 points in total]
Consider table employees that is created as follows for the relevant questions on this page.
CREATE TABLE employees (
cpr INT PRIMARY KEY,
name CHAR(30),
gender CHAR(1) NOT NULL,
start_year INT NOT NULL,
salary INT);
1. Two views are created as follows.
CREATE VIEW view1 AS SELECT cpr, gender, start_year FROM employees;
CREATE VIEW view2 AS SELECT cpr, name, start_year FROM employees;
CREATE VIEW view3 AS SELECT gender, start_year, salary FROM employees;

Is view1 an updatable or non-updatable view? [Clearly circle your answer inline. 1 point]

Is view2 an updatable or non-updatable view? [Clearly circle your answer inline. 1 point]

Is view3 an updatable or non-updatable view? [Clearly circle your answer inline. 1 point]

2. Let Tom and Jerry be database users and the following SQL statement is run successfully:
GRANT SELECT(name, salary) ON employees TO Tom WITH GRANT OPTION;
Which of the following SQL statements can Tom run successfully? (Clearly circle the
numbers of your choices.) [1 point per each]
1. SELECT * FROM employees WHERE salary > 2000;
2. GRANT SELECT ON employees TO Jerry WITH GRANT OPTION;
3. GRANT SELECT ON employees TO Jerry;
4. SELECT name FROM employees.
3. Suppose the following SQL statement has been run successfully:
GRANT ALL ON employees TO Tom WITH GRANT OPTION;
Write an SQL statement such that Tom can no longer delete records from the table. [2 points]

REVOKE DELETE ON students FROM Tom;

6
SQL Queries [7 points in total]
Consider the following two tables about student and departments in a university. In the students table,
std_id is the primary key. In the departments table, dpt_id is the primary key.
students departments
std_id name gender dpt_id dpt_id dept_name location
001 Kris Jensen F 05 01 Computer SLV300
002 Morten Nielsen M 03 02 Physics FVJ8A
003 Morten Nielsen M 01 03 Chemistry FVJ7B
004 Sarah Jul F 05 04 Mathematics FVJ7C
005 Sarah Jul F NULL 05 Electronics FVJ8D

1. How many records are there in the result of the following SQL statement? [2 points]
SELECT * FROM departments NATURAL JOIN students;
4

2. What is the result of the following SQL statement? [2 points]

SELECT std_id FROM students
WHERE name = (SELECT name FROM students WHERE std_id= ‘002’);

std_id
002
003

3. Convert the following SQL statement to one that does not use a subquery but returns the
same result. Your answer should be general enough such that even if the table contents
change the two SQL statements are still equivalent result-wise. [3 points]
SELECT d.dept_name FROM departments d
WHERE d.location LIKE ‘FVJ%’
AND EXISTS (SELECT * FROM students s WHERE d.dpt_id = s.dpt_id);

SELECT dept_name FROM departments NATURAL JOIN students

WHERE location LIKE ‘FVJ%’;

7
Create an ER Diagram [15 points in total]
At Free Land University (FLU), there are many student clubs each focusing on a particular
theme, e.g., football, handball, swimming, film, and so on. The university decided to create a
database to manage all relevant information. For each club, we need to know its name, theme,
established year and month. The members of each club are students at FLU. For each student, we
need to know her/his student number, name, gender, and department. A student at PLU may
choose not to join any clubs, or be a member of one or more clubs. For each membership, there
is a start date and an end date that indicate the valid membership period. A club cannot exist if it
has no members. Each club is managed by a chairperson who is naturally a member of that club.
A student may chair many clubs. For each club and its chairperson, the start and end dates of the
management should also be captured.

Create an ER diagram for the database of FLU clubs according to the requirements above. Make
it explicit which attributes form a primary key. List any other assumptions you make.

8
Map an ER Diagram to Tables [15 points in total]
There are many venture capitals (VCs) and startup companies seeking opportunities in Selma
Valley. Each VC may invest in different startups, and a startup can receive investments from
different VCs. Each investment is associated with a particular date and an amount. Each VC is
identified by an id, and registered in a particular country. A VC also has a name, and a start date
to indicate when it was established. Each startup has a unique id, a name, and a start date.
Specially, a startup is focused on a particular industry. Currently, Selma Valley only supports
startups in four industries: software, electronics, biology and healthcare, as these are regarded as
the most lucrative ones. No other types of startup are allowed.

Map the following ER-diagram to tables using SQL CREATE TABLE statements. Remember to
include all relevant constraints such as primary and foreign keys whenever they are needed.
Make sure you associate tables through foreign keys correctly.

[Use the next page if you need more space for writing the SQL statements.]

CREATE TABLE startup (

id CHAR(10) PRIMARY KEY,
name VARCHAR(30),
industry CHAR(12) CHECK (industry IN (‘software’, ‘electronics’, ‘biology’, ‘medicine’)),
start_date DATE);

CREATE TABLE VC (
id INT PRIMARY KEY,
name VARCHAR(30),
country VARCHAR(30),
start_date DATE);

CREATE TABLE invests (

vc_id CHAR(10) NOT NULL, sid INT NOT NULL,
date DATE, amount INT,
FOREIGN KEY (vc_id) REFERENCES VC(id),
FOREIGN KEY (sid) REFERENCES startup(id));

9
[Use this page if you need more space for writing the SQL statements.]

10
SQL Statements Part 1 [12 points in total]
Free Land University uses a database to manage the information about all PhD theses that have
passed oral defense and been published officially. The thesis table stores a unique ID, thesis title,
the publication date, and the ID of the PhD student who wrote the thesis. The student table stores
a unique ID, the first name, middle name, last name, birthday, and gender of each PhD student.
Note that each PhD student can only write one thesis and one thesis can have at most one PhD
student as the author. Each PhD student is supervised by one or more professors but only one of
them is the responsible supervisor and others are assistant supervisors. One professor can
supervise many PhD students. The professor table and the supervises table capture information
about professors and supervisions, respectively. The four tables are created using the SQL
statements given below.

CREATE TABLE thesis (

tid INT PRIMARY KEY,
title VARCHAR(100) NOT NULL,
publication_date DATE,
sid INT REFERENCES student(sid));

CREATE TABLE student (

sid INT PRIMARY KEY,
first_name VARCHAR(30) NOT NULL,
middle_name VARCHAR(30),
last_name VARCHAR(30) NOT NULL,
birthday DATE,
gender CHAR(1));
CREATE TABLE professor (
pid INT PRIMARY KEY,
first_name VARCHAR(30) NOT NULL,
middle_name VARCHAR(30),
last_name VARCHAR(30) NOT NULL,
birthday DATE,
gender CHAR(1));
CREATE TABLE supervises (
sid INT REFERENCES student(sid),
pid INT REFERENCES professor(pid)，
responsible BOOLEAN);

Write SQL queries that answer the following questions.

1. List the full names of all professors who have a middle name and are younger than 40. [2
points]

11
2. Get the number of theses that are published between 2013 and 2017 and whose titles contain
“Big Data”. [2 points]
3. List the complete information of all students who are supervised by James Bond as the
responsible supervisor.[4 points]
4. List the number of theses supervised by female and male responsible professors, respectively.
[4 points]

1. SELECT first_name, middle_name, last_name FROM professor WHERE

birthday>’19780608’ AND middle_name IS NOT NULL;

2. SELECT COUNT(title) FROM thesis WHERE publication_date BETWEEN ‘20130101’

AND ‘20171231’ AND title LIKE ‘%Big Data%’;

3. SELECT student.* FROM student NATRUAL JOIN supervises NATURAL JOIN

professor WHERE professor.first_name = ‘James’ AND professor.last_name=’Bond’
AND responsible=TRUE;

4. SELECT gender, COUNT(title) FROM thesis NATRUAL JOIN supervises NATURAL

JOIN professor WHERE responsible=TRUE GROUP BY gender;

12
SQL Statements Part 2 [8 points in total]
Selma High School surveyed the height and weight of their last year student. A part of the result is listed
in the following table named survey. The height column is in the unit of cm and the weight column is in
the unit of kg.

student age gender height weight

Lars Jensen 18 Male 175 70

Mads Olsen 19 Male 180 80

Sarah Thomsen 18 Female 165 50

Laura Poulsen 17 Female 165 52

Anne Nielsen 20 Female 168 55

Tim Larsen 18 Male 175 75

Pia Olesen 19 Female 170 60

Alex Wind 18 Male 178 78

1. Write an SQL statement to return the average height and average weight of each gender group. [3
points]

SELECT gender, AVG(height), AVG(weight) FROM survey GROUP BY gender;

2. Write an SQL statement to return the complete information of all students whose weight exceeds the
average by more than 10kg in their gender group. Make sure the records in the result table are sorted
descendingly on the weight column. [5 points]

SELECT * FROM survey NATURAL JOIN (SELECT gender, AVG(weight) AS avg_weight FROM
survey GROUP BY gender ) AS x WHERE (survey.weight - x.avg_weight > 10) ORDER BY
survey.weight DESC;

13

--- End of Exam Paper ---