Final Exam Flashcards
What happens when there is a violation of normal form to fix it?
Usually ends up in creating a new table
if something is in first normal form is it garenteed to be in second?
yes
if its a has a composite key which normal form is in play
2nd and boyce codd
- The__________ determines the common attribute or attributes by looking for identically named attributes and compatible data types.
natural join
When using a subquery, the output of a(n)_________ query is used as the input for the outer query.
inner
- The _________ clause is used to restrict the output of a GROUP BY query by applying a conditional criteria to the grouped rows.
HAVING
- The IN subquery uses a(n)___________ operator.
equality
- The use of the____________ operator allows you to compare a single value with a list of values returned by the first subquery (sqA) using a comparison operator other than EQUALS.
ALL
- A(n)_____________subquery is a subquery that executes once for each row in the outer query.
correlated
- The___________ statement combines the output of two SELECT queries.
UNION
- A(n)____________ query can be used to produce a relation that retains the duplicate rows.
UNION ALL
- The____________ statement can be used to combine rows from two queries, returning only the rows that appear in both sets.
INTERSECT
- If the DBMS does not support the INTERSECT statement, one can use a(n)__________ subquery to achieve the same result.
IN
- A(n)___________view is a view that can be used to update attributes in the base table(s) that are used in the view.
updatable
- A(n)____________ routine pools multiple transactions into a single batch to update a master table field in a single operation.
batch update
- A row-level trigger requires use of the___________ keywords and is executed once for each row affected by the triggering statement.
FOR EACH ROW
- When the critical application code is isolated in a single program, ____________ improves.
maintenance and logic control
_____ is the term used to describe an environment in which the SQL statement is not known in advance and is generated at run time.
Dynamic SQL
A stored function uses the ____ statement to return a value.
return
___is the process that establishes the need for an information system and its extent.
Systems analysis
the traditional systems Development Life Cycle (SDLC) phases are ___, detailed systems design, implementation , and maintenance.
planning
an initial assessment of the information flow-and-extent requirements must be made during the __ portion of the Systems development life cycle(sdlc)
planning
After testing is concluded, the final __ is reviewed and printed and end users are trained.
documentation
the advent of very sophisticated application generators and ___ has substantially decreased coding and testing time
debugging tools
The system and operational costs are addressed by the ___ during the planning phase of the Systems development life cycle SDLC
feasibility study
___ are usually provided by the database management system to check for access violations
Audit trails
The___ describes the general conditions in which a company operates, it organizational structure, and its mission
company situation
the system’s ____ defines the extent of the design according to operational requirements.
scope
The proposed system is subject to limits known as ____, which are external to the system
boundaries
Making sure that the final product meets user and system requirements is the most critical ___ phase.
Database Life Cycle(DBLC)
Programmers use database tools to ___ the applications during coding of the programs.
prototype
___allows the assignment of access rights to specific authroized users.
Password security
The ___ stage uses data modeling to create an abstract database structure that represents real-world objects in the most realistic way possible.
conceptual design
The first step in ___ is to discover the data element characteristics
conceptual desing
A designer must have a thorough understanding of the company’s data types, extent and uses in order to develop an accurate____
data model
From a database point of view, the collection of data becomes meaningful only when the __ are defined
Business rules
a ___ is a brief and precise narrative of a policy, procedures, or principle within a specific organization’s environment
business rule
Because real world database design is generally done by teams, the database deign is probably divided into major components known as ____.
modules
A(n)_____ is an information system component that handles a specific business function, such as inventory, orders, or payroll
module
- The price paid for increased performance through Denormalization is a larger amount of ______.
redundancy
- ______ is a process to help reduce the likelihood of data anomalies.
Normalization
- Any attribute that is at least part of a key is known as a ______.
prime attribute
- A dependency based on only a part of a composite primary key is called a ______.
partial dependency
- Dependencies can be identified with the help of a dependency ______.
diagram
- The problem with transitive dependencies is that they still yield data ______.
anomalies
- The ______ is central to a discussion of normalization.
concept of keys
- All relational tables satisfy the _______ requirements.
1NF
- Data redundancies occur from ______ of data on every row entry.
duplication
Because a partial dependency can exist only when a table’s primary key is composed of several attributes, a table whose ______ key consists of only a single attribute is automatically in 2NF once it is in 1NF.
primary
Any attribute whose value determines other values within a row is known as a ______.
determinant
An attribute that cannot be further subdivided is said to display ______.
atomicity
______ refers to the level of detail represented by the values stored in a table’s row.
Granularity
In a real-world environment, changing granularity requirements might dictate changes in primary key selection, and those changes might ultimately require the use of ______ keys.
surrogate
It becomes difficult to create a suitable ______ key when the related table uses a composite primary key.
foreign
When a non-key attribute is the determinant of a key attribute, the table is in 3NF but not in ______.
4NF
- The conflicts between design efficiency, information requirements, and processing speed, are often resolved through______
compromises that include denormalizations
The combination of ______ and ER modeling yields a useful ERD, whose entities may now be translated into appropriate table structures.
normalization
An ERD is created through a (n) ______ process.
iterative
According to the data-modeling checklist, ______ should be nouns that are familiar to business, should be short and meaningful, and should document abbreviations, synonyms, and aliases for each entity.
entity names
- A ____ is a block of code that is stored and executed at the DBMS server
A. PSM
- Cursor style processing involves retrieving data from the cursor one row at a time
True
- The SQL aggregate function that gives the total of all values for a selected attribute in a given column
Sum
- If a table has multiple candidate keys and one of those candidate keys is a composite key the table can have ____ based on this composite candidate key even when the primary key chosen is a single attribute.
Partial dependency
- The ___ design is the process of selecting the data storage and the data access characteristics of the database
Physical design
- Automating business procedures and automatically maintaining data integrity and consistency are trivial in a modern business environment
False
- The special operator used to check whether an attribute value is within a range of values is ______.
a. BETWEEN
- The ______ special operator is used to check whether an attribute value is null.
IS NULL
- The special operator used to check whether an attribute value matches a given string pattern is ______.
LIKE
- The special operator used to check whether a subquery returns any rows is ______.
EXISTS
- The ______ command is used with the ALTER TABLE command to modify the table by deleting a column.
a. DROP
A table can be deleted from the database by using the ______ command.
a. DROP TABLE
- The SQL query to output the contents of the EMPLOYEE table sorted by last name, first name, and initial is ______.
b. SELECT EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_AREACODE, EMP_PHONE FROM EMPLOYEE ORDER BY EMP_LNAME, IMP_FNAME, IMP_INITIAL;
- The SQL aggregate function that gives the number of rows containing non-null values for a given column is ______.
COUNT
- The SQL aggregate function that gives the arithmetic mean for a specific column is ______.
AVG
The SQL aggregate function that gives the arithmetic mean for a specific column is ______.
AVG
The SQL data manipulation command HAVING:
b. Restricts the selection of grouped rows based on a condition
The ______ constraint assigns a value to an attribute when a new row is added to a table.
DEFAULT
. The syntax for creating an index is ______.
b. CREATE [UNIQUE] INDEX indexname ON tablename (column1 [, column2]);
According to the rules of precedence, which of the following computations should be completed first?
Performing operations within parentheses
All changes in a table structure are made using the ______ command, followed by a keyword that produces the specific changes a user wants to make.
ALTER TABLE
In the SQL environment, the word ______ covers both questions and actions
query
The basic SQL vocabulary has fewer than ______ words
100
A (n) ______ is a logical group of database objects, such as tables and indexes that are related to each other.
schema
With the exception of the database ______ process, most RDBMS vendors use SQL that deviates little from the ANSI standard SQL
creation
U. S. state abbreviations are always two characters, so _____ (2) is a logical choice for the data type.
CHAR
True/False
Date procedures are often more software-specific than other SQL procedures.
True
