Module 4 - Many to Many Relationships and SQL

Page 1: Course Introduction

• Course Code: MIST 7510

• Title: Database Management

• Instructor: Nikhil Srinivasan

• Affiliation: Terry College of Business, The University of Georgia

Page 2: Many-to-Many Relationships

• Acknowledgment of complexities in database relationships.

• Quote by Daniel Webster emphasizes the notorious complications that can arise in many-to-many concepts.

Page 3: Sample Sales Data

• Data structure (example of a sales transaction):

  • Item details include: Item Code, Description, Quantity, Unit Price, and Total.

  • Example items listed (POCKY, RICE CAKES, etc.)

  • Important to note discounts and price features.

• References to transaction totals, including subtotal and tax calculations.

Page 4: Sales Form Structure

• Outline of a sales transaction form.

• Elements include:

  • Sale Number

  • Date of Sale

  • Item Number

  • Description, Quantity, Unit Price, and Total Price fields.

Page 5: Creating Associations in Databases

• Explanation of many-to-many relationships:

  • Implementation via a third entity called an associative entity.

  • Example of identifying relationships using 'LINEITEM' in conjunction with 'SALE'.

  • Classification of LINEITEM as a weak entity that relies on sale and line number.

Page 6: Preference Settings in MySQL Workbench

• Discussion of naming conventions for foreign keys and associative tables.

• Suggestions for setting relationship defaults such as:

  • Foreign Key Names

  • Column Names and Types

  • Defaults on updates/deletes.

Page 7: Many-to-Many Relationships Visualization

• Visual representation of database relationships in MySQL Workbench:

  • Indexes for different relationship types (1:1, 1:m, m:m).

  • Symbols used to denote different relationship types.

Page 8: Attributes of Line Items

• Breakdown of line items in many-to-many relationships:

  • Attributes include: item number, sale number, line item quantity, etc.

  • Importance of indexing in relationships to enhance performance.

Page 9: Necessity of a Third Entity

• Rationale for using a third entity:

  • Allows for detailed data storage regarding the relationships between two entities.

  • Each many-to-many relation can be conceptualized as two one-to-many relationships.

Page 10: Associative Table in Relational Databases

• Guidelines for creating relational databases in respect to many-to-many relationships:

  • Associative tables must reference foreign keys for all related entities.

  • Example line item attributes illustrate real data application.

Page 11: SQL for Creating Tables

• SQL structure for creating tables:

  • Sale, Item, and LineItem table structures shown.

  • Foreign keys established to ensure data integrity.

Page 12: Exercise - Database Design

• Task to create a model for tracking Olympic field hockey medalists:

  • Steps include data modeling and database creation, followed by data population.

  • Wikipedia as a data source.

Page 13: SQL Joins

• Introduction to joining tables in SQL:

  • Syntax for joining sales, line items, and items.

  • Recursive relationship example through associative table implementation.

Page 14: Example Query

• Listing of sold items with quantity and total value on a specific date:

  • SQL requirements: join table statements to trace specific sales group's data.

Page 15: EXISTS Clause in SQL

• Explanation of the EXISTS operator in SQL:

  • Returns true if at least one row fulfills criteria.

  • Example query demonstrates its usage to fetch item details with sales records.

Page 16: Extended Example Data

• Detailed view of line item sales records with multiple attributes noted:

  • Entity structure showing combinations of line numbers, quantities, prices, and sales.

Page 17: NOT EXISTS Clause in SQL

• Introduction of NOT EXISTS as an SQL operator:

  • How it functions to exclude certain records from results.

  • Query example showcasing non-sold item retrieval.

Page 18: Data Structure Summary

• Recap of the sales data and item details once again...

  • Organized tabular representation for clarity.

Page 19: Additional Exercises

• Practice problems focusing on data retrieval:

  • Reporting on items based on specific conditions.

  • Real-world relevance of query structures demonstrated.

Page 20: Set Operations in SQL

• Two key set operations introduced:

  • UNION: combining results from different queries.

  • INTERSECT: finding commonalities

Page 21: UNION Example Query

• Demonstrates usage of UNION operator to compile lists from different queries:

  • Illustrated examples of sold items and color criteria.

Page 22: INTERSECT Example Query

• Explanation of INTERSECT operation's constraints and applications:

  • Identifies items satisfying both sale date and color conditions.

Page 23: Conclusion

• Concluding notes on many-to-many relationships, associative entities, and SQL operational practices:

  • Emphasis on the importance of relational modeling and query proficiency in database management.