1AKIFT DBI Test (Group B)

2023-01-09

Max. 50 points

Name:

Task Max. Achieved
1 4
2 24
3 10
4 3
5 3
6 2
7 4
Sum 50
Grading: >=45: 1, >=39: 2, >=33: 3, >25: 4, <=25: 5
  1. 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 relational database is focused on relationships between in-memory objects.
    SQL is an acronym for "Structured Query Language".
    MariaDB and PostgreSQL are different names for the same DBMS.
    PostgreSQL is available with a permissive open source license.
    An ERD is a diagram that describes the structure of a database.
    A database engine allows to create, read, update and delete (CRUD) data from a database.
    PostgreSQL was bought by Oracle Corporation.
    DBeaver is a DBMS.
  2. Create a Database and Tables (24 points)
    Create the database computer_games matching the following ERD. Make sure to create the references between the tables. Elements in the consoles_games table should be deleted when the corresponding game is deleted. It must not be allowed to delete a console that is referenced from the consoles_games table.
    [Computer Games ERD]
    1 point for creating the database
    2 points for correct syntax to create the games table incl. semicolon
    2 points for the id column + primary key of the games table
    1 point for the games table's `name` column
    6 points (2+2+1+1) for the consoles table incl. semicolon
    7 points (2+2+1+1+1) for the consoles_games table incl. semicolon
    3 points for game_id reference w/ on delete cascade
    2 points for the (by default restricted) console_id reference
    -- mariadb solution
    create database computer_games;
    use computer_games;

    create table consoles (
    console_id INTEGER PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50)
    );

    create table games (
    game_id INTEGER PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    description VARCHAR(255)
    );

    create table consoles_games (
    console_game_id INTEGER PRIMARY KEY AUTO_INCREMENT,
    console_id integer,
    game_id integer,
    price integer,
    FOREIGN KEY (console_id) REFERENCES consoles (console_id) on delete restrict,
    FOREIGN KEY (game_id) REFERENCES games (game_id) on delete cascade
    );
  3. Adding Data (10 points)

    Add the following data to your database.

    consoles
    console_id name
    1 NES
    2 Wii
    3 Xbox
    4 PS 4
    games
    game_id name description
    1 Super Mario Bros.
    2 ASC
    3 Rayman
    3+3 points for insert into (...) values incl. semicolon at end
    2+2 points for data (2 for consoles, 2 for games)
    insert into consoles (name) values
    ('NES'),
    ('Wii'),
    ('Xbox'),
    ('PS 4');

    insert into games (name) values
    ('Super Mario Bros.'),
    ('ASC'),
    ('Rayman');
  4. Delete the 'ASC' game. (3 points)
    2 points for delete statement incl. semicolon
    1 point for correct condition
    delete from games where name = 'ASC';
  5. Rename the 'Super Mario Bros.' to 'Super Mario 1'. (3 points)
    1 point for update table incl. semicolon
    1 point for set name...
    1 point for correct condition
    update games set name = 'Super Mario 1' where name = 'Super Mario Bros.';
  6. Since we don't have time to maintain the content of the consoles_games table, remove it. (2 points)
    2 points for drop statement incl. semicolon
    drop table consoles_games;
  7. Finaly, show the content of both remaining tables (consoles and games) using two separate SQL statements. (4 points)
    2 points for each select statement incl. semicolon
    select * from consoles;
    select * from games;