MySQL/MariaDB SQL TASK
Budget: €8 – €30 EUR
Write SQL statements (MySQL/MariaDB SQL) to:
Create a relational database named ‘GourmetHub’ using MySQL/MariaDB SQL (write also the statement to use it).
Create the following table: RESTAURANT (CODE, name, location) with the following restrictions:
CODE is the primary key. It is an alphanumeric code designating each unique restaurant. The code can have 1 to 5 characters.
NAME is an alphanumeric sequence, it is required, and it is unique.
LOCATION is an alphanumeric sequence, indicating the address or area of the restaurant, and it is required.
Create the following table: DISHES (CODE, name, type, price) with the following restrictions:
CODE is the primary key. It is a four-character alphanumeric code designating each unique dish.
NAME is an alphanumeric sequence, it is required, and it is unique.
TYPE is an alphanumeric sequence, and it denotes the category of the dish (e.g., appetizer, main course, dessert).
PRICE is a numeric field indicating the cost of the dish.
Execute the following statement: INSERT INTO DISHES (CODE, name, type, price) VALUES ('D0001', 'Italian Spaghetti', 'Main Course', 12.99);
Does it work? If not, identify which integrity rule is not being met and modify the statement to solve the problem.
Create the following table: EMPLOYEES (ID, name, surname, role, restaurant_code)
Note that ID is the primary key. It is a numeric field and must be AUTO_INCREMENT.
Note that the field ‘restaurant_code’ is required. This field needs to have the same type as RESTAURANT (CODE), but you don't have to create the foreign key at this point.
The fields ‘name’ and ‘surname’ are required, and they are alphanumeric sequences.
Two employees can't have the same name and surname.
The role field is required and indicates the employee's job (e.g., chef, waiter, manager).
Modify the schema of the table EMPLOYEES setting restaurant_code as a foreign key. The policy on update must be CASCADE, and the policy on delete must be SET NULL. If this doesn't work, explain what is happening and how to solve it.
Explain the three policies on delete/update that can be configured on a foreign key.
Test and explain the RESTRICT policy on delete. To do this, follow these commands and explain each step:
Modify the schema of the table EMPLOYEES setting restaurant_code as a foreign key. The policy on update must be RESTRICT and the policy on delete must be RESTRICT.
Insert a row in RESTAURANT table (if it doesn't exist).
Insert a row in EMPLOYEES table (if it doesn't exist).
Try to delete the row with the specific CODE from RESTAURANT.
Test the CASCADE policy on delete.
Modify the schema of the table EMPLOYEES with CASCADE policies for update and delete.
Perform similar INSERT and DELETE operations as before and explain the outcome.
Test the SET NULL policy on delete.
Modify the schema of the table EMPLOYEES with SET NULL policies for update and delete.
Perform similar INSERT and DELETE operations as before and explain the outcome.
Create the following table: RESERVATIONS (ID, customer_name, party_size, date, time, employee_id)
ID is a numeric field, the primary key, and is auto-incremental.
employee_id is a foreign key related to EMPLOYEES (ID).
Modify the schema of the table EMPLOYEES, adding a new field named ‘hire_date’, the type of the field will be DATE.
Modify the schema of the table EMPLOYEES, delete the attribute 'role'.
Modify the schema of the table EMPLOYEES, add a new field with the name 'position'. This field can be NULL. This field can just take 4 values: ['Chef' | 'Waiter' | 'Host' | 'Manager'].
Create a user with your name and a safe password and give him all the privileges on the GourmetHub database. Test that your user works successfully.
Creating Roles and Users in MySQL. Learn to create roles with different permissions and assign these roles to specific users in a MySQL database.
Step 1: Creation of Roles.
Create an Administrator role. This role should have permissions to perform all DDL and DML operations on the database.
Use the CREATE ROLE command to create the role.
Assign the appropriate permissions using the GRANT command.
Create the Reader Role. This role should have permissions only to perform read operations (SELECT) on all tables in the database.
Use the CREATE ROLE command to create the role.
Assign the appropriate permissions using the GRANT command.
Step 2: Create a User Associated with the Administrator Role:
Create a user named admin_user.
Assign a secure password.
Associate this user with the administrator role.
Step 3: Create a User Associated with the Reader Role:
Create a user named read_user.
Assign a secure password.
Associate this user with the reader role.
Create a relational database named ‘GourmetHub’ using MySQL/MariaDB SQL (write also the statement to use it).
Create the following table: RESTAURANT (CODE, name, location) with the following restrictions:
CODE is the primary key. It is an alphanumeric code designating each unique restaurant. The code can have 1 to 5 characters.
NAME is an alphanumeric sequence, it is required, and it is unique.
LOCATION is an alphanumeric sequence, indicating the address or area of the restaurant, and it is required.
Create the following table: DISHES (CODE, name, type, price) with the following restrictions:
CODE is the primary key. It is a four-character alphanumeric code designating each unique dish.
NAME is an alphanumeric sequence, it is required, and it is unique.
TYPE is an alphanumeric sequence, and it denotes the category of the dish (e.g., appetizer, main course, dessert).
PRICE is a numeric field indicating the cost of the dish.
Execute the following statement: INSERT INTO DISHES (CODE, name, type, price) VALUES ('D0001', 'Italian Spaghetti', 'Main Course', 12.99);
Does it work? If not, identify which integrity rule is not being met and modify the statement to solve the problem.
Create the following table: EMPLOYEES (ID, name, surname, role, restaurant_code)
Note that ID is the primary key. It is a numeric field and must be AUTO_INCREMENT.
Note that the field ‘restaurant_code’ is required. This field needs to have the same type as RESTAURANT (CODE), but you don't have to create the foreign key at this point.
The fields ‘name’ and ‘surname’ are required, and they are alphanumeric sequences.
Two employees can't have the same name and surname.
The role field is required and indicates the employee's job (e.g., chef, waiter, manager).
Modify the schema of the table EMPLOYEES setting restaurant_code as a foreign key. The policy on update must be CASCADE, and the policy on delete must be SET NULL. If this doesn't work, explain what is happening and how to solve it.
Explain the three policies on delete/update that can be configured on a foreign key.
Test and explain the RESTRICT policy on delete. To do this, follow these commands and explain each step:
Modify the schema of the table EMPLOYEES setting restaurant_code as a foreign key. The policy on update must be RESTRICT and the policy on delete must be RESTRICT.
Insert a row in RESTAURANT table (if it doesn't exist).
Insert a row in EMPLOYEES table (if it doesn't exist).
Try to delete the row with the specific CODE from RESTAURANT.
Test the CASCADE policy on delete.
Modify the schema of the table EMPLOYEES with CASCADE policies for update and delete.
Perform similar INSERT and DELETE operations as before and explain the outcome.
Test the SET NULL policy on delete.
Modify the schema of the table EMPLOYEES with SET NULL policies for update and delete.
Perform similar INSERT and DELETE operations as before and explain the outcome.
Create the following table: RESERVATIONS (ID, customer_name, party_size, date, time, employee_id)
ID is a numeric field, the primary key, and is auto-incremental.
employee_id is a foreign key related to EMPLOYEES (ID).
Modify the schema of the table EMPLOYEES, adding a new field named ‘hire_date’, the type of the field will be DATE.
Modify the schema of the table EMPLOYEES, delete the attribute 'role'.
Modify the schema of the table EMPLOYEES, add a new field with the name 'position'. This field can be NULL. This field can just take 4 values: ['Chef' | 'Waiter' | 'Host' | 'Manager'].
Create a user with your name and a safe password and give him all the privileges on the GourmetHub database. Test that your user works successfully.
Creating Roles and Users in MySQL. Learn to create roles with different permissions and assign these roles to specific users in a MySQL database.
Step 1: Creation of Roles.
Create an Administrator role. This role should have permissions to perform all DDL and DML operations on the database.
Use the CREATE ROLE command to create the role.
Assign the appropriate permissions using the GRANT command.
Create the Reader Role. This role should have permissions only to perform read operations (SELECT) on all tables in the database.
Use the CREATE ROLE command to create the role.
Assign the appropriate permissions using the GRANT command.
Step 2: Create a User Associated with the Administrator Role:
Create a user named admin_user.
Assign a secure password.
Associate this user with the administrator role.
Step 3: Create a User Associated with the Reader Role:
Create a user named read_user.
Assign a secure password.
Associate this user with the reader role.