dzaMyB9h

1USE MASTER;
2
3IF EXISTS (SELECT [name] FROM sys.databases WHERE [name] = 'KunjaBoekingDB' )   
4BEGIN   
5ALTER DATABASE KunjaBoekingDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE   
6DROP DATABASE KunjaBoekingDB ;   
7END     
8
9
10CREATE database	KunjaBoekingDB;
11GO
12
13use KunjaBoekingDB
14
15
16-- Create ERD tabels Kunja
17CREATE TABLE gast 
18(
19gastID	INT NOT NULL,
20voornaam	nvarchar(25), 
21achternaam	NVARCHAR(255), 
22adres nvarchar(100) NOT NULL,
23woonplaats nvarchar(100),
24postcode nvarchar(10),
25telNr nvarchar(25),
26email nvarchar(50)
27)
28ALTER TABLE gast ADD constraint gastID PRIMARY KEY CLUSTERED (gastID)
29
30CREATE TABLE boeking(
31boekingID INT NOT NULL,
32gastID INT NOT NULL,
33)
34ALTER TABLE boeking ADD constraint boekingID PRIMARY KEY CLUSTERED (boekingID)
35
36CREATE TABLE reservering(
37reserveringID INT NOT NULL,
38lodgeID INT NOT NULL,
39boekingID INT NOT NULL,
40incheckDatum DateTime NOT NULL,
41uitcheckDatum DateTime NOT NULL
42)
43ALTER TABLE reservering ADD constraint reserveringID PRIMARY KEY CLUSTERED (reserveringID)
44
45CREATE TABLE lodge(
46lodgeID INT NOT NULL,
47lodgeTypeID INT NOT NULL,
48lodgeNaam nvarchar(50),
49lodgeNR nvarchar(50),
50bezettingsgraad nvarchar(20)
51)
52ALTER TABLE lodge ADD constraint lodgeID PRIMARY KEY CLUSTERED (lodgeID)
53
54CREATE TABLE lodgetype(
55lodgeTypeID INT NOT NULL,
56lodgeTypeNaam nvarchar(100),
57lodgeTypeOmschrijving nvarchar(250),
58aantalpersonen INT NOT NULL,
59prijs Money NOT NULL,
60)
61ALTER TABLE lodgetype ADD constraint lodgeTypeID PRIMARY KEY CLUSTERED (lodgeTypeID)
62
63--Boeking FK
64ALTER TABLE boeking ADD CONSTRAINT boeking_gast_FK FOREIGN KEY ( gastID ) REFERENCES gast ( gastID )
65ON DELETE NO ACTION 
66ON UPDATE 
67NO ACTION
68--Reservering FK
69ALTER TABLE reservering ADD CONSTRAINT reservering_lodge_FK FOREIGN KEY ( lodgeID ) REFERENCES lodge ( lodgeID )
70ON DELETE NO ACTION 
71ON UPDATE 
72NO ACTION
73ALTER TABLE reservering ADD CONSTRAINT reservering_boeking_FK FOREIGN KEY ( boekingID ) REFERENCES boeking ( boekingID )
74ON DELETE NO ACTION 
75ON UPDATE 
76NO ACTION
77--Lodge FK
78ALTER TABLE lodge ADD CONSTRAINT lodge_lodgetype_FK FOREIGN KEY ( lodgeTypeID ) REFERENCES lodgetype ( lodgeTypeID )
79ON DELETE NO ACTION 
80ON UPDATE 
81NO ACTION