Course: Library and Database Technology (9213) Semester: Spring, 2024
Level: BS-LIS

Assignment No. 2
(Units 6–9)

Q1. Discuss programming the application, setting up the application, and implementing the data model with examples.
To thoroughly discuss the process of programming an application, setting it up, and implementing the data model, we will break down the process into three main sections:

Programming the Application
Setting Up the Application
Implementing the Data Model
Each section will be illustrated with examples to clarify the concepts.

1. Programming the Application
   Programming an application involves writing code to define the application’s behavior and functionality. This process typically includes:

Defining the application architecture
Writing code for the business logic
Creating user interfaces
Handling user input and interaction
Example: A Simple To-Do List Application

Let’s consider a simple To-Do List application using Python with the Flask web framework.

Application Structure:

bash
Copy code
/todo_app
/static
/templates
app.py
Code:

app.py:

python
Copy code
from flask import Flask, render_template, request, redirect, url_for

app = Flask(name)

In-memory database

todos = []

@app.route(‘/’)
def index():
return render_template(‘index.html’, todos=todos)

@app.route(‘/add’, methods=[‘POST’])
def add_todo():
todo = request.form[‘todo’]
todos.append(todo)
return redirect(url_for(‘index’))

@app.route(‘/delete/’)
def delete_todo(todo_id):
todos.pop(todo_id)
return redirect(url_for(‘index’))

if name == ‘main‘:
app.run(debug=True)
templates/index.html:

html
Copy code

To-Do List

To-Do List

Add

• {{ todo }} Delete

2. Setting Up the Application
   Setting up an application involves configuring the development environment, installing dependencies, and preparing the application for development and deployment.

Steps to Set Up the To-Do List Application:

Install Python and Flask:
Ensure you have Python installed. Then, install Flask using pip:

sh
Copy code
pip install flask
Create the Project Structure:
Create the directories and files as shown in the application structure.

Run the Application:
Run the Flask application using the command:

sh
Copy code
python app.py
Access the Application:
Open a web browser and navigate to http://127.0.0.1:5000/ to see the To-Do List application in action.

3. Implementing the Data Model
   Implementing the data model involves defining how data is stored, retrieved, and manipulated within the application. This can include defining database schemas, creating models, and setting up ORM (Object-Relational Mapping) if using a database.

Example: Enhancing the To-Do List Application with a Database

Let’s enhance our To-Do List application to use an SQLite database with SQLAlchemy.

Updated Application Structure:

bash
Copy code
/todo_app
/static
/templates
app.py
models.py
Code:

app.py:

python
Copy code
from flask import Flask, render_template, request, redirect, url_for
from flask_sqlalchemy import SQLAlchemy

app = Flask(name)
app.config[‘SQLALCHEMY_DATABASE_URI’] = ‘sqlite:///todos.db’
db = SQLAlchemy(app)

from models import Todo

@app.route(‘/’)
def index():
todos = Todo.query.all()
return render_template(‘index.html’, todos=todos)

@app.route(‘/add’, methods=[‘POST’])
def add_todo():
todo_text = request.form[‘todo’]
new_todo = Todo(text=todo_text)
db.session.add(new_todo)
db.session.commit()
return redirect(url_for(‘index’))

@app.route(‘/delete/’)
def delete_todo(todo_id):
todo = Todo.query.get(todo_id)
db.session.delete(todo)
db.session.commit()
return redirect(url_for(‘index’))

if name == ‘main‘:
db.create_all()
app.run(debug=True)
models.py:

python
Copy code
from app import db

class Todo(db.Model):
id = db.Column(db.Integer, primary_key=True)
text = db.Column(db.String(200), nullable=False)

def __repr__(self):
    return f'<Todo {self.id} - {self.text}>'

Setting Up the Enhanced Application:

Install SQLAlchemy:
Install Flask-SQLAlchemy using pip:

