Home / MAD1 / Week 4

Week 4 · Models: databases, SQL, ER diagrams and SQLAlchemy

The one-minute story

By the end of this week you can:

• draw a small ER diagram and turn it into tables with primary and foreign keys;
• write SQL with WHERE, GROUP BY, HAVING, ORDER BY, LIMIT and joins, and predict the output of a query on a small table;
• explain NULL, constraints, normalisation, transactions and ACID;
• write SQLAlchemy and Flask-SQLAlchemy models and predict what query, filter_by, commit and rollback do;
• fix classic ORM mistakes (forgetting commit, using and in a filter, positional constructor arguments).

Words you need

Idea 1: Why a database, and the relational model

Why not just keep data in a text file or a Python list? Because files give you no help with searching fast, sharing between many users, avoiding duplicates, protecting against half-finished writes, and access control. A DBMS gives all of these. (A question in the papers asks which database fits a university with enrolments, prerequisites, grades, fee payments and refunds: the answer is an RDBMS, because the data is structured, linked, and needs transactions.)

In the relational model all data lives in tables.

Each row is one student, each column has one type, and the order of rows has no meaning.

A few more words the exam uses: redundancy (the same fact stored in many places) wastes space and causes mistakes, while replication (deliberate copies on different servers) protects against loss and helps reads. One real question gave "redundancy improves performance and reduces server load" and "replication ensures data is not lost if a copy is destroyed" as a pair, and the right call was that both statements were false as written, so read such statements slowly. An index is an extra lookup structure (like the index at the back of a book) whose job is to speed up searching; it does not encrypt or replicate anything.

Idea 2: ER diagrams and turning them into tables

An ER (entity-relationship) diagram is a plan drawn before any table is made. Entities are rectangles (Student, Course), attributes are ovals or a list inside the box, and relationships are lines (often a diamond) between entities, labelled with a verb ("enrols in").

How to read it. Read the line from each side: "One channel has many videos. One video belongs to one channel." That is one-to-many. Check both directions before you answer.

CREATE TABLE student(
  id    INTEGER PRIMARY KEY,
  name  TEXT NOT NULL,
  email TEXT UNIQUE
);
CREATE TABLE course(
  code    TEXT PRIMARY KEY,
  title   TEXT NOT NULL,
  credits INTEGER DEFAULT 4
);
CREATE TABLE enrolment(
  student_id  INTEGER REFERENCES student(id),
  course_code TEXT    REFERENCES course(code),
  marks       INTEGER CHECK(marks BETWEEN 0 AND 100),
  PRIMARY KEY (student_id, course_code)
);

How an examiner reads a model to find the relationship: if Enrollment has course_id = db.Column(db.Integer, db.ForeignKey("course.id")) and Course does not have a column pointing back, the FK is on the Enrollment side, so one course has many enrolments and each enrolment has one course: one-to-many. No link table, so not many-to-many.

Idea 3: SQL, the language of tables

Use SQLite (a database in a single file, built into Python). Statements end with ;. Keywords are case-insensitive, text values are in single quotes.

INSERT INTO student(name, email) VALUES ('Asha','a@x.in'), ('Ravi','r@x.in'), ('Meena','m@x.in');
INSERT INTO course(code, title) VALUES ('MAD1','App Dev 1'), ('MAD2','App Dev 2'), ('DBMS','Databases');
INSERT INTO enrolment VALUES (1,'MAD1',80), (1,'DBMS',60), (2,'MAD1',70);

SELECT name FROM student ORDER BY name DESC LIMIT 2;       -- Ravi, Meena
UPDATE enrolment SET marks = marks + 5 WHERE course_code = 'MAD1';
DELETE FROM enrolment WHERE marks < 70;

Run on those tables, SELECT name FROM student ORDER BY name DESC LIMIT 2 gives Ravi then Meena. After the update the marks are 85, 60, 75; after the delete (marks under 70) only the rows with 85 and 75 remain, so SELECT COUNT(*) FROM enrolment is 2.

SELECT Manufacturer, COUNT(*) FROM production
WHERE Quantity > 10
GROUP BY Manufacturer
HAVING COUNT(*) = 2;

SELECT Course, count(ID) AS No_Of_Students
FROM table_A GROUP BY Course
ORDER BY No_Of_Students ASC;

A SQLite quirk the papers use. With a table flowers(ID, Name, color) holding Jasmine (white), Rose, Lily, Lotus (all pink) and Red rose (red), the query SELECT count(ID), Name, color FROM flowers GROUP BY color HAVING count(color) < 2 returns 1 | Jasmine | white and 1 | Red rose | red (the pink group has 3 rows, so it is dropped). Name is not an aggregate nor in GROUP BY: other databases refuse this, SQLite quietly picks a value from the group (here the only one).

