用SQL Server T-SQL写一份宿舍管理数据库课程设计

好的，以下是一份宿舍管理数据库的设计，使用 SQL Server T-SQL 语言实现：

**1. 数据库表设计**

1.1 宿舍表 dormitory

CREATE TABLE dormitory (
    id INT PRIMARY KEY,
    building VARCHAR(20),
    number VARCHAR(10),
    capacity INT,
    current_count INT,
    gender VARCHAR(10)
);

1.2 学生表 student

CREATE TABLE student (
    id INT PRIMARY KEY,
    name VARCHAR(20),
    gender VARCHAR(10),
    major VARCHAR(20),
    grade VARCHAR(10),
    phone VARCHAR(20)
);

1.3 入住表 check_in

CREATE TABLE check_in (
    id INT PRIMARY KEY,
    dormitory_id INT FOREIGN KEY REFERENCES dormitory(id),
    student_id INT FOREIGN KEY REFERENCES student(id),
    check_in_date DATE,
    check_out_date DATE
);

**2. 数据库视图设计**

2.1 宿舍信息查询视图 dormitory_info

CREATE VIEW dormitory_info AS
SELECT d.id, d.building, d.number, d.capacity, d.current_count, d.gender, s.name, s.gender, s.major, s.grade, s.phone
FROM dormitory d
LEFT JOIN check_in ci ON d.id = ci.dormitory_id
LEFT JOIN student s ON ci.student_id = s.id;

2.2 学生信息查询视图 student_info

CREATE VIEW student_info AS
SELECT s.id, s.name, s.gender, s.major, s.grade, s.phone, d.building, d.number, d.gender
FROM student s
LEFT JOIN check_in ci ON s.id = ci.student_id
LEFT JOIN dormitory d ON ci.dormitory_id = d.id;

**3. 数据库存储过程设计**

3.1 入住存储过程 check_in_procedure

CREATE PROCEDURE check_in_procedure
    @dormitory_id INT,
    @student_id INT,
    @check_in_date DATE
AS
BEGIN
    -- 检查宿舍是否已满员
    IF EXISTS (
        SELECT *
        FROM dormitory
        WHERE id = @dormitory_id
            AND current_count >= capacity
    )
    BEGIN
        RAISERROR('宿舍已满员，无法入住！', 16, 1);
        RETURN;
    END

    -- 检查学生是否已入住其他宿舍
    IF EXISTS (
        SELECT *
        FROM check_in
        WHERE student_id = @student_id
            AND check_out_date IS NULL
    )
    BEGIN
        RAISERROR('学生已入住其他宿舍，无法再次入住！', 16, 1);
        RETURN;
    END

    -- 新增一条入住记录，并更新宿舍表中的当前入住人数
    INSERT INTO check_in (dormitory_id, student_id, check_in_date)
    VALUES (@dormitory_id, @student_id, @check_in_date);

    UPDATE dormitory
    SET current_count = current_count + 1
    WHERE id = @dormitory_id;
END

3.2 退宿存储过程 check_out_procedure

CREATE PROCEDURE check_out_procedure
    @check_in_id INT,
    @check_out_date DATE
AS
BEGIN
    -- 检查学生是否已入住该宿舍
    IF NOT EXISTS (
        SELECT *
        FROM check_in
        WHERE id = @check_in_id
    )
    BEGIN
        RAISERROR('该入住记录不存在！', 16, 1);
        RETURN;
    END

    -- 更新入住表中的退宿日期，并更新宿舍表中的当前入住人数
    UPDATE check_in
    SET check_out_date = @check_out_date
    WHERE id = @check_in_id;

    UPDATE dormitory
    SET current_count = current_count - 1
    WHERE id = (
        SELECT dormitory_id
        FROM check_in
        WHERE id = @check_in_id
    );
END

以上是一份简单的宿舍管理数据库设计，使用 SQL Server T-SQL 实现。具体实现方式需要根据实际情况进行调整。