标签：实验报告 Relationship 数据库 null eno bno PK 实验 FK

实验报告
课程：数据库应用
班级：2012 姓名：郭幸坤学号：20201213
实验名称：实验一实验日期：2023.4.15
概念模型设计及逻辑模型设计描述
- ER图
- 关系模式集合
数据建模工具的模型设计过程描述
数据库创建过程描述
- 建立数据库表列表
数据库查询设计与实现描述
- 数据库内容
- 测试用例
问题分析
实验感想
- 对工具的理解
- 收获

实验报告

课程：数据库应用

班级：2012 姓名：郭幸坤学号：20201213

实验名称：实验一实验日期：2023.4.15

概念模型设计及逻辑模型设计描述

ER图

关系模式集合

Relationship_5（zhiwuno,bno）
bumen(bno,bum_bno,bname,bpnum,bkind)
danren(danrenno,danrentime)
employ(eno,etime)
worker(wno,bno,eno,danrenno,wname,wbirth,gender,key）
yongyou(zno,bno)
zhiji(zno,eno,zname)三个属性，其中zno是主键。
zhiwu(zhiwuno,danrenno,zhiwuname)

数据建模工具的模型设计过程描述

使用powerdesigner实现CDM、LDM、PDM。

物理模型

逻辑模型

概念模型

数据库创建过程描述

建立数据库表列表

/*==============================================================*/
/* DBMS name:PostgreSQL 8*/
/* Created on:2023/4/13 11:30:35*/
/*==============================================================*/


drop index Relationship_6_FK;

drop index Relationship_5_FK;

drop index Relationship_5_PK;

drop table Relationship_5;

drop index shuyu_FK;

drop index bumen_PK;

drop table bumen;

drop index danren_PK;

drop table danren;

drop index employ_PK;

drop table employ;

drop index Relationship_9_FK;

drop index Relationship_8_FK;

drop index Relationship_1_FK;

drop index worker_PK;

drop table worker;

drop index yongyou2_FK;

drop index yongyou_FK;

drop index yongyou_PK;

drop table yongyou;

drop index Relationship_2_FK;

drop index zhiji_PK;

drop table zhiji;

drop index Relationship_7_FK;

drop index zhiwu_PK;

drop table zhiwu;

/*==============================================================*/
/* Table: Relationship_5 */
/*==============================================================*/
create table Relationship_5 (
zhiwunoCHAR(256)not null,
bnoCHAR(256)not null,
constraint PK_RELATIONSHIP_5 primary key (zhiwuno, bno)
);

/*==============================================================*/
/* Index: Relationship_5_PK */
/*==============================================================*/
create unique index Relationship_5_PK on Relationship_5 (
zhiwuno,
bno
);

/*==============================================================*/
/* Index: Relationship_5_FK */
/*==============================================================*/
createindex Relationship_5_FK on Relationship_5 (
zhiwuno
);

/*==============================================================*/
/* Index: Relationship_6_FK */
/*==============================================================*/
createindex Relationship_6_FK on Relationship_5 (
bno
);

/*==============================================================*/
/* Table: bumen */
/*==============================================================*/
create table bumen (
bnoCHAR(256)not null,
bum_bnoCHAR(256)null,
bname CHAR(256)null,
bpnum CHAR(256)null,
bkind CHAR(256)null,
constraint PK_BUMEN primary key (bno)
);

/*==============================================================*/
/* Index: bumen_PK */
/*==============================================================*/
create unique index bumen_PK on bumen (
bno
);

/*==============================================================*/
/* Index: shuyu_FK */
/*==============================================================*/
createindex shuyu_FK on bumen (
bum_bno
);

/*==============================================================*/
/* Table: danren*/
/*==============================================================*/
create table danren (
danrenno CHAR(256)not null,
danrentimeCHAR(256)null,
constraint PK_DANREN primary key (danrenno)
);

/*==============================================================*/
/* Index: danren_PK*/
/*==============================================================*/
create unique index danren_PK on danren (
danrenno
);

/*==============================================================*/
/* Table: employ*/
/*==============================================================*/
create table employ (
enoCHAR(256)not null,
etime CHAR(256)null,
constraint PK_EMPLOY primary key (eno)
);

/*==============================================================*/
/* Index: employ_PK*/
/*==============================================================*/
create unique index employ_PK on employ (
eno
);

/*==============================================================*/
/* Table: worker*/
/*==============================================================*/
create table worker (
wnoCHAR(256)not null,
bnoCHAR(256)null,
enoCHAR(256)null,
danrenno CHAR(256)null,
wname CHAR(256)null,
wbirthCHAR(256)null,
genderCHAR(256)null,
keyCHAR(256)null,
constraint PK_WORKER primary key (wno)
);

/*==============================================================*/
/* Index: worker_PK*/
/*==============================================================*/
create unique index worker_PK on worker (
wno
);

/*==============================================================*/
/* Index: Relationship_1_FK */
/*==============================================================*/
createindex Relationship_1_FK on worker (
eno
);

/*==============================================================*/
/* Index: Relationship_8_FK */
/*==============================================================*/
createindex Relationship_8_FK on worker (
danrenno
);

/*==============================================================*/
/* Index: Relationship_9_FK */
/*==============================================================*/
createindex Relationship_9_FK on worker (
bno
);

/*==============================================================*/
/* Table: yongyou*/
/*==============================================================*/
create table yongyou (
znoCHAR(256)not null,
bnoCHAR(256)not null,
constraint PK_YONGYOU primary key (zno, bno)
);

/*==============================================================*/
/* Index: yongyou_PK*/
/*==============================================================*/
create unique index yongyou_PK on yongyou (
zno,
bno
);

/*==============================================================*/
/* Index: yongyou_FK*/
/*==============================================================*/
createindex yongyou_FK on yongyou (
zno
);

/*==============================================================*/
/* Index: yongyou2_FK */
/*==============================================================*/
createindex yongyou2_FK on yongyou (
bno
);

/*==============================================================*/
/* Table: zhiji */
/*==============================================================*/
create table zhiji (
znoCHAR(256)not null,
enoCHAR(256)null,
zname CHAR(256)null,
constraint PK_ZHIJI primary key (zno)
);

/*==============================================================*/
/* Index: zhiji_PK */
/*==============================================================*/
create unique index zhiji_PK on zhiji (
zno
);

/*==============================================================*/
/* Index: Relationship_2_FK */
/*==============================================================*/
createindex Relationship_2_FK on zhiji (
eno
);

/*==============================================================*/
/* Table: zhiwu */
/*==============================================================*/
create table zhiwu (
zhiwunoCHAR(256)not null,
danrenno CHAR(256)null,
zhiwunameCHAR(256)null,
constraint PK_ZHIWU primary key (zhiwuno)
);

/*==============================================================*/
/* Index: zhiwu_PK */
/*==============================================================*/
create unique index zhiwu_PK on zhiwu (
zhiwuno
);

/*==============================================================*/
/* Index: Relationship_7_FK */
/*==============================================================*/
createindex Relationship_7_FK on zhiwu (
danrenno
);

alter table Relationship_5
add constraint FK_RELATION_RELATIONS_ZHIWU foreign key (zhiwuno)
references zhiwu (zhiwuno)
on delete restrict on update restrict;

alter table Relationship_5
add constraint FK_RELATION_RELATIONS_BUMEN foreign key (bno)
references bumen (bno)
on delete restrict on update restrict;

alter table bumen
add constraint FK_BUMEN_SHUYU_BUMEN foreign key (bum_bno)
references bumen (bno)
on delete restrict on update restrict;

alter table worker
add constraint FK_WORKER_RELATIONS_EMPLOY foreign key (eno)
references employ (eno)
on delete restrict on update restrict;

alter table worker
add constraint FK_WORKER_RELATIONS_DANREN foreign key (danrenno)
references danren (danrenno)
on delete restrict on update restrict;

alter table worker
add constraint FK_WORKER_RELATIONS_BUMEN foreign key (bno)
references bumen (bno)
on delete restrict on update restrict;

alter table yongyou
add constraint FK_YONGYOU_YONGYOU_ZHIJI foreign key (zno)
references zhiji (zno)
on delete restrict on update restrict;

alter table yongyou
add constraint FK_YONGYOU_YONGYOU2_BUMEN foreign key (bno)
references bumen (bno)
on delete restrict on update restrict;

alter table zhiji
add constraint FK_ZHIJI_RELATIONS_EMPLOY foreign key (eno)
references employ (eno)
on delete restrict on update restrict;

alter table zhiwu
add constraint FK_ZHIWU_RELATIONS_DANREN foreign key (danrenno)
references danren (danrenno)
on delete restrict on update restrict;

数据库查询设计与实现描述

数据库内容

INSERT INTO "bumen"("bno", "bum_bno", "bname", "bpnum", "bkind") VALUES ('B01', NULL, 'Sales ', 'BP01', 'Department');
INSERT INTO "bumen"("bno", "bum_bno", "bname", "bpnum", "bkind") VALUES ('B02', 'B01', 'Marketing ', 'BP02', 'Department');
INSERT INTO "danren"("danrenno", "danrentime") VALUES ('D01', '2023-04-20 09:00:00');
INSERT INTO "danren"("danrenno", "danrentime") VALUES ('D02', '2023-04-21 10:00:00');
INSERT INTO "danren"("danrenno", "danrentime") VALUES ('D03', '2023-04-22 11:00:00');
INSERT INTO "employ"("eno", "etime") VALUES ('E01', '2023-04-20 09:00:00');
INSERT INTO "employ"("eno", "etime") VALUES ('E02', '2023-04-21 10:00:00');
INSERT INTO "employ"("eno", "etime") VALUES ('E03', '2023-04-22 11:00:00');
INSERT INTO "relationship_5"("zhiwuno", "bno") VALUES ('Z01', 'B01');
INSERT INTO "relationship_5"("zhiwuno", "bno") VALUES ('Z02', 'B01');
INSERT INTO "relationship_5"("zhiwuno", "bno") VALUES ('Z03', 'B02');
INSERT INTO "worker"("wno", "bno", "eno", "danrenno", "wname", "wbirth", "gender", "key") VALUES ('W01', 'B01', 'E01', 'D01', 'John', '1990-01-01', 'Male', 'K1 ');
INSERT INTO "worker"("wno", "bno", "eno", "danrenno", "wname", "wbirth", "gender", "key") VALUES ('W02', 'B02', 'E02', 'D02', 'Jane', '1995-01-01', 'Female', 'K2 ');
INSERT INTO "worker"("wno", "bno", "eno", "danrenno", "wname", "wbirth", "gender", "key") VALUES ('W03', 'B02', 'E03', 'D03', 'Bob', '2000-01-01', 'Male', 'K3 ');
INSERT INTO "yongyou"("zno", "bno") VALUES ('Z01', 'B01');
INSERT INTO "yongyou"("zno", "bno") VALUES ('Z02', 'B01');
INSERT INTO "yongyou"("zno", "bno") VALUES ('Z03', 'B02');
INSERT INTO "zhiji"("zno", "eno", "zname") VALUES ('Z01', 'E01', 'Manager');
INSERT INTO "zhiji"("zno", "eno", "zname") VALUES ('Z02', 'E02', 'Director');
INSERT INTO "zhiji"("zno", "eno", "zname") VALUES ('Z03', 'E03', 'Staff ');
INSERT INTO "zhiwu"("zhiwuno", "danrenno", "zhiwuname") VALUES ('Z01', 'D01', '经理');
INSERT INTO "zhiwu"("zhiwuno", "danrenno", "zhiwuname") VALUES ('Z02', 'D02', '副经理 ');
INSERT INTO "zhiwu"("zhiwuno", "danrenno", "zhiwuname") VALUES ('Z03', 'D03', '总监');
INSERT INTO "zhiwu"("zhiwuno", "danrenno", "zhiwuname") VALUES ('Z04', 'D01', '工程师 ');
INSERT INTO "zhiwu"("zhiwuno", "danrenno", "zhiwuname") VALUES ('Z05', 'D02', '文员');

测试用例

每个部门最多有20人，查询每个部门剩余的编制。

SELECT b.bname, 20 - COUNT(w.wno) AS remaining_slots
FROM bumen b
LEFT JOIN worker w ON b.bno = w.bno
GROUP BY b.bno, b.bname

男性退休年龄为60岁，女性退休年龄为55岁，请查询每名员工还能工作几年

SELECT wname, gender, 
    CASE 
        WHEN gender = 'Male' THEN 60 - DATE_PART('year', age(wbirth)) 
        WHEN gender = 'Female' THEN 55 - DATE_PART('year', age(wbirth)) 
    END AS years_left
FROM worker;

经理工资为100000，工程师工资为50000，文员工资为10000，计算总共该发多少工资

SELECT SUM(CASE WHEN eno = 'E01' THEN 100000 
                WHEN eno = 'E02' THEN 50000
                WHEN eno = 'E03' THEN 10000
                ELSE 0 END) AS total_salary
FROM worker;

计算男性、女性平均工资

SELECT gender, AVG(CASE WHEN eno = 'E01' THEN 100000 
                        WHEN eno = 'E02' THEN 50000
                        WHEN eno = 'E03' THEN 10000
                        ELSE 0 END) AS avg_salary
FROM worker
GROUP BY gender
HAVING gender IN ('Male', 'Female');

问题分析

通过写sql语句，我批量插入测试数据，但运行报错了。报错信息提示我，xx表中没有xx数据，这就是说，插入语句执行失败的原因是这个命令当中包含了外码，但外码还未导入。

我调整了对表格的插入顺序，先对没有外码的表格做插入，最后插入员工表的数据，果然奏效了。

在查询中，我最开始只打算计算男性平均工资，写出了这样一段语句：

SELECT AVG(CASE WHEN gender = 'Male' THEN
                  CASE WHEN eno = 'E01' THEN 100000
                       WHEN eno = 'E02' THEN 50000
                       WHEN eno = 'E03' THEN 10000
                       ELSE 0
                  END
             ELSE 0
        END) AS average_salary
FROM worker;

得出了错误的答案

这是因为，平均数的分母为全体worker，而没有通过性别分组。正确代码如下：

SELECT gender, AVG(CASE WHEN eno = 'E01' THEN 100000
                       WHEN eno = 'E02' THEN 50000
                       WHEN eno = 'E03' THEN 10000
                       ELSE 0
                  END) AS average_salary
FROM worker
GROUP BY gender;

这个查询可以同时查询gender为Male、Female的worker的平均工资，其中对于每个gender，使用AVG函数计算了该gender的所有worker的平均工资，结果以两行输出，第一行为Male的平均工资，第二行为Female的平均工资。

实验感想

对工具的理解

powerdesign确实是很牛逼的工具，通过画画ER图，就能生成可执行的sql脚本，完成数据库从设计到实现的过程，真是让我大开眼界。navicat实际上也是很好用的工具，在本次实验报告中，我没有采用powerdesign来生成CDM、LDM、PDM，而是采用了navicat，结果特别直观、美观。

由于在课上没有完成云端的实践，后来还欠费了，只好从虚机上完成实验。虚机使用的是openeuler+opengauss，ssh工具使用的是windows cmd（终端本身就能做ssh连接，没必要非得下载专门的工具），数据库设计采用powerdesign，数据库管理工具采用navicat。数据库管理工具可以通过远程连接，便捷地控制数据库。我将数据库启动命令装在脚本里，每次开机自动执行脚本，实在是妙哉妙哉。

收获

由于sql查询要以对关系代数的理解作为基础，这次实验让我对关系代数的理解更为深刻。光是对照着书本敲，我几乎没有碰到什么问题，最多就是改改数值，碰到的报错也是因为看串行了。但最后选择的四个查询，综合性相对而言比较强，语句本身难度不大，自己编故事自己做查询倒是让我对sql语句的理解更清晰，运用更顺手了。

标签：实验报告,Relationship,数据库,null,eno,bno,PK,实验,FK
From： https://www.cnblogs.com/kenneth2012/p/17344052.html

数据库应用实验一实验报告

实验报告

课程：数据库应用

班级：2012 姓名：郭幸坤学号：20201213

实验名称：实验一实验日期：2023.4.15

概念模型设计及逻辑模型设计描述

ER图

关系模式集合

数据建模工具的模型设计过程描述

物理模型

逻辑模型

概念模型

数据库创建过程描述

建立数据库表列表

数据库查询设计与实现描述

数据库内容

测试用例

问题分析

实验感想

对工具的理解

收获

相关文章

赞助商

阅读排行

数据库应用实验一实验报告

实验报告

课程：数据库应用

班级：2012 姓名：郭幸坤 学号：20201213

实验名称：实验一 实验日期：2023.4.15

概念模型设计及逻辑模型设计描述

ER图

关系模式集合

数据建模工具的模型设计过程描述

物理模型

逻辑模型

概念模型

数据库创建过程描述

建立数据库表列表

数据库查询设计与实现描述

数据库内容

测试用例

问题分析

实验感想

对工具的理解

收获

相关文章

赞助商

阅读排行

班级：2012 姓名：郭幸坤学号：20201213

实验名称：实验一实验日期：2023.4.15