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

实验报告

课程：数据库应用

班级：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语句的理解更清晰，运用更顺手了。

posted @ 2023-04-22 21:27 郭幸坤阅读(43) 评论(0) 编辑收藏举报

刷新页面返回顶部

郭幸坤