MIS FINAL

Relational DMBS

-Organizes data into two-dimensional tables called relations or files -Each table contains data on entity and attributes -One table for each entity (Customer, Supplier, etc.)

Dynamic View

A dynamic view is a table-like structure that is not stored physically on a storage medium but is recreated based on existing base tables and a stored specification at the time of use.

Outer join

A join in which rows that do not have matching values in common columns are nevertheless included in the result table. Outer joins return all the values in one of the tables included in the join, regardless of whether a match exists in the other table(s) or not.

Equi-join

A join in which the joining condition is based on equality between values in the common columns. It produces a table of rows composed of columns from two other tables, where common columns appear (redundantly) in the result table.

Self-join

A join that requires matching rows in a table with other rows in the same table. This is a join that joins a table with itself and often occurs with the presence of a unary relationship in the database, such as a Supervisor or Manager of Employees within an Employee table.

Composite Key

A key made up of more than one column

Base Table

A physically stored table that includes raw data.

transaction log

A record of essential data for each transaction that is processed against the database

data dictionary

A repository of information about a database, including documentation of data elements of a database

Virtualized table (or virtual table)

A table that does not exist as a permanent structure on a storage medium but that the DBMS creates as needed.

File Organization

A technique for physically arranging the records of a file on secondary storage devices

subquery

A type of query that is placed within a WHERE or HAVING clause of another query is called a:

Referential Integrity

An integrity constraint specifying that the value (or existence) of an attribute in one relation depends on the value (or existence) of the same attribute in another relation.

Data Manipulation Language (DML)

DBMS language that changes database content, including data element creations, updates, insertions, and deletions

Data type

Each unit of a detailed coding scheme recognized by system software, such as a D B M S, for representing organizational data

correlated

In SQL, a(n) ________ subquery is a type of subquery in which processing the inner query depends on data from the outer query.

Materialized View

Materialized view is, in essence, a copy of data in base tables physically stored based on a view definition.

Secondary key

One or a combination of fields for which more than one record may have the same combination of values

indexed file organization

Records are either stored sequentially or non-sequentially, and an index is created that allows software to locate individual records

Sequential file organization

Records of the file are stored in sequence by the primary key field values

False

Subqueries can only be used in the WHERE clause. T or F

True

The UNION clause is used to combine the output from multiple queries into a single result table. T or F

equi-join

The following code is an example of a(n):SELECT Customer_T.CustomerID, Order_T.CustomerID,CustomerName, OrderIDFROM Customer_T, Order_TWHERE Customer_T.

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

The following code would include:SELECT Customer_T.CustomerID,CustomerName, OrderIDFROM Customer_T RIGHT OUTER JOIN Order_T ONCustomer_T.CustomerID = Order_T.CustomerID

True

The following queries produce the same results.SELECT DISTINCT Customer_Name, Customer_CityFROM Customer, SalesmanWHERE Customer.Salesman_ID = Salesman.Salesman_IDand Salesman.Lname = 'SMITH';SELECT Customer_Name, Customer_CityFROM CustomerWHERE Customer.Salesman_ID =(SELECT Salesman_IDFROM SalesmanWHERE Lname = 'SMITH'); T or F

Join

The most frequently used relational operation, which brings together data from two or more related tables into one result table

Denormalization

The process of transforming normalized relations into unnormalized physical record specification

False

There is a special operation in SQL to join a table to itself. T or F

Correlated subquery

This type of subquery is processed outside in, rather than inside out. That is, the inner query is executed for each row in the outer query, and the inner query depends in some way on values from the current row in the outer query. The results of the inner query will, in turn, affect the final results of the outer query.

Data Definition Language (DDL)

Those commands used to define a database, including creating, altering, and dropping tables and establishing constraints

Encryption

Transforming data into a humanly unreadable format

Adds a new record to the Customer_T table

What does the following SQL command do?INSERT INTO Customer_T values (001,'John Smith','231 West St','Boston','MA','02115');

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

What does the following SQL statement do? SELECT * From Customer WHERE Cust_Type = "Best"

changes the unit price of product 7 to 775

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

The Customer_Name and telephone of all customers living in either Boston, New York or Denver

What result set is returned from the following query?SELECT Customer_Name, telephone FROM customers WHERE city in ('Boston','New York','Denver');

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

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

A listing of all drivers who made more than 2 deliveries as well as a count of the number of deliveries

What will be returned when the following SQL query is executed? SELECT driver_no, COUNT(*) as num_deliveries FROM deliveries GROUP BY driver_no HAVING COUNT(*) > 2;

A listing of each driver who made deliveries to state = 'MA' as well as the number of deliveries that each driver has made to that state

What will be returned when the following SQL statement is executed? SELECT driver_no, count(*) as num_deliveries FROM deliveries WHERE state = 'MA' GROUP BY driver_no;

A listing of the customer ID as well as the total number of orders and the total amount spent by the customer

What would the following view contain for values?Create view CustomerOrders asSelect CustID, Count(*) as TotOrders, Sum(ordertotal) as ValueFrom customer inner join sale on customer.customer_id = sale.customer_id;

dynamic view.

When a user creates a virtual table it is called a(n):

schema

a structure that contains descriptions of objects created by a user, such as base tables, views, constraints, and so on, as part of database

Hashed file organization

the address for each record is determined using a hashing algorithm