MySQL/MariaDB SQL TASK
€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.
Ταυτότητα Εργασίας: #37514410
Σχετικά με την εργασία
27 freelancers κάνουν προσφορές κατά μέσο όρο €28 για αυτή τη δουλειά
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.
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 Περισσότερα
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 Περισσότερα
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 Περισσότερα
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.
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 Περισσότερα
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 Περισσότερα
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 Περισσότερα
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 Περισσότερα
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
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 Περισσότερα
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 Περισσότερα
✅ 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 Περισσότερα
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!
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
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 Περισσότερα
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 Περισσότερα
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 Περισσότερα
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.
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 Περισσότερα
