DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to setup a bank database for loan processing?

Execute the following Microsoft SQL Server T-SQL example scripts in Management Studio Query Editor to create a bank database model for home equity and auto loans processing. The cardinality of the relationships indicated in the comment sections.

USE AdventureWorks2008;

 

-- Create banking data model - SQL Server T-SQL  

-- Bank database table definitions for home equity and automobile loans   

-- SQL create table: table definition; primary key, foreign key constraint - PK, FK 

-- MSSQL data modelling: 1 (PK) to many(FK) relationship 

CREATE TABLE Branch

  (

     BranchID     INT IDENTITY ( 1, 1 ) UNIQUE,

     BranchName   CHAR(32) NOT NULL UNIQUE,

     BranchCity   CHAR(32) NOT NULL,

     Assets       MONEY NOT NULL,

     ModifiedDate DATETIME DEFAULT (getdate()),

     PRIMARY KEY ( BranchID ),

  );

 

-- Referential integrity action: ON DELETE CASCADE - DELETE FK WHEN PK deleted

CREATE TABLE ACCOUNT

  (

     AccountID     INT IDENTITY ( 1, 1 ) UNIQUE,

     BranchID      INT NOT NULL,

     AccountNumber CHAR(20) NOT NULL UNIQUE,

     AccountType   CHAR(12) NOT NULL CONSTRAINT ATC CHECK (AccountType IN ('C','S')),

     Balance       MONEY NOT NULL,

     ModifiedDate  DATETIME DEFAULT (getdate()),

     PRIMARY KEY ( AccountID ),

     FOREIGN KEY ( BranchID ) REFERENCES Branch(BranchID) ON DELETE CASCADE

  );

 

CREATE TABLE Customer

  (

     CustomerID   INT IDENTITY ( 1, 1 ) UNIQUE,

     Name         CHAR(32) NOT NULL UNIQUE,

     [Type]       CHAR(20) NOT NULL,

     Street       VARCHAR(32) NOT NULL,

     City         CHAR(32) NOT NULL,

     [State]      CHAR(32) NOT NULL,

     Zip          CHAR(10) NOT NULL,

     Country      CHAR(32) NOT NULL,

     ModifiedDate DATETIME DEFAULT (getdate()),

     PRIMARY KEY ( CustomerID )

  );

 

-- BranchID is a FOREIGN KEY in this table

CREATE TABLE Loan

  (

     LoanID       INT IDENTITY ( 1, 1 ) UNIQUE,

     BranchID     INT NOT NULL REFERENCES Branch(BranchID) ON DELETE CASCADE,

     LoanNumber   CHAR(20) NOT NULL UNIQUE,

     LoanType     VARCHAR(30) NOT NULL,

     Amount       MONEY NOT NULL,

     ModifiedDate DATETIME DEFAULT (getdate()),

     PRIMARY KEY ( LoanID )

  );

 

-- SQL junction (bridge, xref) table - many to many relationship 

CREATE TABLE Depositor

  (

     CustomerID   INT NOT NULL,

     AccountID    INT NOT NULL,

     ModifiedDate DATETIME DEFAULT (getdate()),

     PRIMARY KEY ( CustomerID, AccountID ),

     FOREIGN KEY ( AccountID ) REFERENCES ACCOUNT(AccountID) ON DELETE CASCADE,

     FOREIGN KEY ( CustomerID ) REFERENCES Customer(CustomerID)

  );

 

CREATE TABLE Borrower

  (

     CustomerID   INT NOT NULL,

     LoanID       INT NOT NULL,

     ModifiedDate DATETIME DEFAULT (getdate()),

     PRIMARY KEY ( CustomerID, LoanID ),

     FOREIGN KEY ( CustomerID ) REFERENCES Customer(CustomerID),

     FOREIGN KEY ( LoanID ) REFERENCES Loan(LoanID)

  );

 

CREATE TABLE [Transaction]

  (

     TransactionID INT IDENTITY ( 1, 1 ),

     AccountID     INT,

     TranType      CHAR(1),

     Amount        MONEY,

     ModifiedDate  DATETIME DEFAULT (getdate()),

     PRIMARY KEY ( TransactionID ),

     FOREIGN KEY ( AccountID ) REFERENCES ACCOUNT(AccountID) ON DELETE CASCADE

  );

 

GO

 

-- Population script 

INSERT INTO [AdventureWorks2008].[dbo].[Branch]

            ([BranchName],

             [BranchCity],

             [Assets])

VALUES      ('Long Beach Main St',

             'Los Angeles',

             50000000)

 

GO

 

SELECT *

FROM   Branch

 

GO

 

/*

 

BranchID BranchName BranchCity Assets ModifiedDate 

1 Long Beach Main St    Los Angeles   50000000 3/18/09 6:30

 

*/

INSERT INTO [AdventureWorks2008].[dbo].[Account]

            ([BranchID],

             [AccountNumber],

             [AccountType],

             [Balance])

VALUES      (1,

             '1000030045235',

             'PERSONAL',

             6000.0)

 

GO

 

SELECT *

FROM   ACCOUNT

 

GO

 

/*

 

AccountID BranchID AccountNumber AccountType Balance ModifiedDate 

1 1 1000030045235 PERSONAL     6/4/16 0:00 3/18/09 6:30 

 

*/

INSERT INTO [AdventureWorks2008].[dbo].[Customer]

            ([Name],

             [Type],

             [Street],

             [City],

             [State],

             [Zip],

             [Country])

VALUES      ('Robert Bullmore',

             'Business Proprietor',

             '113 Broadway',

             'New York City',

             'New York',

             '10001',

             'USA')

 

GO

 

SELECT *

FROM   Customer

 

GO

 

/*

 

CustomerID Name Type Street City State Zip Country ModifiedDate 

1 Robert Bullmore   Business Proprietor  113 Broadway New York City                    New York  10001 USA  3/18/09 6:30

 

*/

-- Cleanup 

DROP TABLE Borrower

 

DROP TABLE Depositor

 

DROP TABLE Loan

 

DROP TABLE Customer

 

DROP TABLE [Transaction]

 

DROP TABLE ACCOUNT

 

DROP TABLE Branch 

------------


Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE