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. List of only the Accounts that have multiple Clients associated Provide a count and total amount of Transactions for each Type Description PLEASE DONT NOT COPY THE EXISTING SOLUTION ON BARTLEBY IT IS WRONG AND DOES NOT WORK AND INCOMPLETE.  GIVEN THE BELOW: --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));   --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),        FOREIGN KEY (RefNbr) REFERENCES BankBranch (BranchNbr),        FOREIGN KEY (RefNbr) REFERENCES Merchant (MerchantNbr));

Database System Concepts
7th Edition
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Chapter1: Introduction
Section: Chapter Questions
Problem 1PE
icon
Related questions
Question

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.

List of only the Accounts that have multiple Clients associated

Provide a count and total amount of Transactions for each Type Description

PLEASE DONT NOT COPY THE EXISTING SOLUTION ON BARTLEBY IT IS WRONG AND DOES NOT WORK AND INCOMPLETE. 

GIVEN THE BELOW:

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

 

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

       FOREIGN KEY (RefNbr) REFERENCES BankBranch (BranchNbr),

       FOREIGN KEY (RefNbr) REFERENCES Merchant (MerchantNbr));

CLIENT
Client Nbr <pk>
First Name
Last Name
Street
City
Prov_State
Postal Code
Phone
Email
OWNS CLIENT_FK
OWNS
Client Nbr <pk.fk1>
Account Nbr <pk.fk2>
TRANSACTION ACCOUNT_FK
TRANSACTION
Tx Nbr
<pk>
Account Nbr <fk1>
Tx Type Code <fk2>
Ix Date
Tx Amount
Ref Nbr
BANK BRANCH
Branch Nbr <pk>
Branch Name
OWNS ACCOUNT_FK
TX_TYPE_LOOKUP_FK
MERCHANT
Merchant Nbr
Merchant Name
<pk>
ACCOUNT
Account Nbr <pk>
Balance
TX_TYPE
Tx Type Code <pk>
Tx Type Descript
Transcribed Image Text:CLIENT Client Nbr <pk> First Name Last Name Street City Prov_State Postal Code Phone Email OWNS CLIENT_FK OWNS Client Nbr <pk.fk1> Account Nbr <pk.fk2> TRANSACTION ACCOUNT_FK TRANSACTION Tx Nbr <pk> Account Nbr <fk1> Tx Type Code <fk2> Ix Date Tx Amount Ref Nbr BANK BRANCH Branch Nbr <pk> Branch Name OWNS ACCOUNT_FK TX_TYPE_LOOKUP_FK MERCHANT Merchant Nbr Merchant Name <pk> ACCOUNT Account Nbr <pk> Balance TX_TYPE Tx Type Code <pk> Tx Type Descript
Expert Solution
steps

Step by step

Solved in 2 steps

Blurred answer
Knowledge Booster
Intermediate SQL concepts
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.
Similar questions
Recommended textbooks for you
Database System Concepts
Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education
Starting Out with Python (4th Edition)
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
C How to Program (8th Edition)
C How to Program (8th Edition)
Computer Science
ISBN:
9780133976892
Author:
Paul J. Deitel, Harvey Deitel
Publisher:
PEARSON
Database Systems: Design, Implementation, & Manag…
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781337627900
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Programmable Logic Controllers
Programmable Logic Controllers
Computer Science
ISBN:
9780073373843
Author:
Frank D. Petruzella
Publisher:
McGraw-Hill Education