Data Management Applications Review Questions

theta join

A join operator that links tables using an inequality comparison operator (<, >, <=, >=) in the join condition.

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.)

DBMS Performance Tuning

Activities to ensure that clients' requests are addressed as quickly as possible while making optimum use of existing resources.

Difference between ORDER_BY and GROUP_BY:

An ORDER BY clause has no impact on which rows are returned by the query, it simply sorts those rows into the specified order. A GROUP BY clause does impact the rows that are returned by the query. A GROUP BY clause gathers rows into collections that can be acted on by aggregate functions.

Why are entity integrity and referential integrity important in a database?

Entity integrity and referential integrity are important because they are the basis for expressing and implementing relationships in the entity relationship model.

TO_DATE

Returns a date value using a character string and a date format mask; also used to translate a date between formats

TRUE

To join tables, you simply list the tables in the FROM clause of the SELECT statement. The DBMS will create the Cartesian product of every table in the FROM clause. However, to get the correct result—that is, a natural join—you must select only the rows in which the common attribute values match.

What is the difference between UNION and UNION ALL?

UNION yields unique rows. UNION ALL operator will yield all rows of both relations, including duplicates.

predicate logic

Used extensively in mathematics to provide a framework in which an assertion (statement of fact) can be verified as either true or false.

IN Subqueries

When you want to compare a single attribute to a list of values, you use the IN operator.

Review - A subquery can return

1) a single value (one row, one column), 2) a list of values (many rows, one column), or 3) a virtual table (many rows, many columns).

What two conditions must be met before an entity can be classified as a weak entity? Give an example of a weak entity.

1. The entity must be existence-dependent on its parent entity. 2. The entity must inherit at least part of its primary key from its parent entity.

CREATE VIEW

A SQL command that creates a logical, "virtual" table. The view can be treated as a real table. CREATE VIEW viewname AS SELECT query

CREATE INDEX

A SQL command that creates indexes on the basis of a selected attribute or attributes. CREATE [UNIQUE]INDEX indexname ON tablename(column1 [, column2]);

DROP TABLE

A SQL command used to delete database objects such as tables, views, indexes, and users.

NOT

A SQL logical operator that negates a given predicate.

unary

A _________ relationship exists when an association is maintained within a single entity.

subquery

A ____________ also known as a nested query or an inner query, is a query that is embedded (or nested) inside another query.

HAVING

A clause applied to the output of a GROUP BY operation to restrict selected rows.

What is the difference between a column constraint and a table constraint?

A column constraint can refer to only the attribute with which it is specified. A table constraint can refer to any attributes in the table.

What is a composite entity, and when is it used?

A composite entity is generally used to transform M:N relationships into 1:M relationships.

What is a correlated subquery?

A correlated subquery is subquery that executes once for each row in the outer query.

Review - Explain the difference between a regular subquery and a correlated subquery.

A correlated subquery will execute once for each row evaluated by the outer query; and the correlated subquery can potentially produce a different result for each row in the outer query.

What is a derived attribute?

A derived attribute is an attribute whose value is calculated (derived) from other attributes.

cross join

A join that performs a relational product (or Cartesian product) of two tables. SELECT column-list FROM table1 CROSS JOIN table2

input/output (I/O) request

A low-level data access operation that reads or writes data to and from computer devices.

cascading order sequence

A nested ordering sequence for a set of rows, such as a list in which all last names are alphabetically ordered and, within the last names, all first names are ordered. SELECT EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_AREACODE, EMP_PHONE FROM EMPLOYEE ORDER BY EMP_LNAME, EMP_FNAME, EMP_INITIAL;

Review - The difference between a regular subquery and a correlated subquery.

A regular, or uncorrelated subquery, executes before the outer query. It executes only once and the result is held for use by the outer query. A correlated subquery relies in part on the outer query, usually through a WHERE criteria in the subquery that references an attribute in the outer query. Therefore, a correlated subquery will execute once for each row evaluated by the outer query; and the correlated subquery can potentially produce a different result for each row in the outer query.

batch routine update

