ایجاد دیتابیس SQL Server کتابخانه با پروسیجر و ویو و فانکشن و تریگر

[ali]

Library Management System SQL Server Database with Tables, View, Stored Procedures, Trigger and function and some sample data

فرض کنید یک پایگاه داده برای مدیریت کتابخانه ایجاد می کنیم. در این پایگاه داده چهار جدول زیر وجود دارد:

Books (کتاب‌ها)
BookID (شناسه کتاب): شناسه یکتای هر کتاب
Title (عنوان): عنوان کتاب
Author (نویسنده): نویسنده کتاب
PublicationYear (سال انتشار): سال انتشار کتاب
Genre (ژانر): ژانر یا دسته‌بندی کتاب

Authors (نویسندگان)
AuthorID (شناسه نویسنده): شناسه یکتای هر نویسنده
FirstName (نام): نام نویسنده
LastName (نام خانوادگی): نام خانوادگی نویسنده
BirthYear (سال تولد): سال تولد نویسنده

Customers (مشتریان)
CustomerID (شناسه مشتری): شناسه یکتای هر مشتری
FirstName (نام): نام مشتری
LastName (نام خانوادگی): نام خانوادگی مشتری
Email (ایمیل): آدرس ایمیل مشتری
PhoneNumber (شماره تلفن): شماره تلفن مشتری

BorrowedBooks (کتاب‌های امانی)
BorrowID (شناسه امانت): شناسه یکتای هر امانت کتاب به مشتری
CustomerID (شناسه مشتری): شناسه مشتری که کتاب را امانت گرفته است
BookID (شناسه کتاب): شناسه کتابی که به مشتری امانت داده شده است
BorrowDate (تاریخ امانت): تاریخی که کتاب به مشتری امانت داده شده است
ReturnDate (تاریخ بازگشت): تاریخی که کتاب از مشتری بازگشت داده شده است
حالا می توانیم کد SQL Server برای ایجاد این جداول را بنویسیم. در اینجا از دستورات T-SQL استفاده می کنیم:

کد:

-- Create Authors table
CREATE TABLE Authors (
    AuthorID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    BirthYear INT
);

-- Create Books table
CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title VARCHAR(100),
    AuthorID INT,
    PublicationYear INT,
    Genre VARCHAR(50),
    FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID)
);

-- Create Customers table
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100),
    PhoneNumber VARCHAR(20)
);

-- Create BorrowedBooks table
CREATE TABLE BorrowedBooks (
    BorrowID INT PRIMARY KEY,
    CustomerID INT,
    BookID INT,
    BorrowDate DATE,
    ReturnDate DATE,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID),
    FOREIGN KEY (BookID) REFERENCES Books(BookID)
);

این کد SQL یک پایگاه داده با چهار جدول ایجاد می کند و ارتباطات بین آن‌ها را تعیین می‌کند. برای ایجاد این جداول در SQL Server، شما می توانید از نرم‌افزار SQL Server Management Studio یا هر ابزار مشابه دیگری استفاده کنید. می‌توانید رکوردهای نمونه برای جداول مختلف را ایجاد کنید. در اینجا چند رکورد نمونه برای هر جدول ارائه داده شده است:

کد:

-- افزودن رکوردهای نمونه به Authors table
INSERT INTO Authors (AuthorID, FirstName, LastName, BirthYear)
VALUES
    (1, 'John', 'Doe', 1980),
    (2, 'Jane', 'Smith', 1975),
    (3, 'Michael', 'Johnson', 1990);

-- افزودن رکوردهای نمونه به Books table
INSERT INTO Books (BookID, Title, AuthorID, PublicationYear, Genre)
VALUES
    (101, 'Sample Book 1', 1, 2010, 'Fiction'),
    (102, 'Sample Book 2', 2, 2015, 'Mystery'),
    (103, 'Sample Book 3', 3, 2020, 'Science Fiction');

-- افزودن رکوردهای نمونه به Customers table
INSERT INTO Customers (CustomerID, FirstName, LastName, Email, PhoneNumber)
VALUES
    (1001, 'Alice', 'Johnson', 'alice@example.com', '123-456-7890'),
    (1002, 'Bob', 'Smith', 'bob@example.com', '987-654-3210'),
    (1003, 'Carol', 'Davis', 'carol@example.com', '111-222-3333');

-- افزودن رکوردهای نمونه به BorrowedBooks table
INSERT INTO BorrowedBooks (BorrowID, CustomerID, BookID, BorrowDate, ReturnDate)
VALUES
    (10001, 1001, 101, '2023-09-01', '2023-09-15'),
    (10002, 1002, 102, '2023-09-05', '2023-09-20'),
    (10003, 1003, 103, '2023-09-10', '2023-09-25');

