|
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
------------
|
