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
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
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
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
indicates the use of different names to describe the same attribute
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
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
the _____ relationship should be rare in any relational database design
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
returns only the attributes requested, in the order in which they are requested
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
a table is also called a ______________ because the relational models creator used the two terms as synonyms
unique index
index in which the index key can have only one pointer value associated with it
links tables on the basis of an equality condition that compares specified columns of each table
an index key can have multiple _________
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
Also used to establish relationships among tables and to ensure the integrity of the data
one characteristic of generalization hierarchies is that they are implemented as _______ relationships
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
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
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
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
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
T or F: as rare as 1:1 relationships should be, certain conditions absolutely require their use
T or F: character data can contain any character or symbol intended for mathematical manipulation
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
T or F: each table in a relational database must have a primary key
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
T or F: in a natural join the column on which the join was made occurs twice in the new table
T or F: only a single attribute, not multiple attributes, can define functional dependence
T or F: RDBMSs enforce integrity rules automatically
T or F: relational algebra defines the theoretical way of manipulation table contents using relational operators
T or F: the difference operator subtracts one table from the other
T or F: the divide operation uses one single column table as the divisor and one two column table as the dividend
T or F: the idea of determination is unique to the database environment
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
T or F: the order of the rows and columns is important to the DBMS
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
T or F: the rows range of permissible values is known as its domain
T or F: the select operator yields a vertical subset of a table
T or F: you can think of a table as a persistent representation of a logical relation
dashed line
a derived attribute indicated in the chen notation that connects the attribute and an entity
a relationship is an association between ______
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
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
ideally an entity identifier is composed of _______ attribute
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
knowing the minimum and maximum number of ______ occurrences is very helpful at the application software level
the conceptual model can handle _______ relationships and multivalued attributes
the crows foot symbol with two vertical parallel lines indicates ______ cardinality
the decision to store ______ attributes in database tables depends on the processing requirements and the constraints place on a particular application
the entity relationship diagram represents the ________ database as viewed by the end user
the entity relationship model uses the associative entity to represent _____ relationship between two or more entities
the existence of _______ entity indicates that its minimum cardinality is zero
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
when the specific cardinalities are not included on the diagram in crows foot notation, cardinality is implied by the use of ________
when using crows for notation the associative entity is indicated by _________ relationships lines between the parents and associative entity showing a strong relationship
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
a weak entity must be ______ dependent
required attribute
attribute that must have a value
a ________ attribute need not be physically stored within the database
iterative process
based on repetition of processes and procedures
a _______ relationship is also known as an identifying relationship
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
identifying the attributes of entities helps in the better understanding of _______ among entities
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
the chen notation identifies a weak entity by using a double walled entity _______
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
_______ are characteristics of entities
________ are underlined in an ER diagram
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
T or F: a weak entity has a primary key that is partially or totally derived from the parent entity in the relationship
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
T or F: an entity in the entity relationship model corresponds to a table in the relational environment
T or F: composite attributes make it easier to facilitate detailed queries
T or F: connectivities and cardinalities are established by concise statements known as business rules
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
T or F: in chen notation there is no way to represent cardinality
T or F: in implementation terms, an entity is existence dependent if it has a mandatory primary key
T or F: in chen and crows foot notations an entity is represented with a rectangle containing the entity name
T or F: in the entity relationship model a table row corresponds to an entity instance
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
T or F: referential integrity and participation are both bidirectional meaning that they must be addressed in both directions along a relationship
T or F: relationships between entities always operate in one direction
T or F: software vendors have adopted the chen representation because of its compact representation
T or F: crow foot notation easily identifies multivalued attributes
T or F: crows foot notation is less implementation oriented than chen notation
T or F: ERM is dependent on the database type
T or F: existence of a mandatory relationship indicates that the minimum cardinality is 0 or 1 for the mandatory entity
T or F: process of database design is a sequential process
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
T or F: unary relationships are common in manufacturing industries
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
what a table yields that displays data redundancies
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 ______
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
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
attribute A _______ attribute B if all rows in the table that agree in value for A also agree for B
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
partial dependencies
dependencies based on only a part of a composite primary key
for most business transactional databases we should normalize relations into _______
from a structural point of view, 2NF is better than ______
from a structural point of view, 3NF is better than _____
from a system functionality point of view, _______ attribute values can be calculated when they are needed to write reports or invoices
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
in a real world environment we must strike a balance between design integrity and ________
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
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
of the normal forms ______ is mostly of theoretical interest
some very specialized applications may require normalization beyond the _________
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 ______
to generate a surrogate key microsoft access uses an _________ data type
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
an attribute that cannot be further subdivided is said to display ______
an ERD is created through an ______ process
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 _______
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
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
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
in order to meet _______ requirements, you may have to denormalize some portions of a database design
in the ______ no row may contain tow or more multivalued facts about an entity
it becomes difficult to create a suitable _______ key when the related table uses a composite primary key
the combination of _____ and ER modeling yields a useful ERD whose entities may now be translated into appropriate table structures
the price paid for increased performance through denormalization is a larger amount of ____
the problem with transitive dependencies is that they still yield data ________
the ___________ is central to a discussion of normalization
concept of keys
unnormalized tables yield no simple strategies for creating virtual tables known as ______
when a nonkey attribute is the determinant of a key attribute, the table is in 3NF but not in ______
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
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
T or F: a determinant is any attribute whose value determines other values within a column
T or F: a good relational DBMS excels at managing denormalized relations
T or F: a table is in BCNF if every determinant in the table is a foreign key
T or F: a table is in fourth normal form if it is in third normal form and has no independent multivalued dependencies
T or F: all relational tables satisfy the 1NF requirements
T or F: atomic attributes are attributes that can be further subdivided
T or F: attributes should clearly define participation, connectivity, and document cardinality
T or F: data redundancy produces data anomalies
T or F: data stored at their highest level of granularity are said to be atomic data
T or F: denormalization produces a lower normal form
T or F: dependencies that are based on only a part of a composite primary key are called transitive dependencies
T or F: dependency diagrams are very helpful in getting a birds eye view of all the relationships among a tables attributes
T or F: in order to meet performance requirements, portions of the database design may need to be occasionally denormalized
T or F: in the context of partial dependencies, data redundancies occur because every row entry requires duplication of data
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
T or F: normalization is a process that is used for changing attributes to entities
T or F: normalization is a very important database design ingredient and the highest level is always the most desirable
T or F: normalization purity is often easy to sustain in the modern database environment
T or F: normalization represents a micro view of the entities within the ERD
T or F: normalization works through a series of stages called normal forms
T or F: relational models view data as part of a table or collection of tables in which all key values must be identified
T or F: repeating groups must be eliminated by ensuring that each row defines a single entity
T or F: reporting anomalies in a table can cause a multitude of problems for managers and can be fixed through application programming
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
T or F: the advantage of higher processing speed must be carefully weighed against the disadvantage of data anomalies
T or F: combination of normalization and ER modeling yields a useful ERD whose entities can be translated into appropriate relationship structures
T or F: the objective of normalization is to ensure that each table conforms to the concept of well formed relations
T or F: unnormalized database tables often lead to various data redundancy disasters in production databases
a table can be deleted from the database using the ______ command
drop table
query that is embedded inside another query
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
SET P_INDATE=’18-JAN-2004′
in oracle the _______ command is used to change the display for a column for example to place a $ in front of a numeric value
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 ________
the special operator used to check whether an attribute value is within a range of values is ________
the special operator used to check whether an attribute value matches a given string pattern is ______
the SQL aggregate function that gives the number of rows containing non null values for a given column is ________
the SQL command that allows a user to list the contents of a table is _______
the SQL command that allows a user to permanently save data changes is _______
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
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
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
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
query used to correct SQL syntax to list the table contents for either v-code=21344 or v_code=24288
OR V_CODE=24288
what query would list all rows in which the inventory stock dates occur on or after January 20, 2016
what query would output the table contents when the value of v-code is equal to 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
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
a specialty field in mathematics, known as ______ algebra, is dedicated to the use of logical operators
wildcard character
symbol that can be used as a general substitute for other characters or commands
logical group of database objects such as tables and indexes that are related to each other
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
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
DATE() and SYSDATE are special functions that return todays date in MS access and _______ respectively
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
in a 1:M relationship a user must always create the table for the ______ side first
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
in SQL environment the word ______ covers both questions and actions
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
SQL data type DATE stores data in the _____ date format
the ______ clause of the GROUP BY statement operates very much like the WHERE clause in SELECT statement
the _____ command, coupled with appropriate search conditions, is an incredibly powerful tool that enables a user to transform data into information
the ____ condition is generally composed of an equality comparison between the foreign key and primary key of related tables
the _______ specification is used to avoid having duplicated values in a column
to delte an index, one must use the ______ command
to make SQL code more _____, most SQL programmers use one line per column definition
US state abbreviations are always two characters, so _______ (2) is a logical choice for the data type representing a state column
with the exception of the database ________ process, most RDBMS vendors use SQL that deviates little from the ANSI standard SQL
______ words are words used by SQL to perform specific functions
________ is the process the DBMS uses to verify that only registered users access the database
T or F: a database language enables the user to perform complex queries designed to transform the raw data into useful information
T or F: all SQL commands must be issued on a single line
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
T or F: an alias cannot be used when a table is required to be joined to itself in a recursive query
T or F: ansi-standard SQL allows the use of special operators in conjunction with the where clause
T or F: any changes made to the contents of a table are not physically saved on disk until you use the save command
T or F: data type selection is usually dictated by the nature of the data and by the intended use
T or F: date procedures are often more software specific than other SQL procedures
T or F: entity integrity is enforced is enforced automatically when the primary key is specified in the create table command sequence
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
T or F: mathematical operators cannot be used to place restrictions on character based attributes
T or F: most SQL implementations yield case insensitive searches
T or F: only numeric data types can be added and subtracted in SQL
T or F: oracle users can use the access QBE query generator
T or F: some RDBMSs such as microsoft access automatically make the necessary conversations to eliminate case sensitivity
T or F: SQL allows the use of logical restrictions on its inquiries such as or, and , and not
T or F: SQL is considered difficult to learn; its command set has a vocabulary of more than 300 words
T or F: SQL requires the use of the ADD command to enter data into a table
T or F: string comparisons are made from left to right
T or F: the ansi prescribes a standard SQL-the current fully approved version is known as SQL-07
T or F: ansi SQL standards are also accepted by the ISO
T or F: the check constraint used to define a condition for the values that the attribute domain cannot have
T or F: the commit command does not permanently save all changes; in order to do that you must use save
T or F: the conditional LIKE must be used in conjunction with wildcard characters
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
T or F: to list the contents of a table you must use display command
T or F: when joining three or more tables, you need to specify a join condition for one pair of tables
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
T or F: you cannot insert a row containing a null attribute value using SQL
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
T or F: dependencies that are based on only a part of a composite primary key are called transitive dependencies