Create a view of the Join of Deposit and Withdraw transactions to Bank Branch UNION with the join of Bill Payment and Debit Purchase, or Return transactions to Merchant (i.e., all transactions with appropriate reference name.). BranchNbr stores RefNbr of either 'D', OR 'W' transactions and MerchantNbr stores RefNbr of either 'B', 'P' or 'R' transactions. GIVEN THE BELOW: --THIS CREATES THE TRANSACTION TABLE: CREATE TABLE Transaction ( TxNbr INT PRIMARY KEY, AccountNbr INT, TxTypeCode VARCHAR(250), TxDate DATE, TxTime TIMESTAMP, TxAmount FLOAT, RefNbr INT, FOREIGN KEY (AccountNbr)REFERENCES Account(AccountNbr), FOREIGN KEY (TxTypeCode) REFERENCES TxType(TxTypeCode)); --THIS CREATES THE BANK BRANCH TABLE: CREATE TABLE BankBranch ( BranchNbr INT PRIMARY KEY, BranchName VARCHAR(250)); --THIS CREATES THE MERCHANT TABLE: CREATE TABLE Merchant ( MerchantNbr INT PRIMARY KEY, MerchantName VARCHAR(250));
Create a view of the Join of Deposit and Withdraw transactions to Bank Branch UNION with the join of Bill Payment and Debit Purchase, or Return transactions to Merchant (i.e., all transactions with appropriate reference name.).
BranchNbr stores RefNbr of either 'D', OR 'W' transactions and MerchantNbr stores RefNbr of either 'B', 'P' or 'R' transactions.
GIVEN THE BELOW:
--THIS CREATES THE TRANSACTION TABLE:
CREATE TABLE Transaction (
TxNbr INT PRIMARY KEY,
AccountNbr INT,
TxTypeCode VARCHAR(250),
TxDate DATE,
TxTime TIMESTAMP,
TxAmount FLOAT,
RefNbr INT,
FOREIGN KEY (AccountNbr)REFERENCES Account(AccountNbr),
FOREIGN KEY (TxTypeCode) REFERENCES TxType(TxTypeCode));
--THIS CREATES THE BANK BRANCH TABLE:
CREATE TABLE BankBranch (
BranchNbr INT PRIMARY KEY,
BranchName VARCHAR(250));
--THIS CREATES THE MERCHANT TABLE:
CREATE TABLE Merchant (
MerchantNbr INT PRIMARY KEY,
MerchantName VARCHAR(250));
-- Insert test data into TxType table
INSERT INTO TxType (TxTypeCode, TxTypeDescription)
VALUES (‘D’, ‘DEPOSIT’);
INSERT INTO TxType (TxTypeCode, TxTypeDescription)
VALUES (‘W’, ‘WITHDRAWAL’);
INSERT INTO TxType (TxTypeCode, TxTypeDescription)
VALUES (‘P’, ‘PURCHASE’);
INSERT INTO TxType (TxTypeCode, TxTypeDescription)
VALUES (‘R’, ‘RETURN’);
INSERT INTO TxType (TxTypeCode, TxTypeDescription)
VALUES (‘B’, ‘BILL PAYMENT’);
| BRANCH | ||
| Nbr | Name | |
| 101 | … make up your own name | |
| 102 | … make up your own name | |
| 103 | … make up your own name | |
| 104 | … make up your own name | |
| MERCHANT | ||
| Nbr | Name | |
| 301 | … make up your own name | |
| 302 | … make up your own name | |
| 303 | … make up your own name | |
| 304 | … make up your own name |
Step by step
Solved in 3 steps
