hoteldb.sql

酒店管理系统,您酒店物业管理的好帮手

      add constraint pk_RoomUseID primary key(RoomUseID)

--alter table RoomUse

 --     add constraint ck_RoomUseID check(RoomUseID like 'DD[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')

alter table RoomUse

      add constraint fk_BuyerID foreign key(BuyerID) references Buyer(BuyerID)




alter table RoomUse

      add constraint df_Rebate default(1) for Rebate

alter table RoomUse

      add constraint fk_UserID foreign key(UserID) references UserInfo(UserID)

alter table RoomUse

      add constraint fk_RoomUse_GuestRoomID foreign key(GuestRoomID) references GuestRoom(GuestRoomID)

go

---11.员工表  Employee

alter table Employee

      add constraint pk_EmployeeID primary key(EmployeeID)

alter table Employee

      add constraint ck_EmployeeID check(EmployeeID like 'EM[0-9][0-9][0-9][0-9]')



alter table Employee
add constraint FK_Employee_TypeID foreign key(TypeID) references EmployeeType(TypeID)
go

---12.用户表  UserInfo

alter table UserInfo

      add constraint ck_UserID check(UserID like 'US[0-9][0-9][0-9][0-9]')

alter table UserInfo

      add constraint fk_EmployeeID foreign key(EmployeeID) references Employee(EmployeeID)

alter table UserInfo

      add constraint df_frontManage default(1) for frontManage

alter table UserInfo

      add constraint df_financingManage default(0) for financingManage

alter table UserInfo

      add constraint df_SystemManage default(0) for SystemManage

alter table UserInfo

      add constraint df_PriceManage default(0) for PriceManage

alter table UserInfo

      add constraint df_InfoManage default(0) for InfoManage

go

---13.商品类型表 ItemsType


alter table ItemsType

      add constraint pk_ItemsTypeID primary key(ItemsTypeID)
go

---14.商品项目价格表   ItemsPrice

alter table ItemsPrice

      add constraint pk_ItemID primary key(ItemID)

alter table ItemsPrice
add constraint PK_ItemsPrice_ItemsTypeID foreign key(ItemsTypeID) references ItemsType(ItemsTypeID)
go

---15.消费清单表(帐目)表	Account

alter table Account

      add constraint pk_AccountID primary key(AccountID)



alter table Account

      add constraint fk_ItemID foreign key(ItemID) references ItemsPrice(ItemID)

alter table Account

      add constraint df_Rb default(1) for Rebate

go



---17.会员卡表 associator

alter table associator

      add constraint pk_CarID primary key(CarID)

alter table associator

      add constraint df_Reb default(1) for Rebate

go

---18.会员卡号表   associatorInfo

alter table associatorInfo

      add constraint pk_CarNum primary key(CarNum)

alter table associatorInfo

      add constraint fk_CarID foreign key(CarID) references associator(CarID)

alter table associatorInfo

      add constraint fk_associatorInfo_Buyer foreign key(BuyerID) references Buyer(BuyerID)

alter table associatorInfo

      add constraint df_balance default(0) for balance

alter table associatorInfo

      add constraint df_cumulate default(0) for cumulate

alter table associatorInfo

      add constraint df_UseCount default(0) for UseCount

alter table associatorInfo

      add constraint df_Amass default(0) for Amass

alter table associatorInfo

      add constraint df_BTime default(getdate()) for BeginTime

alter table associatorInfo

      add constraint fk_associatorInfo_UserID foreign key(UserID) references UserInfo(UserID)

alter table associatorInfo

      add constraint df_State default('正常') for State


---19.操作日志表   JobInfo

alter table JobInfo

      add constraint pk_JobID primary key(JobID)

alter table JobInfo

      add constraint fk_JobInfo_UserID foreign key(UserID) references UserInfo(UserID)

alter table JobInfo

      add constraint df_UseTime default(getdate()) for UseTime

alter table JobInfo

      add constraint df_NoteMoney default(0) for NoteMoney

go

---20.交易信息表	 businessInfo

alter table businessInfo

      add constraint fk_businessInfo_UserID foreign key(UserID) references UserInfo(UserID)

go


alter table Buyer

      add constraint fk_certificateID foreign key(certificateID) references certificate(certificateID)

-------------------插入国籍----------------------------

select * from nationality

