Database Theory Test 2

What are the three anomaly types that are avoided by normalizing the database?

1) Insert Anomaly 2) Update Anomaly 3) Delete Anomaly

What are major advantages of partitioning?

1.Efficiency: Records used together are grouped together 2.Local optimization: Each partition can be optimized for performance 3.Security, recovery 4.Load balancing: Partitions stored on different disks, reduces contention

What are the benefits of a standardized relational language?

1.Standards are the same across the system which means there is less adaptability needed in terms of skills for users 2.It also improves productivity because there is less time required for learning the skill of a new language every time when you have a new system. 3.Reduce dependence in a single vendor

9. A row or tuple has a ____Relation___ schema, but an entire database has a __Relational_____ schema.

9. A row or tuple has a ____Relation___ schema, but an entire database has a __Relational_____ schema.

What is a functional dependency?

A constraint between two attributes or two sets of attributes

What is the relationship between determinants and candidate keys? Is there one?

A determinant is any attribute that you can use to determine the values assigned to other attribute(s) in the same row. And a candidate key is an attribute, or combination of attributes, that uniquely identifies a row in a relation. Hence, we can say that a candidate key is always a determinant but a determinant is not always the candidate key.

What is Relational algebra?

A formal system for manipulating relations

What is a partial functional dependency?

A functional dependency in which one or more nonkey attributes are functionally dependent on part, but not all, of the primary key.

What is a relation?

A named two-dimensional table of data of rows and columns. A tuple has a relation schema, but an entire database has a relational schema.

When is a relation in first normal form? What criteria must be met?

A relation that has a primary key and in which there are no repeating groups.

What is a Self Join in SQL?

A relation that is joined to itself Ex: Select e.LastName AS Employee, mLastName AS Manage FROM employees e INNER JOIN employees m ON (e.Manager_ID = m.Manage_ID)

What does the SQL CASE statement do?

Allows you to return varying results based upon the evaluation of expressions. You can use a CASE statement anywhere within a SQL statement that you would normally include an expression. SQL CASE is a very unique conditional statement providing if/then/else logic for any ordinary SQL command, such as SELECT or UPDATE.

In 1986, what organizations adopted SQL as a standard?

American National Standards Institute

What is a foreign key?

An attribute in a relation that serves as the primary key of another relation in the same database. An identifier that enables a dependent relation (on the many side of a relationship) to refer to its parent relation (on the one side of the relationship).

Anamoly

An error or inconsistency that may result when a user attempts to update a table that contains redundant data. The three types of anomalies are insertion, deletion, and modification anomalies.

What type of key is UPC (Universal Product Code) in the OrderLines and Products tables?

An external key we will use as the primary key for our products table

What is a well-structured relation?

Any relation that contains minimal redundancy and allows users to insert, modify, and delete the rows in a table without anomalies, errors or inconsistencies

What is an enumerated domain? How would you store the domain values?

Attribute domains that may be specified by a well-defined, reasonably-sized set of constant values are called enumerated domains. PKs and FKs link them to other tables as always.

What two conditions must a candidate key satisfy?

Attribute has to be unique and it has to identify a row in a relation.

What are the various types of indexes?

Bitmap index --> stores most of its data as bit arrays Dense index --> An index record appears for every search key value in file - Hash index Sparse index --> Index records are created only for some of the records. Reverse key index --> reverses the key value before entering it in the index of its data as bit arrays, e.g., the key value 24538 becomes 83542 in the index. Reversing the key value is particularly useful for indexing data such as sequence numbers

Which SQL statement will select all rows from a table called "Employee" and orders the results by "Employee_Name" A. SELECT * FROM Employee ORDER Employee_Name; B. SELECT * FROM Employee ORDER ON Employee_Name; C. SELECT * FROM Employee ORDER BY Employee_Name; D. SELECT * FROM Employee ORDERED Employee_Name;

C

