Database_Review_Final

SQL Wildcard Characters used to substitute for any other character(s) in a string. EX: SELECT * FROM Customers WHERE City LIKE '%es%';

% : Substitute for zero or more characters. _ : Substitute for a single character [charlist] : Sets and ranges of characters to match [^charlist] or [!charlist] : Matches only a character NOT specified within the brackets

Tables that are not normalized will experience modification problems:

- Insertion problems: Difficulties inserting data into a relation. - Modification problems: Difficulties modifying data into a relation. - Deletion problems: Difficulties deleting data from a relation.

There are different types of joins available in SQL:

-INNER JOIN: returns rows when there is a match in both tables. -LEFT JOIN: returns all rows from left table, even if there are no matches in right table. -RIGHT JOIN: returns all rows from right table, even if there are no matches in left table. -OUTER JOIN can be used to obtain data that exists in 1table without matching data in other table.

DDL

A data definition language. Used to build and modify the structure of your tables and other objects in the database. When you execute a DDL statement, it takes effect immediately. • The CREATE TABLE statement does exactly that.

DML

A data manipulation language. Data definition and updating. Data retrieval or queries. SELECT statement; it is considered to be part of DML

Foreign Key

A foreign key is a primary key from one table placed into another table. A table may be related to other tables (i.e., a relationship), and to create relationships, you may need to create a foreign key.

NOT operator

A logical operator used to match any condition opposite of the one defined.

subquery

A query that is embedded in another query. used to return data that will be used in the main query as a condition to further restrict the data to be retrieved. It can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, <, >, >=, <=, IN, BETWEEN etc.

Functional Dependency

A relationship between attributes/ columns in which one or more attributes determines the value of another attribute(s) in the same table

recursive relationship

A relationship that a relation has with itself.Recursive relationships adhere to the same rules as binary relationships. 1:1 and 1:M relationships are saved using foreign keys. M:N relationships are saved by creating an intersecting relation.

SQL Aggregate Functions: return a single value, calculated from values in a column.

AVG() - Returns the average value COUNT() - Returns the number of rows FIRST() - Returns the first value LAST() - Returns the last value MAX() - Returns the largest value MIN() - Returns the smallest value SUM() - Returns the sum

Rollforward

Activities recorded in the log files may be replayed. In doing so, all activities are re-applied to the database. This procedure is used to resynchronize restored database data by adding transactions to the last full backup.

First normal form (1NF)

All attributes must be atomic, and no repeating groups. Eliminate multi-valued attributes, and repeated attributes

Varchar

Allows you to store variable length strings

Decimal

An ANSI compatible exact fractional number

Subtype Discriminator

An attribute of the Supertype whose values determine the target subtype or subtypes

Weak Entity

An entity that has no meaning or means of identifying itself and that dependent on a strong entity.

M:M Relationship

An example is Employees can be assigned to more than one project and at the same time projects must have one or more employees assigned to it

1:M Relationship

An example is a department has many employees each employee is assigned to one department

identifiers:

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

Consistent transactions are often referred to by the acronym ACID.

Atomic. Consistent. Isolated. Durable.

Database operation typically involves several transactions. These transactions are atomic and are sometimes called logical units of work:

Before an operation is committed to the database, all LUWs must be successfully completed. If one or more LUW is unsuccessful, a rollback is performed and no changes are saved to the database.

Normalization - 12 Step process

Collect raw data. List raw data with its key. Remove repeating groups. Remove part key dependencies. Remove inter-data dependencies.Remove inter-key dependencies. Apply tests. Identify transitive dependencies. Optimize. Re-apply the tests. Draw ER Diagram. Use the model to design database.

Date Data Type

Complies with the ANSI standard definition for the Gregorian calendar

Three necessary database administration functions are:

Concurrency control. Security. Backup and Recovery.

Referential integrity constraints

Ensure that the values of a column in one table are valid based on the values in another table. Every value of a FK must match a value of an existing primary key.

ER Model is based on

Entities and their attributes. Relationships among entities.

The most important elements of E-R Models are:

Entities. Attributes. Identifiers. Relationships.

Constraints

Every relation has some conditions that must hold for it to be a valid relation. These conditions are called Relational Integrity Constraints.

Strong Entity

Exists independent of other entities

Second normal form (2NF)

First normal form, and no partial functional dependencies. Eliminate sub keys ,where the subkey is part of a composite primary key.

1:1 Relationship

For example, each passenger in a flight is assigned only one seat. Here both the passenger and seat number are unique in the relationship.

