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;