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

数据库原理与设计

[tel] [varchar](50) NULL, [address] [varchar](50) NULL ) ON [PRIMARY] GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'编码' , @level0type=N'SCHEMA',@level0name=N'dbo', @level2type=N'COLUMN',@level2name=N'id' GO EXEC sys.sp_addextendedproperty @level1type=N'TABLE',@level1name=N'publisher', @name=N'MS_Description', @value=N'名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level2type=N'COLUMN',@level2name=N'name' GO @level1type=N'TABLE',@level1name=N'publisher', EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'联系电话' , @level0type=N'SCHEMA',@level0name=N'dbo', @level2type=N'COLUMN',@level2name=N'tel' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'联系地址' , @level0type=N'SCHEMA',@level0name=N'dbo', @level2type=N'COLUMN',@level2name=N'address' GO @level1type=N'TABLE',@level1name=N'publisher', @level1type=N'TABLE',@level1name=N'publisher', 5.2.6. 书籍管理

Code: CREATE TABLE [dbo].[book](

[id] [int] IDENTITY(1,1) NOT NULL, [name] [varchar](50) NULL, 第 21 页 共 36 页

数据库原理与设计

[model] [varchar](50) NULL, [price_batch] [int] NULL, [price_sale] [int] 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'book', @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'book', @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'book', @level2type=N'COLUMN',@level2name=N'model' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'批发价格' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'book', @level2type=N'COLUMN',@level2name=N'price_batch' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'零售价格' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'book', @level2type=N'COLUMN',@level2name=N'price_sale' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'采购成本' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'book',

第 22 页 共 36 页

数据库原理与设计

@level2type=N'COLUMN',@level2name=N'cost' GO 5.2.7. 仓库管理

Code: CREATE TABLE [dbo].[storehouse]( [id] [int] IDENTITY(1,1) NOT NULL, [tel] [varchar](50) NULL, [manager] [varchar](50) 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'storehouse', @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'storehouse', @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'storehouse',

第 23 页 共 36 页

数据库原理与设计

@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'storehouse', @level2type=N'COLUMN',@level2name=N'cost' GO 5.2.8. 书籍销售

Code: CREATE TABLE [dbo].[saleByEmploye]( [employeid] [int] NOT NULL, [bookid] [int] NOT NULL, [count] [int] NOT NULL, [addtime] [datetime] NOT NULL ) ON [PRIMARY] GO 5.2.9. 书籍采购

Code: CREATE TABLE [dbo].[purchasebyDepartment](

第 24 页 共 36 页

数据库原理与设计

[departmentid] [int] NOT NULL, [bookid] [int] NOT NULL, [count] [int] NOT NULL, [addtime] [datetime] NOT 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'purchasebyDepartment', @level2type=N'COLUMN',@level2name=N'addtime' GO 6. 界面设计

6.1. 登录界面

第 25 页 共 36 页