A routine that pools transactions into a single group to update a master table in a single operation. begin by defining the master product table (PRODMASTER) and the product monthly sales totals table (PRODSALES).

database management tuning

A set of activities and procedures designed to reduce the response time of a database system—that is, to ensure that an end-user query is processed by the DBMS in the minimum amount of time.

data cache/buffer cache

A shared, reserved memory area that stores the most recently accessed data blocks in RAM. Also called buffer cache.

SQL cache/procedure cache

A shared, reserved memory area that stores the most recently executed SQL statements or PL/SQL procedures, including triggers and functions. Also called procedure cache.

What are the three types of results a subquery can return?

A subquery can return 1) a single value (one row, one column), 2) a list of values (many rows, one column), or 3) a virtual table (many rows, many columns).

Derived Table

A table obtained from other tables directly or indirectly through the evaluation of a query expression.

base tables

A table whose data is actually stored in the database.

data files

A typical enterprise database is normally composed of several ______________. A __________ can contain rows from a single table, or it can contain rows from many different tables.

Using the ALTER syntax, the integer V_CODE in the PRODUCT table can be changed to a character V_CODE by using the following command:

ALTER TABLE PRODUCT MODIFY (V_CODE CHAR(5));

The ALTER TABLE command can also be used to add table constraints. In those cases, the syntax would be:

ALTER TABLE tablename ADD constraint [ ADD constraint ]; where constraint refers to a constraint definition similar to those you learned in Section SQL Constraints.

You could also use the ALTER TABLE command to remove a column or table constraint. The syntax would be as follows:

ALTER TABLE tablename DROP {PRIMARY KEY | COLUMN columnname | CONSTRAINT constraintname };

SQL Performance Tuning

Activities to help generate a SQL query that returns the correct answer in the least amount of time, using the minimum amount of resources at the server end.

ADD_MONTHS

Adds a number of months or years to a date

ALTER TABLE

All changes in the table structure are made by using the ___________________ command followed by a keyword that produces the specific change you want to make. Three options are available: ADD, MODIFY, and DROP. You use ADD to add a column, MODIFY to change column characteristics, and DROP to delete a column from a table.

alias

An ________ is an alternate name given to a column or table in any SQL statement.

ANSI-standard SQL allows the use of special operators in conjunction with the WHERE clause. These special operators include:

BETWEEN: Used to check whether an attribute value is within a range IS NULL: Used to check whether an attribute value is null LIKE: Used to check whether an attribute value matches a given string pattern IN: Used to check whether an attribute value matches any value within a value list EXISTS: Used to check whether a subquery returns any rows

Review - Why does the order of the operands (tables) matter in a MINUS query but not in a UNION query?

Because if there are duplicates in both tables the columns won't show up in the query results.

What is the difference between the COUNT aggregate function and the SUM aggregate function?

COUNT returns the number of values without regard to what the values are. SUM adds the values together and can only be applied to numeric values.

Concatenation || Oracle + Access and MS SQL Server & Access CONCAT() MySQL

Concatenates data from two different character columns and returns a single column.

Identify the primary keys.

DIR_NUM is the DIRECTOR table's primary key. PLAY_CODE is the PLAY table's primary key.

Aggregate Functions

FUNCTION: COUNT The number of rows containing non-null values MIN The minimum attribute value encountered in a given column MAX The maximum attribute value encountered in a given column SUM The sum of all values for a given column AVG The arithmetic mean (average) for a specified column

The relational join operation merges rows from two tables and returns the rows with one of the following conditions:

Have common values in common columns (natural join). Meet a given join condition (equality or inequality). Have common values in common columns or have no matching values (outer join).

What are homonyms and synonyms, and why should they be avoided in database design?

Homonyms appear when more than one attribute has the same name. Synonyms exist when the same attribute has more than one name. Avoid both to avoid inconsistencies.

Difference between INSERT and UPDATE:

INSERT creates new rows in the table, while UPDATE changes rows that already exist.

What string function should you use to list the first three characters of a company's EMP_LNAME values?

In Oracle, you use the SUBSTR function, In SQL Server, you use the SUBSTRING function.

extents