این کد SQL رکوردهای نمونه را به هر یک از جداول اضافه می‌کند. می‌توانید این کد را به کمک SQL Server Management Studio یا ابزار مدیریت دیگر اجرا کنید تا رکوردهای نمونه ایجاد شوند.
بر اساس جدول های بالا چند کوئری نمونه برای ساخت جداول انجام دادیم. حالا چند کوئری برای انجام عملیات مختلف روی این جداول به شما ارائه می‌دهم:

بازیابی همه کتاب‌ها به همراه نویسنده‌هایشان:

کد:

SELECT Books.Title, Authors.FirstName, Authors.LastName
FROM Books
INNER JOIN Authors ON Books.AuthorID = Authors.AuthorID;

بازیابی تمام مشتریان و کتاب‌هایی که به اختیار آن‌ها قرار گرفته است:

کد:

SELECT Customers.FirstName, Customers.LastName, Books.Title
FROM Customers
INNER JOIN BorrowedBooks ON Customers.CustomerID = BorrowedBooks.CustomerID
INNER JOIN Books ON BorrowedBooks.BookID = Books.BookID;

بازیابی تعداد کتاب‌هایی که هر نویسنده نوشته است:

کد:

SELECT Authors.FirstName, Authors.LastName, COUNT(Books.BookID) AS TotalBooks
FROM Authors
LEFT JOIN Books ON Authors.AuthorID = Books.AuthorID
GROUP BY Authors.FirstName, Authors.LastName;

بازیابی تاریخ بازگشت کتاب‌ها بر اساس نام مشتری:

کد:

SELECT Customers.FirstName, Customers.LastName, Books.Title, BorrowedBooks.ReturnDate
FROM Customers
INNER JOIN BorrowedBooks ON Customers.CustomerID = BorrowedBooks.CustomerID
INNER JOIN Books ON BorrowedBooks.BookID = Books.BookID
ORDER BY Customers.FirstName, Customers.LastName;

این کوئری‌ها فقط چند نمونه از کوئری‌های ممکن برای این جداول هستند. شما می‌توانید کوئری‌های مختلفی بر اساس نیاز خود ایجاد کنید تا اطلاعات مورد نظر را بازیابی کنید.

البته در مورد محاسباتی، برای محاسبه مقادیر جدید از داده‌های موجود استفاده می‌شود. در این مثال‌ها، محاسبات را بر روی داده‌های جداول تعریف کرده و نتایج را به نمایش می‌گذاریم:

محاسبه میانگین سال تولید کتب:

کد:

SELECT AVG(PublicationYear) AS AveragePublicationYear
FROM Books;

محاسبه تعداد کتاب‌هایی که هر مشتری امانت گرفته است:

کد:

SELECT Customers.FirstName, Customers.LastName, COUNT(BorrowedBooks.BorrowID) AS TotalBorrowedBooks
FROM Customers
LEFT JOIN BorrowedBooks ON Customers.CustomerID = BorrowedBooks.CustomerID
GROUP BY Customers.FirstName, Customers.LastName;

محاسبه تعداد کتاب‌هایی که هر نویسنده نوشته و میانگین تاریخ انتشار آن‌ها:

کد:

SELECT Authors.FirstName, Authors.LastName, COUNT(Books.BookID) AS TotalBooks,
       AVG(PublicationYear) AS AveragePublicationYear
FROM Authors
LEFT JOIN Books ON Authors.AuthorID = Books.AuthorID
GROUP BY Authors.FirstName, Authors.LastName;

محاسبه تعداد کتاب‌های هر ژانر:

کد:

SELECT Genre, COUNT(BookID) AS TotalBooks
FROM Books
GROUP BY Genre;

این مثال‌ها نمایش می‌دهند که چگونه می‌توانید با استفاده از SQL کوئری‌ها محاسبات مختلفی را روی داده‌های جداول انجام دهید. بر اساس نیاز‌های خود می‌توانید محاسبات دلخواه دیگری را نیز ایجاد کنید.

حالا چند نمونه از ویوها (Views)، توابع (Functions)، و پروسیجرها (Stored Procedures) را برای جداول فوق ارائه می‌دهم:

View:
ویویی برای نمایش اطلاعات کتاب‌ها به همراه نویسنده آن‌ها:

کد:

CREATE VIEW BookDetails AS
SELECT Books.Title, Authors.FirstName, Authors.LastName, Books.PublicationYear, Books.Genre
FROM Books
INNER JOIN Authors ON Books.AuthorID = Authors.AuthorID;

Function:
فانکشنی برای محاسبه سن متوسط نویسندگان:

کد:

CREATE FUNCTION CalculateAverageAuthorAge (@BirthYear INT)
RETURNS INT
AS
BEGIN
    DECLARE @CurrentYear INT = YEAR(GETDATE());
    RETURN @CurrentYear - @BirthYear;
END;

Stored Procedure:
پروسیجری برای اضافه کردن یک کتاب جدید به جدول کتب:

کد:

CREATE PROCEDURE AddNewBook
    @Title VARCHAR(100),
    @AuthorID INT,
    @PublicationYear INT,
    @Genre VARCHAR(50)
