CSE444 4/12/99

Homework 1 Solutions

Note that for 1 and 2, there are many possible solutions.

  1. Assumptions:
    1. Games can exist without teams (might have game fields and the times that are available before season starts and you need to schedule the teams into these slots - otherwise games is weak entity).
    2. We only want to keep track of the current team the player plays for (we don't need an archive of teams played for previously).
  1. Assumptions:
    1. A particular shipment goes to only one store.
    2. A trip might load shipments from many warehouses.
    3. We don't need to know which warehouse a shipment came from (otherwise, we could have another relationship from shipment to warehouse).
  1. Bank ER
    1. BANK, ACCOUNT, CUSTOMER, LOAN
    2. weak entity: BANK-BRANCH, partial key: BanchNo, id relationship: BRANCHES
    3. The partial key BranchNo specifies that the same BranchNo value may occur in different banks. BRANCHES specifies that BranchNo values are uniquely assigned for those BANK-BRANCH entities that are related to the same BANK entity. The combination of Bank code and BranchNo together consititute a full indentifier for a BANK-BRANCH.
    4. BRANCHES, ACCTS, LOANS, A-C, L-C

      5. BANK to BRANCHES (1,N): each bank has at least one branch

      BANK-BRANCH to BRANCHES (1,1): each branch is associated with exactly 1 bank

      BANK-BRANCH to ACCTS (0,N): a branch can have 0 or more accounts

      ACCOUNT to ACCTS (1,1): an account is associated with exactly 1 branch

      BANK-BRANCH to LOANS (0,N): a branch can have 0 or more loans

      LOAN to LOANS (1,1): a loan is associated with exactly 1 branch

      ACCOUNT to A-C (1,N): an account is associated with 1 or more customers

      CUSTOMER to A-C (0,N): a customer can have 0 or more accounts

      LOAN to L-C (1,N): a loan is associated with 1 or more customers

      CUSTOMER to L-C (0,N): a customer can have 0 or more loans

    5. Some possible requirements: Each bank has a unique code and a name and address. Each bank is related to one or more branches and the BranchNo is unique among branches for a particular bank. Each bank-branch has a location, zero or more loans, and zero or more accounts. Each acct has a unique number, balance, type and is related to exactly one branch and to at least one customer. Each loan has a unique number, amount, and type and is related to exactly one branch and to at least one customer. Each customer has a SSN, Name, phone, address, and is related to 0 or more accounts and 0 or more loans.
    6. ACCOUNT to A-C (1,N)

CUSTOMER to A-C (1,N)

LOAN to L-C (1,N)

CUSTOMER to L-C (0,2)

LOAN to LOANS (1,1)

BANK-BRANCH to LOANS (0, 1000)