Mandatory

If an instance of an entity must always occur for an entity to be included in a relationship

Subtypes can be exclusive or inclusive.

If exclusive, the supertype relates to at most one subtype. If inclusive, the supertype can relate to one or more subtypes.

Implicit versus Explicit Resource Locking

Implicit locks are issued automatically by the DBMS based on an activity. Explicit locks are issued by users requesting exclusive rights to the data.

Boyce-Codd Normal Form (BCNF)

In 3rd NF Are all determinants also candidate keys.

Key constraints force that −

In a relation with a key attribute, no two tuples can have identical values for key attributes. A key attribute can not have NULL values. Key constraints are also referred to as Entity Constraints.

Structured Query Language (SQL)

International standard for creating, processing and querying databases and their tables. (ANSI national standard adopted in 1992). Originally developed by IBM in 1970. SQL:2008 is current standard. SQL is not a programming language, but rather a data sub-language.

1. Solid line 2. Dotted line 3. Crow's foot 4. Single line

Is represented by: 1. for Mandatory relationship 2. for optional relationship 3. for m degree 4. for "1" degree

recursive relationship.

It is possible for an entity to have a relationship to itself

There are three main integrity constraints −

Key constraints Domain constraints Referential integrity constraints

Pessimistic Locking

Lock required resources Read data Process transaction Issue commit Release locks

Rollback

Log files save activities in sequence order. It is possible to undo activities in reverse order that they were originally executed. This is performed to correct/undo erroneous or malicious transaction(s) after a database is recovered from a full backup.

Many-to-one

More than 1entities from entity set A can be associated with at most one entity of entity set B, however an entity from entity set B can be associated with more than one entity from entity set A.

Many-to-many

One entity from A can be associated with more than one entity from B and vice versa.

One-to-one

One entity from entity set A can be associated with at most one entity of entity set B and vice versa.

One-to-many

One entity from entity set A can be associated with more than one entities of entity set B however an entity from entity set B, can be associated with at most one entity.

Entity-Relationship(ER) Model

Originally proposed by Chen in 1976. A conceptual model that views the real world as entities and relationships.

Database management system (DBMS)

PC software application that interacts with user, other applications, and database itself to capture & analyze data. It allow the definition, creation, querying, update, and administration of databases. Well-known DBMSs: MySQL, PostgreSQL, Microsoft SQL Server, Oracle, Sybase and IBM DB2.

Concurrency:

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

ODBC driver

Processes ODBC requests Submits specific SQL statements to a given type of data source. There is a different driver for each data source type. It is the responsibility of the driver to ensure that standard ODBC commands execute correctly. Converts data source error codes and messages into the ODBC standard codes and messages.

ODBC , Open Database Connectivity

Provides a standard means for each of these requests Defines a standard set of error codes and messages.

Optimistic Locking

Read data, Process transaction Issue update, Look for conflict. IF no conflict occurred THEN commit transaction. ELSE rollback and repeat transaction.

Problems with lists:

Redundancy and multiple themes. Which creates: deletion, update, and insertion problems.

Referential integrity Constraints

Referential integrity constraints work on the concept of Foreign Keys. A foreign key is a key attribute of a relation that can be referred in other relation. It states that if a relation refers to a key attribute of a different or same relation, then that key element must exist.

Rows / Record/ Tuple

Represents a single, implicitly structured data item in a table. Each row in a table represents a set of related data, and every row in the table has the same structure.

Third normal form (3NF)

Second normal form, and n o transitive functional dependencies

ODBC driver manager

Serves as an intermediary between the application and the DBMS drivers When the application requests a connection, the driver: Determines the type of DBMS that processes a given ODBC data source Loads the appropriate driver into memory if needed.

Float variables

Store four-byte or eight-byte floating-point numbers, the scale value for this is one to 53

Int

Stores four bytes of data and its range is -2,147,483,648 to 2,147,483,647

CREATE TABLE statement is used to create a table in a database.

Syntax: CREATE TABLE table_name ( column_name1 data_type(size), column_name2 data_type(size), column_name3 data_type(size), .... );

DELETE statement is used to delete records in a table.

Syntax: DELETE FROM table_name WHERE some_column=some_value;

INSERT INTO Statement is used to add new rows of data to a table in the database.

Syntax: INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)] VALUES (value1, value2, value3,...valueN);

DROP TABLE statement is used to delete a table.

Syntax: DROP TABLE table_name

isolation

