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.

Answer: A general purpose database manager (DBM) has five responsibilities:

a. interaction with the file manager.
b. integrity enforcement.
c. security enforcement.
d. backup and recovery.
e. concurrency control.

If these responsibilities were not met by a given DBM (and the text points out that sometimes a
responsibility is omitted by design, such as concurrency control on a single-user DBM for a micro-
computer) the following problems can occur, respectively:
a. No DBM can do without this, if there is no file manager interaction then nothing stored in the
files can be retrieved.
b. Consistency constraints may not be satisfied, account balances could go below the minimum
allowed, employees could earn too much overtime (e.g., hours > 80) or, airline pilots may fly
more hours than allowed by law.
c. Unauthorized users may access the database, or users authorized to access part of the database
may be able to access parts of the database for which they lack authority. For example, a high
school student could get access to national defense secret codes, or employees could find out
what their supervisors earn.
d. Data could be lost permanently, rather than at least being available in a consistent
state that existed prior to a failure.
e. Consistency constraints may be violated despite proper integrity enforcement in each
transaction. For example, incorrect bank balances might be reflected due to simultaneous
withdrawals and deposits, and so on.

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

Answer: Five main functions of a database administrator are:

a. To create the scheme definition (construction of the database)
b. To define the storage structure and access methods
c. To modify the scheme and/or physical organization when necessary
d. To grant authorization for data access
e. To specify integrity constraints

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.

Answer: Note: The participated relation relates drivers, cars, and accidents.

a. Find the total number of people who owned cars that were involved in accidents
in 2007.

Note: this is not the same as the total number of accidents in 2007. We must count people with
several accidents only once.

Code:

select count (distinct name)

from accident, participated, person
where [Link]-number = [Link]-number
and [Link]-id = [Link]-id
and date between cast( ’2007-00-00’ as date) and cast( ’2007-12-31’ as date);

b. Find the number of accidents in which the cars belonging to “BCD” were involved.

Code:

select count (distinct report-number)

from accident
where exists
(select *
from participated, person
where [Link]-id = [Link]-id
and [Link] = ’BCD’
and [Link]-number = [Link]-number);

c. Add a new accident to the database; assume any values for required attributes. We assume the
driver was “BCD,” although it could be someone else. Also, we assume “BCD” owns one Bentley.
First we must find the license of the given car. Then the participated and accident relations must
be updated
in order to both record the accident and tie it to the given car. We assume values “Berkeley” for
location, ’2008-09-01’ for date and date, 7 for report number and 3000 for damage amount.

Code:

insert into accident

values (7, ’2001-09-01’, ’Berkeley’)

insert into participated

select [Link]-id, [Link], 7, 3000
from person p, owns o, car c
where [Link] = ’BCD’
and [Link]-id = [Link]-id
and [Link] = [Link] and [Link] = ’Bentley’
 d. Delete the Porsche belonging to “BCD”. Since model is not a key of the car relation, we can either
assume that only one of BCD’s cars is a Porsche, or delete all of BCD’s Porsches (the query is the
same). Again assume name is a key for person.

Code:

Delete from car

where model = ’Porsche’ and license in (
select license
from person p, owns o
where [Link] = ’BCD’ and [Link]-id = [Link]-id);create

Note: The owns, accident and participated records associated with the Porsche still exist.

e. Update the damage amount for the car with license number “A1” in the accident with report
number 3 to $3000.

Code:

update participated
set damage-amount = 3000
where report-number = 3
and driver-id in (
select driver-id
from owns
where license = “A1”);

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.

Answer:
a. Find the names of all employees who work for First Bank Corporation.

Code:

select employee-name
from works
where company-name = ’First Bank Corporation’;

b. Find the names and cities of residence of all employees who work for First Bank Corporation.

Code:

select [Link]-name, city

from employee e, works w
where [Link]-name = ’First Bank Corporation’
and [Link]-name = [Link]-name;

c. Find the names, street address, and cities of residence of all employees who work for First Bank
Corporation and earn more than $10,000. If people may work for several companies, the
following solution will only list those who earn more than $10,000 per annum from “First Bank
Corporation” alone.

Code:

select *
from employee
where employee-name in (
select employee-name
from works
where company-name = ’First Bank Corporation’
and salary > 10000);

As in the solution to the previous query, we can use a join to solve this one also.

d. Find all employees in the database who live in the same cities as the companies for which they
work.

Code:

select [Link]-name
from employee e, works w, company c
where [Link]-name = [Link]-name
and [Link] = [Link]
and [Link]-name = [Link]-name;

e. Find all employees in the database who live in the same cities and on the same streets as do their
managers.

Code:

select [Link]-name
from employee P, employee R, manages M
where [Link]-name = [Link]-name
and [Link]-name = [Link]-name
and [Link] = [Link]
 and [Link] = [Link];

f. Find all employees in the database who do not work for First Bank Corporation. The following
solution assumes that all people work for exactly one company.

Code:

select employee-name
from works
where company-name <> ’First Bank Corporation’;

If one allows people to appear in the database (e.g. in employee) but not appear in works, or if
people may have jobs with more than one company, the solution is slightly more complicated.

Code:

select employee-name
from employee
where employee-name not in (
select employee-name
from works
where company-name = ’First Bank Corporation’);

g. Find all employees in the database who earn more than every employee of Small Bank
Corporation. The following solution assumes that all people work for atmost one company.

Code:

select employee-name
from works
where salary > all (
select salary
from works
where company-name = ’Small Bank Corporation’);

If people may work for several companies and we wish to consider the total earnings of each
person, the problem is more complex. It can be solved by using a nested subquery, but we
illustrate below how to solve it using the with clause (not explicitly covered in the theory class).
The with clause provides a way of defining a temporary relation whose definition is available only
to the query in which the with clause occurs.

Code:

