KEMBAR78
Assignment 4 - DB | PDF | Database Index | Databases
Scribd
Upload
6 views3 pages

Assignment 4 - DB

The document outlines an assignment for a Database System course, focusing on designing a library database and addressing various scenarios related to database management. It includes tasks such as identifying anomalies, normalizing tables, creating an ERD, optimizing search indices, and understanding transaction properties in an online bookstore context. The assignment requires students to demonstrate their reasoning and steps in addressing these database-related questions.

Uploaded by

riazhaikal44
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
6 views3 pages

Assignment 4 - DB

The document outlines an assignment for a Database System course, focusing on designing a library database and addressing various scenarios related to database management. It includes tasks such as identifying anomalies, normalizing tables, creating an ERD, optimizing search indices, and understanding transaction properties in an online bookstore context. The assignment requires students to demonstrate their reasoning and steps in addressing these database-related questions.

Uploaded by

riazhaikal44
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 3

Total Marks: 4

Obtained Marks:

Database System
Assignment # 4

Section-B
Last date of Submission: 18th May, 2025

Submitted To:

Student Name:

Reg NO:
Instructions: Carefully read each scenario and answer the questions that follow. Show your
reasoning and steps where applicable.

Question 1: Library Database

A local library wants to design a database to manage its collection of books, members, and
borrowing activities. Initially, they came up with the following table structure:

1. Identify the potential anomalies (insertion, update, deletion) that might arise from this
table structure. Explain each anomaly with a specific example based on the data
provided.
2. Normalize this table into a set of tables in at least the Third Normal Form (3NF). Show
the steps involved, identify the primary keys and foreign keys in each table, and explain
why each step is necessary.
3. Draw an Entity-Relationship Diagram (ERD) representing the normalized database
schema.

Question 2: Library Database Search

A library has a database with tables for Books, Authors, and Borrowers. The Books
table contains information like BookID, Title, AuthorID, Genre, and
PublicationYear. The library wants to optimize searches for books.

1. If the library frequently searches for books by Title, would you recommend creating a
primary or secondary index on the Title field? Explain your reasoning.
2.
The library also wants to quickly retrieve all books written by a specific author. How would
you use an index to optimize this search? What type of index would be most suitable, and
why?
3.
4. Explain the difference between a clustered and unclustered index in this context. How
would each type of index affect the physical storage and retrieval of book records? 5.

Question 3: Online Bookstore Transactions

An online bookstore is processing customer orders. Each order involves multiple operations,
such as checking inventory, updating stock, calculating the order total, and recording
payment.

1. Describe what a transaction is in the context of processing a customer order. Why is it


important to treat an order as a transaction?
2.
3. Explain the ACID properties and how they apply to an online bookstore transaction. Use
examples from the order processing scenario to illustrate each property.
4.
5. The bookstore's database is accessed by multiple users simultaneously. Describe a
potential concurrency issue that could arise when processing multiple orders
concurrently and how it could lead to incorrect data.

You might also like