MAD2-Viva-DataBase

 Questions Related to DataBase

Difference between back_populates and backref?

back_populates:

back_populates performs synchronization on both ends.back_populates requires explicitly defining the relationship on both sides,

1.It can manually define the relationship on both sides of the models. control each side separately.

2.Each relationship explicitly refers to the other with back_populates.

Ex:

class Parent(Base):

    __tablename__ = 'parents'

    id = Column(Integer, primary_key=True)

    children = relationship("Child", back_populates="parent")

class Child(Base):

    __tablename__ = 'children'

    id = Column(Integer, primary_key=True)

    parent_id = Column(Integer, ForeignKey('parents.id'))

    parent = relationship("Parent", back_populates="children")

backref:

 It creates a bidirectional relationship between two models

1.Define the relationship in one place only and it automatically sets up the other side

backref defines the relationship both in the parent and the child models automatically.The child table will have a reference back to the parent table without requiring additional code.

The backref="parent" means that a bidirectional relationship is created in the Child model, a parent attribute is automatically added, allowing each Child instance to refer back to its corresponding Parent.

2.It’s a shorthand for defining relationships.Easier to use, but less explicit.

class Parent(Base):

    __tablename__ = 'parents'

    id = Column(Integer, primary_key=True)

    children = relationship("Child", backref="parent")

class Child(Base):

    __tablename__ = 'children'

    id = Column(Integer, primary_key=True)

    parent_id = Column(Integer, ForeignKey('parents.id'))

3.The parent_id column explicitly establishes the foreign key relationship.

4.The parent attribute is automatically added to this model due to the backref defined in the Parent model.

5.Bidirectional Nature: Both models can navigate the relationship:

• From Parent to Child (parent.children)
• From Child to Parent (child.parent)

6.No Need for Additional Code in the Child Model: The backref automatically handles setting up the reverse relationship.

What is ORM?

ORM stands for Object-Relational Mapping. It is a programming technique that allows developers to interact with a database using objects, classes, and methods in their programming language instead of writing raw SQL queries

1.Maps Classes to Tables

2.Handles relationships (e.g., one-to-many, many-to-many) between tables

3.database operations simpler(CRUD)

4.Easier to switch databases (e.g., SQLite to PostgreSQL)

SQLAlchemy is ORM library

Is the MS SQL server connectable with SQL Alchemy?

Yes, Microsoft SQL Server is fully compatible with SQLAlchemy. SQLAlchemy provides support for MS SQL Server through third-party dialects such as pyodbc or pymssql. You can use these libraries to establish a connection between your Python application and an MS SQL Server database.

5) why you  used extra table for roleusers?

Many-to-Many Relationship

If a user can have multiple roles (e.g., Admin, Customer, Service Professional) and a role can belong to multiple users, a direct one-to-one or one-to-many mapping wouldn't suffice. A roleusers table serves as a bridge (join table) to connect users and roles in such cases.

Example:

• User A: Admin, Customer
• User B: Customer
• User C: Service Professional, Customer

A join table like roleusers would allow flexibility in assigning these relationships.

Scalability, Flexibility ,Simplified Queries

As your application grows, the roleusers table allows you to easily:

• Add new roles without modifying the schema of the users table.
• Assign new roles to existing users dynamically.
  
• Relationships between users and roles, without cluttering the users table.
• Track when a role was assigned or removed (by adding timestamps to roleusers).
  

 what is SQL INJECTION?

SQL Injection is a type of security vulnerability that occurs when an attacker manipulates a SQL query by injecting malicious SQL code through user input fields. This exploit can give attackers unauthorized access to the database, allowing them to retrieve, modify, or delete sensitive information.