EXAM 2 DATABASE

What allows you to override normal precedence rules?

(parentheses)

What are criteria to meet to be in First Normal Form?

- No multivalued attributes - Every attribute value is atomic

When did E. F. Codd develop the relational database concept?

1970

Define a natural (inner) join?

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

Define Referential integrity?

Constraint that ensures that foreign key values of a table must match primary key values of a related table in 1:N relationships.

What do relations (tables) correspond with?

Correspond with entity types and with many-many relationship instances.

Define a vector aggregate?

Multiple values from an SQL query that includes an aggregate function.

What is Entity Integrity?

No primary key attribute may be null. All primary key fields MUST contain data values.

What does a SQL command end with?

Semicolon ;

T/F Mapping associative entities to relational databases is essentially identical to mapping M:N relationships to relational databases.

TRUE

What is a key feature that identifies a correlated subquery?

The fact that the subquery makes reference to a column from the outer query

What are the Delete Rules ?

▪Restrict - don't allow delete of "parent" side if related rows exist in "dependent" side ▪Cascade - automatically delete "dependent" side rows that correspond with the "parent" side row to be deleted ▪Set-to-Null - set the foreign key in the dependent side to null if deleting from the parent side → not allowed for weak entities

How to map a weak entity?

* Pay attention to the dependent relation schema. * Introducing EmployeeID to the dependent row makes this relation a strong one.

Show an example of mapping a 1:N relationship?

* Take note as to how CustomerID( PK on Customer line ) becomes a foreign key on the ORDER line.

What does 2nd normal form entail?

- 1NF plus every non-key attribute is fully functionally dependent on the ENTIRE primary key -Every non-key attribute must be defined by the entire key, not by only part of the key -No partial functional dependencies

Define an outer join?

- A join in which rows that do not have matching values in common columns are nonetheless included in the result table. - Result in the union of two tables.

What is Data Normalization?

- A tool used to validate and improve a logical design so that it satisfies certain constraints that avoid unnecessary duplication of data. - The process of decomposing relations with anomalies to produce smaller, well-structured relations.

Define Schema ? How many relations are in the diagram?

- Digram representation of relations. - Four Relations ( Customer , Order , Order Line , Product)

Which join type os the faster in SQL and why?

- Inner-join because it only returns the rows that are matched based on the join column.

What are the three anomaly types?

- Insertion Anomaly: adding new rows forces user to create duplicate data - Deletion Anomaly: deleting rows may cause a loss of data that would be needed for other future rows -Modification Anomaly : changing data in a row forces changes to other rows because of duplication

Things to keep in mind when mapping supertype/subtype relationships ?

- One relation for supertype and for each subtype - Supertype attributes (including identifier and subtype discriminator) go into supertype relation - Subtype attributes go into each subtype; primary key of supertype relation also becomes primary key of subtype relation. - 1:1 relationship established between supertype and each subtype, with supertype as primary table

What are subquires and what do they do?

- Place an inner query (SELECT statement) inside an outer query - More detailed, but require longer time to process because of the amount of detail involved with them.

Define an inner join?

- Result in the intersection of two tables. -Rows must have matching values in order to appear in the result table)

What is Referential Integrity ?

- Rules that maintain consistency between the rows of two related tables. - Rule states that any foreign key value (on the relation of the many side) MUST match a primary key value in the relation of the one side.(Or the foreign key can be null.)

What does a Data Structure consist of ?

- Tables (relations) , rows, columns.

What does the left outer join do?

- This clause causes rows from the first mentioned table (customer) to appear even if there is no corresponding order data. - Unlike an INNER join, this will include customer rows with no matching order rows. (Null values)

What is a dynamic view ?

-A "virtual table" created dynamically upon request by a user -No data actually stored; instead data from base table made available to user -Based on S Q L SELECT statement on base tables or other views

What is a materialized view?

-Copy or replication of data, data actually stored -Must be refreshed periodically to match corresponding base tables

What is a union join?

-Includes all data from each table that was joined

Subqueries can be what?

-Noncorrelated - executed once for the entire outer query -Correlated - executed once for each row returned by the outer query

How to map Binary Relations ?

-One-to-Many - Primary key on the one side becomes a foreign key on the many side -Many-to-Many - Create a new relation with the primary keys of the two entities as its primary key One-to-One - Primary key on mandatory side becomes a foreign key on optional side