with emp-total-salary as (
select employee-name, sum(salary) as total-salary
from works
group by employee-name)
select employee-name
from emp-total-salary
where total-salary > all (
select total-salary
from emp-total-salary, works
where [Link]-name = ’Small Bank Corporation’
and [Link]-name = [Link]-name);

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. The simplest solution uses the like comparison.
 Code:

select [Link]-name
from company T
where (
select [Link]
from company R
where [Link]-name = [Link]-name)
like (
select [Link]
from company S
where [Link]-name = ’Small Bank Corporation’);

i. Find all employees who earn more than the average salary of all employees of their company.
The following solution assumes that all people work for atmost one company.

Code:

select employee-name
from works T
where salary > (
select avg (salary)
from works S
where [Link]-name = [Link]-name);
j. Find the company that has the most employees.

Code:

select company-name
from works
group by company-name
having count (distinct employee-name) >= all (
select count (distinct employee-name)
from works
group by company-name);

k. Find the company that has the smallest payroll.

Code:

select company-name
from works
group by company-name
having sum (salary) <= all (
select sum (salary)
from works
group by company-name);

l. Find those companies whose employees earn a higher salary, on average, than the average salary
at First Bank Corporation.

Code:

select company-name
from works
group by company-name
 having avg (salary) > (
select avg (salary)
from works
where company-name = ’First Bank Corporation’);

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.

Answer: The solution for part a. assumes that each person has only one tuple in the employee relation.
The solutions to part c. assumes that each person works for at most one company.

a. Modify the database so that Jones now lives in Newtown.

Code:

update employee
set city = ’Newtown’
where employee_name = ’Jones’;

b. Give all employees of First Bank Corporation a 10-percent raise.

Code:

update works
set salary = salary * 1.1
where company-name = ’First Bank Corporation’;

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.

Code:

update works T
set [Link] = [Link] * 1.03
where [Link]-name in (
select manager-name
from manages)
and [Link] * 1.1 > 100000
and [Link]-name = ’First Bank Corporation’;

update works T
set [Link] = [Link] * 1.1
where [Link]-name in (
select manager-name
from manages)
and [Link] * 1.1 <= 100000
and [Link]-name = ’First Bank Corporation’;

SQL-92 provides a case operation which gives a more concise solution:

update works T
 set [Link] = [Link] ∗(
case
when ([Link] ∗ 1.1 > 100000) then 1.03
else 1.1)
end
where [Link]-name in (
select manager-name
from manages)
and [Link]-name = ’First Bank Corporation’;

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.

Answer:

Code without a join expression:

select *
from student, takes
where [Link]= [Link];

Code with a join expression

select *
from student join takes on [Link]= [Link];

A version of this query that displays the ID value only once is as follows:

select [Link] as ID, name, dept name, tot cred, course id, sec id, semester, year, grade
from student join takes on [Link]= [Link];

The result of the above query is shown in Figure 2.5.

Figure 2.5: The result of the joined student and takes relation on the key [Link]= [Link] with
second occurrence of ID omitted.

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.

Answer:

Inner-Join-Code:
select [Link] as ID, name, dept name, tot cred, course id, sec id, semester, year, grade
from student inner join takes;

Left-Join-Code:

select [Link] as ID, name, dept name, tot cred, course id, sec id, semester, year, grade
from student natural left outer join takes;

Figure 2.6 shows the result of left outer join expression. That result includes student Snow (ID 70557),
unlike the result of an inner join, but the tuple for Snow includes nulls for the attributes that appear only
in theschema of the takes relation.

Figure 2.6: Result of student natural left outer join takes.

Right-Join-Code:

select [Link] as ID, name, dept name, tot cred, course id, sec id, semester, year, grade
from takes natural right outer join student;

The right outer join is symmetric to the left outer join. Tuples from the righthand-side relation that do not
match any tuple in the left-hand-side relation are padded with nulls and are added to the result of the
right outer join. Thus, if we rewrite our above query using a right outer join and swapping the order in
which
we list the relations, we get the same result except for the order in which the attributes appear in the
result (see figure 2.7).
Figure 2.7: The result of takes natural right outer join student.

Full-Outer-Join

select [Link] as ID, name, dept name, tot cred, course id, sec id, semester, year, grade
from student natural full outer join takes;

Note: In MySQL a full-outer-join expression is missing but since the full outer join is a combination of the
left and right outer-join types you can easily implement it by using a union expression.

select [Link] as ID, name, dept name, tot cred, course id, sec id, semester, year, grade
from student natural left outer join takes
union
select [Link] as ID, name, dept name, tot cred, course id, sec id, semester, year, grade
from student natural right outer join takes;

2.6 Find all students who have not taken a course.

Answer: You can use a left join for this.

Code:

select [Link]
from student natural left outer join takes
where course_id is null;

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.

Answer: This query can be written by using a full outer join.

Code:

select *
from (
select *
 from student
where dept name = ’Comp. Sci.’)
natural full outer join (
select *
from takes
where semester = ’Spring’ and year = 2009);

Solution in MariaDB:
Hint: Make use of temporary tables using the with clause
Note: Several temp tables can be used within ONE WITH clause but separated by a comma

WITH all_comp_sci as (
SELECT ID, student_name, dept_name
FROM student
WHERE dept_name = 'Comp. Sci.'),
spring_09 as (
SELECT ID, course_id, sem_year, grade
FROM takes
WHERE semester = 'Spring'
AND sem_year = 2009)
SELECT all_comp_sci.*, spring_09.course_id, spring_09.sem_year, spring_09.grade FROM all_comp_sci
LEFT OUTER JOIN spring_09 ON all_comp_sci.ID = spring_09.ID
UNION
SELECT all_comp_sci.*, spring_09.course_id, spring_09.sem_year, spring_09.grade FROM all_comp_sci
RIGHT OUTER JOIN spring_09 ON all_comp_sci.ID = spring_09.ID
WHERE all_comp_sci.ID IS NOT NULL;

