DSAD based on lecture notes

Quiz 1: Week 5, Lecture 1 - Nested Queries (SQL)

• A nested query is a SELECT query within another query.
• Nested queries can be inefficient, acting like nested loops.
• DISTINCT removes duplicate records from a query result.
• The IN operator checks if a value exists in a single-column table returned by a subquery.
• SQL query to find course names that have no marks in the allmarks table: SELECT name FROM course WHERE bc NOT IN (SELECT bc FROM allmarks);
• The EXISTS operator can be useful to check if a subquery returns any records.
• Rewriting SELECT name FROM course WHERE bc IN (SELECT bc FROM allmarks); without a subquery: SELECT DISTINCT name FROM course, allmarks WHERE course.bc = allmarks.bc;

Quiz 2: Week 4, Lecture 1 - Intro & Queries (SQL)

• SQL stands for Structured Query Language.
• Omitting a connecting condition when joining multiple tables can result in a cross product.
• Edgar Codd invented relational databases.
• SQL query to find the course names and years taught by a staff member with the last name 'Reddy': SELECT name, year FROM staff, lecturing, course WHERE staff.lastname = 'Reddy' AND staff.sid = lecturing.sid AND lecturing.cid = course.cid;
• DISTINCT removes duplicate rows from the result set of a query.
• A schema summarizes the structure of a database, defining the tables and their columns.

Quiz 3: Week 4, Lecture 2 - More Queries (SQL)

• The HAVING clause filters groups after aggregation.
• The EXCEPT operator returns results from the first query not in the second.
• NULL values are skipped in aggregation operations like COUNT.
• SQL query to find courses with no hard-failing students (marks < 40): (SELECT bc FROM allmarks) EXCEPT (SELECT bc FROM allmarks WHERE mark < 40);
• WHERE filters individual rows before aggregation.
• HAVING filters groups after aggregation based on functions like COUNT or AVG.
• SQL query to count distinct courses taught each year: SELECT year, COUNT(DISTINCT cid) FROM lecturing GROUP BY year;

Quiz 4: Week 7, Lecture 1 - Entity-Relationship Concepts

• In an ER model, an entity represents a "thing" with data to be stored.
• A multiplicity of (0,1) indicates zero or one occurrence is allowed in a relationship.
• The NOT NULL keyword ensures a field cannot be NULL.
• Schema for a Borrow table based on the small library ER model: Borrow(book_id, member_num, date, due_date, return_date, fine)
• A primary key uniquely identifies each record in a table.
• A relationship connects entities, describing how they interact (e.g., "borrowed" between Book and Member).

Quiz 5: Week 5, Lecture 2 - Relational Algebra

• The Selection operation filters rows based on a condition.
• The worst-case time complexity of a Natural Join is O(n²).
• The Rename operator (ρ) changes the name of a table or its attributes.
• Relational algebra expression to find employees in the Finance department: σ_{Dept='Finance'}(Employee)
• A functional dependency means one attribute determines another (e.g., empID → Name).
• Decomposition splits a large table into smaller ones to eliminate unwanted functional dependencies.

Quiz 6: Week 8, Lecture 1 - Logical Design: Deriving Tables from E-R Models

• A relationship table can be merged into an entity table when the multiplicity is (0,1) or (1,1).
• The REFERENCES constraint links a foreign key to a primary key in another table.
• ON DELETE CASCADE deletes records with foreign keys when the referenced record is deleted.
• Optimized schema for the Member table including Borrow fields from the library example: Member(member_num, last_name, first_name, address, date_of_joining, book_id°, borrow_date°, due_date°, return_date°, fine°)
• Three tables (Member, Book, Borrow) avoid NULL values, handle multiple borrowings better and reduce redundancy.
• A foreign key references a primary key in another table (e.g., book_id in Borrow references book_id in Book).

Quiz 7: Week 7, Lecture 2 - Entity-Relationship Modeling

• A weak entity depends on another for its identity.
• A (t, e) annotation on a hierarchy means total coverage and exclusive subclasses.
• Title is an attribute in the library ER model.
• Schema for the Copy table in the revised library ER model: Copy(book_id, copy_num)
• A strong entity has its own unique primary key.
• A weak entity relies on another entity’s key plus additional attributes for its identity.
• A hierarchy represents an "is-a" relationship where subclasses inherit from a superclass (e.g., UG_student and PG_student being subclasses of Student).