In a DBMS environment, refers to the ability of data files to expand in size automatically using predefined increments.

table space/file group

In a DBMS, a logical storage space used to group related data. Also known as a file group.

outer join

In an ______________, the matched pairs would be retained, and any unmatched values in the other table would be left null.

method

In the OO data model, the name of a method sent to an object in order to perform an action. A message triggers the object's behavior.

DELETE

It is easy to delete a table row using the ____________ statement. DELETE FROM tablename [WHERE conditionlist ];

identifiers

One or more attributes that uniquely identify each entity instance.

SQL Computational Sequence:

Perform operations within parentheses. Perform power operations. Perform multiplications and divisions. Perform additions and subtractions.

TO_CHAR

Returns a character string or a formatted string from a date value

UPPER Oracle, MS SQL Server, and MySQL UCASE MySQL and Access LOWER Oracle, MS SQL Server, and MySQL LCASE MySQL and Access

Returns a string in all capital or all lowercase letters

SUBSTRING

Returns a substring or part of a given string parameter

ABS

Returns the absolute value of a number

LAST_DAY

Returns the date of the last day of the month given in a date

LENGTH

Returns the number of characters in a string value

CEIL/CEILING/FLOOR

Returns the smallest integer greater than or equal to a number or returns the largest integer equal to or less than a number, respectively

SYSDATE

Returns today's date

ROUND

Rounds a value to a specified precision (number of digits)

The IN Special Operator: Many queries that would require the use of the logical OR can be more easily handled with the help of the special operator IN. For example, the following query:

SELECT * FROM PRODUCT WHERE V_CODE IN (21344, 24288);

Example of Join

SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE = VENDOR.V_CODE;

What Oracle function should you use to calculate the number of days between the current date and January 25, 1999?

SELECT SYSDATE - TO_DATE('25-JAN-1999', 'DD-MON-YYYY') FROM DUAL;

The GROUP BY clause is valid only when used in conjunction with one of the SQL aggregate functions, such as COUNT, MIN, MAX, AVG, and SUM. For example, as shown in the first command set in Figure 3.26, if you try to group the output by using:

SELECT V_CODE, P_CODE, P_DESCRIPT, P_PRICE FROM PRODUCT GROUP BY V_CODE;

The ORDER BY clause is especially useful when the listing order is important to you. The syntax is:

SELECT columnlist FROM tablelist [WHERE conditionlist ] [ORDER BY columnlist [ASC | DESC] ];

DISTINCT

SQL's _______________ clause produces a list of only those values that are different from one another. For example, the command SELECT DISTINCT V_CODE FROM PRODUCT; yields only the different vendor codes (V_CODE) in the PRODUCT table

Review - What string function should you use to list the first three characters of a company's EMP_LNAME values? Give an example, using a table named EMPLOYEE.

SUBSTRING - SELECT SUBSTR(EMP_LNAME, 1, 3) FROM EMPLOYEE;

True

String (character) comparisons are made from left to right. This left-to-right comparison is especially useful when attributes such as names are to be compared.

Review - What Oracle function should you use to calculate the number of days between the current date and January 25, 1999?

TO_DATE

Explain why it would be preferable to use a DATE data type to store date data instead of a character data type.

The DATE data type uses numeric values based on the Julian calendar to store dates. This makes date arithmetic such as adding and subtracting days or fractions of days possible.

EXCEPT (MINUS)

The EXCEPT statement in SQL combines rows from two queries and returns only the rows that appear in the first set but not in the second.

FROM Subqueries

The FROM clause specifies the table(s) from which the data will be drawn.

HAVING Subqueries

The HAVING clause is used to restrict the output of a GROUP BY query by applying conditional criteria to the grouped rows.

Suppose you wanted quick lookup capability to get a listing of all plays directed by a given director. Which table would be the basis for the INDEX table, and what would be the index key?

The PLAY table would be the basis for the appropriate index table. The index key would be the attribute DIR_NUM.

When using the GROUP BY clause with a SELECT statement:

