457 – Library

Task Assignment Library Membership & Resource Access System

Project Overview

You are required to design and implement a complete database system for a university library in the Sultanate of Oman. The system must manage physical books, digital resources, and user access while ensuring data security and compliance with Omans Personal Data Protection Law (PDPL).

The solution must include:

• SQL Database (Relational)
• NoSQL Database (MongoDB)
• Security & Legal Analysis

Use Arabic names for members and staff throughout the system.

Part 1: SQL Database Implementation (50 Marks)

1. Database Design & Normalization

You must:

• Start with UNF (Unnormalized Form)
• Convert step-by-step into:
  • 1NF
  • 2NF
  • 3NF

Requirements:

• Clearly list all attributes
• Identify:
  • Primary Keys (PK)
  • Foreign Keys (FK)
• Explain functional dependencies
• Justify why tables are separated

Example Entities (use Arabic names):

• Members (e.g., Ahmed Al-Harthi, Fatma Al-Balushi)
• Books
• Borrowing Records
• Librarians
• Digital Resources

2. ERD Design

Create a Crows Foot ERD diagram that includes:

• All entities
• Attributes
• PK & FK
• Relationships
• Cardinalities

Ensure:

• No redundancy
• Clear structure

3. SQL Implementation (SSMS)

Write SQL code to:

• Create all tables
• Define:
  • PK, FK
  • Constraints
• Enforce referential integrity

Important:

• Add at least TWO CHECK constraints, for example:
  • Fine amount 0
  • Access duration > 0

4. Insert Sample Data

Insert realistic data using Omani Arabic names, such as:

• Ahmed Al-Harthi
• Salim Al-Rawahi
• Fatma Al-Balushi
• Aisha Al-Zahra

Include:

• Book records
• Borrowing transactions
• Digital access logs

5. Role-Based Access Control (RBAC)

Implement access rules:

Rules:

1. A student can only view their own borrowed books
2. Admin has full access
3. Others (e.g., librarians) have read-only access

Deliverables:

• SQL code for roles/permissions
• Show:
  • Allowed action
  • Denied action

6. SQL Queries

Write and test the following queries:

1. Retrieve the most borrowed books
2. Identify librarians with highest transactions
3. Find members who borrowed but never returned books
4. Identify members with above-average digital resource usage
5. Find digital resources with:
   • Avg usage > 30 minutes
   • Used by at least 3 members
   • Sort descending

Part 2: NoSQL Database (MongoDB) 25 Marks

1. Design NoSQL Model

• Create one collection (e.g., LibraryActivity)
• Store:
  • Member info
  • Borrowing records
  • Digital access logs

Use a document-based structure

2. Insert Data

Insert 5 realistic documents using Arabic names.

3. Queries

Implement:

1. Top 3 most borrowed books
2. Members who:
   • Accessed digital resources
   • Borrowed at least 2 books

4. Justification (Max 200 words)

Explain:

• Why NoSQL is suitable
• Focus on:
  • Flexibility
  • High-volume logs
  • Hybrid system (SQL + NoSQL)

Part 3: Security, Legal & Distributed Systems (25 Marks)

1. PDPL Compliance (Oman)

Analyze two data categories:

A. Library Members Data:

• Personal info
• Borrowing history

B. External Providers:

• Vendors
• Digital subscriptions

For EACH:

• Identify relevant PDPL principles
• Justify one database design decision
• Explain differences in compliance challenges

2. Security Risks

Identify TWO risks, for example:

• Unauthorized data access
• Data leakage

For EACH risk:

• Technical solution (e.g., encryption, access control)
• Organizational solution (e.g., policies, training)

3. CAP Theorem

Explain:

• Consistency
• Availability
• Partition Tolerance

Scenario:

If a branch library in Oman loses connection:

Decide:

• Prioritize Consistency OR Availability

Justify your answer logically.

General Instructions

• Use Microsoft Word (.docx)
• Include:
  • SQL code as TEXT (not screenshots)
  • Output screenshots
• Word count:
  • ~2000 words total
  • Activity 3 600 words
• References:
  • 46 academic sources (Harvard style)
• Ensure:
  • Plagiarism < 15%
  • AI detection = 0%

Expected Outcome

The final work should demonstrate:

• Strong database design
• Correct SQL implementation
• Proper NoSQL usage
• Understanding of Omans legal framework
• Awareness of security and distributed systems

WRITE MY PAPER