Project 2 — Relational Model

 

Academic Integrity: I completed this project without the assistance of others. I understand that cheating, helping others to cheat, or failing to report such actions is dishonest and wrong. Such acts could result in disciplinary action against me.

 

· Email your solution in an attached text file to both the instructor and the TA.

· The subject should be the phrase “CMSC461_Project_2”.

· Your assignment must be received by 11:59 pm on the due date.

 

Concepts

1. What is a primary key?

2. What is a foreign key?

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

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.

 

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)

 

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?

 

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.

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

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