315 NON SQL REVIEW

SQL for 1:N recursive

SELECT * FROM CUSTOMER AS A, CUSTOMER AS B WHERE A.CustomerNumber = B.ReferredBy;

Before an operation is committed to the database

all LUWs must be successfully completed

reprocessing

all activities since the backup was performed are redone. This involves re-entering data. This procedure is costly in the effort involved in re-entering the data. This procedure is risky in that human error is likely and in that paper record-keeping may not be accurate.

After the requirements have been gathered, they are transformed into an

an Entity Relationship (E-R) Data Model.

Internet Application Processing is

more complicated than traditional application processing

Common causes of database failures

Hardware failures Programming bugs Human errors/mistakes Malicious actions

SQL COMMIT and ROLLBACK statements

IF transaction has completed normally THEN COMMIT TRANSACTION ELSE ROLLBACK TRANSACTION END IF

These databases are often classified into four categories

Key-Value—Dynamo and MemcacheDB Document—Couchbase and MongoDB Column Family—Apache Cassandra and HBase Graph—Neo4J and AllegroGraph

OLAP systems produce an OLAP report, also know as an

OLAP cube.

Excel PivotTables can be used to create

OLAP reports.

What are sources of requirements?

User Interviews Forms Reports Queries Use Cases Business Rules

Phantom Reads

The transaction rereads data and finds new rows inserted by a committed transaction

Processing rights define

who is permitted to do what and when. The individuals performing these activities have full responsibility for the implications of their actions. Individuals are identified by a username and a password.

Concurrency

People or applications may try to update the same information at the same time.

Some relational DBMS vendors have added object-oriented features to their products such as

oracle

As database failures are impossible to completely avoid...

recovery procedures are essential.

Entities can be associated with one another in

relationships

If one or more LUW is unsuccessful, a

rollback is performed and no changes are saved to the database.

Database Security

strives to ensure that Only authenticated users perform authorized activities

Any entity that is not a weak entity is called a

strong entity.

A subtype entity is a special case of another entity called.

supertype

recursive relationship

when an entity to has a relationship to itself

trigger

a stored procedure that is automatically invoked by the DBMS when a specified activity occurs. ex. BEFORE, AFTER, and INSTEAD OF

three types of data marts arising from data warehouses

- web sales data mart - store sales data mart - inventory data mar

What are the steps to Transforming a Data Model into a Relational Design?

1. Create a table for each entity 2. Create relationships by placing foreign keys

Pessimistic locking steps

1. Lock required resources 2. Read data 3. Process transaction 4. Issue commit 5. Release locks

Optimistic locking steps

1. Read data 2. Process transaction 3. Issue update 4. Look for conflict 5. IF no conflict occurred THEN commit transaction ELSE rollback and repeat transaction

The three stages of database development are:

1. Requirements Analysis Stage 2. Component Design Stage 3. Implementation Stage

SQL for 1:N

SELECT * FROM ITEM, QUOTATION WHERE ITEM.ItemNumber = QUOTATION.ItemNumber;

Record retention

When information should be discarded

Dirty Read

The transaction reads a row that has been changed, but the change has not been committed. If the change is rolled back, the transaction has incorrect data.

AMP

a combination of the Apache Web server, the MySQL DBMS, and the PHP, Perl or Python programming language.

On occasions, two transactions may indefinitely wait on each another to release resources—This condition is known as

a deadlock or the deadly embrace.

stored procedure

a module similar to the subroutine or function that performs database actions. Stored in the database itself

BI systems fall into two broad categories:

(1) Reporting systems - that sort, filter, group, and make elementary calculations on operational data. (2) Data mining applications that perform sophisticated analyses on data; analyses that usually involve complex statistical and mathematical processing.

Problems with Operational Data

- "Dirty Data" - Missing Values - Inconsistent Data - Nonintegrated Data (data from two or more sources that need to be combined) - Incorrect Format - Too Much Data

Representing Entities with the Relational Model

- Create a relation for each entity - Specify a primary key - Specify column properties - The relation is then analyzed using the normalization rules - As normalization issues arise, the initial relation design may need to change

LAMP

AMP running on Linux

WAMP

AMP running on Windows

Data is stored in "documents" which are stored in which types of formats?

BSON binary JSON format

A database is distributed when it is:

Both partitioned and replicated

Interdependency

Changes required by one user may impact others.

Three necessary database administration functions are

Concurrency control Security Backup and Recovery

Non-repeatable Read

Data read twice inside the same transaction cannot be guaranteed to contain the same value. Another transaction could have nipped in and updated the value between the two reads.

Relationship degree defines the number of entity classes participating in the relationship

Degree 2 is a binary relationship. Degree 3 is a ternary relationship

An associative entity (also called an association entity) is used when there are attributes that are

associated with the relationship between two entities rather than with either of the two entities themselves.A new entity is then created to link the two original entities and hold the attributes

Most modification problems are solved by

breaking an existing table into two or more tables through a process known as normalization

Entities have attributes that describe the entity's

characteristics

An API is a

collection of objects, methods and properties for executing DBMS functions from program code.

To redo a transaction the log must

contain a copy of every database record (or page) after it was changed.These records are called after-images. A transaction is redone by applying after-images of all its changes to the database.

To undo a transaction the log must