sh
Copy code
pip install flask-sqlalchemy
Create the Database:
The database will be created automatically when you run the application for the first time, thanks to the db.create_all() command in app.py.

Run the Enhanced Application:
Run the Flask application using the command:

sh
Copy code
python app.py
Access the Application:
Open a web browser and navigate to http://127.0.0.1:5000/ to see the enhanced To-Do List application with database integration.

Summary
In summary, programming an application involves writing code to define its behavior and functionality. Setting up the application includes configuring the development environment and installing dependencies. Implementing the data model involves defining how data is stored and managed within the application. The examples provided demonstrate these processes using a simple To-Do List application developed with Flask and SQLAlchemy.

Q2. Write a note on database security-related techniques with examples

Database Security-Related Techniques
Database security is essential for protecting sensitive information from unauthorized access, misuse, and breaches. Implementing robust security measures ensures data integrity, confidentiality, and availability. Below are some key database security-related techniques along with examples.

1. Authentication
   Authentication ensures that only authorized users can access the database. Strong authentication methods include:

Username and Password: Require strong, complex passwords.
Multi-Factor Authentication (MFA): Adds an extra layer of security by requiring a second form of verification (e.g., SMS code, biometric).
Example:

sql
Copy code
— Creating a user with a strong password
CREATE USER ‘secure_user’@’localhost’ IDENTIFIED BY ‘Str0ngP@ssw0rd!’;

2. Authorization and Access Control
   Authorization determines what authenticated users are allowed to do. Implementing role-based access control (RBAC) is an effective way to manage permissions.

Example:

sql
Copy code
— Creating roles and assigning privileges
CREATE ROLE read_only;
GRANT SELECT ON my_database.* TO ‘read_only’;

CREATE ROLE read_write;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_database.* TO ‘read_write’;

— Assigning roles to users
GRANT ‘read_only’ TO ‘user1’@’localhost’;
GRANT ‘read_write’ TO ‘user2’@’localhost’;

3. Encryption
   Encryption protects data by converting it into a format that can only be read by someone who has the decryption key. This includes:

Data-at-Rest Encryption: Encrypts stored data.
Data-in-Transit Encryption: Encrypts data transmitted over networks using protocols like TLS/SSL.
Example:

sql
Copy code
— Enabling SSL for MySQL
[mysqld]
ssl-ca=/path/to/ca-cert.pem
ssl-cert=/path/to/server-cert.pem
ssl-key=/path/to/server-key.pem

4. Backup and Recovery
   Regular backups and a solid recovery plan are crucial for mitigating the impact of data loss or corruption. This includes:

Automated Backups: Scheduled backups to secure locations.
Point-in-Time Recovery: Restoring the database to a specific moment before a failure occurred.
Example:

sh
Copy code

Backup using MySQL

mysqldump -u root -p my_database > my_database_backup.sql

Restore from backup

mysql -u root -p my_database < my_database_backup.sql

5. Auditing and Monitoring
   Continuous monitoring and auditing help detect and respond to suspicious activities. This includes:

Database Logs: Keeping logs of database activities.
Intrusion Detection Systems (IDS): Monitoring for unusual patterns that may indicate a security breach.
Example:

sql
Copy code
— Enabling general query log in MySQL
[mysqld]
general_log = 1
general_log_file = /var/log/mysql/general.log

6. Patch Management
   Regularly updating the database management system (DBMS) with security patches fixes vulnerabilities that could be exploited by attackers.

Example:

sh
Copy code

Updating MySQL on Ubuntu

sudo apt-get update
sudo apt-get install mysql-server

7. Database Hardening
   Database hardening involves configuring the database to minimize vulnerabilities. This includes:

Disabling Unused Features: Turning off services and features that are not in use.
Changing Default Settings: Modifying default ports and settings to non-default values to avoid common attack vectors.
Example:

sql
Copy code
— Changing the default MySQL port
[mysqld]
port = 3307

