华工2017 数据库(含课程设计)大作业(4)

数据库原理与设计

5. 数据库设计

5.1. 数据库设计概述

根据概念设计的步骤，产生的E-R图，然后再将E-R图各个部分分解。 （E-R图具体见4.5.ER模型图）

根据购书中心信息管理系统的功能模块进行划分和设计局部E-R图，根据需求建立相关联系。

5.2. 数据表及数据字典设置

数据库表规划及设置，如下图所示：

5.2.1. 数据库创建

Code: CREATE DATABASE [BookShop] CONTAINMENT = NONE ON PRIMARY ( NAME = N'BookShop', FILENAME = N'F:\\My Documents\\Desktop\\Sam的技术作业\\Data\\BookShop.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB ) LOG ON ( NAME = N'BookShop_log', FILENAME = N'F:\\My Documents\\Desktop\\Sam的技术作业\\Data\\BookShop_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB ) GO 第 16 页 共 36 页

数据库原理与设计

5.2.2. 购书中心基础信息

Code: USE [BookShop] GO CREATE TABLE [dbo].[centre]( [id] [int] IDENTITY(1,1) NOT NULL, [name] [varchar](50) NULL, [manager] [nvarchar](50) NULL, [cost] [nchar](10) NULL ) ON [PRIMARY] GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'编码' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'centre', @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'centre', @level2type=N'COLUMN',@level2name=N'name' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'总经理' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'centre', @level2type=N'COLUMN',@level2name=N'manager' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'运营费用' , @level0type=N'SCHEMA',@level0name=N'dbo', 第 17 页 共 36 页

数据库原理与设计

@level1type=N'TABLE',@level1name=N'centre', @level2type=N'COLUMN',@level2name=N'cost' GO 5.2.3. 部门信息

Code: CREATE TABLE [dbo].[department]( [id] [int] IDENTITY(1,1) NOT NULL, [manager] [varchar](50) NULL, [address] [varchar](100) NULL, [tel] [varchar](10) NULL, [cost] [int] NULL ) ON [PRIMARY] GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'department', @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'department', @level2type=N'COLUMN',@level2name=N'manager' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'地址' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'department', 第 18 页 共 36 页

数据库原理与设计

@level2type=N'COLUMN',@level2name=N'address' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'电话' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'department', @level2type=N'COLUMN',@level2name=N'tel' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'运营费用' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'department', @level2type=N'COLUMN',@level2name=N'cost' GO 5.2.4. 员工信息

Code: CREATE TABLE [dbo].[employe]( [id] [int] IDENTITY(1,1) NOT NULL, [name] [varchar](10) NULL, [salary] [int] NULL, [position] [varchar](10) NULL, [sex] [char](2) NULL, [age] [int] NULL ) ON [PRIMARY] GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'员工号' , @level0type=N'SCHEMA',@level0name=N'dbo',

@level1type=N'TABLE',@level1name=N'employe', 第 19 页 共 36 页

数据库原理与设计

@level2type=N'COLUMN',@level2name=N'id' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'员工姓名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level2type=N'COLUMN',@level2name=N'name' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'工资' , @level1type=N'TABLE',@level1name=N'employe', @level0type=N'SCHEMA',@level0name=N'dbo', @level2type=N'COLUMN',@level2name=N'salary' GO EXEC sys.sp_addextendedproperty @level1type=N'TABLE',@level1name=N'employe', @name=N'MS_Description', @value=N'职位' , @level0type=N'SCHEMA',@level0name=N'dbo', @level2type=N'COLUMN',@level2name=N'position' GO EXEC sys.sp_addextendedproperty @level1type=N'TABLE',@level1name=N'employe', @name=N'MS_Description', @value=N'性别' , @level0type=N'SCHEMA',@level0name=N'dbo', @level2type=N'COLUMN',@level2name=N'sex' GO EXEC sys.sp_addextendedproperty @level1type=N'TABLE',@level1name=N'employe', @name=N'MS_Description', @value=N'年龄' , @level0type=N'SCHEMA',@level0name=N'dbo', @level2type=N'COLUMN',@level2name=N'age' GO @level1type=N'TABLE',@level1name=N'employe', 5.2.5. 出版商管理

Code: CREATE TABLE [dbo].[publisher]( [id] [int] IDENTITY(1,1) NOT NULL, [name] [varchar](50) NULL, 第 20 页 共 36 页

百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说综合文库华工2017 数据库(含课程设计)大作业(4)在线全文阅读。