Part 2  

Project 2 Financial Data Transactions

 

 

Over the course of a single day, billions of financial transactions occur. There is an important need to keep track of all the data for the financial firms, the customers who buy and sell, and the government who needs to makes sure that everything is running by the law.

 

Customers:

The customer database needs to have the customer name, product name and item bought. The above is a foreign key Customer_ID. Then there is needs to be place for the customer’s holding positions as well as the remaining positions.

There then needs to be a primary key with Payment Method (Payment_Method_Code). This is used to show many different banks that the customer needs to fund their account.

 

 

Financial Firms:

When a financial transaction occurs, there are least two counterparties: the multiple buyers and the multiple sellers called Customer_ID. Then there needs a place for price to make sure the amount is bought and sold is listed to the customer who did those things.

 

Government:

The government needs to make sure that that taxes are collected from the sale of securities. The dates of these transactions are needed to make sure that the transactions happened during the same calendar year.

 

 

 

Customers

 

 

 

Primary keys:Customer_ID

 

Foreign keys: Account_ID-references Account ID in government table,

Indices: CREATE INDEX Customer_name

ON Customers (Customer_name); UPDATE INDEX Customer_name

ON Customers (Customer_name); DELETE INDEX Customer_name

ON Customers (Customer_name);

 

Financial Firms

 

Primary Keys: Transaction_ID

 

Foreign Keys Customer_ID-references Customer_ID in customers table

Indices: CREATE INDEX Item_sold_name ON Financial Firms (Item_sold_name);

UPDATE INDEX Item_sold_name ON Financial Firms (Item_sold_name); DELETE INDEX Customer_name

ON Financial Firms (Item_sold_name);

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Government

 

 

Primary Keys: Account_ID

 

Foreign Keys Tranaction_ID-references Transaction ID in Finacial firms

Indices: CREATE INDEX Account_name ON Government (Account_name);

UPDATE INDEX Transaction_Type ON Government (Transaction_Type);

DELETE INDEX Transaction_Type ON Government (Transaction_Type);

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

User 1:

Query 1:Name:abc

Question:  Select payment method code and product_name

 

 

 

 

 

 

 

 

 

 

 

Query 2:

Select customer name where transaction id is given

 

 

 

 

 

 

 

 

 

 

 

 

Query 3:

 

 

 

 

 

 

 

 

                                                                                       

 

 

User:2

Select  product name and item bought name

 

 

 

 

 

 

 

 

 

 

 

Select price and item sold

 

 

 

 

 

 

 

 

 

 

 

Select account name where transaction id given

 

 

 

 

 

 

 

 

 

 

 

 

User:3

Select payment method code where customer id of finance is given

 

 

 

 

 

 

 

 

 

 

 

Select all from financial after input id

 

 

 

 

 

 

 

 

 

 

Select account name where transact id input