Database Systems - Chapter 3, Database Systems: 11e Chapter 5, Database Systems: 11e Chapter 7, Database Systems: 11e Chapter 8, Database Systems Chapter 6, Database Systems - Jukic - Chapter 2, Database Systems Chapter 4
Relational Model 3 Components
1. A logical data structure represented by relations 2. A set of integrity rules to enforce that the data is consistent and remains consistent over time. 3. A set of operations that defines how data is manipulated
data dictionary
A DBMS component that stores metadata—data about data. Thus, the data dictionary contains the data definition as well as their characteristics and relationships. A data dictionary may also include data that are external to the DBMS. Also known as an information resource dictionary . See also active data dictionary , metadata , and passive data dictionary
subquery
A ________ is a query inside a query.
natural key
A ________ is a real-world, generally accepted identifier used to distinguish real-world objects.
full outer
A ________ join of two tables returns rows with matching values and includes all rows from both tables with unmatched values.
referential integrity
A condition by which a dependent table's foreign key must have either a null entry or a matching entry in the related table.
full functional dependence
A condition in which an attribute is functionally dependent on a composite key but not on any subset of the key
system catalog
A detailed system data dictionary that describes all objects in a database.
inner join
A join operation in which only rows that meet a given criterion are selected. The join criterion can be an equality condition (natural join or equijoin) or an inequality condition (theta join). The inner join is the most commonly used type of join. Contrast with outer join
equijoin
A join operator that links tables based on an equality condition that compares specified columns of the tables.
theta join
A join operator that links tables using an inequality comparison operator (<, >, <=, >=) in the join condition.
secondary key
A key used strictly for data retrieval purposes. For example, customers are not likely to know their customer number (primary key), but the combination of last name, first name, middle initial, and telephone number will probably match the appropriate table row. See also key
cascading
A multilevel ordered sequence is known as a ________ order sequence.
Iterative process
A process based on repetition of steps and procedures.
Existence-independent
A property of an entity that can exist apart from one or more related entities. Such a table must be created first when referencing an existence-dependent table.
Existence-dependent
A property of an entity whose existence depends on one or more other entities. In such an environment, the existence-independent table must be created and loaded first because the existence-dependent key cannot reference a table that does not yet exist.
closure
A property of relational operators that permits the use of relational algebra operators on existing tables (relations) to produce new relations.
Cardinality
A property that assigns a specific value to connectivity and expresses the range of allowed entity occurrences associated with a single occurrence of the related entity.
outer join
A relational algebra join operation that produces a table in which all unmatched pairs are retained; unmatched values in the related table are left null. Contrast with inner join . See also left outer join and right outer join
natural join
A relational operation that yields a new table composed of only the rows with common values in their common attribute(s).
Recursive relationship
A relationship found within a single entity type. For example, an EMPLOYEE is married to an EMPLOYEE or a PART is a component of another PART.
Mandatory participation
A relationship in which one entity occurrence must have a corresponding occurrence in another entity. For example, an EMPLOYEE works in a DIVISION. (A person cannot be an employee without being assigned to a company's division.)
Identifying relationship
A relationship in which related entities are existence-dependent. Also called a strong relationship or strong identifying relationship because the dependent entity's primary key contains the primary key of the parent entity.
Non-identifying relationship
A relationship in which the primary key of the dependent (many side) entity does not contain the primary key of the related parent entity. Also known as a weak relationship.
Weak relationship
A relationship in which the primary key of the related entity does not contain a primary key component of the parent entity.
Strong relationship
A relationship that occurs when two entities are existence-dependent; from a database design perspective, this relationship exists whenever the primary key of the related entity contains the primary key of the parent entity.
relational algebra
A set of mathematical principles that form the basis for manipulating relational table contents; the eight main functions are SELECT, PROJECT, JOIN, INTERSECT, UNION, DIFFERENCE, PRODUCT, and DIVIDE.
discriminator
A specialization hierarchy provides the means to define a special supertype attribute known as the subtype ________.
1:1
A specialization hierarchy reflects the ________ relationship between EMPLOYEE and its subtypes.
correlated
A subquery that executes once for each row in the outer query is a(n) ________ subquery.
foreign keys in
A supertype contains those attributes that are ________ all of its subtypes.
DROP
A table can be deleted from the database by using the ________ command.
Conversion to Second Normal Form (2NF)
A table is in 2NF when it: Is in 1NF Includes no partial dependencies How to remove partial dependencies: For each component of the primary key that is a determinant in a partial dependency, create a new table with this determinant as the primary key, and include all of its dependent attributes in the same table Keep the determinants as well as non-dependent attributes in the original table Give an appropriate name to each table created above
Conversion to Third Normal Form
A table is in 3NF when it: Is in 2NF Contains no transitive dependencies How to remove transitive dependencies: For each determinant in a transitive dependency, create a new table with this determinant as the primary key, and include all of its dependent attributes in the same table Keep the determinants as well as non-dependent attributes in the original table Give an appropriate name to each table created above
a real-world object that is represented in the data model as two separate entities in a strong identifying relationship
A weak entity in a strong identifying relationship with a parent entity is normally used to represent ________.
persistent stored module
A(n) ________ is a block of code (containing standard SQL statements and procedural extensions) that is stored and executed at the DBMS server.
alias
A(n) ________ is an alternate name given to a column or table in any SQL statement.
subtype discriminator
A(n) ________ is the attribute in the supertype entity that determines to which entity subtype each supertype occurrence is related.
outer
A(n) ________ join returns not only the rows matching the join condition but also the rows with unmatched values.
natural
A(n) ________ join will select only the rows with common values in the common column(s), excluding rows with unmatched values and duplicate columns.
UNION ALL
A(n) ________ query can be used to produce a relation that retains duplicate rows.
design
A(n) ________ trap occurs when a relationship is improperly or incompletely identified and, therefore, is represented in a way that is not consistent with the real world.
fan
A(n) ________ trap occurs when you have one entity in two 1:M relationships to other entities, thus producing an association among the other entities that is not expressed in the model.
Binary relationship
An ER term for an association (relationship) between two entities. For example, PROFESSOR teaches CLASS.
Participants
An ER term for entities that participate in a relationship. For example, in the relationship "PROFESSOR teaches CLASS," the teaches relationship is based on the participants PROFESSOR and CLASS.
Ternary relationship
An ER term used to describe an association (relationship) between three entities. For example, a DOCTOR prescribes a DRUG for a PATIENT.
Unary relationship
An ER term used to describe an association within an entity. For example, an EMPLOYEE might manage another EMPLOYEE.
foreign Key (FK)
An attribute or attributes in one table whose values must match the primary key in another table or whose values must be null.
superkey
An attribute or attributes that uniquely identify each entity in a table.
Composite attribute
An attribute that can be further subdivided to yield additional attributes. For example, a phone number such as 615-898-2368 may be divided into an area code (615), an exchange number (898), and a four-digit code (2368). Compare to simple attribute.
Multivalued attributes
An attribute that can have many values for a single entity occurrence. For example, an EMP_DEGREE attribute might store the string "BBA, MBA, PHD" to indicate three different degrees held.
Single-valued attribute
An attribute that can have only one value.
Simple attribute
An attribute that cannot be subdivided into meaningful components. Compare to composite attribute.
Derived attribute
An attribute that does not physically exist within the entity and is derived via an algorithm. For example, the Age attribute might be derived by subtracting the birth date from the current date.
dependent
An attribute whose value is determined by another attribute.
cluster
An entity ________ is a "virtual" entity type used to represent multiple entities and relationships in the ERD.
composite entity
An entity designed to transform an M:N relationship into two 1:M relationships. The composite entity's primary key comprises at least the primary keys of the entities that it connects. Also known as a bridge entity or associative entity . See also linking table
Weak entity
An entity that displays existence dependence and inherits the primary key of its parent entity. For example, a DEPENDENT requires the existence of an EMPLOYEE.
Strong entity
An entity that is existence-independent, that is, it can exist apart from all of its related entities. Also called a regular entity.
Regular entity
An entity that is existence-independent, that is, it can exist apart from all of its related entities. Also called a strong entity.
unique index
An index in which the index key can have only one associated pointer value (row).
Need of Normalization
Analyzes the relationship among the attributes within each entity Determines if the structure can be improved Improves the existing data structure and creates an appropriate database design
determinant
Any attribute in a specific row whose value directly determines other values in that row.
primary key
Arguably, the most important characteristic of an entity is its ________.
17
Assume you are using the UNION ALL statement to combine the results from two identical tables CUSTOMER and CUSTOMER_2. The CUSTOMER table contains 10 rows, while the CUSTOMER_2 table contains seven rows. There are two the UNION ALL statement?
15
Assume you are using the UNION statement to combine the results from two identical tables CUSTOMER and CUSTOMER_2. The CUSTOMER table contains 10 rows, while the CUSTOMER_2 table contains seven rows. There are two customers included in both tables. How many records are returned when using the UNION statement?
1:1
At the implementation level, the supertype and its subtype(s) depicted in the specialization hierarchy maintain a ________ relationship.
Multivalued Attribute
Attribute for which instances of an entity can have multiple values for the same attribute.
Partial Key
Attribute of a weak entity that combined with the unique attribute of the owner entity identifies the weak entity's instances.
Optional Attribute
Attribute that is allowed to not have a value.
Composite Attribute
Attribute that is compose of several attributes.
Composite Unique Attribute
Attribute that is composed of several attributes and whose value is different for each entity instance.
Unique Attribute
Attribute whose vale is different for each entity instance.
Derived Attribute
Attribute whose values are calculated and not permanently stored in a database.
join columns
Columns that are used in the criteria of join operations. The join columns generally share similar values.
USING
Complete the following inner join: SELECT * FROM T1 JOIN T2 ________ (C1)
ER Modeling
Conceptual database modeling technique.
Associative Entity
Construct used as an alternative way of depicting M:N relationships.
Entities
Constructs that represent what a database keeps track of.
Conversion to First Normal Form (1NF)
Conversion steps Eliminate repeating groups Repeating group: Group of multiple entries of same type can exist for any single key attribute occurrence Identify the primary key Identify all dependencies Make sure all attributes are dependent on the primary key Some tables may still contain partial dependencies
Denormalization
Database design goals: Creation of normalized relations Processing speed Number of database tables expands when tables are decomposed to conform to normalization requirements Joining a larger number of tables reduces speed In such cases, denormalization may be desirable Example: ZIP_CODE and CITY can co-exist in a table although former determines the latter (transitive dependency)
Cardinality Constraints
Depict how many instances of one entity can be associated with instances of another entity.
Attribute
Depiction of a characteristic of an entity.
Weak Entity
ER diagram construct depicting an entity that does not have a unique attribute of its own.
Relationship
ER modeling construct depicting how entities are related.
specialization
Entity supertypes and subtypes are organized in a(n) ________ hierarchy.
Owner Entity
Entity whose unique attribute provides a mechanism for identifying instances of a weak entity.
Requirements for Good Normalized Set of Tables
Evaluate PK assignments Check if PK can be in the form of assigned numbers, rather than text Evaluate naming conventions Add shortened entity name to attribute names as prefix Refine attribute atomicity Atomic attribute: Cannot be further subdivided (e.g. EMP_NAME) Identify new attributes and new relationships Maintain historical accuracy JOB_CHG_HOUR (may change over time) ASSIGN_CHG_HOUR (must remain the same once assigned)
only one
Every subtype can have ________ supertype to which it is directly related.
GROUP BY
Frequency distributions can be created quickly and easily using the ________ clause within the SELECT statement.
Functional Dependence
Functional dependence (A → B): Attribute B is functionally dependent on attribute A if each value of A determines one and only one value of B. Two types of functional dependencies: Partial dependency: Functional dependence in which the determinant (A) is only part of the primary key Transitive dependency: An attribute functionally depends on another nonkey attribute
3
How many different types of outer joins exist?
200
How many rows would be returned from a cross join of tables A and B if A contains 10 rows and B contains 20?
subquery
INSERT INTO PRODUCT SELECT * FROM P; is an example of a ________.
JOIN ON
If you wish to create an inner join but the two tables do not have a commonly named attribute, you can use a(n) ________ clause.
Optional participation
In ER modeling, a condition in which one entity occurrence does not require a corresponding entity occurrence in a particular relationship.
Composite identifier
In ER modeling, a key composed of more than one attribute.
Optional attribute
In ER modeling, an attribute that does not require a value; therefore, it can be left empty.
Required attribute
In ER modeling, an attribute that must have a value. In other words, it cannot be left empty.
Category
In MS Visio, you select the subtype discriminator when creating a category using the ________ shape from the available shapes.
FORMAT
In Oracle, the ________ command is used to place a $ in front of a numeric value.
TO_CHAR
In Oracle, the ________ function converts a date to a character string.
Identifiers
In a ERM, unique names of each entity instance. In a relational model, such identifiers are mapped to primary keys in table
inline
In a SELECT query, the attribute list can include a subquery expression, known as a(n) ________ subquery.
nulls
In a data model, grouping employees into types avoids unnecessary ________ in the employee attributes.
right outer join
In a pair of tables to be joined, a join that yields all of the rows in the right table, including the ones with no matching values in the other table. For example, a right outer join of CUSTOMER with AGENT will yield all of the AGENT rows, including the ones that do not have a matching CUSTOMER row. See also left outer join and outer join
left outer join
In a pair of tables to be joined, a join that yields all the rows in the left table, including those that have no matching values in the other table. For example, a left outer join of CUSTOMER with AGENT will yield all of the CUSTOMER rows, including the ones that do not have a matching AGENT row. See also outer join and right outer join
innermost
In a subquery, which query is executed first?
HAVING
In addition to a WHERE clause, a subquery can be used with a(n) ________ clause.
lattices
In addition to the normal behavior, the EER model also supports specialization ________ where a subtype can have multiple parents (supertypes).
subtype
In an aviation company's data model, a PILOT entity might be a(n) ________ of the EMPLOYEE entity.
entity supertype
In modeling terms, the ________ contains the common characteristics and the entity subtypes contain the unique characteristics of each entity subtype.
IN
In order to compare one value to a list of values returned by a subquery, must use a(n) ________ operand.
JOIN
In relational algebra, a type of operator used to yield rows from two tables based on criteria. There are many types of joins, such as natural join, theta join, equijoin, and outer join
DIVIDE
In relational algebra, an operator that answers queries about one set of data being associated with all values of data in another set of data.
UNION
In relational algebra, an operator used to merge (append) two tables into a new table, dropping the duplicate rows. The tables must be union-compatible
PROJECT
In relational algebra, an operator used to select a subset of columns.
SELECT
In relational algebra, an operator used to select a subset of rows. Also known as RESTRICT
PRODUCT
In relational algebra, an operator used to yield all possible pairs of rows from two tables. Also known as the Cartesian product.
DIFFERENCE
In relational algebra, an operator used to yield all rows from one table that are not found in another union-compatible table.
INTERSECT
In relational algebra, an operator used to yield only the rows that are common to two union-compatible tables.
inherits all of the attributes and relationships from all of its upper-level supertypes
In specialization hierarchies with multiple levels of supertype/subtypes, a lower-level subtype ________.
outer
In subquery terminology, the first query in the SQL statement is known as the ________ query.
linking table
In the relational model, a table that implements an M:M relationship. See also composite entity
attributes
It is common to avoid the display of ________ in an ERD that uses entity clusters.
IN
Many queries that would require the use of the logical OR can be more easily handled with the help of the special operator ________.
Normalization Process
Objective is to ensure that each table conforms to the concept of well-formed relations Each table represents a single subject No data item will be unnecessarily stored in more than one table All nonprime attributes in a table are dependent on the primary key Each table is void of insertion, update, and deletion anomalies Ensures that all tables are in the 3NF Works one relation at a time
Entity Instances
Occurrences of an entity.
Unary Relationship
Occurs when an entity is involved in a relationship with itself. (degree 1 relationship)
primary key attribute
One important inheritance characteristic is that all entity subtypes inherit their ________ from their supertype.
Degree of a Relationship
Reflects how many entities are involved in the relationship.
Identifying Relationship
Relationship between a weak entity and its owner entity in which each instance of a weak entity is associated with exactly one instance of an owner entity.
Binary Relationship
Relationship between two entities (degree 2 relationship)
Ternary Relationship
Relationship involving three entities. (degree 3 relationship).
associative entity
See composite entity
bridge entity
See composite entity .
relvar
Short for relation variable, a variable that holds a relation. A relvar is a container (variable) for holding relation data, not the relation itself.
COMMIT
Some RDBMSs (like Oracle) will automatically ________ data changes when issuing data definition commands.
flags
Special codes implemented by designers to trigger a required response, alert end users to specified conditions, or encode values. Flags may be used to prevent nulls by bringing attention to the absence of a value in a table.
clustering
The EER model builds on ER concepts and adds support for entity supertypes, subtypes, and entity _______.
DECODE
The Oracle ________ function compares an attribute or expression with a series of values and returns an associated value or a default value if no match is found.
SYSDATE
The Oracle ________ function returns the current date.
LENGTH
The Oracle ________ function returns the number of characters in a string value.
sequence
The Oracle equivalent to a MS Access AutoNumber is a(n) ________.
||
The Oracle string concatenation function is ________.
UPDATE
The SQL command that enables you to make changes in the data is ________.
INSERT
The SQL command that lets you insert data into a table, one row at a time, is ________.
COMMIT
The SQL command that lets you save your work to disk is ________.
SELECT
The SQL command used to list the contents of a table is ________.
ALTER
The ________ command is used to modify the table structure by adding, removing, or renaming columns.
completeness
The ________ constraint specifies whether each entity supertype occurrence must also be a member of at least one subtype.
DATE()
The ________ function returns the number of the day in MS Access/SQLServer.
inner
The ________ join is the traditional join in which only rows that meet a given criteria are selected.
EXISTS
The ________ operator is used to check whether an attribute has a value.
IS NULL
The ________ operator is used to check whether an attribute value is null.
BETWEEN
The ________ operator is used to define a range limit.
LIKE
The ________ operator is used to find a character string that matches a given tring pattern.
NEXTVAL
The ________ pseudo column is used to select the next available value from a sequence.
INTERSECT
The ________ statement can be used to combine rows from two queries, returning only rows that appear in both sets.
UNION
The ________ statement combines rows from two or more queries without including duplicate rows.
MINUS
The ________ statement in SQL combines rows from two queries and returns only the rows that appear in the first set but not in the second.
primary key
The ________'s function is to guarantee entity integrity.
AVG
The basic SQL aggregate function that gives the arithmetic mean for the specific column is ________.
COUNT
The basic SQL aggregate function that gives the number of rows containing non-null values for the given column is ________.
SUM
The basic SQL aggregate function that gives the total of all values for a selected attribute in a given column is ________.
Connectivity
The classification of the relationship between entities. Classifications include 1:1, 1:M, and M:N.
Normalization Example (A Contracting Company)
The company manages many projects Each project requires many employees An employee may be assigned to multiple projects Some employees are not assigned to a project Each employee has a single primary job classification, which determines the hourly billing rate Many employees can have the same job classification
SQL-2003
The current fully approved version of standard SQL prescribed by the American National Standards Institute is ________.
"old-style" join
The following SQL statement uses a(n) ________. SELECT P_CODE, P_DESCRIPT, P_PRICE, V_NAME FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE = VENDOR.V_CODE;
supertype
The implementation of disjoint subtypes is based on the value of the subtype discriminator attribute in the ________.
WHERE
The most common type of subquery uses an inner SELECT subquery on the right side of a(n) ________ comparison expression.
Relationship degree
The number of entities or participants associated with a relationship. A relationship degree can be unary, binary, ternary, or higher.
Relational schema
The organization of a relational database as described by the database administrator.
inheritance
The property of ________ enables an entity subtype to inherit the attributes and relationships of the supertype.
IS
The relationships depicted within the specialization hierarchy are sometimes described in terms of "____-A" relationships.
ER Diagram
The result of ER modeling.
cross
The statement SELECT * FROM T1, T2 produces a(n) ________ join.
synonym
The use of different names to identify the same object, such as an entity, an attribute, or a relationship; synonyms should generally be avoided. See also hononym
homonym
The use of the same name to label different attributes. Homonyms generally should be avoided. Some relational software automatically checks for homonyms and either alerts the user to their existence or automatically makes the appropriate adjustments. See also synonym
FROM
To join tables, you simply list the tables in the ________ clause of the SELECT statement.
SELECT * FROM PRODUCT;
To list all the contents of the PRODUCT table you would use ________.
union-compatible
Two or more tables that have the same number of columns and the corresponding columns have compatible domains.
SELECT DISTINCT V_CODE FROM PRODUCT;
What command is used to list a unique value for Vendor Code (V_CODE), where the list will produce only a list of those values that are different from one another?
all rows will be deleted
What happens when you issue the DELETE FROM tablename command without specifying a WHERE condition?
SELECT E.EMP_MGR, M.EMP_LNAME, E.EMP_NUM, E.EMP_LNAME FROM EMP E, EMP M WHERE E.EMP_MGR=M.EMP_NUM ORDER BY E.EMP_MGR;
What is an example of a recursive query?
child entity
What is another name for a subtype?
enhanced entity relationship model
What is another name for the extended entity relationship model?
The number of attributes must be the same and their data types must be alike.
What is meant by "union-compatible"?
SELECT EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_AREACODE, EMP_PHONE FROM EMPLOYEE ORDER BY EMP_LNAME, EMP_FNAME, EMP_INITIAL;
What is the SQL command to output the contents of the Employee table sorted by last name, first name, and initial?
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE = 21344 OR V_CODE = 24288;
What is the SQL syntax to list the table contents for either V_CODE = 21344 or V_CODE = 24288?
numeric
What is the best data type for a primary key?
SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE = VENDOR.V_CODE; ORDER BY P_PRICE;
What is the command 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 P_PRICE?
SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE = VENDOR.V_CODE;
What is the command 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?
SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICE FROM PRODUCT ORDER BY P_PRICE;
What is the command used to list the P_CODE, P_DESCRIPT, P_INDATE, and P_PRICE fields from the PRODUCT table in ascending order by P_PRICE?
equal to
What is the default comparison condition for the subtype discriminator attribute?
1
What is the ideal number of attributes used to make up a primary key?
improve readability of the ERD
What is the purpose of an entity cluster?
SELECT column-list FROM table1 LEFT [OUTER] JOIN table2 ON join-condition
What is the syntax for a left outer join?
query UNION query
What is the syntax for the UNION statement?
no aggregate function is used
What is wrong with the following query? SELECT V_CODE, P_CODE, P_DESCRIPT, P_PRICE FROM PRODUCT GROUP BY V_CODE;
JOIN ON
What type of inner join requires the use of a table qualifier?
composite key
What type of primary key should be used as an identifier of a composite entity?
IN
What type of subquery could be used in place of INTERSECT if the RDBMS does not support it?
NOT IN
What type of subquery could be used in place of MINUS if the RDBMS does not support it?
Multiple Unique Attributes
When an entity has more than one unique attribute. (Each unique attribute is also called a candidate key)
in the table that defines the optional entity
When defining a 1:1 relationship where one side of the relationship is mandatory and one is optional, where should the foreign key be placed?
when two columns with the same name are specified in the query
When must table names be used as a prefix for a column name?
HAVING
When using GROUP BY, ________ operates like the WHERE clause in the SELECT statement.
single value
When using an equality (=) or inequality (<, >, etc.) operator for a subquery, what type of value must the subquery return?
MON
When using the Oracle TO_DATE function, the code ________ represents a three letter month name.
9
When using the Oracle TO_NUMBER function to convert a character string into a number, the ________ format character displays a digit.
SELECT P_DESCRIPT, P_HAND, P_MIN, P_PRICE, P_INDATE FROM PRODUCT WHERE P_INDATE >= #20-JAN-08#;
Which MS Access query command will list all the rows in which the inventory stock dates occur on or after January 20, 2006?
ROLLBACK;
Which command is used to restore the table contents?
SELECT columnlist FROM tablelist [WHERE conditionlist];
Which command is used to select partial table contents?
SELECT P_DESCRIPT, P_QOH, P_PRICE,P_QOH*P_PRICE AS TOTVALUE FROM PRODUCT;
Which command uses columns and column aliases to determine the total value of each of the products held on hand and displays the results in a column labeled TOTVALUE?
UPDATE PRODUCT SET P_INDATE = '18-JAN-2008' WHERE P_CODE = '13-Q2/P2';
Which command would you use when changing the date in the PRODUCT table?
SMALLINT
Which data type is compatible with NUMBER?
CHAR(15)
Which data type is considered compatible with VARCHAR(35)?
ANY and ALL
Which operator(s) is/are used with subqueries that use inequality operators on a list of values?
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE <=21344;
Which query would be used to output the table contents where the value of V_CODE is less than or equal to 21344?
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE <> 21344;
Which query would be used to output the table contents where the value of V_CODE is not equal to 21344?
SELECT P_CODE, P_DESCRIPT, P_QOH, P_MIN, P_PRICE FROM PRODUCT WHERE P-CODE = '1558-QW1';
Which query would be used to output the table contents where the value of the character field P_CODE is 1558-QW1?
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE = 21344;
Which query would be used to output the table contents where the value of V_CODE is equal to 21344?
functional Dependence
Within a relation R, an attribute B is functionally dependent on an attribute A if and only if a given value of attribute A determines exactly one value of attribute B. the relationship "B is dependent on A is equivalent to "A determines B", and is written as A -> B.
Time-variant
________ data refer to data whose values change over time and for which you must keep a history of the data changes.
Generalization
________ is the bottom-up process of identifying a higher-level, more generic entity supertypes from lower-level entity subtypes.
Specialization
________ is the top-down process of identifying lower-level, more specific entity subtypes from a higher-level entity supertype.
CHAR & VARCHAR2
________ is/are SQL character data type(s).
Procedural SQL
________ make(s) it possible to merge SQL and traditional programming constructs such as variables, conditional processing (IF-THEN-ELSE), basic loops (FOR and WHILE loops,) and error trapping.
Surrogate
________ primary keys are especially helpful when there is no natural key.
Redundantl
________ relationships occur when there are multiple relationship paths between related entities.
Disjoint
________ subtypes are subtypes that contain a unique subset of the supertype entity set.
Overlapping
________ subtypes are subtypes that contain nonunique subsets of the supertype entity set.
MINUS
_____________ is a relational set operator.
candidate key
a minimal superkey - that is, a key that does not contain a subset of attributes that is itself a superkey
composite Key
a multiple-attribute key
Primary Key (PK)
an attribute or combination of attributes that uniquely identifies any given Row.
MAX
he ________ function is used to find the highest value in a table column.
domain
in data modelling, the construct used to organize and describe an attributes set of possible values
tuple
in the relational model a table row.
Normalization
is a process for evaluating and correcting table structures to minimize data redundancies, thereby reducing data anomalies Normal forms First normal form (1NF) Second normal form (2NF) Third normal form (3NF)
Set Theory
mathematical science that deals with Sets, or groups of things, and is used as the basis for data manipulation in the relational model.
Keys
one or more attributes that determine other attributes.
Predicate Logic
provides a framework in which an assertion(statement of fact) can be verified as either true or false.
relation
same thing as a table. perceived as a two-dimensional structure composed of rows and columns.
RESTRICT
see select
null
the absence of an attribute value, note that null is not a blank
Key attributes
the attribute that form an primary key
entity integrity
the property of a relational table that guarantees each entity has a unique value in a primary key and that the key has no null values
determination
the role of a key. in the context of a database table, the statement "A determines B" indicates that knowing the value of attribute A means that the value of attribute B can be looked up.
