MySQL Workbench 1. Normalization Concepts: Normalization is a process used in relational database design to minimize data redundancy and improve data integrity. It involves organizing the columns and tables in a database to ensure that each table contains only related data and follows specific rules. The most common normal forms are: • First Normal Form (1NF): Each table cell should contain a single value, and there should be no repeating groups. • Second Normal Form (2NF): All non-key attributes should be fully dependent on the primary key. • Third Normal Form (3NF): All non-key attributes should be non-transitively dependent on the primary key. 2. Stored Procedures Concepts: A stored procedure is a precompiled set of SQL statements stored in a database. They can be called by applications, other stored procedures, or triggers to perform specific tasks. Some benefits of stored procedures include: • Code reusability • Improved security • Better performance • Centralized control and easier maintenance 3. User-Defined Functions Concepts: User-defined functions (UDFs) are custom functions created by users in a database. They allow you to encapsulate a series of SQL operations into a single function that can be called with parameters. Some advantages of UDFs include: • Code reusability • Simplified query writing • Enhanced code readability • Easier maintenance and debugging Tables Description: Consider a simplified e-commerce database that contains the following tables: 1. Customers (CustomerID, FirstName, LastName, Email, PhoneNumber, StreetAddress, City, State, ZipCode, Country) 2. Products (ProductID, ProductName, Category, Description, Price, QuantityInStock) 3. Orders (OrderID, CustomerID, OrderDate, TotalAmount) 4. OrderDetails (OrderDetailID, OrderID, ProductID, Quantity, Price) Tasks: 1. MySQL Normalization: Normalize the given tables into 3NF (Third Normal Form). Clearly define the primary keys (PK) and foreign keys (FK) for each table after normalization. 2. MySQL Stored Procedures: Create three stored procedures for the normalized tables. a. Insert a new customer and their order information. b. Update the quantity in stock for a product after an order is placed. c. Retrieve the total sales for a specific product within a specified date range. 3. MySQL Functions: Create two user-defined functions for the normalized tables. a. Calculate the average price of products in a specific category. b. Determine the total number of orders placed by a specific customer

MySQL Workbench

1. Normalization Concepts: Normalization is a process used in relational database design to
minimize data redundancy and improve data integrity. It involves organizing the columns and
tables in a database to ensure that each table contains only related data and follows specific
rules. The most common normal forms are:
• First Normal Form (1NF): Each table cell should contain a single value, and there should be no
repeating groups.
• Second Normal Form (2NF): All non-key attributes should be fully dependent on the primary
key.
• Third Normal Form (3NF): All non-key attributes should be non-transitively dependent on the
primary key.
2. Stored Procedures Concepts: A stored procedure is a precompiled set of SQL statements stored
in a database. They can be called by applications, other stored procedures, or triggers to
perform specific tasks. Some benefits of stored procedures include:
• Code reusability
• Improved security
• Better performance
• Centralized control and easier maintenance
3. User-Defined Functions Concepts: User-defined functions (UDFs) are custom functions created
by users in a database. They allow you to encapsulate a series of SQL operations into a single
function that can be called with parameters. Some advantages of UDFs include:
• Code reusability
• Simplified query writing
• Enhanced code readability
• Easier maintenance and debugging