The SELECT's columnlist must include a combination of column names and aggregate functions. The GROUP BY clause's columnlist must include all nonaggregate function columns specified in the SELECT's columnlist . If required, you could also group by any aggregate function columns that appear in the SELECT's columnlist . The GROUP BY clause columnlist can include any columns from the tables in the FROM clause of the SELECT statement, even if they do not appear in the SELECT's columnlist.

COMMIT

The SQL command that permanently saves data changes to a database. COMMIT;

OR

The SQL logical operator used to link multiple conditional expressions in a WHERE or HAVING clause. It requires only one of the conditional expressions to be true. SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE = 21344 OR V_CODE = 24288;

AND

The SQL logical operator used to link multiple conditional expressions in a WHERE or HAVING clause. It requires that all conditional expressions evaluate to true. SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE P_PRICE < 50 AND P_INDATE > '15-Jan-2016';

The LIKE Special Operator:

The _______ special operator is used in conjunction with wildcards to find patterns within string attributes. Standard SQL allows you to use the percent sign ( % ) and underscore ( _ ) wildcard characters to make matches when the entire string is not known:

EXISTS

The ________ Special Operator: The EXISTS special operator can be used whenever there is a requirement to execute a command based on the result of another query. That is, if a subquery returns any rows, run the main query; otherwise, do not. For example, the following query will list all vendors, but only if there are products to order: SELECT * FROM VENDOR WHERE EXISTS (SELECT * FROM PRODUCT WHERE P_QOH <= P_MIN);

Divide:

The _________ operator is used to answer questions about one set of data being associated with all values of data in another set of data.

index key

The ___________ is, in effect, the index's reference point.

UNION

The _______________ statement combines rows from two or more queries without including duplicate rows.

What does it mean to say that SQL operators are set-oriented?

The description of SQL operators as set-oriented means that the commands work over entire tables at a time, not row-by-row.

To answer the question, therefore, you must compute the maximum price first, then compare it to each price returned by the query. To do that, you need a nested query. In this case, the nested query is composed of two parts:

The inner query , which is executed first. The outer query , which is executed last. (Remember that the outer query is always the first SQL command you encounter—in this case, SELECT.)

WHERE Subqueries

The most common type of subquery uses an inner SELECT subquery on the right side of a WHERE comparison expression.

authentication

The process through which a DBMS verifies that only registered users can access the database.

Suppose that you have two tables, EMPLOYEE and EMPLOYEE_1. The EMPLOYEE table contains the records for three employees: Alice Cordoza, John Cretchakov, and Anne McDonald. The EMPLOYEE_1 table contains the records for employees John Cretchakov and Mary Chen. Given that information, what is the query output for the UNION query? (List the query output.)

The query output will be: Alice Cordoza John Cretchakov Anne McDonald Mary Chen

What are multivalued attributes, and how can they be handled within the database design?

The real test of redundancy is not how many copies of a given attribute are stored, but whether the elimination of an attribute will eliminate information.

Review - What is the difference between UNION and UNION ALL?

UNION yields unique rows. In other words, UNION eliminates duplicates rows. On the other hand, a UNION ALL operator will yield all rows of both relations, including duplicates.

Recursive join

When you join a table to itself. Best to use an alias.

ROLLBACK

_____________________ undoes any changes since the last COMMIT command and brings all of the data back to the values that existed before the changes were made. ROLLBACK;

The difference between HAVING and WHERE:

the WHERE clause eliminates rows before any grouping for aggregate functions occurs while the HAVING clause eliminates groups after the grouping has been done, and the WHERE clause cannot contain an aggregate function but the HAVING clause can.

natural join: ⨝

A ________________ links tables by selecting only the rows with common values in their common attribute(s).

recursive relationship

A ______________________ is one in which a relationship can exist between occurrences of the same entity set. (Naturally, such a condition is found within a unary relationship.)

single-valued

A ________________________ attribute is an attribute that can have only a single value. For example, a person can have only one Social Security number

How would you implement a 1:M relationship in a database composed of two tables?

A car is owned just by one customer. A customer can own more than one car.

derived attribute

An attribute whose value is calculated from other attributes.

A database user manual notes that, "The file contains two hundred records, each record containing nine fields." Use appropriate relational database terminology to "translate" that statement.