8. Data Masking and Anonymization
   Data masking involves hiding sensitive data with altered values. Anonymization removes personally identifiable information (PII) to protect user privacy.

Example:

sql
Copy code
— Masking credit card numbers
UPDATE users SET credit_card = CONCAT(REPEAT(‘X’, 12), SUBSTRING(credit_card, -4));

9. Firewalls and Network Security
   Using firewalls to control access to the database and ensuring secure network configurations are crucial steps.

Example:

sh
Copy code

Configuring UFW firewall to allow MySQL

sudo ufw allow 3306/tcp
sudo ufw enable
Summary
Database security is a multi-faceted discipline involving various techniques to protect data from unauthorized access and breaches. Key strategies include authentication, authorization, encryption, backup and recovery, auditing and monitoring, patch management, database hardening, data masking, and network security. Implementing these techniques ensures a robust defense against potential security threats.

Q3. How to create public interfaces? Discuss with relevant examples

Ans:

Creating public interfaces in software development involves defining a set of methods, functions, or endpoints that allow external systems, applications, or users to interact with your application or service. Public interfaces are crucial for enabling integration, extending functionality, and promoting reusability. This discussion will cover different types of public interfaces with relevant examples:

Application Programming Interfaces (APIs)
Web Services
Graphical User Interfaces (GUIs)
Command-Line Interfaces (CLIs)

1. Application Programming Interfaces (APIs)
   APIs are a set of functions and procedures that allow external applications to access features or data of a software application. APIs can be RESTful, SOAP, or GraphQL.

Example: Creating a RESTful API using Flask (Python)

Project Structure:

bash
Copy code
/api_project
/app
init.py
routes.py
app.py
Code:

app.py:

python
Copy code
from flask import Flask
from app.routes import api

app = Flask(name)
app.register_blueprint(api)

if name == ‘main‘:
app.run(debug=True)
app/routes.py:

python
Copy code
from flask import Blueprint, request, jsonify

api = Blueprint(‘api’, name)

In-memory database

todos = []

@api.route(‘/todos’, methods=[‘GET’])
def get_todos():
return jsonify(todos), 200

@api.route(‘/todos’, methods=[‘POST’])
def add_todo():
todo = request.json.get(‘todo’)
todos.append(todo)
return jsonify({‘message’: ‘Todo added successfully!’}), 201

@api.route(‘/todos/’, methods=[‘DELETE’])
def delete_todo(todo_id):
if 0 <= todo_id < len(todos):
todos.pop(todo_id)
return jsonify({‘message’: ‘Todo deleted successfully!’}), 200
else:
return jsonify({‘message’: ‘Todo not found!’}), 404

2. Web Services
   Web services are standardized ways of integrating web-based applications using XML, SOAP, WSDL, or RESTful services over an internet protocol backbone.

Example: Creating a SOAP Web Service using Flask and Spyne

Project Structure:

bash
Copy code
/soap_project
app.py
Code:

app.py:

python
Copy code
from flask import Flask
from spyne import Application, rpc, ServiceBase, Integer, Unicode
from spyne.protocol.soap import Soap11
from spyne.server.wsgi import WsgiApplication

app = Flask(name)

class HelloWorldService(ServiceBase):
@rpc(Unicode, Integer, _returns=Unicode)
def say_hello(ctx, name, times):
return ‘Hello, ‘ + name * times

soap_app = Application([HelloWorldService], ‘spyne.examples.hello.soap’,
in_protocol=Soap11(validator=’lxml’),
out_protocol=Soap11())

@app.route(‘/soap’, methods=[‘POST’])
def soap_endpoint():
wsgi_app = WsgiApplication(soap_app)
return wsgi_app

if name == ‘main‘:
app.run(debug=True)

3. Graphical User Interfaces (GUIs)
   GUIs provide a visual way for users to interact with software applications using elements like buttons, forms, and windows.

Example: Creating a Simple GUI using Tkinter (Python)

