汽车租赁系统SQL数据库系统设计 - 图文

4）.Customers(Customer_ID,Customer_name,Driver_licenseID,Credit_level,Telephone_num) 5）.Level(Level_num,Creditcard_level,Earnest_money,Oncredit)

6）.Checkout(Customer_ID,Car_ID,Rent_price,Rent_date,Expired_date,Days) 7）.income_book(Date,Sum_sales,Sum_rentprice,Customer_ID) 8）.Expenditure_book(Date,Mend_price,Purchase,Insuranceprice)

9）.Mend_book(Customer_ID,Car_ID,Mend_price,Credit_level,Factory_ID) 10）.Repair_factory(Factory_ID,Factory_name,Address,Service_range)

根据以上10个关系模式,在SQL Server创建了数据库:汽车租赁系统,并为每一个关系模式创建了一个表

分别是: Car、Subscribe、Rent、Customers、Level、Checkout、income_book、Expenditure_book、Mend_book、Repair_factory 3、数据字典： car 数据项名称 数据类型 描述 Car_ID nchar(10) 汽车号，不允许空值 Purchase_Price money(8) 汽车购买价格，不允许空值 Data_manufacture datetime(8) 出厂日期，不允许空值 Rent_price money(8) 每天租金，不允许空值 Insurance_detail text(16) 保险细节，不允许空值 Producer text(16) 生产商，不允许空值 Engine_num nchar(10) 发动机号，不允许空值 Capacity int(4) 可载人数，不允许空值 Fuel_num nchar(10) 燃料号，不允许空值 Type text(16) 车型，不允许空值 Rent_able int(2) 是否可租，可为空 Book_able int(2) 是否被预订，可为空 Subscribe 数据项名称 数据类型 描述 Book_ID nchar(10) 预订表号，不允许空值 Car_ID nchar(10) 预订车号，不允许空值 Customer_ID nchar(10) 客户号，不允许空值 Startbook_date datetime(8) 起始预订日，不允许空值 Expirebook_date datetime(8) 到期日，不允许空值 sumbook_date nchar(10) 总共租用时间，不允许为空，小于一个月 Rent 数据项名称 数据类型 描述 Rent_carid bigint(8) 租车单号,不允许空值,自动增长,种子1 Expired_date datetime(8) 租用到期日，不允许空值 Rent_date datetime(8) 租用起始日，不允许空值 Earnest_money money(8) 定金，不允许空值 Customer_ID nchar(10) 客户ID，不允许空值

Customers 数据项名称 Customer_ID Customer_name Credit_level Driver_licenseID Telephone_number 数据项名称 Level_num Creditcard_level Earnest_money Oncredit 数据类型 nchar(10) text(16) bigint(8) nchar(10) bigint(8) 描述 客户ID,不允许空值,自动增长,种子1 客户姓名,不允许空值 信用等级,不允许空值 租主驾照号,不允许空值 电话号码,不允许空值 Level 数据类型 描述 nchar(5) 信用等级号，不允许空值 text(16) 信用等级，不允许空值 money(8) 付定金，不允许空值 money(8) 允许赊欠额度，不允许空值 Checkout 数据项名称 Customer_ID Rent_price Rent_date Expired_date Days Car_ID 数据类型 nchar(10) money(8) datetime(8) datetime(8) nchar(10) nchar(10) 描述 客户号，不允许空值 日租金，不允许空值 起租日，不允许空值 归还日，不允许空值 总共租用时间，不允许空值 租用车号，不允许空值 数据项名称 Sum_sales Sum_rentprice Customer_ID Date income_book 数据类型 描述 money(8) 销售金额，允许为空 money(8) 租金，允许为空 nchar(10) 客户id，允许空值 datetime(8) 日期，允许空值 expenditure_book 数据类型 描述 money(8) 购买货物，允许为空 money(8) 保险金，允许为空 money(8) 维修费，允许为空 datetime(8) 日期，允许空值 Mend_book 数据类型 描述 nchar(10) 客户号，不允许空值 nchar(10) 维修厂号，不允许空值 nchar(10) 租用车号，不允许空值 money(8) 维修费，允许为空 text(16) 信用等级，不允许空值 数据项名称 Purchase Insurance_price Mend_price Date 数据项名称 Customer_ID Factory_ID Car_ID Mend_price Credit_level

数据项名称 Factory_name Address Service_range Factory_ID Repair_factory 数据类型 描述 text(16) 维修厂名称,不允许空值 text(16) 维修厂地址，不允许空值 text(16) 维修厂服务范围，不允许空值 nchar(10) 维修厂号，不允许空值 4、SQL语句

从SQL Server 导出的sql文件得到的：

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[customer_car]') and OBJECTPROPERTY(id, N'IsView') = 1)

drop view [dbo].[customer_car] GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[manager_Subscribe]') and OBJECTPROPERTY(id, N'IsView') = 1) drop view [dbo].[manager_Subscribe] GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[manager_mend]') and OBJECTPROPERTY(id, N'IsView') = 1) drop view [dbo].[manager_mend] GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[manager_rent]') and OBJECTPROPERTY(id, N'IsView') = 1)

drop view [dbo].[manager_rent] GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CAR]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[CAR] GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Checkout]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[Checkout] GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Customers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[Customers] GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Level]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Level] GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Mend_book]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[Mend_book] GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Rent]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Rent] GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Repair_factory]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[Repair_factory] GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Subscribe]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[Subscribe] GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[expenditure_book]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[expenditure_book] GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[income_book]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[income_book] GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[subscribe_book_date]') and OBJECTPROPERTY(id, N'IsRule') = 1) drop rule [dbo].[subscribe_book_date] GO

create rule subscribe_book_date as @range >=0 and @range <=30 GO

CREATE TABLE [dbo].[CAR] (

[Car_ID] [nchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL , [Purchase_Price] [money] NOT NULL ,

[Data_manufacture] [datetime] NOT NULL , [Rent_price] [money] NOT NULL ,

[Insurance_detail] [text] COLLATE Chinese_PRC_CI_AS NOT NULL , [Producer] [text] COLLATE Chinese_PRC_CI_AS NOT NULL ,

[Engine_num] [nchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL , [Capacity] [int] NOT NULL ,