PostgreSQL

Chapter 5 - DDL (Data Definition Language)

DDL ASSIGNMENT1 - Banking Application System

Objective:

You are required to address a set of Data Definition Language (DDL) for a Banking Application System. Each query focuses on distinct DDL tasks, including the creation of tables, application of constraints, and establishment of relationships between tables.

Requirements:

You will need to complete at least 20 different DDL tasks, covering table creation, primary keys, foreign keys, constraints, and indexes. Each task represents a key part of designing the structure of the Banking Application System.

Question 1: Create the CITY Table with Constraints

Write a DDL SQL statement to create an CITY table:

city_id as a primary key with auto-increment functionality.
country_name as a string with a maximum length of 50 characters, and not null.
city_name as a string with a maximum length of 50 characters, not null.
created_at as a datetime field, default to system timestamp, not null.
Add a UNIQUE constraint on country_name + city_name to ensure that there are no duplicates.

Question 2: Create the BRANCH Table with Default Values

Create a BRANCH table:

branch_id as a primary key with auto-increment functionality.
branch_name as a string with a maximum length of 255 characters, not null.
branch_location as a string with a maximum length of 255 characters.
city_id as a foreign key referencing the CITY table.
Add a default value for branch_location as 'Main Branch'.

Question 3: Define the EMPLOYEE Table with Relationships

Write the SQL statement to create an EMPLOYEE table that includes:

employee_id as a primary key with auto-increment functionality.
first_name as strings, not null.
last_name as strings, not null.
hire_date as a date field, not null.
salary as a decimal, not null.
branch_id as a foreign key referencing the BRANCH table.
city_id as a foreign key referencing the CITY table.
created_at as a date field, not null.

Question 4: Create the CUSTOMER Table

Write DDL SQL statement to create a CUSTOMER table with the following requirements:

customer_id as a primary key with auto-increment functionality.
first_name as a string of maximum length 255 characters, not null.
last_name as a string of maximum length 255 characters, not null.
gender as a char of maximum length 1, not null ('M' or 'F').
ID_type as a string of maximum length 50 characters (e.g., "passport" or "driving license"), not null.
ID_number as a string of maximum length 255 characters (passport number or others), not null.
email as a string, not null.
phone_number as a string, not null.
address as a string, not null.
city_id as a foreign key referencing the CITY table.
created_at as a datetime field, default to system timestamp, not null.
Add a CHECK constraint to ensure that the gender is either 'M' or 'F'.

Question 5: Create the ACCOUNT Table with Constraints

Create an ACCOUNT table with the following specifications:

account_id as a primary key with auto-increment functionality.
account_type as a variable character string, not to exceed 50 characters, and cannot be null.
balance as a decimal with precision (12, 2), not null.
customer_id as a foreign key referencing the CUSTOMER table, not null.
created_at as a datetime field, default to system timestamp, not null.
Add a CHECK constraint to ensure that the balance is greater than or equal to 0.
Add a CHECK constraint to ensure that the account_type is either savings or checking or loan account.

Question 6: Create the CARD Table

Write a DDL SQL statement to create a CARD table with the following details:

card_id as the primary key with auto-increment functionality.
card_number as a string with a maximum length of 16 characters, unique and not null.
card_type as a string with a maximum length of 50 characters (e.g., 'credit', 'debit'), not null.
expiration_date as a date field, not null.
max_credit_limit maximum allowed limit in case of a credit card, not null.
available_credit_limit current available limit in case of a credit card, not null.
account_id as a foreign key referencing the ACCOUNT table, not null.
created_at as a datetime field, default to system timestamp, not null.

Question 7: Create the TRANSACTION Table

Define a TRANSACTION table with the following details:

