457 – Library Membership & Resource Access System

* 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