CSE 480 Exam Sample Questions (HW + Sample Exam + Video Questions)

With 3 transactions, how many possible serial schedules are there? A) 3 B) 6 C) 9 D) Unknown

6

A case statement is very similar to which of the following Python statements? A) An if statement. B) A while statement. C) A function definition. D) A print statement.

A

Although SQLite doesn't support stored procedures, what benefit do they provide? A) They allow database clients to execute code in a very efficient manner. B) They allow machine code to be stored in tables. C) They monitor for unauthorized access of the database via multi-factor authentication. D) They keep track of how the database has changed over time so that restores can be performed. E) None of the above.

A

Are both decompositions in BCNF? R = {A,B,C} F = {A -> B, B -> C} Decomposition 1) R1={A,B}, R2={B,C} Decomposition 2) R1={A,B}, R2={A,C} A) Yes, all sub-relations are in BCNF B) No, left sub-relations are not both in BCNF C) No, right sub-relations are not both in BCNF D) No, (B -> C) isn't preserved

A

How much memory does a view take up? A) Very little memory. B) Very little memory, until it is accessed for the first time. C) Its memory footprint is dependent on its number of rows in it. D) Its memory grows each time it is used.

A

If I wanted to disallow insertions into the courses_taught of instructor_ids that weren't already in the instructors table, what should I do? A) Make courses_taught have a foreign key constraint. B) Make instructors have a foreign key constraint. C) Make courses_taught have a primary key constraint. D) Make instructors have a primary key constraint.

A

If your database needs to preserve functional dependencies, which decomposition is best? A) 3NF B) BCNF C) 4NF D) None

A

Is decomposition into Hair(name, hair_color, eye_size) and Emotion(hair_color,tsundere) a valid 3NF decomposition? AnimeTraits(name, hair_color, tsundere, eye_size) FDs= name -> hair_color hair_color -> tsundere A) Yes B) No, it isn't dependency preserving C) No, the intersection isn't a superkey for a subrelation D) No, the union of attributes isn't the original relation

A

Is the decomposition into the tables: Physical(name,hair_color,eye_size) and Emotional(hair_color,tsundere) a lossless join? Animetraits(name, hair_color, tsundere, eye_size) FDs = name->hair_color hair_color->tsundere A) Yes B) No, it fails the "Drop Attributes" Condition C) No, it fails the "Intersection is a Superkey" Condition D) It fails both conditions

A

Is the following relation in Boyce-Codd Normal Form (BCNF)? R = {A,B,C,D} FDs = AB -> CD A -> B A) Yes, the relation is in BCNF B) No, (AB->CD) is a violation of BCNF C) No, (A->B) is a violation of BCNF D) No, both FDs are violations of BCNF

A

SELECT * FROM shows ORDER BY name, FavoriteShow; What should the order of the rows be? A) Ascending by name (Ascending by FavoriteShow for ties) B) Descending by name (Descending by FavoriteShow for ties) C) Ascending by FavoriteShow (Ascending by name for ties) D) Descending by FavoriteShow (Descending by name for ties)

A

Views are basically read-only tables constructed from a SELECT query, however, if you want to be able to add new data to a view, which of the following options enable that? A) Creating an INSTEAD OF trigger. B) Creating a new aggregate function. C) Creating a new table. D) Creating a new collation.

A

What is the Big-O notation for a simple SELECT statement? A) n B) log(n) C) log(n) + log(n) D) n^2

A

What is the difference b/w Record, Tuple, and Row? A) Nothing B) Whether the order of the elements matter C) Some specify columns and others specify rows

A

What is the primary motivation for a database system chooses to not abide by ACID principles? A) To enable it to be distributed across multiple sites. B) So that it can handle multiple transactions simultaneously. C) To prevent multiple users from accessing the same data. D) All of the above.

A

Which of the following is NOT true about foreign keys? A) A foreign key of a table must be a primary key of the referenced table. B) A foreign key value can be NULL. C) A foreign key value can be UNIQUE. D) A foreign key is a constraint on what values a table can hold.

