| CPSC 343 | Database Theory and Practice | Fall 2024 |
All of the exams in this course will be written (not on the computer) in-class closed book exams.
You may use a single page of notes (8.5x11", one side, hardcopy — it may not be viewed electronically during the exam). It can be handwritten or typed and can contain whatever you would like, but it must be personally prepared by you — you may not copy another student's notes or make copies of your own for others. Creating your own notes is an essential part of the learning process — deciding what to include requires engagement with the material which reinforces understanding and improves long-term retention of the material, provides an opportunity for review in order to identify gaps in your knowledge in time to ask questions before the exam, increases confidence in what you do know, and encourages taking ownership of your own learning.
The midterm exams are one hour, and this time limit will be strictly enforced. While this is intended be enough time to complete the exam, it is still a very limited amount of time — don't assume that you don't have to study beyond preparing your page of notes because you can look up what you need. Instead, make sure you practice enough with the material so that you can start producing a solution right away and are always moving forward in your answering of a question.
Exam 1 will cover SQL: queries (simple queries, products and joins, set operations, subqueries, grouping, aggregation), data modification (INSERT, DELETE, UPDATE), and related concepts (e.g. three-valued logic, the handling of NULL values, and the handling of duplicates). You won't be asked about LOAD DATA INFILE, though you should know how to use it.
Expect questions similar to the in-class and practice problems: writing queries (SELECT) and INSERT, DELETE, UPDATE statements. There may also be short answer questions about concepts or which ask you to describe what a query does or explain whether or not a query accomplishes a specific task.
You will be provided with any schemas you are asked to work with; you don't have to memorize the sailors, library, grades, etc databases from the homeworks. The notation used for the relations, including denoting primary keys and foreign keys, will be the same as what has been used in class. You should know what primary keys and foreign keys are.
You should be familiar with the SQL constructs covered in the reading and in the class slides, and with common patterns of solution that have come up in homework and practice problems (such as handling "every", "at least two", and so forth). You may use SQL constructs not covered in the reading (such as LIMIT, WITH, and COALESCE) but this is not required/expected --- the exam is meant as an opportunity to demonstrate mastery of the core elements of SQL covered in the course, not a research project into the full scope of SQL.
You should also be comfortable with reading a query and understanding what it does. There may be questions specifically asking you to do that, but it is also an important for checking that your answer is correct and fixing incorrect queries --- since the exam is written, you do not have the opportunity to test your queries.
Exam 2 will cover conceptual modeling using the ER and EER models. It will not include anything about SQL, the relational model, ER-to-relational mapping, or normalization.
Expect questions similar to the homework assignments and practice problems: creating ER or EER diagrams to model a given situation, reading ER or EER diagrams, and comparing/contrasting ER or EER diagrams. There may also be short answer questions about concepts, including definitions.
-Exam 3 will cover database implementation: ER-to-relational mapping, relational design principles, decomposition and normalization, and implementation of a relational schema in SQL (including specifying constraints and triggers). You will need to be able to read ER/EER diagrams but you won't be asked to create new ER/EER diagrams or discuss modeling tradeoffs within the ER/EER model.
Expect questions similar to the in-class and homework problems: converting ER or EER diagrams to a relational schema, comparing/contrasting/discussing design choices in relational schemas, normalizing a relational schema up to and including BCNF, implementing a relational schema in SQL, and defining constraints (PK, FK, UNIQUE, NOT NULL, CHECK, DEFAULT, etc) and triggers. There may also be short answer questions about concepts, including definitions.
You will not be asked to write CREATE TABLE, ALTER TABLE, or CREATE TRIGGER statements from scratch. You should, however, be familiar with common SQL data types (CHAR, VARCHAR, the varieties of INT, DECIMAL) and be able to write conditions for CHECK constraints and the bodies for triggers. You should also be able to identify the when (BEFORE or AFTER) and action (INSERT, DELETE, UPDATE) for each trigger.
Exam 4 will cover database-side things of relevance to database applications: security considerations, permissions, views, stored routines, and transactions. Note that while the focus will be on these topics, related material (such as writing queries and the use of INSERT, DELETE, and UPDATE statements) is unavoidable and should be expected. The exam will not cover implementation of the application itself (PHP, Java, etc).
Expect questions similar to the homework assignments and practice problems: writing views and stored routines. This includes determining whether a stored procedure or function is more appropriate for a given situation. You may also be asked to write queries involving views and stored functions and/or to write statements to call stored procedures and access the results.
In addition, there may be short answer questions about concepts (such as SQL injection or transactions).
You will be asked to write the CREATE statements for views and stored routines but you only need the required (basic) parts of the statement — you don't need to include the ALGORITHM, DEFINER, or SQL SECURITY clauses or the DELIMITER statements that flank the CREATE statement. You should be able to use variables, IF statements, WHILE loops, cursors, and EXIT and CONTINUE handlers in the body of the routine and should be able to signal errors.