在“储户”表中创建一个触发器“Del_depositor_tr”,如果存款额大于0则禁止删除此表中对应的储户。
时间: 2024-03-25 09:38:59 浏览: 20
在 SQL 中,可以使用如下语句创建一个名为“Del_depositor_tr”的触发器:
```
CREATE TRIGGER Del_depositor_tr
BEFORE DELETE ON 储户
FOR EACH ROW
BEGIN
IF OLD.存款额 > 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = '该储户有存款,不能删除!';
END IF;
END;
```
这个触发器将在每次从“储户”表中删除一行数据之前被触发。如果被删除的行的“存款额”大于0,则触发器会产生一个 SQLSTATE 为 '45000' 的信号,并将 MESSAGE_TEXT 设为 '该储户有存款,不能删除!',从而阻止删除操作。
相关问题
已知银行企业的数据库由以下表组成: 0分行表 branch(branch-name, branch-city, assets) 容户表 customer(Customer-name, customer-street, customer-city ) 货款明细表 loan(loan-number, branch-name, amount) 客户货款表 borrower(customer-name, loan-number) 存款明细表 account(account-number, branch-name, balance ) 客户存款表 depositor(customer-name, account-number) 注:带下划线的属性为主码,假设客户的名宇不相同 完成如下操作: (1)用SQL 语句创建表。 (2)使用关系代数和 SQL 语句找出;在 Brighton 银行中有存款的所有客户的姓名、存款号、和存款额。 (3)使用关系代数和 SQL 语句找出账户平均余额小于 5000 元的支行,显示支行名称及账户平均余额。 (4)使用关系代数和 sQL 语句找出所有在银行中有货款但无账户的客户。 (5) 使用关系代数和 SQL 语句对所有存款余额大于平均存款额的账户付3%的利息。
(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
);
```
已知银行企业的数据库由以下表组成: ①分行表 branch(branch-name, branch-city, assets) ②客户表 customer(customer-name , customer-street, customer-city ) ③贷款明细表 loan(loan-number, branch-name, amount) ④客户贷款表 borrower(customer-name, loan-number) ⑤存款明细表 account(account-number, branch-name, balance ) ⑥客户存款表 depositor(customer-name, account-number) 注:带下划线的属性为主码,假设客户的名字不相同 完成如下操作: (1)用SQL语句创建表。 (2)使用关系代数和SQL语句找出在Brighton银行中有存款的所有客户的姓名、存款号、和存款额。 (3)使用关系代数和SQL语句找出账户平均余额小于5000元的支行,显示支行名称及账户平均余额。 (4)使用关系代数和SQL语句找出所有在银行中有贷款但无账户的客户。 (5)使用关系代数和SQL语句对所有存款余额大于平均存款额的账户付3%的利息。
很抱歉,我是一个自然语言处理AI,无法直接执行SQL语句或关系代数操作。但我可以给你提供一些参考:
(1)用SQL语句创建表。
```sql
CREATE TABLE branch (
branch_name VARCHAR(255) PRIMARY KEY,
branch_city VARCHAR(255),
assets DECIMAL(10,2)
);
CREATE TABLE customer (
customer_name VARCHAR(255) PRIMARY KEY,
customer_street VARCHAR(255),
customer_city VARCHAR(255)
);
CREATE TABLE loan (
loan_number INT PRIMARY KEY,
branch_name VARCHAR(255),
amount DECIMAL(10,2)
);
CREATE TABLE borrower (
customer_name VARCHAR(255),
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 PRIMARY KEY,
branch_name VARCHAR(255),
balance DECIMAL(10,2)
);
CREATE TABLE depositor (
customer_name VARCHAR(255),
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 customer_name, account_number, balance
FROM depositor
JOIN account ON depositor.account_number = account.account_number
WHERE account.branch_name = 'Brighton';
```
(3)使用关系代数和SQL语句找出账户平均余额小于5000元的支行,显示支行名称及账户平均余额。
关系代数:
$$\pi_{branch\_name,avg(balance)}(\rho_{account2}(account) \bowtie_{account1.branch\_name = account2.branch\_name} \gamma_{account1.branch\_name,avg(balance)}(account as account1))$$
$$\sigma_{avg(balance) < 5000}(account2)$$
SQL语句:
```sql
SELECT account1.branch_name, AVG(account2.balance) AS avg_balance
FROM account AS account1
JOIN account AS account2 ON account1.branch_name = account2.branch_name
GROUP BY account1.branch_name
HAVING AVG(account2.balance) < 5000;
```
(4)使用关系代数和SQL语句找出所有在银行中有贷款但无账户的客户。
关系代数:
$$\pi_{customer\_name}(\sigma_{borrower.loan\_number = loan.loan\_number \land account.branch\_name \text{ is null}}(borrower \bowtie loan \bowtie account))$$
SQL语句:
```sql
SELECT borrower.customer_name
FROM borrower
JOIN loan ON borrower.loan_number = loan.loan_number
LEFT JOIN account ON loan.branch_name = account.branch_name
WHERE account.account_number IS NULL;
```
(5)使用关系代数和SQL语句对所有存款余额大于平均存款额的账户付3%的利息。
关系代数:
$$account' \leftarrow \sigma_{balance > avg(balance)}(account)$$
$$account' \leftarrow \rho_{account2}(account')$$
$$account' \leftarrow account' \cup \gamma_{avg(balance)}(account')$$
$$account' \leftarrow \rho_{account3}(account')$$
$$account' \leftarrow account' \bowtie_{account2.branch\_name = account3.branch\_name}(account2 \times account3)$$
$$account' \leftarrow \sigma_{interest\_rate = 0.03}(account')$$
$$account \leftarrow account \cup account'$$
SQL语句:
```sql
UPDATE account
SET balance = balance * 1.03
WHERE balance > (SELECT AVG(balance) FROM account);
```