Joins

A join glues rows from two tables where a condition holds (normally FK equals PK).

SELECT s.name, e.course_code, e.marks
FROM student s JOIN enrolment e ON e.student_id = s.id
ORDER BY s.name, e.course_code;

Result: Asha DBMS 60, Asha MAD1 80, Ravi MAD1 70. Meena has no enrolment, so she disappears from an inner JOIN. A LEFT JOIN keeps every student: SELECT s.name, e.course_code FROM student s LEFT JOIN enrolment e ON e.student_id = s.id gives Asha twice, Ravi, and Meena | NULL. Forgetting the ON condition produces a cross join: 3 students times 3 courses is 9 rows.

NULL behaviour

NULL means "unknown", so almost every comparison with it is not true.

SELECT NULL = NULL;            -- NULL (not 1)
SELECT NULL IS NULL;           -- 1
SELECT 1 + NULL;               -- NULL

For a column x holding 1, NULL, 3: COUNT(*) is 3, COUNT(x) is 2 (NULLs are skipped), AVG(x) is 2.0, SUM(x) is 4, WHERE x <> 1 returns only the 3 (the NULL row is not returned), and you must write WHERE x IS NULL to find it.

Constraints

CREATE TABLE t(
  id   INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  age  INTEGER CHECK(age >= 0) DEFAULT 18
);
INSERT INTO t(name) VALUES ('Amit');   -- row (1, 'Amit', 18): DEFAULT filled age
INSERT INTO t(name, age) VALUES ('x', -1);  -- fails: CHECK constraint failed: age>=0
INSERT INTO t(age) VALUES (5);              -- fails: NOT NULL constraint failed: t.name

UNIQUE rejects a duplicate value (several NULLs are still allowed in SQLite). SQLite does not enforce foreign keys unless you run PRAGMA foreign_keys = ON; for each connection. That surprises people.

Idea 4: Normalisation and transactions (ACID)

Normalisation is the discipline of splitting tables to remove redundancy so each fact is stored once.

Example of a violation: an orders table with customer_name and customer_phone copied on every order. Change the phone once and some rows keep the old one (an update anomaly). Fix: a customer table and an FK.

A transaction is a group of statements treated as one unit. The classic: transferring 500 rupees means "subtract from A" and "add to B". If the power fails in between, money vanishes. The database promises ACID:

Python's built-in sqlite3 module shows it clearly:

import sqlite3
con = sqlite3.connect("shop.db")      # creates the file if missing
cur = con.cursor()
cur.execute("CREATE TABLE IF NOT EXISTS acct(name TEXT PRIMARY KEY, bal INTEGER)")
cur.execute("INSERT OR REPLACE INTO acct VALUES (?, ?)", ("A", 1000))
con.commit()                          # saved
cur.execute("UPDATE acct SET bal = bal - 500 WHERE name = 'A'")
con.rollback()                        # undone
print(cur.execute("SELECT bal FROM acct WHERE name='A'").fetchall())   # [(1000,)]
con.close()

Always pass values with ? placeholders, never by pasting strings (that invites SQL injection).

Idea 5: SQLAlchemy and the ORM

Writing raw SQL strings inside Python gets messy. An ORM maps class to table, object to row, attribute to column. SQLAlchemy has these parts:

• Engine: knows how to reach the database (create_engine("sqlite:///shop.db")).
• Connection / Session: the Session holds your pending changes and talks to the engine. You add objects, then commit once.
• Declarative base: the parent class of all your models.

from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.orm import declarative_base, sessionmaker, relationship

Base = declarative_base()

class Dept(Base):
    __tablename__ = "dept"
    id    = Column(Integer, primary_key=True)
    name  = Column(String, nullable=False)
    staff = relationship("Emp", back_populates="dept")

class Emp(Base):
    __tablename__ = "emp"
    id      = Column(Integer, primary_key=True)
    name    = Column(String)
    dept_id = Column(Integer, ForeignKey("dept.id"))
    dept    = relationship("Dept", back_populates="staff")

engine = create_engine("sqlite:///:memory:")
Base.metadata.create_all(engine)          # CREATE TABLE for every model
s = sessionmaker(bind=engine)()           # a Session

d = Dept(name="CS")
d.staff.append(Emp(name="Ria"))           # link through the relationship
s.add(d)
s.commit()

e = s.query(Emp).first()
print(e.name, e.dept.name, d.staff[0].name)   # Ria CS Ria

