HIM 350 T2 Flashcard

data dictionary
contains at least all of the attributes and characteristics for each table in the system; provides description of all tables found within a user/designer created database
superkey
any key that uniquely identifies each row
foreign key
the primary key of one table that has been placed into another table to create a common attribute
candidate
key can be described as a minimal super key, a super key without any unnecessary attributes
secondary key
defined as a key that is used strictly for data retrieval purposes
index
an orderly arrangement used to logically access rows in a table
natural join
links tables by selecting only the rows with common values in their common attributes
inner join
only returns matched records from the tables that are being joined
logical data independence rule
application programs and ad hoc facilities are logically unaffected when changes are made to the table structures that preserve the original table values (changing order of columns or inserting columns)
bridge entity
another name for a composite entity
date attributes contain calendar dates stored in a special format known as the _________________________
julian date format
each table ________ represents an attribute
column
synonym
indicates the use of different names to describe the same attribute
homonym
indicates the use of the same attribute name to label different attributes
________ are important because they are used to ensure that each row in a table is uniquely identifiable
keys
referential integrity
dictates that the foreign key must contain values that match the primary key in the related table, or must contain null
the customer tables primary key is cus-code. the customer primary key column has no null entries, and all entries are unique. this is an example of ______________________
entity integrity
the ________ constraint can be placed on a column to ensure that every row in the table has a value for that column
not null
system catalog
system-created database whose tables store the user/designer created database characteristics and contents
the _____ relationship is the “relational model ideal” or relational database norm
1:M
the _____ relationship should be rare in any relational database design
1:1
To be considered minimally relational, the DBMS must support the key relational operators _______, ________, and ________.
select, project, join
union compatible
when two or more tables share the same number of columns and their corresponding columns share the same or compatible domains
When you define a tables primary key, the DBMS automatically creates an ____________ on the primary key columns you declared
unique index
outer joins
useful when you are trying to determine what values in related tables cause referential integrity problems
predicate logic
used extensively in mathematics, provides a framework in which an assertion can be verified as either true or false
M:N relationships
can be implemented by creating a new entity in 1:M relationships with the original entities
PROJECT
returns only the attributes requested, in the order in which they are requested
SELECT
yields values for all rows found in a table that satisfy a given condition
a primary key is a __________ key chosen to be the primary means by which rows of a table are uniquely identified
candidate
a table is also called a ______________ because the relational models creator used the two terms as synonyms
relation
unique index
index in which the index key can have only one pointer value associated with it
equijoin
links tables on the basis of an equality condition that compares specified columns of each table
an index key can have multiple _________
attributes
guaranteed access
rule of relational database that states every value in a table is guaranteed to be accessible through a combination of table name, primary key value, and column name
If one department chair can chair only one department, and one department can have only one department chair, this shows a _________ relationship
1:1
Also used to establish relationships among tables and to ensure the integrity of the data
keys
one characteristic of generalization hierarchies is that they are implemented as _______ relationships
1:1
cartesian product
product yields all possible pairs of rows from two tables
proper _________ design requires carefully defined and controlled data redundancies to function properly
data warehousing
proper use of ______ keys is crucial in controlling data redundancy
foreign
closure
use of relational algebra operators on existing relations produces new relations
system catalog
detailed system data dictionary that describes all objects within database; table names, creator, creation date, number of columns and rows, data type, etc.
to avoid nulls, some designers use special codes known as ________ to indicate the absence of some value
flags
join
power behind the relational database allowing the use of independent tables linked by common attributes
_______ relationships cannot be implemented as such in the relational model
M:N
T or F: a data dictionary is sometimes described as “the database designers database” because it records the design decisions about tables and their structures
true
T or F: a null is created when you press the enter key or tab key to move to the next entry without making a prior entry of any kind
true
T or F: as rare as 1:1 relationships should be, certain conditions absolutely require their use
true
T or F: character data can contain any character or symbol intended for mathematical manipulation
false
T or F: depending on the sophistication of the application development software, nulls can create problems when functions such as count, average, and sum are used
true
T or F: each table in a relational database must have a primary key
true
T or F: if the attribute is functionally dependent on a composite key but not on any subset of that key, the attribute is fully functional on the key
true
T or F: in a natural join the column on which the join was made occurs twice in the new table
false
T or F: only a single attribute, not multiple attributes, can define functional dependence
false
T or F: RDBMSs enforce integrity rules automatically
true
T or F: relational algebra defines the theoretical way of manipulation table contents using relational operators
true
T or F: the difference operator subtracts one table from the other
true
T or F: the divide operation uses one single column table as the divisor and one two column table as the dividend
true
T or F: the idea of determination is unique to the database environment
false
T or F: the 1:M relationship is easily implemented in the relation model by putting the foreign key of the 1 side in the table of the many side as a primary key
false
T or F: the order of the rows and columns is important to the DBMS
false
T or F: the practical significance of taking the logical view of a database is that it serves as a reminder of the simple file concept of data storage
true
T or F: the rows range of permissible values is known as its domain
false
T or F: the select operator yields a vertical subset of a table
false
T or F: you can think of a table as a persistent representation of a logical relation
true
dashed line
a derived attribute indicated in the chen notation that connects the attribute and an entity
a relationship is an association between ______
entities
composite attribute
attribute can be further subdivided to yield additional attributes
simple attribute
attribute is one that cannot be subdivided
weak entity
has a primary key that is partially or totally derived from the parent entity in the relationship
unary relationship
exists when an association is maintained within a single entity
ternary relationship
exists when three entities are associated
domain
set of possible values for a given attribute
existence dependent
entity can exist in the database only when it is associated with another related entity occurrence
complex _______ requirements may dictate data transformations and they may expand the number of entities and attributes within the design
information
ideally an entity identifier is composed of _______ attribute
one
recursive relationship
employee within an employee entity has a relationship with itself
strong entity
entity can exist apart from all of its related entities and is existence independent
if tiny college has some departments that are classified as “research only” and do not offer courses, the course entity of the college database would be _______ the department entity
optional to
in organizations that generate large number of transactions, ________ are often a top priority in database design
high processing speeds
in an ERD cardinality is indicated using _________ notation, where max is the maximum number of associated entities and min represents the minimum number of entities
(min,max)
knowing the minimum and maximum number of ______ occurrences is very helpful at the application software level
entity
the conceptual model can handle _______ relationships and multivalued attributes
M:N
the crows foot symbol with two vertical parallel lines indicates ______ cardinality
(1,1)
the decision to store ______ attributes in database tables depends on the processing requirements and the constraints place on a particular application
derived
the entity relationship diagram represents the ________ database as viewed by the end user
conceptual
the entity relationship model uses the associative entity to represent _____ relationship between two or more entities
M:N
the existence of _______ entity indicates that its minimum cardinality is zero
optional
first step in building an ERD is
creating a detailed narrative of the organizations description of operations
UML notation
in ERD modeling can be used for both conceptual and implementation modeling
To simplify the conceptual design, most higher order relationships are decomposed into appropriate equivalent _____ relationships whenever possible
binary
when the specific cardinalities are not included on the diagram in crows foot notation, cardinality is implied by the use of ________
symbols
when using crows for notation the associative entity is indicated by _________ relationships lines between the parents and associative entity showing a strong relationship
solid
cardinality
expresses the minimum and maximum number of entity occurrences associated with the occurrence of the related entity
a persons social security number would be an example of a _______ attribute
single valued
relationship degree
indicates the number of entities or participants associated with a relationship
a relationship ______ is difficult to establish if only one side of the relationship is known
classification
a weak entity must be ______ dependent
existence
required attribute
attribute that must have a value
a ________ attribute need not be physically stored within the database
derived
iterative process
based on repetition of processes and procedures
a _______ relationship is also known as an identifying relationship
strong
failure to understand the distinction between mandatory and optional _______ in relationships might yield designs in which awkward temporary rows must be created just to accommodate the creation of required entities
participation
identifying the attributes of entities helps in the better understanding of _______ among entities
relationships
in crows foot notation an optional relationship between entities is shown by drawing a _________ on the side of the optional entity
small circle (O)
participation is ________ if one entity occurrence does not require a corresponding entity occurrence in a particular relationship
optional
the chen notation identifies a weak entity by using a double walled entity _______
rectangle
the concept of relationship strength is based on how the _______ of a related entity is defined
primary key
when indicating cardinality the first value represents the ______ number of associated entities
minimum
_______ are characteristics of entities
attributes
________ are underlined in an ER diagram
identifiers
documentation
helps database designers stay on track during the design process and enables them to pick up the design thread when the time comes to modify the design
T or F: a composite identifier is a primary key composed of more than one attribute
true
T or F: a weak entity has a primary key that is partially or totally derived from the parent entity in the relationship
true
T or F: a weak relationship exists if the primary key of the related entity contains at least one primary key component of the parent entity
false
T or F: an entity in the entity relationship model corresponds to a table in the relational environment
true
T or F: composite attributes make it easier to facilitate detailed queries
false
T or F: connectivities and cardinalities are established by concise statements known as business rules
true
T or F: in a 1:M relationship, to avoid the possibility of referential integrity errors, the data of the “1” side must be loaded first
true
T or F: in chen notation there is no way to represent cardinality
false
T or F: in implementation terms, an entity is existence dependent if it has a mandatory primary key
false
T or F: in chen and crows foot notations an entity is represented with a rectangle containing the entity name
true
T or F: in the entity relationship model a table row corresponds to an entity instance
true
T or F: in the original chen notation each attribute is represented by an oval with the attribute name connected to an entity rectangle with a line
true
T or F: referential integrity and participation are both bidirectional meaning that they must be addressed in both directions along a relationship
false
T or F: relationships between entities always operate in one direction
false
T or F: software vendors have adopted the chen representation because of its compact representation
false
T or F: crow foot notation easily identifies multivalued attributes
false
T or F: crows foot notation is less implementation oriented than chen notation
false
T or F: ERM is dependent on the database type
false
T or F: existence of a mandatory relationship indicates that the minimum cardinality is 0 or 1 for the mandatory entity
false
T or F: process of database design is a sequential process
false
T or F: to implement a small database a designer must know the “1” and the “M” sides of each relationship and whether the relationships are mandatory or optional
true
T or F: unary relationships are common in manufacturing industries
true
A relational table must not contain a ___________
repeating group
a table is in 4NF if it is in 3NF and ___________
it has no multivalued dependencies
anomalies
what a table yields that displays data redundancies
1NF
table that has all key attributes defined, has no repeating groups, and all its attributes are dependent on the primary key
a table that is in 2NF and contains no transitive dependencies is said to be in ______
3NF
4NF
table where all attributes are dependent on the primary key but are independent of each other and no row contains two or more multivalued facts about an entity
BCNF
table where every determinant is a candidate key
repeating group
derives its name from the fact that a collection of multiple entries of the same type can exist for any single key attribute occurrence
partial dependency
exists when there are functional dependencies such that XY is functionally dependent on WZ, X is functionally dependent on W, and XY is primary key
transitive dependency
exists when there are functional dependencies that Y is functionally dependent on X, Z dependent on Y, and X is primary key
an atomic attribute _______
can’t be further subdivided
prime attribute
attribute that is part of a key
an example of denormalization is using a ________ denormalized table to hold report data
temporary
attribute A _______ attribute B if all rows in the table that agree in value for A also agree for B
determines
BCNF can be violated only if the table contains more than one ______
candidate key
data redundancy produces ____________________
data integrity problems
data warehouse routinely uses ______ structures in its complex, multilevel, multi source data environment
2NF
partial dependencies
dependencies based on only a part of a composite primary key
for most business transactional databases we should normalize relations into _______
3NF
from a structural point of view, 2NF is better than ______
1NF
from a structural point of view, 3NF is better than _____
2NF
from a system functionality point of view, _______ attribute values can be calculated when they are needed to write reports or invoices
derived
granularity
level of detail represented by the values in a tables row
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 dependencies
improving _______ leads to more flexible queries
atomicity
in a real world environment we must strike a balance between design integrity and ________
flexibility
multivalued dependency
situation where one key determines multiple values of two other attributes and those attributes are independent of each other
in a ________ diagram the arrows above the attributes indicate all desirable dependencies
dependency
normalization works through a series of stages called normal forms. for most purposes in business database design, ______ stages are as high as you need to go in the process
three
of the normal forms ______ is mostly of theoretical interest
DKNF
some very specialized applications may require normalization beyond the _________
4NF
the conflicts between design efficiency, information requirements, and performance are often resolved through ________
compromises that include denormalization
the most likely data type for a surrogate key is ______
numeric
to generate a surrogate key microsoft access uses an _________ data type
autonumber
when a table contains only one candidate key, _______ are considered to be equivalent
3NF and BCNF
when designing a database you should _______________
make sure that entities are in normal form before table structures are created
______________ databases reflect the ever growing demand for greater scope and depth in the data on which decision support systems increasingly rely
data warehouse
a dependency based on only a part of a composite primary key is called a __________
partial dependency
entity names
nouns that are familiar to business, should be short and meaningful, and should document abbreviations, synonyms, and aliases for each entity
all relational tables satisfy the ________ requirements
1NF
an attribute that cannot be further subdivided is said to display ______
atomicity
an ERD is created through an ______ process
iterative
any attribute that is at least a part of a key is known as a _______
prime attribute
any attribute whose value determines other values within a row is known as a _______
determinant
because partial dependency can exist only why a tables primary key is composed of several attributes, a table whose ______ key consists of only a single attribute is automatically in 2NF
primary
if database tables are treated as though they were files in file system, the _______ never has a chance to demonstrate is superior data handling capabilities
RDBMS
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
in order to meet _______ requirements, you may have to denormalize some portions of a database design
performance
in the ______ no row may contain tow or more multivalued facts about an entity
4NF
it becomes difficult to create a suitable _______ key when the related table uses a composite primary key
foreign
the combination of _____ and ER modeling yields a useful ERD whose entities may now be translated into appropriate table structures
normalization
the price paid for increased performance through denormalization is a larger amount of ____
redundancy
the problem with transitive dependencies is that they still yield data ________
anomalies
the ___________ is central to a discussion of normalization
concept of keys
unnormalized tables yield no simple strategies for creating virtual tables known as ______
views
when a nonkey attribute is the determinant of a key attribute, the table is in 3NF but not in ______
BCNF
when designing a new database structure based on the business requirements of the end users, the database designer will construct a data model using a technique such as _________
crows foot notation ERDs
normalization
process to help reduce the likelihood of data anomalies
T or F: a dependency of one nonprime attribute on another nonprime attribute is a partial dependency
false
T or F: a determinant is any attribute whose value determines other values within a column
false
T or F: a good relational DBMS excels at managing denormalized relations
false
T or F: a table is in BCNF if every determinant in the table is a foreign key
false
T or F: a table is in fourth normal form if it is in third normal form and has no independent multivalued dependencies
true
T or F: all relational tables satisfy the 1NF requirements
true
T or F: atomic attributes are attributes that can be further subdivided
false
T or F: attributes should clearly define participation, connectivity, and document cardinality
false
T or F: data redundancy produces data anomalies
true
T or F: data stored at their highest level of granularity are said to be atomic data
false
T or F: denormalization produces a lower normal form
true
T or F: dependencies that are based on only a part of a composite primary key are called transitive dependencies
false
T or F: dependency diagrams are very helpful in getting a birds eye view of all the relationships among a tables attributes
true
T or F: in order to meet performance requirements, portions of the database design may need to be occasionally denormalized
true
T or F: in the context of partial dependencies, data redundancies occur because every row entry requires duplication of data
true
T or F: it is possible for a table in 2NF to exhibit transitive dependency where the primary key may rely on one or more nonprime attributes to functionally determine other nonprime attributes
true
T or F: normalization is a process that is used for changing attributes to entities
false
T or F: normalization is a very important database design ingredient and the highest level is always the most desirable
false
T or F: normalization purity is often easy to sustain in the modern database environment
false
T or F: normalization represents a micro view of the entities within the ERD
true
T or F: normalization works through a series of stages called normal forms
true
T or F: relational models view data as part of a table or collection of tables in which all key values must be identified
true
T or F: repeating groups must be eliminated by ensuring that each row defines a single entity
true
T or F: reporting anomalies in a table can cause a multitude of problems for managers and can be fixed through application programming
false
T or F: since partial dependency can exist only if a tables primary key is composed of several attributes, if a table in 1NF has a single attribute primary key, then the table is automatically in 2NF
true
T or F: the advantage of higher processing speed must be carefully weighed against the disadvantage of data anomalies
true
T or F: combination of normalization and ER modeling yields a useful ERD whose entities can be translated into appropriate relationship structures
false
T or F: the objective of normalization is to ensure that each table conforms to the concept of well formed relations
true
T or F: unnormalized database tables often lead to various data redundancy disasters in production databases
true
a table can be deleted from the database using the ______ command
drop table
subquery
query that is embedded inside another query
alias
alternate name given to a column or table in any SQL statement
according to the rules of precedence, which of the following computations should be completed first?
a) performing additions and subtractions
b) performing power operations
c) performing operations within parentheses
c) 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
an example of a command a user would use when making changes to a product table is
UPDATE PRODUCT
SET P_INDATE=’18-JAN-2004′
WHERE P_CODE=’13-Q2/P2′
in oracle the _______ command is used to change the display for a column for example to place a $ in front of a numeric value
format
the query to join the p_descript and p_price fields from the product table and the v_name, v_areacode, v_phone, and v_contact fields from the vendor table where the values of v_code match is
where product.v_code=vendor. v_code
the query to join the p_descript and p_price fields from the product table and the v_name, v_areacode, v_phone, and v_contact fields from the vendor table where the values of v_code match and the output is ordered by the price is
where product.v_code=vendor.v_code; order by product.p_price
the special operator used to check whether a subquery returns any rows is ________
exists
the special operator used to check whether an attribute value is within a range of values is ________
between
the special operator used to check whether an attribute value matches a given string pattern is ______
like
the SQL aggregate function that gives the number of rows containing non null values for a given column is ________
count
the SQL command that allows a user to list the contents of a table is _______
select
the SQL command that allows a user to permanently save data changes is _______
commit
the SQL data manipulation command HAVING ________
restricts the selection of grouped rows based on a condition
the SQL query to output the contents of the employee table sorted by last name, first name, and initial is
order by emp_lname, emp_fname, emp_initital
the ________ command is used to restore the database to its previous condition
rollback
the _______ command would be used to delete the table row where the p_code is ‘BRT-345’
delete from product where p_code=’BRT-345′
the ______ constraint assigns a value to an attribute when a new row is added to a table
default
UPDATE table name
*****
[WHERE condition list];
The _______ command replaces the ***** in the syntax of the update command shown above
SET columnname=expression
when a user issues the DELETE FROM table name command without specifying a WHERE condition, _______
all rows will be deleted
used to select partial table contents
SELECT ;columns(s); FROMWHERE
query used to list a unique value for v-code where the list will produce only a list of those values that are different from one another
SELECT DISTINCT V_CODE FROM PRODUCT
query used to correct SQL syntax to list the table contents for either v-code=21344 or v_code=24288
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
FROM PRODUCT
WHERE V_CODE=21344
OR V_CODE=24288
what query would list all rows in which the inventory stock dates occur on or after January 20, 2016
WHERE P_INDATE;=’20-JAN-2016′
what query would output the table contents when the value of v-code is equal to 21344
WHERE V_CODE=21344
what query would output the table contents when the value of v_code is not equal to 21344
WHERE V_CODE ;; 21344
what query would output table contents when the value of the character field p_code is alphabetically less than 1558-QW1
WHERE P_CODE ;’1558-QW1′
what query would use the given columns and column aliases from the product table to determine the total value of inventory held on hand
SELECT P_DESCRIPT, P_QOH, P_PRICE, PQOH*P_PRICE
FROM PRODUCT
a common practice is to create an _______ on any field that is used as a search key in comparison operations in a conditional expression, or when a user wants to list rows in a specific order
index
a specialty field in mathematics, known as ______ algebra, is dedicated to the use of logical operators
boolean
wildcard character
symbol that can be used as a general substitute for other characters or commands
schema
logical group of database objects such as tables and indexes that are related to each other
join
performed when data are retrieved from more than one table at a time
cascading order sequence
multilevel ordered sequence that can be created easily by listing several attributes, separated by commas, after the order by clause
subquery
also known as a nested query or inner query, is a query that is embedded inside another query
an alias is especially useful when a table must be joined to itself in an ______ query
recursive
DATE() and SYSDATE are special functions that return todays date in MS access and _______ respectively
oracle
if a user adds a new column to a table that already has rows, the existing rows will default to a value of ________ for the new column
null
in a 1:M relationship a user must always create the table for the ______ side first
1
in an INSERT command a user can indicate just the attributes that have required values by listing the _____ inside parentheses after the table name
attribute names
in SQL all _________ expressions evaluate to true or false
conditional
in SQL environment the word ______ covers both questions and actions
query
rows can be grouped into smaller collections quickly and easily using the ______ clause within the select statement
group by
basic SQL vocabulary has fewer than _____ words
100
SQL data type DATE stores data in the _____ date format
julian
the ______ clause of the GROUP BY statement operates very much like the WHERE clause in SELECT statement
having
the _____ command, coupled with appropriate search conditions, is an incredibly powerful tool that enables a user to transform data into information
select
the ____ condition is generally composed of an equality comparison between the foreign key and primary key of related tables
join
the _______ specification is used to avoid having duplicated values in a column
UNIQUE
to delte an index, one must use the ______ command
DROP INDEX
to make SQL code more _____, most SQL programmers use one line per column definition
readable
US state abbreviations are always two characters, so _______ (2) is a logical choice for the data type representing a state column
CHAR
with the exception of the database ________ process, most RDBMS vendors use SQL that deviates little from the ANSI standard SQL
creation
______ words are words used by SQL to perform specific functions
reserved
________ is the process the DBMS uses to verify that only registered users access the database
authentication
T or F: a database language enables the user to perform complex queries designed to transform the raw data into useful information
true
T or F: all SQL commands must be issued on a single line
false
T or F: although SQL commands can be grouped together on a single line, complex command sequences are best shown on separate lines with space between the SQL command and the components
true
T or F: an alias cannot be used when a table is required to be joined to itself in a recursive query
false
T or F: ansi-standard SQL allows the use of special operators in conjunction with the where clause
true
T or F: any changes made to the contents of a table are not physically saved on disk until you use the save command
false
T or F: data type selection is usually dictated by the nature of the data and by the intended use
true
T or F: date procedures are often more software specific than other SQL procedures
true
T or F: entity integrity is enforced is enforced automatically when the primary key is specified in the create table command sequence
true
T or F: if you have not yet used the commit command to store the changes permanently in the database, you can restore the database to its previous condition with the rollback command
true
T or F: mathematical operators cannot be used to place restrictions on character based attributes
false
T or F: most SQL implementations yield case insensitive searches
false
T or F: only numeric data types can be added and subtracted in SQL
false
T or F: oracle users can use the access QBE query generator
false
T or F: some RDBMSs such as microsoft access automatically make the necessary conversations to eliminate case sensitivity
true
T or F: SQL allows the use of logical restrictions on its inquiries such as or, and , and not
true
T or F: SQL is considered difficult to learn; its command set has a vocabulary of more than 300 words
false
T or F: SQL requires the use of the ADD command to enter data into a table
false
T or F: string comparisons are made from left to right
true
T or F: the ansi prescribes a standard SQL-the current fully approved version is known as SQL-07
false
T or F: ansi SQL standards are also accepted by the ISO
true
T or F: the check constraint used to define a condition for the values that the attribute domain cannot have
false
T or F: the commit command does not permanently save all changes; in order to do that you must use save
false
T or F: the conditional LIKE must be used in conjunction with wildcard characters
true
T or F: the COUNT function is designed to tally the number of non-null values of an attribute and is often in conjunction with the distinct clause
true
T or F: to list the contents of a table you must use display command
false
T or F: when joining three or more tables, you need to specify a join condition for one pair of tables
false
T or F: you can select partial table contents by naming the desired fields and by placing restrictions on the rows to be included in the output
true
T or F: you cannot insert a row containing a null attribute value using SQL
false
used to select partial table contents
T or F: it is possible for a table in 2NF to exhibit transitive dependency where the primary key may rely on one or more nonprime attributes to functionally determine other nonprime attributes
ture
T or F: dependencies that are based on only a part of a composite primary key are called transitive dependencies
false