transaction_id as a primary key with auto-increment functionality.
transaction_date as a date field, defaulted to the current date, not null.
amount as a decimal (10, 2), not null.
transaction_type as a string with a maximum length of 50 characters (e.g., "debit" or "credit"), not null.
payment_mode as a string with CHECK constraint ('ATM', 'cash deposit', 'loan payment', 'fund transfer', 'card' fees', 'credit card'), not null.
account_id as a foreign key referencing the ACCOUNT table, with a maximum length of 50 characters, not null.
transaction_status as a string with a maximum length of 150 characters.
description as a datetime field, default to system timestamp, not null.
Add a CHECK constraint to ensure the transaction type is either 'debit' or 'credit'.
Add a CHECK constraint to ensure the transaction_status is either 'processing' or 'declined' or 'completed'.

Question 8: Create the ACCOUNT_HISTORY Table

Write a DDL SQL statement to create an ACCOUNT_HISTORY table with the details:

history_id as the primary key with auto-increment functionality.
account_id as a foreign key referencing the ACCOUNT table, not null.
balance_before as a decimal, not null.
balance_after as a decimal, not null.
latest_record as a boolean, default true, not null.
transaction_id as a foreign key referencing the TRANSACTION table, not null.
created_at as a datetime field, default to system timestamp, not null.

Question 9: Create the LOAN Table with a Foreign Key

Define a LOAN table:

loan_id as a primary key with auto-increment functionality.
loan_amount as a decimal, not null.
number_of_monthly_instalments numeric, not null.
interest_rate as a decimal (5, 2), not null.
loan_start_date as a date field, not null.
loan_end_date as a date field, nullable.
account_id as a foreign key referencing the ACCOUNT table, not null.
created_at as a datetime field, default to system timestamp, not null.
created_employee_id foreign key referencing table, employee who issued the loan.

Question 10: Define the LOAN_INSTALMENTS Table

Write the SQL statement to create a one-to-many relationship between loan and its instalments. This will require the LOAN_INSTALMENTS table, which includes:

instalment_id as a primary key with auto-increment functionality.
loan_id as a foreign key referencing the LOAN table, not null.
instalment_amount monthly loan payment amount, not null.
due_date 1st day of a given month, not null.
paid_status boolean, default false not null.
created_at as a datetime field, default to system timestamp, not null.

Question 11: Create the LOAN_PAYMENT Table

Define a LOAN_PAYMENT table with the following specifications:

instalment_id as a foreign key referencing the LOAN table, not null.
transaction_id as a foreign key referencing the TRANSACTION table, not null.
Setup composite primary key with instalment_id and transaction_id

Question 12: Create the BENEFICIARY Table with Constraints

Write a DDL SQL statement to create an BENEFICIARY table:

beneficiary_id as a primary key with auto-increment functionality.
primary_consumer as a foreign key referencing the CONSUMER table, not null.
beneficiary_bank beneficiary bank name, not null.
beneficiary_name beneficiary full name, not null.
beneficiary_account_number beneficiary bank account number, not null.
beneficiary_swift beneficiary bank swift number.
beneficiary_iban beneficiary bank iban number.
nick_name give by consumer while adding a beneficiary.
created_at as a datetime field, default to system timestamp, not null.

Question 13: Create the FUND_TRANSFER Table with Constraints

Write a DDL SQL statement to create an FUND_TRANSFER table:

fund_transfer_id as a primary key with auto-increment functionality.
beneficiary_id as a foreign key referencing the BENEFICIARY table, not null.
transaction_id as a foreign key referencing the TRANSACTION table, not null.
refund_transaction_id as a foreign key referencing the TRANSACTION table, null
transfer_timestamp transaction date.
transfer_status as a string with CHECK constraint (e.g., 'processing', 'failed', 'completed'), not null.
created_at as a datetime field, default to system timestamp, not null.

Question 14: Create the CUSTOMER_LOGIN Table

Write a DDL SQL statement to create an CUSTOMER_LOGIN table (one-to-one relationship with customer table):

customer_id as a foreign key referencing the CUSTOMER table, not null.
login_id can use alpha numeric login id or email as login id, not null.
password must encrypt the password before storing it in the database table.
active_flag as numeric, 0 or 1, you can lock the customer login when required, not null.
last_login_datetime as timestamp, last successful login date for a given customer.
created_at as a datetime field, default to system timestamp, not null.

Question 15: Create the AUDIT_LOG Table

Define an AUDIT_LOG table to track changes in the banking system:

log_id as a primary key with auto-increment functionality.
log_date as a date field, not null.
action as a string to describe the action performed.
employee_id as a foreign key referencing the EMPLOYEE table.

Question 16: Create the OVERDRAFT_POLICY Table with Constraints

Write a DDL SQL statement to create an OVERDRAFT_POLICY table:

policy_id as a primary key with auto-increment functionality.
max_overdraft_limit as a decimal, not null.
Add a CHECK constraint to ensure the max_overdraft_limit is greater than or equal to 0.
interest_rate as a decimal (5, 2), not null.
created_at as a datetime field, default to system timestamp, not null.

Question 17: Rename the OVERDRAFT_POLICY Table

Write a SQL statement to rename the OVERDRAFT_POLICY table to COMPANY_POLICY.

Question 18: Drop the COMPANY_POLICY Table

Write a SQL statement to drop the COMPANY_POLICY table.

Question 19: Add Indexes to the CUSTOMER and ACCOUNT Tables

Write SQL statements to:

Add an index on the last_name column in the CUSTOMER table.
Add an index on the account_type column in the ACCOUNT table.

Question 20: Add a CHECK Constraint on the ACCOUNT Table

Write a SQL statement to add a CHECK constraint to the ACCOUNT table ensuring that the balance is greater than or equal to 0.

Question 21: Add a Primary Key to existing table

Add a primary key to the CUSTOMER_LOGIN table on customer_id column.

Question 22: Add a Foreign Key to the PAYMENT Table

Write a SQL statement to add a foreign key in the PAYMENT table to reference the ACCOUNT table.

Question 23: Add a Column to the EMPLOYEE Table

Write a SQL statement to add a phone_number column of type VARCHAR(15) to the EMPLOYEE table.

Question 24: Alter the CUSTOMER Table to Add a Foreign Key Column

Write a SQL statement to add a branch_id foreign key column to the CUSTOMER table, referencing the BRANCH table.

Question 25: Alter a Column in the CUSTOMER Table

Write a SQL statement to alter the email column in the CUSTOMER table, increasing its length to 255 characters.

Question 26: Drop a Column from the LOAN Table

Write a SQL statement to drop the loan_end_date column from the LOAN table.

Question 27: Drop an Index from the ACCOUNT Table

Write a SQL statement to drop the index on the account_type column in the ACCOUNT table.

Question 28: Add a default Current timestamp constraint on the EMPLOYEE Table

Write a SQL statement to add a current timestamp DEFAULT constraint to the EMPLOYEE table on the created_at column.

Question 29: Add a UNIQUE Constraint on the CUSTOMER Table

Write a SQL statement to add a UNIQUE constraint to the CUSTOMER table ensuring that the combination of id_type&id_number is not repeated.

Question 30: Enforce UNIQUE constraints on all applicable tables.

Apply UNIQUE constraints across the entire database wherever duplicate data is not permitted.

GOOD LUCK WITH YOUR ASSIGNMENT!!!

Don't forget to Contact us if you need any further assistance with your assignments, and most importantly, for a manual review and approval of your work.