使用sql server语言写出一个宿舍信息管理数据库，要求有5张表和至少两个视图，给出相关类型，有主、外键，有触发器，有权限分配，和针对某功能模块的复杂查询设计，写出相关代码

好的，以下是一个简单的宿舍信息管理数据库的设计，包括5张表和2个视图：

1. 学生信息表（Student）

| 列名 | 类型 | 主键 | 外键 |
| :----: | :----: | :---: | :----: |
| stu_id | int | √ | |
| name | char | | |
| gender | char | | |
| age | int | | |
| room | int | | Room.r_id |

2. 宿舍楼信息表（Building）

| 列名 | 类型 | 主键 | 外键 |
| :-----: | :----: | :---: | :----: |
| bldg_id | int | √ | |
| name | char | | |
| floors | int | | |

3. 宿舍房间信息表（Room）

| 列名 | 类型 | 主键 | 外键 |
| :----: | :-----: | :---: | :----: |
| r_id | int | √ | |
| number | int | | |
| floor | int | | |
| beds | int | | |
| bldg | int | | Building.bldg_id |

4. 维修记录表（Maintenance）

| 列名 | 类型 | 主键 | 外键 |
| :------ | :----- | :--: | :--- |
| m_id | int | √ | |
| r_id | int | | Room.r_id |
| date | date | | |
| reason | char | | |
| comment | char | | |

5. 入住记录表（CheckIn）

| 列名 | 类型 | 主键 | 外键 |
| :----: | :---- | :--: | :--- |
| ci_id | int | √ | |
| stu_id | int | | Student.stu_id |
| date | date | | |
| room | int | | Room.r_id |

以下是两个视图的创建语句：

1. 查看每个宿舍楼的入住情况视图

CREATE VIEW View_CheckIn AS
SELECT Building.name AS 'Building', COUNT(CheckIn.ci_id) AS 'Occupancy'
FROM CheckIn
INNER JOIN Room ON CheckIn.room = Room.r_id
INNER JOIN Building ON Room.bldg = Building.bldg_id
GROUP BY Building.name;

2. 查看每个学生的宿舍信息视图

CREATE VIEW View_Student AS
SELECT Student.stu_id, Student.name, Room.number AS 'Room Number', Building.name AS 'Building'
FROM Student
INNER JOIN Room ON Student.room = Room.r_id
INNER JOIN Building ON Room.bldg = Building.bldg_id;

以下是触发器的创建语句：

1. 当新增一个学生时，更新宿舍房间的空床位数

CREATE TRIGGER tr_UpdateRoomVacancy
ON Student
AFTER INSERT
AS
BEGIN
    UPDATE Room
    SET beds = beds - 1
    FROM inserted
    WHERE Room.r_id = inserted.room;
END;

2. 当删除一个学生时，更新宿舍房间的空床位数

CREATE TRIGGER tr_UpdateRoomVacancy
ON Student
AFTER DELETE
AS
BEGIN
    UPDATE Room
    SET beds = beds + 1
    FROM deleted
    WHERE Room.r_id = deleted.room;
END;

以下是权限分配的语句：

1. 创建一个数据库角色

CREATE ROLE dormitory_manager;

2. 授予该角色对学生信息表和入住记录表的 SELECT、INSERT、UPDATE 和 DELETE 权限

GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.Student TO dormitory_manager;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.CheckIn TO dormitory_manager;

3. 将一个用户添加到该角色

EXEC sp_addrolemember 'dormitory_manager', 'username';

以下是针对某功能模块的复杂查询设计，查询每个宿舍楼的维修记录数量和总花费：

SELECT Building.name AS 'Building',
       COUNT(Maintenance.m_id) AS 'Maintenance Count',
       SUM(CASE WHEN Maintenance.reason = 'Plumbing' THEN 50
                WHEN Maintenance.reason = 'Electricity' THEN 80
                WHEN Maintenance.reason = 'Carpentry' THEN 100
                ELSE 0
           END) AS 'Total Cost'
FROM Maintenance
INNER JOIN Room ON Maintenance.r_id = Room.r_id
INNER JOIN Building ON Room.bldg = Building.bldg_id
GROUP BY Building.name;