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

1、商品信息管理系统需求描述：

该系统主要用于管理商品的基本信息，包括商品编号、名称、描述、价格、库存、分类、品牌、产地等属性。系统应支持商品信息的添加、删除、修改和查询操作。同时，系统应具备权限管理功能，不同的用户可以有不同的权限，例如管理员可以对商品信息进行完全管理，普通员工只能进行查询和部分修改操作。系统还应具备日志记录和数据备份功能，以便于数据的安全管理和恢复。

2、表结构设计：

商品表（commodity）：

CREATE TABLE commodity (
  commodity_id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  description VARCHAR(255),
  price DECIMAL(10, 2) NOT NULL,
  stock INT NOT NULL,
  category_id INT NOT NULL,
  brand_id INT NOT NULL,
  origin VARCHAR(50),
  UNIQUE (name)
);

商品分类表（category）：

CREATE TABLE category (
  category_id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  description VARCHAR(255),
  UNIQUE (name)
);

商品品牌表（brand）：

CREATE TABLE brand (
  brand_id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  description VARCHAR(255),
  UNIQUE (name)
);

用户表（user）：

CREATE TABLE user (
  user_id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  password VARCHAR(50) NOT NULL,
  role VARCHAR(20) NOT NULL,
  UNIQUE (name)
);

操作日志表（log）：

CREATE TABLE log (
  log_id INT PRIMARY KEY,
  user_id INT NOT NULL,
  operation VARCHAR(50) NOT NULL,
  time DATETIME NOT NULL,
  description VARCHAR(255)
);

数据备份表（backup）：

CREATE TABLE backup (
  backup_id INT PRIMARY KEY,
  time DATETIME NOT NULL,
  description VARCHAR(255)
);

3、其他约束设计：

在商品表中，category_id 和 brand_id 分别为外键，参考分类表和品牌表的主键。在用户表中，role 列应该具备默认值为“普通员工”的约束条件。

ALTER TABLE commodity ADD FOREIGN KEY (category_id) REFERENCES category (category_id);
ALTER TABLE commodity ADD FOREIGN KEY (brand_id) REFERENCES brand (brand_id);
ALTER TABLE user ALTER COLUMN role SET DEFAULT '普通员工';

4、表空间设计：

为了提高系统的性能和可维护性，可以使用分区来对商品表进行表空间设计。按照商品的分类进行分区，每个分区存储一类商品的信息。

CREATE TABLESPACE ts_commodity_1
  ADD DATAFILE '/path/to/datafile/commodity_1.dbf' SIZE 100M;
CREATE TABLESPACE ts_commodity_2
  ADD DATAFILE '/path/to/datafile/commodity_2.dbf' SIZE 100M;

CREATE TABLE commodity (
  ...
) PARTITION BY RANGE (category_id) (
  PARTITION p1 VALUES LESS THAN (10) TABLESPACE ts_commodity_1,
  PARTITION p2 VALUES LESS THAN (20) TABLESPACE ts_commodity_2
);

5、其他数据库对象设计：

为了提高系统的查询效率，可以为商品表的 name 列创建索引。同时，为了方便查询，可以创建一个视图，将商品表和分类表联合查询，显示商品的分类名称。

CREATE INDEX idx_commodity_name ON commodity (name);

CREATE VIEW commodity_view AS
SELECT commodity.commodity_id, commodity.name, commodity.description,
       commodity.price, commodity.stock, category.name AS category_name,
       brand.name AS brand_name, commodity.origin
FROM commodity
JOIN category ON commodity.category_id = category.category_id
JOIN brand ON commodity.brand_id = brand.brand_id;

为了保证数据的完整性和安全性，可以创建一个触发器，对商品表进行修改时进行检查，确保价格和库存列的值不为负数。

CREATE TRIGGER check_commodity BEFORE UPDATE ON commodity
FOR EACH ROW
BEGIN
  IF NEW.price < 0 THEN
    SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = '价格不能为负数';
  END IF;
  IF NEW.stock < 0 THEN
    SIGNAL SQLSTATE '45002' SET MESSAGE_TEXT = '库存不能为负数';
  END IF;
END;

为了方便管理，可以创建一个存储过程，实现商品信息的添加操作，并对操作进行日志记录。

CREATE PROCEDURE add_commodity (
  IN p_name VARCHAR(50),
  IN p_description VARCHAR(255),
  IN p_price DECIMAL(10, 2),
  IN p_stock INT,
  IN p_category_id INT,
  IN p_brand_id INT,
  IN p_origin VARCHAR(50),
  IN p_user_id INT,
  OUT p_commodity_id INT
)
BEGIN
  INSERT INTO commodity (name, description, price, stock, category_id, brand_id, origin)
  VALUES (p_name, p_description, p_price, p_stock, p_category_id, p_brand_id, p_origin);
  SET p_commodity_id = LAST_INSERT_ID();
  INSERT INTO log (user_id, operation, time, description)
  VALUES (p_user_id, '添加商品', NOW(), CONCAT('商品编号:', p_commodity_id));
END;

为了方便查询，可以创建一个函数，实现根据商品名称模糊查询的功能。

CREATE FUNCTION search_commodity (p_name VARCHAR(50))
RETURNS TABLE (
  commodity_id INT,
  name VARCHAR(50),
  description VARCHAR(255),
  price DECIMAL(10, 2),
  stock INT,
  category_id INT,
  brand_id INT,
  origin VARCHAR(50)
)
BEGIN
  RETURN (
    SELECT commodity_id, name, description, price, stock, category_id, brand_id, origin
    FROM commodity
    WHERE name LIKE CONCAT('%', p_name, '%')
  );
END;