Lab 7: Working With Views Overview

Lab7 Working With Viewslab Overviewlab 7 Will Introduce The Concept O

Lab 7 will introduce the concept of database views. It involves working with a local MySQL database using the OM database tables created from provided scripts and ERD. Specific tasks include modifying the customer table to add auto-increment to the primary key, creating views for filtered customer data, demonstrating updateability of views, creating views for shipping time reports, total sales per artist, and total sales per customer, and performing various queries to display, sort, and aggregate data.

Paper For Above instruction

The exploration of database views is fundamental in understanding how to efficiently manage, query, and manipulate data within relational databases. This lab provides hands-on experience with creating, modifying, and utilizing views in MySQL, particularly through practical exercises that simulate real-world database administrative and reporting scenarios. By examining the tasks involved, students will deepen their comprehension of view creation, updating through views, and leveraging views for complex queries involving joins and aggregates.

1. Modifying the Customers Table:

The first step involves altering the 'customers' table to assign the primary key field as an auto-increment type. Due to referential integrity constraints, direct alteration can lead to errors if other tables, like 'orders,' depend on the primary key. To resolve this, the process includes locking the table, disabling foreign key checks, performing the alteration, and then re-enabling constraints. The sequence ensures data integrity is preserved during the modification.

Executing the following SQL commands accomplishes this safely:

LOCK TABLES customers WRITE;


SET foreign_key_checks = 0;


ALTER TABLE customers MODIFY customer_id INT NOT NULL AUTO_INCREMENT;


SET foreign_key_checks = 1;


UNLOCK TABLES;

Following this, two sample insert statements demonstrate the table's integrity and auto-increment functionality, ensuring the key assignment works as intended. Confirming the new customer IDs using a SELECT statement verifies the successful configuration.

2. Creating and Testing a State-specific View:

The next task is to craft a view named 'CA_CUSTOMERS' that filters the 'customers' table to include only those residing in California. After creating this view, executing a SELECT query displays only California residents’ data, validating its filtering capability.

To demonstrate view updateability, an UPDATE statement modifies a specific customer’s last name (Karina Lacy to Lacie). Subsequently, re-querying the 'customers' table confirms that the update has propagated correctly through the view, illustrating that views can be used for data modification when properly configured.

3. Shipping Time View and Queries:

Creating the 'SHIPPING_TIME' view involves selecting customer first and last names, order dates, shipped dates, and calculating the number of days between these dates using the DATEDIFF function. This view simplifies reporting on order fulfillment durations.

Using this view, students will perform sorted queries to display the data ordered by the number of days taken to ship (descending). Additional filters include showing only orders that shipped in fewer than 10 days or more than 30 days, illustrating how views can facilitate targeted analysis without altering the underlying schema.

4. Total Sales by Artist Using Views:

To analyze sales by artist, a 'SalesData' view is constructed, displaying order IDs, item IDs, the total amount for each item (quantity times price), along with title and artist ID. Sorting this view by artist ID helps visualize how data can be grouped.

An aggregate query then calculates total sales per artist using SUM, enabling report generation. Joining this with the 'artist' table retrieves artist names alongside total sales, illustrating how views serve as effective tools for complex joins and summaries.

5. Total Sales per Customer:

Similarly, creating a 'SalesData' view capturing customer ID and item total (excluding the customer table directly), students can then generate total sales figures per customer using aggregate functions. By joining the view with the 'customer' table, the report displays customer names as a single combined field, with total sales ordered in descending order.

This process demonstrates how views streamline grouping and aggregation tasks, making complex customer sales analysis more manageable.

References

  • Folk, M., & Codd, E. H. (1970). A relational model of data for large shared data banks. Communications of the ACM, 13(6), 377-387.

Related Assignments