Part A: Overview

1.1 List five responsibilities of a database management system. For each responsibility, explain the
problems that would arise if the responsibility were not discharged.

1.2 What are five main functions of a database administrator (internet investigation)?

Part B: Relational Language

2.1 Consider the insurance database of figure 2.1, where the primary keys are underlined. Construct the
following SQL queries for this relational database. Please validate your results.
a. Find the total number of people who owned cars that were involved in accidents in 2007.
b. Find the number of accidents in which the cars belonging to “BCD were involved.
c. Add a new accident to the database; assume any values for required attributes.
d. Delete the Porsche belonging to “BCD”.
e. Update the damage amount for the car with license number “AABB2000” in the accident with
report number “AR2197” to $3000.

Figure 2.1: Insurance database.

2.2 Consider the employee database of Figure 2.2, where the primary keys are underlined. Give an
expression in SQL for each of the following queries:

a. Find the names of all employees who work for First Bank Corporation.
b. Find the names and cities of residence of all employees who work for First Bank Corporation.
c. Find the names, street addresses, and cities of residence of all employees who work for First Bank
Corporation and earn more than $10,000.
d. Find all employees in the database who live in the same cities as the companies for which they
work.
e. Find all employees in the database who live in the same cities and on the same streets as do their
managers.
f. Find all employees in the database who do not work for First Bank Corporation.
g. Find all employees in the database who earn more than each employee of Small Bank
Corporation.
h. Assume that the companies may be located in several cities. Find all companies located in every
city in which Small Bank Corporation is located.
i. Find all employees who earn more than the average salary of all employees of their company.
j. Find the company that has the most employees.
k. Find the company that has the smallest payroll.
l. Find those companies whose employees earn a higher salary, on average, than the average salary
at First Bank Corporation.

Figure 2.2: Employee database.

 2.3 Consider the relational database of figure 2.2. Give an expression in SQL for each of the following
queries.
a. Modify the database so that Jones now lives in Newtown.
b. Give all employees of First Bank Corporation a 10 percent raise.
c. Give all managers of First Bank Corporation a 10 percent raise unless the salary becomes greater
than $100,000; in such cases, give only a 3 percent raise.

Figure 2.3: The student relation.

Figure 2.4: The takes relation.

2.4 Consider the following student and takes relations as presented in Figures 2.3 and 2.4 respectively:
Combine the two relations, firstly without using a join expression and then by using an aribitrary join
expression.

2.5 Suppose we wish to display a list of all students, displaying their ID, and name, dept_name, and
tot_cred, along with the courses that they have taken. Make use of an inner join, left outer join a right
outer join and a full outer join expression to display the students results. Compare the outcomes.
 2.6 Find all students who have not taken a course.

2.7 Display a list of all students in the Comp. Sci. department, along with the course sections, if any, that
they have taken in Spring 2009; all course sections fromSpring 2009 must be displayed, even if no student
from the Comp. Sci. department has taken the course section.

2.8 Consider once again the employee database as depicted in figure 5.2. Define a view consisting of
manager-name and the average salary of all employees who work for that manager. Explain the
advantages of views in general.

Part C: Design

3.1 Construct an E-R diagram for a car-insurance company whose customers own one or more cars each.
Each car has associated with it zero to any number of recorded accidents.

3.2 A university registrar’s office maintains data about the following entities:
a. courses, including number, title, credits, syllabus, and prerequisites;
b. courseofferings, including course number, year, semester, section number, instructor(s),
timings, and classroom;
c. students, including student-id, name, and program; and
d. instructors, including identification number, name, department, and title.

Further, the enrollment of students in courses and grades awarded to students in each course they are
enrolled for must be appropriately modeled. Construct an E-R diagram for the registrar’s office. Document
all assumptions that you make about the mapping constraints.

Production Publishing
CD_ID Album Tracks
Year Year
4711 Anastacia – Not That Kind 1999 2000 {1. Not That Kind, 2. I’m Outta Love,
3. Cowboys & Kisses}
4712 Pink Floyd – Wish You Were 1965 1975 {1. Shine On You Crazy Diamond}
Here
4713 Anastacia – Freak of Nature 1999 2001 {1. Paid my Dues}
Table 3.1: Music albums.

3.3: Consider the following table displaying different music albums (example taken from Wikipedia).
Why does table 3.1 not fulfill the condition of the first normal form (1NF).

3.4: Transform the table 3.1 so that it fulfills the 1NF condition. Why does it not meet the second normal
form (2NF) condition?

3.5: Transform the table 3.2 so that it fulfills the 2NF condition. Why does it not meet the third normal form
(3NF) condition?

3.6: Transform the tables 3.3 and 3.4 so that they fulfill the 3NF condition.

3.7. Please transform the following table into the 1st, 2nd and 3rd normal form. Explain your
decomposition.
 Table 3.2: The student relation.

Part D: Alternative Approaches

4.1: How does the concept of an object in the object-oriented model differ from the concept of an entity in
the entity-relationship model?

