Execute the following Microsoft SQL Server T-SQL script to demonstrate the use of the CREATE TABLE command.


USE tempdb;
-- Create banking data model - SQL CREATE TABLE
-- Bank database table definitions for home equity and automobile loans  
-- SQL table definition- T-SQL primary key, foreign key constraint - PK, FK 
-- MSSQL data modelling: 1 (PK) to many(FK) relationship
CREATE TABLE Branch (
  BranchID     INT    IDENTITY ( 1 , 1 )   PRIMARY KEY,
  BranchName   CHAR(32)    NOT NULL    UNIQUE,
  BranchCity   CHAR(32)    NOT NULL CHECK ( LEN(BranchCity)>0),
  Assets       MONEY    NOT NULL,
  ModifiedDate DATETIME    DEFAULT (getdate()));
 
CREATE TABLE ACCOUNT (
  AccountID     INT    IDENTITY ( 1 , 1 )   PRIMARY KEY,
  BranchID      INT    NOT NULL references Branch(BranchID),
  AccountNumber CHAR(20)    NOT NULL    UNIQUE,
  AccountType   CHAR(12)    NOT NULL,
  Balance       MONEY    NOT NULL,
  ModifiedDate  DATETIME    DEFAULT (getdate()));
 
CREATE TABLE Customer (
  CustomerID   INT    IDENTITY ( 1 , 1 )   PRIMARY KEY,
  Customer     CHAR(64)    NOT NULL    UNIQUE,
  [Type]       CHAR(20)    NOT NULL,
  Street       VARCHAR(32)    NOT NULL CHECK (Street !=''),
  City         CHAR(32)    NOT NULL,
  [State]      CHAR(32)    NOT NULL,
  Zip          CHAR(10)    NOT NULL,
  Country      CHAR(32)    NOT NULL,
  ModifiedDate DATETIME    DEFAULT (getdate()));
 
CREATE TABLE Loan (
  LoanID       INT    IDENTITY ( 1 , 1 )    PRIMARY KEY,
  LoanNumber   CHAR(20)    NOT NULL    UNIQUE,
  LoanType     VARCHAR(30)    NOT NULL,
  BranchName   CHAR(32)    NOT NULL,
  Amount       MONEY    NOT NULL,
  ModifiedDate DATETIME    DEFAULT (getdate()));
 
-- SQL junction table - many to many relationship
-- Composite PRIMARY KEY
CREATE TABLE Depositor (
  CustomerID   INT    NOT NULL references Customer(CustomerID),
  AccountID    INT    NOT NULL  references Account(AccountID),
  ModifiedDate DATETIME    DEFAULT (getdate()),
     PRIMARY KEY ( CustomerID,AccountID ));
 
CREATE TABLE Borrower (
  CustomerID   INT    NOT NULL references Customer(CustomerID),
  LoanID       INT    NOT NULL references Loan(LoanID),
  ModifiedDate DATETIME    DEFAULT (getdate()),
     PRIMARY KEY ( CustomerID,LoanID ));
 
 
CREATE TABLE [Transaction] (
  TransactionID INT    IDENTITY ( 1 , 1 ) PRIMARY KEY,
  AccountID     INT references Account(AccountID),
  TranType      CHAR(1),
  Amount        MONEY,
  ModifiedDate  DATETIME    DEFAULT (getdate()));
GO
 
-- Population script
INSERT INTO [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 [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 [dbo].[Customer]
           ([Customer],
            [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 Customer 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 - FOREIGN KEY tables first
DROP TABLE Borrower
DROP TABLE Depositor
DROP TABLE Loan
DROP TABLE Customer
DROP TABLE [Transaction]
DROP TABLE ACCOUNT
DROP TABLE Branch