LSIS 5451 North Carolina Central University ER Modeling and Normalization Project
LSIS 5451 – Homework 5 ER Modeling and Normalization Due Date: Nov. 16th, 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 FormSecond Normal FormThird Normal Form custNo LastName FirstName invoiceNo InvoiceDate ItemNo Item Brand BrandNo Price Total C001 Shire Robert I001 I001 I006 14-Jan-20 14-Jan-20 5-Feb-20 IT01 IT02 IT03 Laptop Phone Tablet Samsung Apple Dell B001 B002 B003 1000 500 750 1500 1500 750 C002 Goodyear Katherine I003 I008 I008 15-Jan-20 15-Feb-20 15-Feb-20 IT04 IT05 IT06 Printer Kindle Laptop HP Amazon Apple B004 B005 B002 700 250 1750 700 2000 2000 C003 Bancroft Chris I004 15-Jan-20 IT07 iPad Apple B002 500 500 COO4 Griffith John I005 23-Jan-20 IT03 Tablet Dell B003 750 750 COO5 Tierney Doris I007 I011 10-Feb-20 25-Feb-20 IT04 IT08 Printer Phone HP Huawei B004 B006 700 350 700 350 COO6 Anderson Donna I010 15-Feb-20 IT01 Laptop Samsung B001 1000 1000