| 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.
For exam 5 and the final exam, you may use two pages of notes (8.5x11", either both sides of one sheet or one side each of two sheets). Otherwise, the same rules apply as for exams 1-4 (see above) — it must be a hardcopy (not viewed electronically during the exam) and personally prepared by you.
Exam 5 and the final exam will be during the scheduled final exam period (Wed 12/11 7-10pm). Exam 5 is intended to take about one hour of that time, though this time limit won't be enforced — you can spend as much or as little of the three hours as you want on exam 5. The final exam is intended to be shorter overall than the combined total of exams 1-4 — and each section will generally be shorter than the corresponding section of the midterms — but you will not necessarily have time to complete the entire exam. (You won't be penalized for questions you don't answer on the final; see the more detailed information below for specific about how the final works.) Be strategic about what to focus on first and what to work on if you have time.
Unless it is an emergency, you may not leave the room while working on an exam. To allow for a break, exam 5 and the final will be separate, with exam 5 handed out at the beginning of the exam period. You may leave the room for any reason after handing in exam 5 and before starting on the final, and, of course, you may leave after handing in both exams if you finish early. (It's not required that you hand in exam 5 before starting on the final — you can work on both at the same time in order to better prioritize your efforts — but you then don't have an opportunity for a break.) Plan ahead — use the bathroom and take care of any other necessary business beforehand or between exam 5 and the final.
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.
Exam 5 will cover file organization, indexes, and query processing.
Expect questions similar to the homework assignments and practice problems: calculating various quantities (record size, blocking factor, etc), calculating the number of block accesses needed for a search under various conditions (different file organizations and/or indexes), giving a query tree corresponding to a simple SQL query (SELECT, FROM, WHERE; may involve subqueries in FROM or operations NOT IN or NOT EXISTS with subqueries), carrying out heuristic optimization, identifying an execution plan for a query tree, and calculating the cost of an execution plan (with and without pipelining). There may also be short answer questions about concepts and terms, such as identifying the type of an index (primary, clustering, or secondary), explaining how to find all of the records matching a certain condition using a particular type of index, explaining why we focus on the number of blocks as a measure of running time, or identifying and justifying which index would be most effective for speeding up the execution time of a particular query.
Calculators will be provided. It may be useful to know that logb a = (logc a)/(logc b) — so you can use ln or log10 to compute log2. (This rule will also be on the exam so you don't need to memorize it, just be aware of how to use it.)
The cost functions discussed in class for the various SELECT and JOIN algorithms will also be provided on the exam — you don't need to memorize these or include them in your two pages of notes. You should, however, be familiar with the meaning of the letters (b, x, etc) as a legend won't be included.
The final will be cumulative, covering the material of the first four exams (but not exam 5). It is entirely optional, and acts as a second chance opportunity for the material on exams 1-4 by reducing the weight on those exams. Expect questions to be focused on those aspects commonly missed on the midterms — the length of the final is limited, and it is an opportunity to demonstrate mastery of those things not previously mastered.
The short version —
If you didn't do as well as you would have liked on one or more topics from exams 1-4, completing questions in the corresponding sections on the final will reduce the weight of that portion of exams 1-4 in the final grade.
Study material that you missed and make sure you understand how to fix any problems — come to office hours! You won't be penalized for questions you don't answer on the final (the original grade from exams 1-4 will stand as is), but anything you do answer will count, even if the score on the final is lower than the original grade on the exam.
Review exams 1-4 and prioritize where to focus your attention as you may not have time to complete everything on the final — and there's only so much time for studying. Don't get too bogged down in the details of how to maximize a point here or there — stick to the big picture of where you have the most to gain, namely the higher-point topics where there's significant room for improvement.
The details —
The final will be divided into sections, each corresponding to a topic from exams 1-4:
The weight of each topic on exams 1-4 is shown in brackets e.g. [25/100].
There will not necessarily be a one-to-one match between questions on the final and questions on exams 1-4, but each section as a whole will have the same weight as the corresponding topic on exams 1-4. For example, exam 2 had one question worth 20 points involving reading ER/EER diagrams and two questions worth a total of 55 points involving creating ER/EER diagrams. The final might have four questions involving reading ER/EER diagrams (5 points each, for a total of 20 points) and three questions involving creating ER/EER diagrams (two 15 points each and one 25 points, for a total of 55 points).
Your final grade for each section/topic will be a combination of your grade for that topic on the original exam and your grade for that section on the final, weighted according to how much of that section you do on the final. At one extreme, if you skip a section on the final, your grade for that topic is your original exam grade. (The original grade is weighted 100%.) At the other extreme, if you answer all of the questions in a section on the final, your original exam grade is weighted 50% and your grade on those questions on the final is the other 50%. For an in-between example, let's say you answer two of the reading ER/EER diagrams questions (10 points possible in total), one of the 15-point creating ER/EER diagrams questions, and the 25 point ER/EER diagram question. The reading ER/EER questions will be weighted 20/30 = 2/3 on your original score and 10/30 = 1/3 on the questions answered on the final — there were 20 points possible on exam 2 and 10 points possible on the questions answered on the final. The creating ER/EER diagrams questions will be weighted 55/95 = 58% on the original and 40/95 = 42% on the questions answered on the final because there were 55 points possible on exam 2 and 15+25 = 40 points possible on the questions answered on the final.