COMP 251 Quiz 2 ChatGPT

Functionality of Table Relationships

Allows drawing data from multiple tables simultaneously and ensures referential integrity.

Advanced Joins - Outer Joins

Allows the inclusion of rows that have no corresponding rows in the related table.

One-to-Many Relationship (1-M)

A single record in Table A relates to multiple records in Table B.

One-to-One Relationship (1-1)

A single record in Table A relates to only one in Table B, and vice versa.

SQL Syntax for Joins - Select Statement

Defines which columns from different tables to be selected.

Natural Join

Eliminates multiple occurrences of the same column, typically used when joined tables have common columns.

Relationship Level Integrity

Ensures sound connections between tables and appropriate limits on interrelated records.

Outer Join

Includes rows without related rows in the joined table.

Inner Join

Joins tables based on a common key.

Many-to-Many Relationship (M-M)

Multiple records in Table A relate to multiple in Table B.

Self Join

Refers to the same table more than once in a single select for internal comparison or relation.

Self-Referencing Relationships

Relationships within the same table, can be 1-1, 1-M, or M-M.

SQL Syntax for Joins - Where Clause

Serves as the condition for how the tables are joined.

Purpose of Table Relationships

To associate records from one table to another and establish connections between two logically related tables.

Purpose of ER Diagrams

To illustrate relationships between tables in a database and assist in their identification and establishment.

Function of Table Joins in SQL

To join tables in real-time within an SQL SELECT statement for data retrieval.

Establishing M-M Relationships

Use Linking Tables with a Composite Primary Key (CPK).

Establishing 1-1 and 1-M Relationships

Use Primary Key (PK) and Foreign Key (FK).

Advanced Joins - Self Joins

Used for querying within the same table based on specific conditions.