A

How long do user defined functions, aggregates, collations, adapters, and converters last in SQLite? A) For the lifetime of the database B) For the duration of the connection C) For the duration of the statement

B

How many rows are returned from a CROSS JOIN? (hint: Cartesian product) A) 2 * (m * n) B) m * n C) m + n D) m ^ n

B

Is it possible for us to write a FULL OUTER JOIN in this class? A) FULL OUTER JOIN is provided in SQLite B) We can write a manual FULL OUTER JOIN with a complex SQL statement within SQLite C) It is impossible for us to write a FULL OUTER JOIN in SQLite

B

Is normalization a good thing? A) Always, otherwise database is broken B) Yes, if the properties of the normal form are desirable C) No, they slow down development and cost programmer time

B

What is the Big-O notation for a lookup with a covering index? A) n B) log(n) C) log(n) + log(n) D) It is log(n), but it is a faster log(n) than B

B

What is the Big-O notation for a lookup with an index? A) n B) log(n) C) log(n) + log(n) D) log(n) * log(n)

B

When can you use variables and CASE statements? A) In any legal SQL statement B) Only in stored procedures C) Only in stored procedures and triggers D) Can be used anywhere

B

When is a LEFT OUTER JOIN the same as an INNER JOIN? A) Never B) When the left table's rows always match C) When the left table's rows don't have NULLs

B

When should we use a FOREIGN KEY constraint? A) When you need to enforce matching values B) When you refer to values in a separate table C) Never

B

Which of the following are downsides to using indices A) They only improve performance on SELECT statements that have WHERE clauses. B) They add considerable overhead to SQL statements that alter a table. C) They require O(n^2) time to construct. D) They can only be created for numeric attributes.

B

Which of the following never need to be indexed? A) The Foreign Key B) rowid C) Columns that are INTEGER, NOT NULL, and UNIQUE D) Columns that have the TEXT datatype

B

If your database has lots of redundancy due to Functional Dependencies, which decomposition is best? A) 3NF B) BCNF C) 4NF D) None

B (or C)

Order from Least to Greatest the number of rows returned from each kind of JOIN A) LEFT OUTER JOIN B) INNER JOIN C) CROSS JOIN

B,A,C

If a column is a PRIMARY KEY, what properties does it have? (Choose all that apply) A) INTEGER B) UNIQUE C) NOT NULL

B,C

What data structure should we use to hold the rows of a table (Choose all that apply) A) set B) list C) bag (multiset) D) tuple

B,C

Why would you want to define a collation as a column attribute instead of an ORDER BY clause? (Choose all that apply) A) There is no difference b/w the two B) So that future ORDER BYs don't need to specify it explicitly C) If we want to change behavior of comparison operators

B,C

Which of the following values would match LIKE '%c%' (Choose all that apply) A) Cat B) nice C) canary D) Nic E) Python

B,C,D

How is an aggregate function different from a normal function? (Choose all that apply) A) Aggregate functions get called multiple times B) Aggregates can have multiple parameters C) Aggregates can store state and return summaries D) Aggregates are more complex

C,D

Order the statements in the order they should be in for a SELECT statement (each separated by a comma) WHERE ORDER BY ; SELECT HAVING FROM GROUP BY LIMIT

SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, ;

In SQLite, 1 is the same as 1.0 (T/F)

T

Schemas are NOT required for which of the following storage formats? (Choose all that apply) A) CSV B) JSON C) XML D) SQL

A, B, C

Which of the following types can be used to store date times? (Choose all that apply) A) TEXT B) REAL C) INTEGER

A, B, C

How is a stored procedure different from a function? (Choose all that apply) A) Stored procedures can return multiple values B) Stored procedures need the CALL keyword C) Stored procedures can have multiple parameters

A,B

What is the advantage to SQL not being Turing-complete? (Choose all that apply) A) Easier to optimize B) Easier to write C) Actually, SQL is Turing-complete

A,B

