Working with Security Principals

To begin setting up a login at the server level, execute the following. This creates a principal named u1 with a specified password and default database context:

CREATE LOGIN u1 WITH PASSWORD = 'secureKey!2', DEFAULT_DATABASE = retail;

Within the retail database, map this login to a database user:

CREATE USER u1 FOR LOGIN u1;

Granting read access to a specific table is done as follows:

GRANT SELECT ON Customer TO u1;

Log in as u1 to validate the granted privileges.

Implementing Integrity Rules

Create a fresh workspace called jxgl and define the department and teacher entities with embedded constraints:

CREATE DATABASE jxgl;
GO

CREATE TABLE Department (
    DepartNo INT PRIMARY KEY,
    DepartName VARCHAR(8) NOT NULL UNIQUE
);

CREATE TABLE Teacher (
    TeacherNo CHAR(6) PRIMARY KEY,
    TeacherName VARCHAR(20) NOT NULL,
    Age INT CHECK (Age BETWEEN 18 AND 65),
    Gender CHAR(2) CHECK (Gender IN ('男', '女')),
    Title VARCHAR(6),
    Salary MONEY,
    DepartNo INT FOREIGN KEY REFERENCES Department(DepartNo)
);

Post-creation, apply additional domain rules. For example, limiting valid titles and enforcing a salary floor for professors:

ALTER TABLE Teacher ADD CONSTRAINT CHK_Title_Range
    CHECK (Title IN ('教授', '副教授', '讲师', '助教'));

A multi-condition constraint ensures a professor cannot earn less than 6000:

ALTER TABLE Teacher ADD CONSTRAINT CHK_Professor_Salary
    CHECK (Title <> '教授' OR Salary >= 6000);

To handle departmental restructuring, adjust the foreign key behavior so that deleting a department nullifies the reference, but updating its key cascades:

ALTER TABLE Teacher DROP CONSTRAINT FK__Teacher__DepartN__...;

ALTER TABLE Teacher ADD CONSTRAINT FK_Teacher_Department
    FOREIGN KEY (DepartNo) REFERENCES Department(DepartNo)
    ON DELETE SET NULL
    ON UPDATE CASCADE;

Trigger-Driven Change Tracking

In the retail database, maintain a log table for significant credit limit increases:

CREATE TABLE CreditChangeLog (
    CustNo CHAR(8),
    FName CHAR(20),
    LastName CHAR(20),
    OldLimit INT,
    NewLimit INT
);

The following trigger fires when a customer’s credit limit rises by more than 10%:

CREATE TRIGGER trg_CreditSpike
ON Customer
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    IF UPDATE(CreditLimit)
    BEGIN
        INSERT INTO CreditChangeLog (CustNo, FName, LastName, OldLimit, NewLimit)
        SELECT d.CustNo, d.FName, d.LastName, d.CreditLimit, i.CreditLimit
        FROM deleted d
        INNER JOIN inserted i ON d.CustNo = i.CustNo
        WHERE i.CreditLimit > d.CreditLimit * 1.1;
    END
END;

For personnel changes, create an archive table and an associated trigger for departed sales representatives:

CREATE TABLE PreviousRep (
    SalesRepNo CHAR(8),
    FName CHAR(8),
    LastName CHAR(8),
    DepartNo CHAR(8)
);

CREATE TRIGGER trg_RepDeparture
ON SalesRep
AFTER DELETE
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO PreviousRep (SalesRepNo, FName, LastName, DepartNo)
    SELECT SalesRepNo, FName, LastName, DepartNo FROM deleted;
END;

To maintain data consistency during order entry, a triggger adjusts account balances and inventory levels atomically:

CREATE TRIGGER trg_OrderFulfillment
ON OrderLine
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE Cust
    SET Balance = Balance - ins.SubTotal
    FROM Customer AS Cust
    INNER JOIN (
        SELECT CustNo, SUM(PurchasePrice * QtyPurchased) AS SubTotal
        FROM inserted
        GROUP BY CustNo
    ) AS ins ON Cust.CustNo = ins.CustNo;

    UPDATE Prod
    SET QtyOnHand = Prod.QtyOnHand - ins.TotalQty
    FROM Product AS Prod
    INNER JOIN (
        SELECT ProductNo, SUM(QtyPurchased) AS TotalQty
        FROM inserted
        GROUP BY ProductNo
    ) AS ins ON Prod.ProductNo = ins.ProductNo;
END;