数据库应用系统设计

1、实验目的
  熟练掌握概念结构设计和逻辑结构设计步骤，建立和操作数据库。
2、实验内容与要求
（1）根据已有信息，设计概念模型
（2）将概念模型转换为关系模型
（3）创建数据表，填入信息
（4）设计查询语句，完成查询任务。
要求：给出完整的步骤和截图。
3、实验环境
Microsoft SQL Server (Express) + SQL Server Management Studio

已有信息

设有一家百货商店，已知信息如下：
1）每个职工的数据是职工号、姓名、地址和他所在的商品部。
2）每一商品部的数据有：它的职工，经理和它经销的商品。
3）每种经销的商品数据有：商品名、生产厂家、价格、型号（厂家定的）和内部商品代号（商店规定的）。
4）每个生产厂家的数据有：厂名、地址、向商店提供的商品价格。

实验步骤

1）根据已知信息，设计该百货商店的概念模型 （给出E-R图）
2）将概念模型转换为关系模型。（给出关系模型）
3）详细列出设计了几张表，各个表所含的字段，以及字段采用的数据类型。有些字段要有约束（价格大于0等）。
3）用SQL创建数据表，填入信息，每张表填入的信息不少于8条。（建表语句以及结果截图）
4）用SQL语言进行以下数据查询操作：（查询语句以及结果截图）
      a) 查询所有经理的姓名
      b) 查询某商品部交涉的厂家列表。

实验过程

①    根据给出的信息，可以设计绘制出ER图

ER图

② 将上面ER概念模型转换为关系模型
职工（职工号，姓名，地址，所在部门）
商品部（部门号，部门名称，经理）
商品（商品代号，商品名，生产厂家，价格，型号，商品部）
厂家（厂家代号，厂名，地址，提供价格）

③ 根据模型设计数据表，具有详细的字段名称、数据类型以及数据的约束
共设计了4张表，分别是职工表、商品部表、商品表、厂家表

职工表：

字段名 数据类型 长度 主键 外键 约束
职工号 char 10 是 否 非空
姓名 varchar 20 否 否 非空
地址 varchar 50 否 否 非空
所在部门 char 10 否 是 非空，参照商品部表的部门号

商品部表：

字段名 数据类型 长度 主键 外键 约束
部门号 char 10 是 否 非空
部门名称 varchar 20 否 否 非空
经理 varchar 20 否 否 非空

商品表：

字段名 数据类型 长度 主键 外键 约束
商品代号 char 10 是 否 非空
商品名 varchar 20 否 否 非空
生产厂家 char 10 否 是 非空，参照厂家表的厂家代号
价格 decimal 10,2 否 否 非空，大于0
型号 varchar 20 否 否 非空
部门号 char 10 否 是 非空，参照商品部表的部门号

厂家表：

字段名 数据类型 长度 主键 外键 约束
厂家代号 char 10 是 否 非空
厂名 varchar 20 否 否 非空
地址 varchar 50 否 否 非空
提供价格 decimal 10,2 否 否 非空，大于0

④ 用SQL创建数据表，填入模拟的数据信息

  1. 职工表：
    1
    2
    3
    4
    5
    6
    7
    -- 创建职工表
    CREATE TABLE employee (
      emp_id char(10) NOT NULL PRIMARY KEY,
      emp_name varchar(20) NOT NULL,
      emp_address varchar(50) NOT NULL,
      dept_id char(10) NOT NULL REFERENCES department(dept_id)
    );
    职工表结构
1
2
3
4
5
6
7
8
9
10
-- 填入职工信息
INSERT INTO employee VALUES
('E001', '张三', '北京市朝阳区', 'D001'),
('E002', '李四', '上海市浦东新区', 'D002'),
('E003', '王五', '广州市天河区', 'D003'),
('E004', '赵六', '深圳市南山区', 'D004'),
('E005', '孙七', '杭州市西湖区', 'D001'),
('E006', '周八', '南京市鼓楼区', 'D002'),
('E007', '吴九', '成都市武侯区', 'D003'),
('E008', '郑十', '重庆市渝北区', 'D004');

