数据库的创建

-删除数据库系统中原的的数据库
use master
if exists(select * from sysdatabases where name='CommService')
drop database CommService
go
--创建新的数据库
create database CommService
go
use CommService
--删除表
if exists(select * from sysobjects where name='People')
    drop table People
if exists(select * from sysobjects where name='Customers')
    drop table Customers
if exists(select * from sysobjects where name='Client')
    drop table Client
if exists(select * from sysobjects where name='CommService_Log')
    drop table CommService_Log
if exists(select * from sysobjects where name='ClientApplication')
    drop table ClientApplication
if exists(select * from sysobjects where name='Message')
    drop table Message
if exists(select * from sysobjects where name='Application')
    drop table Application
go   
--创建表
create table People
(
    Id int identity(1,1) primary key,
    FirstName char(8) not null,
    LastName char(10) not null,
    Email char(20) not null,
    Tele char(11) not null,
    [Address] char(20) not null,
    Fax char(20) not null   
)
create table Customers
(
    Id int identity(1,1) primary key,
    --外键
    PersonInChargeId int constraint fk_PersonId references People(Id),
    Status int not null,
    RegisteredTime datetime not null,
    LastModifiedTime datetime not null     
)
create table CommService_Log
(
    Id int identity(1,1) primary key,
    Type int not null,
    SubType int not null,
    Message char(20) null,
    LogTime datetime not null
)
create table Client
(
    Id int identity(1,1) primary key,
    DeviceType int not null,
    MacAddress char(18) not null,
    DeviceName char(20) null,
    ConnedStatus int not null,
    LastHeartbeatTime datetime not null,
    [Status] int not null,
    LastModifiedTime datetime not null
)
create table Application
(
    Id int identity(1,1) primary key,
    Name char(10) not null,
    [Description] char(100) null
)
create table ClientApplication
(
    Id int identity(1,1) primary key,
    AppId int constraint fk_ApplicationId references Application(Id),
    ClientId int constraint fk_ClientId references Client(Id),
    CustomerId int constraint fk_CustomerId references Customers(Id),
    UserName char(10) not null,
    Password char(6) not null,
)
create table Message
(
    Id int identity(1,1) primary key,
    ClientAppId int constraint fk_ClientApplicationId references ClientApplication(Id),
    Content char(20) null,
    Direction int not null,
    HandlingStatus int not null,
    ArrivedTime datetime not null,
    TakeAwayTime datetime not null,
    MStatus int not null,
    LastModifiedTime dateTime not null
)
go