msin616 final project
1.0.0
Ini adalah database sederhana yang dapat digunakan untuk memelihara data yang disimpan dan diproses oleh perpustakaan peminjaman . Basis data telah dirancang dengan memodifikasi basis data Pubs Microsoft. Basis data Pubs menyediakan serangkaian informasi fiktif tentang penerbit , penulis , judul , dan penjualan buku. Sekarang dianggap usang ; itu tidak lagi disediakan dengan SQL Server sejak versi 2008 . Tabel dan fieldnya cukup jelas. Mereka memiliki nama seperti Authors , Titles , dll., yang mencerminkan kontennya. Dan bidang tersebut juga memiliki nama yang jelas yang menjelaskan isinya.
Anda dapat menemukan detail tentang Pubs DB asli di tautan: Microsoft Pubs DB
Di bawah ini Anda dapat melihat diagram database yang diubah dari database Pubs
Tautan untuk skema database: Skema DB
Microsoft SQL Server 2019 - 15.0.4198.2 Edisi Pengembang (64-bit) di Linux (Ubuntu 20.04.3 LTS)Azure Data Studio versi 1.35.1Docker versi 4.4.2 (73305) Gentellela , panel admin sumber terbuka, telah digunakan untuk membuat UI front-end. [TITLES] telah diubah dan kolom prekuel_id telah ditambahkan untuk menyimpan prekuel sebuah buku. [TITLES] . [TITLES] telah diubah dan kolom ISBN telah ditambahkan. [TITLES] . [Audit].[Book] untuk menyimpan riwayat audit buku yang dibuat. [Adventureworks].[Person].[Person] dan [AdventureWorks].[HumanResources].[Employee] untuk menghasilkan data dummy. Jobs telah dihapus Roysched telah dijatuhkan Employees telah diubah Employee_type telah dibuat Category telah dibuat TitleCategory telah dibuat Degrees telah dibuat Schools telah dibuat Branchs telah dibuat Shift_logs telah dibuat Paychecks telah dibuat Borrowers telah dibuat Bookcopies telah dibuat Bookcopy_history telah dibuat Books_borrowed telah dibuat 
Tautan untuk modul ui front-end: Lihat Demo
Diagram alur algoritma operasi pembuatan buku
Prosedur tersimpan untuk menambahkan buku ke dalam tabel 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 Prosedur tersimpan [dbo].[USP_InsertBook] agak rumit karena tidak hanya membuat buku. Jika tidak ada penerbit atau penulis yang terkait dengan buku baru tersebut, hal ini juga membantu pengguna untuk membuatnya. Itu sebabnya parameter @pub_id dan @author_id dari prosedur tersimpan [dbo].[USP_InsertBook] bersifat opsional. Jika pengguna memilih penerbit atau penulis yang ada dari elemen menu tarik-turun di aplikasi front-end, nilai yang dipilih dari opsi yang dipilih, dalam bentuk @pub_id atau @author_id , diteruskan ke prosedur tersimpan. Jika penulis atau penerbit buku baru tidak ditemukan di menu tarik-turun (atau database), aplikasi akan memudahkan pembuatannya. Menggunakan opsi terakhir Create New Author... dan Create New Publisher... pada menu tarik-turun, pengguna dapat membuat penulis atau penerbit baru. Juga sebuah buku mungkin memiliki prekuel atau tidak. Jadi dengan mempertimbangkan semua ini, ada 4 kemungkinan skenario pembuatan buku.
Catatan: Item menu tarik-turun diisi dengan penulis dan penerbit yang ada dalam database.
Kasus Uji ini adalah:
+ 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
Uji Kasus 1: Buat Buku untuk Penulis dan Penerbit yang Ada
-- 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. 'Kasus Uji 4: Membuat Buku untuk Penulis yang Tidak Ada dan Penerbit yang Tidak Ada
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... 'Uji Kasus 2: Buat Buku untuk Penulis yang Ada dan Penerbit yang Tidak Ada
-- 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... '
Kasus Uji 3: Membuat Buku untuk Penulis yang Belum Ada dan Penerbit yang Sudah Ada
-- 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... '
Tautan untuk modul ui front-end: Lihat Demo
Prosedur tersimpan untuk menambahkan penulis ke dalam tabel 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 Karena pembuatan author_id digunakan dalam beberapa prosedur tersimpan, saya membuat fungsi yang membuat 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;
Tautan untuk modul ui front-end: Lihat Demo
Prosedur tersimpan untuk menambahkan CoAuthor . Jika rekan penulis tidak ada pada menu dropdown kita dapat membuat penulis baru dan menambahkannya.
-- 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
Tautan untuk modul ui front-end: Lihat Demo
Prosedur tersimpan untuk menambahkan karyawan ke tabel 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 
Tautan untuk modul ui front-end: Lihat Demo
Prosedur tersimpan untuk mendapatkan books seperti dalam bentuk 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
Tautan untuk modul ui front-end: Lihat Demo
Prosedur tersimpan untuk mendapatkan authors seperti dalam bentuk 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
Tautan untuk modul ui front-end: Lihat Demo
Prosedur tersimpan untuk mendapatkan publishers seperti dalam bentuk 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
Tautan untuk modul ui front-end: Lihat Demo
Di sini, di sisi front-end, saya mengembangkan beberapa logika dengan javascript untuk mensimulasikan fungsionalitas prosedur tersimpan. Ini adalah prosedur tersimpan Recursive untuk mendapatkan buku prekuel atau buku untuk buku tertentu dalam satu seri. Menggunakan CTE di dalam kueri.
/*
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 Sekarang jika kita menjalankan prosedur tersimpan ini dengan title_id dari buku lanjutan dalam sebuah seri, kita akan mendapatkan buku prekuel dari buku itu.
-- 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 Sekarang jika kita menjalankan prosedur tersimpan ini dengan title_id dari sebuah buku dalam sebuah seri, kita akan mendapatkan buku lanjutan dari seri tersebut.
-- 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 ' 
Prosedur tersimpan untuk menghapus buku
/*
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]Tautan untuk file catatan [Audit].[Buku]: Lihat Catatan
Perpustakaan menerbitkan kartu perpustakaan kepada masyarakat yang ingin meminjam buku di perpustakaan. Perpustakaan menyimpan daftar setiap peminjam dengan menyimpan card id , borrower name , address , phone number , birthdate , date the card was issued dan balance due . Sebuah kartu habis masa berlakunya sepuluh tahun sejak diterbitkan. Jika seseorang berusia kurang dari 18 tahun , maka perpustakaan juga akan menyimpan informasi tentang orang tua atau wali sah Peminjam seperti name , address dan phone number . Seseorang hanya dapat memiliki satu kartu perpustakaan yang sah pada waktu tertentu. Seseorang tidak dapat diberikan kartu perpustakaan baru , jika ia berhutang uang pada kartu yang sudah habis masa berlakunya .
/*
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
ENDProsedur tersimpan untuk kartu yang kedaluwarsa setelah 10 tahun
CREATE PROCEDURE [dbo].[USP_ExpireLibraryCards]
AS
BEGIN
UPDATE borrowers SET isexpired = 1
WHERE DATEDIFF(YY,card_issuedate,GETDATE()) >= 10
ENDMembuat data peminjam dummy menggunakan prosedur tersimpan di bawah ini.
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. ' )
ENDFungsi yang ditentukan pengguna untuk membuat data dummy yang diperlukan
-- 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; Untuk setiap salinan buku yang dipinjam, perpustakaan mencatat copy id dan card number orang yang meminjamnya. Perpustakaan mencatat date on which it was borrowed dan mencatat tanggal jatuh tempo yaitu dua minggu setelah tanggal peminjaman. Ketika salinan dikembalikan, catatan ini diperbarui dengan return date . Ketika salinan buku dipinjam, salinannya ditandai sebagai BORROWED . Ketika salinan buku dikembalikan, salinannya ditandai sebagai AVAILABLE atau NOT BORROWED . Peminjam tidak dapat meminjam buku dari cabang tertentu kecuali cabang tersebut mempunyai salinan buku tersebut dan saat ini masih tersedia (misalnya tidak sedang dipinjam orang lain) Apabila peminjam mengembalikan salinan buku setelah tanggal jatuh tempo yang dihitung oleh sistem amount owed dan biaya tunggakan yang timbul ditambahkan ke card balance . Seorang peminjam tidak dapat menggunakan kartunya untuk meminjam buku, jika dia berhutang lebih dari 10 dolar pada kartu itu. Perpustakaan memiliki daftar biaya yang telah jatuh tempo. Biayanya saat ini adalah 0,05 setiap hari untuk buku remaja dan 0,10 per hari untuk buku dewasa. Jika buku terlambat dikembalikan, peminjam membayar biaya yang berlaku pada saat buku dikembalikan. Bahan bacaan apa pun yang termasuk dalam kategori reference tidak boleh dipinjam.
Prosedur tersimpan untuk operasi peminjaman
/*
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 Prosedur tersimpan untuk mengembalikan operasi
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 Prosedur tersimpan untuk mendaftar semua buku yang tersedia untuk dipinjam
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 Perpustakaan memberikan Condition untuk setiap salinan buku. Nilai kondisi sampel dapat berupa NEW , EXCELLENT , GOOD , WORN dan POOR . Pada akhirnya salinan yang kondisinya POOR akan dibuang dan diganti dengan salinan baru. Seorang peminjam dapat mengakui bahwa dia telah kehilangan salinan bukunya. Jika ya, salinannya ditandai LOST dan biaya buku ditambahkan ke saldo kartu. Akhirnya salinan tersebut dapat dihapus dari inventaris salinan cabang saat ini dan disimpan dalam file riwayat.
Menambahkan CHECK CONSTRAINT pada tabel Bookcopies untuk kolom kondisi
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 ' )) Prosedur tersimpan untuk membuang buku dalam kondisi 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 Prosedur tersimpan untuk membuang buku 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 Ada beberapa cabang dalam sistem perpustakaan peminjaman ini. Untuk setiap cabang menyimpan branch id , name , address , telephone number , fax number , head librarian . Sebuah cabang mungkin mempekerjakan beberapa pustakawan, tetapi hanya satu kepala pustakawan . Untuk setiap pustakawan, simpan employee id , name , address , telephone number , salary dan cell phone number . Seorang pustakawan hanya dapat ditugaskan pada satu cabang. Cabang memiliki tipe karyawan yang berbeda-beda. Beberapa jenisnya adalah: Librarian , Network Administrator , Computer Programmer , IT Manager , Floor Manager , Custodian , Accountant , Data Analyst . Pustakawan harus memperoleh gelar di bidang ilmu perpustakaan. Untuk setiap karyawan, perpustakaan menyimpan name , address , phone number , birthdate , hire date dan type of employee . Bagi pustakawan, perpustakaan juga mengelola kapan pustakawan memperoleh gelarnya dan sekolah tempat pustakawan memperoleh gelar tersebut. Perpustakaan mendukung dua jenis PayTypes: salaried dan hourly . Karyawan yang digaji memperoleh gaji tahunan yang dibayarkan dalam 12 kali pembayaran pada tanggal pertama setiap bulannya . Karyawan yang klerikal, mendapat upah per jam . Semua karyawan mendapat vacation time tergantung masa kerja mereka. Jumlah waktu liburan minimal adalah dua minggu . Perpustakaan menyimpan catatan berapa jam yang dicatat oleh masing-masing tipe karyawan klerikal selama setiap minggu dia bekerja . Log ini digunakan untuk menghasilkan paychecks bagi staf administrasi pada akhir setiap minggu. Pustakawan mendapat penghasilan antara 20.000 dan 70.000 per tahun. Tingkat gaji untuk pekerja per jam harus minimal pukul 15.00 . Seorang pustakawan tidak dapat dipekerjakan sebelum ia mendapatkan gelar MS di bidang Ilmu Perpustakaan .
Pemicu untuk memastikan pembatasan pada tabel 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]
Prosedur tersimpan untuk memperbarui Vacation Hours karyawan tahunan
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
ENDSebutkan judul buku yang merupakan buku terpopuler untuk dipinjam. (Yaitu, paling sering dipinjam beberapa kali)
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
)Buku pinjaman dengan jumlah total
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] DESCPustakawan mana yang memiliki gaji tertinggi ketiga saat ini?
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 = 3Untuk setiap pegawai, cantumkan namanya dan nama cabang dimana ia bekerja saat ini serta berapa jumlah pegawai yang bekerja pada cabang tersebut.
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] DESCUntuk setiap buku, cantumkan judul dan penerbit buku serta jumlah eksemplar yang saat ini tersedia untuk judul tersebut di setiap cabang, terlepas dari apakah buku tersebut sedang dipinjamkan.
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_idUntuk setiap kuartal pada tahun berjalan, untuk setiap cabang, cantumkan jumlah total buku yang telah dipinjam pada kuartal tersebut. Kuartal pertama adalah bulan Jan, Feb, Mar. Kuartal kedua adalah bulan Apr, Mei, Juni dst. Cantumkan jumlah untuk masing-masing kuartal ini, pada baris yang sama.
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 bPada setiap kartu, cantumkan nama peminjam dan nama buku yang sedang dipinjamnya pada kartu tersebut, yang belum dikembalikan.
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]Untuk setiap kartu, cantumkan nama peminjam dan pada baris yang sama jumlah buku yang dipinjam pada tahun 2020 dan jumlah buku yang dipinjam pada tahun 2021 dengan kartu tersebut.
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 Untuk kartu tertentu, daftarkan kartu lain mana yang meminjam SEMUA buku yang sama dengan yang dipinjam menggunakan kartu ini. (bagi kueri). Anda memilih kartu yang akan Anda cocokkan.
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
)Cantumkan nama pegawai yang paling lama bekerja di perpustakaan
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)Untuk setiap buku, cantumkan judul dan cabangnya, jika buku tersebut tidak sedang dipinjamkan
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 = 1Cantumkan nama peminjam dan id kartu yang dimilikinya jika belum habis masa berlakunya.
SELECT b . first_name + ' ' + b . last_name AS [Borrower]
, b . card_id
FROM borrowers b
WHERE isexpired = 0Untuk setiap penulis, cantumkan namanya dan judul buku yang telah ditulisnya
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_idUntuk setiap penulis, cantumkan namanya dan nama kategori buku yang telah ditulisnya
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_idUntuk setiap karyawan, hitung jumlah uang yang seharusnya dia peroleh tahun ini berdasarkan jam kerjanya.
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_idSebutkan judul buku yang belum pernah dipinjam.
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
)Untuk setiap cabang, cantumkan jumlah total buku, jumlah total berdasarkan kategori, jumlah total berdasarkan penulis
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 Untuk setiap kartu, cantumkan nama pemegang kartu, cantumkan kategori buku dan setiap buku yang dipinjam pada kartu tersebut. Dalam daftar kueri yang sama untuk berapa banyak buku yang telah dipinjam untuk setiap kategori, dan berapa total buku yang telah dipinjam dengan kartu ini.
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]Pada satu baris, cantumkan berapa banyak karyawan yang saat ini dipekerjakan untuk setiap jenis karyawan.
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 Siapa nama peminjam yang saat ini paling banyak meminjam buku.
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
) Cantumkan nama peminjam yang pernah meminjam buku A dan buku B (Anda dapat memilih judul buku tertentu).
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 ' Tautan untuk seluruh file skrip Pubs DB: Lihat File
Tautan untuk seluruh file bak Pubs DB: Lihat File
Aplikasi UI Front End Sederhana untuk Aplikasi Tampilan Database Pub
Nury Amanmadov - @amanmadov - [email protected]
Tautan Proyek: https://github.com/amanmadov/msin616-final-project
(kembali ke atas)
