博客系统数据库设计报告
数据库设计说明书
个人博客系统
一数据库设计原则
1、E-R 模型向关系模型转换的规则
(1)一个实体型转化为一个关系模式。一般E-R 图中的一个实体转化为一个关系模式,实体的属性就是关系的属性,实体的代码就是关系的代码。
(2)一个1:1联系可以抓换为一个独立的关系模式,也可以与任意一端对应的关系模式合并。若单独作为一个关系模式,则该单独的关系模式的属性包括其自身的属性,以及与该联系相连的实体的码;该关系的码为n 端实体的属性。
(3)一个1:n 联系可以抓换为一个独立的关系模式,也可以与n 端对应的关系模式合并。
(4)一个m:n联系可以转换为一个独立的关系模式。该关系的属性包括联系自身的属性,以及与联系相连的实体的属性。各实体的码组成关系码或关系码的一部分。
(5)一个多元联系可以转换为一个独立的关系模式。与该多元联系相连的各实体的码,以及联系本身的属性均转换为关系的属性,各实体的码组成关系的码或关系码的一部分。
(7)具有相同码的关系模式可以合并。
(8)有些1:n 的联系,将属性合并到n 端后,该属性也可以作为主码的一部分。 2、主要设计内容表:
(1)本系统设计的表符合基本表的性质如: 1) 原子性。基本表中的字段是不可再分解的。
2) 原始性。基本表中的记录是原始数据(基础数据)的记录。 3) 演绎性。由基本表与代码表中的数据,可以派生出所有的输出数据。
4) 稳定性。基本表的结构是相对稳定的,表中的记录是要长期保存的。
(2)本系统设计的基本表及其字段之间的关系, 满足第三范式 (3)本系统的主键与外键的设计:一般而言,一个实体都有一个主键和一个外键。
(4)本系统还要符合主键PK 的取值方法的原则:用物理意义的字段名或字段名的组合。字段的个数不要太多,多了不但索引占用空间大,而且速度也慢。
(5)本系统还涉及视图的建立,它依赖数据源的实表而存在的,是使用数据库的 一个窗口,是基表数据综合的一种形式, 是数据处理的一种方法,是用户数据保密的一种手段。 3、设计工具:SQLServer2005企业管理器 4、规定常用数据类型:
本系统涉及的常用数据类型为int/Nvarchar(50)/Nvarchar (max) 5、标准ANSI/ISO:Core SQL-99
二、命名规范
(1) 实体(表)的命名
1) 表以名词或名词短语命名,确定表名是采用复数还是单数形
式。
2) 如果表或者是字段的名称仅有一个单词,那么建议不使用缩
写,而是用完整的单词
3) 所有的存储值列表的前面加一个前缀Z ,目的是将这些值列
表类排序在数据库最后。
4) 所有的冗余类的命名(主要是累计表)前面加上前缀X ,冗
余类是为了提高数据库效率,非规范化数据库的时候加入的字段或者表
5) 关联类通过用下划线连接两个基本类之后,再加前缀R 的方
式命名,后面按照字母顺序罗列两个表名或者表名的缩写。关联表用于保存多对多关系。
(2) 属性(列)的命名
1) 采用有意义的表名,表内的列要针对键采用一整套设计规
则。每个表都将有一个自动ID 作为主键,逻辑上的主键作为第一个候选主键来定义,如果是数据库自动生成的编码,统一命名为ID ;如果是自定义的逻辑上的编码则用缩写加“ID ”的方法命名。如果键是数字类型,可以用_NO作为后缀;如果是字符类型可以采用_CODE后缀。对列名应该采用标准的前缀和后缀。
2) 所有的属性加上有关类型的后缀,注意,如果还需要其它的
后缀,都放在类型后缀之前。 3) 采用前缀命名 (3) 视图的命名
1) 视图以V 作为前缀,其它命名规则和表的命名类似; 2) 命名应尽量体现各视图的功能。 (4) 变量名
变量名采用小写,若属于词组形式,用下划线分隔每个单词 (5) 命名中其他注意事项
1) 以上命名都不得超过30个字符的系统限制。变量名的长度限
制为29(不包括标识符@)
2) 数据对象、变量的命名都采用英文字符,禁止使用中文命名。
绝对不要再对象名的字符之间留空格。
3) 小心保留词,要保证你的字段名没有和保留词、数据库系统或
者常用访问方法冲突
4) 保持字段名和类型的一致性,在命名字段并为其指定数据类型
的时候一定要保证一致性。假如数据类型在一个表里是整数,那在另一个表里可就变成字符型了。
三、E-R 图
四、数据库表的设计 表4-1用户表(dbo.Admins )
表4-2用户照片表(dbo.photos )
表4-3回复信息表(dbo.Tags )
表4-4评论信息表(dbo.Articles )
表4-5文章信息表(dbo.Comments )
表4-6登陆信息表(dbo.denglu )
表4-7给我留言信息表(dbo.message )
表4-8用户视频信息表(dbo.vdeos )
表4-9文章类型信息表(dbo.votes )
五、数据关系图
附录:
一数据库设计的SQL 实现代码 SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tags]') AND type in (N'U')) BEGIN
CREATE TABLE [dbo].[Tags](
[Tag_ID] [int] IDENTITY(1,1) NOT NULL,
[Tag_Name] [nvarchar](50) NOT NULL,
[Tag_Dec] [nvarchar](200) NOT NULL,
CONSTRAINT [PK_Tags] PRIMARY KEY CLUSTERED
(
[Tag_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ID值' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Tags',
@level2type=N'COLUMN', @level2name=N'Tag_ID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户名' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Tags',
@level2type=N'COLUMN', @level2name=N'Tag_Name'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description',
@value=N'信息' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Tags',
@level2type=N'COLUMN', @level2name=N'Tag_Dec'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'回复信息表' ,@level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',
@level1name=N'Tags'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Votes]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Votes](
[Vote_ID] [int] IDENTITY(1,1) NOT NULL,
[Item] [nvarchar](200) NULL,
CONSTRAINT [PK_Votes] PRIMARY KEY CLUSTERED
(
[Vote_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Vote的 ID值' ,@level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',
@level1name=N'Votes', @level2type=N'COLUMN',
@level2name=N'Vote_ID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'文章类型名称' ,@level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',
@level1name=N'Votes', @level2type=N'COLUMN',
@level2name=N'Item'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'文章类型信息表' ,@level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',
@level1name=N'Votes'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Admins]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Admins](
[Admin_ID] [int] IDENTITY(1,1) NOT NULL,
[Admin_Name] [nvarchar](50) NULL,
[Password] [nvarchar](50) NULL,
CONSTRAINT [PK_Admins] PRIMARY KEY CLUSTERED
(
[Admin_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'标识唯一的用户信息编号' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',
@level1name=N'Admins', @level2type=N'COLUMN',
@level2name=N'Admin_ID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户名' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Admins',
@level2type=N'COLUMN', @level2name=N'Admin_Name'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'密码' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Admins',
@level2type=N'COLUMN', @level2name=N'Password'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户表' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Admins'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Articles]') AND type in (N'U')) BEGIN
CREATE TABLE [dbo].[Articles](
[Artcle_ID] [int] IDENTITY(1,1) NOT NULL,
[Titile] [nvarchar](50) NOT NULL,
[Artcle_Content] [nvarchar](max) NULL,
[Tag_ID] [int] NOT NULL,
[Dcc] [nvarchar](200) NOT NULL,
[ViewNum] [int] NOT NULL,
[PublishedTime] [datetime] NOT NULL,
[Admin_ID] [int] NOT NULL,
CONSTRAINT [PK_Articles] PRIMARY KEY CLUSTERED (
[Artcle_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'内容的ID 值' ,@level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',
@level1name=N'Articles', @level2type=N'COLUMN',
@level2name=N'Artcle_ID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'标题信息' ,@level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',
@level1name=N'Articles', @level2type=N'COLUMN',
@level2name=N'Titile'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'信息内容' ,@level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',
@level1name=N'Articles', @level2type=N'COLUMN',
@level2name=N'Artcle_Content'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'回复信息ID 值' ,@level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',
@level1name=N'Articles', @level2type=N'COLUMN',
@level2name=N'Tag_ID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'发表时间' ,@level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',
@level1name=N'Articles', @level2type=N'COLUMN',
@level2name=N'PublishedTime'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户信息编号' ,@level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',
@level1name=N'Articles', @level2type=N'COLUMN',
@level2name=N'Admin_ID'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'评论信息表' ,@level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',
@level1name=N'Articles'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Comments]') AND type in (N'U')) BEGIN
CREATE TABLE [dbo].[Comments](
[Cmt_ID] [int] IDENTITY(1,1) NOT NULL,
[Cmt_Content] [nvarchar](max) NULL,
[Admin_ID] [int] NOT NULL,
[Vote_ID] [int] NOT NULL,
[Cmt_Time] [datetime] NULL,
CONSTRAINT [PK_Comments] PRIMARY KEY CLUSTERED (
[Cmt_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'文章的ID 值' ,@level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',
@level1name=N'Comments', @level2type=N'COLUMN', @level2name=N'Cmt_ID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'文章信息' ,@level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',
@level1name=N'Comments', @level2type=N'COLUMN', @level2name=N'Cmt_Content'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户信息编号' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',
@level1name=N'Comments', @level2type=N'COLUMN',
@level2name=N'Admin_ID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'类型ID 值' ,@level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',
@level1name=N'Comments', @level2type=N'COLUMN',
@level2name=N'Vote_ID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'发表时间' ,@level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',
@level1name=N'Comments', @level2type=N'COLUMN',
@level2name=N'Cmt_Time'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'文章信息表' ,@level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',
@level1name=N'Comments'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Messages]') AND type in (N'U')) BEGIN
CREATE TABLE [dbo].[Messages](
[Mag_ID] [int] IDENTITY(1,1) NOT NULL,
[Mag_Title] [nvarchar](150) NULL,
[Mag_Content] [nvarchar](1000) NULL,
[Admin_ID] [int] NOT NULL,
[AddDate] [datetime] NULL,
CONSTRAINT [PK_Messages] PRIMARY KEY CLUSTERED (
[Mag_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'信息的ID 值' ,@level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',
@level1name=N'Messages', @level2type=N'COLUMN',
@level2name=N'Mag_ID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'简要信息' ,@level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',
@level1name=N'Messages', @level2type=N'COLUMN',
@level2name=N'Mag_Title'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'信息内容' ,@level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',
@level1name=N'Messages', @level2type=N'COLUMN',
@level2name=N'Mag_Content'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description',
@value=N'用户信息编号' ,@level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',
@level1name=N'Messages', @level2type=N'COLUMN',
@level2name=N'Admin_ID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'发表时间' ,@level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',
@level1name=N'Messages', @level2type=N'COLUMN',
@level2name=N'AddDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'给我留言信息表' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',
@level1name=N'Messages'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Photos]') AND type in (N'U')) BEGIN
CREATE TABLE [dbo].[Photos](
[Photo_ID] [int] IDENTITY(1,1) NOT NULL,
[Photo_Name] [nvarchar](50) NOT NULL,
[Photo_Intro] [nvarchar](200) NOT NULL,
[Photo_Url] [nvarchar](150) NOT NULL,
[AddData] [datetime] NULL,
[Admin_ID] [int] NOT NULL,
CONSTRAINT [PK_Photos] PRIMARY KEY CLUSTERED
(
[Photo_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'上传照片的ID 值' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',
@level1name=N'Photos', @level2type=N'COLUMN',
@level2name=N'Photo_ID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'照片信息' ,@level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',
@level1name=N'Photos', @level2type=N'COLUMN',
@level2name=N'Photo_Name'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'照片说明信息' ,@level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',
@level1name=N'Photos', @level2type=N'COLUMN',
@level2name=N'Photo_Intro'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'照片路径' ,@level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',
@level1name=N'Photos', @level2type=N'COLUMN',
@level2name=N'Photo_Url'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'照片上传日期' ,@level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',
@level1name=N'Photos', @level2type=N'COLUMN',
@level2name=N'AddData'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户信息编号' ,@level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',
@level1name=N'Photos', @level2type=N'COLUMN',
@level2name=N'Admin_ID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户照片信息表' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',
@level1name=N'Photos'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Vdeos]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Vdeos](
[Vdeos_ID] [int] IDENTITY(1,1) NOT NULL,
[Vdeos_Name] [nvarchar](50) NOT NULL,
[Vdeos_Url] [nvarchar](max) NULL,
[Vdeos_Des] [nvarchar](500) NULL,
[AddDate] [datetime] NULL,
[Admin_ID] [int] NOT NULL,
CONSTRAINT [PK_Vdeos] PRIMARY KEY CLUSTERED
(
[Vdeos_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description',
@value=N'视频的ID 值' ,@level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',
@level1name=N'Vdeos', @level2type=N'COLUMN',
@level2name=N'Vdeos_ID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'视频的名称' ,@level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',
@level1name=N'Vdeos', @level2type=N'COLUMN',
@level2name=N'Vdeos_Name'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'视频的路径' ,@level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',
@level1name=N'Vdeos', @level2type=N'COLUMN',
@level2name=N'Vdeos_Url'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'视频的描述信息' ,@level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',
@level1name=N'Vdeos', @level2type=N'COLUMN',
@level2name=N'Vdeos_Des'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'上传时间' ,@level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',
@level1name=N'Vdeos', @level2type=N'COLUMN',
@level2name=N'AddDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户信息编号' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',
@level1name=N'Vdeos', @level2type=N'COLUMN',
@level2name=N'Admin_ID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户视频信息表' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE',
@level1name=N'Vdeos'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Denglu]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Denglu](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Admin_ID] [int] NOT NULL,
CONSTRAINT [PK_Denglu] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ID值' ,@level0type=N'SCHEMA', @level0name=N'dbo',
@level1type=N'TABLE', @level1name=N'Denglu',
@level2type=N'COLUMN', @level2name=N'Id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户信息编号' ,@level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',
@level1name=N'Denglu', @level2type=N'COLUMN',
@level2name=N'Admin_ID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'登录信息表' ,@level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',
@level1name=N'Denglu'
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Articles_Admins]') AND
parent_object_id = OBJECT_ID(N'[dbo].[Articles]'))
ALTER TABLE [dbo].[Articles] WITH CHECK ADD CONSTRAINT
[FK_Articles_Admins] FOREIGN KEY([Admin_ID])
REFERENCES [dbo].[Admins] ([Admin_ID])
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Articles_Tags]') AND
parent_object_id = OBJECT_ID(N'[dbo].[Articles]'))
ALTER TABLE [dbo].[Articles] WITH CHECK ADD CONSTRAINT
[FK_Articles_Tags] FOREIGN KEY([Tag_ID])
REFERENCES [dbo].[Tags] ([Tag_ID])
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Comments_Admins]') AND
parent_object_id = OBJECT_ID(N'[dbo].[Comments]'))
ALTER TABLE [dbo].[Comments] WITH CHECK ADD CONSTRAINT
[FK_Comments_Admins] FOREIGN KEY([Admin_ID])
REFERENCES [dbo].[Admins] ([Admin_ID])
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Comments_Votes]') AND
parent_object_id = OBJECT_ID(N'[dbo].[Comments]'))
ALTER TABLE [dbo].[Comments] WITH CHECK ADD CONSTRAINT
[FK_Comments_Votes] FOREIGN KEY([Vote_ID])
REFERENCES [dbo].[Votes] ([Vote_ID])
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Messages_Admins]') AND
parent_object_id = OBJECT_ID(N'[dbo].[Messages]'))
ALTER TABLE [dbo].[Messages] WITH CHECK ADD CONSTRAINT
[FK_Messages_Admins] FOREIGN KEY([Admin_ID])
REFERENCES [dbo].[Admins] ([Admin_ID])
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Photos_Admins]') AND
parent_object_id = OBJECT_ID(N'[dbo].[Photos]'))
ALTER TABLE [dbo].[Photos] WITH CHECK ADD CONSTRAINT
[FK_Photos_Admins] FOREIGN KEY([Admin_ID])
REFERENCES [dbo].[Admins] ([Admin_ID])
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Vdeos_Admins]') AND parent_object_id = OBJECT_ID(N'[dbo].[Vdeos]'))
ALTER TABLE [dbo].[Vdeos] WITH CHECK ADD CONSTRAINT
[FK_Vdeos_Admins] FOREIGN KEY([Admin_ID])
REFERENCES [dbo].[Admins] ([Admin_ID])
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Denglu_Admins]') AND
parent_object_id = OBJECT_ID(N'[dbo].[Denglu]'))
ALTER TABLE [dbo].[Denglu] WITH CHECK ADD CONSTRAINT
[FK_Denglu_Admins] FOREIGN KEY([Admin_ID])
REFERENCES [dbo].[Admins] ([Admin_ID])
二、数据库使用的示例SQL 语句
示例1:添加新用户
INSERT Admins(Admin_ID, Admin_Name, Password) +VALUES (@Admin_ID, @ Admin_Name, @ Password);
示例2:通过Admin_ID获得用户
SELECT * FROM Admins WHERE Admin_ID = @Admin_ID