LSIS 5451 North Carolina Central University ER Modeling and Normalization Project

LSIS 5451 – Homework 5

ER Modeling and Normalization

Due Date: Nov. 16^th, 2020 (midnight)

General Instructions

• Do not include your name or any other personally identifiable information in your submission.
• In your ER model, clearly indicate the keys, cardinalities of the relationships, and any other constraints.
• Any assumptions you make, which are not stated in the problem definition, need to written explicitly. The assumptions you add must be “in addition” to the specified requirements in the problem definition without deleting any of these requirements.
• You are welcome to use any suitable tool in creating your ER model.

Problem 1 [40 points]

The Federal Reserve System is looking to create a database to track banking transactions and loans across the country. They have asked you to help design the database and the first step is to create an ER model of the database with the following requirements:

• Each bank has a unique ID, name, and headquarters address. Each bank has multiple branches, and for each branch, the following data are stored; the branch ID, address, and phone number. The branch ID is unique within each bank but is not unique across different banks.
• Customers at the bank can operate multiple accounts at the same branch or at different branches. Each account has a unique account ID, type (savings, checking, or investment), currency (e.g. dollar, pounds), and balance.
• A customer can take multiple loans from different branches. However, a customer is not allowed to take more than three loans from a given branch in the same calendar year. For each loan, the amount and the year it was taken is recorded.
• Each customer has a unique ID (SSN), name, address, and date of birth.
• For each customer, and each account, the type of transaction (withdrawal or deposit), the date and time of the transaction, and the amount of the transaction are recorded.

Problem 2 [60 points]

The table below shows sales data for The Zikora Shop. Use the table to answer the following questions.

• Using the data, state assumptions about functional dependencies among the columns of data. Justify your assumptions based on the sample data and also on what you know about retail sales.
• Given your assumptions in question 1, comment on the appropriateness of the following designs:
  • SALE(custNo, LastName, FirstName, invoiceNo, InvoiceDate, ItemNo, Item, Brand, BrandNo, Price, Total)
  • SALE(custNo, LastName, FirstName, invoiceNo, InvoiceDate, ItemNo, Item, Brand, BrandNo, Price, Total)
  • SALE(custNo, LastName, FirstName, invoiceNo, InvoiceDate, ItemNo, Item, Brand, BrandNo, Price, Total)
  • SALE(custNo, LastName, FirstName, invoiceNo, InvoiceDate, ItemNo, Item, Brand, BrandNo, Price, Total)
• Using any approach, transform the table to the underlisted forms:
  • First Normal Form
  • Second Normal Form
  • Third Normal Form