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 |
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. |
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.-- 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
);
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 |
insert into consoles (name) values
('NES'),
('Wii'),
('Xbox'),
('PS 4');
insert into games (name) values
('Super Mario Bros.'),
('ASC'),
('Rayman');
delete from games where name = 'ASC';
update games set name = 'Super Mario 1' where name = 'Super Mario Bros.';
drop table consoles_games;
consoles
and games
)
using two separate SQL statements. (4 points)
select * from consoles;
select * from games;