AN 324 UKY

Data

Data

Facts that are recorded and can be accessed

information

refers to the data that is accessed by a user for some particular purpose

knowledge

the application of experience to information

Metadata

data that describes the structure and the properties of the data

database

structured collection of related data stored on a computer medium

Database Metadata

represents the structure of the database; ex) data types, data descriptions, names of data structures

Database Management System (DBMS)

used for creation of databases; insertion, storage, retrieval, update, and deletion of the data in the database

database system

computer-based system whose purpose is to enable an efficient interaction between the users and the information captured in a database

Front-end applications

provide a mechanism for easy interaction between the users and the DBMS

End-users (business-users)

users using a database system to support their tasks and processes

indirect interactions

end-user communicating with the database through front-end applications

direct interaction

end-user communicating with the database directly through DBMS

database analyst

involved in the requirements collection, definition, and visualization stage

database designers

involved in the database modeling stage

database developers

in charge of implementing the database model as a functioning database using the DBMS software

Front-end application analysts

in charge of collecting and defining requirements for front-end applications

Front-end applications developers

in charge of creating the front-end applications

Database Administrator (DBA)

perform the tasks related to the maintenance and administration of a database system

Database end users

use a database system to support their work or life related tasks and processes

operational information (transactional information)

the information collected and used in support of day to day operational needs in business and other organizations

operational database

collects and presents operational information in support of daily operational procedures and processes

Analytical Information

the information collected and used in support of analytical tasks

analytical database

collects and presents analytical information in support of analytical tasks

entities

constructs that represent what the database keeps track of; represent various real world notions such as people, places, objects, events, items, and other concepts

attribute

depiction of a characteristic of an entity; represents the details that will be recorded for each entity instance

Unique Attribute

attribute whose value is different for each entity instance; every regular entity must have at least one unique attribute

Cardinality constraint

depict how many instances of one entity can be associated with instances of another entity

composite attribute

attribute that is composed of several attributes (attribute in parenthesis)

Composite Unique Attribute

attribute that is composed of several attributes and whose value is different for each entity instance (Underlined composite attribute)

Multiple unique attributes (candidate keys)

when an entity has more than one unique attribute each unique attribute is also called a candidate key

multivalued attribute

attribute for which instances of an entity can have multiple values for the same attribute (double circle around attribute)

derived attribute

attribute whose values are calculated and not permanently stored in a database (Dotted circle around attribute)

optional attribute

attribute that is allowed to not have a value

degree of relationship

reflects how many entities are involved in the relationship

binary relationship

relationship between 2 entities (Degree 2)

unary relationship

occurs when an entity is involved in a relationship with itself (Degree 1)

weak entity

an entity that does not have a unique attribute of its own (double boxed)

owner entity

entity whose unique attribute provides a mechanism for identifying instances of a weak entity

Identifying relationship

relationship between a weak entity and its owner entity in which each instance of a weak entity is associated with exactly one instance of an owner entity (double line around relationship)

Partial Key

attribute of a weak entity that combined with the unique attribute of the owner entity uniquely identifies the weak entity's instances

Associative Entity

construct used as an alternative way of depicting M:N relationships

relational database model

logical database model that represents a database as a collection of related tables

relation

table in a relational database

relational database

collection of related relations within which each relation has a unique name

Primary Key

column (or a set of columns) whose value is unique for each row

composite primary key

a primary key that is composed of multiple columns

Entity Integrity Constraint

in a relational table, no primary key column can have null values

Foreign Key

column in a relation that refers to a primary key column in another (referred) relation

referential integrity constraint

in each row of a relation containing a foreign key, the value of the foreign key either matches one of the values in the primary key column of the referred relation or the value of the foreign key is null

Granularity of the table

Describes what is depicted by one row in the table

relational database constraints

rules that a relational database has to satisfy in order to be valid (implicit and user-defined constraints)

Update operation

collective term for insert, delete and modify operations

update anomaly

anomalies in relations that contain redundant data, caused by update operations

insertion anomaly

occurs when inserting data about one real-world entity requires inserting data about another real-world entity

Deletion Anomaly

occurs when deletion of data about a real-world entity forces deletion of data about another real-world entity

Modification Anomaly

occurs when, in order to modify one real-world value, the same modification has to be made multiple times