Which of the following are reasons to use parameterized queries? (Select all that apply) A) They are safer to use that raw text insertion due to automatic escaping of characters. B) They can automatically convert some data types when inserted into the database. C) They can automatically convert some data types when retrieved from the database. D) They can enforce create tables as needed to hold the necessary data.

A,B,C

Select the properties that enable an index on a column to improve query response time (Choose all that apply). A) The ability to binary search for values. B) Values will be sorted according to the ORDER BY clause. C) Values that compare equal to each other are adjacent. D) The indexed values are made the PRIMARY KEY of the table.

A,C

Which column(s) would make for the best primary key? (Choose all that apply) A) Social Security Number B) First name and Last name C) Student Number D) MSU Net ID

A,C,D

Do the columns that are returned by an EXISTS subquery matter? A) Only the first value matters because that tells that there is an existing query for our search B) Yes because you can only have one value that is returned C) No, we just want to know if any rows are returned

C

How many NULLs should be allowed under a UNIQUE column? A) Zero B) One C) As many as we want D) Depends on data type of column

C

If your database has lots of redundancy due to MVDs, which decomposition is best? A) 3NF B) BCNF C) 4NF D) None

C

In the following code, what happens if the commit is removed? conn = sqlite3.connect("data.db") conn.execute( "CREATE TABLE pets(owner TEXT, pet TEXT);" ) conn.execute("INSERT INTO pets VALUES (?, ?);", ("Josh", "Mal")) conn.commit() # What happens if this line is removed? conn.close() A) No data.db file is created. B) A data.db file is created, but no table is created within the database. C) A data.db file is created, but no row was inserted into the table. D) A data.db file is created, and the row was inserted into the table.

C

Is decomposition into Hair(name, hair_color), HotHead(name, tsundere), and BigEyed(name,eye_size) dependency preserving? AnimeTraits(name, hair_color, tsundere, eye_size) FDs= name->hair_color hair_color->tsundere A) Yes B) No, (name->hair_color) isn't preserved C) No, (hair_color->tsundere) isn't preserved

C

What is the Big-O notation for sorting without index? A) n B) log(n) C) n * log(n) D) n^2

C

What should the following query return for SQLite? SELECT date('12:34') A) 0000-01-01 B) 1970-01-01 C) 2000-01-01 D) Error, no date specified

C

Which of the following principle ensures that transactions don't interfere with each other as they are being performed? A) Atomicity B) Consistency C) Isolation D) Durability

C

If you have multiple indices, which one is faster? A) The index with the most rows B) The index with the least duplicates C) The index that is sorted D) The result is situational, depends on the constraint value being checked

D

If your database needs to preserve MVDs, which decomposition is best? A) 3NF B) BCNF C) 4NF D) None

D

In an XML Schema, what is the distinction between an xsd:simpleType and xsd:complexType? A) simpleTypes can only be a string, whereas a complexTypes can be a custom type B) simpleTypes can only be included as part of a complexType, but never on their own. C) simpleTypes can have additional restraints placed on them, complexTypes can not. D) None of the above

D

What does the || operator do in SQLite? A) Concatenates two TEXT values B) Combines two TEXT values C) Adds two TEXT values D) All of the Above

D

What is the Big-O notation for sorting with an index? A) n B) log(n) C) n * log(n) D) It is n * log(n), but is a faster n * log(n) than C

D

What should happen if a trigger's "on" table is dropped? A) ERROR, you need to DROP TRIGGER first B) The trigger should be dropped automatically C) The trigger should be dropped, but only if it doesn't refer to other tables D) We really shouldn't be dropping tables, instead copy our old data to a new table

D

With 3 transactions, how many possible serializable schedules are there? A) 3 B) 6 C) 9 D) Unknown

D

What order should tuples be returned if there is no "ORDER BY" statement? A) Values ascending B) Values descending C) Insertion Order Ascending D) Insertion Order Descending E) None of the above

E

Which of the following database architectures is designed to store behavior instead of just static data? A) File Based Storage B) Hierarchical Databases C) Network Data Model D) Relational Databases E) Object Oriented Databases

E