CS 341 (CSE341) — Database Systems (Fall 2024)

This repository is a complete semester archive for CS 341 / CSE341: Database Systems taught at the Institute of Business Administration (IBA), Karachi in Fall 2024.
Instructor: Abeera Tariq.

It contains:

• Labs (Oracle SQL, PL/SQL, ERD design, transactions/concurrency, MongoDB)
• Lecture slides (PDFs)
• Quizzes
• Reference books
• A Lab 07 Oracle-backed CRUD app (Node/Express + React)

Note: This repo was maintained throughout the semester as a structured academic archive for revision and reference.

---

Repository Structure

• Fall24Outline_CS341_Database_Systems.pdf — Course outline
• lectures/ — Lecture slides (PDFs)
• labs/ — All labs (each lab has its own README.md)
• quizzes/ — Quiz PDFs
• books/ — Reference textbooks
• relax.png — Personal file kept as-is

---

What This Course Covers (High-Level)

1) Database Fundamentals & Design

• Database concepts, schemas, and modeling
• ER modeling (Chen + Crow’s Foot)
• ERD → relational mapping (PK/FK, constraints)
• Integrity constraints and good schema design practices

2) Relational Databases & SQL (Oracle)

• Core SQL querying: filtering, sorting, projection
• Aggregations: GROUP BY, HAVING, COUNT/SUM/AVG/MIN/MAX
• Joins: inner/outer/self joins, different join syntaxes
• Subqueries, correlated subqueries, EXISTS/NOT EXISTS
• Set operators: UNION, INTERSECT, MINUS
• Views and reusable query logic
• DDL + constraints (CREATE, ALTER, PK/FK/CHECK/UNIQUE/NOT NULL)
• Safe DML patterns (INSERT/UPDATE/DELETE)

3) PL/SQL (Oracle)

• Procedures, loops, conditionals
• Cursors (explicit/implicit)
• Exception handling
• Modular organization patterns (where relevant)

4) Transactions & Concurrency (Oracle)

• Transaction boundaries (COMMIT, ROLLBACK, SAVEPOINT)
• Visibility across sessions and locking behavior
• Deadlocks (ORA-00060) and prevention patterns (SELECT ... FOR UPDATE)
• Blocking/waiting semantics under common isolation behavior

5) NoSQL & MongoDB

• Setup (local/cloud)
• Importing JSON/CSV into collections
• CRUD operations (filter/projection/sort/limit)
• Nested fields/arrays
• Aggregation pipelines for analytics-style outputs

---

Lab Index (Quick Map)

Each lab is inside labs/ and includes a lab-specific README.md plus the relevant solution files.

• Lab 01 — Oracle setup + HR schema + basic SQL
• Lab 02 — Aggregations + SQL functions
• Lab 03 — DDL, constraints, schema building + insertions
• Lab 04 — ERD design (Chen + Crow’s Foot + DB Designer)
• Lab 05 — Joins (HR schema)
• Lab 06 — Advanced SQL (subqueries, CTEs, EXISTS, set ops, views, CASE, DML)
• Lab 07 — Oracle-backed CRUD app (REST APIs + frontend)
• Lab 08 — PL/SQL basics
• Lab 09 — PL/SQL II (more advanced constructs and robust routines)
• Lab 10 — Transactions & concurrency (multi-session)
• Lab 11 — DCL + advanced SQL/PLSQL (roles/privileges + packaged logic)
• Lab 12 — MongoDB setup + intro CRUD
• Lab 13 — MongoDB II (advanced queries + aggregation)

---

Lab 07 App (Oracle CRUD) — How to Run

Path:

labs/lab07_app/lab7-dbms-app/

Contents:

• db-init.sql — DB initialization script
• backend/ — Node.js/Express API (Oracle DB)
• frontend/ — React UI
• backend/postman/ — Postman collection for API testing

Backend

1. Go to:

cd labs/lab07_app/lab7-dbms-app/backend

2. Install dependencies:

npm install

3. Create a .env file (do NOT commit credentials). Use your Oracle connection details.
4. Start server:

npm start

Frontend

1. Go to:

cd labs/lab07_app/lab7-dbms-app/frontend

2. Install + run:

npm install
npm start

---

Notes for Running Oracle Labs

• Most SQL/PLSQL labs assume the Oracle HR schema.
• PL/SQL labs may use a dedicated schema user for clean execution.
• Concurrency labs require two sessions (e.g., two SQL*Plus windows) to correctly reproduce behavior.

---

Academic Integrity

This repository is intended as a structured archive for learning and revision. Any reuse must follow IBA’s academic integrity policies.