Database System Concepts 7th Edition Solutions -Chapter 2

Database System Concepts is the highly recommended book for the people interested in databases. Free version of this book can be downloaded from HERE.

Practice Exercises

2.1 Consider the employee database in below Figure. What are the appropriate primary keys?

database system concepts

Primary key is an attribute in a table which can uniquely identify that table. In employee table the person_name, in works table person_name and in company table company_name are the primary keys.

2.2 Consider the foreign-key constraint from the dept name attribute of instructor to the department relation. Give examples of inserts and deletes to these relations that can cause a violation of the foreign-key constraint.

Insertion:

As Instructor is child table of department for attribute dept_name, then adding something in instructor table which is not in department table will cause error. For example if we add economics as dept_name in instructor table but department table don’t contain it then it is a violation of foreign key constraint.

Deletion:

As department is parent table of instructor for attribute dept_name, then deleting something in department table which is present in instructor table will cause error. For example if we delete economics as dept_name in department table but instructor table do contain it then it is a violation of foreign key constraint.

2.5 What is the result of first performing the Cartesian product of student and advisor, and then performing a selection operation on the result with the predicate s_id = ID? (Using the symbolic notation of relational algebra, this query can be written as σs_id=ID(student × advisor).)

As select in Relational algebra selects all the attributes, so in result we will get all the attributes of student followed by all the attributes of advisor table. Note: the students who do not have an advisor will not appear in the table.

2.6 Consider the employee database of Figure 2.17. Give an expression in the relational algebra to express each of the following queries:

a. Find the name of each employee who lives in city “Miami”.
b. Find the name of each employee whose salary is greater than $100000.
c. Find the name of each employee who lives in “Miami” and whose salary is greater than $100000.

Note: I have used words instead of symbols you should use symbols.

  1. Project name ( Select city = “Miami” (employee))
  2. Project name ( Select salary > 100000(employee))
  3. Project name ( Select city = “Miami” and salary > 100000(employee))

2.7 Consider the bank database of Figure 2.18. Give an expression in the relational algebra for each of the following queries:
a. Find the name of each branch located in “Chicago”.
b. Find the ID of each borrower who has a loan in branch “Downtown”

Note: I have used words instead of symbols you should use symbols.

  1. Project branch_name ( Select branch_city = “Chicago” (branch))
  2. Project customer_id ( Select branch_name = “Downtown” (borrower join loan))

READ MORE

Databases related posts Visit HERE

Python-related posts Visit HERE

C++ related posts Visit HERE

Data Structures related posts visit HERE

Algorithms related posts visit HERE

Data Science related posts visit HERE

Share the Knowledge