1AKIFT DBI Test (Group A

Task	Max.	Achieved
1	4
2	24
3	10
4	3
5	3
6	2
7	4
Sum	50

Databases, DBMS, SQL
Answer the following statements indicating whether they are True or False.

0-4 correct: 0 points, 5 correct: 1 point, 6 correct: 2 points, 7 correct: 3 points, 8 correct: 4 points.

Statement	True	False
A database is an organized collection of data stored and accessed electronically.
SQL is an acronym for "Secure Query Language".
MariaDB and Mysql were founded by the same person.
PostgreSQL is a DBMS.
With regard to databases, a relation describes the reference between two tables.
A database engine allows to create, read, update and delete (CRUD) data from a database.
MariaDB was bought by Oracle Corporation.
DBeaver is a DBMS.

Create a Database and Tables (24 points)
Create the database beer matching the following ERD. Make sure to create the references between the tables. Elements in the beer_ingredients table should be deleted when the corresponding beer is deleted. It must not be allowed to delete an ingredient that is referenced from the beer_ingredients table.
[Beer DB ERD]

1 point for creating the database
2 points for correct syntax to create the ingredients table incl. semicolon
2 points for the id column + primary key of the ingredients table
1 point for the ingredient table's `name` column
6 points (2+2+1+1) for the beers table incl. semicolon
7 points (2+2+1+1+1) for the beer_ingredients table incl. semicolon
3 points for beer_id reference w/ on delete cascade
2 points for ingredient_id reference

-- mariadb solution
create database beer;
use beer;

create table ingredients (
  ingredient_id INTEGER PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50)
);

create table beers (
  beer_id INTEGER PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50),
  description VARCHAR(255)
);

create table beer_ingredients (
  beer_ingredient_id INTEGER PRIMARY KEY AUTO_INCREMENT,
  quantity numeric,
  beer_id integer,
  ingredient_id integer,
  FOREIGN KEY (beer_id) REFERENCES beers (beer_id) on delete cascade,
  FOREIGN KEY (ingredient_id) REFERENCES ingredients (ingredient_id)
);

Adding Data (10 points)

Add the following data to your database.

`ingredients`
`ingredient_id`	`name`
1	hops
2	malt
3	cherry
4	water
5	wheat

`beers`
`beer_id`	`name`	`description`
1	lager
2	ale

3+3 points for insert into (...) values incl. semicolon at end
2+2 points for data (2 for ingredients, 2 for beers)

insert into ingredients (name) values
('hops'),
('malt'),
('cherry'),
('water'),
('wheat');

insert into beers (name) values
('lager'),
('ale');

Delete the 'cherry' ingredient. (3 points)

2 points for delete statement incl. semicolon
1 point for correct condition

delete from ingredients where name = 'cherry';

Replace the 'ale' with an 'IPA'. (3 points)

1 point for update table incl. semicolon
1 point for set name...
1 point for correct condition

update beers set name = 'IPA' where name = 'ale';

Since we don't have time to maintain the content of the beer_ingredients table, remove it. (2 points)

2 points for drop statement incl. semicolon

drop table beer_ingredients;

Finaly, show the content of both remaining tables (beers and ingredients) using two separate SQL statements. (4 points)

2 points for each select statement incl. semicolon

select * from ingredients;
select * from beers;