/postgres_credit_card_database

Tiny credit card company with following table : customer, vendor, transaction

Primary LanguagePLpgSQL

Tiny credit card company

Postgres database assignment



This project contains nine documents, contain each a function for a specific task?

File 1 filedata.sql

Contains the database and three table creation functions

File 2 p1.sql

**Program 1:**displays data of all the transactions of a given customer. For each transaction, the data to display include vendor name, date, and amount. Write the program as a function that accepts a customer name as a parameter, and displays transactions of the customer.

File 3 p2.sql

Program 2: displays data of the customers who have transactions with a given vendor. The data include customer numbers, customer names, and provinces. Write the program as a function that accepts a vendor name as a parameter and displays data of customers.

File 4 p3.sql

Program 3: inserts a new customer record (tuple). Write this program as a function, which takes data of the customer as parameters and stores the data into the customer table. It then displays all the customer records. The balance of the new customer should be zero (0.00).

File 5 p4.sql

Program 4 displays the most recent transaction of every customer. The program displays account number, customer name, amount, and vendor name. If a customer has no transaction (e.g. the new one), the program should display “no transaction”.

File 6 p5.sql

Program 5 calculates the total amount of transactions of every vendor in the transaction table, and add the total amount to the vendor’s current balance. The program then displays vendor numbers, vendor names and the new balances.

File 7 p6.sql

Program 6 charges each vendor a service fee that is 2.5% of the vendor’s balance, and subtracts the service fee from the balance. The program then displays the name of each vendors, the fee charged, and the new balance.

File 8 p7.sql

Program 7: charges a service fee for each customer whose current balance is greater than the credit limit and add the charge to the balance. The service fee is 10% of the portion over the credit limit. The program then displays the name of each of such customers and the new balance.

File 9 p8.sql

Program 8: adds a new transaction. Each time the program is executed, it takes a vendor number, an account number, and an amount from the user. The program first checks the validity of the vendor number and account number. The transaction number is generated by the program. The date of the transaction should be the date on which the program is executed and assigned by the computer automatically. The program stores the new transaction into the transaction table, it then updates the balances of the related customer and vendor with the amount of the new transaction. It then displays the new transaction, and the updated customer and vendor records.

Running this project?

Installation

Before you get started with using PostgreSQL, you'll have to install it. Follow these steps to get started:

MacOS

  1. There are a couple of ways to install PostgreSQL. One of the easier ways to get started is with Postgres.app. Navigate to https://postgresapp.com/ and then click "Download": download

  2. Once it's finished downloading, double click on the file to unzip then move the PostgreSQL elephant icon into your applications folder. Double click the icon to launch the application.

  3. You should now see a new window launched with a list of servers to the left side of the window (if it's a fresh install, you should see one named PostgreSQL XX). If it shows anything else or an error props up, make sure you don't have any other instances of Postgres on your computer and reinstall. To fully reinstall follow these steps to delete data directories and preferences. Click on the button 'Initialize' (or 'Start' if you had already installed previously). download

  4. Run sudo mkdir -p /etc/paths.d && echo /Applications/Postgres.app/Contents/Versions/latest/bin | sudo tee /etc/paths.d/postgresapp (found here) to use psql in the terminal. Close and open the terminal.

  5. Postgres.app will by default create a role and database that matches your current macOS username. You can connect straight away by running psql.

  6. You should then see something in your terminal that looks like this (with your macOS username in front of the prompt rather than 'postgres'):

terminal

  1. You should now be all set up to start using PostgreSQL. For documentation on command line tools etc see https://postgresapp.com/documentation/

Ubuntu

Digital Ocean have got a great article on getting started with postgres. A quick summary is below.

Installation
sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

By default the only role created is the default 'postgres', so PostgreSQL will only respond to connections from an Ubuntu user called 'postgres'. We need to pretend to be that user and create a role matching our actual Ubuntu username:

sudo -u postgres createuser --interactive

This command means 'run the command createuser --interactive as the user called "postgres"'.

When asked for the name of the role enter your Ubuntu username. If you're not sure, open a new Terminal tab and run whoami.

When asked if you want to make the role a superuser, type 'y'.

We now need to create the database matching the role name, as PostgreSQL expects this. Run:

sudo -u postgres createdb [your user name]

You can now connect to PostgreSQL by running psql.

Executing the programs

Step-by-step guide

  1. In the object browser on the left, double-click on PostgesSQL x.x,
  2. double-click on Databases,
  3. and double-click on esp_mdphnet.
  4. In the taskbar at the top of the screen, click the SQL button. A new Query window will open.
  5. Click the green triangle (Execute Query) to run the query.