The 1992 ANSI SQL standard defines four isolation levels that specify which of the concurrency control problems are allowed to occur.

Database is self-describing when

The database itself contains the definition of its structure. Metadata is data describing the structure of the database data.

Degree of Relationship

The number of participating entities in a relationship defines the degree of the relationship. Binary = degree 2 Ternary = degree 3

Cardinality

The property that describes the maximum degree of participation between instances of two related entities.

Numeric

The same as decimal type

Phantom reads

The transaction re-reads a data set and finds that a new record has been added.

Inconsistent reads

The transaction re-reads a data set and finds that the data has changed.

Dirty reads

The transaction reads a changed record that has not been committed to the database.

Candidate key:

The value of a candidate key can be used to find the value of every other attribute in the table. A simple candidate key consists of only one attribute. A composite candidate key consists of more than one attribute.

consistency

This ensures that the transaction integrity has statement level consistency among all records.

Resource Locking

To avoid concurrency issues, resource locking will disallow transactions from reading, modifying and/or writing to a data set that has been locked.

Relational design principles for normalized relations

To be a well-formed relation, every determinant must be a candidate key. Any relation that is not well formed should be broken into two or more well-formed relations.

Update statement.

To change the data values in an existing row/s Syntax: UPDATE table_name SET column1=value1,column2=value2 WHERE some_column=some_value;

AND,OR,NOT

Used in conditional expressions

Serializable Transactions

When 2 or more transactions are processed concurrently, the results in database should be logically consistent with the results that would have been achieved had the transactions been processed in an arbitrary serial fashion. A scheme for processing concurrent transactions in this way is serializable.

association relationship.

When an intersection table has columns beyond those in the primary key, the relationship.

Asterisk wildcard character (*)

When used with the LIKE operator, you can match any number of occurrences of any character.

Brackets wildcard character ( [] )

When used with the LIKE operator, you can specify a set of characters that match a character in a specific position.

Table/ File/ Relation

a collection of related data held in a structured format within a database. It consists of fields (columns), and rows.

entity class

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

Supertype

a generic entity that has a relationship with one or more subtypes

composite key

a key that contains two or more attributes/columns. For a key to be unique, it must often become a composite key.

Column/ Field/ Attribute

a set of data values of a particular simple type, one for each row of the table.

entity instance

a specific occurrence of an entity within an entity class.

Subtype

a subgrouping of a Supertype entity that is meaningful to the organization (NOT JUST POSSIBLE)

reprocessing,

all activities since the backup was performed are redone. This is a brunt-force technique. This procedure is costly in the effort involved in re-entering the data.

atomic

atomic transaction is one in which all of the database actions occur or none of them do.

Domain Constraints

attributes have specific values in real-world scenario. Example, age can only be a positive integer. Every attribute is bound to have a specific range of values. Example, age cannot be less than zero and telephone numbers cannot contain a digit outside 0-9.

Most modification problems are solved by

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

composite identifier

consists of two or more attributes. E.g., OrderNumber & LineItemNumber are both required.

attributes

describe the entity's characteristics, Attributes have a data type and properties.

Normalization involves

dividing a database into 2 or more tables & defining relationships between tables. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just 1table & then propagated through the rest of the database via the defined relationships.

The DBMS

driver sends requests to data sources on the database server

HAVING

enables you to specify conditions that filter which group results appear in the final results. SELECT column1, column2 FROM table1, table2 WHERE [ conditions ] GROUP BY column1, column2 HAVING [ conditions ] ORDER BY column1, column2

Concurrency control

ensures that one user's actions do not adversely impact another user's actions.

intersection table

has a composite key consisting of the keys from each of the tables that it connects.

primary key

is a candidate key chosen to be the main key for the relation. If you know the value of the primary key, you will be able to uniquely identify a single row.

application programming interface (API).

is a collection of objects, methods and properties for executing DBMS functions from program code. Each DBMS has its own API, and APIs vary from one DBMS product to another.

A stored procedure

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

Entity

is a real-world entity having properties called attributes. Every attribute is defined by its set of values called domain. Example, in a school database, a student is considered as an entity. Student has various attributes like name, age, class, etc.

trigger

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

A relation

is a two-dimensional table that has specific characteristics.

A surrogate key

is a unique, numeric value that is added to a relation to serve as the primary key. SK values have no meaning to users and are usually hidden on forms, queries, and reports. Often used in place of a composite primary key.

SQL Views

is a virtual table based on the result-set of an SQL statement. Syntax: CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition

