Lab 1 - Relational Models

  1. (10 points)

    A) Identify and discuss the serious data redundancy problems exhibited by the relation structure shown below:

    B) If this relation is the only one describing the teaching schedule, what problem(s) might arise if the KOM building was condemned and every row referring to that building was deleted?

    image

  2. (10 points)

    Create a Crow’s Foot ERD to include the following business rules for the ProdCo company:

    • Each sales representative writes many invoices.

    • Each invoice is written by one sales representative.

    • Each sales representative is assigned to one department.

    • Each department has at least one sales representative.

    • Each customer can generate many invoices.

    • When a new customer is added that customer may or may not have any invoices.

    • Each invoice is generated by one customer.

  3. (10 points)

    Write the business rules that are reflected in the ERD shown in the figure below. Remember, business rules generally DO NOT include “helper” relations.

  4. (10 points)

    (From Tannenbaum) Given the following relational model:

    employee (personName, street, city )
works (personName, companyName, salary)
company (companyName, city)
manages (personName, managerName)

    give Relational Algebra expressions to express each of the following queries:

    a. Find the names of all employees who work for “First Bank Corporation”.
    b. Find the names and cities of residence of all employees who work for “First Bank Corporation”.
    c. Find the names, street addresses, and cities of residence of all employees who work for “First Bank Corporation” and earn more than $10,000.
    d. Find the names of all employees in this database who live in the same city as the company for which they work.
    e. Assume the companies may be located in several cities. Find all companies located in every city in which “Small Bank Corporation” is located.

  5. (10 points)

    Consider the two tables T1 and T2:

T1:

a b c
5 Q 3
10 R 7
15 Q 11


T2:

x y z
25 R 13
10 R 5
10 S 11


Show the results of these relational algebra expressions:

Relational Algebra Expressions