U

Lecture 4B

SQL and Data Querying

Overview of Utility Company Database

  • A utility company maintains a database for managing service calls.

  • The call center processes "tickets" that track jobs based on reported issues.

Customer Scenarios

  • Scenario 1: Customer (ID 001234) queries about a faulty boiler repair.

  • Scenario 2: A worker inquires about their next job assignment.

Database Design

  • Tables for Database:

    • Customer: custId, name, phone, address

    • Worker: workId, name, phone

    • Ticket: ticketId, timeReported, problem, status, custId, workId

SQL Queries

  • Identify Worker Handling Customer Complaint:

    SELECT workId, name FROM Worker, Ticket  
WHERE (Worker.workId = Ticket.workId)  
AND (custId = 001234);

  • Find Customer Address with Open Ticket:

    SELECT address FROM Ticket, Customer  
WHERE Ticket.custId = Customer.custId  
AND status = 'open'  
ORDER BY timeReported;

Referential Integrity and Database Normalisation

Referential Integrity

  • Ensures consistency between related tables in a relational database.

  • A foreign key in one table must reference a primary key in another table.

Normalisation vs Denormalisation

  • Normalisation: Data stored in multiple related tables to reduce redundancy.

  • Denormalisation: Consolidates data into fewer tables for performance optimization.

  • Example scenario governing the decision to denormalise based on organization’s activities.

Challenges with Large Databases

Common Issues

  • Managing multiple users and frequently accessed data across large R-tables.

  • Potential performance bottlenecks if tables are stored in various locations.

  • Denormalisation Advantages:

    • Optimizes query performance by allowing data redundancy based on real usage patterns.

Data Warehouse Concepts

Overview of Data Warehouse

  • Stores consolidated, denormalized historical data for analytical purposes.

  • Transaction data only loaded after activities are complete.

Data Mining

  • Process of analyzing large datasets to identify patterns and relationships.

  • Examples of queries:

    • Sales volume analysis during product promotions.

    • Correlation between products bought together.

Application of Data Mining

Domains Utilized

  • Banking: Fraud detection and customer loyalty analysis.

  • Healthcare: Hospital visit predictions based on past behaviors.

  • Supply Chain Management: Supplier reliability assessment.

Key Data Mining Applications

  • Consumer Clustering: Identifying characteristics of similar customers.

  • Churn Prediction: Detecting reasons customers switch to competitors.

  • Fraud Detection: Establishing parameters for likely fraudulent transactions.

Web Concepts

Surface Web vs Deep Web

  • Surface Web: Indexable and visible through search engines.

  • Deep Web: Not indexed; includes protected or uncrawlable content.

  • The deep web is estimated to contain 96% of the internet’s content, including databases and organizational repositories.

Dynamic Web Pages

  • Generated based on user interactions and access times, leading to different results for the same search at different times.

Client/Server Architecture

  • Highlights the interaction between client, server, and database through web applications.

Takeaway Messages

  • SQL is essential for data projection, joins, and restrictions.

  • Data warehouses facilitate historical data management and decision-making.

  • Data mining discovers hidden relationships and patterns in datasets.

  • Dynamic web pages rely on databases to tailor content to user needs.