Category: SQL

See attached instructions. I will need and entity relationship diagram created with their corresponding table instance chart for each output specification shown in the Warner health club attachment. Please read instructions carefully.

i, I need this assignment prepared at a distinction level (70%+), so please focus not only on correct answers but also on strong academic explanation and structure. For Assignment 1 (SQL Queries): Include the correct SQL queries. Add screenshots from MySQL Workbench showing execution and results. Provide clear explanations of why each query is written that way. Explain concepts like INNER JOIN, LEFT JOIN, multiple joins, and how they work logically and from a business perspective. For Assignment 2 (DDL & DML): Clearly justify the choice of data types (INT, VARCHAR, etc.). Explain why PRIMARY KEY is used and its importance. Explain NOT NULL constraints and how they ensure data integrity. When performing joins for PAYMENT_DETAILS, explain referential integrity and table relationships. Include screenshots of execution. For Assignment 3 (Normalization Very Important): Start with an introduction explaining normalization and its importance. Create an unnormalized library table first and explain the problems (redundancy, update anomaly, insert anomaly, delete anomaly). Then apply 1NF, 2NF, and 3NF step by step. Show before-and-after tables for each stage. Clearly explain partial dependency and transitive dependency. Include a simple ER diagram or structured relationship diagram. Add critical evaluation on how normalization improves data integrity, reduces redundancy, and enhances query performance. Also mention the trade-off of excessive normalization. Referencing: Use proper Harvard referencing style. Include 58 credible academic references (textbooks or journal articles). Avoid Wikipedia or generic websites. Formatting: Proper headings and academic tone. Logical flow and structured explanation. All screenshots must be clear and readable. Please make sure the work is analytical, not just desrptive, and written in a natural academic style. One ZIP folder containing: 1. Word document 2. SQL script file (.sql) 3. ER diagram image (if separate) 4. Any additional supporting files used

Assignment: Normalization to 3NF

Scenario Overview:

The university library tracks which students borrow which books. Each book may have multiple authors, and each author has a nationality. The system initially stored this information in a single table, but the library realized that this approach led to several data problems. Your task is to help the library by identifying and fixing these problems using the normalization process (1NF, 2NF, and 3NF). Please use screenshot attached.

In the initial table:

• Some columns contain multiple values in a single cell, such as the Authors and AuthorNationality columns. For example:
• Dr. Smith (American) and Dr. Brown (British) are both authors of the book “Database Systems”.
• When reading the Authors and AuthorNationality columns, interpret the values in order: Dr. Smith is American, and Dr. Brown is British.
• The library has decided to use a composite key consisting of StudentID and BookID in their database because a student can borrow multiple books, and each book can be borrowed by multiple students. Together, these two fields uniquely identify each borrowing event.

Step-by-Step Questions and Tasks:

Step 1: Identify 1NF Violation

Question 1: Does the initial table satisfy 1NF (First Normal Form)? Why or why not?

• Hint: Look for columns with multiple values stored in a single cell.

Task 1: Fix the 1NF violation.

Step 2: Identify 2NF Violation

Question 2: Does the new table (after fixing 1NF) satisfy 2NF (Second Normal Form)? Why or why not?

• Hint: In 2NF, every non-key attribute must depend on the entire composite key.

Task 2: Fix the 2NF violation.

Step 3: Identify 3NF Violation

Question 3: Does the new table (after fixing 2NF) satisfy 3NF (Third Normal Form)? Why or why not?

• Hint: In 3NF, there must be no transitive dependencies. Does any non-key column depend on another non-key column?

Task 3: Fix the 3NF violation.

Final Table Format after Normalization:

After completing the normalization process, the database will consist of four tables:

1. Students Table Contains information about each student.
2. Authors Table Contains information about each author and their nationality.
3. Books Table Contains information about each book and its author(s).
4. Borrowing Table Tracks which student borrowed which book and for how long.

Final Questions:

1. Why was it important to fix the 1NF violation?
2. How did you fix the partial dependencies issue that caused the table to violate 2NF?
3. What was the transitive dependency that violated 3NF, and how did you fix it?

Answer the following questions. Make sure you use comments in your SQL file to clarify/show which SQL statement is the answer to which question. The questions are:

1- Write an SQL query to show the CustomerName and Country of Customers.

2- Write an SQL query to show all Countries (no duplicates) customers are coming from, and order them alphabetically.

3- Write an SQL query to show the first name of all employees ordered by their last name in alphabetic order.

4- Write an SQL query to show the detail of all offices.

5- Write an SQL query to show the top five expensive products.

6 – Write an SQL query to show all details of payments, show only five of them.

7- Write an SQL query to show all details of employees.

8- Write an SQL query to show all details of Orders, show the recent ones on the top.

9- Write an SQL query to show product names, and their prices.

10 – Write an SQL query to show all details of orders (from OrderDetails table).