What are the three types of relationships and their notations?

1. 1:1 = One to One 2. 1:N = One to Many 3. M:N = Many to Many

What are the three types of anomalies ?

1. Insertion 2.deletion 3.modification

What are the three functional dependency types?

1. Partial dependency: Consists on a primary key. 2. Full dependency: Consists of all primary keys together. 3.Transitive dependency: A functional dependency between the primary key and one or more non key attributes that are dependent on the primary key via another non key attribute.

What are two types of key field?

1. Primary Key > Unique identifiers 2. Foreign key > dependent ( on parent relation)

What are the steps in table creation?

1.Identify data types for attributes 2.Identify columns that can and cannot be null 3.Identify columns that must be unique (candidate keys) 4.Identify primary key-foreign key mates 5.Determine default values 6.Identify constraints on columns (domain specifications) 7.Create the table and associated indexes

When was the relational data model first introduced?

1970 by E. F. Codd,

What form are relations in

1st Normal form

Define RDBMS?

A database management system that manages data as a collection of tables in which all relationships are represented by common values in related tables

Define an Equi-join?

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

What type of relation transformation does this digram show?

A non relation table transformed into first normal form.

Define a join?

A relational operation that causes two or more tables with a common domain to be combined into a single table or view

What is the Referential integrity constrain?

A rule that states that either each foreign key value must matcha primary key value in another relation or the foreign key value must be null.

What is the Entity integrity rule?

A rule that states that no primary key attribute (or component of a primary key attribute) may be null.

Define a catalog?

A set of schemas that constitute the description of a database

Define a scalar aggregate?

A single value returned from an SQL query that includes an aggregate function.

Define Null

A value that may be assigned to an attribute when no other value applies or when the applicable value is unknown.

Which function allows you to change tables ?

ALTER TABLE statement allows you to change column specifications:

What are Domain Constraints?

Allowable values for an attribute (includes data types and restrictions on values)

Define an Alias?

An alternative column or table name

What is an Anomaly?

An error or inconsistency that may result when a user attempts to update a table that contains redundant data .

What do relation table columns correspond with ?

Attributes

What function to use to find totals?

COUNT aggregate function. •Note: With aggregate functions you can't have single-valued columns included in the SELECT clause, unless they are included in the GROUP BY clause.

What if the difference between multivalued and composite ?

Composite :Composite attributes can be divided into subparts. For example, an attribute name could be structured as a composite attribute consisting of first-name, middle-initial, and last-name. Multivalued:There may be instances where an attribute has a set of values for a specific-entity. Consider an employee entity set with the attribute phone-number. An employee may have zero, one, or several phone numbers, and different employees may have different numbers of phones. This type of attribute is said to be Composite Attributes : Attribute Divided into sub parts. Eg. Name (First name,Middle Name, last name) Multivalued Attributes : Attribute having more than one values. Eg. PhoneNumber.

What happens any time you have a many-to-many relationship, whether or not it contains its own attributes?

It must be implemented as a separate relation (table). This is often called an intersection table or a junction table.

What does the MERGE statement do ?

Makes it easier to update a table. It allows combination of Insert and Update in one statement. Useful for updating master tables with new data.

What is Data integrity?

Mechanisms for implementing business rules that maintain integrity of manipulated data

What is the associative entity in the given diagram?

Order Line

What is the difference between primary key and foreign key ?

PK:An attribute or a combination of attributes that uniquely identifies each row in a relation. FK:An attribute in a relation that serves as the primary key of another relation in the same database

What is Data manipulation?

Powerful SQL operations for retrieving and modifying data

What is another name for candidate Key?

Primary Key (A unique identifier)

What does a relation =?

Relation = table * A relation is a named, two-dimensional; table of data . *•Consists of rows (records) and columns (attribute or field) *Requirements for a table to qualify as a relation: 1.Each relation (or table) in a database has a unique name. 2.An entry at the intersection of each row and column is atomic (or single valued). There can be only one value associated with each attribute on a specific row of a table; no multivalued attributes are allowed in a relation. 3.Each row is unique; no two rows in a relation can be identical. 4.Each attribute (or column) within a table has a unique name. 5.The sequence of columns (left to right) is insignificant. The order of the columns in a relation can be changed without changing the meaning or use of the relation. 6. The sequence of rows (top to bottom) is insignificant. As with columns, the order of the rows of a relation may be changed or stored in any sequence.

