Module 3 - Multi Entity Modeling and SQL
Page 1: Introduction
Course Title: MIST 7510E - Database Management
Instructor: Nikhil Srinivasan
Institution: The University of Georgia, Terry College of Business
Page 2: One-to-Many Relationships
Entities: Related to other entities
1:m Relationship: Read as: ‘a nation can have many stocks, but a stock belongs to only one nation’
Page 3: Hierarchical Relationships
Often occur due to multiple 1:m relationships
Example Hierarchy: FIRM > DIVISION > DEPARTMENT > SECTION
Page 4: STOCK Table Structure
Table Structure:
| Column Name | Data Type | Example Data |
| --- | --- | --- |
| stkcode | CHAR(3) | FC |
| stkfirm | VARCHAR(20) | Freedonia Copper |
| stkprice | DECIMAL(6,2) | 27.50 |
| stkqty | DECIMAL(8) | 10529 |
| stkdiv | DECIMAL(5,2) | 1.84 |
| stkpe | DECIMAL(5) | 16 |
| natname | VARCHAR(20) | United Kingdom |
| exchrate | DECIMAL(9,5) | 1.00 |
Note on Redundancy: natname and exchrate show redundancy in data.
Page 5: Data Anomalies and Solutions
Insert Anomaly: Cannot insert a nation's exchange rate without a stock entry.
Delete Anomaly: Deleting stock data may lead to loss of exchange rate information.
Update Anomaly: Higher likelihood of error and more updates required.
Separate Table: Helps to resolve the above anomalies.
Page 6: Mapping Entities to Relational Database
Strategy: Each entity becomes a separate table.
Entity Name: Becomes the table name.
Attributes: Become columns in the table.
1:m Relationship: Add a column at the many end to hold the identifier from the one end.
Page 7: NATION and STOCK Tables
NATION Table Structure:
| Column Name | Data Type |
| --- | --- |
| natcode | CHAR(3) |
| natname | VARCHAR(20) |
| exchrate | DECIMAL(9,5) |
STOCK Table Structure: (including natcode)
natcode in both tables: Reflects the relationship between nations and stocks.
Page 8: Foreign Keys
Definition: A foreign key is a column in one table that must match the primary key of another table.
Example: natcode in stock refers to natcode in nation, establishing a 1:m relationship.
Page 9: Referential Integrity Constraint
Definition: Ensures that for every foreign key value, there is a corresponding primary key.
Example: natcode in stock must correspond to a valid natcode in nation.
Page 10: Creating Tables (NATION)
SQL Query to Create NATION Table:
CREATE TABLE nation (
natcode CHAR(3),
natname VARCHAR(20),
exchrate DECIMAL(9,5),
PRIMARY KEY (natcode)
);Page 11: Creating Tables (STOCK)
SQL Query to Create STOCK Table:
CREATE TABLE stock (
stkcode CHAR(3),
stkfirm VARCHAR(20),
stkprice DECIMAL(6,2),
stkqty DECIMAL(8),
stkdiv DECIMAL(5,2),
stkpe DECIMAL(5),
natcode CHAR(3),
PRIMARY KEY(stkcode),
CONSTRAINT fk_has_nation FOREIGN KEY(natcode) REFERENCES nation(natcode) ON DELETE RESTRICT
);Page 12: Representing a 1:m Relationship in MySQL Workbench
Overview of relational structure showing relationships within the database.
Page 13: Exercise Example
Develop a data model for tracking a distance runner’s times over various lengths with a sample database.
Page 14: SQL Join Syntax
JOIN Query: Create a new table by matching records from two existing tables.
SELECT * FROM stock JOIN nation ON stock.natcode = nation.natcode;Page 15: Stock Holding Values in UK Pounds
JOIN Query Example: To report values based on stock holdings in UK.
Shape SQL queries to display value calculations and sort results by nation and firm.
Page 16: Practice Exercise 1
Query examples for Faculty data, focusing on departments and faculty names.
Page 17: Practice Exercise 1.1
Queries based on ClassicModels database focusing on employee orders and customer country data.
Page 18: GROUP BY Query Example
Total Value of Stockholdings: Summarize stock values by nation.
SELECT natname, SUM(stkprice*stkqty*exchrate) AS stkvalue FROM stock JOIN nation ON stock.natcode = nation.natcode GROUP BY natname;Page 19: HAVING Clause Use in SQL
Total Value of Stocks for Nations: Only include nations with stocks count greater than two.
SELECT natname, SUM(stkprice*stkqty*exchrate) AS stkvalue FROM stock JOIN nation ON stock.natcode = nation.natcode GROUP BY natname HAVING COUNT(*) >= 2;Page 20: Exercise Focus
Objective: Report total dividend payments for each country with three or more stocks.
Page 21: Practice Exercise 2
Query Development: Count faculty members per department and display respective details.
Page 22: Practice Exercise 3
Limitations in Data Display: Departments with two or fewer faculty.
Page 23: Practice Exercise 1.2
Queries: Employee and order relationship, customer checks, lists per country.
Page 24: Practice Exercise 1.3
Queries based on employee checks and customer transactions in the ClassicModels database.
Page 25: Structure of SQL Statements
Basic SQL Structure:
SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY.
Page 26: Regular Expressions - Basic Concepts
Example: Identifying strings not containing specified characters.
SELECT * FROM nation WHERE natname REGEXP '[^a-z|A-Z]';Page 27: Regular Expressions - Filtering Results
Nations without specified characters: Listing names without 's' or 'S'.
Page 28: Regular Expressions - Repetitions
Quantification: Listing names of firms with repeating characters.
Page 29: Regular Expressions - Alternation
Using alternation to filter results based on multiple conditions.
Page 30: Regular Expressions - Set Matching
Pattern Matching: defining sets for SQL queries.
Page 31: Regular Expressions - Specific Position Matching
Substring matching based on position in a string.
Page 32: Regular Expression Resources
Regex Resources: Libraries and tools for regular expressions.
Page 33: Exercise Development
Reporting on nations starting with 'United'.
Page 34: Practice Exercise 1.4
Queries focused on product descriptions within databases.
Page 35: Subqueries - Overview
Definition: A nested query providing additional data filtering.
SELECT stkfirm FROM stock WHERE natcode IN (SELECT natcode FROM nation WHERE natname = 'Australia');Page 36: Correlated Subquery - Definition
Functionality: Executes inner queries multiple times, impacting performance.
Page 37: Correlated Subquery Example
Query analyzing stock quantities against averages for respective countries.
Page 38: Subquery vs Correlated Subquery
Execution Comparison: Different execution frequencies for subqueries.
Page 39: Nested Queries - Example Comparisons
Query Structures: Displaying prep times using nested queries.
Page 40: Additional Exercises
Reporting maximum stock holdings per country.
Page 41: Class Exercise 4
Applying queries to recipe databases for data extraction.
Page 42: Practice Exercise 4
Elaboration on prep times for recipes from specific sources.
Page 43: Recipe Source Table
Sample data structure for managing recipe sources.
Page 44: Recipe Table Structure
Further details on recipe attributes and foreign keys.
Page 45: Practice Exercise 1.5
Queries centering on customer payment information regarding check transactions.
Page 46: Views - Conceptual Overview
Definition: Virtual tables used for simplifying complex queries.
CREATE VIEW stkvalue AS SELECT...;Page 47: Querying Views
Example of querying created views as if they were tables.
Page 48: Benefits of Views
Simplification of query writing and access control.
Page 49: Exercise Development
Creating views for dividend data.
Page 50: Summary - New Topics Covered
1:m relationship, Foreign key, Correlated subquery, GROUP BY, HAVING clause, and Views.
Page 51: Data Modelling Exercise
Database design proposal for artists and museum painting records.
Page 52: Additional SQL Exercise
Further queries based on ClassicModels database.