The purpose of a database is to help people keep track of things.
A relational database stores data in the form of lists.
A database is a set of one or more computer programs that serves as an intermediary between the users and the database management system (DBMS).
In the general division of labor between database applications and the DBMS, the application program determines which tables need to be modified.
Which of the following problems associated with storing data in a list is avoided by storing data in a relational database? A) Maintaining the data may require changing the same data value in many locations.
B) Inconsistency when a data item is used multiple times
C) Inability to store partial data
D) Duplication of data items
E) All of the above
E) All of the above
Today almost every commercial database is based on:
the relational model
A relational database stores data in the form of:
A relational database is:
a self-describing collection of related tables.
The component of a database that makes it self-describing is the:
Which of the following would not be an example of database metadata?
Queries against records in the database tables
Every relation is a table, but not every table is a relation.
Every cell in a relation can hold only a single value.
To be considered a composite key, a key must contain at least two attributes.
Candidate keys may or may not be unique.
The primary key is used both to identify unique rows in a relation and to represent rows in relationships.
If the condition exists such that knowing the value of attribute X determines the value attribute Y, then attribute X is functionally dependent on attribute Y.
Given the functional dependency for the attributes of ENTITY1, X → (A, B, C), X is a candidate key for the relation ENTITY1 (A, B, C, X).
Any table that meets the definition of a relation is said to be in second normal form.
In the normalization process, it is not necessary to identify all the functional dependencies in a relation.
In the normalization process, if you find that every determinant in a relation is a candidate key, then you have determined that the relation is well formed.
Which of the following is not true about a relation?
A) A relation is a two-dimensional table.
B) The cells of a relation must hold a single value.
C) A relation may have duplicate column names.***
D) A relation does not have duplicate rows.
E) The order of the rows of a relation is insignificant.
Which of the following terms is synonymous with "tuple"?
A primary key is:
A) required to be unique.
B) used to represent rows in relationships.
C) a candidate key.
D) used to identify unique rows.
E) All of the above***
A candidate key is:
A) required to be unique for each row.
B) used to represent rows in relationships.
C) a candidate to be the primary key.
D) Both A and B
E) Both A and C***
STUDENT (SID, StudentName, Major, AdvisorID)
ADVISOR (AdvisorID, AdvisorName, Office, Phone)
Given the relations above such that each student is assigned to one advisor, which of the following is true?
AdvisorID is a foreign key in the Student relation.
A rule that requires that the values in a foreign key must have a matching value in the primary key to which the foreign key corresponds is called:
a referential integrity constraint
MedicineCode → (MedicineName, ShelfLife, Manufacturer, Dosage)
Given the above functional dependency, which of the following statement is not known to be true?
MedicineName is a determinant.
One important relational design principle is that:
every determinant must be a candidate key.
The first step of the normalization process is to:
identify all the candidate keys of a relation.
A table that meets the requirements of a relation is said to be in which normal form?
First normal form
SQL is a data sublanguage, not a complete programming language.
When using SQL to create a table, a column is defined by declaring, in this order: data type, column name, and optional constraints.
When using SQL to create a table, specifying the NULL property for a column indicates that only null values may be stored in that column.
When using SQL to create a table, specifying a data type of Char(10) indicates a fixed length field of 10 characters.
Data is added to a table using the SQL INSERT command.
The result for SELECT statements in SQL is a relation unless the result is a single number.
To force the DBMS to remove duplicate rows from the results of an SQL SELECT query, the keyword DISTINCT must be used.
In SQL, to refer to a range in a WHERE clause, use the WITHIN keyword.
In SQL, the order of the rows that result from a SELECT statement can be set using the SORT BY phrase.
A subquery is appropriate only if the final result contains data from a single table.
The basic idea of a join is to combine the contents of two or more relations into a new relation.
Values of existing data can be changed using SQL through the CHANGE command.
Which of the following is not true about primary keys?
A) Primary keys cannot be null.
B) Primary keys must be unique.
C) Primary keys must be a single attribute.***
D) Primary keys are used to represent relationships.
E) Primary keys can be defined using an SQL CONSTRAINT phrase.
Which of the following cannot be done using the CONSTRAINT phrase?
A) Create a single attribute primary key.
B) Define a foreign key.
C) Establish a referential integrity constraint.
D) All of the above can be done using the CONSTRAINT phrase.***
E) None of the above can be done using the CONSTRAINT phrase.
The order of the columns returned by an SQL SELECT statement are determined by the:
order they are listed in following SELECT.
Which of the following is the correct SQL clause to restrict the results of a SELECT query to only records that have a value in the range of 10 to 50 in the Hours column?
WHERE Hours BETWEEN 10 AND 50
Which of the following is the correct SQL clause to sort the results of a SELECT query in reverse-alphabetic order using the Department field?
ORDER BY Department DESC
Which of the following SQL commands would be used to remove both the data and the table structure of a table named STUDENT?
DROP TABLE STUDENT;
Which SQL keyword is used to apply conditions to restrict groups that appear in the results of a SELECT query that uses GROUP BY?
Which SQL keyword can be used in conjunction with wildcards to select partial values?
When developing a database system, the database is constructed during the implementation phase.
Use cases can be used to validate the data model, design, and implementation.
System requirements as documented by use cases, business rules, forms, reports, and queries are sufficient to design the database without using a formal data model.
The E-R Model assumes that all instances of a given entity class have the same attributes.
In crow's foot E-R notation, a circle indicates a minimum cardinality of zero.
Subtype entities may be either exclusive or inclusive.
Which of the following is not a basic element of the E-R Model?
D) Use cases***
Which of the following is not a source for user requirements during the requirements analysis stage of developing a database system?
A) Business rules
B) User interviews
D) Use cases
E) All of the above are sources for user requirements***
In the Entity-Relationship data model, all instances of an entity of a given type are grouped into:
The number of entity classes involved in a relationship is known as the relationship's:
If a weak entity is ID-dependent but not existence-dependent, it can be represented using the same techniques as a strong entity.
The key of the parent entity becomes part of the key of an ID-dependent entity.
From a pragmatic standpoint, the only important rule of normalization is that the determinant of every functional dependency must be a candidate key.
To represent a 1:N relationship in the relational model, the key of the entity on the ONE side of the relationship is placed as a foreign key in the entity on the MANY side of the relationship.
To represent a 1:1 relationship in the relational model, the key of either entity may be placed as a foreign key in the other entity.
To represent a M:N relationship in the relational model, an intersection relation is created to represent the relationship itself.
The key for an intersection relation is always the combination of the keys of the parent entities.
All recursive relationships are 1:1.
Which of the following is the first step in representing entities using the relational model?
Define a table for each entity.
Which of the following would be a reason to denormalize a relation?
The purpose of concurrency control is to ensure that one user's work does not inappropriately influence another user's work.
All actions in an atomic transaction are completed successfully or none of the actions are completed at all.
One way to prevent concurrent processing problems is to lock data that are retrieved for update.
Lock granularity refers to whether a lock is placed by the DBMS automatically or is placed by a command from the application program.
Two-phased locking can be used to ensure that transactions are serializable.
With optimistic locking, locks are first issued, then the transaction is processed, and then the locks are released.
Generally, the more restrictive the transaction isolation level, the less the throughput of database processing.
In terms of database security, a user can be assigned many roles, and a role can be assigned to many users.
The simplest and most feasible means of recovering a database is through reprocessing.
Rollback requires the use of logs, but rollforward does not.
Transactions should be written to the log before they are applied to the database itself.
An ACID transaction is one that is:
E) all of the above***
Preventing multiple applications from obtaining copies of the same record when the record is about to changed is called:
Which of the following is not a means of processing databases?
A) Internet applications using ASP
B) Internet applications using JSP
C) Applications invoking stored procedures
D) Programs coded in C#
E) All of the above are means of processing a database.***
What concurrent processing problem occurs when a transaction rereads data and finds new rows that were inserted by a different transaction since the prior read?
SQL stands for Structural Question Language.
Metadata is the user data stored in the database.
SELECT STUDENT.StudentNumber, STUDENT.StudentName,
WHERE STUDENT.StudentNumber = S12345678;
is an example of:
When used to represent a relationship, the primary key must have the same name as the corresponding foreign key.
If the condition exists such that knowing the value of attribute X determines the value attribute Y, then attribute Y is functionally dependent on attribute X.
To create a well-formed relation through normalization, every determinant must be a candidate key.
Which of the following is true about a relation?
All entries in any column must be of the same kind.
In the normalization process, if you find that every determinant in a relation is a candidate key when you have determined that:
the relation is well formed.
The SUM built-in function in SQL is used to total the values in a numeric column.
The TOP built-in function in SQL is used to find the maximum value in a numeric column.
A composite primary key can be defined using the CONSTRAINT phrase in which SQL command?
Briefly describe four components of a database system.
1. User- can enter & create data, queries.
2. Database Application- has apps that run he database.
3. Database Management System (DBMS)- manages the whole data system/apps; contains the metadata in the database.
4. Database- contains all the information needed from the other 3 components of a database system to run; self describing.
Explain the terms relation, tuple, and attribute.
Relation= Table, File
Tuple= Row, Record
Attribute= Column, Field
The number of entity classes involved in a relationshop is the cardinality of the relationship.
It is possible for an entity to have a relationship to itself.
In crow's foot E-R notation, the crow's foot indicated a maximum cardinality of one.
Which of the following activities is not performed during the implementation phase of developing a database system?
Transforming the datamodel into a database design.
An ID-dependent entity is:
A) a special type of weak entity.
B) an entity in which the identifier of the entity includes the identifier of another entity.
C) a strong entity.
D) an entity with a maximum cardinality of one.
E) Both A and B***
When applied to 1:N relationships, the term "parent" refers to the many side of the relationship.
The fundamental rule of normalization can be stated as:
every determinant must be a candidate key.
Concurrent processing of a database only poses problems when two or more processes attempt to use the same data.
What concurrent processing problem occurs when a transaction reads a changed record that has not been committed to the database?
Whether a lock applies to data at the record level, page level, table level, or database level is referred to as:
Which type of lock assumes that no conflicts will occur?
Distinguish between an entity class and an entity instance.
Entity class= describes the structure & format of the occurrences of an entity.
Ex: Student, can stand alone
Entity instance= specific occurrences of an entity within a entity class.
Ex: Mary Smith, can't stand alone
Briefly explain the strategy of two-phase locking.
1. Growing lock - when the transaction continues to request additional locks.
2. Shrinking lock - when the transaction starts to release the locks.
Explain the process of using PESSIMISTIC locking.
1. Lock required resources.
2. Reads data.
3. Process transaction.
4. Issue commit.
5. Releases Locks.