Chapter 3.2 Flashcards
(33 cards)
What does an Integrity Constraint (IC) refer to in a database?
A condition that must be true for any instance of the database, such as domain constraints.
ICs are specified when the schema is defined and checked when relations are modified.
What is a legal instance of a relation?
An instance that satisfies all specified Integrity Constraints (ICs).
What is the role of a Database Management System (DBMS) regarding illegal instances?
The DBMS should not allow illegal instances.
What is a primary key?
A set of fields that uniquely identifies a tuple in a relation, ensuring no two distinct tuples can have the same values in all key fields.
What is the difference between a candidate key and a superkey?
A candidate key is a minimal superkey; no subset of the candidate key can uniquely identify a tuple.
Fill in the blank: A set of fields in one relation that refers to a tuple in another relation is called a ______.
[foreign key]
What is referential integrity?
A condition achieved when all foreign key constraints are enforced, preventing dangling references.
What happens if an Enrolled tuple with a non-existent student ID is inserted?
It is rejected.
What is the default action for foreign key constraints on deletes and updates in SQL?
NO ACTION (delete/update is rejected).
What does the CASCADE option do in foreign key constraints?
It deletes all tuples that refer to the deleted tuple.
What is a weak entity?
An entity that can only be identified uniquely by considering the primary key of another (owner) entity.
What is the significance of total participation in a weak entity set?
It means that every weak entity must be associated with an owner entity.
True or False: A weak entity set must have total participation in its identifying relationship set.
True.
What is the purpose of a view in a database?
A view stores a definition rather than a set of tuples, allowing for virtual tables.
What is the primary key in the Enrolled table example?
PRIMARY KEY (sid, cid).
Fill in the blank: When a student’s tuple is deleted, the corresponding Enrolled tuples can either be ______ or ______.
[deleted, set to a default value]
What is an ISA hierarchy in databases?
A relationship where one entity set is a subclass of another, allowing for inheritance of attributes.
What are participation constraints?
Constraints that specify whether all or only some entities in a set must participate in a relationship.
What does the ON DELETE CASCADE option do?
It ensures that when a referenced row is deleted, all rows referencing it are also deleted.
What is the purpose of specifying foreign key constraints in SQL?
To enforce referential integrity between tables.
What is a primary key constraint?
A constraint that uniquely identifies each record in a table.
How can aggregate relationships be represented in a database?
By creating a relation that includes keys from the participating entities and any descriptive attributes.
What is a view in the context of database management?
A view is like a relation, but we store a definition, rather than a set of tuples.
Views can be used to simplify complex queries or to provide a specific subset of data.
How is a view created in SQL?
CREATE VIEW view_name AS SELECT columns FROM table WHERE condition;
Example: CREATE VIEW YoungActiveStudents (name, age) AS SELECT S.name, S.age FROM Students S, Enrolled E WHERE S.sid = E.sid and S.age<21.