contain a copy of every database record before it was changed. Such records are called before-images. A transaction is undone by applying before-images of all its changes to the database.

Relationships are named and classified by their cardinality, which is a word that means

count

Attributes have a

data type and properties.

An entity class is a

description of the structure and format of the occurrences of the entity

The OLAP report uses inputs called

dimensions

An attribute of the supertype may be included that indicates which of the subtypes is appropriate for a given instance; this attribute is called a

discriminator

Concurrency control

ensures that one user's actions do not adversely impact another user's actions. At the core of concurrency is accessibility

Entity instances have identifiers. An identifier will

identify a particular instance in the entity class. Identifiers may be unique or nonunique. If the identifier is unique, the data value for the identifier must be unique for all instances.

Database Security Guidelines

- Run the DBMS behind a firewall. - Apply the latest operating system and DBMS service packs and patches. - Limit DBMS functionality to needed features. - Protect the computer that runs the DBMS. - Manage accounts and passwords

The most important elements of E-R Models are

Entities Attributes Identifiers Relationships

Data may need to be transformed for use in a data warehouse.

Example {CountryCode > CountryName} "US" > "United States"

A weak entity is

an entity that cannot exist in the database without the existence of another entity.

RFM Analysis

analyzes and ranks customers according to purchasing patterns: R = Recent (most recent order) F = Frequent (how often an order is made) M = Money (dollar amount of orders) Customers are sorted into five groups, each containing 20% of the customers.

Every DBMS product has an

application programming interface (API)

A database operation typically involves several transactions. These transactions are

are atomic and are sometimes called logical units of work (LUW).

Business intelligence (BI) systems are

are information systems that Assist managers and other professionals in the analysis of current and past activities and in the prediction of future events. Do not support operational activities, such as the recording and processing of orders.

Implicit locks

are issued automatically by the DBMS based on an activity.

Explicit locks

are issued by users requesting exclusive rights to the data.

Products such as oracle that have object-oriented features are known as

object-relational DBMSs and support object-relational databases.

OnLine Analytical Processing (OLAP)

is a technique for dynamically examining database data. OLAP uses arithmetic functions such as Sum and Average.

Object-oriented programming (OOP)

is based on objects, and OOP is now used as the basis of many computer programming languages

Minimum cardinality

is the minimum number of entity instances that must participate in a relationship instance. These values typically assume a value of zero (optional) or one (mandatory).

An associative entity

is used when there are attributes that are associated with the relationship between two entities rather than with either of the two entities themselves.

Most database management systems provide a mechanism to record activities into a

log file.

Maximum cardinality is many for both ITEM and SUPPLIER. Minimum cardinality is zero (optional) for ITEM and one (mandatory) SUPPLIER .

many-to-many A SUPPLIER does not have to supply an ITEM. An ITEM must have a SUPPLIER. ITEM-0---<1:N>---|-SUPPLIER

(N:M)

many-to-many a supplier may supply several items; and a particular item may be supplied by several suppliers. ITEM----<1:N>----SUPPLIER

The OLAP report calculates outputs called

measures

Tables that are not normalized will experience issues known as

modification problems

Tiers refer to the

number of computers involved in the Web database application

Database users are known as an individual and as a member of

one or more roles.

What are some of the components of the data processing environment

- forms - active server - queries - Java server pages - database - application programs (C#, Java, etc.) - stored procedures - triggers - reports

Components of a Data Warehouse

- operational databases - other internal data - external data - ETL system - Data warehouse database - Data warehouse metadata

What are the 3 types of modification problems?

1. Insertion problems 2. Modification problems 3. Deletion problems

NoSQL movement

The NoSQL movement is a movement to use non-relational databases. Cannot easily join data in tables, but very fast to read and write data.

(1:N)

one-to-many A quotation is associated with only one item; and An item may have several quotations ITEM----<1:N>----QUOTATION

(1:1)

one-to-one An employee may have no more than one locker; and A locker may only be accessible by one employee EMPLOYEE----<1:1>----LOCKER

To avoid concurrency issues,_________ ________ will disallow transactions from reading, modifying and/or writing to a data set that has been ________

resource locking; locked

The log file is used for recovery via

rollback or rollforward.

An entity instance is a

specific occurrence of an entity within an entity class.

Specifically, with Internet Application Processing

The network becomes an integral part of the application

API interface standards are used to make it easier for programmers. they write to the interface standard instead of the DBMS API. The two known ones are;

ODBC - Open Database Connectivity JDBC - Java Database Connectivity

SQL for 1:1

SELECT * FROM LOCKER, EMPLOYEE WHERE LOCKER.LockerNumber = EMPLOYEE.LockerNumber;

SQL for 1:1 recursive

SELECT * FROM PERSON1 AS A, PERSON1 AS B WHERE A.Person = B.PersonSponsored;

SQL for N:M

SELECT * FROM STUDENT, CLASS, STUDENT_CLASS WHERE STUDENT.SID = STUDENT_CLASS.SID AND STUDENT_CLASS.ClassNumber = CLASS.ClassNumber;

SQL for N:M recursive

SELECT * FROM DOCTOR AS A, DOCTOR AS B, TREATMENT-INTERSECTION WHERE A.Name = TREATMENT-INTERSECTION.Physician AND TREATMENT-INTERSECTION.Patient = B.Name;