当前位置 : 主页 > 编程语言 > 其它开发 >

一、数据库设计

来源:互联网 收集:自由互联 发布时间:2022-05-30
一、数据库结构设计三范式 第一范式: 是对属性的原子性,要求属性具有原子性,不可再分解。 如有如下表结构设计: create table Student --学生表(StuId varchar(20) primary key,--学号StuName v
一、数据库结构设计三范式

第一范式:是对属性的原子性,要求属性具有原子性,不可再分解。

如有如下表结构设计:

create table Student --学生表
(
	StuId varchar(20) primary key,--学号
	StuName varchar(20) not null,--学生姓名
	StuContact varchar(50) not null, --联系方式
)
insert into Student(StuId,StuName,StuContact) 
values('001','刘备','QQ:185699887;Tel:13885874587')
select * from Student

上述设计则不满足第一范式,联系方式这一列并不是不可再分的最小单元,应修改为如下结构

create table Student --学生表
(
	StuId varchar(20) primary key,--学号
	StuName varchar(20) not null,--学生姓名
  	Tel varchar(20) not null, --联系电话
  	QQ varchar(20) not null,  --联系QQ
)

第二范式:是对记录的惟一性,要求记录有惟一标识,即实体的惟一性,即不存在部分依赖;

如有如下表结构设计:

--选课成绩表
create table StudentCourse
(
	StuId varchar(20),--学号
	StuName varchar(20) not null,--学生姓名
	CourseId varchar(20) not null,--课程编号
	CourseName varchar(20) not null, --选课课程名称
	CourseScore int not null, --考试成绩
)
insert into StudentCourse(StuId,StuName,CourseId,CourseName,CourseScore)
values('001','刘备','001','语文',80)
insert into StudentCourse(StuId,StuName,CourseId,CourseName,CourseScore)
values('001','刘备','002','数学',70)
insert into StudentCourse(StuId,StuName,CourseId,CourseName,CourseScore)
values('002','关羽','003','英语',80)
insert into StudentCourse(StuId,StuName,CourseId,CourseName,CourseScore)
values('003','张飞','003','英语',90)

上述设计中有两个事物,一个学生信息,一个课程信息,很显然这两个事物都没有保证实体的唯一性,这里的姓名依赖学号,课程名称依赖课程编号,所以不符合二范式。

create table Course --课程
(
	CourseId int primary key identity(1,1),--课程编号
	CourseName varchar(30) not null, --课程名称
	CourseContent text --课程介绍
)
insert into Course(CourseName,CourseContent) values('HTML','静态网页的制作')
insert into Course(CourseName,CourseContent) values('WinForm','Windows应用程序开发')

create table Student --学生
(
	StuId int primary key identity(1,1), --学生编号
	StuName varchar(50) not null, --学生名字
	StuSex char(2) not null --学生性别
)
insert into Student(StuName,StuSex) values('刘备','男')
insert into Student(StuName,StuSex) values('关羽','男')

create Table Exam --考试信息表
(
	ExamId int primary key identity(1,1), --选课成绩编号
	StuId int not null, --学生编号
	CourseId int not null,  --课程编号
	Score int not null, --考试分数
)
insert into Exam(StuId,CourseId,Score) values(1,1,90)
insert into Exam(StuId,CourseId,Score) values(1,2,80)
insert into Exam(StuId,CourseId,Score) values(2,2,85)

select * from Student inner join Exam on Student.StuId = Exam.StuId
inner join Course on Course.CourseId = Exam.CourseId

第三范式:要求任何字段不能由其他字段派生出来,它要求字段没有冗余,即不存在传递依赖 ;

如有如下表结构设计:

create table Student
(
	StuId varchar(20) primary key,--学号
	StuName varchar(20) not null,--学生姓名
	ProfessionalId int not null,--专业编号
	ProfessionalName varchar(50),--专业名称
	ProfessionalRemark varchar(200), --专业介绍
)
insert into Student(StuId,StuName,ProfessionalId,ProfessionalName,ProfessionalRemark)
values('001','刘备',1,'计算机','最牛的专业')
insert into Student(StuId,StuName,ProfessionalId,ProfessionalName,ProfessionalRemark)
values('002','关羽',2,'工商管理','管理学的基础专业')
insert into Student(StuId,StuName,ProfessionalId,ProfessionalName,ProfessionalRemark)
values('003','张飞',1,'计算机','最牛的专业')
select * from Student

上述设计种专业名称字段和专业介绍字段,在数据库种会产生很多冗余数据,不满足第二范式,优化方案如下:

create table Professional
(
	ProfessionalId int primary key identity(1,1),--专业编号
	ProfessionalName varchar(50),--专业名称
	ProfessionalRemark varchar(200), --专业介绍
)
create table Student
(
	StuId varchar(20) primary key,--学号
	StuName varchar(20) not null,--学生姓名
	ProfessionalId int not null,--专业编号
)
insert into Professional(ProfessionalName,ProfessionalRemark) values('计算机','最牛的专业')
insert into Professional(ProfessionalName,ProfessionalRemark) values('工商管理','管理学的基础专业')
insert into Student(StuId,StuName,ProfessionalId) values('001','刘备',1)
insert into Student(StuId,StuName,ProfessionalId) values('002','关羽',2)
insert into Student(StuId,StuName,ProfessionalId) values('003','张飞',1)
select * from Student
二、表关系

(1)一对多关系(专业--学生)

create table Profession  --专业
(
	ProId int primary key identity(1,1), --专业编号
	ProName varchar(50) not null --专业名称
)
create table Student --学生
(
	StuId int primary key identity(1,1), --学生编号
	ProId int references Profession(ProId),
	StuName varchar(50) not null, --学生名字
	StuSex char(2) not null --学生性别
)
insert into Profession(ProName) values('软件开发')
insert into Profession(ProName) values('企业信息化')
insert into Student(ProId,StuName,StuSex) values(1,'刘备','男')
insert into Student(ProId,StuName,StuSex) values(1,'关羽','男')
insert into Student(ProId,StuName,StuSex) values(2,'张飞','男')
insert into Student(ProId,StuName,StuSex) values(2,'赵云','男')
select * from Student left join Profession on Student.ProId = Profession.ProId

(2)一对一关系(学生基本信息--学生详情)

方案一:

create table StudentBasicInfo  --学生基本信息
(
	StuNo varchar(20) primary key not null,  --学号
	StuName varchar(20) not null, --姓名
	StuSex nvarchar(1) not null  --性别
)
create table StudentDetailInfo  --学生详细信息
(
	StuNo varchar(20) primary key not null,
	StuQQ varchar(20), --QQ
	stuPhone varchar(20), --电话
	StuMail varchar(100), --邮箱
	StuBirth date         --生日
)
--插入数据的时候按照顺序先插入刘备的基本信息,在插入关羽的基本信息
--insert into StudentBasicInfo(StuNo,StuName,StuSex) values('QH001','刘备','男')
--insert into StudentBasicInfo(StuNo,StuName,StuSex) values('QH002','关羽','男')
--插入数据的时候按照顺序先插入关羽的详细信息,在插入刘备的详细信息
--insert into StudentDetailInfo(StuNo,StuQQ,stuPhone,StuMail,StuBirth)
--values('QH002','156545214','13654525478','guanyu@163.com','1996-6-6')
--insert into StudentDetailInfo(StuNo,StuQQ,stuPhone,StuMail,StuBirth)
--values('QH001','186587854','15326545214','liubei@163.com','1998-8-8')

--或者如下结构也行:
create table StudentBasicInfo  --学生基本信息
(
	StuNo int primary key identity(1,1),  --学号
	StuName varchar(20) not null, --姓名
	StuSex nvarchar(1) not null  --性别
)
create table StudentDetailInfo  --学生详细信息
(
	StuNo int primary key,  --学号
	StuQQ varchar(20), --QQ
	stuPhone varchar(20), --电话
	StuMail varchar(100), --邮箱
	StuBirth date         --生日
)

此方案要求两个表的主键相等关系确定一个学生,所以此设计必须保证主键是可以维护和编辑的,如果主键是自动增长,将很大程度增加了数据维护的难度。

方案二:

create table StudentBasicInfo  --学生基本信息
(
	StuNo int primary key identity(1,1),  --学号
	StuName varchar(20) not null, --姓名
	StuSex nvarchar(1) not null  --性别
)
create table StudentDetailInfo  --学生详细信息
(
	StuDetailNo int primary key identity(1,1),  --详细信息编号
	StuNo int references StudentBasicInfo(StuNo) --学号,外键
	StuQQ varchar(20), --QQ
	stuPhone varchar(20), --电话
	StuMail varchar(100), --邮箱
	StuBirth date         --生日
)

此方案中实际上我们是使用一对多的模式来表示一对一,保证多的表中只有一条对应数据即可。

(3)多对多关系:(选课成绩--学生)

create table Course --课程
(
	CourseId int primary key identity(1,1),--课程编号
	CourseName varchar(30) not null, --课程名称
	CourseContent text --课程介绍
)
insert into Course(CourseName,CourseContent) values('HTML','静态网页的制作')
insert into Course(CourseName,CourseContent) values('WinForm','Windows应用程序开发')

create table Student --学生
(
	StuId int primary key identity(1,1), --学生编号
	StuName varchar(50) not null, --学生名字
	StuSex char(2) not null --学生性别
)
insert into Student(StuName,StuSex) values('刘备','男')
insert into Student(StuName,StuSex) values('关羽','男')

create Table Exam --考试信息表
(
	ExamId int primary key identity(1,1), --选课成绩编号
	StuId int not null, --学生编号
	CourseId int not null,  --课程编号
	Score int not null, --考试分数
)
insert into Exam(StuId,CourseId,Score) values(1,1,90)
insert into Exam(StuId,CourseId,Score) values(1,2,80)
insert into Exam(StuId,CourseId,Score) values(2,2,85)

select * from Student inner join Exam on Student.StuId = Exam.StuId
inner join Course on Course.CourseId = Exam.CourseId

此方案中,一个学生可以有多门选课,一门课程也可以被多个学生选择,我们称之为多对多关系,在处理多对多关系的时候,我们需要建立一个中间关联表,该关联表中需要有另外两张表的主键字段。

三、数据库设计案例

业务需求说明:
模拟银行业务,设计简易版的银行数据库表结构,要求可以完成以下基本功能需求:
1.银行开户(注册个人信息)及开卡(办理银行卡)(一个人可以办理多张银行卡,但是最多只能办理3张)
2.存钱
3.查询余额
4.取钱
5.转账
6.查看交易记录
7.账户挂失
8.账户注销

表设计:

1.账户信息表:存储个人信息。
2.银行卡表:存储银行卡信息。
3.交易信息表(存储存钱和取钱的记录)
4.转账信息表(存储转账信息记录)
5.状态信息变化表(存储银行卡状态变化的记录,状态有1:正常,2:挂失,3:冻结,4:注销)

表结构设计:

--账户信息表:存储个人信息
create table AccountInfo
(
	AccountId int primary key identity(1,1), --账户编号
	AccountCode varchar(20) not null, --身份证号码
	AccountPhone varchar(20) not null, --电话号码
	RealName varchar(20) not null, --真实姓名
	OpenTime smalldatetime not null, --开户时间
)
--银行卡表:存储银行卡信息
create table BankCard
(
	CardNo varchar(30) primary key, --银行卡卡号
	AccountId int not null, --账户编号(与账户信息表形成主外键关系)
	CardPwd varchar(30) not null, --银行卡密码
	CardMoney money not null, --银行卡余额
	CardState int not null,--1:正常,2:挂失,3:冻结,4:注销
	CardTime smalldatetime default(getdate()) --开卡时间
)
--交易信息表(存储存钱和取钱的记录)
create table CardExchange
(
	ExchangeId int primary key identity(1,1), --交易自动编号
	CardNo varchar(30) not null, --银行卡号(与银行卡表形成主外键关系)
	MoneyInBank money not null, --存钱金额
	MoneyOutBank money not null, --取钱金额
	ExchangeTime smalldatetime not null, --交易时间
)
--转账信息表(存储转账信息记录)
create table CardTransfer
(
	TransferId int primary key identity(1,1),--转账自动编号
	CardNoOut varchar(30) not null, --转出银行卡号(与银行卡表形成主外键关系)
	CardNoIn varchar(30) not null, --转入银行卡号(与银行卡表形成主外键关系)
	TransferMoney money not null,--交易金额
	TransferTime smalldatetime not null, --交易时间
)
--状态信息变化表(存储银行卡状态变化的记录,状态有1:正常,2:挂失,3:冻结,4:注销)
create table CardStateChange
(
	StateId int primary key identity(1,1),--状态信息自动编号
	CardNo varchar(30) not null, --银行卡号(与银行卡表形成主外键关系)
	OldState int not null, --银行卡原始状态
	NewState int not null, --银行卡新状态
	StateWhy varchar(200) not null, --状态变化原因
	StateTime smalldatetime not null, --记录产生时间
)

添加测试数据:

--为刘备,关羽,张飞三个人进行开户开卡的操作
--刘备身份证:420107198905064135
--关羽身份证:420107199507104133
--张飞身份证:420107199602034138
insert into AccountInfo(AccountCode,AccountPhone,RealName,OpenTime)
values('420107198905064135','13554785425','刘备',GETDATE())
insert into BankCard(CardNo,AccountId,CardPwd,CardMoney,CardState)
values('6225125478544587',1,'123456',0,1)

insert into AccountInfo(AccountCode,AccountPhone,RealName,OpenTime)
values('420107199507104133','13454788854','关羽',GETDATE())
insert into BankCard(CardNo,AccountId,CardPwd,CardMoney,CardState)
values('6225547858741263',2,'123456',0,1)

insert into AccountInfo(AccountCode,AccountPhone,RealName,OpenTime)
values('420107199602034138','13456896321','张飞',GETDATE())
insert into BankCard(CardNo,AccountId,CardPwd,CardMoney,CardState)
values('6225547854125656',3,'123456',0,1)

select * from AccountInfo
select * from BankCard

--进行存钱操作,刘备存钱2000元,关羽存钱:8000元,张飞存钱:500000元
select * from AccountInfo
update BankCard set CardMoney = CardMoney + 2000 where CardNo = '6225125478544587'
insert into CardExchange(CardNo,MoneyInBank,MoneyOutBank,ExchangeTime)
values('6225125478544587',2000,0,GETDATE())

update BankCard set CardMoney = CardMoney + 8000 where CardNo = '6225547858741263'
insert into CardExchange(CardNo,MoneyInBank,MoneyOutBank,ExchangeTime)
values('6225547858741263',8000,0,GETDATE())

update BankCard set CardMoney = CardMoney + 500000 where CardNo = '6225547854125656'
insert into CardExchange(CardNo,MoneyInBank,MoneyOutBank,ExchangeTime)
values('6225547854125656',500000,0,GETDATE())

--转账:刘备给张飞转账1000元
update BankCard set CardMoney = CardMoney -1000 where CardNo = '6225125478544587'
update BankCard set CardMoney = CardMoney + 1000 where CardNo = '6225547854125656'
insert into CardTransfer(CardNoOut,CardNoIn,TransferMoney,TransferTime)
values('6225125478544587','6225547854125656',1000,GETDATE())

网友评论