SDSC5003 - Assignment1

#assignment #sdsc5003

Part I. ER Modelling

Part II: Creating Relational Schemas in SQL for Part I

SQL scripts

-- Create customer table
CREATE TABLE Customer (
    cid TEXT PRIMARY KEY NOT NULL,
    cname TEXT NOT NULL
);

-- Create company customer table
CREATE TABLE Company (
    cid TEXT PRIMARY KEY NOT NULL,
    street TEXT NOT NULL,
    city TEXT NOT NULL,
    FOREIGN KEY (cid) REFERENCES Customer(cid) ON DELETE CASCADE
);

-- Create individual customer table
CREATE TABLE Individual (
    cid TEXT PRIMARY KEY NOT NULL,
    gender TEXT CHECK(gender IN ('M', 'F', 'Other')) NOT NULL,
    age INTEGER CHECK(age >= 18) NOT NULL, -- 假设银行客户至少18岁
    FOREIGN KEY (cid) REFERENCES Customer(cid) ON DELETE CASCADE
);

-- Create branch table
CREATE TABLE Branch (
    branch_no TEXT PRIMARY KEY NOT NULL,
    city TEXT NOT NULL,
    street TEXT NOT NULL
);

-- Create account table
CREATE TABLE Account (
    aid TEXT PRIMARY KEY NOT NULL,
    overdraft_limit REAL NOT NULL CHECK(overdraft_limit >= 0)
);

-- Create account ownership table (handling many-to-one relationships)
CREATE TABLE Owns (
    cid TEXT NOT NULL,
    aid TEXT NOT NULL,
    opening_date DATE NOT NULL,
    PIN TEXT NOT NULL CHECK(LENGTH(PIN) >= 4), -- PIN码至少4位
    PRIMARY KEY (cid, aid),
    FOREIGN KEY (cid) REFERENCES Customer(cid) ON DELETE CASCADE,
    FOREIGN KEY (aid) REFERENCES Account(aid) ON DELETE CASCADE
);

-- Create account balance table (weak entity)
CREATE TABLE Loan (
    loan_no TEXT PRIMARY KEY NOT NULL,
    loan_type TEXT NOT NULL,
    amount REAL NOT NULL CHECK(amount > 0),
    branch_no TEXT NOT NULL,
    FOREIGN KEY (branch_no) REFERENCES Branch(branch_no) ON DELETE CASCADE
);

-- Create loan payment table (weak entity)
CREATE TABLE LoanPayment (
    loan_no TEXT NOT NULL,
    payment_no INTEGER NOT NULL,
    date DATE NOT NULL,
    amount REAL NOT NULL CHECK(amount > 0),
    PRIMARY KEY (loan_no, payment_no),
    FOREIGN KEY (loan_no) REFERENCES Loan(loan_no) ON DELETE CASCADE
);

-- Create borrows table (handling many-to-many relationships)
CREATE TABLE Borrows (
    cid TEXT NOT NULL,
    loan_no TEXT NOT NULL,
    PRIMARY KEY (cid, loan_no),
    FOREIGN KEY (cid) REFERENCES Customer(cid) ON DELETE CASCADE,
    FOREIGN KEY (loan_no) REFERENCES Loan(loan_no) ON DELETE CASCADE
);

Database Constraints Description

Customer Table

Constraint	Type	Description
PRIMARY KEY (cid)	Primary Key	Uniquely identifies the customer, ensuring each customer ID is not duplicated
NOT NULL (cid)	Not Null	Customer ID is a core identifier and must exist
NOT NULL (cname)	Not Null	Customer name is basic business information and cannot be missing

Company Table

Constraint	Type	Description
PRIMARY KEY (cid)	Primary Key	Each corporate customer exists uniquely in the table
FOREIGN KEY (cid)	Foreign Key	References the Customer table, ensuring corporate customers must exist in the main customer table first
ON DELETE CASCADE	Cascade Delete	Automatically deletes associated company information when the main customer is deleted
NOT NULL (street/city)	Not Null	Company address information is necessary business data

Individual Table

Constraint	Type	Description
PRIMARY KEY (cid)	Primary Key	Each individual customer exists uniquely in the table
FOREIGN KEY (cid)	Foreign Key	References the Customer table, ensuring individual customers must exist in the main table first
ON DELETE CASCADE	Cascade Delete	Automatically deletes associated individual information when the main customer is deleted
CHECK (gender)	Check	Gender is limited to ‘M’, ‘F’, ‘Other’ as standard values to ensure data standardization
CHECK (age >= 18)	Check	Age restriction complies with the legal minimum age requirement for bank account opening
NOT NULL	Not Null	Gender and age are necessary identity information

Branch Table

Constraint	Type	Description
PRIMARY KEY (branch_no)	Primary Key	Branch number uniquely identifies each branch office
NOT NULL	Not Null	Branch address information is necessary business data

Account Table

Constraint	Type	Description
PRIMARY KEY (aid)	Primary Key	Account ID uniquely identifies each account
CHECK (overdraft_limit >= 0)	Check	Overdraft limit cannot be negative, complying with financial business rules
NOT NULL	Not Null	Overdraft limit is a core attribute of the account

Owns Table

Constraint	Type	Description
PRIMARY KEY (cid, aid)	Composite Primary Key	Uniquely identifies the customer-account relationship, allowing one customer to own multiple accounts
FOREIGN KEY (cid)	Foreign Key	Ensures the account holder must be an existing customer
FOREIGN KEY (aid)	Foreign Key	Ensures the owned account must exist in the account table
ON DELETE CASCADE	Cascade Delete	Automatically removes the association when a customer/account is deleted
CHECK (LENGTH(PIN) >= 4)	Check	PIN length must meet the minimum security standard
NOT NULL	Not Null	Account opening date and PIN are essential security information

Loan Table

Constraint	Type	Description
PRIMARY KEY (loan_no)	Primary Key	Loan number uniquely identifies each loan
FOREIGN KEY (branch_no)	Foreign Key	Ensures the loan is associated with an existing branch
ON DELETE CASCADE	Cascade Delete	Automatically deletes associated loans when the branch is deleted
CHECK (amount > 0)	Check	Loan amount must be positive, complying with business logic
NOT NULL	Not Null	Loan type and amount are core business attributes

LoanPayment Table

Constraint	Type	Description
PRIMARY KEY (loan_no, payment_no)	Composite Primary Key	Uniquely identifies each payment for a loan
FOREIGN KEY (loan_no)	Foreign Key	Ensures the payment is associated with an existing loan
ON DELETE CASCADE	Cascade Delete	Automatically deletes associated payment records when the loan is deleted
CHECK (amount > 0)	Check	Payment amount must be positive
NOT NULL	Not Null	Payment date and amount are necessary financial information

Borrows Table

Constraint	Type	Description
PRIMARY KEY (cid, loan_no)	Composite Primary Key	Uniquely identifies the customer-loan relationship, supporting multiple borrowers for a loan
FOREIGN KEY (cid)	Foreign Key	Ensures the borrower must be an existing customer
FOREIGN KEY (loan_no)	Foreign Key	Ensures the loan must exist in the loan table
ON DELETE CASCADE	Cascade Delete	Automatically removes the borrowing relationship when a customer/loan is deleted