There are only two types of accounts at this time: Checking and Savings accounts. The provided column list should be separated into appropriate entities (tables) with relationships between these entities defined. The most efficient choices as far as your primary key constraints and foreign key constraints, and picked the appropriate data types for each of the columns.
The goal of the project is to understand database entities in more depth and have practical experience of working with different objects of SQL.
- Create a database for a banking application called “Bank”.
- Create all the tables mentioned in the database diagram.
- Create all the constraints based on the database diagram.
- Insert at least 5 rows in each table.
- Create a view to get all customers with checking account from ON province.
- Create a view to get all customers with total account balance (including interest rate) greater than 5000.
- Create a view to get counts of checking and savings accounts by customer.
- Create a view to get any particular user’s login and password using AccountId.
- Create a view to get all customers’ overdraft amount.
- Create a stored procedure to add “User_” as a prefix to everyone’s login (username).
- Create a stored procedure that accepts AccountId as a parameter and returns customer’s full name.
- Create a stored procedure that returns error logs inserted in the last 24 hours.
- Create a stored procedure that takes a deposit as a parameter and updates CurrentBalance value for particular account.
- Create a stored procedure that takes a withdrawal amount as a parameter and updates CurrentBalance value for that account.
- Create a stored procedure to remove all security questions for a particular login.
- Delete all error logs created in the last hour.
- Write a query to remove SSN column from Customer table.
- Prepare a report to describe the project.
- Prepare a presentation for the project.