AS
BEGIN
    INSERT INTO Books (Title, AuthorID, PublicationYear, Genre)
    VALUES (@Title, @AuthorID, @PublicationYear, @Genre);
END;

این مثال‌ها نشان می‌دهند که چگونه می‌توانید ویوها، توابع، و پروسیجرهای مختلف را برای انجام کارهای مختلف بر روی داده‌های جداول تعریف کنید. بر اساس نیاز‌های خود می‌توانید این موارد را گسترش دهید و ویوها را برای نمایش اطلاعات خاص، توابع را برای محاسبات مختلف، و پروسیجرها را برای انجام عملیات‌های پیچیده‌تر استفاده کنید.

اینجا یک نمونه کرسر (Cursor) و یک تریگر (Trigger) برای جدول‌های Authors و Books ارائه می‌شود:

Cursor:
این کرسر برای نمایش نویسندگانی که سن آن‌ها بیشتر از 50 سال است، ایجاد می‌شود:

کد:

DECLARE @AuthorID INT;
DECLARE @FirstName VARCHAR(50);
DECLARE @LastName VARCHAR(50);
DECLARE @BirthYear INT;

DECLARE AuthorCursor CURSOR FOR
SELECT AuthorID, FirstName, LastName, BirthYear
FROM Authors
WHERE DATEDIFF(YEAR, BirthYear, GETDATE()) > 50;

OPEN AuthorCursor;

FETCH NEXT FROM AuthorCursor INTO @AuthorID, @FirstName, @LastName, @BirthYear;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Author ID: ' + CAST(@AuthorID AS VARCHAR) + ', Name: ' + @FirstName + ' ' + @LastName + ', Birth Year: ' + CAST(@BirthYear AS VARCHAR);
    FETCH NEXT FROM AuthorCursor INTO @AuthorID, @FirstName, @LastName, @BirthYear;
END;

CLOSE AuthorCursor;
DEALLOCATE AuthorCursor;

Trigger:
این تریگر برای افزودن کتاب جدید به جدول Books با توجه به سال انتشار آن و ژانر، نمونه‌ای از یک تریگر است:

کد:

CREATE TRIGGER CheckBookGenre
ON Books
AFTER INSERT
AS
BEGIN
    DECLARE @Genre VARCHAR(50);
    DECLARE @PublicationYear INT;
    
    SELECT @Genre = inserted.Genre, @PublicationYear = inserted.PublicationYear
    FROM inserted;

    IF @Genre = 'Mystery' AND @PublicationYear < 2000
    BEGIN
        RAISEERROR('Books in the Mystery genre published before 2000 are not allowed.', 16, 1);
        ROLLBACK;
    END;
END;

تریگر CheckBookGenre هر زمان که یک کتاب به جدول Books اضافه می‌شود، بررسی می‌کند که آیا ژانر کتاب Mystery است و سال انتشار آن کمتر از 2000 است یا نه. اگر شرایط صدق کنند، یک خطای اجرایی (Error) ایجاد می‌کند و اضافه کردن کتاب را بازنگری می‌کند.

اینجا یک نمونه پروسیجر برای انجام عملیات آپدیت روی جدول Authors در نظر گرفته شده است:

کد:

CREATE PROCEDURE UpdateAuthor
    @AuthorID INT,
    @NewFirstName VARCHAR(50),
    @NewLastName VARCHAR(50),
    @NewBirthYear INT
AS
BEGIN
    UPDATE Authors
    SET FirstName = @NewFirstName,
        LastName = @NewLastName,
        BirthYear = @NewBirthYear
    WHERE AuthorID = @AuthorID;
    
    IF @@ROWCOUNT = 0
    BEGIN
        RAISEERROR('Author with ID ' + CAST(@AuthorID AS VARCHAR) + ' not found.', 16, 1);
        RETURN;
    END;
    
    PRINT 'Author with ID ' + CAST(@AuthorID AS VARCHAR) + ' updated successfully.';
END;

این پروسیجر به عنوان ورودی شناسه نویسنده (AuthorID) و مشخصات جدید نویسنده شامل نام (@NewFirstName)، نام خانوادگی (@NewLastName) و سال تولد (@NewBirthYear) را می‌پذیرد. سپس اطلاعات نویسنده با شناسه داده شده بروزرسانی می‌شود. اگر هیچ ردیفی آپدیت نشود (یعنی نویسنده با شناسه داده شده وجود نداشته باشد)، یک خطای اجرایی ایجاد می‌شود. در غیر این صورت پیامی با موفقیت آپدیت شدن نویسنده نمایش داده می‌شود.
برای استفاده از این پروسیجر، می‌توانید کوئری زیر را اجرا کنید:

کد:

EXEC UpdateAuthor @AuthorID = 1, @NewFirstName = 'John', @NewLastName = 'Doe', @NewBirthYear = 1980;

در این مثال، نویسنده با شناسه 1 به نام "John Doe" و با سال تولد 1980 آپدیت می‌شود.