1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
CREATE TABLE building
(
    BUILDING_ID INT PRIMARY KEY  IDENTITY,
    BUONDING_CAR VARCHAR(1)
)
GO
CREATE TABLE room
(
    ROOM_ID INT PRIMARY KEY  IDENTITY,
    ROOM_MUMBER INT,
    FK_BUILDING_ID INT,
    CONSTRAINT FK_buolding_room FOREIGN KEY (FK_BUILDING_ID)     
    REFERENCES building(BUILDING_ID)     
    ON DELETE CASCADE    
    ON UPDATE CASCADE    
)
GO
CREATE TABLE equipmen
(
    EQUIPMENT_ID INT PRIMARY KEY IDENTITY,
    EQUIPMENT_NAME VARCHAR(500),
    EQUIPMENT_NUMBER INT CHECK (EQUIPMENT_NUMBER>0),
    FK_ROOM_ID INT NULL,
    
    CONSTRAINT FK_equipmen_room FOREIGN KEY (FK_ROOM_ID)     
    REFERENCES room(ROOM_ID)     
    ON DELETE SET NULL   
    ON UPDATE CASCADE 
)
GO
 
/* 
-- Creates the login root with password '123'. 
CREATE LOGIN root   
    WITH PASSWORD = '123';  
GO  
 
-- Creates a database user for the login created above.  
CREATE USER root FOR LOGIN root;  
GO 
 
GRANT ALL ON Administrator TO root;
GO 
 
*/
 
GRANT ALL ON root TO building, room, equipmen;
GO
 
/*
--Отключение
ALTER LOGIN root DISABLE;
--Включение
ALTER LOGIN root ENABLE;
GO
*/