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

数据库大作业

课题名称

专 业

班 级

学 号

姓 名

教 师

成 绩

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 数据库的建表语句。
/*==============================================================*/
/* DBMS name: Microsoft SQL Server */
/* Created on: -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 PK_S 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