What is the difference between the functions Count( ), Count(*) and COUNT DISTINCT?

Count( ) and Count (*) are the same as it both counts the number of records in a table. Eg. Select Count(*)/( ) AS NumberofCustomers from Customers adds up all the customers from customer table. Count Distinct totals up unique customers in a table i.e. it doesn't one customer twice from a record

What are the basic statements of DDL?

Create, Alter, Drop

What is the SQL equivalent of the CRUD acronym?

Create, Read, Update, Delete

What operations are classified as Data Manipulation Language (DML)?

Create, Read, Update, Delete, Select

. Which of the following CASE expressions says if a Score is 60 or above it's a "Pass" or below 60 it's a "Fail"? A. CASE IF n >= 60 THEN 'Pass' OR n < 60 THEN 'Fail' B. CASE IF n >= 60 THEN 'Pass' ELSE 'Fail' C. CASE IF n >= 60 THEN 'Fail' OR n < 60 THEN 'Pass' D. CASE WHEN n >= 60 THEN 'Pass' ELSE 'Fail'

D

What command is used to delete a table from a database?

DELETE

What is the attribute on the left-hand side of the arrow in a functional dependency called?

Determinant

What qualifier is used in the SQL Select command to eliminate duplicate rows in a query?

Distinct

What is horizontal partitioning?

Distribution of the rows of a logical relation into several separate tables.

What researcher defined the relational data model?

Edgar F. Codd

1.In the following reduced form or shorthand notation EMPLOYEE(Emp_ID, Name, Dept_Name, Salary). What is the primary key for the table?

Emp_ID

What do we call a primary key whose value is unique across all relations?

Enterprise Key

What is a primary key whose value is unique across all relations called?

Enterprise Key

1.Relation: Employees(EmployeeID,EmployeeName,ManagerID) data 61 Sue Smith (null) 62 David Jones 61 63 Troy Parker 61 64 Claire Smith-Jones 63 65 Grover Rivers 63 What employees are managers?

Ex: Select e.LastName AS Employee, mLastName AS Manager FROM employees e INNER JOIN employees m ON (e.Manager_ID = m.Manage_ID)

An equi-join is a join in which one of the duplicate columns is eliminated in the result table.

F

T/F A relation is in first normal form when there are no repeating groups in the relation and there is only one determinant of the relation.

F

What is an attribute in a relation of a database that serves as the primary key of another relation in the same database is called?

Foreign Key

A relation is in which normal form if it is in BCNF and has no multivalued dependencies?

Fourth Normal Form

11.How does horizontal partitioning differ from vertical partitioning?

Horizontal Partitioning- distributing the rows of a table into several separate files - Useful for situations where different users need access to different rows Vertical Partitioning - dist the columns of a table into several separate files - Useful for situations where diff users need access to diff columns; (primary key must be repeated in each file)

What happens when you issue the DELETE FROM tablename command without specifying a WHERE condition?

If WHERE clause is not present, all records will be deleted

What is an example of an update anomaly?

Information appears in multiple rows so may get logical inconsistencies. Ex: For Employee skills table might contain EmployeeID, Employee Address, and Skill so a change in address for a certain employee may have to be applied to multiple records.

What type of join can be further classified as an equi-join, natural join, or cross-join?

Inner join

What is the "key to successful denormalization"?

Is to make sure that end users of the system never have to manually duplicate or maintain the redundant data.

What is the purpose of the SQL HAVING clause?

It is used for aggregate functions b/c WHERE does not work with aggregate functions

What is a common hashing algorithm? Explain.

It's usually a division remainder to determine a record position by dividing each primary key by a suitable prime number and then using the remainder of the division as the relative storage location. Eg. If there are 1000 employees, then closest prime number is 997. Now if an employee has a record of 12396, then we divide this by 997 to get 443 which is stored on file.

What are the "left" and "right" tables in an outer join?

Left table is the first table and right is the second table.

