在“储户”表中创建一个触发器“Del_depositor_tr”，如果存款额大于0则禁止删除此表中对应的储户。

在bankcard数据库中创建相关的表可以帮助我们存储银行卡片系统的各个信息。以下是关于三个关键表的描述： 1. Depositor储户表（也称为Customer表）: ```sql CREATE TABLE Depositor ( CustomerID INT PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50), Address VARCHAR(100), PhoneNumber VARCHAR(15), Email VARCHAR(50) ); ``` 这个表用于存储储户的基本信息，如姓名、地址、电话号码和电子邮件。 2. Account账户表: ```sql CREATE TABLE Account ( AccountNumber INT PRIMARY KEY, CustomerID INT, Balance DECIMAL(10, 2), AccountType ENUM('Savings', 'Checking'), FOREIGN KEY (CustomerID) REFERENCES Depositor(CustomerID) ); ``` 此表记录每个账户的详细信息，包括账户号、关联的储户ID、余额和账户类型（储蓄或支票账户）。 3. TRecord交易记录表: ```sql CREATE TABLE TRecord ( TransactionID INT AUTO_INCREMENT PRIMARY KEY, AccountNumber INT, TransactionDate DATE, Amount DECIMAL(10, 2), TransactionType ENUM('Deposit', 'Withdrawal'), FOREIGN KEY (AccountNumber) REFERENCES Account(AccountNumber) ); ``` 这个表用于保存每一笔交易，包括交易日期、交易金额、类型（存款或取款）以及与之关联的账户号。

(1) SQL 语句创建表： ```sql CREATE TABLE branch ( branch_name VARCHAR(50), branch_city VARCHAR(50), assets DECIMAL(10, 2), PRIMARY KEY (branch_name) ); CREATE TABLE customer ( customer_name VARCHAR(50), customer_street VARCHAR(50), customer_city VARCHAR(50), PRIMARY KEY (customer_name) ); CREATE TABLE loan ( loan_number INT, branch_name VARCHAR(50), amount DECIMAL(10, 2), PRIMARY KEY (loan_number), FOREIGN KEY (branch_name) REFERENCES branch(branch_name) ); CREATE TABLE borrower ( customer_name VARCHAR(50), loan_number INT, PRIMARY KEY (customer_name, loan_number), FOREIGN KEY (customer_name) REFERENCES customer(customer_name), FOREIGN KEY (loan_number) REFERENCES loan(loan_number) ); CREATE TABLE account ( account_number INT, branch_name VARCHAR(50), balance DECIMAL(10, 2), PRIMARY KEY (account_number), FOREIGN KEY (branch_name) REFERENCES branch(branch_name) ); CREATE TABLE depositor ( customer_name VARCHAR(50), account_number INT, PRIMARY KEY (customer_name, account_number), FOREIGN KEY (customer_name) REFERENCES customer(customer_name), FOREIGN KEY (account_number) REFERENCES account(account_number) ); ``` (2) 使用关系代数和 SQL 语句找出在 Brighton 银行中有存款的所有客户的姓名、存款号、和存款额。 关系代数： $$ \Pi_{customer\_name, account\_number, balance}(\sigma_{branch\_name='Brighton'}(depositor \bowtie account)) $$ SQL 语句： ```sql SELECT d.customer_name, d.account_number, a.balance FROM depositor d INNER JOIN account a ON d.account_number = a.account_number WHERE a.branch_name = 'Brighton'; ``` (3) 使用关系代数和 SQL 语句找出账户平均余额小于 5000 元的支行，显示支行名称及账户平均余额。 关系代数： $$ \pi_{branch\_name, avg\_balance}(\gamma_{branch\_name}(account) \div \rho_{avg\_balance}(account)) $$ $$ \pi_{branch\_name, avg\_balance}(\sigma_{avg\_balance<5000}(\pi_{branch\_name, avg\_balance}(\gamma_{branch\_name}(account) \div \rho_{avg\_balance}(account)))) $$ SQL 语句： ```sql SELECT a.branch_name, AVG(a.balance) AS avg_balance FROM account a GROUP BY a.branch_name HAVING AVG(a.balance) < 5000; ``` (4) 使用关系代数和 SQL 语句找出所有在银行中有货款但无账户的客户。 关系代数： $$ \Pi_{customer\_name}((borrower - \sigma_{account.account\_number IS NOT NULL}(depositor)) \bowtie loan) $$ SQL 语句： ```sql SELECT b.customer_name FROM borrower b INNER JOIN loan l ON b.loan_number = l.loan_number LEFT JOIN depositor d ON b.customer_name = d.customer_name WHERE d.account_number IS NULL; ``` (5) 使用关系代数和 SQL 语句对所有存款余额大于平均存款额的账户付3%的利息。 关系代数： $$ account' \leftarrow \gamma_{AVG(balance)}(account) $$ $$ account'' \leftarrow account \bowtie \rho_{interest\_rate}(3\%) $$ $$ account''' \leftarrow account'' \bowtie \gamma_{account\_number}(\sigma_{balance>AVG(balance)}(account')) $$ $$ account''' \leftarrow \sigma_{balance'=balance*1.03}(account''') $$ SQL 语句： ```sql UPDATE account SET balance = balance * 1.03 WHERE balance > ( SELECT AVG(balance) FROM account ); ```