msin616 final project
1.0.0
นี่เป็นฐานข้อมูลง่ายๆ ที่สามารถใช้เพื่อรักษาข้อมูลที่จัดเก็บและประมวลผลโดย ห้องสมุดให้ยืม ฐานข้อมูลได้รับการออกแบบโดยการแก้ไขฐานข้อมูล Pubs ของ Microsoft ฐานข้อมูล Pubs จัดเตรียมชุดข้อมูลที่สมมติขึ้นเกี่ยวกับ ผู้จัดพิมพ์ ผู้แต่ง ชื่อ หนังสือ และ การขาย หนังสือ ตอนนี้ถือว่า ล้าสมัยแล้ว ไม่มีให้กับ SQL Server อีกต่อไปตั้งแต่ เวอร์ชัน 2008 ตารางและเขตข้อมูลค่อนข้างชัดเจน พวกเขามีชื่อต่างๆ เช่น Authors , Titles ฯลฯ ซึ่งสะท้อนถึงเนื้อหาของพวกเขา และช่องต่างๆ ยังมีชื่อที่ชัดเจนซึ่งอธิบายสิ่งที่มีอยู่ด้วย
คุณสามารถดูรายละเอียดเกี่ยวกับ Pubs DB ดั้งเดิมได้จากลิงก์: Microsoft Pubs DB
ด้านล่างนี้ คุณสามารถดูไดอะแกรมฐานข้อมูลที่เปลี่ยนแปลงของฐานข้อมูล Pubs
ลิงก์สำหรับสคีมาฐานข้อมูล: DB Schema
Microsoft SQL Server 2019 - 15.0.4198.2 Developer Edition (64 บิต) บน Linux (Ubuntu 20.04.3 LTS)Azure Data Studio เวอร์ชัน 1.35.1Docker เวอร์ชัน 4.4.2 (73305) Gentellela ซึ่งเป็นแผงผู้ดูแลระบบโอเพ่นซอร์สถูกนำมาใช้เพื่อสร้าง UI ส่วนหน้า [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 แล้ว 
ลิงก์สำหรับโมดูล UI ส่วนหน้า: ดูการสาธิต
ผังขั้นตอนอัลกอริทึมของการดำเนินการสร้างสมุด
ขั้นตอนการจัดเก็บสำหรับการเพิ่มหนังสือลงในตาราง 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... '
ลิงก์สำหรับโมดูล UI ส่วนหน้า: ดูการสาธิต
ขั้นตอนการจัดเก็บสำหรับการเพิ่มผู้เขียนลงในตาราง 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;
ลิงก์สำหรับโมดูล UI ส่วนหน้า: ดูการสาธิต
ขั้นตอนการจัดเก็บสำหรับการเพิ่ม 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
ลิงก์สำหรับโมดูล UI ส่วนหน้า: ดูการสาธิต
ขั้นตอนการจัดเก็บสำหรับการเพิ่มพนักงานลงในตาราง 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 
ลิงก์สำหรับโมดูล UI ส่วนหน้า: ดูการสาธิต
ขั้นตอนการจัดเก็บการรับ 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
ลิงก์สำหรับโมดูล UI ส่วนหน้า: ดูการสาธิต
ขั้นตอนการจัดเก็บการรับ 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
ลิงก์สำหรับโมดูล UI ส่วนหน้า: ดูการสาธิต
ขั้นตอนการจัดเก็บเพื่อรับ 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
ลิงก์สำหรับโมดูล UI ส่วนหน้า: ดูการสาธิต
ที่ส่วนหน้า ฉันพัฒนาตรรกะบางอย่างด้วยจาวาสคริปต์เพื่อจำลองฟังก์ชันการทำงานของกระบวนงานที่เก็บไว้ นี่เป็นขั้นตอนการจัดเก็บ 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 ดอลลาร์ ห้องสมุดมีรายการค่าบริการที่ค้างชำระ ปัจจุบันค่าบริการอยู่ที่ .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 และค่าหนังสือจะถูกเพิ่มไปยังยอดคงเหลือของบัตร ในที่สุดสำเนาอาจถูกลบออกจากรายการปัจจุบันของสำเนาสาขาและเก็บไว้ในไฟล์ประวัติ
เพิ่ม CHECK CONSTRAINT ในตาราง 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 สำหรับบรรณารักษ์ ห้องสมุดยังรักษา เวลาที่บรรณารักษ์ได้รับปริญญา และ โรงเรียนที่บรรณารักษ์ได้รับปริญญา ห้องสมุดสนับสนุน PayTypes สองประเภท: 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 ทั้งหมด: ดูไฟล์
ลิงก์สำหรับไฟล์ Pubs DB bak ทั้งหมด: ดูไฟล์
แอป UI ส่วนหน้าอย่างง่ายสำหรับแอปดูฐานข้อมูล Pubs
นูรี อมานมาดอฟ - @amanmadov - [email protected]
ลิงค์โครงการ: https://github.com/amanmadov/msin616-final-project
(กลับไปด้านบน)