Project Structure:

bash
Copy code
/gui_project
app.py
Code:

app.py:

python
Copy code
import tkinter as tk
from tkinter import messagebox

class TodoApp:
def init(self, root):
self.root = root
self.root.title(“To-Do List”)

    self.todos = []

    self.todo_label = tk.Label(root, text="Enter a new to-do:")
    self.todo_label.pack()

    self.todo_entry = tk.Entry(root)
    self.todo_entry.pack()

    self.add_button = tk.Button(root, text="Add", command=self.add_todo)
    self.add_button.pack()

    self.todos_listbox = tk.Listbox(root)
    self.todos_listbox.pack()

    self.delete_button = tk.Button(root, text="Delete", command=self.delete_todo)
    self.delete_button.pack()

def add_todo(self):
    todo = self.todo_entry.get()
    if todo:
        self.todos.append(todo)
        self.update_listbox()
        self.todo_entry.delete(0, tk.END)

def delete_todo(self):
    selected_todo_index = self.todos_listbox.curselection()
    if selected_todo_index:
        del self.todos[selected_todo_index[0]]
        self.update_listbox()

def update_listbox(self):
    self.todos_listbox.delete(0, tk.END)
    for todo in self.todos:
        self.todos_listbox.insert(tk.END, todo)

if name == “main“:
root = tk.Tk()
app = TodoApp(root)
root.mainloop()

4. Command-Line Interfaces (CLIs)
   CLIs allow users to interact with software applications by typing commands into a console or terminal.

Example: Creating a CLI using Click (Python)

Project Structure:

bash
Copy code
/cli_project
app.py
Code:

app.py:

python
Copy code
import click

todos = []

@click.group()
def cli():
pass

@cli.command()
@click.argument(‘todo’)
def add(todo):
“””Add a new to-do.”””
todos.append(todo)
click.echo(f’Todo “{todo}” added!’)

@cli.command()
@click.argument(‘todo_id’, type=int)
def delete(todo_id):
“””Delete a to-do by its index.”””
if 0 <= todo_id < len(todos):
todo = todos.pop(todo_id)
click.echo(f’Todo “{todo}” deleted!’)
else:
click.echo(‘Invalid index!’)

