CIS 355 Test #2

<>SELECT

* FROM table_name or DISTINCT (column) FROM table_name WHERE

DDL Statements

CREATE ALTER DROP

DCL

Data Control Language. Used for GRANT, REVOKE statements.

DDL

Data Definition Language. Used for CREATE, ALTER, DROP statements.

WHERE clause

Includes the conditions for row selection within the items in the FROM clause and the conditions between tables, derived tables, or views for joining. Because SQL is considered a set manipulation language, the WHERE clause is important in defining the set of rows being manipulated.

IDENTITY column

Is a column in a database table that is made up of values generated by the database. Kind of like an auto number.

Homonym

An attribute that may have more than one meaning.

Select Aliases

SELECT clause for column aliases; FROM clause for table aliases

DELETE statement purpose

To remove one or more rows from an existing table; corresponds to the "D" in CRUD.

T-SQL

Transaction SQL. An extended form of SQL that adds declared variables transaction control and error and exception handling.

A relation is in 2NF if...

1. It is in 1NF 2. Contains NO partial functional dependencies

A relation is in 3NF if...

1. It is in 2NF 2. NO transitive dependencies exist

A relation is in 1NF if...

1. There are no repeating groups in the relation 2. A PK has been defined, which uniquely identifies each row in the relation

Properties of Relations

1. Unique name 2. Atomic or single-valued cells 3. Each row is unique - no two rows are identical 4. Each attribute (column) has a unique name 5. Sequence of columns is insignificant 6. Sequence of rows is insignificant

Microsoft SQL Server

A Microsoft product used to manage and store information. Technically, a relational database management system

Functional Dependency

A constraint between two attributes in which the value of one attribute is determined by the value of another attribute.

Relation

A named two-dimensional table of data.

Normalization

A process for evaluating and correcting table structures to minimize data redundancies and data anomalies. The process of decomposing relations with anomalies to produce smaller, well-structured relations.

Alias

An alternative name used for an attribute.

Data type

An attribute that tells what kind of data that value can have.

Repeating group

Any attribute that can have multiple values associated with a single instance of some entity for example. A book might have multiple authors.

Key (prime) attribute

Attribute that is (part of) a candidate key in a relation.

CRUD

Create, Read, Update, Delete

DML

Data Manipulation Language. Used for INSERT, SELECT, UPDATE, and DELETE statements.

DBMS

Database Management System. It stores and manages data.

2NF

Deals with composite keys. If given part of the composite key, could you get somewhere? It is a partial dependency if only part of the composite key gives you information. This is a 2NF violation. You need to have the whole composite key to determine attributes. Any partial functional dependencies have been removed (i.e., nonkey attributes are identified by the whole primary key). THE WHOLE KEY

Transitive dependency

Functional dependency where one non-key attribute determines another non-key attribute. They lead to fact redundancy & data anomalies. A functional dependency between the primary key and one or more nonkey attributes that are dependent on the primary key via another nonkey attribute. OrderID → CustomerID → CustomerName ----In other words, both customer name and address are uniquely identified by CustomerID, but CustomerID is not part of the primary key

Referential Integrity Rule

Maintains consistency among the rows of two relations. A rule that states that either each foreign key value must match a primary key value in another relation or the foreign key value must be null.

Required Clauses

SELECT, FROM

GRANT statement purpose

To allow a user in the current database to work with the data in the current database.

ALTER table statement purpose

To change the structure (metadata) of a table, for example, by adding or dropping columns, adding or dropping constraints. Often used to add foreign key constraints.

UPDATE statement purpose

To change the values of one or more columns in one or more rows of an existing table' corresponds to the "U" in CRUD.

CREATE table statement purpose

To create a table (metadata) in an existing database schema, with column specifications and various integrity constraints.

INSERT statement purpose

To insert a new row into a table; to create an entity occurrence or instance; corresponds to the "C" in CRUD.

REVOKE statement purpose

To remove a previously-granted permission(s).

DROP table purpose

To remove a table and all of its data from a database.

TRUNCATE table statement purpose

To remove all rows from a table without logging the individual row deletes.

SELECT statement purpose

