一、实验项目名称：数据库设计实验
二、实验环境
openGauss数据库、Windows 11操作系统、eclipse java IDE
三、实验内容
本实验旨在设计并实现一个关系数据库系统，用于管理一家国产汽车公司的业务数据。该系统将涉及数据库的概念设计、逻辑设计、实施、操作和维护，以及相关应用程序的开发。选定的汽车公司（例如比亚迪、长城、吉利或奇瑞）需处理包括车辆、品牌、车型、配置、经销商、供应商和客户信息等数据。
- 实体联系模型（E-R模型）
车辆实体：包含唯一的车辆识别号（VIN），以及与品牌、车型和配置相关的属性。
品牌实体：描述公司旗下不同品牌的信息。
车型实体：每个品牌下的具体车型信息。
配置实体：车辆的详细配置信息，如颜色、发动机排量、变速器等。
经销商实体：经销商的基本信息及其销售记录。
供应商实体：供应零件的供应商信息及其供应的零件详情。
客户实体：购车客户的个人信息及购买记录。
- 关系模型
从E-R模型到关系模型的转换：将E-R模型转换为初始的关系模型，并根据规范化理论对其进行优化。
数据库实施：基于OPENGAUSS平台创建关系数据库，定义表结构、索引和约束。
- 数据初始化
数据生成：模拟或抓取真实数据，生成初步测试数据，填充到数据库中。
数据校验：确保数据的完整性和有效性。
- 查询和数据分析
销售趋势分析：展示过去三年各品牌的销售趋势，按年、月分列，并按购买者性别和收入范围分类。
缺陷追踪：根据供应商信息，追踪存在缺陷的变速器，并找出受影响的车辆VIN及其客户。
品牌销售排名：分析过去一年的销售额和单位销售额，确定前两大品牌。
单月最佳销售车型：确定某个车型（如H4）在哪个月销售最佳。
库存分析：找出平均库存时间最长的经销商。
- 应用程序设计
程序目标：设计一个应用程序，实现上述查询和数据分析功能。
技术选型：根据需求选择C++、Java或Python语言，可以是单机版、GUI应用程序或Web应用。
程序结构：描述应用程序的架构、模块和用户界面设计。
四、实验过程
1、构建E-R模型
2、构建关系模型
实体：
车辆（VIN，颜色，发动机排量，型号，制造商号），VIN为主键，型号为外键。
车型（型号，品牌名），型号为主键，品牌名为外键。
品牌（品牌名），品牌名就是主键。
供应商（供应商ID，供应商名），供应商ID为主键。
客户（客户ID，地址，电话，年收入，姓名，性别），其中客户ID为主键。
经销商（经销商名），经销商名为主键。
联系：
供应（型号，供应商ID，零件种类，供应日期），型号和供应商名为主键。
购买（VIN，经销商名，购买日期），VIN和经销商名为主键。
销售（经销商名，客户ID，成交价格，日期，VIN），经销商名和客户ID和VIN构成主键。
3、基于OPENGAUSS 创建该关系数据库
车辆Vehicle（VIN，颜色，发动机排量，型号，制造商号），VIN为主键，型号为外键。
| 属性名 | 数据类型 | 对应英文名 |
| VIN | varchar(17) | VIN |
| 颜色 | varchar(50) | color |
| 发动机排量 | int | engine_displacement |
| 型号 | varchar(255) | type_name |
| 制造商号 | varchar(255) | manufacturer |
车型cartype（型号，品牌名），型号为主键，品牌名为外键。
品牌brand（品牌名），品牌名就是主键。
| 属性名 | 数据类型 | 对应英文名 |
| 型号 | varchar(255) | type_name |
| 品牌名 | varchar(255) | brand |
供应商supplier（供应商ID，供应商名），供应商ID为主键。
| 属性名 | 数据类型 | 对应英文名 |
| 供应商ID | Varchar(17) | supplyid |
| 供应商名 | varchar(255) | supplier |
客户customer（客户ID，地址，电话，年收入，姓名，性别），其中客户ID为主键。
| 属性名 | 数据类型 | 对应英文名 |
| 客户ID | varchar(12) | customer_id |
| 地址 | varchar(255) | address |
| 电话 | varchar(11) | phone_number |
| 年收入 | int | income |
| 姓名 | varchar(255) | name |
| 性别 | varchar(10) | sex |
经销商（经销商名），经销商名为主键。
| 属性名 | 数据类型 | 对应英文名 |
| 经销商名 | varchar(255) | distributor |
供应supply（型号，供应商ID，零件种类，供应日期），型号和供应商名为主键。
| 属性名 | 数据类型 | 对应英文名 |
| 型号 | varchar(255) | type_name |
| 供应商ID | Varchar(17) | supplyid |
| 零件种类 | varchar(255) | part_type |
| 供应日期 | date | supply_date |
购买purchase（VIN，经销商名，购买日期），VIN和经销商名为主键。
| 属性名 | 数据类型 | 对应英文名 |
| VIN | varchar(17) | VIN |
| 经销商名 | varchar(255) | distributor |
| 购入日期 | date | purchase_date |
销售（经销商名，客户ID，成交价格，日期，VIN），经销商名和客户ID和VIN构成主键。
| 属性名 | 数据类型 | 对应英文名 |
| 经销商名 | varchar(255) | distributor |
| 客户ID | varchar(12) | customer_id |
| VIN | varchar(17) | VIN |
| 成交价格 | int | price |
| （成交）日期 | date | deal_date |
接下来，在OpenGauss下进行操作：
CREATE DATABASE Car OWNER Amy;
gsql -d car -p 26000 -U amy -W -r
CREATE SCHEMA amy AUTHORIZATION amy;
SET search_path TO amy;
创建表并赋予约束：
(1)create table brand(brand varchar(255) primary key);
(2)create table car_type(type_name varchar(255) primary key,brand varchar(255),foreign key (brand) references brand(brand) ON UPDATE CASCADE ON DELETE CASCADE);
(3)create table vehicle(vin varchar(17) primary key,color varchar(50),engine_displacement int,type_name varchar(255),manufacturer varchar(255),foreign key (type_name) references car_type(type_name) ON UPDATE CASCADE ON DELETE CASCADE);
(4)create table supplier(supplier varchar(255) primary key);
(5) CREATE TABLE parts_supply (type_name varchar(255),supplier varchar(255),part_type varchar(255), supply_date date, PRIMARY KEY (type_name, supplier), FOREIGN KEY (type_name) REFERENCES car_type(type_name)ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY (supplier) REFERENCES supplier(supplier))ON UPDATE CASCADE ON DELETE CASCADE;
(6) create table distributor(distributor varchar(255) primary key);
(7) CREATE TABLE purchase (
VIN varchar(17),
distributor varchar(255),
purchase_date date,
PRIMARY KEY (VIN, distributor),
FOREIGN KEY (VIN) REFERENCES vehicle(VIN),
FOREIGN KEY (distributor) REFERENCES distributor(distributor)
ON UPDATE CASCADE
ON DELETE CASCADE
);
(8)create table customer(customer_id varchar(12) primary key,address varchar(255),phone_number varchar(11),income int,name varchar(255),sex varchar(10));
(9) CREATE TABLE sales (
distributor varchar(255),
customer_id varchar(12),
price int,
deal_date date,
VIN varchar(17),
PRIMARY KEY (distributor, customer_id),
FOREIGN KEY (distributor) REFERENCES distributor(distributor),
FOREIGN KEY (customer_id) REFERENCES customer(customer_id),
FOREIGN KEY (VIN) REFERENCES vehicle(VIN)
ON UPDATE CASCADE
ON DELETE CASCADE
);
接下来向表中插入数据：
-- Insert multiple entries into 'brand' table
INSERT INTO brand (brand) VALUES ('比亚迪');
INSERT INTO brand (brand) VALUES ('长城汽车');
INSERT INTO brand (brand) VALUES ('吉利汽车');
INSERT INTO brand (brand) VALUES ('奇瑞汽车');
INSERT INTO brand (brand) VALUES ('宝马');
INSERT INTO brand (brand) VALUES ('奔驰');
INSERT INTO brand (brand) VALUES ('奥迪');
INSERT INTO brand (brand) VALUES ('特斯拉');
INSERT INTO brand (brand) VALUES ('本田');
INSERT INTO brand (brand) VALUES ('丰田');
-- Insert multiple entries into 'car_type' table
INSERT INTO car_type (type_name, brand) VALUES ('比亚迪唐DM', '比亚迪');
INSERT INTO car_type (type_name, brand) VALUES ('比亚迪汉', '比亚迪');
INSERT INTO car_type (type_name, brand) VALUES ('比亚迪秦Pro', '比亚迪');
INSERT INTO car_type (type_name, brand) VALUES ('长城哈弗H6', '长城汽车');
INSERT INTO car_type (type_name, brand) VALUES ('长城哈弗H2', '长城汽车');
INSERT INTO car_type (type_name, brand) VALUES ('长城哈弗H9', '长城汽车');
INSERT INTO car_type (type_name, brand) VALUES ('吉利博越Pro', '吉利汽车');
INSERT INTO car_type (type_name, brand) VALUES ('吉利帝豪', '吉利汽车');
INSERT INTO car_type (type_name, brand) VALUES ('吉利远景', '吉利汽车');
INSERT INTO car_type (type_name, brand) VALUES ('奇瑞瑞虎8', '奇瑞汽车');
INSERT INTO car_type (type_name, brand) VALUES ('奇瑞瑞虎7', '奇瑞汽车');
INSERT INTO car_type (type_name, brand) VALUES ('奇瑞艾瑞泽5', '奇瑞汽车');
INSERT INTO car_type (type_name, brand) VALUES ('宝马X5', '宝马');
INSERT INTO car_type (type_name, brand) VALUES ('宝马3系', '宝马');
INSERT INTO car_type (type_name, brand) VALUES ('宝马5系', '宝马');
INSERT INTO car_type (type_name, brand) VALUES ('奔驰C级', '奔驰');
INSERT INTO car_type (type_name, brand) VALUES ('奔驰E级', '奔驰');
INSERT INTO car_type (type_name, brand) VALUES ('奔驰GLC', '奔驰');
INSERT INTO car_type (type_name, brand) VALUES ('奥迪A4L', '奥迪');
INSERT INTO car_type (type_name, brand) VALUES ('奥迪Q5L', '奥迪');
INSERT INTO car_type (type_name, brand) VALUES ('奥迪A6L', '奥迪');
INSERT INTO car_type (type_name, brand) VALUES ('特斯拉Model S', '特斯拉');
INSERT INTO car_type (type_name, brand) VALUES ('特斯拉Model 3', '特斯拉');
INSERT INTO car_type (type_name, brand) VALUES ('特斯拉Model X', '特斯拉');
INSERT INTO car_type (type_name, brand) VALUES ('本田雅阁', '本田');
INSERT INTO car_type (type_name, brand) VALUES ('本田CR-V', '本田');
INSERT INTO car_type (type_name, brand) VALUES ('本田思域', '本田');
INSERT INTO car_type (type_name, brand) VALUES ('丰田卡罗拉', '丰田');
INSERT INTO car_type (type_name, brand) VALUES ('丰田普拉多', '丰田');
INSERT INTO car_type (type_name, brand) VALUES ('丰田RAV4荣放', '丰田');
-- Insert multiple entries into 'vehicle' table
INSERT INTO vehicle (vin, color, engine_displacement, type_name, manufacturer) VALUES ('VIN031', '绿色', 2200, '比亚迪唐DM', '比亚迪');
INSERT INTO vehicle (vin, color, engine_displacement, type_name, manufacturer) VALUES ('VIN032', '橙色', 1800, '比亚迪宋', '比亚迪');
INSERT INTO vehicle (vin, color, engine_displacement, type_name, manufacturer) VALUES ('VIN033', '金色', 1600, '比亚迪秦Pro', '比亚迪');
INSERT INTO vehicle (vin, color, engine_displacement, type_name, manufacturer) VALUES ('VIN034', '黄色', 2000, '长城哈弗H6', '长城汽车');
INSERT INTO vehicle (vin, color, engine_displacement, type_name, manufacturer) VALUES ('VIN035', '紫色', 1900, '长城哈弗H2', '长城汽车');
INSERT INTO vehicle (vin, color, engine_displacement, type_name, manufacturer) VALUES ('VIN036', '粉色', 1700, '长城哈弗H9', '长城汽车');
INSERT INTO vehicle (vin, color, engine_displacement, type_name, manufacturer) VALUES ('VIN037', '红色', 2500, '吉利博越Pro', '吉利汽车');
INSERT INTO vehicle (vin, color, engine_displacement, type_name, manufacturer) VALUES ('VIN038', '黑色', 2000, '吉利帝豪', '吉利汽车');
INSERT INTO vehicle (vin, color, engine_displacement, type_name, manufacturer) VALUES ('VIN039', '白色', 2200, '吉利远景', '吉利汽车');
INSERT INTO vehicle (vin, color, engine_displacement, type_name, manufacturer) VALUES ('VIN040', '灰色', 2000, '奇瑞瑞虎8', '奇瑞汽车');
INSERT INTO vehicle (vin, color, engine_displacement, type_name, manufacturer) VALUES ('VIN041', '银色', 2500, '奇瑞瑞虎7', '奇瑞汽车');
INSERT INTO vehicle (vin, color, engine_displacement, type_name, manufacturer) VALUES ('VIN042', '蓝色', 2200, '奇瑞艾瑞泽5', '奇瑞汽车');
INSERT INTO vehicle (vin, color, engine_displacement, type_name, manufacturer) VALUES ('VIN043', '红色', 1800, '宝马X5', '宝马');
INSERT INTO vehicle (vin, color, engine_displacement, type_name, manufacturer) VALUES ('VIN044', '黑色', 2000, '宝马3系', '宝马');
INSERT INTO vehicle (vin, color, engine_displacement, type_name, manufacturer) VALUES ('VIN045', '白色', 2200, '宝马5系', '宝马');
INSERT INTO vehicle (vin, color, engine_displacement, type_name, manufacturer) VALUES ('VIN046', '灰色', 2000, '奔驰C级', '奔驰');
此处省略一些
-- Insert multiple entries into 'supplier' table
INSERT INTO supplier (supplier) VALUES ('全球汽车零件供应商');
INSERT INTO supplier (supplier) VALUES ('高性能零件有限公司');
INSERT INTO supplier (supplier) VALUES ('智能驱动系统供应商');
INSERT INTO supplier (supplier) VALUES ('优质汽车电子供应商');
INSERT INTO supplier (supplier) VALUES ('环保汽车配件供应商');
INSERT INTO supplier (supplier) VALUES ('先进材料科技公司');
INSERT INTO supplier (supplier) VALUES ('汽车安全系统有限公司');
INSERT INTO supplier (supplier) VALUES ('动力系统解决方案供应商');
INSERT INTO supplier (supplier) VALUES ('车辆信息娱乐系统供应商');
INSERT INTO supplier (supplier) VALUES ('汽车照明系统有限公司');
-- Insert multiple entries into 'parts_supply' table
INSERT INTO parts_supply (type_name, supplier, part_type, supply_date) VALUES ('比亚迪秦Pro', '环保汽车配件供应商', '座椅', '2023-01-20');
INSERT INTO parts_supply (type_name, supplier, part_type, supply_date) VALUES ('长城哈弗H9', '先进材料科技公司', '车窗', '2023-02-25');
INSERT INTO parts_supply (type_name, supplier, part_type, supply_date) VALUES ('吉利远景', '汽车安全系统有限公司', '刹车系统', '2023-03-30');
INSERT INTO parts_supply (type_name, supplier, part_type, supply_date) VALUES ('奇瑞瑞虎7', '动力系统解决方案供应商', '启动器', '2023-04-04');
INSERT INTO parts_supply (type_name, supplier, part_type, supply_date) VALUES ('宝马3系', '车辆信息娱乐系统供应商', '音响系统', '2023-05-09');
INSERT INTO parts_supply (type_name, supplier, part_type, supply_date) VALUES ('奔驰E级', '汽车照明系统有限公司', '尾灯', '2023-06-14');
INSERT INTO parts_supply (type_name, supplier, part_type, supply_date) VALUES ('奥迪Q5L', '全球汽车零件供应商', '空调系统', '2023-07-19');
INSERT INTO parts_supply (type_name, supplier, part_type, supply_date) VALUES ('特斯拉Model 3', '高性能零件有限公司', '触摸屏', '2023-08-24');
INSERT INTO parts_supply (type_name, supplier, part_type, supply_date) VALUES ('本田CR-V', '智能驱动系统供应商', '仪表盘', '2023-09-29');
INSERT INTO parts_supply (type_name, supplier, part_type, supply_date) VALUES ('丰田RAV4荣放', '优质汽车电子供应商', '发动机控制单元', '2023-10-04');
INSERT INTO parts_supply (type_name, supplier, part_type, supply_date) VALUES ('奇瑞艾瑞泽5', '环保汽车配件供应商', '排气管', '2023-11-09');
INSERT INTO parts_supply (type_name, supplier, part_type, supply_date) VALUES ('宝马5系', '先进材料科技公司', '车轮','2023-12-14');
-- Insert multiple entries into 'distributor' table
INSERT INTO distributor (distributor) VALUES ('东方汽车销售有限公司');
INSERT INTO distributor (distributor) VALUES ('环球车行');
INSERT INTO distributor (distributor) VALUES ('速驰汽车销售中心');
INSERT INTO distributor (distributor) VALUES ('明星汽车经销商');
INSERT INTO distributor (distributor) VALUES ('四海汽车贸易公司');
INSERT INTO distributor (distributor) VALUES ('银河汽车销售集团');
INSERT INTO distributor (distributor) VALUES ('阳光汽车经销商');
INSERT INTO distributor (distributor) VALUES ('宏伟汽车销售');
INSERT INTO distributor (distributor) VALUES ('骏马汽车销售有限公司');
INSERT INTO distributor (distributor) VALUES ('飞跃汽车销售中心');
-- Insert multiple entries into 'purchase' table
INSERT INTO purchase (VIN, distributor, purchase_date) VALUES ('VIN013', '东方汽车销售有限公司', '2023-01-16');
INSERT INTO purchase (VIN, distributor, purchase_date) VALUES ('VIN014', '环球车行', '2023-02-21');
INSERT INTO purchase (VIN, distributor, purchase_date) VALUES ('VIN015', '速驰汽车销售中心', '2023-03-26');
INSERT INTO purchase (VIN, distributor, purchase_date) VALUES ('VIN016', '明星汽车经销商', '2023-04-01');
INSERT INTO purchase (VIN, distributor, purchase_date) VALUES ('VIN017', '四海汽车贸易公司', '2023-05-06');
INSERT INTO purchase (VIN, distributor, purchase_date) VALUES ('VIN018', '银河汽车销售集团', '2023-06-11');
INSERT INTO purchase (VIN, distributor, purchase_date) VALUES ('VIN019', '阳光汽车经销商', '2023-07-16');
INSERT INTO purchase (VIN, distributor, purchase_date) VALUES ('VIN020', '宏伟汽车销售', '2023-08-21');
INSERT INTO purchase (VIN, distributor, purchase_date) VALUES ('VIN021', '骏马汽车销售有限公司', '2023-09-26');
INSERT INTO purchase (VIN, distributor, purchase_date) VALUES ('VIN022', '飞跃汽车销售中心', '2023-10-31');
INSERT INTO purchase (VIN, distributor, purchase_date) VALUES ('VIN023', '东方汽车销售有限公司', '2023-11-05');
INSERT INTO purchase (VIN, distributor, purchase_date) VALUES ('VIN024', '环球车行', '2023-12-10');
INSERT INTO purchase (VIN, distributor, purchase_date) VALUES ('VIN025', '速驰汽车销售中心', '2023-01-17');
INSERT INTO purchase (VIN, distributor, purchase_date) VALUES ('VIN026', '明星汽车经销商', '2023-02-22');
INSERT INTO purchase (VIN, distributor, purchase_date) VALUES ('VIN027', '四海汽车贸易公司', '2023-03-27');
INSERT INTO purchase (VIN, distributor, purchase_date) VALUES ('VIN028', '银河汽车销售集团', '2023-04-02');
INSERT INTO purchase (VIN, distributor, purchase_date) VALUES ('VIN029', '阳光汽车经销商', '2023-05-07');
INSERT INTO purchase (VIN, distributor, purchase_date) VALUES ('VIN030', '宏伟汽车销售', '2023-06-12');
INSERT INTO purchase (VIN, distributor, purchase_date) VALUES ('VIN031', '骏马汽车销售有限公司', '2023-07-17');
INSERT INTO purchase (VIN, distributor, purchase_date) VALUES ('VIN032', '飞跃汽车销售中心', '2023-08-22');
INSERT INTO purchase (VIN, distributor, purchase_date) VALUES ('VIN033', '东方汽车销售有限公司', '2023-09-27');
INSERT INTO purchase (VIN, distributor, purchase_date) VALUES ('VIN034', '环球车行', '2023-10-01');
INSERT INTO purchase (VIN, distributor, purchase_date) VALUES ('VIN035', '速驰汽车销售中心', '2023-11-06');
INSERT INTO purchase (VIN, distributor, purchase_date) VALUES ('VIN036', '明星汽车经销商', '2023-12-11');
-- Insert multiple entries into 'customer' table
INSERT INTO customer (customer_id, address, phone_number, income, name, sex) VALUES ('CUST001', '北京市朝阳区', '13800138000', 50000, '张伟', '男');
INSERT INTO customer (customer_id, address, phone_number, income, name, sex) VALUES ('CUST002', '上海市浦东新区', '13900139001', 55000, '李娜', '女');
INSERT INTO customer (customer_id, address, phone_number, income, name, sex) VALUES ('CUST003', '广州市天河区', '13700137002', 60000, '王浩', '男');
INSERT INTO customer (customer_id, address, phone_number, income, name, sex) VALUES ('CUST004', '深圳市南山区', '13600136003', 45000, '刘晨', '女');
INSERT INTO customer (customer_id, address, phone_number, income, name, sex) VALUES ('CUST005', '成都市武侯区', '13500135004', 48000, '陈薇', '女');
INSERT INTO customer (customer_id, address, phone_number, income, name, sex) VALUES ('CUST006', '杭州市西湖区', '13400134005', 53000, '李明', '男');
INSERT INTO customer (customer_id, address, phone_number, income, name, sex) VALUES ('CUST007', '武汉市江汉区', '13300133006', 58000, '赵丽', '女');
INSERT INTO customer (customer_id, address, phone_number, income, name, sex) VALUES ('CUST008', '南京市秦淮区', '13200132007', 47000, '孙伟', '男');
INSERT INTO customer (customer_id, address, phone_number, income, name, sex) VALUES ('CUST009', '西安市碑林区', '13100131008', 62000, '周强', '男');
INSERT INTO customer (customer_id, address, phone_number, income, name, sex) VALUES ('CUST010', '重庆市江北区', '13000130009', 51000, '吴倩', '女');
INSERT INTO customer (customer_id, address, phone_number, income, name, sex) VALUES ('CUST011', '苏州市姑苏区', '13900139010', 54000, '徐凯', '男');
INSERT INTO customer (customer_id, address, phone_number, income, name, sex) VALUES ('CUST012', '天津市和平区', '13800138011', 56000, '黄婷', '女');
INSERT INTO customer (customer_id, address, phone_number, income, name, sex) VALUES ('CUST013', '沈阳市沈河区', '13700137012', 61000, '曹阳', '男');
INSERT INTO customer (customer_id, address, phone_number, income, name, sex) VALUES ('CUST014', '哈尔滨市道里区', '13600136013', 49000, '韩梅', '女');
INSERT INTO customer (customer_id, address, phone_number, income, name, sex) VALUES ('CUST015', '长春市南关区', '13500135014', 52000, '李华', '男');
-- Insert multiple entries into 'sales' table
INSERT INTO sales (distributor, customer_id, price, deal_date, VIN) VALUES ('东方汽车销售有限公司', 'CUST013', 360000, '2023-01-16', 'VIN013');
INSERT INTO sales (distributor, customer_id, price, deal_date, VIN) VALUES ('环球车行', 'CUST014', 340000, '2023-02-21', 'VIN014');
INSERT INTO sales (distributor, customer_id, price, deal_date, VIN) VALUES ('速驰汽车销售中心', 'CUST015', 370000, '2023-03-26', 'VIN015');
INSERT INTO sales (distributor, customer_id, price, deal_date, VIN) VALUES ('明星汽车经销商', 'CUST016', 310000, '2023-04-01', 'VIN016');
INSERT INTO sales (distributor, customer_id, price, deal_date, VIN) VALUES ('四海汽车贸易公司', 'CUST017', 330000, '2023-05-06', 'VIN017');
INSERT INTO sales (distributor, customer_id, price, deal_date, VIN) VALUES ('银河汽车销售集团', 'CUST018', 380000, '2023-06-11', 'VIN018');
INSERT INTO sales (distributor, customer_id, price, deal_date, VIN) VALUES ('阳光汽车经销商', 'CUST019', 300000, '2023-07-16', 'VIN019');
INSERT INTO sales (distributor, customer_id, price, deal_date, VIN) VALUES ('宏伟汽车销售', 'CUST020', 320000, '2023-08-21', 'VIN020');
INSERT INTO sales (distributor, customer_id, price, deal_date, VIN) VALUES ('骏马汽车销售有限公司', 'CUST021', 340000, '2023-09-26', 'VIN021');
INSERT INTO sales (distributor, customer_id, price, deal_date, VIN) VALUES ('飞跃汽车销售中心', 'CUST022', 350000, '2023-10-31', 'VIN022');
INSERT INTO sales (distributor, customer_id, price, deal_date, VIN) VALUES ('东方汽车销售有限公司', 'CUST023', 360000, '2023-11-05', 'VIN023');
INSERT INTO sales (distributor, customer_id, price, deal_date, VIN) VALUES ('环球车行', 'CUST024', 310000, '2023-12-10', 'VIN024');
INSERT INTO sales (distributor, customer_id, price, deal_date, VIN) VALUES ('速驰汽车销售中心', 'CUST025', 330000, '2023-01-17', 'VIN025');
INSERT INTO sales (distributor, customer_id, price, deal_date, VIN) VALUES ('明星汽车经销商', 'CUST026', 340000, '2023-02-22', 'VIN026');
INSERT INTO sales (distributor, customer_id, price, deal_date, VIN) VALUES ('四海汽车贸易公司', 'CUST027', 350000, '2023-03-27', 'VIN027');
INSERT INTO sales (distributor, customer_id, price, deal_date, VIN) VALUES ('银河汽车销售集团', 'CUST028', 320000, '2023-04-02', 'VIN028');
INSERT INTO sales (distributor, customer_id, price, deal_date, VIN) VALUES ('阳光汽车经销商', 'CUST029', 360000, '2023-05-07', 'VIN029');
在数据库中进行实验调试。
五、实验结果
- 显示过去3年各品牌的销售趋势，按年、月分列。然后将这些数据按购买者的性别和收入范围进行分类。
SELECT
EXTRACT(YEAR FROM deal_date) AS year,
EXTRACT(MONTH FROM deal_date) AS month,
brand,
COUNT(*) AS sales_count,
sex,
CASE
WHEN income < 30000 THEN '0-30000'
WHEN income >= 30000 AND income < 60000 THEN '30000-60000'
ELSE '60000以上'
END AS income_range
FROM
(((sales NATURAL JOIN distributor) NATURAL JOIN customer) NATURAL JOIN vehicle) NATURAL JOIN car_type
WHERE
deal_date >= '2020-12-21'
GROUP BY
EXTRACT(YEAR FROM deal_date),
EXTRACT(MONTH FROM deal_date),
brand,
sex,
income_range
ORDER BY
EXTRACT(YEAR FROM deal_date),
EXTRACT(MONTH FROM deal_date),
brand,
sex,
income_range;
运行结果：
- 假设发现供应商'高性能零件有限公司’在两个给定日期之间进行的变速器存在缺陷。找到每辆装有这种变速器的汽车的车辆识别号（VIN）以及向其销售的客户。
SELECT
v.vin AS vehicle_vin,
c.name AS customer_name,
c.phone_number AS customer_phone
FROM
vehicle v
JOIN
sales s ON v.vin = s.vin
JOIN
parts_supply p ON v.type_name = p.type_name
JOIN
customer c ON s.customer_id = c.customer_id
JOIN
purchase pc ON pc.vin = v.vin
WHERE
p.supplier = '高性能零件有限公司'
AND pc.purchase_date BETWEEN '2023-01-01' AND '2023-12-10';
查询结果：
- 按过去一年的销售金额找出前两大品牌
SELECT brand, SUM(price) AS total_sales_amount
FROM sales natural join (vehicle natural join car_type) WHERE deal_date >= 2023-00-00
GROUP BY brand ORDER BY total_sales_amount DESC LIMIT 2;
- 根据过去一年的单位销售额找出前两大品牌。
SELECT
brand,
AVG(price) AS average_sales_amount
FROM
sales
JOIN
vehicle ON sales.VIN = vehicle.VIN
JOIN
car_type ON vehicle.type_name = car_type.type_name
WHERE
deal_date >= '2023-01-01'
GROUP BY
brand
ORDER BY
average_sales_amount DESC
LIMIT 2;
- 某种车型在哪个月卖得最好？
SELECT EXTRACT(MONTH FROM deal_date) AS month,
COUNT(*) AS sales_count
FROM sales natural join vehicle
WHERE type_name = '车型1'
GROUP BY EXTRACT(MONTH FROM deal_date)
ORDER BY sales_count DESC LIMIT 1;
此处由于测试数据设置的问题，导致车辆查询结果若按月分类将出现大量count为1的情况，但不妨碍对数据库功能进行验证。
- 找到平均库存时间最长的经销商。
SELECT
s.distributor,
AVG(
365 * (EXTRACT(YEAR FROM s.deal_date) - EXTRACT(YEAR FROM p.purchase_date)) +
30 * (EXTRACT(MONTH FROM s.deal_date) - EXTRACT(MONTH FROM p.purchase_date)) +
(EXTRACT(DAY FROM s.deal_date) - EXTRACT(DAY FROM p.purchase_date))
) AS avg_inventory_time
FROM
sales s
JOIN
purchase p ON s.vin = p.vin
GROUP BY
s.distributor
ORDER BY
avg_inventory_time DESC
LIMIT 1;
图像GUI设计：
import javax.swing.*;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.*;
public class CustomerGUI {
public static final String DRIVER_CLASS = "com.mysql.cj.jdbc.Driver";
public static final String DB_URL = "jdbc:mysql://localhost:3306/dbexperiment?&useSSL=false&serverTimezone=UTC";
public static final String DB_USER = "root";
public static final String DB_PASSWORD = "12138";
public static void main(String[] args) {
JFrame frame = new JFrame("CustomerGUI");
frame.setSize(400, 380);
JPanel panelInput = new JPanel();
panelInput.setLayout(new GridLayout(5, 2, 10, 10));
JLabel customerIdLabel = new JLabel("CustomerID:");
JTextField customerIdText = new JTextField();
JLabel nameLabel = new JLabel("Name:");
JTextField nameText = new JTextField();
JLabel sexLabel = new JLabel("Sex:");
JTextField sexText = new JTextField();
JLabel phoneNumberLabel = new JLabel("Phone number:");
JTextField phoneNumberText = new JTextField();
JLabel incomeLabel = new JLabel("Income:");
JTextField incomeText = new JTextField();
JButton submitButton = new JButton("Submit");
panelInput.add(customerIdLabel);
panelInput.add(customerIdText);
panelInput.add(nameLabel);
panelInput.add(nameText);
panelInput.add(sexLabel);
panelInput.add(sexText);
panelInput.add(phoneNumberLabel);
panelInput.add(phoneNumberText);
panelInput.add(incomeLabel);
panelInput.add(incomeText);
frame.add(panelInput, BorderLayout.NORTH);
frame.add(submitButton, BorderLayout.SOUTH);
frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
frame.setLocationRelativeTo(null);
frame.setVisible(true);
submitButton.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
String customerId = customerIdText.getText();
try {
Class.forName(DRIVER_CLASS);
Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
String querySQL = "SELECT * FROM customer WHERE customer_id = ?;";
PreparedStatement pst = conn.prepareStatement(querySQL);
pst.setString(1, customerId);
ResultSet rs = pst.executeQuery();
if (rs.next()) {
nameText.setText(rs.getString("name"));
sexText.setText(rs.getString("sex"));
phoneNumberText.setText(rs.getString("phone_number"));
incomeText.setText(String.valueOf(rs.getInt("income")));
} else {
JOptionPane.showMessageDialog(frame, "No customer found with ID: " + customerId);
}
rs.close();
pst.close();
conn.close();
} catch (ClassNotFoundException | SQLException classNotFoundException) {
classNotFoundException.printStackTrace();
}
}
});
}
}
- 总结及心得体会
在本次实验中，成功创建了数据库和所需的表格，包括外键约束和适当的数据类型。
插入操作和查询操作验证了数据库模型的有效性，同时也展示了数据的一致性和完整性。
GUI程序能够成功连接数据库，并能根据用户的输入执行查询，显示结果。
通过本次实验，我深入了解了数据库的设计和实现过程。通过实践，我学会了如何创建和维护一个关系数据库，以及如何使用SQL来进行各种数据库操作。此外，Java GUI的开发让我体会到了前端与后端数据交互的实际应用，这对我的编程技能和数据库管理能力都是一次宝贵的提升。
在未来的学习中，我希望能够继续深化对数据库更高级特性的理解，例如触发器、存储过程以及数据库优化等。同时，我也期待能够学习更多关于用户界面设计和用户体验提升的知识，以创建更加直观和友好的应用程序。
报告评分：
指导教师签字：
6355
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
