CS2855-SQL-JDBC

CS2855 Database Revision Notes: SQL & JDBC

1. SQL Basics (SQL Basics.pdf)

Core Components

• SELECT Structure:

  SELECT [DISTINCT] col1, aggregate(col2) 
  FROM table 
  [WHERE condition] 
  [GROUP BY col] 
  [HAVING group_condition]

• String Operations:

  • || for concatenation
  • LIKE 'pattern' with % (any string) and _ (single char)
    Example:

  SELECT customer_name 
  FROM customer 
  WHERE customer_street LIKE '%Main%';

• Set Operations:

  • UNION (duplicates removed)
  • INTERSECT
  • EXCEPT (difference)
    Example for union:
    (SELECT name FROM depositor) UNION (SELECT name FROM borrower);

• Aggregate Functions:

  • COUNT(*), SUM(col), AVG(col), MIN/MAX(col)
  • Use DISTINCT to eliminate duplicates
    Example:
    SELECT AVG(balance) FROM account WHERE branch_name='Perryridge';

Exam Alert (Past Papers)

• Frequently asked to write queries using GROUP BY/HAVING and string matches (e.g., 2023 Q3b/c).

---

2. Intermediate SQL (6.SQL_intermediate.pptx)

Integrity Constraints

• Domain Constraints:
  CREATE DOMAIN Dollars NUMERIC(12,2) NOT NULL;

• Table Constraints:

  • NOT NULL:
    CREATE TABLE branch (name VARCHAR(15) NOT NULL);
  • UNIQUE:
    UNIQUE (branch_name, customer_name)
  • CHECK:
    CHECK (balance >= 0)
  • Foreign Keys:
    FOREIGN KEY (acc_number) REFERENCES account
    ON DELETE CASCADE

• Assertions (Rarely Tested):
  CREATE ASSERTION balance_limit
  CHECK (NOT EXISTS (SELECT * FROM account WHERE balance < 0));

Exam Alert

• Constraints like primary/foreign keys frequently appear in schema design questions (2024 Q2: Convert ER to tables with constraints).

---

3. JDBC (7.SQL_JDBC Part 1 & 2)

Core Steps

1. Connect to Database:
   String url = "jdbc:postgresql://teachdb.cs.rhul.ac.uk/CS2855%2Fuser123";
Connection conn = DriverManager.getConnection(url, "user123", "password");

2. Execute Queries:

   • Use PreparedStatement to prevent SQL injection:

     String sql = "SELECT * FROM students WHERE name = ?";
     PreparedStatement stmt = conn.prepareStatement(sql);
     stmt.setString(1, inputName);
     ResultSet rs = stmt.executeQuery();

3. Process Results:

while (rs.next()) {
   int id = rs.getInt("id");
   String grade = rs.getString("grade");
}

4. Close Resources:
   Always close ResultSet, Statement, Connection in finally block.

Maven Setup (pom.xml)

org.postgresql postgresql 42.6.0

Exam Alert

• 2022 Q4: JDBC code snippets to identify resource leaks.
• 2023 Q4: Use PreparedStatement for secure inserts.

---

4. Past Paper Analysis

Recurring Question Types

1. ER to Relational Model (2022 Q1, 2023 Q1):

   • Map entities to tables, relationships to foreign keys.
   • Handle weak entities (use parent’s PK + partial key).

2. Relational Algebra (2024 Q2a):

   • Write expressions for queries (σ for selection, ⨝ for joins).
     Example: Find customers with loans:
     π_{customer_name, loan_no, amount} (borrower ⨝ loan)

3. SQL Optimization (2023 Q3d):

   • Avoid correlated subqueries; use JOINs.
   • Use aggregate subqueries in HAVING.

4. BCNF/3NF Decomposition (2022 Q5):

   • Check all FDs left-side are superkeys for BCNF.
   • For 3NF, allow transitive dependencies if RHS is prime.

5. Transaction Schedules (2023 Q5):

   • Test conflict serializability via precedence graphs.
   • Check if schedules are view serializable.

---

5. Exam Tips

1. Time Management: Spend 30 mins on ER/relational algebra, 45 mins on SQL/JDBC.

2. ACID Definitions (2024 Q4):

   • Atomicity: All or nothing.
   • Consistency: Valid state transitions.
   • Isolation: Concurrent runs mimic serial.
   • Durability: Committed survives crashes.

3. Normalization Steps:

   • Check for BCNF violations (non-superkey determinants).
   • Decompose using violating FD: R1(αβ), R2(R-β).
   • Verify lossless join: α must be key in R1/R2.