一、数据库结构设计三范式
第一范式：是对属性的原子性，要求属性具有原子性，不可再分解。
如有如下表结构设计：
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.StuIdinner 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）一对一关系（学生基本信息--学生详情）
方案一：
海南党性培训 www.fjganxun.cn 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), --QQstuPhone 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), --QQstuPhone 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), --QQstuPhone 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.StuIdinner 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--张飞身份证：420107199602034138insert 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 AccountInfoselect * from BankCard--进行存钱操作，刘备存钱2000元，关羽存钱：8000元，张飞存钱：500000元select * from AccountInfoupdate 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())
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