4.2: A car-rental company maintains a vehicle database for all vehicles in its current fleet. For all vehicles, it
includes the vehicle identification number, license number, manufacturer, model, date of purchase, and
color. Special data are included for certain types of vehicles:
- Trucks: cargo capacity
- Sports cars: horsepower, renter age requirement
- Vans: number of passengers
- Off-road vehicles: ground clearance, drivetrain (four- or two-wheel drive)
Construct an object-oriented database schema definition for this database. Use inheritance where
appropriate.
Construct an object-oriented database schema definition for this database. Use inheritance where
appropriate.

4.3: Give the DTD for an XML representation of the nested-relational schema presented in figure 10.1.

Figure 4.3: Children nested-relational schema.

4.4: For each of the following application areas, explain why a relational database system would be
inadequate. List all specific system components that would need to be modified:
a. Computer-aided design,
b. Multimedia databases.

4.5 Additional Group work: Search the internet for different cloud services distributes. Limit your search on
distributers that of Software-as-a-Service (SaS), Platform-as-a-Serce (PaS) and Infrastructure-as-a-Serce
(Ias).

Part E: Modern Approaches

 5.1 Suppose you need to store a very large number of small files, each of size say 2 kilobytes. If your choice
is between a distributed file system and a distributed key-value store, which would you prefer, and explain
why.

5.2 Suppose you need to store data for a very large number of students in a distributed document store
such as MongoDB. Suppose also that the data for each student correspond to the data in the student and
the takes relations. How would you represent the above data about students, ensuring that all the data for
a particular student can be accessed efficiently? Give an example of the data representation for one
student.

5.3 Suppose you wish to store utility bills for a large number of users, where each bill is identified by a
customer ID and a date. How would you store the bills in a key-value store that supports range queries, if
queries request the bills of a specified customer for a specified date range.

5.4 Suppose you wish to model the university schema as a graph. For each of the following relations,
explain whether the relation would be modeled as a node
or as an edge:
(i) student,
(ii) instructor,
(iii) course,
(iv) section,
(v) takes,
(vi) teaches.
Does the model capture connections between sections and courses?

5.5 What is a blockchain fork? List the two types of fork and explain their differences.

5.6 If you were designing a new public blockchain, why might you choose proofof-
stake rather than proof-of-work?

5.7 If you were designing a new public blockchain, why might you choose proofof-
work rather than proof-of-stake?

5.8 Data stored in a blockchain are protected by the tamper-resistance property

of a blockchain. In what way is this tamper resistance more secure in practice
than the security provided by a traditional enterprise database system?

5.9 Why do enterprise blockchains often incorporate database-style access?

Part F: Exam Preparation

Find the example of a music album database that can be formalized by the following relations:

Artist(Artist_ID, Artist, Artist_YOB)

Albums(Album_ID, Album_name, PublicationYear, Artist_ID)
Tracks(Album_ID, Track, Track_name)

The relation entries are displayed in tables 6.1-6.3 respectively (primary keys displayed in Italics. In the
following we use MariaDB syntax.

Artist_I Artist_YO
D Artist B
1 P!nk 1979
Katy
2 Perry 1984
3 Dua 1995
 Lipa
Ava
4 Max 1994

Table 6.1: Artist Relation

Album_I Publication_I Artist_I

D Album_name D D
8978 Hurts 2B Human 2019 1
8979 Witness (Deluxe) 2017 2
8980 Future Nostalgia 2020 3
8981 Heaven & Hell 2020 4
Future Nostalgia (DJ
8982 Mix) 2020 3
Table 6.2: Albums relation

Album_I
D Track Track_name
8978 1 Hustle
8978 2 (Hey Why) Miss You Sometime
8978 3 Walk me home
8978 4 My Attic
8979 1 Witness
8979 2 Hey Hey Hey
8980 1 Future Nostalgia
8980 2 Don’t Start Now
8980 3 Physical
8981 1 H.E.A.V.E.N
8981 2 Kings & Queens
Future Nostalgia (Joe Goddard
8982 1 Remix/Mixed)
8982 2 Physical (Mark Ronson Remix)
8980 3 Physical
8981 1 H.E.A.V.E.N
8981 2 Kings & Queens
Future Nostalgia (Joe Goddard
8982 1 Remix/Mixed)
8982 2 Physical (Mark Ronson Remix)
Table 6.3: Tracks relation.

6.1: Create the tables for the Artist, Albums and Tracks relation and fill it up with entries as shown in tables
6.1-6.3. Check (use SELECT * FROM Relation) whether the tables have been filled correctly.

6.2: Update the name of track 1 of the album with ID 8982 to 'Future Nostalgia (Joe Goddard Remix/Mixed)'.

6.3: Select all artists that are older than 30 years.

Hint: Use the artist’s birthday (variable Artist_YOB in the Artist relation)

6.4: Select all artists that were born in 1994.

6.5: Select the average number of tracks per album.

6.6: Select the average number of tracks just for albums with ID smaller or equal to 8980.

6.7: Use a join expression to add the albums, artist and tracks relation.

6.8: Show the number of tracks and albums per arts.

6.9: Show the average number of tracks per album for each artist