IS 410 EXAM 2 (Ch.4-6)

Match the SELECT statement clause to its definition. A. Indicate the conditions under which a category (group) will be included B. Indicate the conditions under which a row will be included in the result C. Lists the columns (and expressions) to be returned from the query D. Indicate categorization of results E. Indicate the table(s) or view(s) from which data will be obtained

(A) HAVING (B) WHERE (C) SELECT (D) GROUPED BY (E) FROM

Which of the following is not a data integrity restriction? A.Updates to primary records B.Inserts of primary records C.Inserts of dependent records D.Deletion of primary records

(B) Inserts of primary records

Place the steps for developing a relational database in the proper order. - Understand Business Need for the database -Create an EER diagram -create an ER diagram - Create a relational & SQL schema -Create the database Using SQL Programming

1. Understand Business Need for the database 2. Create an ER diagram 3. Create an EER diagram 4. Create a relational & SQL schema 5. Create the database Using SQL Programming

A subquery that is executed once for the entire query is called [A] and a subquery that is executed once for each row returned by the outer query is called [B].

A subquery that is executed once for the entire query is called NONCORRELATED and a subquery that is executed once for each row returned by the outer query is called CORRELATED.

Match the relational model component with its definition. A. Tables (relations), rows, columns B. Power SQL operations for retrieving and modifying data C. Mechanisms for implementing business rules that maintain integrity of manipulated data D. A named, two-dimensional table of data

A. Data structure B. Data manipulation C. Data integrity D. Relation

Match the definition with the correct term. A. A relational operation that causes two or more tables with a common domain to be combined into a single table or view B. A join in which the joining condition is based on equality between values in the common columns; common columns appear redundantly in the result table C. An equi-join in which one of the duplicate columns is eliminated in the result table D. A join in which rows that do not have matching values in common columns are nonetheless included in the result table (as opposed to inner join, in which rows must have matching values in order to appear in the result table) E. Includes all data from each table that was joined

A. Join B. Equi-Join C. Natural (Inner) join D. Outer Join E. Union Join

What does the following SQL statement do? UPDATE Product_T SET Unit_Price = 775 WHERE Product_ID = 7

Changes the unit price of Product 7 to 775

A primary key that consists of more than one attribute is called a: -foreign key -composite key -multivalued key -cardinal key

Composite key

What does the following SQL statement do? DELETE FROM Customer_T WHERE state = 'HI';

Deletes all records from Customer_t where the state is equal to HI

T/F: An OUTER join will only return rows from each table that have matching rows in the other.

FALSE

T/F: There can be multivalued attributes in a relation.

FALSE

Which of the following are properties of relations? -Each attribute has the same name -No two rows in a relation are identical -there are multivalued attributes in a relation -all columns are numeric

No two rows in a relation are identical

In the figure below, what type of relationship do the relations depict? -identifying entity/weak entity -one to many -ternary -many to many

One-to-many

A rule that states that each foreign key value must match a primary value in the other relation is called the: -referential integrity constraint -key match rule -entity key group rule -foreign/primary match rule

Referential integrity constraint

What does the following SQL statement do? SELECT * From Customer WHERE Cust_Type = "Best" -Selects fields with a "*" in them from the Customer table -Selects all the fields from the Customer table for each row with a customer labeled "*" -Selects the "*" field from the Customer table for each row with a customer labeled "Best" -Selects all the fields from the Customer table for each row with a customer labeled "Best"

Selects all the fields from the Customer table for each row with a customer labeled "Best"

What kind of join does the following code represent: SELECT E.EmployeeID, E.EmployeeName, M.EmployeeName AS Manager FROM Employee_T E, Employee_T M WHERE E.EmployeeSupervisor = M.EmployeeID; Result: EMPLOYEEID 122-44-347 EMPLOYEENAME Jim Jason MANGER Robert Lewis

Self Join

What result set will the following query return? SELECT Item_No, description FROM item WHERE weight > 100 and weight < 200;

The Item_No and description for all items weighing between 101 and 199

What result will the following SQL statement produce? SELECT Avg(Standard_Price) as average from Product_V;

The average Standard_Price of all products in Product_V

