use master
go

if exists (select * from dbo.sysdatabases where name = 'Room') 
drop database Room
GO

create database Room
go
use Room
go

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

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

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

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

CREATE TABLE [dbo].[user_Info] (
	[user_ID] [varchar] (15) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[user_PWD] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[user_Des] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[bookin] (
	[bookno] [varchar] (14) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[customname] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[customID] [varchar] (18) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[roomno] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[indate] [datetime] NULL ,
	[discount] [float] NULL ,
	[inmemo] [text] COLLATE Chinese_PRC_CI_AS NULL ,
	[checkdate] [datetime] NULL ,
	[ammount] [float] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[rooms] (
	[roomNO] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[roomtype] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[roomposition] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[roomprice] [float] NULL ,
	[putup] [bit], 
	[roommemo] [text] COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[roomtype] (
	[typeid] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[typename] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[area] [float] NULL ,
	[bednum] [int] NULL ,
	[haircondition] [bit], 
	[htelephone] [bit],
	[htelevision] [bit],
	[htoilet] [bit],
	[price] [float] NULL 
) ON [PRIMARY]
GO

INSERT user_Info (user_ID, user_PWD) VALUES ('張德金', NULL)
GO