Chapter 3.2

Question 1

What does an Integrity Constraint (IC) refer to in a database?

Answer 1

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.

Question 2

What is a legal instance of a relation?

Answer 2

An instance that satisfies all specified Integrity Constraints (ICs).

Question 3

What is the role of a Database Management System (DBMS) regarding illegal instances?

Answer 3

The DBMS should not allow illegal instances.

Question 4

What is a primary key?

Answer 4

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.

Question 5

What is the difference between a candidate key and a superkey?

Answer 5

A candidate key is a minimal superkey; no subset of the candidate key can uniquely identify a tuple.

Question 6

Fill in the blank: A set of fields in one relation that refers to a tuple in another relation is called a ______.

Answer 6

[foreign key]

Question 7

What is referential integrity?

Answer 7

A condition achieved when all foreign key constraints are enforced, preventing dangling references.

Question 8

What happens if an Enrolled tuple with a non-existent student ID is inserted?

Answer 8

It is rejected.

Question 9

What is the default action for foreign key constraints on deletes and updates in SQL?

Answer 9

NO ACTION (delete/update is rejected).

Question 10

What does the CASCADE option do in foreign key constraints?

Answer 10

It deletes all tuples that refer to the deleted tuple.

Question 11

What is a weak entity?

Answer 11

An entity that can only be identified uniquely by considering the primary key of another (owner) entity.

Question 12

What is the significance of total participation in a weak entity set?

Answer 12

It means that every weak entity must be associated with an owner entity.

Question 13

True or False: A weak entity set must have total participation in its identifying relationship set.

Answer 13

True.

Question 14

What is the purpose of a view in a database?

Answer 14

A view stores a definition rather than a set of tuples, allowing for virtual tables.

Question 15

What is the primary key in the Enrolled table example?

Answer 15

PRIMARY KEY (sid, cid).

Question 16

Fill in the blank: When a student’s tuple is deleted, the corresponding Enrolled tuples can either be ______ or ______.

Answer 16

[deleted, set to a default value]

Question 17

What is an ISA hierarchy in databases?

Answer 17

A relationship where one entity set is a subclass of another, allowing for inheritance of attributes.

Question 18

What are participation constraints?

Answer 18

Constraints that specify whether all or only some entities in a set must participate in a relationship.

Question 19

What does the ON DELETE CASCADE option do?

Answer 19

It ensures that when a referenced row is deleted, all rows referencing it are also deleted.

Question 20

What is the purpose of specifying foreign key constraints in SQL?

Answer 20

To enforce referential integrity between tables.

Question 21

What is a primary key constraint?

Answer 21

A constraint that uniquely identifies each record in a table.

Question 22

How can aggregate relationships be represented in a database?

Answer 22

By creating a relation that includes keys from the participating entities and any descriptive attributes.

Question 23

What is a view in the context of database management?

Answer 23

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.

Question 24

How is a view created in SQL?

Answer 24

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.

Question 25

What command is used to remove a view from the database?

Answer 25

DROP VIEW command.

This command allows users to delete a view from the database.

Question 26

What happens to a table if there is a view associated with it when using the DROP TABLE command?

Answer 26

DROP TABLE command has options to let the user specify how to handle views associated with the table.

Users can choose to drop the view along with the table or keep it.

Question 27

How can views be used in terms of security?

Answer 27

Views can present necessary information while hiding details in underlying relations.

For example, a view can show enrolled students without revealing course IDs.

Question 28

What is an updatable view?

Answer 28

An updatable view allows data modification through the view.

Example: CREATE VIEW GoodStudents1 (sid, gpa) AS SELECT S.sid, S.gpa FROM Students S WHERE S.gpa > 3.0.

Question 29

What is the relational model?

Answer 29

A tabular representation of data.

It is simple, intuitive, and the most widely used model in database systems.

Question 30

What types of integrity constraints can be specified in the relational model?

Answer 30

Primary keys, foreign keys, and domain constraints.

These constraints ensure data integrity and adherence to business rules.

Question 31

What is the role of the DBA regarding integrity constraints?

Answer 31

The DBA specifies integrity constraints based on application semantics, and the DBMS checks for violations.

This helps maintain the accuracy and reliability of the data.

Question 32

Fill in the blank: A powerful and natural query language exists for the relational model, which allows for _______.

Answer 32

expressive data retrieval.

SQL is the most common query language used with relational databases.

Question 33

True or False: Views can be used to hide sensitive data while still providing necessary information.

Answer 33

True.

This is a common practice in database security.