CS430 Midterm Study Guide
What is the transaction manager responsibility
Ensures that transactions request and release locks according to a suitable locking protocol
Match the responsibility to the appropriate component of the DBMS architecture. Recovery manager
Ensures transaction atomicity and durability
Rectangles
Entity sets
We create a table in SQL using the insert table command.
False
When using the GROUP BY clause, you cannot have a column in the select clause that is not in the grouping-list.
False
Which of the following defines a functional dependency?
If there were a schema (A,B), then A is able to serve as a primary key.
Some functional dependencies are said to be _______________ because they are satisfied by all relations.
Trivial
A foreign key is the primary key of another relation schema.
True
The schema developed at the conceptual design phase provides a detailed overview of the enterprise.
True
If a view relation is stored, but the DBMS makes sure that if the actual relations in the view definition change the view is kept up to date, the view is called:
a materialized view
A domain is _____________________ if the elements of the domain are considered to be indivisible units.
atomic
A ___________________ is a minimal superkey
candidate key
Do the queries below return the same result? SELECT name, employee_deptFROM employee, emp_deptWHERE employee.id = emp_dept.id; SELECT name, employee_deptFROM employee NATURAL JOIN emp_dept;
same
Which clauses of the SELECT statement can the AS clause appear?
select clause from clause
What are the three basic clauses of a SELECT statement?
select, from, where clauses
domain
set of permitted attributes
Line with arrow
shows a key constraint from the entity to the relationship
How many tuples are there in a row of a table with a column header with 3 attributes?
A 3-tuple
A language in which a user specifies a database schema is called:
A data definition language
A language that is used to express database queries and updates is called:
A data manipulation language
Why might we not want to place the restriction on an E-R diagram that only binary relationship sets be used?
A n-ary relationship set shows more clearly that several entities participate in a single relationship. There may not be a way to translate constraints on the ternary relationship in to constraints on the binary relationships. An identifying attribute may have to be created for the entity set created to represent the relationship set, increasing the complexity of the design.
Which statements about keys are true?
A primary key is a candidate key and a superkey.
Entities are described in a database by which of the following?
A set of attributes
Entity set
A set of objects of the same type that share the same properties
An instance of a relation is which of the following:
A set of tuples, also called records, in which each tuple has the same number of fields as the relation schema.
Entity
A thing or object in the real world that is distinguishable from all other objects
A relationship in a database is which of the following?
An association between entities
Derived entity
An attribute whose value is computed from the values of other attributes
Match the key and participation constraint to its appropriate description Many-to-many
An entity in A is associated with any number of entities in B. An entity in B is associated with any number of entities in A.
Match the key and participation constraint to its appropriate description One-to-many
An entity in A is associated with any number of entities in B. An entity in B is associated with at most one entity in A.
Match the key and participation constraint to its appropriate description One-to-one
An entity in A is associated with at most one entity in B, and an entity in B is associated with at most one entity in A.
Match the key and participation constraint to its appropriate description Many-to-one
An entity in A is associated with at most one entity in B. An entity in B is associated with any number of entities in A.
One very important advantage of using a DBMS is that it offers data independence. This is defined as what?
Application programs are insulated from changes in the way data is structured and stored
Value
Assigned to each attribute with an entity
The elements within a(n) ___________________ domain are not divisible.
Atomic
Items within the rectangles
Attributes
Composite attributes
Attributes that are divided into other attributes
Multi-valued attribute
Attributes that have a set of values for a particular entity
Single-valued attribute
Attributes that have a single value for a particular entity
Simple attributes
Attributes that have not been subdivided into sub-parts
Full outer join
Both the left and the right side relations are extended and filled with null for the non-existent values
What is the buffer manager responsibility?
Brings pages in from disk to memory as needed in response to read requests
Which of the following are components of the storage manager?
Buffer manager File manager Transaction manager Authorization and integrity manager
The set of all functional dependencies that can be inferred give the set F, denoted by F+ is called the _____________ of set F.
Closure
What are the two possible SQL statements that must end a transaction?
Commit Work Rollback Work
If a birthdate attribute has three components (birthDay, birthMonth, birthYear), it could be treated as a ____________________ attribute.
Composite
Date is an example of which type of attribute.
Composite attribute
Describe Application programmers
Computer professionals who write packages that facilitate access for end users
Match the responsibility to the appropriate component of the DBMS architecture. Transaction manager
Controls the execution of the transactions
Which of the following accurately describes the following: ρ(R1, πnameσage=23(Employees))
Create a relation instance called R1 containing the names of all employees that are 23 years old.
What are the components of the query processor?
DDL interpreter Query evaluation engine DML compiler
Which of the following are the data structures the storage manager implements as part of the physical system?
Data dictionary Data files Indicies
A snapshot of the data in a database at a given time is called which of the following?
Database instance
I've just added a new person to the Employee database. Which of the following have I affected?
Database instance
I've just added a new column to the Employee database so that I can keep track of pay rates for each employee. Which of the following have I affected?
Database instance.
What is the disk space manager responsibility
Deals with management of space on disk, where the data is stored
If a relation contains as attributes the birthdate of a person as well at the age of the age of that person, the age could be treated as a ____________________ attribute.
Derived
Attribute
Descriptive properties possessed by each member of an entity set
SELECT __________ forces the elimination of duplicates.
Distinct
___________________ specify the set of possible values that may be associated with an attribute.
Domain Constraints
_____________________ specify the set of possible values that may be associated with an attribute
Domain Constraints
A sequence of primitive operations that can be used to evaluate a query is called a_____________________ plan.
Execution
Sorting of relations that do not fit in memory is called_____________ sorting.
External
Place the clauses of the SELECT statement in their operational order.
FROM WHERE SELECT
A candidate key must consist of a single attribute with an atomic domain.
False
A conjunction of conditions of the form where op is a comparison operator is said to be in Bryce Codd Normal Form.
False
A marginalized temporary table is one that is created to pass intermediate tables between operators.
False
A nested query is a query that has another query embedded within it; the embedded query is called a recursive query.
False
A set of names (first, last) is an example of an atomic value.
False
A transaction is a single operation that performs multiple logical functions within a database.
False
An assertion is a predicate expressing a condition that you want to ensure never occurs within the database.
False
An attribute takes a null value when the value of the attribute is computed to be zero.
False
An object-oriented data model permits the specification of data where individual data items of the same type many have different sets of attributes.
False
BCNF can always be achieved without sacrificing losslessness or dependency preservation.
False
Data mining refers loosely to the process of semiautomatically analyzing raw input such as newspapers and field data to create reports.
False
If some but not all entities in entity set E participates in at least two relationships in relationship set R, the participation is said to be partial.
False
If you perform a relational algebra cross-product on two relation instances that contain exactly the same attributes (and their associated domains) and exactly the same values in those relation instances, you will get the same result as performing a natural join on those relation instances.
False
Indices are created by using the "GRANT index {index-name} on {relation-name} using {field-name}" command.
False
It is desirable for all users to see the entire logical model.
False
It is not possible for several attributes to have the same domain, we require they be unique.
False
It is required that the primary key of an entity set be used as an attribute of another entity set when that entity set is in total participation of a relationship set.
False
Match the result of handling an unknown value in the "and" boolean operation. false and unknown
False
Relational Algebra is a non-procedural query language.
False
SQL specifies strings by enclosing them in double quotes.
False
The "UPDATE TABLE" command is used when we wish to change the structure of a table.
False
The DDL interpreter of the query processor is responsible for query optimization.
False
The NOT NULL specification allows the insertion of a null value for the associated attribute.
False
The ORDER BY clause lists items in descending order by default.
False
The SQL phrase "ON DELETE CASCADE" is associated with referential integrity as it applies to the WHERE clause.
False
The discriminator of a weak entity set is the combination of the primary key from the identifying relationship set and enough attributes to distinguish the weak entity set.
False
The natural-join algorithm (also called the sort-merge-join algorithm) can be used to compute natural joins and equi-joins.
False
The order of how a tuple appears in a relations is extremely important as that is what determines the primary key.
False
The project operation is a tertiary operation that returns its argument relation, the predicate is was called with, and the lead attribute.
False
The result of an SQL query is a single value.
False
The schema of a relation changes when tuples are added to the database.
False
Two scan algorithms to implement a selection operation are a linear search and a bubble search.
False
Howwould you translate the following relational algebra expression: πperson-name(σcompany-name="Alpha"(works))
Find the names of all employees who work for the company named Alpha.
In the circumstances where we would like to apply an aggregate function to a group of sets of tuples, we specify this wish using which of the following clauses?
GROUP BY
Match the responsibility to the appropriate component of the DBMS architecture. Lock managers
Grants and releases locks appropriately
If we wish to constrain ourselves to relations on schema R that satisfy a set F of functional dependencies, we say that F ______________ on R.
Holds
To distinguish normal joins from outer joins, normal joins are called which of the following?
Inner Joins
What is a materialized table used for?
It is a temporary table used to pass tuples between operations.
What is the lock manager responsibility
Keeps track of request for locks and grant locks on database objects when they become available
Lines
Link attributes to entity sets and entity sets to relationship sets
I've just added a new person to the Employee database and I did not have to alter my application that uses this database. This is an example of which type of abstraction?
Logical
In SQL, we can using pattern matching to match strings. The pattern '__% Cubs' does which of the following (note, there are 2 underline characters before the %):
Matches any string of at least 2 characters followed by a space and Cubs.
A data dictionary contains ____________________, data about data.
Metadata
Match the relational algebra name to its operation. Cartesian product
Output all pairs of rows from the two input relations.
Match the relational algebra name to its operation. Natural join
Output pairs of rows from the two input relations that have the same value on all attributes that have the same name.
Match the relational algebra name to its operation. Projection
Output specified attributes from all rows of the input relation.
Match the relational algebra name to its operation. Union
Output the union of tuples from the two input relations.
Which of the following are steps involved in processing a query?
Parsing and translation Optimization Evaluation
I've just changed the indexing scheme for a certain database. The change is transparent to the rest of the database and other applications. This is an example of which type of abstraction?
Physical
Which of the following are reasons to let the DBMS manage disk space and buffer management as opposed to the OS?
Portability issues between OS OS Limitations i.e. files spanning disk Need the ability to perform special tasks such as pinning a page, pre-fetch policy, etc.
A ___________________ is a candidate key.
Primary key
Database administrators
Professionals that design and mantain the database
Pipelining is important in an execution plan because of which of the following?
Reduces the cost by passing the tables directly from one operator to another.
___________________________ constraints ensure that a value that appears in one relation for a given set of attributes also appears for a certain set of attributes in another relation.
Referential integrity
Diamonds
Relationship sets
What is the recovery manager responsibility
Responsible for maintaining a log and restoring the system to a consistent state after a crash
Match the relational algebra name to its operation. Selection
Return rows of the input relation that satisfy the predicate
Which of the following is true about SQL?
SQL is both a Data Manipulation Language (DML) and a Data Definition Language (DDL).
A database schema is depicted by which of the following?
Schema Diagram
Double line
Shows total participation of the entity in the relationship
What describes the kinds of operations or transactions that will be performed in the database?
Specification of functional requirements
A __________________ is a set of one or more attributes that uniquely identify an entity (or tuple).
Superkey
What is the File and access methods layer responsibility
Supports the concept of a file, which in DBMS is a collection of pages or a collection of records
What is the concept of referential integrity?
That a foreign key entry in one table must point to a valid record in the table associated with the foreign key.
Views are considered updatable if which of the following conditions are satisfied:
The from clause only had one database relation The select clause contains only attribute names of the relation and does not have an expressions, aggregates or distinct specification. Any attribute not listed in the select clause can be set to null.
Define Non-Procedural Query Language
The user describes the desired information
Define Procedural Query Language
The user instructs the system to perform a sequence of operations
Which of the following is the responsibility of the query optimizer?
To identify an efficient execution plan
A ____________________ is a statement that the system executes automatically as a side effect of a database modification.
Trigger
A decomposition is a lossless decomposition if for all legal database instances we project r onto R1 and R2, compute the natural join of the projection results, and we get back exactly r.
True
A functional dependency is a form of an integrity constraint.
True
A query is a statement requesting the retrieval of information.
True
A query language is a language in which the user requests information from the database.
True
A relation schema consists of a list of attributes and their corresponding domains.
True
A storage manager is a program module that provides the interface between the low-level data stored in the database and the applications programs and queries submitted to the system.
True
Aggregation is an abstraction through which relationships are treated as higher level entities.
True
An access path is a way of retrieving tuples from a table and consists of either 1) a file scan or 2) and index plus a matching selection condition.
True
An identifer that is used to rename a relation is referred to as a correlation name in the SQL standard.
True
An index on an attribute of a relation is a data structure that allows the database system to find those tuples in the relation that have a specified value for that attribute efficiently.
True
An item in the select list can be in the form of expression AS column_name, where expression is any arithmetic or string expression.
True
Atomicity refers to the requirements that either all of a transaction must occur or none of it.
True
Axioms, or rules of inference, provide a simpler technique for reasoning about functional dependencies.
True
Division is rarely implemented as a distinct operator in DBMS systems.
True
Durability refers to the fact that values within a database must survive system failures.
True
For each attribute, there is a set of permitted values called the domain or the value set.
True
For two relation instances to be union compatible, the must have the same number of fields and the corresponding fields must have the same domain.
True
If a relation r is legal under a set F of functional dependencies, we say that r satisfies F.
True
If a relation schema R is not in BCNF, we can decompose R into a collection of BCNF schemas R1, R2, R3, etc by the use of the BCNF decomposition algorithm.
True
If the evaluation of a query requires repeated passes to accomplish the partitioning it is called recursive partitioning.
True
In general, the goal of a relational database design is to generate a set of relation schemas that allows us to store information without unnecessary redundancy and yet allows us to retrieve information easily.
True
It is always possible to decompose a relation into 3NF form that is both lossless join and dependency preserving.
True
Outer join query operations can be implemented by a simple extension of join algorithms.
True
SQL allows the use of null values to indicate absence of information about the value of an attribute.
True
Several operations in a query plan may be grouped together in a pipeline in which each of the operations starts working on its input tuples even as they are being generated by another operation.
True
The natural join operation operates on two relations and produces a single relation as the result.
True
The output of a relational algebra operation is a single relation.
True
The participation of an entity set E in a relationship set R is said to be total if every entity E participates in at least one relationship in R.
True
The person that has central control over a DBMS is called a DataBase Administrator (DBA)
True
The select operation selects tuples that satisfy a given predicate.
True
View names may appear in a query any place where a relation name may appear.
True
We define a view in SQL by using the create view command.
True
We remove a relation from an SQL database using the drop table command.
True
When building an access path, an index matches a selection criteria if the index can be used to retrieve just the tuples that satisfy the condition.
True
Left outer join
Tuples from the left-hand-side relation that do not match any tuple in the right-hand-side relation are padded with nulls
Right outer join
Tuples from the right-hand-side relation that do not match any tuple in the left-hand-side relation are padded with nulls
Match the result of handling an unknown value in the "and" boolean operation. true and unknown
Unknown
Describe Naïve users
Unsophisticated users accessing the system through application program
Match the database design phase to its description Specification of functional requirements
Users describe the kinds of operations or transactions that will be performed on the data.
Describe Sophisticated / Specialized users
Users who interact with the system and/or write specialized database applications
What is the Query optimizer responsibility
Uses information about how the data is stored to provide an efficient execution plan for evaluating the query
An entity set that does not have sufficient attributes to form a primary key is called:
Weak entity set
Double line rectangle w/ associated double line diamond
Weak entity set
relational algebra
a formal query language based on a collection of operators for manipulating relations
query language
a specialized language in which queries can be posed
Union operation
binary operation that return the values present in both tables
Match the database design phase to its description Initial phase
characterize fully the data needs of the prospective database users
Column headers
collection of tables
Cartesian-product operation
combines information from any two tables
A ___________ nested query is a nested query where the inner query is dependent on the row being examined by the outer query.
correlated
Which of the following are data types the SQL standard supports?
date time timestamp
Match the database design phase to its description Logical-design phase
designer maps the high-level conceptual schema onto the implementation data model of the database system that will be used
Which of the following are representative applications of database systems?
enterprise applications telecommunications banking and finance applications universities airlines
Set-difference operations
finds tuples that are in one relation but not another
A faculty member is a person; a student is a person. These are two examples of ________________________. More than one answer may apply.
generalization the IS-A relationship
Name the two major pitfalls of a database design process.
incompleteness & redundancy
The _________________ command is used to insert data into a relation.
insert into
In which phases does the design move from an abstract data mode to the design of the database
logical design phase physical design phase
If a phoneNumber attribute could include more than one phone number, it could be treated as a ____________________ attribute.
multi-valued
One domain value that is a member of any possible domain is what?
null
Rename operations
provides a unique identifies to the results of a relational-algebra expression
____________________refers to the range of activities involved in extracting data from a database.
query processing
The types of access for a data manipulation language are:
retrieval of information insertion and deletion
____________________ are a scheme that allows authorizations to be assigned to a set of responsibilities, and people to be assigned to those responsibilities.
role
Which of the following are common subqueries - a select-from-where expression nested within another query?
set membership set comparison test for empty relations test for the absence of duplicate tuples
Typical functions of a database administrator would include which of the following?
storage structure and access-method definition security and access rights definition schema definition
Match the database design phase to its description Conceptual-design phase
the designer chooses a data model and by applying the concepts of the chosen data model, translates into a conceptual schema
The cardinality of a database relation is ______________________.
the number of relationships in which the entity is involved
Match the database design phase to its description Physical-design phase
the physical features of the database, such as internal storage structures are specified
Which of the following is information the DDL features of SQL can specify about a relation?
the schema about each relation the integrity constraints the type of an individual attribute within the relation a set of indices to be maintained for each relation
We create views for which of the following purposes?
to provide a restricted set of tables to a set of users to provide a simplified view of the database for the users
Which of the following are set operations within SQL?
union intersect except
What are the 3 mechanisms to modify a database?
update deletion insertion