"the table -- or entity set -- contains two hundred rows -- or, if you like, two hundred tuples, or entities. Each of these rows contains nine attributes."

Entity integrity is the condition in which each row (entity instance) in the table has its own unique identity. To ensure entity integrity, the primary key has two requirements:

(1) all of the values in the primary key must be unique, and (2) no key attribute in the primary key can contain a null.

Create the table that results from applying a UNION relational operator to the tables shown.

A UNION operator will eliminate duplicate rows from the result; however, the entire row must match for two rows to be considered duplicates. In the case of "Chips", the product names were the same but the prices were different. In the case of "Energy Drink", both the product names and the prices matched so the second Energy Drink row was dropped from the result.

ternary

A ___________ relationship exists when three entities are associated.

binary

A ____________ relationship exists when two entities are associated.

Discuss the difference between a composite key and a composite attribute.

A composite key is one that consists of more than one attribute. A composite attribute is one that can be subdivided to yield meaningful attributes for each of its components.

equijoin

A join operator that links tables based on an equality condition that compares specified columns of the tables.

schema

A logical grouping of database objects, such as tables, indexes, views, and queries, that are related to each other. Usually, a schema belongs to a single user or application.

candidate key

A minimal superkey; that is, a key that does not contain a subset of attributes that is itself a superkey. ________________ are called ______________ because they are the eligible options from which the designer will choose when selecting the primary key.

recursive query

A nested query that joins a table to itself.

composite identifier

A primary key composed of more than one attribute.

closure

A property of relational operators that permits the use of relational algebra operators on existing tables (relations) to produce new relations.

What is a recursive relationship? Given an example.

A recursive relationship exists when an entity is related to itself. For example, a COURSE may be a prerequisite to a COURSE.

weak (non-identifying) relationship

A relationship in which the primary key of the related entity does not contain a primary key component of the parent entity.

strong (identifying) 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.

Briefly, but precisely, explain the difference between single-valued attributes and simple attributes.

A single-valued attribute is one that can have only one value. For example, a person has only one first name and only one social security number. A simple attribute is one that cannot be decomposed into its component pieces. For example, a person's sex is classified as either M or F and there is no reasonable way to decompose M or F.

What is a strong (or identifying) relationship, and how is it depicted in a Crow's Foot ERD?

A strong relationship exists when en entity is existence-dependent on another entity and inherits at least part of its primary key from that entity.

What is the difference between a database and a table?

A table, a logical structure that represents an entity set, is only one of the components of a database.

Explain the difference between an ORDER BY clause and a GROUP BY clause.

An ORDER BY clause has no impact on which rows are returned by the query, it simply sorts those rows into the specified order. A GROUP BY clause does impact the rows that are returned by the query.

referential integrity

An RDBMS automatically enforces ________________________ for foreign keys. That is, you cannot have an invalid entry in the foreign key column; at the same time, you cannot delete a vendor row as long as a product row references that vendor.

inner join

An ___________ only returns matched records from the tables that are being joined.

composite attribute

An attribute that can be further subdivided to yield additional attributes. For example, an address can be subdivided by street, city and state.

simple attribute

An attribute that cannot be subdivided. For example, age, sex, and marital status.

Explain why it might be more appropriate to declare an attribute that contains only digits as a character data type instead of a numeric data type.

An attribute that contains only digits may be properly defined as character data when the values are nominal; that is, the values do not have numerical significance but serve only as labels such as ZIP codes and telephone numbers.

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.

index

An ordered array of ________ key values and row ID values (pointers). __________ are generally used to speed up and facilitate data retrieval.

Write the relational algebra formula to apply an INTERSECT relational operator to the tables shown

BOOTH ⋂ MACHINE MACHINE ⋂ BOOTH

Write the relational algebra formula to apply a UNION relational operator to the tables shown.

BOOTH ⋃ MACHINE MACHINE ⋃ BOOTH

In a SELECT query, what is the difference between a WHERE clause and a HAVING clause?

