msin616 final project
1.0.0
Это простая база данных, которую можно использовать для хранения и обработки данных, предоставляемых библиотекой . База данных была разработана путем модификации базы данных Microsoft Pubs . База данных Pubs предоставляет набор вымышленных сведений об издателях , авторах , названиях и продажах книг. Сейчас он считается устаревшим ; он больше не поставляется с SQL Server начиная с версии 2008 года . Таблицы и поля совершенно очевидны. У них есть такие имена, как Authors , Titles » и т. д., которые отражают их содержание. Поля также имеют очевидные имена, объясняющие, что они содержат.
Подробную информацию об исходной базе данных Pubs можно найти по ссылке: Microsoft Pubs DB.
Ниже вы можете увидеть измененную схему базы данных Pubs
Ссылка на схему базы данных: Схема БД.
Microsoft SQL Server 2019 – 15.0.4198.2 Developer Edition (64-разрядная версия) в Linux (Ubuntu 20.04.3 LTS)Azure Data Studio версии 1.35.1.Docker версии 4.4.2 (73305) Gentellela , панель администратора с открытым исходным кодом, использовалась для создания пользовательского интерфейса. [TITLES] была изменена и добавлен столбец prequel_id для хранения приквела книги. [TITLES] . [TITLES] была изменена и добавлен столбец ISBN . [TITLES] добавлены некоторые известные серии книг, такие как «Игра престолов» или «Властелин колец» . [Audit].[Book] для хранения истории аудита созданных книг. [Adventureworks].[Person].[Person] и [AdventureWorks].[HumanResources].[Employee] для генерации фиктивных данных. Jobs удалена Roysched удалена. Employees изменена. Employee_type создана. Category создана. TitleCategory создана. Degrees создана. Schools создана. Branchs создана. Shift_logs создана. Paychecks создана. Borrowers создана. Bookcopies создана. Bookcopy_history создана. Books_borrowed создана. 
Ссылка на интерфейсный модуль пользовательского интерфейса: Посмотреть демонстрацию
Алгоритм операции создания книги
Хранимая процедура добавления книги в таблицу TITLES
/*
Created by Nury Amanmadov
Date created: 10.04.2022
*/
CREATE PROCEDURE [dbo].[USP_InsertBook]
@book_title AS VARCHAR ( 100 )
,@prequel_id AS VARCHAR ( 6 ) = NULL
,@book_price AS MONEY
,@book_advance AS MONEY
,@book_royalty INT
,@book_ytd_sales INT
,@book_notes VARCHAR ( 800 )
,@book_pubdate DATETIME
,@book_isbn VARCHAR ( 17 )
,@pub_id AS CHAR ( 4 ) = NULL
,@pub_name AS VARCHAR ( 40 ) = NULL
,@pub_city AS VARCHAR ( 20 ) = NULL
,@pub_state AS CHAR ( 2 ) = NULL
,@pub_country AS VARCHAR ( 30 ) = NULL
,@author_id VARCHAR ( 11 ) = NULL
,@au_lname VARCHAR ( 40 ) = NULL
,@au_fname VARCHAR ( 20 ) = NULL
,@au_phone CHAR ( 12 ) = NULL
,@au_city VARCHAR ( 20 ) = NULL
,@au_state CHAR ( 2 ) = NULL
,@au_order TINYINT = 1
,@royalty_per INT = 100
AS
BEGIN
SET NOCOUNT ON ;
BEGIN TRY
BEGIN TRANSACTION
-- #region Insert into Publisher Table
IF (@pub_id IS NULL )
-- #region Create New Publisher
BEGIN
-- Create pub_id as Max Id + 1
SET @pub_id = ( SELECT CAST( MAX (CAST(pub_id AS INT ) + 1 ) AS VARCHAR ) FROM publishers AS VARCHAR )
INSERT INTO [pubs].[dbo].[publishers]
VALUES (
ISNULL(@pub_id, 1 )
,@pub_name
,@pub_city
,@pub_state
,@pub_country
)
-- #region Insert into Pub_Info Table
DECLARE @pr_info AS VARCHAR ( 255 )
SET @pr_info = (
SELECT ' This is sample text data for '
+ pub_name + ' , publisher '
+ pub_id + ' in the pubs database. '
+ pub_name + ' is located in ' + city + ' ' + country + ' . ' AS pr_info
FROM [pubs].[dbo].[publishers] p
WHERE p . pub_id = @pub_id
)
INSERT INTO [pubs].[dbo].[pub_info]
VALUES (
ISNULL(@pub_id, 1 )
, NULL
,@pr_info
)
-- #endregion
END
-- #endregion
ELSE
BEGIN
-- Just to make sure that selected publisher exists in the database
IF NOT EXISTS ( SELECT TOP 1 1 FROM publishers p WHERE pub_id = @pub_id)
BEGIN
;THROW 50001 , ' Publisher with selected ID does not exist ' , 1
END
ELSE
BEGIN
-- Setting params. Will be inserted into Audit.Book table
SELECT @pub_name = p . pub_name
,@pub_city = p . city
,@pub_state = p.[state]
,@pub_country = p . country
FROM publishers p
WHERE p . pub_id = @pub_id
END
END
-- #endregion
-- #region Insert into Titles Table
DECLARE @random_title_id [dbo].[tid] = [dbo].[fn_GenerateRandomTitleId](RAND())
INSERT INTO titles
VALUES
(
@random_title_id
,@book_title
,ISNULL(@pub_id, 1 )
,@book_price
,@book_advance
,@book_royalty
,@book_ytd_sales
,@book_notes
,@book_pubdate
,@prequel_id
,@book_isbn
)
-- #endregion
-- #region Insert into Authors Table
DECLARE @au_zip CHAR ( 5 )
DECLARE @au_contract BIT
DECLARE @au_address VARCHAR ( 40 )
IF(@author_id IS NULL )
BEGIN
-- Generate Random AuthorID
SET @author_id = dbo . fn_GenerateRandomAuthorId (RAND())
-- Setting Random Zip in the 99xyz format
SET @au_zip = ' 99 ' + ( SELECT (CAST((FLOOR(RAND() * ( 999 - 100 + 1 ) + 100 )) AS CHAR )))
-- Setting @au_contract as random
SET @au_contract = ( SELECT (CAST((FLOOR(RAND() * ( 1 - 0 + 1 ) + 0 )) AS BIT )))
-- Setting Random Address from AdventureWorks DB Person.Address table
SET @au_address = ( SELECT TOP 1 LEFT(AddressLine1, 40 ) FROM AdventureWorks . Person .Address WHERE AddressLine1 IS NOT NULL ORDER BY NEWID())
INSERT INTO authors
VALUES
(
@author_id
,@au_lname
,@au_fname
,@au_phone
,@au_address
,@au_city
,@au_state
,@au_zip
,@au_contract
)
END
ELSE
BEGIN
IF NOT EXISTS ( SELECT TOP 1 1 FROM authors a WHERE a . au_id = @author_id)
BEGIN
;THROW 50002 , ' Author with selected ID does not exist ' , 1
END
ELSE
BEGIN
SELECT @au_lname = a . au_lname
,@au_fname = a . au_fname
,@au_phone = a . phone
,@au_city = a . city
,@au_state = a.[state]
FROM authors a
WHERE a . au_id = @author_id
END
END
-- #endregion
-- #region Insert into TitleAuthor Table
INSERT INTO titleauthor
VALUES
(
@author_id
,@random_title_id
,@au_order
,@royalty_per
)
-- #endregion
-- #region Insert into Audit Table
INSERT INTO Audit . Book
VALUES
(
ISNULL(@pub_id, 1 )
,@pub_name
,@pub_city
,@pub_state
,@pub_country
,@random_title_id
,@prequel_id
,@book_title
, NULL
,@book_price
,@book_advance
,@book_royalty
,@book_ytd_sales
,@book_notes
,@book_pubdate
,@author_id
,@au_lname
,@au_fname
,@au_phone
,@au_address
,@au_city
,@au_state
,@au_zip
,@au_contract
,@royalty_per
,@au_order
,GETDATE()
, SYSTEM_USER
)
-- #endregion
PRINT( ' Book Has Been Sucessfully Added ' )
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
PRINT( ' An Error Occured During The Transaction. Error SP: ' + ERROR_PROCEDURE() + ' Error line: ' + CAST(ERROR_LINE() AS VARCHAR ))
PRINT(ERROR_MESSAGE())
END CATCH
END Хранимая процедура [dbo].[USP_InsertBook] немного сложна, поскольку она не создает просто книгу. Если с этой новой книгой не связан ни издатель, ни автор, это также помогает пользователю создать ее. Вот почему параметры @pub_id и @author_id хранимой процедуры [dbo].[USP_InsertBook] являются необязательными. Если пользователь выбирает существующего издателя или автора из элемента раскрывающегося меню во внешнем приложении, выбранное значение выбранного параметра в форме @pub_id или @author_id передается хранимой процедуре. Если автор или издатель новой книги не найден в раскрывающемся меню (или в базе данных), приложение позволяет легко их создать. Используя последние опции Create New Author... и Create New Publisher... в раскрывающемся меню, пользователь может создать нового автора или издателя. Также книга может иметь приквел или нет. Итак, учитывая все это, существует 4 возможных сценария создания книги.
Примечание. Пункты раскрывающегося меню заполняются существующими авторами и издателями в базе данных.
Эти тестовые примеры:
+ Create Book for Existing Author and Existing Publisher
+ Create Book for Existing Author and Non-Existent Publisher
+ Create Book for Non-Existent Author and Existing Publisher
+ Create Book for Non-Existent Author and Non-Existent Publisher
Тестовый пример 1. Создайте книгу для существующего автора и существующего издателя.
-- Publisher Allen & Unwin with pub_id = '9910' and Author J.R.R Tolkien with author_id = '254-26-6712' already exists on the db
EXEC pubs . dbo .USP_InsertBook @author_id = ' 254-26-6712 '
,@pub_id = ' 9910 '
,@au_order = 1
,@royalty_per = 100
,@book_title = ' The Silmarillion '
,@book_type = ' Mythopoeia Fantasy '
,@book_price = 45
,@book_advance = 2500000
,@book_royalty = 40
,@book_ytd_sales = 50000000
,@book_pubdate = ' 1977-09-15 '
,@book_notes = ' The Silmarillion is a collection of mythopoeic stories by the English writer J. R. R. Tolkien. 'Тестовый пример 4. Создание книги для несуществующего автора и несуществующего издателя
EXEC pubs . dbo .USP_InsertBook @pub_name = ' Crown Publishing Group '
,@pub_city = ' New York City '
,@pub_state = ' NY '
,@pub_country = ' US '
,@au_lname = ' Acemoglu '
,@au_fname = ' Daron '
,@au_phone = ' 999 000-0000 '
,@au_city = ' Newton '
,@au_state = ' MA '
,@au_order = 1
,@royalty_per = 50
,@book_title = ' Why Nations Fail '
,@book_type = ' Comparative Politics, Economics '
,@book_price = 70
,@book_advance = 100000
,@book_royalty = 30
,@book_ytd_sales = 500000
,@book_pubdate = ' 2012-03-20 '
,@book_notes = ' Why Nations Fail first published in 2012, is a book by economists Daron Acemoglu and James Robinson... 'Тестовый пример 2. Создайте книгу для существующего автора и несуществующего издателя.
-- A book by existing author Daron Acemoglu with author_id = '408-40-8965' but with different publisher
EXEC pubs . dbo .USP_InsertBook @pub_name = ' Cambridge University Press '
,@pub_city = ' Cambridge '
,@pub_country = ' UK '
,@author_id = ' 408-40-8965 '
,@au_order = 1
,@royalty_per = 50
,@book_title = ' Economic Origins of Dictatorship and Democracy '
,@book_type = ' Economics, Macroeconomics '
,@book_price = 30
,@book_advance = 50000
,@book_royalty = 15
,@book_ytd_sales = 500000
,@book_pubdate = ' 2012-09-01 '
,@book_notes = ' Book develops a framework for analyzing the creation and consolidation of democracy... '
Тестовый пример 3. Создайте книгу для несуществующего автора и существующего издателя.
-- Different book by existing 'Cambridge University Press' with pub_id = '9912'
EXEC pubs . dbo .USP_InsertBook @pub_id = ' 9912 '
,@au_lname = ' Von Zur Gathen '
,@au_fname = ' Joachim '
,@au_phone = ' 000 000-0000 '
,@au_city = ' Bonn '
,@au_order = 1
,@royalty_per = 50
,@book_title = ' Modern Computer Algebra '
,@book_type = ' Computer Science '
,@book_price = 100
,@book_advance = 50000
,@book_royalty = 10
,@book_ytd_sales = 10000
,@book_pubdate = ' 2013-01-01 '
,@book_notes = ' Computer algebra systems are now ubiquitous in all areas of science and engineering... '
Ссылка на интерфейсный модуль пользовательского интерфейса: Посмотреть демонстрацию
Хранимая процедура добавления автора в таблицу Authors
/*
Created by Nury Amanmadov
Date created: 17.04.2022
*/
CREATE PROCEDURE [dbo].[USP_CreateAuthor]
@au_lname VARCHAR ( 40 )
,@au_fname VARCHAR ( 20 )
,@au_phone CHAR ( 12 ) = ' 000 000-0000 '
,@au_address VARCHAR ( 250 ) = NULL
,@au_city VARCHAR ( 20 ) = NULL
,@au_state CHAR ( 2 ) = NULL
,@au_zip CHAR ( 5 ) = NULL
,@au_contract BIT = 0
AS
BEGIN
BEGIN TRY
-- Generate Random AuthorID
DECLARE @au_id dbo . tid
SET @au_id = dbo . fn_GenerateRandomAuthorId (RAND())
INSERT INTO authors
VALUES
(
@au_id
,@au_lname
,@au_fname
,@au_phone
,@au_address
,@au_city
,@au_state
,@au_zip
,@au_contract
)
END TRY
BEGIN CATCH
PRINT( ' An Error Occured During The Transaction. Error SP: ' + ERROR_PROCEDURE() + ' Error line: ' + CAST(ERROR_LINE() AS VARCHAR ))
PRINT(ERROR_MESSAGE())
END CATCH
END Поскольку author_id используется в нескольких хранимых процедурах, я создал функцию, которая author_id .
CREATE FUNCTION [dbo].[fn_GenerateRandomAuthorId](
@RAND FLOAT
)
RETURNS VARCHAR ( 11 ) AS
BEGIN
DECLARE @author_id VARCHAR ( 11 )
DECLARE @isFound BIT = 0
WHILE (@isFound = 0 )
BEGIN
DECLARE @a1 AS CHAR ( 3 ) = ( SELECT (CAST((FLOOR(@RAND * ( 999 - 100 + 1 ) + 100 )) AS CHAR )))
DECLARE @a2 AS CHAR ( 2 ) = ( SELECT (CAST((FLOOR(@RAND * ( 99 - 10 + 1 ) + 10 )) AS CHAR )))
DECLARE @a3 AS CHAR ( 4 ) = ( SELECT (CAST((FLOOR(@RAND * ( 9999 - 1000 + 1 ) + 1000 )) AS CHAR )))
SET @author_id = ( SELECT @a1 + ' - ' + @a2 + ' - ' + @a3)
IF EXISTS( SELECT TOP 1 1 FROM [authors] WHERE au_id = @author_id)
BEGIN
CONTINUE
END
ELSE
BEGIN
SET @isFound = 1
END
END
RETURN @author_id
END;
Ссылка на интерфейсный модуль пользовательского интерфейса: Посмотреть демонстрацию
Хранимая процедура добавления CoAuthor . Если соавтор не существует в раскрывающемся меню, мы можем создать нового автора и добавить его.
-- for demonstration purposes some columns (like address) are generated with dummy data
CREATE PROCEDURE USP_InsertCoAuthorForTitle
@title_id dbo . tid
,@au_id VARCHAR ( 11 ) = NULL
,@au_lname VARCHAR ( 40 ) = NULL
,@au_fname VARCHAR ( 20 ) = NULL
,@au_phone CHAR ( 12 ) = ' 000-000-0000 '
,@au_city VARCHAR ( 20 ) = NULL
,@au_state CHAR ( 2 ) = NULL
,@au_order TINYINT = 2
,@royalty_per INT = 0
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
IF NOT EXISTS( SELECT TOP 1 1 FROM titles WHERE title_id = @title_id)
BEGIN
;THROW 50001 , ' Book with provided ID does not exist ' , 1
END
IF(@au_id IS NULL )
BEGIN
DECLARE @au_zip CHAR ( 5 )
DECLARE @au_contract BIT
DECLARE @au_address VARCHAR ( 40 )
-- Generate Random AuthorID
SET @au_id = dbo . fn_GenerateRandomAuthorId (RAND())
-- Setting Random Zip in the 99xyz format
SET @au_zip = ' 99 ' + ( SELECT (CAST((FLOOR(RAND() * ( 999 - 100 + 1 ) + 100 )) AS CHAR )))
-- Setting @au_contract as random
SET @au_contract = ( SELECT (CAST((FLOOR(RAND() * ( 1 - 0 + 1 ) + 0 )) AS BIT )))
-- Setting Random Address from AdventureWorks DB Person.Address table
SET @au_address = (
SELECT TOP 1 LEFT(AddressLine1, 40 )
FROM AdventureWorks . Person .Address
WHERE AddressLine1 IS NOT NULL
ORDER BY NEWID()
)
INSERT INTO authors
VALUES
(
@au_id
,@au_lname
,@au_fname
,@au_phone
,@au_address
,@au_city
,@au_state
,@au_zip
,@au_contract
)
INSERT INTO titleauthor
VALUES
(
@au_id
,@title_id
,@au_order
,@royalty_per
)
END
ELSE
BEGIN
IF NOT EXISTS( SELECT TOP 1 1 FROM authors WHERE au_id = @au_id)
BEGIN
;THROW 50002 , ' Author with provided ID does not exist ' , 1
END
ELSE
BEGIN
INSERT INTO titleauthor
VALUES
(
@au_id
,@title_id
,@au_order
,@royalty_per
)
END
END
PRINT( ' CoAuthor for the Provided Book Has Been Sucessfully Added ' )
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
PRINT( ' An Error Occured During The Transaction. Error SP: ' + ERROR_PROCEDURE() + ' Error line: ' + CAST(ERROR_LINE() AS VARCHAR ))
PRINT(ERROR_MESSAGE())
END CATCH
END
Ссылка на интерфейсный модуль пользовательского интерфейса: Посмотреть демонстрацию
Хранимая процедура добавления сотрудника в таблицу Employee
/*
Created by Nury Amanmadov
Date created: 11.04.2022
*/
CREATE PROCEDURE [dbo].[USP_CreateEmployee]
(
@firstname AS VARCHAR ( 100 )
,@lastname AS VARCHAR ( 100 )
,@address AS VARCHAR ( 200 )
,@homephone AS CHAR ( 12 )
,@cellphone AS CHAR ( 12 )
,@salary_type AS CHAR ( 1 )
,@salary AS DECIMAL ( 6 , 0 )
,@birthdate DATE
,@degee_id INT
,@school_id INT
,@branch_id INT
,@ishead_librarin BIT
,@emp_type_id INT
,@degreedate DATE
)
AS
BEGIN
SET NOCOUNT ON ;
BEGIN TRY
BEGIN TRANSACTION
DECLARE @id INT = ( SELECT MAX (employee_id) + 1 FROM employees)
INSERT INTO [pubs].[dbo].[employees]
VALUES (
@id
,@firstname
,@lastname
,@address
,@homephone
,@cellphone
,@salary_type
,@salary
,@birthdate
,GETDATE()
, 114
,@degee_id
,@school_id
,@branch_id
,@ishead_librarin
,@emp_type_id
,@degreedate
, 1
)
-- #endregion
PRINT( ' Employee Has Been Sucessfully Added ' )
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
PRINT( ' An Error Occured During The Transaction. Error SP: ' + ERROR_PROCEDURE() + ' Error line: ' + CAST(ERROR_LINE() AS VARCHAR ))
PRINT(ERROR_MESSAGE())
END CATCH
END 
Ссылка на интерфейсный модуль пользовательского интерфейса: Посмотреть демонстрацию
Хранимая процедура получения books в виде HTML table row
/*
Created by Nury Amanmadov
Date created: 16.04.2022 ddMMyyyy
Selects TOP 20 Book as in the form of HTML table row records
These records then rendered as an HTML elements on the front-end side
*/
CREATE PROCEDURE [dbo].[USP_GetAllBooks]
AS
BEGIN
SELECT TOP 20
' <tr class="even pointer">
<td class="a-center "><input type="checkbox" class="flat" name="table_records"></td>
<td> ' + t . title_id + ' </td>
<td> ' + title + ' </td>
<td> ' + a . au_fname + ' ' + a . au_lname + ' </td>
<td> ' + p . pub_name + ' </td>
<td> ' + type + ' </td>
<td> ' + ISNULL(CAST(price AS VARCHAR ), ' NA ' ) + ' </td>
<td> ' + ISNULL(CAST(advance AS VARCHAR ), ' NA ' ) + ' </td>
<td> ' + ISNULL(CAST(royalty AS VARCHAR ), ' NA ' ) + ' </td>
<td> ' + ISNULL(CAST(ytd_sales AS VARCHAR ), ' NA ' ) + ' </td>
<td class="last"><a href="#">View</a></td>
</tr> ' AS TableRow
FROM titles t
JOIN publishers p ON t . pub_id = p . pub_id
JOIN titleauthor ta ON ta . title_id = t . title_id
JOIN authors a ON a . au_id = ta . au_id
ORDER BY p . pub_id DESC
END
Ссылка на интерфейсный модуль пользовательского интерфейса: Посмотреть демонстрацию
Хранимая процедура для получения authors в виде HTML table row
CREATE PROCEDURE [dbo].[USP_GetAllAuthors]
AS
BEGIN
SELECT TOP 20
' <tr class="even pointer">
<td class="a-center "><input type="checkbox" class="flat" name="table_records"></td>
<td> ' + au_id + ' </td>
<td> ' + au_fname + ' </td>
<td> ' + au_lname + ' </td>
<td> ' + ISNULL(CAST(phone AS VARCHAR ), ' NA ' ) + ' </td>
<td> ' + ISNULL(CAST(address AS VARCHAR ), ' NA ' ) + ' </td>
<td> ' + ISNULL(CAST(city AS VARCHAR ), ' NA ' ) + ' </td>
<td> ' + ISNULL(CAST(state AS VARCHAR ), ' NA ' ) + ' </td>
<td> ' + ISNULL(CAST(zip AS VARCHAR ), ' NA ' ) + ' </td>
<td> ' + ISNULL(CAST(contract AS VARCHAR ), ' NA ' ) + ' </td>
<td class="last"><a href="#">View</a></td>
</tr> ' AS TableRow
FROM authors a
ORDER BY zip DESC
END
Ссылка на интерфейсный модуль пользовательского интерфейса: Посмотреть демонстрацию
Хранимая процедура для получения publishers в виде HTML table row
CREATE PROCEDURE [dbo].[USP_GetAllPublishers]
AS
BEGIN
SELECT TOP 20
' <tr class="even pointer">
<td class="a-center "><input type="checkbox" class="flat" name="table_records"></td>
<td> ' + p . pub_id + ' </td>
<td> ' + p . pub_name + ' </td>
<td> ' + p . city + ' </td>
<td> ' + ISNULL(p.[state], ' NA ' ) + ' </td>
<td> ' + p . country + ' </td>
<td> ' + CAST( COUNT (DISTINCT t . title_id ) AS VARCHAR ) + ' </td>
<td> ' + CAST(ISNULL( SUM ( t . ytd_sales ), 0 ) AS VARCHAR ) + ' </td>
<td> ' + CAST( COUNT (DISTINCT e . emp_id ) AS VARCHAR ) + ' </td>
<td class="last"><a href="#">View</a></td>
</tr> ' AS TableRow
FROM publishers p
LEFT JOIN titles t ON t . pub_id = p . pub_id
LEFT JOIN employee e ON e . pub_id = p . pub_id
GROUP BY p . pub_id
, p . pub_name
, p . city
,p.[state]
, p . country
ORDER BY SUM ( t . ytd_sales ) DESC
END
Ссылка на интерфейсный модуль пользовательского интерфейса: Посмотреть демонстрацию
Здесь, на стороне внешнего интерфейса, я разработал некоторую логику с использованием JavaScript для имитации функциональности хранимых процедур. Это Recursive хранимая процедура для получения книги-приквела или книг для конкретной книги в серии. Использовал CTE внутри запроса.
/*
Created by Nury Amanmadov
Date created: 17.04.2022 ddMMyyyy
Selects all the prequel book series of a given book
*/
CREATE PROCEDURE [dbo].[USP_GetAllPrequelBooksByTitleId]
@title_id dbo . tid
AS
BEGIN
IF NOT EXISTS( SELECT TOP 1 1 FROM titles WHERE title_id = @title_id)
BEGIN
;THROW 50001 , ' Book with provided ID does not exist ' , 1
END
;WITH CTEBooks
AS
(
SELECT title_id AS TitleId
,title AS Book
,prequel_id
,CAST( t1 . pubdate AS DATE ) AS [Publication Date ]
, p . pub_name AS Publisher
FROM titles t1
JOIN publishers p ON p . pub_id = t1 . pub_id
WHERE title_id = @title_id
UNION ALL
SELECT t2 . title_id AS TitleId
, t2 . title AS Book
, t2 . prequel_id
,CAST( t2 . pubdate AS DATE ) AS [Publication Date ]
, p . pub_name AS Publisher
FROM titles t2
JOIN publishers p ON p . pub_id = t2 . pub_id
JOIN CTEBooks ON t2 . title_id = CTEBooks . prequel_id
)
SELECT CTEBooks . TitleId
, CTEBooks . Book
,ISNULL( t . title , ' No Prequel ' ) AS PrequelBook
,[Publication Date ]
, a . au_fname + ' ' + a . au_lname AS Author
, CTEBooks . Publisher
FROM CTEBooks
LEFT JOIN titles t ON CTEBooks . prequel_id = t . title_id
LEFT JOIN titleauthor ta ON ta . title_id = CTEBooks . TitleId
LEFT JOIN authors a ON a . au_id = ta . au_id
END Теперь, если мы выполним эту хранимую процедуру с title_id продолжающейся книги в серии, мы получим книги-приквелы этой книги.
-- For 'Harry Potter and the Deathly Hallows' in the 'Harry Potter' Series
EXEC USP_GetAllPrequelBooksByTitleId @title_id = ' GU4539 ' 
-- For 'The Return of the King' in the 'Lord of the Rings' Series
EXEC USP_GetAllPrequelBooksByTitleId @title_id = ' ZJ4675 ' 
-- For 'A Dream of Spring' in the 'Game of the Thrones' Series
EXEC USP_GetAllPrequelBooksByTitleId @title_id = ' SA4547 ' 
/*
Created by Nury Amanmadov
Date created: 18.04.2022 ddMMyyyy
Selects all the continuing books in series
*/
CREATE PROCEDURE [dbo].[USP_GetAllContinuingBooksByTitleId]
@title_id dbo . tid
AS
BEGIN
IF NOT EXISTS( SELECT TOP 1 1 FROM titles WHERE title_id = @title_id)
BEGIN
;THROW 50001 , ' Book with provided ID does not exist ' , 1
END
;WITH CTEBooks
AS
(
SELECT title_id AS TitleId
,title AS Book
,prequel_id
,CAST( t1 . pubdate AS DATE ) AS [Publication Date ]
, p . pub_name AS Publisher
,[Order In Series] = 1
FROM titles t1
JOIN publishers p ON p . pub_id = t1 . pub_id
WHERE title_id = @title_id
UNION ALL
SELECT t2 . title_id AS TitleId
, t2 . title AS Book
, t2 . prequel_id
,CAST( t2 . pubdate AS DATE ) AS [Publication Date ]
, p . pub_name AS Publisher
,[Order In Series] + 1
FROM titles t2
JOIN publishers p ON p . pub_id = t2 . pub_id
JOIN CTEBooks ON t2 . prequel_id = CTEBooks . TitleId
)
SELECT CTEBooks . TitleId
, CTEBooks . Book
,ISNULL( t . title , ' No Prequel ' ) AS PrequelBook
,[Publication Date ]
, a . au_fname + ' ' + a . au_lname AS Author
, CTEBooks . Publisher
,[Order In Series]
FROM CTEBooks
LEFT JOIN titles t ON CTEBooks . prequel_id = t . title_id
LEFT JOIN titleauthor ta ON ta . title_id = CTEBooks . TitleId
LEFT JOIN authors a ON a . au_id = ta . au_id
END Теперь, если мы выполним эту хранимую процедуру с title_id книги в серии, мы получим продолжающиеся книги этой серии.
-- For 'The Lord of the Rings' Series
EXEC [dbo].[USP_GetAllContinuingBooksByTitleId] ' EX5727 ' 
-- For 'A Game of Thrones' Series
EXEC [dbo].[USP_GetAllContinuingBooksByTitleId] ' CI5668 ' 
-- For 'Harry Potter' Series
EXEC [dbo].[USP_GetAllContinuingBooksByTitleId] ' VC5136 ' 
Хранимая процедура удаления книги
/*
Created by Nury Amanmadov
Date created: 16.04.2022
Rule for deleting a book
- Publisher related to that title will not be deleted
- PublisherInfo related to the Publisher of that title will not be deleted
- Author related to that title will not be deleted
- Records related to that title from Titles and TitleAuthor tables will be deleted
Because there are publishers without any title on the initial database created by Microsoft (ex: pub_id with 1622 and 1756)
SELECT DISTINCT pub_id
FROM publishers
WHERE pub_id NOT IN
(
SELECT DISTINCT pub_id FROM titles
)
*/
CREATE PROCEDURE [dbo].[USP_DeleteBook]
@title_id dbo . tid ,
@au_id dbo . id
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
DELETE FROM titles WHERE title_id = @title_id
DELETE FROM titleauthor WHERE title_id = @title_id and au_id = @au_id
PRINT( ' Book Has Been Sucessfully Deleted ' )
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
PRINT( ' An Error Occured During The Transaction. Error SP: ' + ERROR_PROCEDURE() + ' Error line: ' + CAST(ERROR_LINE() AS VARCHAR ))
PRINT(ERROR_MESSAGE())
END CATCH
END SELECT *
FROM [pubs].[Audit].[Book]Ссылка на файл записей [Аудит].[Книга]: Просмотр записей
Библиотека выдает читательские билеты людям, желающим брать книги в библиотеке. Библиотека ведет список каждого заемщика, сохраняя card id , borrower name , address , phone number , birthdate , date the card was issued и balance due . Срок действия карты истекает через десять лет с момента ее выдачи. Если человеку меньше 18 лет , библиотека также сохранит информацию о родителе или законном опекуне Заемщика, такую как name , address и phone number . Одновременно человек может иметь только один действующий читательский билет . Человеку не могут выдать новый читательский билет , если у него есть задолженность по просроченной карточке .
/*
Rules for creating a card for borrower
- A person can have only one valid library card at a given time.
- A person can’t be issued a new library card, if he owes money on an expired card.
- a person can not be younger than 10.
*/
CREATE PROCEDURE [dbo].[USP_CreateBorrower]
@ssn VARCHAR ( 11 )
,@fname VARCHAR ( 100 )
,@lname VARCHAR ( 100 )
,@address VARCHAR ( 200 )
,@phone CHAR ( 12 )
,@birthdate DATE
,@lg_address VARCHAR ( 200 ) = NULL
,@lg_name VARCHAR ( 100 ) = NULL
,@lg_phone VARCHAR ( 12 ) = NULL
AS
BEGIN
BEGIN TRY
-- Check if borrower has an active card
IF EXISTS ( SELECT TOP 1 1 FROM borrowers WHERE ssn = @ssn AND isexpired = 0 )
BEGIN
;THROW 50001 , ' A person already has an active card. ' , 1
END
-- Check if borrower has an a balancedue on expired card
IF EXISTS ( SELECT TOP 1 1 FROM borrowers WHERE ssn = @ssn AND isexpired = 1 AND balancedue > 0 )
BEGIN
;THROW 50002 , ' A person owes library from previous card. ' , 1
END
-- Check if borrower is older than 10
DECLARE @age INT = DATEDIFF(YY,@birthdate,GETDATE())
IF(@age < 10 )
BEGIN
;THROW 50003 , ' Borrower can not be younger than 10. ' , 1
END
DECLARE @id INT = ( SELECT MAX (id) + 1 FROM borrowers)
DECLARE @cardid INT = ( SELECT MAX (card_id) + 1 FROM borrowers)
-- validation of legal guardian details should be on the front-end
INSERT INTO borrowers
VALUES
(
ISNULL(@id, 1 ) -- if first time
,ISNULL(@cardid, 1 ) -- if first time
,@ssn
,@fname
,@lname
,@address
,@phone
,@birthdate
,GETDATE()
, 0 -- initial balance
, 0 -- default value
,@lg_address
,@lg_name
,@lg_phone
)
END TRY
BEGIN CATCH
PRINT( ' An Error Occured During The Transaction. Error SP: ' + ERROR_PROCEDURE() + ' Error line: ' + CAST(ERROR_LINE() AS VARCHAR ))
PRINT(ERROR_MESSAGE())
END CATCH
ENDХранимая процедура истечения срока действия карты через 10 лет
CREATE PROCEDURE [dbo].[USP_ExpireLibraryCards]
AS
BEGIN
UPDATE borrowers SET isexpired = 1
WHERE DATEDIFF(YY,card_issuedate,GETDATE()) >= 10
ENDСозданы фиктивные данные заемщика с помощью хранимой процедуры, приведенной ниже.
CREATE PROCEDURE [dbo].[USP_InsertRandomBorrower]
AS
BEGIN
DECLARE @id INT
DECLARE @ssn VARCHAR ( 11 )
DECLARE @fname VARCHAR ( 100 )
DECLARE @lname VARCHAR ( 100 )
DECLARE @address VARCHAR ( 200 )
DECLARE @phone CHAR ( 12 )
DECLARE @birthdate DATE
DECLARE @cardissuedate DATE
DECLARE @balancedue DECIMAL ( 6 , 2 )
DECLARE @lgname VARCHAR ( 200 ) = NULL
DECLARE @lgaddress VARCHAR ( 200 ) = NULL
DECLARE @lgphone VARCHAR ( 200 ) = NULL
SET @id = ( SELECT MAX (id) + 1 FROM borrowers )
SET @fname = dbo . fn_GenerateFirstName ()
SET @lname = dbo . fn_GenerateLastName ()
SET @ssn = dbo . fn_GenerateRandomSsn (RAND())
SET @address = dbo . fn_GenerateRandomAddress ()
SET @phone = dbo . fn_GenerateRandomPhone (RAND())
SET @birthdate = dbo . fn_GenerateRandomDate ( ' 1999-01-01 ' , ' 2010-01-01 ' ,RAND())
DECLARE @age INT = DATEDIFF(YY,@birthdate,GETDATE())
SET @cardissuedate = DATEADD(year, dbo . fn_GetRandomNumber ( 5 ,@age,RAND()), @birthdate)
SET @balancedue = 0
IF(@age < 17 )
BEGIN
SET @lgname = dbo . fn_GenerateFirstName ()
-- Set randomly address of borrower same as guardians
DECLARE @isSameAddress BIT = CAST(ROUND(RAND(), 0 ) AS BIT )
IF(@isSameAddress = 0 )
BEGIN
SET @lgaddress = @address
END
ELSE
BEGIN
SET @lgaddress = dbo . fn_GenerateRandomAddress ()
END
SET @lgphone = dbo . fn_GenerateRandomPhone (RAND())
END
INSERT INTO borrowers
VALUES
(
ISNULL(@id, 1 ) -- if first time
,ISNULL(@id, 1 ) -- if first time
,@ssn
,@fname
,@lname
,@address
,@phone
,@birthdate
,@cardissuedate
,@balancedue
, 0 -- default value
,@lgaddress
,@lgname
,@lgphone
)
PRINT( ' Borrower sucessfully created. ' )
ENDПользовательские функции для создания необходимых фиктивных данных
-- Since inside user defined functions we cant use NEWID() or RAND()
CREATE View [dbo].[view_NewID] AS SELECT NEWID() AS idCREATE FUNCTION [dbo].[fn_GenerateFirstName]()
RETURNS VARCHAR ( 100 ) AS
BEGIN
DECLARE @fname VARCHAR ( 100 )
SET @fname =
(
SELECT TOP 1 p . FirstName
FROM AdventureWorks . Person .Person p
ORDER BY ( SELECT id FROM dbo . view_NewID )
)
RETURN @fname
END;CREATE FUNCTION [dbo].[fn_GenerateLastName]()
RETURNS VARCHAR ( 100 ) AS
BEGIN
DECLARE @lname VARCHAR ( 100 )
SET @lname =
(
SELECT TOP 1 p . LastName
FROM AdventureWorks . Person .Person p
ORDER BY ( SELECT id FROM dbo . view_NewID )
)
RETURN @lname
END;CREATE FUNCTION [dbo].[fn_GenerateRandomAddress]()
RETURNS VARCHAR ( 200 ) AS
BEGIN
DECLARE @address VARCHAR ( 200 )
SET @address =
(
SELECT TOP 1 ad . AddressLine1
FROM AdventureWorks . Person .Address ad
WHERE AddressLine1 IS NOT NULL
ORDER BY ( SELECT id FROM dbo . view_NewID )
)
RETURN @address
END;CREATE FUNCTION [dbo].[fn_GenerateRandomDate]
(
@DateStart DATE
,@DateEnd DATE
,@RAND FLOAT
)
RETURNS DATE AS
BEGIN
DECLARE @randomDate DATE
SET @randomDate = DateAdd(Day, @RAND * DateDiff(Day, @DateStart, @DateEnd), @DateStart)
RETURN @randomDate
END;CREATE FUNCTION [dbo].[fn_GenerateRandomPhone]
(
@RAND FLOAT
)
RETURNS VARCHAR ( 12 ) AS
BEGIN
DECLARE @phone VARCHAR ( 12 )
DECLARE @p1 AS CHAR ( 3 ) = ( SELECT (CAST((FLOOR(@RAND * ( 999 - 100 + 1 ) + 100 )) AS CHAR )))
DECLARE @p2 AS CHAR ( 3 ) = ( SELECT (CAST((FLOOR(@RAND * ( 999 - 100 + 1 ) + 100 )) AS CHAR )))
DECLARE @p3 AS CHAR ( 4 ) = ( SELECT (CAST((FLOOR(@RAND * ( 9999 - 1000 + 1 ) + 1000 )) AS CHAR )))
SET @phone = ( SELECT @p1 + ' - ' + @p2 + ' - ' + @p3)
RETURN @phone
END;CREATE FUNCTION [dbo].[fn_GenerateRandomSsn]
(
@RAND FLOAT
)
RETURNS VARCHAR ( 11 ) AS
BEGIN
DECLARE @ssn VARCHAR ( 11 )
SET @ssn = (
CAST(CAST( 100 + ( 898 * @RAND) AS INT ) AS VARCHAR ( 3 )) +
' - ' +
CAST(CAST( 10 + ( 88 * @RAND) AS INT ) AS VARCHAR ( 2 )) +
' - ' +
CAST(CAST( 1000 + ( 8998 * @RAND) AS INT ) AS VARCHAR ( 4 ))
)
RETURN @ssn
END;CREATE FUNCTION [dbo].[fn_GenerateRandomTitleId](
@RAND FLOAT
)
RETURNS [dbo].[tid] AS
BEGIN
DECLARE @random_title_id [dbo].[tid]
DECLARE @isFound BIT = 0
WHILE (@isFound = 0 )
BEGIN
DECLARE @randomid1 UNIQUEIDENTIFIER = ( SELECT id FROM dbo . view_NewID )
DECLARE @randomid2 UNIQUEIDENTIFIER = ( SELECT id FROM dbo . view_NewID )
DECLARE @t1 AS CHAR ( 1 ) = ( SELECT SUBSTRING ( ' ABCDEFGHIJKLMNOPQRSTUVWXYZ ' ,(ABS(CHECKSUM(@randomid1)) % 26 ) + 1 , 1 ))
DECLARE @t2 AS CHAR ( 1 ) = ( SELECT SUBSTRING ( ' ABCDEFGHIJKLMNOPQRSTUVWXYZ ' ,(ABS(CHECKSUM(@randomid2)) % 26 ) + 1 , 1 ))
DECLARE @t3 AS CHAR ( 1 ) = ( SELECT (CAST((FLOOR(@RAND * ( 9 - 1 + 1 ) + 1 )) AS CHAR )))
DECLARE @t4 AS CHAR ( 1 ) = ( SELECT (CAST((FLOOR(@RAND * ( 9 - 1 + 1 ) + 1 )) AS CHAR )))
DECLARE @t5 AS CHAR ( 1 ) = ( SELECT (CAST((FLOOR(@RAND * ( 9 - 1 + 1 ) + 1 )) AS CHAR )))
DECLARE @t6 AS CHAR ( 1 ) = ( SELECT (CAST((FLOOR(@RAND * ( 9 - 1 + 1 ) + 1 )) AS CHAR )))
SET @random_title_id = ( SELECT @t1 + @t2 + @t3 + @t4 + @t5 + @t6)
IF EXISTS( SELECT TOP 1 1 FROM [titles] WHERE title_id = @random_title_id)
BEGIN
CONTINUE
END
ELSE
BEGIN
SET @isFound = 1
END
END
RETURN @random_title_id
END; Для каждого экземпляра книги, взятого напрокат, библиотека отслеживает copy id и card number человека, который его одолжил. Библиотека отслеживает date on which it was borrowed и записывает дату сдачи, которая наступает через две недели после даты получения. Когда копия возвращается, эта запись обновляется с указанием return date . Когда экземпляр книги заимствован, он помечается как BORROWED . Когда экземпляр книги возвращается, экземпляр помечается как AVAILABLE или NOT BORROWED . Заемщик не может одолжить книгу в определенном филиале, если в этом филиале нет экземпляра этой книги и она в настоящее время находится на складе (например, не взята кем-то другим). Когда заемщик возвращает экземпляр книги после установленного срока, система рассчитывает amount owed и любые просроченные платежи добавляются к card balance . Заемщик не может использовать карту для получения книг, если его задолженность по этой карте превышает 10 долларов . В библиотеке имеется список просроченных платежей. В настоящее время плата составляет 0,05 доллара в день за книги для детей и 0,10 доллара в день за книги для взрослых. Если книга возвращается с опозданием, заемщик уплачивает сборы, действующие на момент возврата книги. Любой материал для чтения, отнесенный к категории reference , не может быть заимствован.
Хранимая процедура для операции заимствования
/*
Rules for borrowing a book:
- Any reading item that is categorized as reference may not be borrowed.
- Copies that are in POOR condition may not be borrowed.
- When a book copy is borrowed, the copy is marked as BORROWED. BORROWED copies may not be borrowed.
- A borrower can not use a card to borrow books, if he owes more than 10 dollars on that card.
*/
CREATE PROCEDURE [dbo].[USP_BorrowBook]
@copy_id AS INT ,
@card_id AS INT
AS
BEGIN
SET NOCOUNT ON ;
BEGIN TRY
BEGIN TRANSACTION
-- #region Check if copy is of type Reference
IF EXISTS(
SELECT TOP 1 1
FROM titlecategory tc
JOIN bookcopies bc ON bc . title_id = tc . title_id
WHERE bc . copy_id = @copy_id AND tc . title_type_id = ( SELECT type_id FROM category c WHERE c.[type] = ' Reference ' )
)
BEGIN
;THROW 50001 , ' Copy of type Reference can not be borrowed ' , 1
END
-- #endregion
-- #region Check if copy is in POOR
IF EXISTS (
SELECT copy_id
FROM bookcopies
WHERE condition = ' POOR ' AND copy_id = @copy_id
)
BEGIN
;THROW 50002 , ' Copies in POOR condition can not be borrowed ' , 1
END
-- #endregion
-- #region Check if copy is BORROWED or Discarded
IF EXISTS (
SELECT copy_id
FROM bookcopies
WHERE (isactive = 0 OR isavailable = 0 ) AND copy_id = @copy_id
)
BEGIN
;THROW 50003 , ' Copies that are Discarded or already Borrowed can not be borrowed. ' , 1
END
-- #endregion
-- #region Check if borrower is available to borrow a book
IF EXISTS (
SELECT *
FROM borrowers
WHERE card_id = @card_id AND (isexpired = 1 OR balancedue >= 10 )
)
BEGIN
;THROW 50004 , ' Either borrowers card expired or borrower owes over 10 dollars. ' , 1
END
-- #endregion
UPDATE bookcopies SET isavailable = 0 WHERE copy_id = @copy_id
DECLARE @id INT = ( SELECT MAX (id) + 1 FROM books_borrowed)
INSERT INTO books_borrowed
VALUES
(
ISNULL(@id, 1 ),
@copy_id,
@card_id,
GETDATE(),
GETDATE() + 14 ,
0 ,
NULL
)
PRINT( ' Book Has Been Sucessfully Borrowed ' )
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
PRINT( ' An Error Occured During The Transaction. Error SP: ' + ERROR_PROCEDURE() + ' Error line: ' + CAST(ERROR_LINE() AS VARCHAR ))
PRINT(ERROR_MESSAGE())
END CATCH
END Хранимая процедура для возврата операции
CREATE PROCEDURE [dbo].[USP_ReturnBook]
@card_id INT ,
@copy_id INT
AS
BEGIN
SET NOCOUNT ON ;
BEGIN TRY
BEGIN TRANSACTION
-- #region Check borrowed book exists
IF NOT EXISTS( SELECT TOP 1 1 FROM books_borrowed WHERE card_id = @card_id AND copy_id = @copy_id AND isReturned = 0 )
BEGIN
;THROW 50001 , ' There is no borrowed book with the given details. ' , 1
END
-- #endregion
DECLARE @id INT = ( SELECT id FROM books_borrowed WHERE card_id = @card_id AND copy_id = @copy_id AND isReturned = 0 )
DECLARE @daysElapsed INT = ( SELECT DATEDIFF(DD,borroweddate,GETDATE()) FROM books_borrowed WHERE id = @id)
-- PRINT(CAST(@daysElapsed as varchar))
-- Set book available to borrow
UPDATE bookcopies SET isavailable = 1 WHERE copy_id = @copy_id
-- Set isReturned to true
UPDATE books_borrowed SET isReturned = 1 , returndate = GETDATE() WHERE id = @id
DECLARE @pr DECIMAL ( 6 , 2 )
IF(@daysElapsed > 14 )
BEGIN
DECLARE @typeId INT = ( SELECT tc . title_type_id FROM bookcopies bc JOIN titlecategory tc ON tc . title_id = bc . title_id WHERE copy_id = @copy_id)
-- Check if Juvenile
IF(@typeId = 4 )
BEGIN
SET @pr = . 05
END
ELSE
BEGIN
SET @pr = . 10
END
-- Update card balancedue
UPDATE borrowers SET balancedue = balancedue + (@pr * @daysElapsed) WHERE card_id = @card_id
END
PRINT( ' Book Has Been Sucessfully Returned ' )
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
PRINT( ' An Error Occured During The Transaction. Error SP: ' + ERROR_PROCEDURE() + ' Error line: ' + CAST(ERROR_LINE() AS VARCHAR ))
PRINT(ERROR_MESSAGE())
END CATCH
END Хранимая процедура для вывода списка всех доступных книг для заимствования
CREATE PROCEDURE [dbo].[USP_GetAllAvailableBooks]
AS
BEGIN
SELECT *
FROM bookcopies bc
JOIN titles t ON t . title_id = bc . title_id
JOIN titlecategory tc ON tc . title_id = bc . title_id
JOIN category c ON c . type_id = tc . title_type_id
WHERE bc . isactive = 1 AND isavailable = 1 AND c . type <> ' Reference '
END Библиотека присваивает Condition каждому экземпляру книги. Примерные значения состояния могут быть NEW , EXCELLENT , GOOD , WORN и POOR . В конечном итоге копии, находящиеся в POOR состоянии, будут выброшены и заменены новыми копиями. Заемщик может признать, что он потерял экземпляр книги. В этом случае копия помечается как LOST , а стоимость книги добавляется к балансу карты. В конечном итоге копия может быть удалена из текущего списка копий ветвей и сохранена в файле истории.
Добавлена ПРОВЕРКА ОГРАНИЧЕНИЯ в таблицу Bookcopies для столбца условия.
ALTER TABLE [dbo].[bookcopies] WITH CHECK ADD CONSTRAINT [CK__bookcopie__condi__09746778]
CHECK (([condition] = ' NEW ' OR [condition] = ' EXCELLENT ' OR [condition] = ' GOOD ' OR [condition] = ' WORN ' OR [condition] = ' POOR ' OR [condition] = ' LOST ' )) Хранимая процедура удаления книги в POOR состоянии
CREATE PROCEDURE [dbo].[USP_DiscardBook]
@copy_id INT
AS
BEGIN
-- Check if copy_id is valid
IF NOT EXISTS( SELECT TOP 1 1 FROM bookcopies WHERE copy_id = @copy_id)
BEGIN
;THROW 50001 , ' Book copy with provided ID does not exist. ' , 1
END
-- Check if book is returned
IF EXISTS( SELECT TOP 1 1 FROM bookcopies WHERE copy_id = @copy_id AND isavailable = 0 )
BEGIN
;THROW 50002 , ' Book copy with provided ID has not been returned. ' , 1
END
-- Check if book has not been discarded
IF EXISTS( SELECT TOP 1 1 FROM bookcopies WHERE copy_id = @copy_id AND isactive = 0 )
BEGIN
;THROW 50003 , ' Book copy with provided ID has been already discarded. ' , 1
END
UPDATE bookcopies SET isavailable = 0 , isactive = 0 , condition = ' POOR ' WHERE copy_id = @copy_id
END Хранимая процедура удаления LOST книги
CREATE PROCEDURE [dbo].[USP_DiscardLostBook]
@copy_id AS INT ,
@card_id AS INT
AS
BEGIN
SET NOCOUNT ON ;
BEGIN TRY
BEGIN TRANSACTION
-- #region Check borrowed book exists
IF NOT EXISTS( SELECT TOP 1 1 FROM books_borrowed WHERE card_id = @card_id AND copy_id = @copy_id AND isReturned = 0 )
BEGIN
;THROW 50001 , ' There is no borrowed book with the given details. ' , 1
END
-- #endregion
DECLARE @id INT = ( SELECT id FROM books_borrowed WHERE card_id = @card_id AND copy_id = @copy_id AND isReturned = 0 )
DECLARE @daysElapsed INT = ( SELECT DATEDIFF(DD,borroweddate,GETDATE()) FROM books_borrowed WHERE id = @id)
-- Set isReturned to true
UPDATE books_borrowed SET isReturned = 1 , returndate = GETDATE() WHERE id = @id
-- Set condition to LOST
UPDATE bookcopies SET condition = ' LOST ' ,isavailable = 0 , isactive = 0 WHERE copy_id = @copy_id
DECLARE @i INT = ( SELECT MAX (id) + 1 FROM bookcopy_history)
-- Insert note to book history table
INSERT INTO bookcopy_history
VALUES
(
ISNULL(@i, 1 ),
@copy_id,
' Book with id: ' + @copy_id + ' has been lost by user with cardid: ' + @card_id,
GETDATE()
)
DECLARE @pr DECIMAL ( 6 , 2 )
IF(@daysElapsed > 14 )
BEGIN
DECLARE @typeId INT = ( SELECT tc . title_type_id FROM bookcopies bc JOIN titlecategory tc ON tc . title_id = bc . title_id WHERE copy_id = @copy_id)
-- Check if Juvenile
IF(@typeId = 4 )
BEGIN
SET @pr = . 05
END
ELSE
BEGIN
SET @pr = . 10
END
-- Update card balancedue
UPDATE borrowers SET balancedue = balancedue + (@pr * @daysElapsed) WHERE card_id = @card_id
END
PRINT( ' Book Has Been Sucessfully discarded as LOST. ' )
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
PRINT( ' An Error Occured During The Transaction. Error SP: ' + ERROR_PROCEDURE() + ' Error line: ' + CAST(ERROR_LINE() AS VARCHAR ))
PRINT(ERROR_MESSAGE())
END CATCH
END В этой абонементной библиотечной системе есть несколько филиалов. Для каждого филиала сохраните branch id , name , address , telephone number , fax number , head librarian . В филиале может работать несколько библиотекарей, но только один главный библиотекарь . Для каждого библиотекаря сохраните employee id , name , address , telephone number , salary и cell phone number . Библиотекарь может быть закреплен только за одним филиалом. В филиалах работают разные сотрудники. Некоторые типы: Librarian , Network Administrator , Computer Programmer , IT Manager , Floor Manager , Custodian , Accountant , Data Analyst . Библиотекари должны иметь высшее библиотечное образование. Для каждого сотрудника в библиотеке хранятся name , address , phone number , birthdate , hire date и type of employee . Для библиотекарей библиотека также сохраняет информацию о том, когда библиотекарь получил свою степень , и о школе, в которой библиотекарь получил эту степень. Библиотека поддерживает два типа оплаты: salaried и hourly . Наемные сотрудники получают годовую зарплату, которая выплачивается 12 платежами первого числа каждого месяца . Канцелярские работники получают почасовую оплату . Всем работникам предоставляется vacation time в зависимости от стажа работы . Минимальная продолжительность отпуска составляет две недели . Библиотека ведет журнал, в котором указывается, сколько часов каждый канцелярский сотрудник отработал в течение каждой недели, в течение которой он работал . Этот журнал используется для выдачи paychecks конторскому персоналу в конце каждой недели. Библиотекари зарабатывают от 20 000 до 70 000 в год. Ставка оплаты труда почасовых работников должна быть не менее 15,00 . Библиотекаря нельзя нанять, пока он не получит степень магистра библиотековедения .
Триггер для обеспечения ограничений таблицы Employees
CREATE TRIGGER [dbo].[CheckEmployees]
ON [dbo].[employees]
INSTEAD OF INSERT
AS
BEGIN
DECLARE @branchId INT = ( SELECT branch_id FROM inserted)
DECLARE @isHead BIT = ( SELECT ishead_librarian FROM inserted)
DECLARE @empTypeId INT = ( SELECT employee_type_id FROM inserted)
DECLARE @salaryType CHAR ( 1 ) = ( SELECT salary_type FROM inserted)
DECLARE @salary DECIMAL ( 6 , 0 ) = ( SELECT salary FROM inserted)
DECLARE @degreeid INT = ( SELECT degree_id FROM inserted)
-- phone can be used as unique identifier
DECLARE @phone VARCHAR ( 12 ) = ( SELECT cellphone FROM inserted)
IF(@isHead = 1 )
BEGIN
-- Check if head librarian exists for the branch
IF EXISTS( SELECT TOP 1 1 FROM employees WHERE branch_id = @branchId AND ishead_librarian = 1 )
BEGIN
RAISERROR( ' There is already a head librarian on this branch ' , 10 , 1 )
ROLLBACK
END
-- Check if employee is of type librarian
IF(@empTypeId <> 1 )
BEGIN
RAISERROR( ' Only librarians can be head librarian ' , 10 , 1 )
ROLLBACK
END
END
-- Check if librarian exists
IF EXISTS( SELECT TOP 1 1 FROM employees WHERE cellphone = @phone AND isActive = 1 )
BEGIN
RAISERROR( ' Employee is already assigned to ' , 10 , 1 )
ROLLBACK
END
-- Check if librarian exists
IF(@salaryType = ' C ' AND @salary < 15 )
BEGIN
RAISERROR( ' Minimum hourly pay for clerical employees must be greater than 15 dollars. ' , 10 , 1 )
ROLLBACK
END
IF(@empTypeId = 1 AND (@salary < 20000 OR @salary > 70000 ))
BEGIN
RAISERROR( ' Salary for librarians must be between 20000 and 70000 ' , 10 , 1 )
ROLLBACK
END
IF(@empTypeId = 1 AND @degreeid <> 2 )
BEGIN
RAISERROR( ' Librarian must have earned MS degree in Library Sciences. ' , 10 , 1 )
ROLLBACK
END
PRINT( ' Employee has been sucessfully added. ' )
END
GO
ALTER TABLE [dbo].[employees] ENABLE TRIGGER [CheckEmployees]
Хранимая процедура для ежегодного обновления Vacation Hours сотрудников
CREATE PROCEDURE [dbo].[USP_UpdateEmployeeVacationHours]
AS
BEGIN
UPDATE employees
SET vacation_hours = CASE
WHEN DATEDIFF(YY,hiredate,GETDATE()) BETWEEN 0 AND 5 THEN 114
WHEN DATEDIFF(YY,hiredate,GETDATE()) BETWEEN 5 AND 10 THEN 154
ELSE 200
END
FROM employees
ENDНазовите книгу, которую чаще всего берут напрокат. (А именно, его чаще всего заимствовали несколько раз)
SELECT t . title
, COUNT (id) AS [Borrowed Count]
FROM books_borrowed bb
JOIN bookcopies bc ON bc . copy_id = bb . copy_id
JOIN titles t ON t . title_id = bc . title_id
GROUP BY t . title
HAVING COUNT (id) >= ALL
(
SELECT COUNT (id) AS [Borrowed Count]
FROM books_borrowed bb
JOIN bookcopies bc ON bc . copy_id = bb . copy_id
JOIN titles t ON t . title_id = bc . title_id
GROUP BY t . title
)Взятые книги с общим количеством
SELECT t . title
, COUNT (id) AS [Borrowed Count]
FROM books_borrowed bb
JOIN bookcopies bc ON bc . copy_id = bb . copy_id
JOIN titles t ON t . title_id = bc . title_id
GROUP BY t . title
ORDER BY [Borrowed Count] DESCКакой библиотекарь имеет третью по величине зарплату в настоящее время?
SELECT *
FROM
(
SELECT e . employee_id
,first_name + ' ' + last_name AS [Full Name]
,salary
,ROW_NUMBER() OVER( ORDER BY salary DESC ) AS RowNo
FROM employees e
WHERE isActive = 1 AND e . employee_type_id =
(
SELECT et . id
FROM employee_type et
WHERE [type] = ' Librarian '
)
) subQ
WHERE subQ . RowNo = 3Для каждого сотрудника укажите его/ее имя и название филиала, в котором он/она в настоящее время работает, а также количество сотрудников, работающих в этом филиале.
SELECT e . employee_id
, e . first_name + ' ' + e . last_name AS [Fullname]
, b . name
, COUNT ( e . employee_id ) OVER (PARTITION BY b . branch_id ) AS [Branch Employee Count]
FROM employees e
JOIN branchs b ON e . branch_id = b . branch_id
ORDER BY [Branch Employee Count] DESCДля каждой книги укажите название и издателя книги, а также количество экземпляров этой книги, имеющихся в настоящее время в каждом филиале, независимо от того, находится ли она в настоящее время во временном пользовании.
SELECT t . title
, t . title_id
, p . pub_name
, bc . branch_id
, COUNT ( bc . title_id ) AS [Copy Count]
FROM titles t
JOIN publishers p ON t . pub_id = p . pub_id
JOIN bookcopies bc ON bc . title_id = t . title_id
GROUP BY t . title
, t . title_id
, p . pub_name
, bc . branch_idЗа каждый квартал текущего года по каждому филиалу перечислите общее количество книг, взятых в кредит за этот квартал. Первый квартал — это месяцы январь, февраль, март. Второй квартал — это месяцы апрель, май, июнь и т. д. Перечислите суммы за каждый из этих кварталов в одной строке.
SELECT b . name
,(
SELECT COUNT ( bb . copy_id ) AS [Total]
FROM bookcopies bc
JOIN books_borrowed bb ON bb . copy_id = bc . copy_id
WHERE MONTH( bb . borroweddate ) IN ( 1 , 2 , 3 ) AND b . branch_id = bc . branch_id
) AS [Count Of First Quarter]
,(
SELECT COUNT ( bb . copy_id ) AS [Total]
FROM bookcopies bc
JOIN books_borrowed bb ON bb . copy_id = bc . copy_id
WHERE MONTH( bb . borroweddate ) IN ( 4 , 5 , 6 ) AND b . branch_id = bc . branch_id
) AS [Count Of Second Quarter]
,(
SELECT COUNT ( bb . copy_id ) AS [Total]
FROM bookcopies bc
JOIN books_borrowed bb ON bb . copy_id = bc . copy_id
WHERE MONTH( bb . borroweddate ) IN ( 7 , 8 , 9 ) AND b . branch_id = bc . branch_id
) AS [Count Of Third Quarter]
,(
SELECT COUNT ( bb . copy_id ) AS [Total]
FROM bookcopies bc
JOIN books_borrowed bb ON bb . copy_id = bc . copy_id
WHERE MONTH( bb . borroweddate ) IN ( 10 , 11 , 12 ) AND b . branch_id = bc . branch_id
) AS [Count Of Fourth Quarter]
FROM branchs bДля каждой карты укажите имя заемщика и названия книг, которые он в настоящее время одолжил на карте, но еще не вернул.
SELECT b . first_name + ' ' + b . last_name AS [Borrower Fullname]
, t . title
FROM borrowers b
JOIN books_borrowed bb ON b . card_id = bb . card_id
JOIN bookcopies bc ON bc . copy_id = bb . copy_id
JOIN titles t ON t . title_id = bc . title_id
WHERE bb . isReturned = 0
ORDER BY [Borrower Fullname]Для каждой карты укажите имя заемщика и в той же строке количество книг, которые были взяты взаймы в 2020 году, и количество книг, которые были взяты взаймы в 2021 году с помощью этой карты.
SELECT b . first_name + ' ' + b . last_name AS [Borrower Fullname]
,(
SELECT COUNT ( bb . id )
FROM books_borrowed bb
WHERE YEAR( bb . borroweddate ) = 2020 AND b . card_id = bb . card_id
) AS [Borrowed2020]
,(
SELECT COUNT ( bb . id )
FROM books_borrowed bb
WHERE YEAR( bb . borroweddate ) = 2021 AND b . card_id = bb . card_id
) AS [Borrowed2021]
,(
SELECT COUNT ( bb . id )
FROM books_borrowed bb
WHERE YEAR( bb . borroweddate ) = 2022 AND b . card_id = bb . card_id
) AS [Borrowed2022]
FROM borrowers b Для конкретной карты укажите, какие другие карты одолжили ВСЕ те же книги, которые были заимствованы с помощью этой карты. (разделить запрос). Вы выбираете карту, которая вам подойдет.
SELECT AllBooks . card_id
FROM
(
SELECT DISTINCT bb . card_id
,title
FROM books_borrowed bb
JOIN bookcopies bc ON bc . copy_id = bb . copy_id
JOIN titles t ON t . title_id = bc . title_id
) AS AllBooks
JOIN
(
SELECT DISTINCT bb . card_id
,title
FROM books_borrowed bb
JOIN bookcopies bc ON bc . copy_id = bb . copy_id
JOIN titles t ON t . title_id = bc . title_id
WHERE bb . card_id = 2
) AS Card1Books ON Card1Books . title = AllBooks . title
GROUP BY AllBooks . card_id
HAVING COUNT ( AllBooks . title ) = (
SELECT COUNT (title)
FROM books_borrowed bb
JOIN bookcopies bc ON bc . copy_id = bb . copy_id
JOIN titles t ON t . title_id = bc . title_id
WHERE bb . card_id = 2
)Укажите имя сотрудника, который проработал в библиотеке дольше всего.
SELECT e . first_name + ' ' + e . last_name AS [Employee Fullname]
, e . hiredate
,DATEDIFF(YY, e . hiredate ,GETDATE()) AS [Years of Employment]
FROM employees e
WHERE e . hiredate = ( SELECT MIN (hiredate) FROM employees)Для каждой книги укажите название и ветвь, в которой она находится, если она в настоящее время не взята во временное пользование.
SELECT t . title
, b . name
FROM titles t
JOIN bookcopies bc ON t . title_id = bc . title_id
LEFT JOIN books_borrowed bb ON bb . copy_id = bc . copy_id
JOIN branchs b ON b . branch_id = bc . branch_id
WHERE bc . isactive = 1 AND isavailable = 1Перечислите имена заемщиков и идентификатор карты, которая у них есть, если срок ее действия еще не истек.
SELECT b . first_name + ' ' + b . last_name AS [Borrower]
, b . card_id
FROM borrowers b
WHERE isexpired = 0Для каждого автора укажите его имя и названия написанных им книг.
SELECT DISTINCT a . au_fname + ' ' + a . au_lname AS [Author]
, t . title
FROM authors a
JOIN titleauthor ta On ta . au_id = a . au_id
JOIN titles t ON t . title_id = ta . title_idДля каждого автора укажите его имя и названия категорий написанных им книг.
SELECT DISTINCT a . au_fname + ' ' + a . au_lname AS [Author]
,c.[type]
FROM authors a
JOIN titleauthor ta On ta . au_id = a . au_id
JOIN titles t ON t . title_id = ta . title_id
JOIN titlecategory tc ON tc . title_id = t . title_id
JOIN category c ON c . type_id = tc . title_type_idДля каждого сотрудника рассчитайте сумму денег, которую он должен был заработать в этом году, исходя из отработанных им часов.
SELECT subQ . employee_id
,subQ.[Hours Total] * e . salary
FROM
(
SELECT e . employee_id
, SUM (sl.[hours]) AS [Hours Total]
FROM employees e
JOIN shift_logs sl ON e . employee_id = sl . employee_id
WHERE YEAR( sl . shiftdate ) = YEAR(GETDATE()) AND e . salary_type = ' C '
GROUP BY e . employee_id
) subQ
JOIN employees e ON e . employee_id = subQ . employee_idПеречислите названия книг, которые никогда не брали напрокат.
SELECT t . title
FROM titles t
WHERE t . title_id NOT IN
(
SELECT DISTINCT t . title_id
FROM borrowers b
JOIN books_borrowed bb ON b . card_id = bb . card_id
JOIN bookcopies bc ON bc . copy_id = bb . copy_id
JOIN titles t ON t . title_id = bc . title_id
)Для каждой отрасли укажите общее количество книг, общее количество по категориям, общее количество по авторам.
SELECT b . name
,(
SELECT COUNT ( bc . copy_id )
FROM bookcopies bc
WHERE bc . branch_id = b . branch_id
) AS [Book Count]
,(
SELECT COUNT (DISTINCT tc . title_type_id )
FROM bookcopies bc
JOIN titles t ON t . title_id = bc . title_id
JOIN titlecategory tc ON tc . title_id = t . title_id
WHERE bc . branch_id = b . branch_id
) AS [Category Count]
,(
SELECT COUNT (DISTINCT ta . au_id )
FROM bookcopies bc
JOIN titles t ON t . title_id = bc . title_id
JOIN titleauthor ta On t . title_id = ta . title_id
WHERE bc . branch_id = b . branch_id
) AS [Category Count]
FROM branchs b Для каждой карты укажите имя владельца карты, укажите категорию книг и каждую книгу, которая была одолжена по этой карте. В том же списке запросов указано, сколько книг было одолжено по каждой категории и сколько всего книг было одолжено с помощью этой карты.
WITH CTE
AS
(
SELECT b . first_name + ' ' + b . last_name AS [Borrower]
, t . title
,c.[type]
FROM borrowers b
JOIN books_borrowed bb ON b . card_id = bb . card_id
JOIN bookcopies bc ON bc . copy_id = bb . copy_id
JOIN titles t ON t . title_id = bc . title_id
JOIN titlecategory tc ON tc . title_id = t . title_id
JOIN category c ON c . type_id = tc . title_type_id
)
SELECT Borrower
,title
,[type]
, COUNT ( c . title ) OVER (PARTITION BY Borrower,TYPE) AS [Category Count]
, COUNT ( c . title ) [Total Books Borrowed]
FROM CTE c
GROUP BY Borrower
,title
,[type]
ORDER BY [Borrower]В одной строке укажите, сколько сотрудников в настоящее время работает для каждого типа сотрудников.
SELECT et.[type]
, COUNT ( e . employee_id ) AS [Employee Count]
FROM employees e
JOIN employee_type et ON e . employee_type_id = et . id
WHERE e . isActive = 1
GROUP BY et.[type]
ORDER BY [Employee Count] DESC Как зовут заемщика, который на данный момент взял больше всего книг?
SELECT b . first_name + ' ' + b . last_name AS [Borrower]
FROM borrowers b
JOIN books_borrowed bb ON bb . card_id = b . card_id
GROUP BY b . first_name + ' ' + b . last_name
HAVING COUNT ( bb . copy_id ) >= ALL
(
SELECT COUNT (copy_id)
FROM books_borrowed
GROUP BY card_id
) Перечислите имена заемщиков, которые одолжили как книгу А, так и книгу Б (вы можете выбрать конкретные названия книг).
SELECT DISTINCT b . first_name + ' ' + b . last_name AS [Borrower]
FROM titles t
JOIN bookcopies bc ON t . title_id = bc . title_id
JOIN books_borrowed bb ON bb . copy_id = bc . copy_id
JOIN borrowers b ON b . card_id = bb . card_id
WHERE t . title_id = ' UX2157 '
INTERSECT
SELECT DISTINCT b . first_name + ' ' + b . last_name AS [Borrower]
FROM titles t
JOIN bookcopies bc ON t . title_id = bc . title_id
JOIN books_borrowed bb ON bb . copy_id = bc . copy_id
JOIN borrowers b ON b . card_id = bb . card_id
WHERE t . title_id = ' BR5671 ' Ссылка на весь файл сценариев Pubs DB: Просмотр файла.
Ссылка на весь файл BAK Pubs DB: Посмотреть файл
Простое внешнее приложение с пользовательским интерфейсом для приложения просмотра базы данных пабов
Нуры Аманмадов - @amanmadov - [email protected]
Ссылка на проект: https://github.com/amanmadov/msin616-final-project.
(вернуться к началу)