Note: The full outer join is a combination of the left and right outer-join types. After the operation
computes the result of the inner join, it extends with nulls those tuples from the left-hand-side relation
that did not match with any from the right-hand side relation, and adds them to the result. Similarly, it
extends with nulls those tuples from the right-hand-side relation that did not match with any tuples from
the left-hand-side relation and adds them to the result

As a wrap up figure 2.8 summarizes the use of different joins with the help of a Venn diagram.
Figure 2.8: Overview of Joins in SQL.

2.8 Consider once again the employee database as depicted in Figure 2.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.

Answer:

create view salinfo as

select manager-name, avg(salary)
from manages m, works w
where [Link]-name = [Link]-name
group by manager-name

Views can provide the following advantages over tables (cf. Wikipedia):

- Views can represent a subset of the data contained in a table. Consequently, a view can limit the
degree of exposure of the underlying tables to the outer world: a given user may have
permission to query the view, while denied access to the rest of the base table.
- Views can join and simplify multiple tables into a single virtual table.
- Views can act as aggregated tables, where the database engine aggregates data (sum, average,
etc.) and presents the calculated results as part of the data.
- Views can hide the complexity of data. For example, a view could appear as Sales2000 or
Sales2001, transparently partitioning the actual underlying table.
- Views take very little space to store; the database contains only the definition of a view, not a
copy of all the data that it presents.
- Depending on the SQL engine used, views can provide extra security.
- Database practitioners can define views as read-only. Read-only views do not support such
operations because the DBMS cannot map the changes to the underlying base tables. In this case
views can serve as additional security mechanism to protect damages to the database.
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.

Answer: See Figure 3.1

Figure 3.1: E-R diagram for a Car-insurance company.

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.

Answer: See Figure 3.2

 Figure 3.2: E-R diagram for a university.

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 1965 1975 {1. Shine On You Crazy Diamond}
Were 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 no fulfill the condition of the first normal form (1NF).

Answer: Under 1NF condition, all attributes are atomic (i.e, single domain for each attribute), and each
record must be characterised by a unique primary key value. In this scenario:
- The album field contains the attribute value ranges Artist and Album Title.
- The tracks attribute is not atomic. Each album consists of several tracks, also indicated by the set
notation within the tracks cells entries.

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?

Answer: See Table 3.2.

CD_ID Album Title Interpret Production Publishing Year Track Title

Year
4711 Not That Kind Anastacia 1999 2000 1 Not That Kind
4711 Not That Kind Anastacia 1999 2000 2 I’m Outta Love
4711 Not That Kind Anastacia 1999 2000 3 Cowboys & Kisses
 4712 Wish You Were Here Pink Floyd 1965 1975 1 Shine On You Crazy Diamond
4713 Freak of Nature Anastacia 1999 2001 1 Paid my Dues
Table 3.2: Music albums fulfilling 1 NF condition.

The attribute value ranges are split into atomic attribute value ranges:
- The Album field is split into the Album Title and Artist fields.
- The Title List field is split into the Track and Title fields and split into multiple records.

A database fulfills the 2NF condition if and only if it fulfills the 1NF condition and every non-key attribute is
fully functionally dependent on the primary key. In this scenario:
- The primary key of the relation is composed of the fields CD_ID and Track (in principle, a primary
key may consist of several attributes, but in the above example this results in a conflict).
- The fields Album Title, Artist and Publishing Year depend on the field CD_ID, but not on the field
Track. This violates the 2NF, because the three non-primary attributes must not depend on only
one part of the key (here CD_ID). If the key were not composite, this could not happen.

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?

Answer: The data of table 3.2 is divided into two tables CD (Table 3.3) and Song (Table 3.4).

CD
CD_ID Album Title Interpret Production Year Publishing Year
4711 Not That Kind Anastacia 1999 2000
4712 Wish You Were Here Pink Floyd 1965 1975
4713 Freak of Nature Anastacia 1999 2001
Table: 3.3: CD table.

Song
CD_ID Track Title
4711 1 Not That Kind
4711 2 I’m Outta Love
4711 3 Cowboys & Kisses
4712 1 Shine On You Crazy Diamond
4713 1 Paid my Dues
Table 3.4: Song table.

The table CD only contains fields that are fully functionally dependent on CD_ID, so it has CD_ID as its
primary key. The Album Title alone would also be unique, i.e. a key candidate. Since no other (composite)
key candidates exist, the table is thus automatically in 2NF. Finally, the Song table only contains fields that
are fully functionally dependent on CD_ID and Track, so it is also in 2nd normal form.

A database is fulfilling the 3NF condition If and only if it fulfills the 2NF condition and no non-key attribute
is fully functionally dependent on other non-key attributes. Obviously, the Album Title of a CD can be
determined from the CD_ID, the Production Year of the band/artist depends in turn on the artist and thus
transitively on the CD_ID. The problem here is again data redundancy. If, for example, a new CD is
introduced with an existing artist, the year of foundation is stored redundantly.

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

Answer: The relation CD is split, whereby the two interdependent columns Album Title and Interpret are
swapped out into a separate table Artist (see tables 3.5 and 3.6) with the introduction of a new variable called
Interpret_ID. The key of the new table must remain in the old table as a foreign key. No changes were made to
the Song table during the transfer to the 3NF (see table 3.7).

CD
CD_ID Album Title Interpret_ID Erscheinungsjahr
4711 Not That Kind 311 2000
4712 Wish You Were Here 312 1975
4713 Freak of Nature 311 2001
Figure: 3.5: CD Table adjusted for 3NF.

Artist
Interpret_ID Interpret Gründungsjahr
311 Anastacia 1999
312 Pink Floyd 1965
Table 3.6: Artist table.

Song
CD_ID Track Title
4711 1 Not That Kind
4711 2 I’m Outta Love
4711 3 Cowboys & Kisses
4712 1 Shine On You Crazy Diamond
4713 1 Paid my Dues
Table 3.7: Song table.

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?