Both a WHERE clause and a HAVING clause can be used to eliminate rows from the results of a query. The differences are the WHERE clause eliminates rows before any grouping for aggregate functions occurs while the HAVING clause eliminates groups after the grouping has been done, and the WHERE clause cannot contain an aggregate function but the HAVING clause can.

What type of integrity is enforced when a primary key is declared?

Creating a primary key constraint enforces entity integrity (i.e. no part of the primary key can contain a null and the primary key values must be unique).

What does it mean to say that a database displays both entity integrity and referential integrity?

Entity integrity describes a condition in which all tuples within a table are uniquely identified by their primary key. The unique value requirement prohibits a null primary key value, because nulls are not unique. Referential integrity describes a condition in which a foreign key value has a match in the corresponding table or in which the foreign key value is null. The null foreign key "value" makes it possible not to have a corresponding value, but the matching requirement on values that are not null makes it impossible to have an invalid value.

Inserting Rows with Optional Attributes:

Rather than declaring each attribute as NULL in the INSERT command, you can indicate just the attributes that have required values. INSERT INTO PRODUCT(P_CODE, P_DESCRIPT) VALUES ('BRT-345','Titanium drill bit');

optional participation

In ER modeling, a condition in which one entity occurrence does not require a corresponding entity occurrence in a particular relationship.

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.

True

In a 1:M relationship, you must always create the table for the "1" side first.

unique index

In a _________________, as its name implies, the index key can have only one pointer value (row) associated with it.

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.

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.

What are the requirements that two relations must satisfy in order to be considered union-compatible?

In order for two relations to be union-compatible, both must have the same number of attributes (columns) and corresponding attributes (columns) must have the same domain.

Identify and describe the components of the table shown using correct terminology. Use your knowledge of naming conventions to identify the table's probable foreign key(s).

One entity set: EMPLOYEE. Five attributes: EMP_NUM, EMP_LNAME, EMP_INIT, EMP_FNAME, DEPT_CODE and JOB_CODE. Ten entities: the ten workers shown in rows 1-10. One primary key: the attribute EMP_NUM because it identifies each row uniquely. Two foreign keys: the attribute DEPT_CODE, which probably references a department to which the employee is assigned and the attribute JOB_CODE which probably references another table in which you would find the description of the job and perhaps additional information pertaining to the job.

What are "referential constraint actions"?

Referential constraint actions, such as ON DELETE CASCADE, are default actions that the DBMS should take when a DML command would result in a referential integrity constraint violation. Without referential constraint actions, DML commands that would result in a violation of referential integrity will fail with an error indicating that the referential integrity constrain cannot be violated.

Relational database entity integrity rules:

Requirement-All primary key entries are unique, and no part of a primary key may be null. Purpose-Each row will have a unique identity, and foreign key values can properly reference primary key values. Example-No invoice can have a duplicate number, nor can it be null; in short, all invoices are uniquely identified by their invoice number.

data definition language (DDL)

SQL includes commands to create database objects such as tables, indexes, and views, as well as commands to define access rights to those database objects.

data manipulation language (DML)

SQL includes commands to insert, update, delete, and retrieve data within the database tables.

INSERT

SQL requires the use of the _________ command to enter data into a table. INSERT INTO tablename VALUES (value1 , value2 , , valuen );

relvar

Short for relation variable, a variable that holds a relation. A ______ is a container (variable) for holding relation data, not the relation itself.

Besides the PRIMARY KEY and FOREIGN KEY constraints, the ANSI SQL standard also defines the following constraints:

The NOT NULL constraint ensures that a column does not accept nulls. The UNIQUE constraint ensures that all values in a column are unique. The DEFAULT constraint assigns a value to an attribute when a new row is added to a table. The end user may, of course, enter a value other than the default value. The CHECK constraint is used to validate data when an attribute value is entered.

Which relational algebra operators can be applied to a pair of tables that are not union-compatible?

The Product, Join, and Divide operators can be applied to a pair of tables that are not union-compatible.

middleware

The computer software that allows clients and servers to communicate within the client/server architecture. It is used to insulate client processes from the network protocols and the details of the server process protocols.

Identify the foreign keys.