职工表数据

  1. 商品部表
    1
    2
    3
    4
    5
    6
    -- 创建商品部表
    CREATE TABLE department (
      dept_id char(10) NOT NULL PRIMARY KEY,
      dept_name varchar(20) NOT NULL,
      dept_manager varchar(20) NOT NULL
    );
    商品部表结构
1
2
3
4
5
6
7
8
9
10
-- 填入商品部信息
INSERT INTO department VALUES
('D001', '服装部', '张三'),
('D002', '电器部', '李四'),
('D003', '食品部', '王五'),
('D004', '化妆品部', '赵六'),
('D005', '生鲜部', '孙七'),
('D006', '家庭用品部', '周八'),
('D007', '鞋帽部', '吴九'),
('D008', '首饰部', '郑十');

商品部表数据

  1. 商品表：
    1
    2
    3
    4
    5
    6
    7
    8
    9
    -- 创建商品表
    CREATE TABLE product (
      prod_id char(10) NOT NULL PRIMARY KEY,
      prod_name varchar(20) NOT NULL,
      prod_maker char(10) NOT NULL REFERENCES manufacturer(maker_id),
      prod_price decimal(10,2) NOT NULL CHECK (prod_price > 0),
      prod_model varchar(20) NOT NULL,
      prod_dept char(10) NOT NULL REFERENCES department(dept_id)
    );
    商品表结构
1
2
3
4
5
6
7
8
9
10
-- 填入商品信息
INSERT INTO product VALUES
('P001', '牛仔裤', 'M001', 199.00, 'A001', 'D001'),
('P002', 'T恤', 'M005', 99.00, 'A002', 'D001'),
('P003', '电饭煲', 'M002', 299.00, 'B001', 'D002'),
('P004', '电水壶', 'M006', 99.00, 'B002', 'D002'),
('P005', '巧克力', 'M003', 19.90, 'C001', 'D003'),
('P006', '蛋糕', 'M007', 39.90, 'C002', 'D003'),
('P007', '口红', 'M004', 299.00, 'D001', 'D004'),
('P008', '香水', 'M008', 399.00, 'D002', 'D004');

商品表数据

  1. 厂家表：
    1
    2
    3
    4
    5
    6
    7
    -- 创建厂家表
    CREATE TABLE manufacturer (
      maker_id char(10) NOT NULL PRIMARY KEY,
      maker_name varchar(20) NOT NULL,
      maker_address varchar(50) NOT NULL,
      maker_price decimal(10,2) NOT NULL CHECK (maker_price > 0)
    );
    厂家表结构
1
2
3
4
5
6
7
8
9
10
-- 填入厂家信息
INSERT INTO manufacturer VALUES
('M001', '优衣库', '日本东京', 150.00),
('M002', '美的', '广东佛山', 200.00),
('M003', '费列罗', '意大利米兰', 15.00),
('M004', '迪奥', '法国巴黎', 180.00),
('M005', '耐克', '美国俄勒冈', 90.00),
('M006', '九阳', '江苏常州', 80.00),
('M007', '好利来', '中国北京', 20.00),
('M008', '博柏利', '英国伦敦', 299.00);

厂家表数据

总体结构:
总体结构

⑤ 用SQL语言进行以下数据查询操作
a) 查询所有经理的姓名

1
2
-- a) 查询所有经理的姓名
SELECT dept_manager FROM department;

查询结果

b) 查询某商品部交涉的厂家列表

1
2
3
4
5
6
7
8
9
10
-- b) 查询某商品部交涉的厂家列表
-- 这里以D001服装部为例
SELECT maker_name FROM manufacturer
WHERE maker_id IN (
  SELECT prod_maker FROM product
  WHERE prod_id IN (
    SELECT prod_id FROM product
    WHERE prod_dept = 'D001'
  )
);

查询结果