2023年数据库应用技术大作业模板.doc

数据库大作业

课题名称

专 业

班 级

学 号

姓 名

教 师

成 绩

2023年11月 日

1. 需求分析
（加入需求分析的概念）
描述题目内容
1.1 数据流图（DFD）
什么是数据流图。
画数据流图

图1-1 XXXX图

1.2 数据字典（DD）

什么是数据字典
写数据字典
数据文献：订单明细表
文献组成：订单序号Id, 订单编号, 菜名, 价格, 数量, 下单时间

数 据 项：订单序号Id
数据类型：整型
数据长度：4

数 据 项：订单编号
数据类型：可变字符类型
数据长度：50

数据组成：A+日期时间

数 据 项：菜名
数据类型：可变字符类型
数据长度：50

2. 概念结构设计
什么概念结构设计
E-R图（在Powerdesigner中创建概念模型，粘贴图）

图2-1 XXXX图

3. 逻辑结构设计
关系模式（（在Powerdesigner中由概念模型转化为物理数据模型，粘图））

图3-1 XXXX图

4. 建表SQL语句
由物理数据模型生成SQL Server 2023数据库的建表语句。
/*==============================================================*/
/* DBMS name: Microsoft SQL Server 2023 */
/* Created on: 2023-9-16 14:39:14 */
/*==============================================================*/

if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('SC') and o.name = 'FK_SC_SC_S')
alter table SC
drop constraint FK_SC_SC_S
go

if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('SC') and o.name = 'FK_SC_SC2_C')
alter table SC
drop constraint FK_SC_SC2_C
go

if exists select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('TC') and o.name = 'FK_TC_TC_T')
alter table TC
drop constraint FK_TC_TC_T
go

if exists (select 1
from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
where r.fkeyid = object_id('TC') and o.name = 'FK_TC_TC2_C')
alter table TC
drop constraint FK_TC_TC2_C
go

if exists (select 1
from sysobjects
where id = object_id('C')
and type = 'U')
drop table C
go

if exists (select 1
from sysobjects
where id = object_id('S')
and type = 'U')
drop table S
go

if exists (select 1
from sysindexes
where id = object_id('SC')
and name = 'SC2_FK'
and indid > 0
and indid < 255)
drop index SC.SC2_FK
go

if exists (select 1
from sysindexes
where id = object_id('SC')
and name = 'SC_FK'
and indid > 0
and indid < 255)
drop index SC.SC_FK
go

if exists (select 1
from sysobjects
where id = object_id('SC')
and type = 'U')
drop table SC
go

if exists (select 1
from sysobjects
where id = object_id('T')
and type = 'U')
drop table T
go

if exists (select 1
from sysindexes
where id = object_id('TC')
and name = 'TC2_FK'
and indid > 0
and indid < 255)
drop index TC.TC2_FK
go

if exists (select 1
from sysindexes
where id = object_id('TC')
and name = 'TC_FK'
and indid > 0
and indid < 255)
drop index TC.TC_FK
go

if exists (select 1
from sysobjects
where id = object_id('TC')
and type = 'U')
drop table TC
go

/*==========================================/
/* Table: C */
/*==============================================================*/
create table C (
CNo char(2) not null,
CN varchar(40) null,
CT smallint null,
constraint PK_C primary key nonclustered (CNo)
)
go

/*==============================================================*/
/* Table: S */
/*==============================================================*/
create table S (
SNo char(2) not null,
SN varchar(40) null,
Gen char(2) null,
Birth datetime null,
Dept varchar(40) null,
constraint PKS primary key nonclustered (SNo)
)
go

/*==============================================================*/
/* Table: SC */
/*==============================================================*/
create table SC (
SNo char(2) not null,
CNo char(2) not null,
Score smallint null,
constraint PK_SC primary key (SNo, CNo)
)
go

/*==============================================================*/
/* Index: SC_FK */
/*==============================================================*/
create index SC_FK on SC (
SNo ASC
)
go

/*==============================================================*/
/* Index: SC2_FK */
/*============================/
create index SC2_FK on SC (
CNo ASC
)
go

/*==============================================================*/
/* Table: T */
/*==============================================================*/
create table T (
TNo char(2) not null,
TN varchar(40) null,
Gen char(2) null,
Birth datetime null,
Prof varchar(40) null,
Sal int null,
Comm int null,
Dept varchar(40) null,
constraint PK_T primary key nonclustered (TNo)
)
go

/*==============================================================*/
/* Table: TC */
/*==============================================================*/
create table TC (
TNo char(2) not null,
CNo char(2) not null,
constraint PK_TC primary key (TNo, CNo)
)
go

/*=======================/
/* Index: TC_FK */
/*==============================================================*/
create index TC_FK on TC (
TNo ASC
)
go

/*==============================================================*/
/* Index: TC2_FK */
/*==============================================================*/
create index TC2_FK on TC (
CNo ASC
)
go

alter table SC
add constraint FK_SC_SC_S foreign key (SNo)
references S (SNo)
go

alter table SC
add constraint FK_SC_SC2_C foreign key (CNo)
references C (CNo)
go

alter table TC
add constraint FK_TC_TC_T foreign key (TNo)
references T (TNo)
go

alter table TC
add constraint FK_TC_TC2_C foreign key (CNo)
references C (CNo)
go
5. 结论
心得体会（假如有相同的，则都不及格）
参考教材：
《数据库实验指导》 主编：杨海霞 出版社：人民邮电出版社 书号：978-115-16535-0