Functional Dependency

occurs when the value of one (or more) columns in each record of a relation uniquely determines the value of another column in that same record of the relation (ex: A->B; ClientID->ClientName)

Trivial Functional Dependency

occurs when an attribute (or a set of attributes) functionally determines itself or its subset

augmented functional dependency

functional dependency that contains an existing functional dependency

Partial functional dependency

occurs when a column of a relation is functionally dependent on a component of a composite primary key

Full key functional dependency

occurs when a primary key functionally determines the column of a relation and no separate component of the primary key partially determines the same column

transitive functional dependency

occurs when nonkey columns functionally determine other nonkey columns of a relation

Normalization

process used to improve the design of relational databases

normal form

term representing a set of particular conditions (whose purpose is reducing data redundancy) that a table has to satisfy

First Normal Form (1NF)

a table is in 1NF if each row is unique and no column in any row contains multiple values

Second Normal Form (2NF)

a table is in 2NF if it is in 1NF and if it does not contain partial functional dependencies

Third Normal Form (3NF)

a table is in 3NF if it is in 2NF and it does not contain transitive functional dependencies

Denormalization

reversing the effect of normalization by joining normalized relations into a relation that is not normalized, in order to improve query performance

Data Definition Language (DDL)

Used to create and modify the structure of the database• Example commands:CREATE, ALTER, DROP

Data Manipulation Language (DML)

• Used to insert, modify, delete and retrieve data • Example commands: INSERT INTO, UPDATE, DELETE, SELECT

Data Control Language (DCL)

Used for data access control

Transaction Control Language (TCL)

Used for managing database transactions

CREATE TABLE

Used for creating and connecting relational tables

DROP TABLE

Used to remove a table from the database

INSERT INTO

Used to populate the created relations with data

SELECT

used for the retrieval of data from the database relations

WHERE

Where condition determines which rows should be retrieved and consequently which rows shouldn't be retrieved

DISTINCT

can be used in conjunction with the SELECT statement; eliminates duplicate values from a query result

ORDER BY

Used to sort the results of the query by one or more columns (or expressions)

LIKE

used for retrieval of records whose values partially match a certain criteria

Aggregate functions

For calculating and summarizing values in queries, SQL provides the following aggregate functions:
COUNT
SUM
AVG
MIN
MAX

GROUP BY

Enables summarizations across the groups of related data within tables

HAVING

determines which groups will be displayed in the result of a query and consequently which groups will not be displayed in the result of the query

nested query

a query that is used within another query (aka inner query)

IN

used for comparison of a value with a set of values

JOIN

facilitates the querying of multiple tables

Alias

An alternative name that can be used anywhere within a query instead of the full relation name

ALTER TABLE

Used to change the structure of the relation, once the relation is already created

UPDATE

used to modify the data stored in database relations

DELETE

used to delete the data stored in database relations

VIEW

mechanism in SQL that allows the structure of a query to be saved in the RDBMS (aka virtual table, it allows people to work on the query without altering the actual data)

Set Operators

Used to combine the results of two (or more) SELECT statements that are union compatible; columns are union compatible if they contain the same number of columns, and if the data types of the columns in one set match the data types of the columns in the other set

UNION

Used to combine the union compatible results of two SELECT statements by listing all rows from the result of the first SELECT statement and all rows from the result of the other SELECT statement

INTERSECT

Used to combine the results of two SELECT statements that are union compatible by listing every row that appears in the result of both of the SELECT statements

MINUS (EXCEPT)

Used to combine the results of two SELECT statements that are union compatible by listing every row from the result of the first SELECT statement that does not appear in the result of the other SELECT statement

Self-JOIN

A join statement that includes a relation that contains a foreign key referring to itself, and joins a relation with itself in a query

INNER JOIN

regular join; Joins records from two tables where the value of a specified column in a record in one table matches the value of a specified column in another (or same) table

OUTER JOIN

Variation of the JOIN operation that supplements the results with the records from one relation that have no match in the other relation

IS NULL

used in queries that contain comparisons with an empty value in a column of a record

EXISTS

In queries where the inner query (nested query) uses columns from the relations listed in the SELECT part of the outer query, the inner query is referred to as a correlated subquery; In such cases, the EXISTS operator can be used to check if the result of the inner correlated query is empty