What does a well-structured relation entail?

Relations that contain minimal data redundancy and allow users to insert, delete, and update rows without causing data inconsistencies

How to map a multivalued attribute? What is the multivalued attribute in the given diagram?

Skill

What does SQL stand for?

Standard Query Language or sequel

Define a determinant ?

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

Define a schema?

The structure that contains descriptions of objects created by a user (base tables, views, constraints)

What is functional dependency?

The value of one attribute (the determinant) determines the value of another attribute

If you ever see a database table where the entire primary key is also a foreign key, this should imply what?

There is a supertype-subtype relationship involved.

If a relationship schema has a double line .. is it indicating a strong relationship or a weak one?

Weak = Double line Strong = Single line

Where are subqueries usually placed?

placed in the WHERE or HAVING clause of the outer query

What are some tips for developing queries?

•Be familiar with the data model (entities and relationships) •Understand the desired results •Know the attributes desired in results •Identify the entities that contain desired attributes •Review E R D •Construct a WHERE equality for each link •Fine tune with GROUP BY and HAVING clauses if needed •Consider the effect on unusual data

What are the three SQL commands?

•Data Definition Language (DDL) -Commands that define a database, including creating, altering, and dropping tables and establishing constraints •Data Manipulation Language (DML) -Commands that maintain and query a database •Data Control Language (DCL) -Commands that control a database, including administering privileges and committing data

List the anomalies in the digram ?

•Insertion - can't enter a new employee without having the employee take a class (or at least empty fields of class information) •Deletion - if we remove employee 140, we lose information about the existence of a Tax Acc class •Modification - giving a salary increase to employee 100 forces us to update multiple records

What is the difference between correlated and non-correlated subqueries?

•Noncorrelated subqueries: -Do not depend on data from the outer query -Execute once for the entire outer query •Correlated subqueries: -Make use of data from the outer query -Execute once for each row of the outer query -Can use the EXISTS and ALL operators

List the benefits of a standardized relational language?

•Reduced training costs •Productivity •Application portability •Application longevity •Reduced dependence on a single vendor •Cross-system communication

What was the original purpose of SQL standard?

•Specify syntax/semantics for data definition and manipulation •Define data structures and basic operations •Enable portability of database definition and application modules •Specify minimal (level 1) and complete (level 2) standards •Allow for later growth/enhancement to standard (referential integrity, transaction management, user-defined functions, extended join operations, national character sets)

What does EER stand for?

Enhanced entity relation

Entities = ?

Entities= tables= relations.

What do relation table rows correspond with ?

Entity instances and with many to many relationship instances.

Which function allows you to see redundancy?

Equi-join function

T/F Relation = Relationship

False

What are the steps in Normalization?

First normal form Any multivalued attributes (also called repeating groups) have been removed, so there is a single value (possibly null) at the intersection of each row and column of the table (as in Figure 4-2b). Second normal form Any partial functional dependencies have been removed (i.e., nonkey attributes are identified by the whole primary key). Third normal form Any transitive dependencies have been removed (i.e., non- key attributes are identified by only the primary key). Boyce-Codd normal form An y remaining anomalies that result from functional dependencies have been removed (because there was more than one possible primary key for the same nonkeys). Fourth normalform Any multivalued dependencies have been removed. Fifth normalform Any remaining anomalies have been removed.

What clause is an alternative to WHERE clause ?

INNER JOIN clause is an alternative to WHERE clause, and is used to match primary and foreign keys.

What statement to use to add one or more rows to a table?

INSERT Statement

What are joins used for?

To combine contents of different tables.

Give an example of how to map a composite attribute? Which attribute is composite in the diagram given?

Customer Adress * Pay attention to how customer address is not listed in the mapping just the different things that compose customer address.

What statement do you use to remove tables?

DROP TABLE statement allows you to remove tables from your schema:

What must happen before using a union clause?

Each query involved must output the same number of columns, and they must be UNION compatible. This means that the output from each query for each column should be of compatible data types

T/F Goal is to avoid anomalies?

True

Which statement allows you to modify data in existing rows?

UPDATE Statement