Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

CSC 230 Final Exam Study Guide – Fall 2023

My goal when writing the exam: 1 hour

Allowed time: 2h 20min (including travel)

Available Exam Times

Students with accommodations through ODS have a different time table and should have received instructions from the registrar’s office.

Instructions

Here are the exam instructions. Please ask any questions about these instructions ahead of time!

YOU MAY USE A CALCULATOR AND YOUR NOTES ON THIS EXAM.

YOU MAY NOT USE THE TEXTBOOK, A COMPUTER, OR
ANY OTHER INFORMATION SOURCE BESIDES YOUR PAGES OF NOTES.

THIS EXAM CONSISTS OF NINE (9) PAGES WITH FOUR (4) MULTI-PART QUESTIONS

Goal

There are two goals to having a final exam in this course:

1. Cement the major concepts that you have learned so that if you need it later, you remember how to get started.

2. Give you an opportunity to synthesize material across units into a broader context

Structure

The exam is made up of four multi-part problems that will each draw on your knowledge of one or more topics we covered this semester. Possible question topics include the areas covered by the assignments, as well as the content from lectures 18-20.

The four question topics are:

PROBLEM 1: Designing and Querying a Database (HW 1, HW 2, HW5)

PROBLEM 2: Normalization and Schema Design (HW 3)

PROBLEM 3: Indexing (HW 6)

PROBLEM 4: Queries and Transactions (L18-20)

Notes:

- Sub-problems range from being straightforward math (e.g., calculate x) to requiring you to make a short argument (e.g., this diagram can/cannot show X because…)  

- Sub-problems also range in difficulty. Some are straight-forward application of concepts (e.g., HW6 where you could get points for calculating mods correctly and converting to binary), but some require you to synthesize concepts across units. I tried to organize them in a logical order within each problem to help you think through the problem.

- While studying, I suggest trying to get a “big picture” of the material so that you are prepared to think about the major principles you learned from each unit in the context of another unit. My intention in writing this exam was to give you opportunities to show that you can integrate concepts.

Topics to Review

- Database design (HW1 & HW 2)

o Drawing an ER diagram (e.g., how to represent entities, attributes, relationships, multiplicity of relationships)

o Converting an ER diagram to a schema

o Identifying and indicating keys

o Writing SQL queries using SELECT FROM WHERE

o Writing SQL queries using aggregation (MIN, MAX, AVG, COUNT, GROUP BY)

o Writing SQL queries using joins

o Writing SQL queries that use subqueries

- Normalization (HW3)

o Identifying closures of attribute sets based on FDs

o Definitions of different types of keys (minimal key, candidate key, superkey, composite key, etc.)

o Identifying candidate keys

o Normal Forms: 1NF, 2NF, 3NF, BCNF

o How to normalize for 3NF and BCNF

- Set Theory & Operations (HW4)

o What is a set?

o Set logic

o Set membership (e.g., treating items as objects so you cannot have duplicates)

- Triggers & Constraints (HW5)

o What are some different types of constraints?

o How do you apply constraints?

o What constraints should be applied to primary keys? Foreign keys?

o What is referential integrity?

o What is a trigger?

- Indexing (HW6)

o What is the goal of indexing?

o How do you construct an index using a B+ tree?

o How do you construct an index using a hash map?

o How do you insert and remove keys from each of these index types?

o What is an extensible hash map? What are the pros and cons of extensibility?

o Think about what a good versus bad index would look like in the context of B+ trees and hash maps.

o Note: For hash maps, remember that we chose to represent the keys in 3-bit binary because of the hash function we were using (mod 8—keys range from 0-7 and we can represent these in 3-bit binary as 000 - 111). Think about how the hash function used affects how you approach the problem.

- Query processing (L18)

o How do you compute different query types?

o What needs to be read into memory for different operations?

o How do you calculate the amount of memory you need?

o Why are some algorithms one vs two pass?

o What kinds of operations are one-pass? Two-pass?

o Note: don’t try to memorize memory requirements etc. Instead, think about how to think about an operation in terms of query processing: what do you need to read in, what do you need to write out, and how do you determine the cost.

- Logging and recovery (L19)

o Reading a transaction log

o Determining which transactions completed (either COMMIT or ABORT) versus needs to be recovered

o Back-tracking from a particular time-point in the log (e.g., what happens if the computer crashes at log line 10?)

o Determining the state of a particular object based on a log (e.g., A gets multiplied by 2 on line X, but is this preserved after a crash at line Y?)

- Concurrency control (L20)

o A.C.I.D.

o Reading a schedule

o Drawing a precedence graph

o Determining conflict serializability via a proof

o Considering the status of different transactions at different points in a schedule