Answer: An entity is simply a collection of variables or data items. An object is an encapsulation of data as
well as the methods (code) to operate on the data. The data members of an object are directly visible only
to its methods. The outside world can gain access to the object’s data only by passing pre-defined
messages to it, and these messages are implemented by the methods.

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.
Answer:

create type vehicle (

vehicle-id integer,
license-number varchar(20),
manufacturer varchar(20),
model varchar(20),
purchase-date date,
color varchar(20))

create type truck

under vehicle (
cargo-capacity integer)

create type sports-car

under vehicle (
horsepower integer,
renter-age-requirement integer))

create type van

under vehicle (
num-passengers integer)

create type off-road-vehicle

under vehicle (
ground-clearance float,
drivetrain varchar(20))

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.

Answer:

XML-Code:

<!DOCTYPE db [
<!ELEMENT emp (ename, children*, skills*)>
<!ELEMENT children (name, birthday)>
<!ELEMENT birthday (day, month, year)>
<!ELEMENT skills (type, exams+)>
<!ELEMENT exams (year, city)>
<!ELEMENT ename( #PCDATA )>
<!ELEMENT name( #PCDATA )>
<!ELEMENT day( #PCDATA )>
<!ELEMENT month( #PCDATA )>
<!ELEMENT year( #PCDATA )>
<!ELEMENT type( #PCDATA )>
<!ELEMENT city( #PCDATA )>
 ]>

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.

Answer: Each of the applications includes large, specialized data items (e.g., a program module, a graphic
image, digitized voice, a document). These data items have operations specific to them (e.g., compile,
rotate, play, format) that cannot be expressed in relational query languages. These data items are of
variable
length making it impractical to store them in the short fields that are allowed in records for such database
systems. Thus, the data model, data manipulation language, and data definition language need to be
changed.
Also, long-duration and nested transactions are typical of these applications. Changes to the concurrency
and recovery subsystems are likely to be needed.

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).

Answer: Own research.

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.

Answer: In this scenario, a distributed key-value store is generally the better choice for storing a very large
number of small files (e.g., each 2 KB in size) for the following reasons.

1. Efficiency with Small Files

Distributed file systems (e.g., HDFS) are designed for handling large files efficiently, as they are optimized for
high throughput rather than low-latency access to small files. Distributed file systems typically divide files into
large blocks (e.g., 64 MB or 128 MB in HDFS) and store these blocks across multiple nodes. Storing small files in
this structure is inefficient because each small file would consume metadata and block storage space
inefficiently, leading to excessive storage overhead and unnecessary resource consumption.
Distributed key-value stores (e.g., Cassandra, DynamoDB) are well-suited for storing large numbers of small
items. Each key-value pair can map to a single small file, and these stores are optimized for low-latency
retrieval and high availability of small data items. Key-value stores also do not have the same overhead in
terms of large block management, making them a more resource-efficient choice for numerous small files.

2. Scalability and Metadata Management

In a distributed file system, each file (even a small one) requires a certain amount of metadata to manage
file locations, permissions, replication, etc. With a large number of small files, the metadata overhead
becomes significant, often leading to performance bottlenecks due to metadata server overload.
Distributed key-value stores, however, distribute both the data and metadata across nodes, often without a
central metadata server. This makes them more scalable and efficient for storing and managing large volumes
of small files. Each file can be accessed with a unique key, and the key-value store can quickly retrieve the data
based on the key.

3. Access Patterns and Low-Latency Requirements

 If you require frequent, low-latency access to individual small files, a key-value store is generally preferable.
Key-value stores are optimized for rapid read and write operations based on keys, which can be ideal if each
small file needs to be accessed individually.
Distributed file systems are more suited to sequential or batch processing of large files and may not perform
as well with random access to small files.

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.

Answer: To store data for a large number of students efficiently in a distributed document store like
MongoDB, you should leverage data embedding to ensure that all data related to a particular student is stored
within a single document. This design avoids the need for joins and ensures that all relevant data for a student
can be accessed efficiently by retrieving a single document.
Example Data Model

Assume we have the following relations:

Student relation, containing basic information about each student, such as student_id, name, and major.
Takes relation, representing courses taken by each student, with attributes such as course_id, course_name,
and grade.

In MongoDB, we can structure the data for each student as a single document that includes the student's basic
information and an embedded array of courses taken by the student. This way, all information about the
student is kept together in a single document, and accessing it requires only one read operation.
MongoDB Document Structure.

Each student's data would be represented as a document in a students collection, where each document might
look like this:

json

{
"student_id": "S12345",
"name": "Alice Smith",
"major": "Computer Science",
"courses": [
{
"course_id": "CS101",
"course_name": "Introduction to Computer Science",
"grade": "A"
},
{
"course_id": "CS102",
"course_name": "Data Structures",
"grade": "B+"
},
{
"course_id": "MATH201",
"course_name": "Calculus I",
"grade": "A-"
}
]
}
Explanation of the Data Representation

Embedding Courses as an Array: The courses array is embedded directly in each student document. Each
element in this array represents a course that the student has taken, including details like course_id,
course_name, and grade.
Efficient Access: By embedding the courses within the student's document, we ensure that all data related
to a student can be retrieved with a single query. For example, fetching all data for a student with student_id:
"S12345" only requires querying one document.
Atomicity and Consistency: Since each student's data is stored in a single document, any updates to a
student's courses or personal details can be performed atomically, ensuring data consistency without needing
complex transactions.

Advantages of This Approach

Fast Read Performance: Since all relevant data for a student is stored in a single document, retrieval is fast
and efficient, suitable for applications requiring quick data access.
Simplicity: This structure is easy to manage and aligns well with MongoDB's document-based model, where
each document represents a complete entity.
Scalability: MongoDB’s sharding and distributed architecture support efficient scaling, even with a large
number of documents (students) in the collection.

This design ensures that all data for a student can be accessed efficiently and managed easily within a
distributed document store like MongoDB.

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?

Answer: To store utility bills for a large number of users in a key-value store that supports range queries, we
need a design that:

1. Allows efficient retrieval of bills for a specific customer.

2. Supports date range queries for bills of each customer.

A typical approach in a key-value store is to use a composite key that combines the customer_id and the date
to store each bill. This design enables efficient range queries on dates within each customer’s records by
organizing bills in a way that naturally orders them chronologically.
Key-Value Design

1. Key Structure: Use a composite key in the format customer_id:date.

For example, a key for a bill issued to customer C12345 on 2024-01-15 could be represented as:

C12345:2024-01-15

The date should be in ISO format (YYYY-MM-DD) so that it sorts lexicographically. This ensures that bills
are stored in chronological order within each customer’s records, enabling range queries on dates.

2. Value: Store each bill’s details (e.g., amount, billing period, due date) in the value associated with this
composite key. The value could be a serialized JSON or binary object containing all relevant information
about the bill.

Example Data Entries

Here’s how several bills for customer C12345 might be stored:

Key Value
C12345:2024-01-15 { "amount": 100, "billing_period": "Jan 2024", "due_date": "2024-02-15" }
C12345:2024-02-15 { "amount": 95, "billing_period": "Feb 2024", "due_date": "2024-03-15" }
C12345:2024-03-15 { "amount": 105, "billing_period": "Mar 2024", "due_date": "2024-04-15" }

Each entry is stored as a single key-value pair, with the key encoding both the customer ID and the date.
Querying Bills for a Date Range

To retrieve bills for a specified customer within a date range (e.g., bills for customer C12345 from 2024-01-01
to 2024-03-31), we can use a range query on the composite keys:

Start key: C12345:2024-01-01

End key: C12345:2024-03-31

The key-value store will return all keys in this range, efficiently retrieving bills for that customer within the
specified date range.
Advantages of This Approach

Efficient Range Queries: Because the date is part of the key and is in lexicographical order, the range query
retrieves only the relevant bills for the specified customer and date range.
Simplicity: This design avoids secondary indexes by embedding both customer_id and date in the key.
Scalability: Key-value stores that support range queries are generally optimized for composite key-based
retrievals like this, making the design scalable across large data volumes.

This approach ensures efficient storage and retrieval of utility bills in a way that aligns well with the capabilities
of a distributed key-value store that supports range queries.

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?

Answer: In a graph model, we represent entities as nodes and relationships between entities as edges. Here’s
how each relation in the university schema could be represented:
(i) student - Node

Each student is an individual entity, so it makes sense to represent each student as a node in the graph. This
node can have attributes such as student_id, name, major, etc.

(ii) instructor - Node

Similarly, each instructor is a unique entity, so each instructor should also be a node. Attributes could
include instructor_id, name, department, etc.

(iii) course - Node

Each course is a distinct entity that exists independently of students or instructors, so it is best represented
as a node. Attributes might include course_id, course_name, and credits.

(iv) section - Node

 A section represents a specific instance of a course (for example, a semester-specific or section-specific
offering of a course). It has its own attributes, such as section_id, semester, and year, and thus can be modeled
as a node.
This approach also makes it easier to link specific sections to students and instructors.

(v) takes - Edge

The takes relation represents an association between a student and a section, indicating that the student is
enrolled in that section. Since it describes a relationship, takes should be represented as an edge between a
student node and a section node.
Additional attributes like grade can be stored as properties on the edge.

(vi) teaches - Edge

The teaches relation represents an association between an instructor and a section, indicating that the
instructor teaches that specific section. This is also a relationship and should be represented as an edge
between an instructor node and a section node.

Does the Model Capture Connections Between Sections and Courses?

Yes, this model can capture connections between sections and courses. To do this, we could add an edge (e.g.,
offering_of) between each section node and the corresponding course node. This edge represents the fact that
a particular section is an offering of a specific course.

For example:

A section node with section_id "SEC001" in "Fall 2024" could be connected to a course node with course_id
"CS101" by an edge labeled offering_of.

This offering_of edge allows us to easily trace which course a particular section belongs to, thus capturing the
relationship between sections and courses within the graph model.

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

Answer: A blockchain fork occurs when there is a divergence in the blockchain’s transaction history, resulting
in two or more separate chains. Forks can arise intentionally due to protocol updates or changes, or
unintentionally when miners or nodes disagree temporarily on the next block to add. They are typically
classified into two types: soft forks and hard forks.

1. Hard Fork

A hard fork is a permanent divergence in the blockchain, where nodes running the new version of the software
are incompatible with nodes running the old version. In a hard fork, all participants must upgrade to the new
protocol if they want to remain on the same chain. If some participants do not upgrade, the blockchain splits
into two separate chains: one following the old rules, and one following the new rules.

Example: Ethereum’s hard fork in 2016 after the DAO hack led to two separate blockchains: Ethereum (ETH)
and Ethereum Classic (ETC). Ethereum moved to the new protocol, while Ethereum Classic maintained the old
one.

Compatibility: Hard forks are not backward-compatible, meaning nodes running the older version cannot
validate blocks created under the new rules.

When Necessary: Hard forks are typically used for substantial protocol upgrades or to correct critical security
issues.
2. Soft Fork

A soft fork is a backward-compatible update to the blockchain. In a soft fork, the new rules are more restrictive
than the old rules, meaning that nodes running the old software can still recognize and validate blocks created
under the new protocol as valid (but not vice versa). For a soft fork to be effective, a majority of the network’s
hash power or nodes must adopt the new rules.

Example: Bitcoin’s SegWit (Segregated Witness) upgrade in 2017 was implemented as a soft fork. It
introduced a new way to structure transactions, which increased block capacity without requiring a hard fork.

Compatibility: Soft forks are backward-compatible, as nodes that do not upgrade can still participate in the
network and validate transactions, even if they do not adopt the new rules.

When Used: Soft forks are often used for minor upgrades, feature additions, or optimizations that do not
require a complete overhaul of the protocol.

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

Answer: If we were designing a new public blockchain, we might choose proof-of-stake (PoS) over proof-of-
work (PoW) for several key reasons:

1. Energy Efficiency

Proof-of-work requires miners to solve complex cryptographic puzzles, which demands a tremendous
amount of computational power and energy. The environmental impact is substantial, with networks like
Bitcoin consuming as much energy as some small countries.
Proof-of-stake, on the other hand, does not rely on intensive computational work. Instead, validators are
chosen to create new blocks based on the amount of cryptocurrency they hold (or "stake") in the network.
This drastically reduces the energy consumption of the network, making it more environmentally sustainable.

2. Cost Effectiveness

Running a proof-of-work blockchain is costly because miners need powerful hardware (such as ASICs) and
must continually spend on electricity to remain competitive. This creates a barrier to entry, as only those who
can afford the substantial investment in equipment and energy can participate.
Proof-of-stake reduces these entry costs because validators only need to lock up tokens as their "stake"
rather than investing in expensive hardware. This makes the network more accessible and encourages broader
participation.

3. Increased Security Against Certain Attacks

Proof-of-stake is generally more resistant to a 51% attack in which a single entity gains control over the
majority of network resources. In PoS, an attacker would need to acquire more than 50% of the total stake
(tokens), which would be extremely costly on a well-established network.

4. Scalability and Performance

Proof-of-stake generally supports faster block times and more efficient consensus mechanisms, which can
improve transaction throughput and scalability. This makes it easier to implement scaling solutions, as PoS
networks often have shorter block intervals and finality times compared to PoW.
Proof-of-work blockchains like Bitcoin have longer block times and slower transaction processing rates,
limiting their ability to handle high transaction volumes, which can be a drawback for high-use public
blockchain applications.
5. Decentralization and Accessibility

By reducing reliance on expensive mining hardware and high electricity consumption, proof-of-stake
encourages a more decentralized distribution of validators across the network. More people can participate in
staking since they only need tokens and not specialized equipment.
In proof-of-work, mining tends to centralize over time, as those with better access to capital and electricity
become dominant players, reducing the overall decentralization of the network.

6. Community and Regulatory Pressures

There is growing regulatory and public concern about the environmental impact of high-energy consensus
mechanisms like proof-of-work. Choosing proof-of-stake aligns with these sustainability concerns and may
reduce the likelihood of regulatory restrictions or public opposition.

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

Answer: If we were designing a new public blockchain, there are some important reasons why we might
choose proof-of-work (PoW) over proof-of-stake (PoS), particularly if the focus is on proven security, simplicity,
and robustness. Here are the main reasons:

1. Proven Security and Robustness

Proof-of-work is a time-tested consensus mechanism that has demonstrated strong security on established
networks like Bitcoin and Ethereum (prior to Ethereum's transition to PoS). PoW has shown resilience against a
wide range of attacks, with over a decade of real-world use and battle-tested protocols.
By requiring miners to perform intensive computational work to add blocks, PoW discourages attacks as
they would require significant computing resources. This high cost of attack has helped PoW-based
blockchains remain secure against double-spending and other threats.

2. Simplicity and Transparency

Proof-of-work is conceptually straightforward. It requires solving cryptographic puzzles, which are easy to
understand, verify, and implement. This simplicity can enhance transparency, as the rules for block creation
are objective and visible to all participants.
In contrast, proof-of-stake introduces more complexity, with factors like staking, slashing conditions, and
validator rotation, which can make the protocol more difficult to design, implement, and explain.

3. Sybil Resistance and Equal Opportunity Mining

Proof-of-work relies on computational power, which is widely available and accessible. Anyone with the
necessary hardware can participate in mining, theoretically leveling the playing field and providing a more
equal opportunity to contribute to block validation.
In proof-of-stake, validators are selected based on their token holdings, which can lead to centralization, as
those with more wealth can stake larger amounts and have a greater influence over the network. This wealth-
based selection process can be seen as favoring the already wealthy and potentially reducing decentralization
over time.

4. Resistance to Certain Forms of Centralization

Mining in proof-of-work is naturally limited by external resources like energy availability and hardware costs,
which adds a physical layer of decentralization. Although mining pools exist, PoW miners are generally spread
across different geographic locations, energy providers, and hardware suppliers.
Proof-of-stake networks, on the other hand, might face centralization pressures more quickly, as large
stakeholders have more influence in the network simply by owning tokens. This could allow large holders to
dominate consensus, especially in networks where wealth concentration is high.
5. Immutable History and High Commitment Costs

Proof-of-work adds blocks to the chain by solving computational puzzles, and each new block deepens the
chain’s history in a way that becomes progressively harder to reverse. The energy and computational
resources required to rewrite history in PoW networks provide a strong guarantee of immutability.
Proof-of-stake is sometimes considered more susceptible to certain attack vectors, such as long-range
attacks and nothing-at-stake attacks, because validators do not need to expend physical resources. Ensuring
the same level of historical immutability in PoS requires additional protocol safeguards, which can increase
complexity.

6. Community and Regulatory Acceptance in Certain Cases

For certain use cases, proof-of-work blockchains like Bitcoin have become recognized as highly secure and
reliable, with a strong community and market acceptance around their trustworthiness as a store of value.
Bitcoin’s success has also led to legal recognition in some jurisdictions, which might be valuable if the new
blockchain aims to build similar credibility.
PoW's transparent and deterministic process could attract communities that prioritize security and
decentralization over scalability or energy efficiency.

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?

Answer: The tamper resistance of a blockchain is generally more secure in practice than the security provided
by a traditional enterprise database system for several key reasons:

1. Decentralization and Distributed Consensus

In a blockchain, data is stored across a decentralized network of nodes that must reach consensus to
validate each transaction and add new data to the chain. This consensus process makes it nearly impossible for
any single entity to unilaterally alter the data once it’s confirmed.
In contrast, a traditional enterprise database is typically centralized, meaning data storage and management
are controlled by a single entity or a small group of administrators. A centralized setup is more vulnerable to
tampering because a single compromised administrator or insider could alter or delete records without
detection.

2. Immutability Through Cryptographic Hashing

Blockchains achieve immutability by linking blocks of data in a chain, where each block contains a
cryptographic hash of the previous block. This linking means that any modification to a previous block would
alter its hash and invalidate all subsequent blocks. Therefore, to change a single record, an attacker would
need to re-compute hashes for the modified block and every block that follows, which is computationally
infeasible on well-secured blockchains.
Traditional enterprise databases do not use cryptographic chaining or hashing for historical data, so it’s
easier for unauthorized users or insiders to tamper with records without detection. While some databases
offer logging or auditing, these features are often optional and can be bypassed by those with sufficient access
privileges.

3. Transparency and Public Verification

Most blockchains are public or allow broad access to their transaction history, meaning that anyone can
verify the integrity of the data. This transparency ensures that changes to the blockchain are visible to all
participants, which discourages tampering and makes it easy to detect anomalies.
Enterprise databases are generally private, with limited access. Changes made by privileged users, especially
if logs are not monitored or logs are tampered with, are not visible to external parties. This reduces the
transparency and accountability mechanisms in place to detect tampering.
4. High Attack Cost Due to Proof Mechanisms

Blockchain networks, especially those using proof-of-work (PoW) or proof-of-stake (PoS), make tampering
prohibitively expensive. In PoW, an attacker would need enormous computational power to re-mine blocks in
order to alter past data. In PoS, the attacker would need to control a majority stake in the network’s
cryptocurrency, which would require purchasing or controlling a significant portion of the total token supply.
Traditional databases do not have such built-in mechanisms to prevent unauthorized changes. An attacker
with sufficient access privileges (like an administrator) could alter records without needing to overcome
significant resource-based barriers.

5. Consensus-Based Validations and Redundancy

Every transaction in a blockchain is validated by multiple independent nodes. This redundancy ensures that
even if some nodes are compromised, the network as a whole can maintain integrity through consensus. It’s
difficult for an attacker to compromise or manipulate enough nodes to rewrite history on a distributed
blockchain.
In an enterprise database, there is often no such redundancy in transaction validation. If the central
database or its backup is compromised, an attacker could alter records without requiring consensus from
other parties, making tampering much easier.

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

Answer: Enterprise blockchains often incorporate database-style access to meet the specific needs of
businesses and organizations, which differ significantly from the requirements of public blockchains. Here are
the main reasons why enterprise blockchains blend blockchain technology with traditional database features:
1. Access Control and Privacy Requirements

Enterprises often handle sensitive data, such as financial records, medical information, or trade secrets.
Unlike public blockchains, where data is fully transparent and accessible to anyone, enterprise blockchains
need fine-grained access control to ensure that only authorized users can view or modify certain data.
Database-style access allows enterprises to set permissions for specific users or groups, ensuring compliance
with privacy regulations like GDPR or HIPAA. This restricted access model is essential for organizations that
must maintain confidentiality and data protection while still using blockchain technology.

2. Efficient Data Querying and Retrieval

Traditional databases are designed for efficient querying, indexing, and data retrieval. Enterprise
applications often require complex queries to generate reports, extract insights, and support decision-making.
Integrating database-style querying capabilities enables enterprise blockchains to support more
sophisticated data retrieval processes than a typical blockchain, which primarily focuses on appending
transactions to a ledger rather than complex queries. This allows enterprises to leverage blockchain for secure,
verifiable records while still performing necessary data analytics.

3. Scalability and Performance Needs

Enterprise applications often involve high transaction volumes and demand quick response times, which
traditional blockchains struggle to achieve due to their consensus mechanisms and append-only data
structures.
By incorporating database-style access and hybrid architectures, enterprise blockchains can handle higher
volumes of transactions more efficiently. Techniques like caching, indexing, and partial data replication help
improve scalability and performance, making the blockchain more suitable for business environments where
speed and throughput are critical.

4. Support for Off-Chain Data and Hybrid Storage Solutions

Enterprises often need to store large files, such as documents or multimedia files, which may not be suitable
for direct storage on a blockchain due to size limitations and cost.
 Database-style access allows an enterprise blockchain to manage off-chain storage effectively. In this model,
the blockchain can store hashes or references to data stored in traditional databases or distributed file
systems, combining the immutability of blockchain with the storage flexibility of databases. This approach
supports both blockchain integrity and practical data storage requirements.

5. Interoperability with Legacy Systems

Many enterprises already rely on traditional databases and legacy systems for core operations. Database-
style access in enterprise blockchains facilitates integration with existing databases and applications, allowing
organizations to adopt blockchain without completely overhauling their infrastructure.
This interoperability allows data to flow between blockchain and non-blockchain systems, enhancing data
consistency and enabling blockchain to add value to existing processes rather than replace them entirely.

6. Data Consistency and Transaction Management

Enterprise applications typically require transaction management features, such as atomic transactions and
rollbacks, which are standard in traditional databases. Database-style access allows enterprise blockchains to
incorporate these features, ensuring data consistency and reliability.
For example, in financial applications, it is critical to ensure that either all parts of a transaction are
processed or none at all (atomicity). Implementing database-style transaction controls helps enterprise
blockchains meet these needs without compromising the reliability and consistency of data.

7. Enhanced Auditing and Compliance

Many enterprises must comply with auditing and regulatory requirements that mandate traceability and
accountability for data access and modifications. Database-style access in an enterprise blockchain can enable
detailed audit trails, showing who accessed what data, when, and what changes were made.
While blockchain inherently provides a record of transactions, the addition of database-style access and
logging allows organizations to monitor access and modifications at a more granular level, aiding compliance
with regulatory standards and providing evidence for audits.

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_ID Artist Artist_YOB

1 P!nk 1979
Katy
2 Perry 1984
Dua
3 Lipa 1995
Ava
4 Max 1994

Table 6.1: Artist Relation

Album_ID Album_name Publication_ID Artist_ID

 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_ID 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

Answer:
-- Create table Artist
CREATE TABLE Artist (
Artist_ID INTEGER, -- ID for an artist (we do it manually, so no auto-
increment)
Artist VARCHAR(30) NOT NULL, -- name of the artist, 30 characters, NOT
NULL = is required
Artist_YOB INTEGER(4), -- four digit integer for the year of birth
(MySQL/MariaDB also has YEAR as type, other implementations do not have)
PRIMARY KEY (Artist_ID) -- becomes primary key and thereby implictly
UNIQUE as well
);

-- Create table Album

CREATE TABLE Albums (
Album_ID INTEGER, -- ID for an album (we do it manually, so no auto-
increment)
Album_name VARCHAR(50) NOT NULL, -- NOT NULL = is required
PublicationYear INTEGER(4) NOT NULL, -- four digit integer for the
year, NOT NULL = is required
Artist_ID INTEGER, -- that will be our foreign key (see below)
FOREIGN KEY (Artist_ID) REFERENCES Artist(Artist_ID), -- degining the
relationship
PRIMARY KEY (Album_ID)
);

-- Create table Tracks

CREATE TABLE Tracks (
Album_ID INTEGER,
Track INTEGER,
Track_name VARCHAR(160) NOT NULL, -- each track needs a name
FOREIGN KEY (Album_ID) REFERENCES Albums(Album_ID),
PRIMARY KEY (Album_ID, Track)
);

-- inserting values into Artist

INSERT INTO Artist (Artist_ID, Artist, Artist_YOB) VALUES ('1', 'P!nk',
'1979');
INSERT INTO Artist (Artist_ID, Artist, Artist_YOB) VALUES ('2', 'Katy
Perry', '1984');
INSERT INTO Artist (Artist_ID, Artist, Artist_YOB) VALUES ('3', 'Dua Lipa',
'1995'), ('4', 'Ava Max', '1994'); -- insert multiple entries at once,
please note the use of brackets

SELECT * FROM Artist;

-- inserting values into Albums

INSERT INTO Albums (Album_ID, Album_name, PublicationYear, Artist_ID)
VALUES ('8978', 'Hurts 2B Human', '2019', '1'), ('8979', 'Witness
(Deluxe)', '2017', '2'), ('8980', 'Future Nostalgia', '2020', '3'),
('8981', 'Heaven & Hell', '2020', '4'), ('8982', 'Future Nostalgia (DJ
Mix)', '2020', '3');

SELECT * FROM Albums;

-- inserting values into Tracks

INSERT INTO Tracks (Album_ID, Track, Track_name) VALUES
(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'),
(8982, 1, 'Future Nostalgia (Joe Goddard Remix / Mixed)'),
(8982, 2, 'Physical (Mark Ronson Remix)');

SELECT * FROM Tracks;

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

-- updating some information

UPDATE Tracks SET Track_name = 'Future Nostalgia (Joe Goddard Remix/Mixed)'
WHERE Album_ID = 8982 AND Track = 1;

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

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

Answer:

-- select artists with a calculated age >= 30

SELECT Artist, 2021 - Artist_YOB AS age
FROM Artist
WHERE 2020 - Artist_YOB >= 30;

11.4: Select all artists that were born in 1994.

Answer:

--select Artists born between 1994 and 1995

SELECT *
FROM Artist
WHERE Artist_YOB >= 1994
AND Artist_YOB < 1995;

11.5: Select the average number of tracks per album.

Answer:

-- SELECT average number of tracks per album, including subquery

SELECT AVG(numberOfTracks)
FROM (
SELECT Album_ID, COUNT(Track) AS numberOfTracks
FROM Tracks
GROUP BY Album_ID) AS tracksCountTable;

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

Answer:

-- average number of tracks - just for albums with ID smaller or equal 8980
SELECT AVG(numberOfTracks)
FROM (
SELECT Album_ID,
COUNT(Track) AS numberOfTracks
FROM Tracks WHERE Album_ID <= 8980
GROUP BY Album_ID) AS tracksCountTable;

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

Answer:

-- JOIN tracks with albums with artist

SELECT *
FROM Tracks INNER JOIN Albums ON Tracks.Album_ID = Albums.Album_ID
INNER JOIN Artist ON Albums.Artist_ID = Artist.Artist_ID;

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

Answer:

-- Get number of tracks and albums per artist (albums need to be distinct
in the count)
SELECT [Link], COUNT([Link]) AS numberOfTracks, COUNT(DISTINCT
Albums.Album_ID) AS numberOfAlbums
FROM Tracks INNER JOIN Albums ON Tracks.Album_ID = Albums.Album_ID
INNER JOIN Artist ON Albums.Artist_ID = Artist.Artist_ID
GROUP BY [Link];

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

Answer:

-- Get average number of tracks per album for each artist

SELECT *, numberOfTracks/numberOfAlbums AS avgNumberTracksPerAlbum
FROM (
SELECT [Link], COUNT([Link]) AS numberOfTracks,
COUNT(DISTINCT Albums.Album_ID) AS numberOfAlbums
FROM Tracks INNER JOIN Albums ON Tracks.Album_ID = Albums.Album_ID
INNER JOIN Artist ON Albums.Artist_ID = Artist.Artist_ID
GROUP BY [Link]) AS artistStat;