Relational Database Model: Keys, Integrity, and Set Operations
What is an entity set?
A collection of similar types of entities in a relational database.
What is an index in a relational database?
A database structure that improves the speed of data retrieval operations.
What is a system catalog?
A detailed system data dictionary that describes all objects within the database.
What is a composite key?
A key composed of more than one attribute.
What is a secondary key?
A key used strictly for data retrieval purposes.
What is the first step in good database design?
Identifying entities, attributes, and relationships.
What is an equijoin?
Links tables based on an equality condition that compares specified columns, retaining duplicate columns.
What does a natural join do?
Links tables by selecting rows with common values in common attributes, eliminating duplicate columns.
What does the INTERSECT operator yield?
Only the rows that appear in both tables, requiring union compatibility.
What does an outer join do?
Retains matched pairs and leaves unmatched values in other tables as null.
What is an inner join?
Returns only matched records from the tables being joined, retaining duplicate columns.
What are integrity rules in relational databases?
Rules that ensure data accuracy and consistency, such as entity and referential integrity.
What does structural independence mean in a relational database?
The ability to change the structure of a database without affecting the data access.
What is a primary key?
The candidate key chosen to uniquely identify every row in a table.
What is referential integrity?
The condition where a foreign key contains a value that refers to an existing valid tuple (row) in another relation.
What is controlled redundancy in a relational database?
The sharing of common attributes among tables to enable linking, while avoiding unnecessary duplication of attribute values.
What is data redundancy?
The unnecessary duplication of data within a database.
What is a synonym in database terminology?
The use of different names to describe the same attribute.
What is the closure property in relational algebra?
The use of relational algebra operators on existing relations produces new relations.
What is a homonym in the context of databases?
The use of the same name to label different attributes.
What is a persistent representation of a logical relation?
The way data is stored in a table format in a relational database.
What role do foreign keys play in data redundancy?
They control data redundancies by using common attributes shared by tables.
What is the purpose of a join in relational databases?
To intelligently combine information from two or more tables.
What is the purpose of a data dictionary?
To provide a centralized repository of information about the data in the database.
What is the role of a key in a relational database?
To uniquely identify each row in a table and determine other attributes.
What is the primary focus of the relational database model?
To view data logically rather than physically.
What is a theta join?
Uses any comparison operator to link tables.
What does the DIVIDE operator do?
Uses one 2-column table as the dividend and one single-column table as the divisor, outputting a single column of values associated with every row in the divisor.
What is functional dependence?
When the value of one attribute (A) determines the value of another attribute (B).
What is the PRODUCT operator in relational algebra?
Yields all possible pairs of rows from two tables.
What does the DIFFERENCE operator do?
Yields all rows in one table that are not found in the other table, requiring union compatibility.
What is a right outer join?
Yields all rows in the AGENT table, including those without matching values in the CUSTOMER table.
What is a left outer join?
Yields all rows in the CUSTOMER table, including those without matching values in the AGENT table.
What is a 1:M relationship in relational databases?
A relationship where one entity is related to many entities, considered the norm in database design.
What is a 1:1 relationship?
A relationship where one entity is related to only one other entity, which is rare in database design.
What is the NOT NULL constraint?
A restriction placed on a column to ensure that every row has a value for that column.
What is the UNIQUE constraint?
A restriction placed on a column to ensure that no duplicate values exist for that column.
What is a superkey?
A set of one or more attributes that, taken collectively, can uniquely identify a record in a table.
What are Codd's Relational Database Rules?
A set of thirteen rules proposed by Edgar F. Codd that define what is required from a database management system for it to be considered relational.
What is a candidate key?
A superkey without unnecessary attributes, also known as a minimal superkey.
What does relational algebra define?
A theoretical way of manipulating table contents using relational operators.
What is a table in the context of a relational database?
A two-dimensional structure composed of rows and columns that represents a logical relation.
What is the purpose of the SELECT operator?
A unary operator that yields a horizontal subset of a table.
What does the PROJECT operator do?
A unary operator that yields a vertical subset of a table.
What is a foreign key?
An attribute whose values match primary key values in a related table.
What is a unique index?
An index where the index key can have only one pointer value associated with it.
What is a relational set operator?
An operator used to perform operations on sets of data within relational databases.
What is an index in a database?
An orderly arrangement to logically access rows in a table.
What is the function of the UNION operator?
Combines all rows from two tables, excluding duplicate rows.
What does entity integrity ensure?
Each row in a table has a unique identity, with a primary key defined that has a unique non-null value.
Provide an example of using the DIVIDE operator.
Finding the Lines of Code (LOC) that are common among Codes A and B.
How can M:N relationships be implemented in relational databases?
By breaking them into a set of 1:M relationships using a composite entity.
Define a superkey.
Any key that uniquely identifies each row in a table.
What is the function of a data dictionary?
Provides detailed accounting of all tables in a database, including attribute names and characteristics.
What are nulls in a database?
Representations of no data entry, which are not permitted in primary keys and should be avoided in other attributes.
What are relational algebra functions supported by the relational model?
SELECT, PROJECT, JOIN, INTERSECT, UNION, DIFFERENCE, PRODUCT, DIVIDE.
What is required for tables to be union-compatible?
Tables must share the same number of columns, and their corresponding columns must share compatible domains.
What are the basic building blocks of a relational database?
Tables.
