Reference no: EM132169205
Using the information below create an ERD crows foot diagram showing the entities and the relationships between them with cardinalities. You can use subtypes if you wish.
1. The database stores information about members. Each member provides personal information such as name, social security number, address, phone number, and email address. Each member is also assigned a unique ID.
2. The database also stores information about accounts. Each account has a single primary owner (must be a member) and can have one or more joint owners (members as well). Each account has an account number and a current balance.
3. There are several types of accounts, including checking account, saving account, CD, and loan. Each account has an interest rate. Checking and saving account also have a minimal balance requirement. Checking, saving, and CD also have a last interest date (the date the interest was deposited).
4. You can assume there are several types of CD account with different terms, e.g., 6 month, 12 month, 24 months, and 60 months.
5. You can also assume there are several types of loans (e.g., auto loan or home mortgage). You need to store the following information in loan table: term (the length of the loan), principal, unpaid interest, annual rate, monthly payment, payment received in last month, next payment due date, next payment due amount, termination date, and an account status ("good" or "bad"). "bad" means the account is past due.
6. Each loan has a fixed rate and a fixed monthly payment amount which is due before the next due date.
7. If the payment is less than the monthly payment, the due amount for the next month will be higher.
8. To capture average daily balance, you can create a table called daily balance, which stores the account number of a checking and saving account, a date, and the balance at the end of that day.
9. There is a rate table storing the current interest rate for all types of accounts. You can assume the same type of account has the same rate except for the loan and CDs.
10. There is a transaction table stores transactions made on accounts. There are four types of transactions: deposit, withdraw, transfer between accounts, and payment received for a loan. For each type of transaction, the database needs to record the account involved (two accounts are involved for transfer), the transaction type, the initiator of the transaction, and the amount of money in the transaction.
11. The transaction initiator could be the owner, the bank, or other party. You should also place reasonable restrictions on who can initiate a transaction. Everyone can deposit money to a checking or saving account. Only owner or authorized party can withdraw money from an account. CD and loan cannot take withdraw or deposit.
12. There is a message table that stores the message that will be sent to a member.