What is the main motivation for creating an enterprise key for a database?

Make it easier to search

Oracle doesn't have a boolean data type. How do Oracle developers typically indicate a 'Y' or 'N', 'T' or 'F' value?

Oracle developers typically use a CHAR(1) data type, and assign it values of 'T' or 'F').

In Oracle DBMS what is the language that has all of the conditional (IF ...THEN) looping (WHILE), assignment, variable declaration and other language constructs called?

PL/SQL

What is a functional dependency between two or more nonkey attributes called?

Partial Functional Dependency

What is it called when the value for a non-key attribute of a relation is dependent on the value of some part of the relation's primary key, but not all of it?

Partial Functional Dependency

What would we call a functional dependency between two (or more) non-key attributes?

Partial Functional Dependency

How would you model the repeated attribute phone number in a Contacts database?

Phone 0...*: Phone Number (phoneType)(number)

What are the two parts of the internal schema?

Physical and Logical

What is the term for a group of one or more attributes that uniquely identifies a tuple?

Primary Key

What are positive attributes/advantages of indexing?

Provide faster direct data access

What theoretical RAID level is not used in practice?

RAID 2

What is the most common form of RAID in use today? What is RAID 5?

RAID 5 is most common. RAID 5 offers the advantages of data storage redundancy along with a high level of performance. It works differently from RAID 0 or 1 by using striping and parity. (striping - dist small segments of data across a number of hard drives) (parity - technique that allows data to be reconstructed from the remaining hard drives in the system if one drive fails)

What does the acronym RAID mean?

RAID is Redundant array of independent skills. This is a set of disk drives that appear to the user to be a single disk drive.

What type of integrity constraint maintains consistency among the rows of two relations?

Referential Integrity Constraint

What is an alternate name given to a column or table in any SQL statement called?

Relation or Attribute

What are the requirements for a table to qualify as a relation?

Requirements for a table to qualify as a relation: 1. Table must have a unique name. 2. Every attribute value must be atomic (not multivalued, not composite) 3. Every row must be unique 4. Attributes (columns) in tables must have unique names 5. Order of columns is irrelevant 6. Order of rows is irrelevant

What is a synonym for tuple in a relational database?

Row

How many orders did each customer place in August 2014? What type of join would you use?

SELECT Customer.Customer_Name, Order.Order_ID FROM Customer FULL OUTER JOIN Orders ON Customer.Customer_ID=Order.Customer_ID ORDER BY Customer.Customer_Name;

How would you list the Customer_ID and Customer_Name for all Customers in alphabetical order?

SELECT Customer_ID, Customer_Name FROM Customers ORDER BY Customer_Name;

What is the SQL to list all customers who live in Eastern US states of "FL", "NY", "NY"? List the customers alphabetically by state and alphabetically by customer within each state.

SELECT Customer_Name AS 'Customer Name', State; FROM Customer WHERE State = 'FL' OR State= 'NY' ORDER BY State, Customer_Name;

What SQL pattern is used to produce a list of employees and their managers? What do E.lastName and M.lastName mean in the query?

SELECT Employees, Managers FROM _____; E.lastname means the last name for all the employees in the employees table. M.lastname is the same as for employees.

What is the SQL to list the finish and standard price for all desks with a Standard_Price greater than $300 in the PRODUCT table? (Note: desk is a product description).

SELECT Product_Finish AS 'Finished Product', Standard_Price AS 'Standard Price' FROM Product WHERE Standard_Price>300 AND Product_Description = 'Desk';

What is the Data Definition Language (DDL) used for?

SQL commands to define a database (schema), including creating, altering, and dropping tables and establishing constraints.

A relation that has no multivalued attributes, but does have partial functional or transitive dependencies is in what normal form?

Second Normal Form

How does third normal form differ from second normal form? Explain.

Second Normal Form is in first normal form and gets rid of partial functional dependencies. Whereas third normal form has no transitive functional dependencies.

