Question $1$

Project: Database Management System for a Fictional Online Bookstore

You are tasked with creating a database management system for a fictional online bookstore called "Bookworm Haven."

The database will store information about books, authors, customers, orders, and transactions. As part of this project, you

will design the database schema, create tables, populate the database with sample records, and perform various data

management tasks.

Database Schema: $($USE ANY DBMS$)$

$$ Books Table: Contains information about the books available in the bookstore.

$$ Columns: BookID $($Primary Key$),$ Title, AuthorID $($Foreign Key$),$ Genre, Price, PublicationYear.

$$ Authors Table: Stores details about the authors of the books.

$$ Columns: AuthorID $($Primary Key$),$ AuthorName, Nationality, BirthYear.

$$ Customers Table: Holds customer information for online orders.

$$ Columns: CustomerID $($Primary Key$),$ CustomerName, Email, Phone, Address.

$$ Orders Table: Records each customer's order details.

$$ Columns: OrderID $($Primary Key$),$ CustomerID $($Foreign Key$),$ OrderDate, TotalAmount.

$$ OrderItems Table: Stores the items $($books$)$ in each order.

$$ Columns: OrderItemID $($Primary Key$),$ OrderID $($Foreign Key$),$ BookID $($Foreign Key$),$ Quantity.

Sample Records:

Books Table:

ADI$632-$ Formative Assessment $1$ Semester $2$ Paper $2024|$ V$1\mathrm{.}0$

Authors Table:

Customers Table:

Orders Table:

Orderitems Table:

Questions:

Configure Data Access and Auditing:

$(15$ marks$)$ Explain the steps you would take to grant SELECT, INSERT, UPDATE, and DELETE permissions to a new

user "BookwormUser" for the Books and Authors tables. Include SQL commands and security considerations.

$(10$ marks$)$ Implement row$-$level security on the Customers' table, ensuring that each customer can only access

their information. Describe the security policy you would set up and the benefits of using row$-$level security in this

scenario.

ADI$632$ Formative Assessment $1$ Semester $2$ Paper $2024|$ V$1\mathrm{.}0$ Page $5$ of $5$

$(15$ marks$)$ Describe the process of enabling and configuring database$-$level auditing for the Orders and

OrderItems tables. Identify the events you would audit and explain how auditing helps in maintaining data

integrity and compliance.

Manage Backup and Restore of Databases:

$(20$ marks$)$ Design a backup strategy for the Bookworm Haven database to ensure data availability and disaster

recovery. Specify the backup types $($full$,$ differential, transaction log$),$ frequency, and retention policies.

$(20$ marks$)$ Implement a point$-$in$-$time restore for the Orders table to recover data to a specific timestamp. Provide

step$-$by$-$step instructions using T$-$SQL