To retrieve, read, or query data from one or more tables; corresponds to the "R" in CRUD. Lists the columns (including expressions involving columns) from base tables, derived tables, or views to be projected into the table that will be the result of the command. (That's the technical way of saying it lists the data you want to display.)

Synonym

Two (or more) attributes that have different names but the same meaning.

PRIMARY KEY constraint

Uniquely identifies each record in a database table. Must contain UNIQUE values and cannot contain NULL values.

Partial dependency

When a non-key attribute of a table is dependent on the value of only a part of the table's primary key, but not the entire primary key. A functional dependency in which one or more nonkey attributes are functionally dependent on part (but not all) of the primary key. OrderID → OrderDate, CustomerID, CustomerName, CustomerAddress ProductID → ProductDescription, ProductFinish, ProductStandardPrice

<>UPDATE

table_name SET column = X WHERE

SQL

Structured Query Language used for database manipulation.

Determinant

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

Weaknesses of normalization

-Cannot catch integrity problems across relations -Not concerned with performance -Many small tables (slows down query processing) -Denormalization can help with performance problems

Strengths of normalization

-Data integrity -Simple table structures -Mature DBMS products & standards -Appropriate for business transaction processing

Well-structured relation

A relation that contains minimal redundancy and allows users to insert, modify, and delete the rows in a table without errors or inconsistencies.

Entity Integrity Rule

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

Schema

A structure that contains descriptions of objects created by a user, such as base tables, views, and constraints, as part of a database.

UNIQUE constraint

A type of column restriction within a table , which dictates that all values in that column must be unique though may be null.

3NF

All attributes in a table are determined only by the candidate keys of that table and not by any non-prime attributes. If not in 2NF, it is not in 3NF. Violates 3NF if non-prime attributes can determine one another. Any transitive dependencies have been removed (i.e., nonkey attributes are identified by only the primary key). NOTHING BUT THE KEY

1NF

Does the primary key determine all? If it does not meet 1NF it is considered ZERO 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 BY THE KEY

CHECK constraint

Enforce domain integrity by limiting the values that are accepted by one or more columns.

NOT NULL constraint

Enforces a column to NOT accept NULL values.

<>DELETE

FROM table_name WHERE

Logical order of execution

FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY

DML Statements

INSERT SELECT UPDATE DELETE

<>INSERT

INTO table_name (column_names) VALUES

FROM clause

Identifies the tables, derived tables, or views from which columns can be chosen to appear in the result table and includes the tables, derived tables, or views needed to join tables to process the query.

SELECT DISTINCT

If the user does not wish to see duplicate rows in the result

Anomolies

Insertion Modification Deletion

DEFAULT constraint

Is used to insert a default value into a column. The default value will be added to all new records, if no other value is specified.

FOREIGN KEY constraint

Is used to prevent actions that would destroy links between tables. Points to a primary key in another table.

ALTER table constraints

May be defined in CREATE TABLE or ALTER TABLE statement.

CREATE table constraints

May be defined in the CREATE TABLE OR ALTER TABLE statement.

SSMS

Microsoft Sequel Server Management Studio.

Relational schema (shorthand notation)

Model or representation of relational database tables (relations), usually specified in shorthand notation, as shown below. R(A,B,C,D) R= Relation name, A,BCD= attribute(s) in R Bolded Underlined attribute(s)=primary key, Italicized attribute(s)=foreign key(s)

Nonkey (nonprime) attribute

Nonprime attribute that is NOT part of any candidate key

<>DROP

TABLE

<>CREATE

TABLE table_name (column name, data type, NOT NULL) CONSTRAINT [name] PRIMARY KEY [column name]

<>ALTER

TABLE table_name ADD CONSTRAINT UQ_Name#1 UNIQUE (column) or TABLE table_name ADD CONSTRAINT fk_Name#1 FOREIGN KEY (column) REFERENCES /parent table/ (parent table column)

Data Integrity

The model includes mechanisms to specify business rules that maintain the integrity of data when they are manipulated.

Denormalization

The process of attempting to optimize the read performance of a database by adding redundant data or by grouping data.