2) Normalization is the most important concept, bar none. What level of normalization is the gold standard for modern database implementation? What are the requirements for a database to be at that level of normalization? Recall that levels of normalization build upon each other, so you must include the information about the prior levels.
3) There is a table (called Grades) that includes course, firstName, lastName, lastAccessDate, and all of your grades for each assignment and exam (a1, a2, a3, a4, a5, a6, a7, m1, m2, final). Assume that each of my classes has exactly the same distribution of assignments, namely that there are seven assignments, two midterms, and a final.
Write a query that will determine the number of students who have averaged at least an 83 on the two exams and have not yet submitted the final in each of my courses, sorted by the course ascending.
DO NOT generate the tables. Just provide the query. You have everything you need in the question to answer it.
4) Two important acronyms as they pertain to databases are CRUD and ACID. These stand for: Create, read, update, and deleteAtomic, consistent, isolated, durable
Define each of these terms as it pertains to databases. In your definitions, DO NOT use the word you are defining.
5) It is argued that it is better to use a satellite table to maintain a list of acceptable inputs for a field than to use a CONSTRAINT of the form IN ( ). Why argue that? Your argument should include something about a programmer's perspective and the nature of change.
6) Match the following 10 SQL keywords to their purpose.
SELECT FROM WHERE AS CONSTRAINT GROUP BY
A. Displays attributes
B. Sorts a table ascending or descending by fields
C. Combines two tables over a common attribute
D. Passes the change in one table to other connected tables
E. Makes a new something (usually table or LIKE CASCADE NULL CREATE user)
F. Used to find things of a similar structure
G. Removes an attribute, constraint, or a table from the database
H. No value has been entered
I. Identifies conditions to reduce tables in query
J. Used to aggregate selected records
K. Used to impose a condition on an aggregated query
L. Used to prevent a record from being committed if violated
M. Defines a group of items to choose from
N. Renaming a column or a table in a query
O. Selects tables
7) Match the type of join to the situation
INNER JOIN LEFT JOIN RIGHT JOIN OUTER JOIN
A. All records are returned. If they can be matched, they are. If they cannot be, they are attached to NULL values for the other table
B. The only returned records are the ones where the joined fields can be matched
C. All of the second table's records are returned, even if they cannot be matched
D. All of the first table's records are returned, even if they cannot be matched
8) Match the type of key to its definition Primary
Secondary Foreign Composite
A. The chosen identifier for a table
B. Any identifier that does not carry additional attributes not used in the identification
C. Any identifier that has multiple necessary
Candidate Super Unique Alternate parts
D. Used to link two tables together such that any value entered must be in the other table
E. Any identifier that was not chosen as the identifier of choice
F. Any group of attributes that contains an identifier
G. A natural identifier for the records, but not necessarily unique (such as name)
H. A group of attributes that are not the primary key, but are forced to be unique
9) Match the operation with its definition Selection
Cartesian product Intersection Difference Join Divide
A. Chooses the columns of interest
B. Combines tables over a common attribute
C. Returns all records from the first table not in the second table
D. Identifies the rows of interest
E. Returns all common rows between two tables
F. Combines as individual records the records of separate tables
G. Combines all rows of one table with all rows of another table, creating way more rows than originally present
H. Undoes a Cartesian product
10) Which of these is the most similar to a hierarchical database? Network
Relational Deductive Temporal
11) Which of these is nearly a drop-dead reason to choose an object-oriented database over another type?
You have multiple dimensions of interests You are interested in the role of space or time
You have objects that have natural parts that are also objects You are interested in connectivity between items
12) Let's say that a retail store designed a database that only recorded instances of products upon purchase of them. In other words, when you scanned the barcode, that's where the information of the product entered the database. Which of the three types of anomalies would that easily allow to happen?
Update anomaly - if another product is scanned, it might compromise the integrity of this one
Insertion anomaly - scanning the bar code again provides repetition, but the data is nonetheless accurate
Deletion anomaly - returning the product removes the product from the database
13) What is most important about naming fields and tables? Whatever you do, stay consistent
Only use nouns
Names must stay at an eighth grade level Use no more than eight characters
14) Which of the following is not true of a FOREIGN KEY in SQL?
The columns participating in the arrangement must have the same (or comparable) data type
The column that it references must be unique within the table it references A foreign key can have multiple attributes
The values entered in that column must be unique
15) When going over the second exam, I talked about some key terms to watch for when trying to figure out if something was an association, aggregation, or inheritance scenario. Which of these is not paired with the right type of OO idea?
any verb - association part - aggregation
is a - inheritance
All of these are correct
16) What is a front-end solution?
Building a table such that a query need not write a condition to isolate itself (so you could just dump the contents of a table and it would naturally answer the question)
An attempt to manage a database process (usually data entry to a standard) without using database programming techniques in SQL
A constraint in SQL only levied when using a query
17) Which of these counts as NULL with respect to its data type? 0 for any numerical type
" " for any text type
Day 0 (January 1, 1904) for a date type Only no entry counts as NULL
18) Why do we define even the most pedantic things in a database's documentation? Because Matt (or Dr. Dube - however you choose to refer to me) told us so Because SQL has to know what the fields are to interpret their data type
Because we need to agree upon what things are so that it is easier to combine them or work with them later
Because Access requires that information
19) What does it mean to be consistent? Data is good enough for what we are doing
Data has the proper data type and does not violate any rule of the database
Data is correct relative to the real world
Data is precise enough to represent the real world
These solutions may offer step-by-step problem-solving explanations or good writing examples that include modern styles of formatting and construction of bibliographies out of text citations and references. Students may use these solutions for personal skill-building and practice. Unethical use is strictly forbidden.
By purchasing this solution you'll be able to access the following files: