Lab: Advanced SQL DDL

In this lab, you will modify an existing PostgreSQL database schema.

Instructions

You can work on this assignment either individually or with a partner.

Task 1: Load Credit Card Database in PostgreSQL

Run a PostgreSQL container, and load the 2024-02-15.pg_dump database backup from the Docker lab.

Verify the backup was properly restored.

Task 2: Add Balance View

The bonus question for the SQL DML lab required calculating credit card balances:

SELECT cc_creditcard.number,
COALESCE(transactions + interest - payments, 0.0) AS balance
FROM cc_creditcard LEFT JOIN
(
SELECT card_number, SUM(amount) AS transactions
FROM cc_transaction
GROUP BY card_number
) AS transactions ON cc_creditcard.number = transactions.card_number
LEFT JOIN
(
SELECT card_number, SUM(amount) AS interest
FROM cc_interest
GROUP BY card_number
) AS interest ON cc_creditcard.number = interest.card_number
LEFT JOIN
(
SELECT card_number, SUM(amount_paid) AS payments
FROM cc_payment
GROUP BY card_number
) AS payments ON cc_creditcard.number = payments.card_number;

Save this query in a view: cc_balance_view

Task 3: Add Constraints

Next, add constraints to ensure these columns are non-negative:

• cc_payment.amount_due
• cc_payment.amount_paid
• cc_interest.amount
• cc_transaction.amount

Hint: Create and use a custom domain.

Submit

Upload view.sql and constraints.sql to Gradescope:

• view.sql should contain your CREATE VIEW statement
• constraints.sql should contain your CREATE DOMAIN and ALTER TABLE statements

This will be graded for completion as part of your attendance and participation grade.