Project 2 — Relational Model

SOLUTIONS

 

Concepts

1. What is a primary key?

 

· A candidate key chosen to be the ONE primary means of identifying tuples in the relation

 

2. What is a foreign key?

 

· A primary key of one relation included in another relation for the purpose of joining logically related data in two separate relations.

 

3. What is the difference between a relation instance and a relation schema?

 

· relation schema—Logical design of a relation; consists of relation name, list of named attributes, and their domains

· relation instance—Current set of values for all the tuples in a relation; The set of tuples that actually exists in a relation at a specific point in time

 

4. List two reasons why null values might be used in a database.  Give an concrete example of each based on one of the example schemas given above.

 

· Nulls are used when the value is not known or does not exist

· Null might be used in the section relation of the university database for the building and room number when a class as been scheduled but not assigned to a classroom yet.

 

Exercises

 

Consider the following relation schemas for the questions below.

banking_relation_schemas.pdf.

university_relation_schemas.pdf 

 

5. Consider the relations in the sample banking relation schemas.

a. List the primary keys for each relation

b. List the foreign keys for each relation (if there are any)

 

PKs—underlined         FKs—marked with (+)

branch          (branch_name, branch_city, assets )

customer       (customer_name, customer_street, customer_city )

account         (account_number, branch_name(+), balance )

loan              (loan_number, branch_name(+), amount )

depositor      (customer_name(+), account_number(+) )

borrower      (customer_name(+), loan_number(+) )

 

6. In the university relation schema, consider the advisor relation with s_id as the primary key of advisor. Suppose a student can have more than one advisor.

a. Would s_id still be a primary key of the advisor relation?

b. If not, what should the primary key of advisor be?

 

No, s id would not be a primary key, since there may be two (or more) tuples for a single student, corresponding to two (or more) advisors. The primary key should then be s id, i id.

 

7. For the banking database, give an expression in the relational algebra for each of the following queries:

a. Find all loan numbers with a loan value greater than $10,000.

 

p_{loan_number} (δ_{amount > 10000} (loan))

 

b. Find the names of all depositors who have an account with a value greater than $6,000.

p_{customer_name} (δ_{balance > 6000} (depositor ⋈ account ))

 

b. Find the names of all depositors who have an account at the “Uptown” branch.

p _{customer_name} (δ _{(branch_name = “Uptown” )} (depositor  ⋈ account ) )