Download - Project Portfolio
![Page 1: Project Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062308/559c04ff1a28ab8d188b462c/html5/thumbnails/1.jpg)
Project Portfolio
Linh Huyen Nguyen
![Page 2: Project Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062308/559c04ff1a28ab8d188b462c/html5/thumbnails/2.jpg)
PROJECT 1
![Page 3: Project Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062308/559c04ff1a28ab8d188b462c/html5/thumbnails/3.jpg)
RDBMS PROJECT
• Create a new database for the financial services wing, PiggyBank.
• Create a Visio Entity Relationship diagram when utilizing the columns placed in the list.
• There will be only two types of accounts at this time, Checking and Savings accounts.
• Separate the columns into appropriate normalized entities (tables), and to define the relationships between these entities ( use Crow’s Foot notation).
• Make the most efficient choices as far as primary key constraints and foreign key constraints, and picked the appropriate data types for each of the columns.
![Page 4: Project Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062308/559c04ff1a28ab8d188b462c/html5/thumbnails/4.jpg)
ER Diagram
![Page 5: Project Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062308/559c04ff1a28ab8d188b462c/html5/thumbnails/5.jpg)
PROJECT 2
![Page 6: Project Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062308/559c04ff1a28ab8d188b462c/html5/thumbnails/6.jpg)
T-SQL Client application
JungleBook Database • Cheap Books form • Search for Authors form • Range of Customer form • Large Order Page • Available Books Page • Expired Card Page• Author and Books Page• Hot Item Page• Search for Authors Page
![Page 7: Project Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062308/559c04ff1a28ab8d188b462c/html5/thumbnails/7.jpg)
JungleBook Diagram
![Page 8: Project Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062308/559c04ff1a28ab8d188b462c/html5/thumbnails/8.jpg)
Large order page
![Page 9: Project Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062308/559c04ff1a28ab8d188b462c/html5/thumbnails/9.jpg)
Hot Item Page
![Page 10: Project Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062308/559c04ff1a28ab8d188b462c/html5/thumbnails/10.jpg)
Author and Book Page
![Page 11: Project Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062308/559c04ff1a28ab8d188b462c/html5/thumbnails/11.jpg)
T-SQL Client application
Library Database • Return member info from Member and Adult table
• Display available books
• Search member info using member number
• Use UNION, list all member reserve the specific book
• Use CASE, list all member reserve the specific book
• Create temporary table
• Display members have past due loan use temporary table
• Display members who pay highest fine
• List of member who want to reserve the specific book
![Page 12: Project Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062308/559c04ff1a28ab8d188b462c/html5/thumbnails/12.jpg)
Library Diagram
![Page 13: Project Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062308/559c04ff1a28ab8d188b462c/html5/thumbnails/13.jpg)
Display available books
![Page 14: Project Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062308/559c04ff1a28ab8d188b462c/html5/thumbnails/14.jpg)
Use Union
![Page 15: Project Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062308/559c04ff1a28ab8d188b462c/html5/thumbnails/15.jpg)
Display member pay highest fine
![Page 16: Project Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062308/559c04ff1a28ab8d188b462c/html5/thumbnails/16.jpg)
PROJECT 3
![Page 17: Project Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062308/559c04ff1a28ab8d188b462c/html5/thumbnails/17.jpg)
Library Stored Procedure
• Add Adult
• Add Juvenile
• Check Out Item
• Check In Item
• Add a Book
• Convert Juvenile to Adult
• Renew membership
• Update Adult stored procedure
![Page 18: Project Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062308/559c04ff1a28ab8d188b462c/html5/thumbnails/18.jpg)
AddAdult Stored procedure
![Page 19: Project Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062308/559c04ff1a28ab8d188b462c/html5/thumbnails/19.jpg)
CheckOutItem Stored Procedure
![Page 20: Project Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062308/559c04ff1a28ab8d188b462c/html5/thumbnails/20.jpg)
Working Test Script
![Page 21: Project Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062308/559c04ff1a28ab8d188b462c/html5/thumbnails/21.jpg)
Failure Test Case
![Page 22: Project Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062308/559c04ff1a28ab8d188b462c/html5/thumbnails/22.jpg)
PROJECT 4
![Page 23: Project Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062308/559c04ff1a28ab8d188b462c/html5/thumbnails/23.jpg)
PiggyBank ProjectStore Procedure• Create Customer
• Withdraw
• Update Customer
• Create Saving
• Create Checking
• Update Interest
• Add Customer
• Get Transaction
• Deposit
• Transfer
• Update Over Draft
• Get Balance
• Close Account
• Reactivate Account
Trigger• DDL Trigger to prevent drop and
alter to tables and views
• DML Trigger to prevent delete of Account and delete/update of Transactions
View• Account History for past month
• Account History for any month
• Customer Account past month
• Search Customer by Name
![Page 24: Project Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062308/559c04ff1a28ab8d188b462c/html5/thumbnails/24.jpg)
PiggyBank Diagram
![Page 25: Project Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062308/559c04ff1a28ab8d188b462c/html5/thumbnails/25.jpg)
Create Database and Tables
![Page 26: Project Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062308/559c04ff1a28ab8d188b462c/html5/thumbnails/26.jpg)
Create Customer Stored Procedure
![Page 27: Project Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062308/559c04ff1a28ab8d188b462c/html5/thumbnails/27.jpg)
Successful Test Script
![Page 28: Project Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062308/559c04ff1a28ab8d188b462c/html5/thumbnails/28.jpg)
Failure Test Case
![Page 29: Project Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062308/559c04ff1a28ab8d188b462c/html5/thumbnails/29.jpg)
Create VIEW of Customer Account
![Page 30: Project Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062308/559c04ff1a28ab8d188b462c/html5/thumbnails/30.jpg)
Account History Any Month
![Page 31: Project Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062308/559c04ff1a28ab8d188b462c/html5/thumbnails/31.jpg)
DDL Trigger
![Page 32: Project Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062308/559c04ff1a28ab8d188b462c/html5/thumbnails/32.jpg)
DML Trigger
![Page 33: Project Portfolio](https://reader035.vdocuments.us/reader035/viewer/2022062308/559c04ff1a28ab8d188b462c/html5/thumbnails/33.jpg)
THANK YOU