insert nationality values(86,'中国','china','CN')
insert nationality values(244,'安哥拉','Angola','AO')
insert nationality values(93,'阿富汗','Afghanistan','AF')
insert nationality values(355,'阿尔巴尼亚','Albania','AL')
insert nationality values(213,'阿尔及利亚','Algeria','DZ')
insert nationality values(376,'安道尔共和国','Andorra','AD')
insert nationality values(1264,'安圭拉岛','Anguilla','AI')
insert nationality values(1268,'安提瓜和巴布达','Antigua and Barbuda','AG')
insert nationality values(54,'阿根廷','Argentina','AR')
insert nationality values(374,'亚美尼亚','Armenia','AM')             
insert nationality values(61,'澳大利亚','Australia','AU')
insert nationality values(43,'奥地利','Austria','AT')
insert nationality values(994,'阿塞拜疆','Azerbaijan','AZ')
insert nationality values(1242,'巴哈马','Bahamas','BS')
insert nationality values(973,'巴林','Bahrain','BH')
insert nationality values(880,'孟加拉国','Bangladesh','BD')
insert nationality values(1246,'巴巴多斯','Barbados','BB')
insert nationality values(375,'白俄罗斯','Belarus','BY')
insert nationality values(32,'比利时','Belgium','BE')
insert nationality values(501,'伯利兹','Belize','BZ')
insert nationality values(229,'贝宁','Benin','BJ')
insert nationality values(1441,'百慕大群岛','Bermuda Is','BM')
insert nationality values(591,'玻利维亚','Bolivia','BO')
insert nationality values(267,'博茨瓦纳','Botswana','BW')
insert nationality values(55,'巴西','Brazil','BR')
insert nationality values(673,'文莱','Brunei','BN')
insert nationality values(359,'保加利亚','Bulgaria','BG')
insert nationality values(226,'布基纳法索','Burkina-faso','BF')
insert nationality values(95,'缅甸','Burma','MM')
insert nationality values(257,'布隆迪','Burundi','BI')
insert nationality values(237,'喀麦隆','Cameroon','CM')
insert nationality values(1,'加拿大','Canada','CA')
insert nationality values(236,'中非共和国','Central African Republic','CF')
insert nationality values(235,'乍得','Chad','TD')
insert nationality values(57,'哥伦比亚','Colombia','CO')
insert nationality values(242,'刚果','Congo','CG')
insert nationality values(682,'库克群岛','Cook Is','CK')
insert nationality values(506,'哥斯达黎加','Costa Rica','CR')
insert nationality values(53,'古巴','Cuba','CU')
insert nationality values(357,'塞浦路斯','Cyprus','CY')
insert nationality values(420,'捷克','Czech Republic ','CZ')
insert nationality values(45,'丹麦','Denmark','DK')
insert nationality values(253,'吉布提','Djibouti','DJ')
insert nationality values(1890,'多米尼加共和国','Dominica Rep','DO')
insert nationality values(593,'厄瓜多尔','Ecuador','EC')
insert nationality values(20,'埃及','Egypt','EG')
insert nationality values(503,'萨尔瓦多','EI Salvador','SV')
insert nationality values(372,'爱沙尼亚','Estonia','EE')
insert nationality values(251,'埃塞俄比亚','Ethiopia','ET')
insert nationality values(679,'斐济','Fiji','FJ')
insert nationality values(358,'芬兰','Finland','FI')
insert nationality values(33,'法国','France','FR')
insert nationality values(594,'法属圭亚那','French Guiana','GF')
insert nationality values(241,'加蓬','Gabon','GA')
insert nationality values(220,'冈比亚','Gambia','GM')
insert nationality values(995,'格鲁吉亚','Georgia ','GE')
insert nationality values(49,'德国','Germany ','DE')
insert nationality values(233,'加纳','Ghana','GH')
insert nationality values(350,'直布罗陀','Gibraltar','GI')
insert nationality values(30,'希腊','Greece','GR')
insert nationality values(1809,'格林纳达','Grenada','GD')
insert nationality values(1671,'关岛','Guam','GU')
insert nationality values(502,'危地马拉','Guatemala','GT')
insert nationality values(224,'几内亚','Guinea','GN')
insert nationality values(592,'圭亚那','Guyana','GY')
insert nationality values(509,'海地','Haiti','HT')
insert nationality values(504,'洪都拉斯','Honduras','HN')
insert nationality values(852,'香港','Hongkong','HK')
insert nationality values(36,'匈牙利','Hungary','HU')
insert nationality values(354,'冰岛','Iceland','IS')
insert nationality values(91,'印度','India','IN')
insert nationality values(62,'印度尼西亚','Indonesia','ID')
insert nationality values(98,'伊朗','Iran','IR')
insert nationality values(964,'伊拉克','Iraq','IQ')
insert nationality values(353,'爱尔兰','Ireland','IE')
insert nationality values(972,'以色列','Israel','IL')
insert nationality values(39,'意大利','Italy','IT')
insert nationality values(1876,'牙买加','Jamaica','JM')
insert nationality values(81,'日本','Japan','JP')
insert nationality values(962,'约旦','Jordan','JO')
insert nationality values(855,'柬埔寨','Kampuchea (Cambodia )','KH')
insert nationality values(327,'哈萨克斯坦','Kazakstan','KZ')
insert nationality values(254,'肯尼亚','Kenya','KE')
insert nationality values(82,'韩国','Korea','KR')
insert nationality values(965,'科威特','Kuwait','KW')
insert nationality values(331,'吉尔吉斯坦','Kyrgyzstan ','KG')
insert nationality values(856,'老挝','Laos','LA')
insert nationality values(371,'拉脱维亚','Latvia ','LV')
insert nationality values(961,'黎巴嫩','Lebanon','LB')
insert nationality values(266,'莱索托','Lesotho','LS')
insert nationality values(231,'利比里亚','Liberia','LR')
insert nationality values(218,'利比亚','Libya','LY')
insert nationality values(423,'列支敦士登','Liechtenstein','LI')
insert nationality values(370,'立陶宛','Lithuania','LT')
insert nationality values(352,'卢森堡','Luxembourg','LU')
insert nationality values(853,'澳门','Macao','MO')
insert nationality values(261,'马达加斯加','Madagascar','MG')
insert nationality values(265,'马拉维','Malawi','MW')
insert nationality values(60,'马来西亚','Malaysia','MY')
insert nationality values(960,'马尔代夫','Maldives','MV')
insert nationality values(223,'马里','Mali','ML')
insert nationality values(356,'马耳他','Malta','MT')
insert nationality values(230,'毛里求斯','Mauritius','MU')
insert nationality values(52,'墨西哥','Mexico','MX')
insert nationality values(373,'摩尔多瓦','Moldova, Republic of ','MD')
insert nationality values(377,'摩纳哥','Monaco','MC')
insert nationality values(976,'蒙古','Mongolia ','MN')