midterm 2 flashcards
What is a relation in the relational model?
A relation is a named, two-dimensional table of data with rows (tuples) and columns (attributes).
What are the key properties of a relation?
- Unique relation name
- Atomic attribute values
- Unique rows
- Unique column names
- Column and row order does not matter.
What is the difference between an entity and a relation?
An entity is a conceptual representation of a real-world object, while a relation is its implementation as a table in a database.
How do entities in an ER diagram convert to relational tables?
Each entity becomes a table, attributes become columns, and the primary key of the entity becomes the table’s primary key.
How are composite attributes handled in relational conversion?
They are broken down into their simpler attributes as separate columns.
How are multi-valued attributes handled in relational conversion?
They are placed in a separate relation with a foreign key linking to the original entity.
How are one-to-many (1:N) relationships represented in tables?
The primary key of the ‘one’ side is placed as a foreign key in the ‘many’ side.
How are many-to-many (M:N) relationships represented in tables?
A new junction table is created with foreign keys referencing both entities.
How are weak entities represented in tables?
A weak entity gets its own table, with its primary key including a foreign key from its identifying strong entity.
What is an inner join?
An inner join returns only the rows where there is a match between tables.
What is an outer join?
An outer join includes unmatched rows from one or both tables (Left, Right, or Full Join).
What is a self-join?
A join where a table is joined with itself, typically using aliases.
How does a natural join differ from an equi-join?
A natural join removes duplicate columns, while an equi-join keeps all columns in the result.
What is a subquery?
A SELECT statement inside another SELECT, used to fetch values for filtering or comparison.
What is a scalar subquery?
A subquery that returns only one value and is used in single-value comparisons.
What operator should be used for multi-row subqueries?
Operators like IN, ANY, or ALL should be used for multi-row subqueries.
What is the difference between a join and a subquery?
A join combines multiple tables into one result set, while a subquery provides values for conditions in another query.
What are the types of anomalies in a database?
- Insertion Anomaly 2. Deletion Anomaly 3. Modification Anomaly.
What is referential integrity?
A constraint ensuring that a foreign key must match an existing primary key in the referenced table.
What is functional dependency?
A relationship where one attribute uniquely determines another, e.g., Emp_ID → Emp_Name.
What is the goal of normalization?
To reduce redundancy and anomalies by structuring data into well-formed tables.
What is First Normal Form (1NF)?
A table is in 1NF if it has atomic attribute values and no repeating groups.
What is Second Normal Form (2NF)?
A table is in 2NF if it is in 1NF and has no partial dependencies.
What is Third Normal Form (3NF)?
A table is in 3NF if it is in 2NF and has no transitive dependencies (one attribute determines another and another and another…).
