实验报告

数据库建模与查询设计

数据库建模与查询设计

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

根据给定的应用需求和ER图参考，给出最终的ER图，并根据ER图进行逻辑模型转化，给出关系模式集合。
ER图:

手工逻辑模型转换:

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

使用工具 power desiger 根据实验附件步骤选择实体框以及关系线完成CDM的设计

选择对应的DBMS内核生成对应的PDM（因为在CDM阶段没有多对多关系属性的插入选项，故需要在此时插入对应的属性）。

选择生成DDL文件，并对部分SQL语句做调整，最终生成可用DDL语句：




/*==============================================================*/
/* Table: DanRen                                                */
/*==============================================================*/
create table DanRen (
   Eno                  CHAR(8)              not null,
   Pno                  CHAR(4)              not null,
   DRdate               DATE                 null,
   constraint PK_DANREN primary key (Eno, Pno)
);




/*==============================================================*/
/* Table: Have1                                                 */
/*==============================================================*/
create table Have1 (
   Dno                  CHAR(2)              not null,
   Rno                  CHAR(4)              not null,
   constraint PK_HAVE1 primary key (Dno, Rno)
);




/*==============================================================*/
/* Table: Have2                                                 */
/*==============================================================*/
create table Have2 (
   Dno                  CHAR(2)              not null,
   Pno                  CHAR(4)              not null,
   constraint PK_HAVE2 primary key (Dno, Pno)
);





/*==============================================================*/
/* Table: PinYong                                               */
/*==============================================================*/
create table PinYong (
   Eno                  CHAR(8)              not null,
   Rno                  CHAR(4)              not null,
   PYdate               DATE                 null,
   constraint PK_PINYONG primary key (Eno, Rno)
);


/*==============================================================*/
/* Table: department                                            */
/*==============================================================*/
create table department (
   Dno                  CHAR(2)              not null,
   dep_Dno              CHAR(2)              null,
   Dname                CHAR(20)             null,
   Dtype                CHAR(20)             null,
   Dman                 INT4                 null,
   constraint PK_DEPARTMENT primary key (Dno)
);


/*==============================================================*/
/* Table: empolyeer                                             */
/*==============================================================*/
create table empolyeer (
   Eno                  CHAR(8)              not null,
   Dno                  CHAR(2)              null,
   Ename                CHAR(20)             null,
   Epasswd              CHAR(12)             null,
   Esex                 CHAR(3)              null,
   Ebirth               DATE                 null,
   constraint PK_EMPOLYEER primary key (Eno)
);

/*==============================================================*/
/* Table: rank                                                  */
/*==============================================================*/
create table rank (
   Rno                  CHAR(4)              not null,
   Rname                CHAR(20)             null,
   constraint PK_RANK primary key (Rno)
);

/*==============================================================*/
/* Table: post                                                  */
/*==============================================================*/
create table post (
   Pno                  CHAR(4)              not null,
   Pname                CHAR(20)             null,
   constraint PK_POST primary key (Pno)
);



alter table DanRen
   add constraint FK_DANREN_DANREN_EMPOLYEE foreign key (Eno)
      references empolyeer (Eno)
      on delete restrict on update restrict;

alter table DanRen
   add constraint FK_DANREN_DANREN2_POST foreign key (Pno)
      references post (Pno)
      on delete restrict on update restrict;

alter table Have1
   add constraint FK_HAVE1_HAVE1_DEPARTME foreign key (Dno)
      references department (Dno)
      on delete restrict on update restrict;

alter table Have1
   add constraint FK_HAVE1_HAVE2_RANK foreign key (Rno)
      references rank (Rno)
      on delete restrict on update restrict;

alter table Have2
   add constraint FK_HAVE2_HAVE3_DEPARTME foreign key (Dno)
      references department (Dno)
      on delete restrict on update restrict;

alter table Have2
   add constraint FK_HAVE2_HAVE4_POST foreign key (Pno)
      references post (Pno)
      on delete restrict on update restrict;

alter table PinYong
   add constraint FK_PINYONG_PINYONG_EMPOLYEE foreign key (Eno)
      references empolyeer (Eno)
      on delete restrict on update restrict;

alter table PinYong
   add constraint FK_PINYONG_PINYONG2_RANK foreign key (Rno)
      references rank (Rno)
      on delete restrict on update restrict;

alter table department
   add constraint FK_DEPARTME_SUOSHU_DEPARTME foreign key (dep_Dno)
      references department (Dno)
      on delete restrict on update restrict;

alter table empolyeer
   add constraint FK_EMPOLYEE_BELONG_DEPARTME foreign key (Dno)
      references department (Dno)
      on delete restrict on update restrict;

3.数据库创建过程描述

参考华为的 openGauss 安装文件，首先在云端购买弹性云服务器

然后本地使用 ssh 连接到云主机，根据步骤下载安装openGauss

服务器打开 openGauss 服务，并使用 _Date Studio_连接数据库，并运行保存的DDL命令建立数据表

最终创建各表如下：
部门表

员工表

聘用表

任职表

拥有表1

拥有表2

职务表

职级表

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

查找员工中年龄最大的人的姓名以及编号
- 代码：
  select ename,eno from empolyeer where ebirth IN (select min(ebirth) from empolyeer);
- 查询结果
查找张三的职务是什么
- 代码：
  select pname from post where pno IN (select pno from danren where eno IN (select eno from empolyeer where ename = '张三'));
- 查询结果
查询财政部有哪些具体的职务
- 代码：
  select post.*,have2.* from post,have2 where post.pno = have2.pno AND have2.dno IN (select dno from department where dname = '财政部');
- 查询结果
查询担任过一级科员的男性员工姓名
- 代码：
  select ename from empolyeer where eno IN (select eno from pinyong where rno IN (select rno from rank where rname = '一级科员')) INTERSECT select ename from empolyeer where esex='男';
- 查询结果