(T/F) Data structures include data organized in the form of tables with rows and columns.

True

T/F: A LEFT OUTER JOIN clause causes rows from the first mentioned table (customer) to appear even if there is no corresponding order data.

True

T/F: A candidate key is an attribute, or combination of attributes, that uniquely identifies a row in a relation

True

T/F: A cascading delete removes all records in other tables associated with the record to be deleted.

True

T/F: A primary key is an attribute that uniquely identifies each row in a relation

True

T/F: A referential constraint is a rule that maintains consistency among the rows of the two relations

True

T/F: A subquery is when you place an inner query (SELECT statement) inside an outer query.

True

T/F: A view is based on a SELECT statement and has a name.

True

T/F: Referential integrity are constraints that ensures that foreign key values of a table must match primary key values of a related table in 1:M relationships.

True

T/F: SQL stands for Structured Query Language.

True

T/F: The CHECK statement allows you to constrain attributes to specific values.

True

T/F: The following queries produce the same results. SELECT DISTINCT Customer_Name, Customer_City FROM Customer, Salesman WHERE Customer.Salesman_ID = Salesman.Salesman_ID and Salesman.Lname = 'SMITH'; SELECT Customer_Name, Customer_City FROM Customer WHERE Customer.Salesman_ID = (SELECT Salesman_ID FROM Salesman WHERE Lname = 'SMITH');

True

When creating a database, you define the database in the [A], you load the database in the [B] and you control the database in the [C].

When creating a database, you define the database in the DDL, you load the database in the DML and you control the database in the DCL.

The following code would include: SELECT Customer_T.CustomerID,CustomerName, OrderID FROM Customer_T LEFT OUTER JOIN Order_T ON Customer_T.CustomerID = Order_T.CustomerID;

all rows of the Customer_T Table regardless of matches with the Order_T Table.

The following code would include: SELECT Customer_T.CustomerID,CustomerName, OrderID FROM Customer_T RIGHT OUTER JOIN Order_T ON Customer_T.CustomerID = Order_T.CustomerID;

all rows of the Order_T Table regardless of matches with the Customer_T Table.

A join operation: -brings together data from two different fields. -is used to combine indexing operations. -causes two tables with a common domain to be combined into a single table or view. -causes two disparate tables to be combined into a single table or view

causes two tables with a common domain to be combined into a single table or view.

In the figure below, the primary key for the "Order Line" is which type of key? -composite -foreign -standard -grouped

composite

When a regular entity type contains a multivalued attribute, one must: -create a single relation with multiple lines for each instance of the multivalued attribute -create two new relations, one containing the multivalued attribute -create two new relations, both containing the multivalued attribute -delete the relation and start over

create two new relations, one containing the multivalued attribute

The _____ states that no primary key attribute may be null -referential integrity rule -entity integrity rule -partial specialization rule -range domain rule

entity integrity rule

The following code is an example of a(n): SELECT Customer_T.CustomerID, Order_T.CustomerID, CustomerName, OrderID FROM Customer_T, Order_T WHERE Customer_T.CustomerID = Order_T. CustomerID;

equi-join

An attribute in a relation of a database that serves as the primary key of another relation in the same database is called a: -link attribute -link key -foreign key -foreign attribute

foreign key

The entity integrity rule states that: -no primary key attribute can be null -referential integrity must be maintained across all entities -each entity must have a primary key - a primary key must have only one attribute

no primary key attribute can be null

____use the result of the inner query to determine the processing of the outer query. -Subqueries -Inner subqueries -Outer subqueries -Correlated subqueries

subqueries

The following code is an example of a: SELECT CustomerName, CustomerAddress, CustomerCity, CustomerState, CustomerPostalCode FROM Customer_T WHERE Customer_T.CustomerID = (SELECT Order_T.CustomerID FROM Order_T WHERE OrderID = 1008);

subquery

Data is represented in the form of: -data trees -tables -data notes -chairs

tables

All of the following are guidelines for better query design EXCEPT: -use a lot of self-joins. -retrieve only the data that you need. -understand how indexes are used in query processing. -write simple queries.

use a lot of self-joins