MySQL/MariaDB SQL TASK

En curso Publicado hace 5 meses Pagado a la entrega
En curso Pagado a la entrega

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.

Desarrollo de base de datos Administración de bases de datos Programación de bases de datos SQL MySQL

Nº del proyecto: #37514410

Sobre el proyecto

27 propuestas Proyecto remoto Activo hace 5 meses

27 freelancers están ofertando un promedio de €28 por este trabajo

OWINOKAMAU

Hello I am a skilled SQL programmer and can help with MySQL and Maria DB SQL tasks. I will ensure that I do all SQL queries accurately and correctly.

€30 EUR en 2 días
(35 comentarios)
5.8
techsolsoftwares

Hi there! My name is Atif and I am a WEB DEVELOPER working since 2007. With my skills, I develop competitive advantages and create technological solutions. I understand that you are searching for someone to write SQL Más

€30 EUR en 1 día
(39 comentarios)
5.6
talhamq

Hello, My name is Talha and I am a professional with extensive experience in database administration, MySQL and SQL. I understand that you are looking for someone to create relational databases using MySQL/MariaDB SQL Más

€40 EUR en 1 día
(101 comentarios)
5.5
vincentgrerry

Hi there.. I'm a personal freelancer and interested help you to answer your SQL task assignment related database design and implementation ERD.. also providing some SQL for sample implementation database.. according w Más

€30 EUR en 2 días
(33 comentarios)
5.0
niarfedevelop

Hola, saludos cordiales, en la actualidad cuento con más de 15 años de experiencia trabajando como desarrollador de software, experto en este tipo de proyectos, soy la persona ideal. EXPERTO EN BASES DE DATOS.

€24 EUR en 7 días
(37 comentarios)
5.2
fugus

Hello, I am a database expert with over 15 years. My expertise include database design, development and administration. I can create the database that you want within a few hrs. Hope to discuss more over the chat. Than Más

€30 EUR en 1 día
(28 comentarios)
4.1
aleksisgsr

Hello, I'm a SQL developer with more than 10 years of experience. Over 5 years I worked as senior SQL developer in various companies. I design databases, schemas, indexes, constraints etc. I develop complex SQL quer Más

€30 EUR en 7 días
(4 comentarios)
3.7
hemsingh1

Hello, SQL, Excel expert here. I can perfectly assist you with this and can start on an immediate basis. I am an experienced Excel professional with years of experience in all the tools of Excel including Visual Basi Más

€40 EUR en 1 día
(22 comentarios)
4.2
sutharRajesh

Hello there! My name is Rajesh and I am a Full Stack Developer and Solution Architect with 3+ years of experience in the industry. I have a strong background in Python, PHP, Java, JavaScript and various web development Más

€50 EUR en 3 días
(3 comentarios)
3.0
OleksandrFilat

Hello there, I am MySQL & MariaDB expert and I am very interested in your project. Looking forward to working with you. Let's discuss more detail. Thank you, Oleksandr

€60 EUR en 7 días
(3 comentarios)
2.8
mr310

Hello, I'm an IT expert with more than 20 years of experience in the IT industry . I'm Cisco Certified networking professional 300-100 and 300-115 and Linux Certified Professional lpi 101, 102,103 and red hat certified Más

€30 EUR en 7 días
(1 comentario)
1.0
HUZHVA

Hello, How are you? I am very interested with your task now. I have checked your proposal carefully. I understand your requirement well. I have full experienced with MySQL I can complete your task perfectly in time. Pl Más

€20 EUR en 2 días
(1 comentario)
0.3
kimphuong589

✅ MySQL Expert Here! ✅ Hi, How are u? I am very interested with your task now. I have checked your proposal carefully. I understand your requirement well. I can complete your task perfectly in time. Please contact me a Más

€30 EUR en 1 día
(0 comentarios)
0.0
angelalfonzo

Hi Dear! I understood your work and I am creating your database "GourmetHub" and tables "RESTAURANT ", "DISHES ",..."Roles ". Please message me for any question to discuss in details. Best regards!

€20 EUR en 1 día
(0 comentarios)
0.0
asadtanveer507

Hi there, i am MySQL expert I can do all these SQL statements and also help to create relational schema and ER Diagram for a complete report so it will easy to understand within few hours

€30 EUR en 1 día
(1 comentario)
0.0
BBestid

Hello Client. How are you? With over 8 years of full-stack development experience, I'm confident in handling your SQL project seamlessly. I've successfully executed similar database tasks with precision. My hourly ra Más

€30 EUR en 1 día
(0 comentarios)
0.0
azcarlo

Hello, and thank you for considering me for this project. As you can see from my profile, I have extensive experience in writing SQL statements (MySQL/MariaDB SQL) to create relational databases and create the tables r Más

€19 EUR en 7 días
(0 comentarios)
0.0
Joshmoz16

Hi, I can create your SQL relational database at an affordable price and deliver it within a couple of days. I noticed that you are from Spain, so we could discuss more about your project in Spanish. I'll be waiting fo Más

€24 EUR en 3 días
(0 comentarios)
0.0
villasanticiroig

Soy desarrollador SQL con 4 años de experiencia, puedo realizar lo requerido sin dificultades dado que está todo bien detallado y el alcance de lo pedido es claro.

€16 EUR en 2 días
(0 comentarios)
0.0
ITtechsol

Dear Client, When it comes to your project, I am expert. I will provide you with 100% satisfaction with work and full service. It would be great if you hire me. Based on your feedback, I can draft my proposal accur Más

€19 EUR en 1 día
(0 comentarios)
0.0