一个图书馆描述如下:图书馆存有大量的图书,拥有大量的读者,读者包括学生,教师,社会人员,每类人所借图书的本数不同,学生一次可以借4本,教师一次可以借6本,社会人员一次可以借3本,图书馆经常有读者的注册和图书的添加,每位读者可以多次借同一本书,每位读者的借书日期只有两个月的期限,期限自动生成,两个月后读者只有交罚款后才能借书。在此数据库中经常查看某个读者的借书情况和某本书被外借的情况,请根据所学知识设计一个图书管理系统数据库能满足以上基本要求。
解决方案 »
- SQL2008写一个导出表中数据的脚本
- 关于mysql的疑问
- 请问各位大侠:如何将Excel表格转换成sql server 2000中的数据库表?
- 请问如何知道指定表中有沒有該列,如沒有的话就插入一新列?谢谢!
- 如何实现 应用程序、MSDE、数据库文件一起制作一个安装程序 ,请在坐大侠慷慨出招
- 这是计算一个月第一天的SQL 脚本: SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
- 请高手写个SQL语句
- 谁熟悉SQL SERVER7?
- 三层结构 页面取值
- SQL新手,有个关于存储过程的小问题请教?
- 求救一个SQL语句?如何把从1到1000万的这些顺序记录用最高效的SQL语句读到数据库里?谢谢!
- 如何將datetime型轉換為date型
----------------------------------------------------------图书类型表
------------------
图书类型编号
图书类型名称
......
图书信息表:
------------------
图书编号
馆内部编号
图书类型编号
图书名称
图书作者
......
借阅标志
读者类型表
------------------
读者类型编号
读者类型名称
借阅时限
借阅数限
......
读者信息表
------------------
读者编号
读者类型编号
读者姓名
......
借阅信息表
------------------
读者编号
馆内部编号
借阅时间 --默认getdate()
归还时间
......
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[tsys_zzjyjl]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[tsys_zzjyjl](
[work_no] [char](10) NULL,
[borrow_date] [datetime] NULL,
[return_date] [datetime] NULL,
[borrowday] [char](10) NULL,
[re] [char](500) NULL,
[b_] [char](20) NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[tsys_tskeyword]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[tsys_tskeyword](
[keyword] [char](100) NULL,
[book_no] [char](50) NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[tsys_yssq]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[tsys_yssq](
[department] [char](100) NULL,
[movie_name] [char](100) NULL,
[sq_date] [datetime] NULL,
[work_no] [char](50) NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[tsys_yskeyword]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[tsys_yskeyword](
[keyword] [char](100) NULL,
[movie_no] [char](50) NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[tsys_tssq]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[tsys_tssq](
[department] [char](100) NULL,
[book_name] [char](300) NULL,
[publisher] [char](100) NULL,
[publishing_com] [char](100) NULL,
[price] [char](50) NULL,
[importance] [char](50) NULL,
[sq_date] [datetime] NULL,
[work_no] [char](50) NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[tsys_ysinfo]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[tsys_ysinfo](
[movie_no] [char](50) NULL,
[movie_name] [char](300) NULL,
[zone_no] [char](100) NULL,
[department] [char](100) NULL,
[bar_code] [char](100) NULL,
[keyword] [char](100) NULL,
[input_date] [datetime] NULL,
[input_status] [char](100) NULL,
[price] [char](100) NULL,
[re] [char](100) NULL,
[borrow_status] [char](100) NULL,
[id] [int] NULL,
[movie_pos] [char](100) NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[tsys_tsjyjl]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[tsys_tsjyjl](
[book_no] [char](50) NULL,
[book_name] [char](300) NULL,
[work_no] [char](10) NULL,
[borrow_date] [datetime] NULL,
[return_date] [datetime] NULL,
[input_status] [char](100) NULL,
[return_status] [char](100) NULL,
[borrow_status] [char](100) NULL,
[bar_code] [char](100) NULL,
[borrowday] [char](10) NULL,
[lose_date] [datetime] NULL,
[limit_date] [datetime] NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[tsys_ysjyjl]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[tsys_ysjyjl](
[movie_no] [char](50) NULL,
[movie_name] [char](300) NULL,
[work_no] [char](10) NULL,
[bar_code] [char](100) NULL,
[borrow_date] [datetime] NULL,
[return_date] [datetime] NULL,
[input_status] [char](100) NULL,
[return_status] [char](100) NULL,
[borrow_status] [char](100) NULL,
[borrowday] [char](10) NULL,
[lose_date] [datetime] NULL,
[limit_date] [datetime] NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[tsys_ysfj]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[tsys_ysfj](
[movie_no] [char](100) NULL,
[true_name] [char](300) NULL,
[false_name] [char](300) NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[tsys_tsyy]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[tsys_tsyy](
[work_no] [char](100) NULL,
[book_no] [char](100) NULL,
[book_name] [char](300) NULL,
[bar_code] [char](100) NULL,
[keyword] [char](50) NULL,
[re] [char](100) NULL,
[input_status] [char](100) NULL,
[borrow_status] [char](100) NULL,
[booking_date] [datetime] NULL,
[id] [int] NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[tsys_ysyy]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[tsys_ysyy](
[work_no] [char](100) NULL,
[movie_no] [char](100) NULL,
[movie_name] [char](300) NULL,
[bar_code] [char](100) NULL,
[keyword] [char](50) NULL,
[re] [char](100) NULL,
[input_status] [char](100) NULL,
[borrow_status] [char](100) NULL,
[booking_date] [datetime] NULL,
[id] [int] NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[tsys_admin]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[tsys_admin](
[work_no] [char](50) NULL,
[admin] [char](10) NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[tsys_tstemp]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[tsys_tstemp](
[book_sum] [int] NULL,
[bar_code] [char](50) NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[tsys_ystemp]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[tsys_ystemp](
[movie_sum] [int] NULL,
[bar_code] [char](50) NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[tsys_tsinfo]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[tsys_tsinfo](
[book_no] [char](50) NULL,
[book_name] [char](300) NULL,
[publisher] [char](100) NULL,
[publishing_com] [char](100) NULL,
[department] [char](100) NULL,
[bar_code] [char](100) NULL,
[keyword] [char](100) NULL,
[input_date] [datetime] NULL,
[input_status] [char](100) NULL,
[price] [char](100) NULL,
[re] [char](100) NULL,
[borrow_status] [char](100) NULL,
[id] [int] NULL,
[book_pos] [char](100) NULL
) ON [PRIMARY]
END