@cli.command()
def list():
“””List all to-dos.”””
for i, todo in enumerate(todos):
click.echo(f'{i}: {todo}’)

if name == ‘main‘:
cli()
Summary
Creating public interfaces is crucial for enabling interaction between your application and external systems or users. By using APIs, web services, GUIs, and CLIs, you can provide various ways for users and other applications to access and manipulate your application’s data and functionality. Each type of interface serves different purposes and audiences, ensuring flexibility and extensibility in how your application is used and integrated.

Q4. Discuss the following with examples:
i. Providing links from authority entries
ii. Placing your application in production
iii. Maintaining and upgrading database
iv. Links to Full Records

Ans:
i. Providing Links from Authority Entries
Authority entries are standardized terms used in databases to ensure consistency and accuracy in data entry. They are often used in contexts like library catalogs, bibliographic databases, and other information systems. Providing links from authority entries helps users navigate to related information easily.

Example: Authority Control in Library Catalogs

Consider a library catalog where “Jane Austen” is an authority entry for the author. Linking this entry to all related works allows users to find all books written by or about Jane Austen easily.

Implementation Example:

Authority Entry Table:

sql
Copy code
CREATE TABLE Authors (
author_id INT PRIMARY KEY,
name VARCHAR(255) NOT NULL
);
Books Table:

sql
Copy code
CREATE TABLE Books (
book_id INT PRIMARY KEY,
title VARCHAR(255) NOT NULL,
author_id INT,
FOREIGN KEY (author_id) REFERENCES Authors(author_id)
);
Query to Retrieve Books by Jane Austen:

sql
Copy code
SELECT Books.title
FROM Books
JOIN Authors ON Books.author_id = Authors.author_id
WHERE Authors.name = ‘Jane Austen’;
ii. Placing Your Application in Production
Placing an application in production involves deploying it to a live environment where it can be accessed by users. This process includes several steps such as setting up the production environment, ensuring security, configuring databases, and monitoring performance.

Example: Deploying a Flask Application to a Production Server Using Gunicorn and Nginx

Install Necessary Packages:

sh
Copy code
pip install gunicorn flask
sudo apt-get install nginx
Gunicorn Configuration:

sh
Copy code
gunicorn –workers 3 app:app
Nginx Configuration:
Create a configuration file for your site in /etc/nginx/sites-available/your_site:

nginx
Copy code
server {
listen 80;
server_name your_domain.com;

location / {
    proxy_pass http://127.0.0.1:8000;
    proxy_set_header Host $host;
    proxy_set_header X-Real-IP $remote_addr;
    proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
    proxy_set_header X-Forwarded-Proto $scheme;
}

}
Enable Nginx Configuration:

sh
Copy code
sudo ln -s /etc/nginx/sites-available/your_site /etc/nginx/sites-enabled
sudo systemctl restart nginx
iii. Maintaining and Upgrading Database
Database maintenance and upgrades are crucial to ensure performance, security, and availability. This involves regular backups, updates, indexing, and migrations.

Example: Using Alembic for Database Migrations in SQLAlchemy

Install Alembic:

sh
Copy code
pip install alembic
Initialize Alembic:

sh
Copy code
alembic init alembic
Configure Alembic:
Edit alembic.ini and set the database URL:

ini
Copy code
sqlalchemy.url = postgresql://user:password@localhost/mydatabase
Create a Migration Script:

sh
Copy code
alembic revision –autogenerate -m “Add new column to users table”
Apply the Migration:

sh
Copy code
alembic upgrade head
Regular Backups:
Schedule regular backups using cron jobs:

sh
Copy code
crontab -e

Add the following line to backup every day at 2 AM

0 2 * * * pg_dump mydatabase > /path/to/backup/mydatabase_$(date +\%F).sql
iv. Links to Full Records
Providing links to full records enables users to access detailed information about a particular entry directly from a summary or index view. This is commonly used in digital libraries, e-commerce sites, and databases.

Example: Linking to Full Product Records in an E-commerce Application

Product Table:

sql
Copy code
CREATE TABLE Products (
product_id INT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
description TEXT,
price DECIMAL(10, 2),
image_url VARCHAR(255)
);
Query to Retrieve a Product Summary:

sql
Copy code
SELECT product_id, name, price FROM Products;
Generating Links to Full Records in HTML:

html
Copy code

• {{ product[‘name’] }} – ${{ product[‘price’] }}

Route to Display Full Product Details:

python
Copy code
@app.route(‘/product/’)
def product_detail(product_id):
product = get_product_by_id(product_id) # Function to fetch product details from the database
return render_template(‘product_detail.html’, product=product)
HTML Template for Product Details (product_detail.html):

html
Copy code

{{ product[‘name’] }}

{{ product[‘description’] }}

Price: ${{ product[‘price’] }}

Summary
Creating public interfaces, deploying applications to production, maintaining and upgrading databases, and linking to full records are essential practices in software development. Each step ensures the application is accessible, reliable, secure, and user-friendly, providing a comprehensive approach to building and managing robust software solutions.

Q5. Write short notes on the following:
i. Authority table Maintenance
ii. Database Maintenance Functions
iii. Encryption
iv. Full Keyword Searching

ans:
i. Authority Table Maintenance
Authority table maintenance refers to the process of managing and updating authority tables, which contain standardized entries used to ensure consistency and accuracy in databases. Authority tables are commonly used in contexts such as library catalogs, bibliographic databases, and information systems to maintain controlled vocabularies for names, subjects, and other categories.

Key Activities:

Adding New Entries: Integrating new standardized terms as they are identified.
Updating Existing Entries: Modifying entries to reflect changes in terminology or corrections.
Merging Duplicates: Identifying and consolidating duplicate entries to maintain data integrity.
Deleting Obsolete Entries: Removing terms that are no longer relevant or have been replaced.
Example:

In a library catalog system, an authority table for authors might include entries like:

Author ID Author Name
1 Austen, Jane
2 Shakespeare, William
3 Orwell, George
Maintenance involves ensuring these entries are up-to-date and accurately reflect the standard forms of names used in the catalog.

ii. Database Maintenance Functions
Database maintenance functions are a set of operations performed regularly to ensure the smooth functioning, integrity, and performance of a database. These functions are crucial for preventing data corruption, ensuring data availability, and optimizing performance.

Key Functions:

Backup and Recovery: Regularly creating backups to prevent data loss and implementing recovery plans.
Indexing: Creating and updating indexes to speed up query performance.
Data Integrity Checks: Ensuring that data remains accurate and consistent through validation and constraints.
Vacuuming and Analyzing: Removing obsolete data and updating statistics to optimize query planning (e.g., in PostgreSQL).
Archiving and Purging: Moving old data to archives and deleting obsolete data to manage database size.
Example:

In PostgreSQL, you can use the VACUUM command to clean up the database and improve performance:

sql
Copy code
VACUUM ANALYZE;
This command helps reclaim storage by removing dead tuples and updates statistics used by the query planner.

iii. Encryption
Encryption is the process of converting data into a coded format that can only be read by someone who has the decryption key. It is a fundamental technique for ensuring the confidentiality and security of data, both at rest and in transit.

Types of Encryption:

Symmetric Encryption: The same key is used for both encryption and decryption (e.g., AES).
Asymmetric Encryption: Uses a pair of keys – a public key for encryption and a private key for decryption (e.g., RSA).
Use Cases:

Data-at-Rest Encryption: Protects stored data, such as files on disk or database records.
Data-in-Transit Encryption: Secures data transmitted over networks, typically using protocols like TLS/SSL.
Example:

Using AES encryption in Python:

python
Copy code
from Crypto.Cipher import AES
import base64

Encryption

key = b’Sixteen byte key’
cipher = AES.new(key, AES.MODE_EAX)
nonce = cipher.nonce
data = b’Hello, World!’
ciphertext, tag = cipher.encrypt_and_digest(data)

Decryption

cipher = AES.new(key, AES.MODE_EAX, nonce=nonce)
plaintext = cipher.decrypt(ciphertext)
print(plaintext.decode(‘utf-8’))
iv. Full Keyword Searching
Full keyword searching is a search technique that allows users to find records in a database by matching any part of a text field against a query keyword. This method enhances search functionality by enabling more flexible and comprehensive searches.

Key Features:

Relevance Ranking: Results are often ranked based on relevance to the search terms.
Wildcard and Fuzzy Matching: Allows for partial matches and tolerance for misspellings or variations.
Full-Text Indexing: Uses specialized indexes to speed up keyword searches in large text fields.
Example:

Implementing full keyword searching in a MySQL database using the FULLTEXT index:

Create a Table with FULLTEXT Index:

sql
Copy code
CREATE TABLE Articles (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255),
body TEXT,
FULLTEXT (title, body)
);
Insert Sample Data:

sql
Copy code
INSERT INTO Articles (title, body) VALUES
(‘Introduction to Databases’, ‘Databases are organized collections of data.’),
(‘Advanced SQL Queries’, ‘Learn advanced SQL techniques for querying databases.’);
Perform a Full-Text Search:

sql
Copy code
SELECT * FROM Articles
WHERE MATCH(title, body) AGAINST(‘databases’ IN NATURAL LANGUAGE MODE);
This query will return all articles containing the keyword “databases” in either the title or body, demonstrating the flexibility and power of full keyword searching.