What happened: ForeignKey("dept.id") is the database-level link (a column). relationship(...) is the Python-level convenience: d.staff is a list of Emp, and e.dept is one Dept. back_populates says the two relationship attributes are two ends of the same link (use the same names on both sides). The older shortcut backref="dept" on one side creates the other side automatically. The commit wrote both rows and filled dept_id for you.

A model declaration must match the table: a question gave CREATE TABLE "Items" (ID INTEGER, name TEXT, cost INTEGER, PRIMARY KEY(ID AUTOINCREMENT)) and the correct class has __tablename__ = "Items", ID = Column(Integer, primary_key=True, autoincrement=True), name = Column(String), cost = Column(Integer). Same class name, table name and column names as the SQL.

Commit and rollback. s.add(Emp(name="Tom")); s.rollback() throws the pending Tom away, so s.query(Emp).count() is still 1.

Idea 6: Flask-SQLAlchemy and CRUD

Flask-SQLAlchemy wires SQLAlchemy into Flask so you write less setup. The names are different, so learn them as a set.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import func

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///shop.sqlite3"
db = SQLAlchemy(app)                      # or db = SQLAlchemy() then db.init_app(app)

class Author(db.Model):
    id    = db.Column(db.Integer, primary_key=True)
    name  = db.Column(db.String(80), nullable=False)
    books = db.relationship("Book", backref="author", lazy=True)

class Book(db.Model):
    id        = db.Column(db.Integer, primary_key=True)
    title     = db.Column(db.String(100), nullable=False, unique=True)
    price     = db.Column(db.Integer, default=100)
    author_id = db.Column(db.Integer, db.ForeignKey("author.id"))
    def __repr__(self): return f"<Book {self.title}>"

with app.app_context():                   # needed outside a request
    db.create_all()                       # creates missing tables only

Notes: the table name is the lower-case class name (Author becomes author), which is why ForeignKey("author.id") uses lower case. In Flask-SQLAlchemy 3 the models and db.session need an application context (inside a view you already have one; in a script use with app.app_context():). create_all() does not alter a table that already exists: after changing a model, delete the .sqlite3 file (or use migrations).

with app.app_context():
    db.create_all()
    a = Author(name="Asha")
    b1 = Book(title="DB 101", price=300, author=a)
    b2 = Book(title="SQL", author=a)
    db.session.add_all([a, b1, b2]); db.session.commit()
    print(a.books)                        # [<Book DB 101>, <Book SQL>]
    print(b1.author.name, b2.price)       # Asha 100
    print(Book.query.filter(Book.price > 150).all())      # [<Book DB 101>]
    print(db.session.query(func.max(Book.price)).scalar())   # 300
    b = Book.query.filter_by(title="SQL").first()
    b.price += 50; db.session.commit()
    print(b.price)                        # 150
    db.session.delete(b); db.session.commit()
    print(Book.query.all())               # [<Book DB 101>]

Constructing objects. SQLAlchemy's default constructor takes keyword arguments only. Book("a", 1, 1) raises TypeError: __init__() takes 1 positional argument but 4 were given. Use Book(title="a", price=1). The right order is create, add, then commit; commit before add saves nothing.

Violating a constraint. Adding a second book titled "SQL" and committing raises IntegrityError (the unique=True). The session is then in a failed state, so always call db.session.rollback() in the except block.

user1 = Student.query.filter_by(first_name="John").first()
user1.first_name = "Harry"
user1.last_name = "Luther"
db.session.commit()
for s in Student.query.all():
    print(s.first_name, s.last_name)

items = Product.query.filter_by(type="edible", form="liquid").all()
for item in items:
    item.type = "perishable"
    item.quantity += 450
db.session.commit()

repairs = GlassRepair.query.all()
result = [(r.id, (r.fixed_at - r.broken_at).days) for r in repairs]

Combining conditions

Order.query.filter(Order.status == "PAID").filter(Order.amount > 500).all()   # correct
Order.query.filter(Order.status == "PAID", Order.amount > 500).all()          # correct, same
from sqlalchemy import and_, or_
Order.query.filter(and_(Order.status == "PAID", Order.amount > 500)).all()    # correct
Order.query.filter(Order.status == "PAID" and Order.amount > 500)             # TypeError!

The last one uses Python's and on two SQL expressions, which fails with TypeError: Boolean value of this clause is not defined. Use , / chained .filter / and_ / & (with brackets). filter_by takes keyword equality only (filter_by(status="PAID")); filter takes expressions (Order.amount > 500).

Exam radar

Quick quiz

Viva: say it out loud

Recap

Practise with real exam questions

These come straight from past papers (with the real figures and the official answer key). Open 60 real questions on this week   All Quiz 1 papers