Unit 4

Lesson 9 Summary: Database Systems Fundamentals

Learning Outcomes:

1. Implement different types of joins in SQL.
2. Create and manage SQL views.
3. Understand and use SQL transactions.
4. Implement integrity constraints to maintain data consistency.
5. Implement indexes in SQL.
6. Define and use SQL data types and schemas.

---

Key Concepts:

Joins in SQL

• Natural Join: Combines two tables by matching columns with the same name. It removes duplicates and includes only consistent rows.
  • Example:

    SELECT Orders.OrderID, Customers.CustomerName 
    FROM Orders 
    INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
    

• Inner Join: Returns only rows with matching data in both tables, and excludes non-matching rows.
  • Example:

    SELECT * 
    FROM Orders 
    INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
    

• Outer Joins: Returns rows with matching data and includes rows with unmatched data from one or both tables.
  • LEFT JOIN: Includes unmatched rows from the left table, along with matching rows.
  • RIGHT JOIN: Includes unmatched rows from the right table, along with matching rows.
  • FULL OUTER JOIN: Includes unmatched rows from both tables, along with matching rows.

SQL Views

• A view is a virtual table that stores the result of an SQL query. It doesn’t store data itself, but fetches data dynamically from the source tables each time it is accessed.
• Materialized Views: A type of view that stores data physically and is updated when the underlying data changes. This can improve performance for complex queries.

Integrity Constraints

• Entity Integrity: Ensures that each row has a unique primary key and no duplicate rows exist.
• Domain Constraints: Limits the values a column can hold, such as setting a column to only accept integers or certain ranges.
• Referential Integrity: Ensures that foreign keys correctly point to valid primary keys in related tables, maintaining consistency across tables.
• Key Constraints: Defines the rules for primary keys (uniqueness) and foreign keys (relationships between tables).

SQL Data Types and Schemas

• Date and Time Types: Used for storing date and time information (e.g., DATE, TIME).
• Type Conversion: You can convert between data types using functions like CAST().
  • Example:

    SELECT CAST(ID AS numeric(5)) FROM instructor;
    

• Default Values: You can set default values for columns using the DEFAULT keyword.
  • Example:

    totcred numeric(3,0) DEFAULT 0;
    

• Large Object Types: Used to store large data like images, audio, or videos (e.g., BLOB, CLOB).
• User-Defined Types: Allows the creation of custom data types, such as defining a Dollars type for monetary values.
• Auto Increment: Automatically generates unique values for a column (e.g., SERIAL in PostgreSQL).

Create Table Extensions

• You can create a new table that has the same schema as an existing table using the LIKE keyword.
  • Example:

    CREATE TABLE temp_instructor (LIKE instructor INCLUDING ALL);
    

Lesson 10: Database Systems Fundamentals

1. Schemas, Catalogs, and Environments

• Hierarchical Organization: Databases have evolved from flat namespaces to hierarchical structures with catalogs, schemas, and relations.
• User Connectivity: Users connect to a database using unique credentials (username and password), with a default catalog and schema.
• Three-Part Naming Convention: Relations are identified by a catalog, schema, and relation name (e.g., EHRDB.public.patient).
• Independence and Clashes: Multiple catalogs and schemas enable users and applications to work independently without naming conflicts.
• Schema Management: Schemas can be created or dropped using SQL. Catalog management is system-specific.

2. Index Definition in SQL

• Purpose of Indexes: Indexes allow efficient data retrieval without full table scans.
• Creating an Index:

    CREATE INDEX deptindex ON instructor (deptname);
  

• Unique Index: Declare an index as unique if the search key is a candidate key:

    CREATE UNIQUE INDEX deptindex ON instructor (deptname);
  

• Dropping an Index:

    DROP INDEX index_name;
  

3. Authorization

• Types of Authorization: Authorizations include privileges for reading, inserting, updating, and deleting data.
• Granting Privileges: Use GRANT to give privileges on relations:

    GRANT SELECT ON department TO user;
  

• Revoking Privileges: Withdraw privileges using the REVOKE command. Prevent cascading revocation:

    REVOKE SELECT ON department FROM U2, U5 RESTRICT;
  

• Role-based Authorization: Create roles and assign privileges to them, which can be granted to users.
• Views and Schemas: A user who creates a view may not have all privileges on it. Schema owners can modify schemas.

4. Accessing SQL from a Programming Language

• Dynamic SQL: Programs can construct SQL queries dynamically at runtime and interact with the database using functions or methods. Common standards include:
  • JDBC (Java)
  • ODBC (Other languages)
• Embedded SQL: SQL statements are embedded within the source code and processed at compile time. Preprocessors translate SQL into function calls.

5. Next Week’s Topics

• 4.9 Functions and Procedures
• 4.10 Triggers
• 4.11 Recursive Queries
• 4.12 Advanced Aggregation Features

6. Weekend Task

• Install pgAdmin4 for the next class.

Lesson 11: Functions and Procedures in SQL

• Functions and procedures are blocks of code used to perform specific tasks in a database. They define the business logic and can be stored in the database.

• Functions:

  • Defined with CREATE FUNCTION.

  • Return a single value.

  • Can be executed within a SELECT statement.

  • Cannot have output parameters.

• Procedures:

  • Defined with CREATE PROCEDURE.

  • Can perform multiple actions.

  • Must be explicitly called using EXECUTE.

  • Can have output parameters.

2. Example of Function:

• get_patient_appointments: This function retrieves patient appointments for a given patient ID.