The foreign key is DIR_NUM, located in the PLAY table. Note that the foreign key is located on the "many" side of the relationship between director and play. (Each director can direct many plays ... but each play is directed by only one director.)

A relvar has two parts:

The heading and the body. The relvar heading contains the names of the attributes, while the relvar body contains the relation.

Using the STUDENT and PROFESSOR tables shown to illustrate the difference between a natural join, an equijoin, and an outer join.

The natural JOIN process begins with the PRODUCT of the two tables. Next, a SELECT (or RESTRICT) is performed on the PRODUCT generated in the first step to yield only the rows for which the PROF_CODE values in the STUDENT table are matched in the PROF table. Finally, a PROJECT is performed to produce the natural JOIN output by listing only a single copy of each attribute. The order in which the query output rows are shown is not relevant. The equiJOIN's results depend on the specified condition. The common attribute appears from both tables. In the Outer JOIN, the unmatched pairs would be retained and the values that do not have a match in the other table would be left null. It should be made clear to the students that Outer Joins are not the opposite of Inner Joins (like Natural Joins and Equijoins). Rather, they are "Inner Join Plus" - they include all of the matched records found by the Inner Join plus the unmatched records.

relationship degree

The number of entities or participants associated with a relationship. A relationship degree can be unary, binary, ternary, or higher.

INSERT RULES

The row contents are entered between parentheses. Character (string) and date values must be entered between apostrophes ( ' ). Numerical entries are not enclosed in apostrophes. Attribute entries are separated by commas. A value is required for each column in the table.

DROP INDEX

To delete an index, use the DROP INDEX command: DROP INDEX indexname;

UPDATE

Use the ____________ command to modify data in a table. The syntax for this command is as follows: UPDATE tablename SET columnname = expression [, columnname = expression ] [WHERE conditionlist ];

union-compatible

When two or more tables share the same number of columns, and when their corresponding columns share the same or compatible domains, they are said to be __________________.

reserved words

Words used by a system that cannot be used for any other purpose. For example, in Oracle SQL, the word INITIAL cannot be used to name tables or columns.

Select (Restrict): σ

________, also known as RESTRICT, is referred to as a unary operator because it only uses one table as input. It yields values for all rows found in the table that satisfy a given condition.

Union: ∪

___________ combines all rows from two tables, excluding duplicate rows. To be used in the ________, the tables must have the same attribute characteristics; in other words, the columns and domains must be compatible.

Project: π

_____________ yields all values for selected attributes. It is also a unary operator, accepting only one table as input. ___________ will return only the attributes requested, in the order in which they are requested.

Product: ×

______________ yields all possible pairs of rows from two tables—also known as the Cartesian ___________. Therefore, if one table has 6 rows and the other table has 3 rows, the ________________ yields a list composed of 6 × 3 = 18 rows.

Intersect: ∩

______________ yields only the rows that appear in both tables. The tables must be union-compatible to yield valid results. For example, you cannot use _________________ if one of the attributes is numeric and one is character-based.

Difference: -

_________________ yields all rows in one table that are not found in the other table; that is, it subtracts one table from the other. The tables must be union-compatible to yield valid results.

Transaction-safe

___________________ tables provide improved support for data integrity, implementation of database transactions and transaction logs, and improved backup and recovery options.

Multivalued attributes

___________________________ are attributes that can have many values. For instance, a person may have several college degrees, and a household may have several different phones, each with its own number.

How would you (graphically) identify each of the following ERM components in a Crow's Foot model? a. an entity b. the cardinality (0,N) c. a weak relationship d. a strong relationship

a. An entity is represented by a rectangle containing the entity name. b. Cardinality is represented by a Crows Foot Notation c. A weak relationship exists when the PK of the related entity does not contain at least one of the PK attributes of the parent entity. d. A strong relationship exists when the PK of the related entity contains at least one of the PK attributes of the parent entity.

What three (often conflicting) database requirements must be addressed in database design?

a. Design elegance requires that the design must adhere to design rules concerning nulls, derived attributes, redundancies, relationship types, and so on. b. Information requirements are dictated by the end users c. Operational (transaction) speed requirements are also dictated by the end users.