Activity 20: Python Orm Sqlalchemy

Understanding the Prompt: A Step-by-Step Guide

1. Setting Up the Environment

Install Python: Ensure you have Python installed on your system. You can download the latest version from the official Python website.
Create a Virtual Environment: This isolates your project's dependencies from other projects. Use a tool like virtualenv or venv to create a virtual environment.
Install SQLAlchemy: This is a Python SQL toolkit and ORM that allows you to interact with databases using Python objects. Install it using pip install sqlalchemy.

2. Creating the Databases You'll create five databases:

ORMQuizDb: For quiz-related data (questions, answers, user scores, etc.)
ORMEcommerceDb: For e-commerce data (products, orders, customers, etc.)
ORMJobBoardDb: For job board data (job postings, applications, resumes, etc.)
ORMEventManagementDb: For event management data (events, attendees, bookings, etc.)
ORMTravelBookingDb: For travel booking data (flights, hotels, bookings, etc.)

3. Generating Dummy Data Populate each database table with at least 5 rows of fake data. This data can be simple placeholders or more complex, depending on the specific requirements of each database. You can use Python's built-in random module or external libraries like Faker to generate realistic data.

4. Interacting with the Database Using SQLAlchemy, you'll perform basic CRUD operations:

Create: Add new records to a table.
Read: Retrieve existing records from a table.
Update: Modify existing records in a table.
Delete: Remove existing records from a table.

Example Code Snippet (Creating a Table and Inserting Data):

Python

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker1

# Create an engine (replace 'sqlite:///mydatabase.db' with your database URI)
engine = create_engine('sqlite:///mydatabase.db')

# Create a declarative base
Base = declarative_base()

# Define a model (e.g., for a User table)
class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    email = Column(String)

# Create the2 database schema
Base.metadata.create_all(engine)

# Create a session
Session = sessionmaker(bind=engine)
session = Session()

# Insert data
user1 = User(name='Alice', email='alice@example.com')
user2 = User(name='Bob', email='bob@example.com')
session.add_all([user1, user2])
session.commit()

Additional Considerations:

Data Modeling: Design the database schema carefully, considering relationships between tables (one-to-one, one-to-many, many-to-many).
Error Handling: Implement error handling mechanisms to catch exceptions and handle potential issues during database operations.
Security: If working with sensitive data, ensure proper security measures are in place to protect it.
Performance Optimization: For large datasets, consider techniques like indexing, query optimization, and connection pooling to improve performance.

By following these steps and understanding the concepts of SQLAlchemy and database interactions, you can effectively work with the given databases and perform the required tasks.

Database Schema Design

Here are the proposed database schemas for each domain:

ORMQuizDb

CREATE TABLE Users (
    userId INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE   
);

CREATE TABLE Quizzes (
    quizId INT PRIMARY KEY AUTO_INCREMENT,
    quizName VARCHAR(100) NOT NULL,
    description TEXT,
    userId INT,
    FOREIGN KEY (userId) REFERENCES Users(userId)
);

CREATE TABLE Questions (
    questionId INT PRIMARY KEY AUTO_INCREMENT,
    questionText TEXT NOT NULL,
    quizId INT,
    FOREIGN KEY (quizId) REFERENCES Quizzes(quizId)
);

CREATE TABLE Answers (
    answerId INT PRIMARY KEY AUTO_INCREMENT,
    answerText TEXT NOT NULL,
    isCorrect BOOLEAN,
    questionId INT,
    FOREIGN KEY (questionId) REFERENCES Questions(questionId)
);

CREATE TABLE UserScores (
    scoreId INT PRIMARY KEY AUTO_INCREMENT,
    userId INT,
    quizId INT,
    score INT,
    FOREIGN KEY (userId) REFERENCES Users(userId),
    FOREIGN KEY (quizId) REFERENCES Quizzes(quizId)
);

This SQL code defines a database schema for a quiz application, creating tables for user information, quizzes, questions, answers, and user scores. Each table is linked through foreign keys, ensuring data integrity and allowing users to create quizzes, answer questions, and track their performance.

ORMEcommerceDb

CREATE TABLE Categories (
    categoryId INT PRIMARY KEY AUTO_INCREMENT,
    categoryName VARCHAR(50) NOT NULL
);

CREATE TABLE Products (
    productId INT PRIMARY KEY AUTO_INCREMENT,
    productName VARCHAR(100)    NOT NULL,
    description TEXT,
    price DECIMAL(10,2),
    categoryId INT,
    FOREIGN KEY (categoryId) REFERENCES Categories(categoryId)
);

CREATE TABLE    Customers (
    customerId INT PRIMARY KEY    AUTO_INCREMENT,
    customerName VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    address TEXT
);

CREATE TABLE Orders (
    orderId INT PRIMARY KEY AUTO_INCREMENT,
    customerId INT,
    orderDate DATETIME,
    totalPrice DECIMAL(10,2),
    FOREIGN KEY (customerId) REFERENCES Customers(customerId)
);

CREATE TABLE OrderItems (
    orderItemId INT PRIMARY KEY AUTO_INCREMENT,
    orderId INT,   
    productId INT,
    quantity INT,
    price DECIMAL(10,2),
    FOREIGN KEY (orderId) REFERENCES Orders(orderId),
    FOREIGN KEY (productId) REFERENCES Products(productId)   
);

This SQL code defines a database schema for an e-commerce platform that manages products, customers, and orders. It creates six tables: Categories, Products, Customers, Orders, OrderItems, representing different aspects of the platform. The schema allows for managing product categories, storing product details, tracking customer information, recording orders, and maintaining order items.