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]