The selection, or restriction, operation retrieves tuples from a relation, limiting the results to only those that meet a specific criteria. The SQL equivalent of selection is the ______ query statement with a ________ clause.

Select and Where clause

What are the 3 primary Relational Algebra operators?

Selection - Filters the rows Projection - Shows columns you want Join - Join tables together

In the relational model, how are relationships between relations created?

Structures, Operations, Integrity Rules. A relation is a set of tuples. A tuple is an unordered set of attribute values.

Which type of entity has its relationship to another entity determined by an attribute in that other entity called a discriminator?

Subtype Entity

An SQL query that implements an outer join will return rows that do not have matching values in common columns.

T

One of the original purposes of the SQL standard was to provide a vehicle for portability of database definition and application modules between conforming DBMSs.

T

T/F A candidate key is always a determinant, but a determinant may or may not be a candidate key.

T

What records does a full outer join return?

The FULL OUTER JOIN keyword returns all the rows from the left table (Customers), and all the rows from the right table (Orders). If there are rows in "Customers" that do not have matches in "Orders", or if there are rows in "Orders" that do not have matches in "Customers", those rows will be listed as well.

What is a determinant?

The attribute on the left-hand side of the arrow in a functional dependency.

If an identifier is not assigned to the relation formed from the associative entity, what is the default primary key?

The default primary key is the two primary attributes from the other two relations.

What is the purpose of SQL standard?

The purpose of SQL is to provide an interface to a relational database such as Oracle Database, and all SQL statements are instructions to the database.

What does a primary key uniquely identify?

The relation

What is the difference between second normal form and third normalization form?

Third normal form has no transitive dependencies no non key fields are trasitively dependent upon the key fields.

What does the following SQL statement do? Select * From Customer Where Cust_Type = "Best"?

This brings out all of the records from the 'Customer' table where the 'cust_type' f field is Best. 1)It is selecting all attributes 2) From the customers table 3) And they have to be of the customer type best.

What is the goal of database normalization?

To have a well-structured relation containing minimal data redundancy and allows users to insert, delete, and update rows without causing data inconsistencies or anomalies.

What is the purpose for building a logical data model?

To help get more detail and communicate with people like DBA's. Using business terms for relations and attributes provides us with common normalized set of relations.

What is the purpose of physical database design?

Translate the logical description of data into the technical specifications for storing and retrieving data

How does a Full Outer join differ from a Union join?

Union includes all columns from each table that is joined, and an instance for each row of each table, while a Full Outer join is a join in which rows that do not have matching values in common columns are nonetheless included in the result table.

What anomalies does normalization to third normal form help avoid?

Update Anomaly, Insertion Anomaly, Deletion Anomaly

What data type is most appropriate for a character string that is a fixed-width n-character string, padded with spaces as needed?

VARCHAR

What is the purpose of the dateTimeOut attribute in the library loan pattern? and what is this type of attribute called? Is it part of the Loans Primary Key?

We have to know the dateTimeOut in order to pair a customer with the same book more than once. We can call this a discriminator attribute, since it allows us to discriminate between the multiple pairings of customer and book.

Which type of entity cannot exist in the database unless another type of entity also exists in the database, but does not require that the identifier of that other entity be included as part of its own identifier?

Weak Entity

What is an example of a sub key? What does this mean in a relation?

When we have an attribute or set of attributes that are a super key for some of the other attributes in the relation, but not a super key for the entire relation. An example would be zip code

When is an outer join used instead of a natural join?

When you want rows that do not have matching values in common columns are nonetheless included in the result table

In an SQL statement, which parts states the conditions for row selection?

Where

Check the order entry example, how is the derived attribute subtotal indicated in the association class OrderLine in a class diagram?

With a /subtotal meaning that it is a derived attribute

What does the notation X→Y indicate?

Y is functionally dependent on X

Is the SQL SELECT the same as the selection operator in relational algebra?

Yes