1、选定一个商品信息管理题目，进行需求描述。(参看教材P372) (10分)+ 2、针对系统，进行表结构设计，包括主键、非空、唯一约束。(写出 sQL语句)(30分)3、对表进行各种其他约东设计。(写出sQL语句)(10分) 4、进行表空间设计。(写出sQL语句)( 20分) 5、设计其他合适的数据库对象，如:序列、索引、视图、存储过程、触发器、函数等。(写出sQL语句)(30分)

1、选定一个商品信息管理题目，进行需求描述。

假设我们要设计一个商品信息管理系统，系统需要支持以下功能：

- 管理员可以添加、编辑和删除商品信息；
- 用户可以浏览商品信息，并根据价格、类型等条件进行筛选；
- 系统需要记录每个商品的销售历史，并能够生成销售报表；
- 系统需要支持多语言、多货币和多时区。

2、针对系统，进行表结构设计，包括主键、非空、唯一约束。(写出 SQL语句)

根据需求，我们可以设计以下表结构：

商品表：

CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  price DECIMAL(10, 2) NOT NULL,
  type VARCHAR(50) NOT NULL,
  description TEXT,
  created_at DATETIME DEFAULT NOW(),
  updated_at DATETIME DEFAULT NOW() ON UPDATE NOW(),
  UNIQUE (name)
);

销售记录表：

CREATE TABLE sales (
  id INT PRIMARY KEY AUTO_INCREMENT,
  product_id INT NOT NULL,
  quantity INT NOT NULL,
  price DECIMAL(10, 2) NOT NULL,
  currency VARCHAR(10) NOT NULL,
  sold_at DATETIME DEFAULT NOW(),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

用户表：

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  password VARCHAR(255) NOT NULL,
  created_at DATETIME DEFAULT NOW(),
  updated_at DATETIME DEFAULT NOW() ON UPDATE NOW(),
  UNIQUE (email)
);

3、对表进行各种其他约束设计。(写出 SQL语句)

根据需求，我们可以对表进行以下约束设计：

商品表：

- 非空约束：商品名称、价格、类型不能为空。

ALTER TABLE products MODIFY COLUMN name VARCHAR(255) NOT NULL;
ALTER TABLE products MODIFY COLUMN price DECIMAL(10, 2) NOT NULL;
ALTER TABLE products MODIFY COLUMN type VARCHAR(50) NOT NULL;

销售记录表：

- 非空约束：产品ID、数量、价格、货币不能为空。

ALTER TABLE sales MODIFY COLUMN product_id INT NOT NULL;
ALTER TABLE sales MODIFY COLUMN quantity INT NOT NULL;
ALTER TABLE sales MODIFY COLUMN price DECIMAL(10, 2) NOT NULL;
ALTER TABLE sales MODIFY COLUMN currency VARCHAR(10) NOT NULL;

用户表：

- 非空约束：用户名、电子邮件、密码不能为空。

ALTER TABLE users MODIFY COLUMN name VARCHAR(255) NOT NULL;
ALTER TABLE users MODIFY COLUMN email VARCHAR(255) NOT NULL;
ALTER TABLE users MODIFY COLUMN password VARCHAR(255) NOT NULL;

4、进行表空间设计。(写出 SQL语句)

根据需求，我们可以设计以下表空间：

- 商品表和销售记录表可以使用默认表空间；
- 用户表可以使用单独的表空间。

ALTER TABLE users TABLESPACE users_space;

5、设计其他合适的数据库对象，如:序列、索引、视图、存储过程、触发器、函数等。(写出 SQL语句)

根据需求，我们可以设计以下数据库对象：

- 序列：用于生成唯一的ID。

CREATE SEQUENCE product_seq;

- 索引：加速商品表的名称和类型查询。

CREATE INDEX product_name_index ON products (name);
CREATE INDEX product_type_index ON products (type);

- 视图：用于显示商品的总销售额。

CREATE VIEW sales_total AS
SELECT product_id, SUM(quantity * price) AS total
FROM sales
GROUP BY product_id;

- 存储过程：用于添加新的商品。

CREATE PROCEDURE add_product(
  IN p_name VARCHAR(255),
  IN p_price DECIMAL(10, 2),
  IN p_type VARCHAR(50),
  IN p_description TEXT
)
BEGIN
  INSERT INTO products (name, price, type, description)
  VALUES (p_name, p_price, p_type, p_description);
END;

- 触发器：用于记录商品的更新时间。

CREATE TRIGGER product_update
BEFORE UPDATE ON products
FOR EACH ROW
SET NEW.updated_at = NOW();

- 函数：用于将货币转换为本地货币。

CREATE FUNCTION convert_currency(
  amount DECIMAL(10, 2),
  from_currency VARCHAR(10),
  to_currency VARCHAR(10)
) RETURNS DECIMAL(10, 2)
BEGIN
  DECLARE exchange_rate DECIMAL(10, 2);
  -- 查询货币汇率
  SELECT rate INTO exchange_rate
  FROM exchange_rates
  WHERE source_currency = from_currency
  AND target_currency = to_currency;
  -- 计算转换后的金额
  RETURN amount * exchange_rate;
END;