• CREATE OR REPLACE FUNCTION get_patient_appointments(p_patient_id INT)RETURNS TABLE (appointment_id INT, appointment_date DATE, appointment_time TIME, purpose VARCHAR(255)) AS \(BEGIN RETURN QUERY SELECT a.AppointmentID, a.AppointmentDate, a.AppointmentTime, a.Purpose FROM Appointment a WHERE a.PatientID = p\_patient\_id ORDER BY a.AppointmentDate, a.AppointmentTime;END;\) LANGUAGE plpgsql;

3. Example of Procedure:

• schedule_appointment: This procedure schedules a new appointment, checking doctor availability and inserting appointment data.

• CREATE OR REPLACE PROCEDURE schedule_appointment( p_patient_id INT, p_doctor_id INT, p_appointment_date DATE, p_appointment_time TIME, p_purpose VARCHAR(255))LANGUAGE plpgsql AS \(BEGIN -- Check doctor's availability SELECT COUNT(\*) = 0 INTO doctor\_availability FROM Appointment WHERE DoctorID = p\_doctor\_id AND AppointmentDate = p\_appointment\_date AND AppointmentTime = p\_appointment\_time; IF NOT doctor\_availability THEN RAISE EXCEPTION 'Doctor is not available at the requested time'; END IF; -- Schedule appointment INSERT INTO Appointment (PatientID, DoctorID, AppointmentDate, AppointmentTime, Purpose) VALUES (p\_patient\_id, p\_doctor\_id, p\_appointment\_date, p\_appointment\_time, p\_purpose);END;\);

4. Language Constructs for Procedures and Functions

• SQL uses the Persistent Storage Module (PSM), allowing constructs like loops, conditionals, and variable declarations to enhance the logic of functions and procedures.

5. External Language Routines

• Some databases support external programming languages (e.g., Java, Python) within the SQL environment, but care must be taken to avoid security issues or corruption.

6. Triggers in SQL

• Triggers are automatic actions that occur as a result of a modification to the database.

• Used for enforcing integrity constraints, alerting users, or initiating tasks (e.g., updating related data or notifying users).

• Example: notify_new_prescription trigger, which issues an alert whenever a new prescription is added.

7. Example of Trigger:

• CREATE OR REPLACE FUNCTION notify_new_prescription()RETURNS TRIGGER AS \(BEGIN RAISE NOTICE 'New prescription added: % for patient % (Medication: %)', NEW.PrescriptionID, NEW.PatientID, NEW.MedicationName; RETURN NEW;END;\) LANGUAGE plpgsql;

8. When Not to Use Triggers

• Triggers should be avoided when they might cause unintended actions during replication, lead to infinite loops, or conflict with other database operations.

Lesson 12 Summary: Recursion & Advanced Aggregation in SQL

Learning Outcomes:

1. Understand and write recursive queries in SQL.

2. Use advanced aggregation features in SQL.

Recursion in SQL:

• Definition: A recursive SQL Common Table Expression (CTE) is a query that references its own output until a termination condition is met. It’s ideal for querying hierarchical data.

• WITH R AS (SELECT 1 AS n)SELECT n + 1 FROM R

  • R is the recursive CTE, starting with a base query (SELECT 1 AS n) and referencing itself in subsequent operations.

• Transitive Closure:

  • If (a1, a2) ∈ R and (a2, a3) ∈ R, then (a1, a3) ∈ R.

  • Recursive queries are useful for expressing transitive closure.

• Structure of Recursive Queries:

  • Must include:

    1. A base case (non-recursive query).

    2. A recursive case (refers to the CTE itself).

  • Defined using WITH RECURSIVE.

Restrictions on Recursive Queries:

• Recursive queries must be monotonic.

• Cannot use:

  • Aggregation on the recursive view.

  • NOT EXISTS subqueries on the recursive view.

  • EXCEPT where the recursive view is on the right side.

Advanced Aggregation:

• Mentioned as part of the lesson but left for an exercise.

Next Class: Unit V - Relational Database Design

Topics will include:

• Good relational design principles

• Functional dependencies

• Normal forms (1NF, 2NF, 3NF, BCNF, etc.)

• Decomposition algorithms

• Temporal data modeling

  Sure! Here’s a concise and polished conclusion with key takeaways you can add to the end of your post:

Conclusion

Unit 4 offered a deep dive into the core functionalities of SQL that are crucial for designing, managing, and optimizing relational databases. From implementing joins and integrity constraints to exploring dynamic SQL, functions, procedures, and recursion—each lesson built on foundational knowledge to prepare for more complex design principles in the next unit.

Key Takeaways:

• Joins are essential for combining data across multiple tables, with inner and outer joins offering different perspectives on matched and unmatched records.

• Views and materialized views help simplify complex queries and enhance performance.

• Integrity constraints ensure data validity and consistency, forming the backbone of reliable databases.

• Indexing improves data retrieval speeds, and understanding authorization is vital for database security.

• Dynamic and embedded SQL allow integration with programming languages, enabling powerful applications.

• Functions and procedures encapsulate logic for reusability and clarity, while triggers automate database behavior in response to changes.

• Recursive queries are a powerful tool for handling hierarchical or transitive relationships in data.

• Advanced aggregation lays the groundwork for insightful analytics and reporting.

With this solid understanding, you’re now ready to explore Relational Database Design in Unit 5, where you’ll learn how to build efficient, normalized, and scalable database schemas.