$1.$ Which of the following is NOT a valid data type in SQL Server?

$($a$)$ int

$($b$)$ varchar

$($c$)$ decimal

$($d$)$ blob

$2.$ What is the purpose of a primary key?

$($a$)$ To enforce data integrity

$($b$)$ To improve query performance

$($c$)$ To store foreign key relationships

$($d$)$ All of the above

$3.$ What is the difference between a stored procedure and a trigger?

$($a$)$ Stored procedures are compiled, triggers are interpreted.

$($b$)$ Triggers fire automatically, stored procedures require manual execution.

$($c$)$ Stored procedures can modify data, triggers cannot.

$($d$)$ All of the above

Question $2$: Short Answer

Briefly answer the following questions.

$2\mathrm{.}1$ Explain $3$ benefits of using normalization in database design.

$2\mathrm{.}2$ Describe $2$ different types of joins used in SQL queries.

$2\mathrm{.}3$ What are $2$ potential consequences of unhandled concurrency conflicts in a database?

Question $3$: Practical

$3\mathrm{.}1$ Create a Database Schema: You are designing a database for a library management system. The

system needs to store information about books, authors, borrowers, and loans. Create a relational

database schema in SSMS for this system, including tables, columns, data types, primary keys, and

foreign keys. You are to populate the Database with at least $15$ records before you attempt to answer

the following questions.

$3\mathrm{.}2$ CRUD Operations: Using your library database schema from question $3\mathrm{.}1,$ perform the following CRUD

operations in SSMS:

$$ Create: Insert $5$ new records into the "Books" table with appropriate data.

$$ Read: Write a query to retrieve all books by a specific author.

$$ Update: Update the information for a specific book loan.

$$ Delete: Delete a book record that is no longer available in the library.

Question $4$: Practical

$4\mathrm{.}1$ Stored Procedures: Create a stored procedure in SSMS that calculates and returns the

overdue fines for all outstanding book loans.

$$ Define the input parameters for the stored procedure $($e$.$g$.,$ current date, overdue fine rate$).$

$$ Write the SQL code for calculating overdue fines for each outstanding loan, considering due

date, loan date, and fine rate.

$$ Implement logic to return the calculated fines, either as a result set or through output

parameters.

$4\mathrm{.}2$ Triggers and Transactions: Implement a trigger in SSMS that automatically updates the number of

available copies whenever a book is loaned or returned. Explain $2$ ways how you will ensure data integrity

using transactions within your trigger.

Query Optimization: Analyze the following query that retrieves information about books and their

authors:

SELECT B$.$title,

A$.$name

FROM Books B

INNER JOIN Authors A ON B$.$author$\_$id $=$

A$.$id;

$5\mathrm{.}1$ Identify potential performance bottlenecks in the query and suggest optimization techniques you

would implement.

Marks Allocation:

$$ Analysis: Identify and explain potential bottlenecks in the given query, such as missing

indexes, inefficient joins, or lack of filtering.

$$ Optimization Techniques: Suggest specific optimization techniques to address the

identified bottlenecks, including creating indexes, using appropriate join types, utilizing WHERE

clauses efficiently, etc.

$$ Cost Estimation: Explain how you would estimate the impact of your chosen

optimization techniques on query performance using tools like SQL Server Management Studio

query plans or estimated execution plans.

$5\mathrm{.}2$ Indexing and Statistics: Explain the benefits of using indexes and maintaining database statistics for

query performance improvement. Create an appropriate index on one of the tables in your library

database schema and explain how it will optimize related queries.

Marks Allocation:

$$ Benefits and Maintenance: Explain the benefits of using indexes and maintaining

database statistics for query performance improvement, covering aspects like faster data

retrieval, reduced I$/$O operations, and efficient query planning.

$$ Index Creation: Create an appropriate index on one of the tables in the library database

schema, identifying the most suitable columns and index type based on typical query patterns.

$$ Impact Explanation: Explain how the created index will optimize related queries,

providing specific examples of query scenarios where the index benefits would be most

significant.