msin616 final project
1.0.0
Se trata de una base de datos sencilla que se puede utilizar para mantener los datos almacenados y procesados por una biblioteca de préstamo . La base de datos ha sido diseñada modificando la base de datos Pubs de Microsoft. La base de datos Pubs proporciona un conjunto de información ficticia sobre editoriales , autores , títulos y ventas de libros. Actualmente se considera obsoleto ; Ya no se proporciona con SQL Server desde la versión 2008 . Las tablas y los campos son bastante obvios. Tienen nombres como Authors , Titles , etc., que reflejan su contenido. Y los campos también tienen nombres obvios que explican lo que contienen.
Puede encontrar los detalles sobre Pubs DB original en el enlace: Microsoft Pubs DB
A continuación puede ver el diagrama de base de datos modificado de la base de datos Pubs .
Enlace para el esquema de la base de datos: Esquema de base de datos
Microsoft SQL Server 2019 - 15.0.4198.2 Edición para desarrolladores (64 bits) en Linux (Ubuntu 20.04.3 LTS)Azure Data Studio versión 1.35.1Docker 4.4.2 (73305) Gentellela , un panel de administración de código abierto, para crear una interfaz de usuario. [TITLES] y se agregó la columna prequel_id para almacenar la precuela de un libro. [TITLES] . [TITLES] y se agregó la columna ISBN . [TITLES] . [Audit].[Book] para mantener el historial de auditoría de los libros creados. [Adventureworks].[Person].[Person] y [AdventureWorks].[HumanResources].[Employee] para generar datos ficticios. Jobs ha sido eliminada. Roysched ha sido eliminada Employees ha sido modificada. Employee_type Category TitleCategory Degrees Schools Branchs Shift_logs Paychecks Borrowers Bookcopies Bookcopy_history Books_borrowed 
Enlace para el módulo de interfaz de usuario: Ver demostración
Diagrama de flujo del algoritmo de la operación de creación de un libro.
Procedimiento almacenado para agregar un libro a la tabla 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] es un poco complejo porque no crea solo un libro. Si no hay ningún editor o autor asociado con ese nuevo libro, también ayuda al usuario a crearlos. Es por eso que los parámetros @pub_id y @author_id del procedimiento almacenado [dbo].[USP_InsertBook] son opcionales. Si el usuario selecciona un editor o autor existente en el elemento del menú desplegable de la aplicación de interfaz de usuario, el valor seleccionado de la opción seleccionada, en formato @pub_id o @author_id , se pasa al procedimiento almacenado. Si no se encuentra un autor o editor para el nuevo libro en el menú desplegable (o base de datos), una aplicación facilita su creación. Usando las últimas opciones Create New Author... y Create New Publisher... en el menú desplegable, un usuario puede crear un nuevo autor o editor. También un libro puede tener precuela o no. Entonces, considerando todo esto, existen 4 escenarios posibles para crear un libro.
Nota: Los elementos del menú desplegable se completan con los autores y editores existentes en una base de datos.
Estos casos de prueba son:
+ 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
Caso de prueba 1: crear un libro para el autor y el editor existentes
-- 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. 'Caso de prueba 4: Crear un libro para un autor y un editor inexistentes
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... 'Caso de prueba 2: crear un libro para un autor existente y un editor inexistente
-- 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... '
Caso de prueba 3: crear un libro para un autor inexistente y un editor existente
-- 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... '
Enlace para el módulo de interfaz de usuario: Ver demostración
Procedimiento almacenado para agregar un autor a la tabla de 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 Dado que la creación de un author_id se utiliza en múltiples procedimientos almacenados, creé una función que crea 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;
Enlace para el módulo de interfaz de usuario: Ver demostración
Procedimiento almacenado para agregar un CoAuthor . Si el coautor no existe en el menú desplegable podemos crear un nuevo autor y agregarlo.
-- 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
Enlace para el módulo de interfaz de usuario: Ver demostración
Procedimiento almacenado para agregar un empleado a la tabla 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 
Enlace para el módulo de interfaz de usuario: Ver demostración
Procedimiento almacenado para obtener books en forma de 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
Enlace para el módulo de interfaz de usuario: Ver demostración
Procedimiento almacenado para obtener authors en forma de 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
Enlace para el módulo de interfaz de usuario: Ver demostración
Procedimiento almacenado para obtener publishers en forma de 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
Enlace para el módulo de interfaz de usuario: Ver demostración
Aquí, en el front-end, desarrollé algo de lógica con javascript para simular la funcionalidad del procedimiento almacenado. Este es un procedimiento almacenado Recursive para obtener libros precuelas de un libro específico de una serie. CTE usado dentro de la consulta.
/*
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 Ahora, si ejecutamos este procedimiento almacenado con un title_id de un libro que continúa en una serie, obtendremos los libros precuelas de ese libro.
-- 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 Ahora, si ejecutamos este procedimiento almacenado con un title_id de un libro de una serie, obtendremos los libros que continúan de esa serie.
-- 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 ' 
Procedimiento almacenado para eliminar un libro.
/*
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]Enlace para el archivo de registros [Auditoría].[Libro]: Ver registros
La biblioteca emite tarjetas de biblioteca a las personas que desean tomar prestados libros de la biblioteca. La biblioteca mantiene una lista de cada prestatario almacenando la card id , borrower name , address , phone number , birthdate , date the card was issued y balance due . Una tarjeta caduca a los diez años desde el momento de su emisión. Si una persona tiene menos de 18 años , la biblioteca también conservará información sobre el padre o tutor legal del Prestatario, como name , address y phone number . Una persona sólo puede tener una tarjeta de biblioteca válida a la vez. A una persona no se le puede emitir una nueva tarjeta de biblioteca si debe dinero por una tarjeta vencida .
/*
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
ENDProcedimiento almacenado para tarjetas que caducan después de 10 años
CREATE PROCEDURE [dbo].[USP_ExpireLibraryCards]
AS
BEGIN
UPDATE borrowers SET isexpired = 1
WHERE DATEDIFF(YY,card_issuedate,GETDATE()) >= 10
ENDSe crearon datos ficticios del prestatario mediante el siguiente procedimiento almacenado.
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. ' )
ENDFunciones definidas por el usuario para crear datos ficticios necesarios
-- 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; Para cada copia de libro prestada, la biblioteca realiza un seguimiento de la copy id y el card number de la persona que la tomó prestada. La biblioteca realiza un seguimiento de la date on which it was borrowed y registra la fecha de vencimiento, que es dos semanas después de la fecha del préstamo. Cuando se devuelve la copia, este registro se actualiza con la return date . Cuando se toma prestada una copia de un libro, la copia se marca como BORROWED . Cuando se devuelve la copia del libro, la copia se marca como AVAILABLE o NOT BORROWED . Un prestatario no puede pedir prestado un libro a una sucursal en particular a menos que esa sucursal tenga una copia de ese libro y esté actualmente en stock (por ejemplo, no haya sido prestado por otra persona). Cuando un prestatario devuelve una copia del libro después de la fecha de vencimiento, el sistema calcula el amount owed y cualquier cargo vencido incurrido se agrega al card balance . Un prestatario no puede utilizar una tarjeta para pedir prestados libros si debe más de 10 dólares en esa tarjeta. La biblioteca tiene una lista de cargos vencidos. Los cargos son actualmente de 0,05 por día para libros juveniles y 0,10 por día para libros para adultos. Cuando un libro se devuelve tarde, el prestatario paga los cargos que están vigentes en el momento de la devolución del libro. No se podrá tomar prestado ningún elemento de lectura que esté categorizado como reference .
Procedimiento almacenado para operación de préstamo
/*
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 Procedimiento almacenado para devolver la operación.
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 Procedimiento almacenado para enumerar todos los libros disponibles para prestar
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 La Biblioteca asigna una Condition a cada ejemplar de libro. Los valores de condición de muestra pueden ser NEW , EXCELLENT , GOOD , WORN y POOR . Eventualmente las copias que estén en POOR estado serán descartadas y reemplazadas por copias nuevas. Un prestatario puede reconocer que ha perdido una copia de un libro. Si es así, la copia se marca como LOST y el costo del libro se agrega al saldo de la tarjeta. Con el tiempo, la copia puede eliminarse del inventario actual de copias de sucursales y almacenarse en un archivo histórico.
Se agregó VERIFICAR RESTRICCIÓN en la tabla Bookcopies para la columna de condición.
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 ' )) Procedimiento almacenado para desechar un libro en POOR estado
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 Procedimiento almacenado para descartar un libro 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 Hay varias sucursales dentro de este sistema de bibliotecas de préstamo. Para cada sucursal almacene la branch id , name , address , telephone number , fax number y head librarian . Una sucursal puede emplear varios bibliotecarios, pero sólo un bibliotecario jefe . Para cada bibliotecario, almacene la employee id , name , address , telephone number , salary y cell phone number . Se puede asignar un bibliotecario a una sola sucursal. Las sucursales tienen diferentes tipos de empleados. Algunos tipos son: Librarian , Network Administrator , Computer Programmer , IT Manager , Floor Manager , Custodian , Accountant , Data Analyst . Los bibliotecarios deben haber obtenido un título en biblioteconomía. Para cada empleado, la biblioteca mantiene name , address , phone number , birthdate , hire date y type of employee . Para los bibliotecarios, la biblioteca también mantiene cuándo el bibliotecario obtuvo su título y la escuela en la que obtuvo el título. La biblioteca admite dos tipos de tipos de pago: salaried y hourly . Los empleados asalariados ganan un salario anual que se paga en 12 pagos el primero de cada mes . Los empleados que son administrativos ganan un salario por hora . Todos los empleados disfrutan vacation time en función de su antigüedad en el servicio . El tiempo mínimo de vacaciones es de dos semanas . La biblioteca mantiene un registro de cuántas horas registró cada tipo de empleado administrativo durante cada semana que trabajó . Este registro se utiliza para generar paychecks para el personal administrativo al final de cada semana. Los bibliotecarios ganan entre 20.000 y 70.000 al año. La tarifa salarial para los trabajadores por horas debe ser de al menos 15,00 . No se puede contratar a un bibliotecario antes de haber obtenido una maestría en biblioteconomía .
Activador para garantizar restricciones en la tabla de 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]
Procedimiento almacenado para actualizar Vacation Hours anuales de los empleados
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
ENDIndique el título del libro que es el más popular para ser prestado. (Es decir, se ha tomado prestado con mayor frecuencia varias veces)
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
)Libros prestados con recuento 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] DESC¿Qué bibliotecario tiene el tercer salario más alto en este momento?
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 = 3Para cada empleado, indique su nombre y el nombre de la sucursal para la que trabaja actualmente y cuántos empleados trabajan para esa sucursal.
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] DESCPara cada libro, indique el título y la editorial del libro y la cantidad de copias actualmente almacenadas para este título en cada sucursal, independientemente de si está actualmente en préstamo.
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_idPara cada trimestre del año en curso, para cada sucursal indique la cantidad total de libros que se han prestado en ese trimestre. El primer trimestre son los meses de enero, febrero y marzo. El segundo trimestre son los meses de abril, mayo, junio, etc. Enumere las cantidades de cada uno de estos trimestres en la misma fila.
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 bPara cada tarjeta, indique el nombre del prestatario y el nombre de los libros que actualmente ha pedido prestados en la tarjeta y que aún no han sido devueltos.
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]Para cada tarjeta, indique el nombre del prestatario y en la misma fila la cantidad de libros que se tomaron prestados en 2020 y la cantidad de libros que se tomaron prestados en 2021 con esa tarjeta.
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 Para una tarjeta específica, indique qué otras tarjetas tomaron prestados TODOS los mismos libros que se tomaron prestados con esta tarjeta. (consulta dividida). Tú eliges la tarjeta que combinarás.
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
)Indique el nombre del empleado que ha estado trabajando para la biblioteca durante más tiempo.
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)Para cada libro, indique el título y la rama en la que se encuentra, si no está actualmente en préstamo.
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 = 1Enumere los nombres de los prestatarios y la identificación de la tarjeta que tienen si no ha caducado.
SELECT b . first_name + ' ' + b . last_name AS [Borrower]
, b . card_id
FROM borrowers b
WHERE isexpired = 0Para cada autor, indique su nombre y los títulos de los libros que ha escrito.
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_idPara cada autor, indique su nombre y el nombre de las categorías de libros que ha escrito.
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_idPara cada empleado, calcule la cantidad de dinero que debería haber ganado este año en función de las horas registradas.
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_idEnumere el título de los libros que nunca han sido prestados.
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
)Para cada sucursal, indique la cantidad total de libros, la cantidad total por categoría, la cantidad total por autor
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 Para cada tarjeta, indique el nombre del titular de la tarjeta, indique la categoría de libros y cada libro que se tomó prestado en esta tarjeta. En la misma consulta, enumere cuántos libros se han prestado para cada categoría y cuántos libros se han prestado en total con esta tarjeta.
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]En una fila, indique cuántos empleados están empleados actualmente para cada tipo de empleado.
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 ¿Cuál es el nombre del prestatario que actualmente ha pedido prestado la mayor cantidad de libros?
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
) Enumere los nombres de los prestatarios que han tomado prestados tanto el libro A como el libro B (puede elegir los títulos de los libros específicos).
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 ' Enlace para el archivo completo de scripts de Pubs DB: Ver archivo
Enlace para todo el archivo bak de Pubs DB: Ver archivo
Aplicación de interfaz de usuario simple para la aplicación Pubs Database View
Nury Amanmadov - @amanmadov - [email protected]
Enlace del proyecto: https://github.com/amanmadov/msin616-final-project
(volver arriba)