Entity-Relationship (ER) Model i

is based on notion of real-world entities & relationships among them. While formulating real-world scenario into the database model, the ER Model creates entity set, relationship set, general attributes and constraints.

candidate key

is called "candidate" because it is a candidate to become the primary key. A candidate key is a unique key.

An entity

is something of importance to a user that needs to be represented in a database. An entity represents one theme or topic and is represented by a table.

Maximum cardinality

is the maximum number of entity instances that may participate in a relationship instance—one, many, or some other fixed number.

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).

Pessimistic Locking

is when you lock the record for your exclusive use until you have finished with it. It has much better integrity than optimistic locking but requires you to be careful with your application design to avoid Deadlocks.

DISTINCT

keyword can be used to return only distinct (different) values. Syntax: SELECT DISTINCT column_name,column_name FROM table_name;

The table dimensions

like a matrix, consist of rows/tuples and columns/ attributes.

Metadata

literally "data about data." This term refers to info. about data itself -- perhaps the origin, size, formatting or other characteristics of a data item. In the database field, metadata is essential to understanding and interpreting the contents of a data warehouse.

Relationship

logical association among entities is called relationship. Relationships are mapped with entities in various ways. Mapping cardinalities define the number of association between two entities.

Three-tier architecture

means that the Web server and the DBMS are on separate servers.

Two-tier architecture

means that the Web server and the DBMS are on the same server.

SQL Defined

nonprocedural language that is used to manipulate and retrieve data from relational DBMSs such as Microsoft Access, DB2, Sybase, and Microsoft SQL Server. It is considered nonprocedural because of the way operations are carried out.

NOT NULL

obligate to specify particular value for the field. Use NOT NULL when you want forbid empty values for the field. Additional goal that the most DBs works slowly with NULL-values. So if you doubt use NOT NULL.

IN

operator allows you to specify multiple values in a WHERE clause. Syntax: SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...);

LIKE

operator is used to search for a specified pattern in a column. Syntax: SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern;

BETWEEN

operator selects values within a range. The values can be numbers, text, or dates. Syntax: SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;

Normalizing relations

or breaking them apart into many component relations may significantly increase the complexity of the data structure.

Normalization

process of analyzing a relation to ensure that it is well formed/rows can be inserted, deleted, or modified without creating update anomalies.

Referential integrity

property of data which, when satisfied, requires every value of one attribute (column) of a relation (table) to exist as a value of another attribute in a different (or the same) relation (table). When a foreign key value is used it must reference a valid, existing primary key in the parent table.

Tiers

refer to the number of computers involved in the Web database application. The workstation with Web browser is the first tier.

Binary Relationship and Cardinality

relationship where 2 entities are participating is called a binary relationship. Cardinality is the number of instance of an entity from a relation that can be associated with the relation.

Entities can be associated with one another in

relationships.

NULL values: ( NULL and 0 are not equivalent)

represent missing unknown data. By default, a table column can hold NULL values. NULL values are treated differently from other values, and is used as a placeholder for unknown or inapplicable values.

ORDER BY

sorts records in ascending order by default. To sort records in descending order, use the DESC keyword. Syntax: SELECT column_name, column_name FROM table_name ORDER BY column_name ASC|DESC, column_name ASC|DESC;

The Open Database Connectivity (ODBC)

standard was developed in the early 1990s. Provides a DBMS-independent means for processing relational database data

UPDATE

statement is used to update records in a table. Syntax: UPDATE table_name SET column1=value1,column2=value2, WHERE some_column=some_value;

Optimistic Locking

strategy where you read a record, take note of a version number (other methods to do this involve dates, timestamps or checksums/hashes) and check that the version hasn't changed before you write the record back. When you write the record back you filter the update on the version to make sure it's atomic.

durable

transaction is one in which all committed changes are permanent.

GROUP BY

used in conjunction with the aggregate functions to group the result-set by one or more columns. Syntax: SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name;

ALTER TABLE command

used to add, delete or modify columns in an existing table. You would also use ALTER TABLE command to add and drop various constraints on a an existing table. Syntax: ALTER TABLE table_name ADD column_name datatype;

Joins clause

used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each.

SELECT statement

used to select data from a database and result is stored in a result table, called the result-set. Syntax: SELECT column_name,column_name FROM table_name;

WHERE

used to specify a condition while fetching the data from single table or joining with multiple tables. SELECT column1, column2, columnN FROM table_name WHERE [condition]