DBMS Exam 1 (Chapters 1 - 4)

basic functions of application programs

- Create and process forms - Process user queries - Create and process reports - Execute application logic - Control the application itself

Functions of a DBMS

- Create database - Create tables - Create supporting structures (e.g., indexes) - Modify (insert, update, or delete) database data - Read database data - Maintain database structures - -- Enforce rules - Control concurrency - Perform backup and recovery

DBMS least to most powerful

- Microsoft Access (ADE) - Oracle MySQL - Microsoft SQL Server - IBM DB2 - Oracle's Oracle Database

Practical Problems in Designing Databases from Existing Data

- The multivalue, multicolumn problem - Inconsistent values - Missing values - General-purpose remarks column

two common types of database redesign

- database migration - integrating two or more databases

x --> y. What is "x -->y"? What is x?

- functional dependency (from x we can conclude what y is) - x is called the determinant

Relation Requirements

- the rows of the table must store data about an entity - the columns of the table must store data about the characteristics of those entities. - the names of the columns are unique; no two columns in the same relation may have the same name. - all of the values in a column are of the same kind The order of the columns is unimportant. The order of the rows is unimportant. No two rows may be identical

A null value can indicate one of three conditions:

- the value is inappropriate - the value is appropriate but unknown - the value is appropriate and known, but no one has entered it into the database.

SQL AS keyword (example usage)

/* *** SQL-Query-CH02-33 *** */ SELECT SUM(OrderTotal) AS OrderSum FROM RETAIL_ORDER;

HAVING clause case usage

/* *** SQL-Query-CH02-49 *** */ SELECT Department, COUNT(SKU) AS NumberOfCatalogItems FROM CATALOG_SKU_2017 WHERE CatalogPage IS NOT NULL GROUP BY Department HAVING COUNT(SKU) > 2;

Advantages of normalization

1. Eliminate modification anomalies 2. Reduce duplicated data - Eliminate data integrity problems - Save file space 3. Single table queries will run faster

Process for Putting a Relation into BCNF

1. Identify every functional dependency. Process for Putting a Relation into BCNF 2. Identify every candidate key. 3. If there is a functional dependency that has a determinant that is not a candidate key (Note: if there is more than one such functional dependency, start with the one with the most columns).: A. Move the columns of that functional dependency into a new relation. B. Make the determinant of that functional dependency the primary key of the new relation. C. Leave a copy of the determinant as a foreign key in the original relation. D. Create a referential integrity constraint between the original relation and the new relation. 4. Repeat step 3 until every determinant of every relation is a candidate key.

disadvantages of normalization

1. More complicated SQL required for multi-table subqueries and joins 2. Extra work for DBMS can mean slower applications

concurrency

A DBMS controls concurrency by ensuring that one user's work does not inappropriately interfere with another user's work.

transitive dependency

A condition in which an attribute is dependent on another attribute that is not part of the primary key

database migration

A database is adapted to new or changing requirements. In the migration process, tables may be created, modified, or removed; relationships may be altered; data constraints may be changed; and so forth.

Why are databases self discribing?

A database is self-describing because it contains a description of itself. Thus, databases contain not only tables of user data, but also tables of data that describe that user data. Such descriptive data is called metadata because it is data about data.

relational database

A database that represents data as a collection of tables in which all data relationships are represented by common values in related tables

client-server architecture

A network design in which client computers use a centrally administered server to share data, data storage space, and devices. USERS use DIVICES on which they have CLIENT applications to access SERVICES. These services are processed by SERVERS, which hold the DATABASES with the data needed (for the client apps).

foreign key

A primary key of one table that appears as an attribute in another table and acts to provide a logical relationship between the two tables

3NF

A relation is in 3NF if and only if it is in 2NF and there are no non-key attributes determined by another non-key attribute.

first normal form (1NF)

A relation that has a primary key and in which there are no repeating groups.

recursive relationship (definition and example)

A relationship found within a single entity type (a relationship between columns in the same table). For example, an EMPLOYEE is married to an EMPLOYEE or a PART is a component of another PART.

referential integrity constraint

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. Ex. SKU in ORDER_ITEM must exist in SKU in SKU_DATA

null value

A special field value, distinct from zero, blank, or any other value, that indicates that the value for the field is missing or otherwise unknown.

surrogate key

A system-assigned primary key, generally numeric and auto-incremented. If the numbers used in primary key columns such as StudentNumber and ClassNumber are automatically generated and assigned in the database itself

SQL subquery

An SQL subquery is an SQL query statement used to determine a set of values that are provided (or returned) to the SQL query (often referred to as the top level query) that used (or called) the subquery. A subquery is often described as a nested query or a query within a query

a table

An arrangement of data made up of horizontal rows and vertical columns.

we can eliminate anomalies due to functional dependencies by placing all tables in _____

BCNF

Index

Because this is a large database, searching through the table to find, for example, all employees in the accounting department would take a long time. To improve performance, we can create an index (akin to the index at the back of a book) for DepartmentName to show which employees are in which departments. Such an index is an example of a supporting structure that is created and maintained by a DBMS.

BCNF

Boyce-Codd Normal Form A relation is in BCNF if and only if it is in 3NF and every determinant is a candidate key The only way a relation in 3NF can have problems actually requiring further normalization work to get it into BCNF is if it has overlapping composite candidate keys. If the relation (1) does not have composite candidate keys or (2) has non-overlapping composite candidate keys, then it is already in BCNF once it is in 3NF.

POS system

Businesses use point of sale (POS) systems to record every purchase in a database, to monitor inventory, and, if you have a sales promotion card from the store (the one you use to get those special prices for "card holders only"), to keep track of everything you buy for marketing purposes. All the data POS systems gather is stored in, of course, a database.

CHAR vs VARCHAR

CHAR columns use a fixed number of bytes to store the data, whereas VARCHAR columns adjust the number of bytes used to fit the actual length of the data

AVG

Calculate the average of all values (numeric columns only)

MAX

Calculate the maximum value of all values

MIN

Calculate the minimum value of all values

SUM

Calculate the sum of all values (numeric columns only)

COUNT({Name})

Count the number of rows in the table where column {Name} IS NOT NULL

If we want to count the unique values of Department, we need to use the SQL ______keyword as follows: (example for this table)

DISTINCT keyword; SELECT COUNT(DISTINCT Department) AS DeptCount FROM SKU_DATA;

Open Database Connectivity (ODBC)

Database middleware developed by Microsoft to provide a database access API to Windows applications.

e-commerce database application

Databases are a key component of e-commerce order entry, billing, shipping, and customer support. Surprisingly, however, the largest databases at an e-commerce site are not order-processing databases. The largest databases are those that track customer browser behavior

Digital dashboards (function)

Digital dashboards and other reporting systems assess past and current performance.

NOT LIKE

Does NOT match a sequence of characters

columns (def and alternative name)

Each column of a table stores a characteristic common to all rows Columns are also known as fields

row (def and alternative name)

Each row of a table has data about a particular occurrence, or instance of the thing of interest also called records

In 1970, a then little-known IBM engineer named __________- (better known as just _____) published a paper in the _______________ in which he applied the concepts of a branch of mathematics called relational algebra to the problem of "shared data banks," as databases were then known. The results of this work are now the relation_______ for databases, and all ______ DBMS products are built on this model.

Edgar Frank Codd; E. F. Codd; Communications of the ACM relational model; relational database;

referential integrity constraints

Every value in a foreign key must have a corresponding, valid value in the referenced primary key

RTRIM (meaning plus example)

For example, if we store the value "four" in a CHAR(8) column named Number, the DBMS will actually store "four " ("four" plus four spaces). To deal with these extra spaces, we use the RTRIM function to remove the trailing blanks before the comparison is performed: WHERE RTRIM(Number) LIKE 'four';

Funny widely known phrase to help with 1NF, 2NF, 3NF and BCNF. Explain

I swear to construct my tables so that all non-key columns are dependent on ■ the key, [This is 1NF] ■ the whole key, [This is 2NF] ■ and nothing but the key, [This is 3NF and BCNF] so help me Codd!

Over time, Codd convinced IBM management to develop relational model DBMS products. The result was ________ and its variants, which are still very popular today

IBM's DB2

SQL ORDER BY clause

If you want the DBMS to display the rows in a particular (default is ascending); ASC; DESC

entity integrity constraint

In order to function properly, a primary key, whether it is a single column or a composite key, must have unique data values inserted into every row of the table or in other words ... in a relational table, no primary key column can have null (empty) values

composite keys.

Keys that have two or more columns

if (A, B) --> C, then can we say that A-->C?

No. Neither A nor B will determine C by itself

fifth normal form (5NF), also known as

Project-Join Normal Form (PJ/NF)

SQL JOIN ON syntax

SELECT * FROM RETAIL_ORDER JOIN ORDER_ITEM ON RETAIL_ORDER.OrderNumber = ORDER_ITEM.OrderNumber ORDER BY RETAIL_ORDER.OrderNumber, ORDER_ITEM.SKU;

SQL LEFT OUTER JOIN syntax

SELECT StudentPK, StudentName, LockerFK, LockerPK, LockerType FROM STUDENT LEFT OUTER JOIN LOCKER ON STUDENT.LockerFK = LOCKER.LockerPK ORDER BY StudentPK;

SQL RIGHT OUTER JOIN syntax

SELECT StudentPK, StudentName, LockerFK, LockerPK, LockerType FROM STUDENT RIGHT OUTER JOIN LOCKER ON STUDENT.LockerFK = LOCKER.LockerPK ORDER BY LockerPK;

The basic form of SQL queries uses the SQL __________ framework

SELECT/FROM/WHERE

In the SQL SELECT statement, the _____ clause and the _____ clause are the only required clauses in the statement.

SELECT; FROM

One way to limit the results of this query is to use the _____ function. + ex.

SQL TOP {NumberOfRows} SELECT TOP 5 * FROM SKU_DATA;

There are five standard SQL built-in aggregate functions for performing arithmetic on table columns: (name them)

SUM, AVG, MIN, MAX, and COUNT

normalization theory can be divided into three major categories: ....

Source of anomaly: - functional dependencies, (1,2,3NF and BCNF) - multivalued dependencies (4NF) - data constraints and odd conditions (5NF and DK/NF)

SQL (What does it stand for? What is it?)

Structured Query Language (SQL), a database processing language, to query database data

Why do we say reduce data duplication rather than eliminate data duplication?

The answer is that we cannot eliminate all duplicated data because we must duplicate data in foreign keys

Rules for referencing foreign keys

The foreign key does not need to have the same name as the primary key it references—it only has to contain the same type of data!

data sublanguage

The language that is used to describe a database to a DBMS. A data sublanguage consists of at least two parts: Data Definition Language (DDL) and a Data Manipulation Language (DML) it has only those statements needed for creating and processing database data and metadata

INTERSECT

The result is all the row values common to both tables

UNION

The result is all the row values in one or both tables

EXCEPT

The result is all the row values in the first table but not the second

domain

The set of possible data values for a column

Explain digital dashboards and data mining applications

These applications use the data generated by order processing and other operational systems to produce information to help manage the enterprise. Such applications do not generate new data, but instead summarize existing data to provide insights to management.

composite key

Two or more fields that collectively define the primary key by unique combinations of their values. When more than one column in a table must be combined to form the primary key

always place the _____ clause before the _____ clause WHERE, GROUP BY; or GROUP BY, WHERE?

WHERE, GROUP BY;

equijoin

When the tables are joined using an inner join with an is equal to condition (like the one on OrderNumber)

A database schema

a "map" of data tables and their relationships to one another A complete logical view of the database, containing all the tables, all the columns in each table, the primary key of each table (indicated by underlining the column names of the primary key columns), and the foreign keys that link the tables together (indicated by italicizing the column names of the foreign key columns)

a key

a combination of one or more columns that is used to identify particular rows in a relation.

database management system (DBMS)

a computer program used to create, process, and administer the database. The DBMS receives requests encoded in SQL and translates those requests into actions on the database. The DBMS is a large, complicated program that is licensed from a software vendor; companies almost never write their own DBMS programs.

BI systems typically store their associated data in _______

a data warehouse

When Are Determinant Values Unique?

a determinant is unique in a relation only if it determines every other column in the relation

A candidate key

a determinant that determines all of the other columns in a relation

a set

a group of distinct items

In database structures, a recursive relationship is ...

a relationship between two columns in the same table

Integrated table

a table that stores both data and the relationships among the data

Unlike functional dependencies, the anomalies from multivalued dependencies are so serious that multivalued dependencies should....

always be eliminated.

A surrogate key

an artificial column that is added to a table to serve as the primary key.

A non-key attribute

an attribute that is neither (1) a candidate key itself nor (2) part of a candidate key

Looking at the statement syntax in SQLQuery-CH02-60, note that the SQL JOIN keyword is not used anywhere in the SQL statement—therefore, this is _________

an implicit inner join

customer relationship management (CRM) system

an information system that manages customer contacts from initial solicitation through acceptance, purchase, continuing purchase, support, and so forth.

An enterprise resource planning (ERP) system

an information system that touches every department in a manufacturing company. It includes sales, inventory, production planning, purchasing, and other business functions

indexes

are structures that speed the sorting and searching of database data

Venn diagrams

are the standard method of visualizing sets and their relationships.

SQL wildcard character

asterisk (*) - to indicate that we want all the columns to be displayed

Column equivalent names

attribute, field

Read-only databases are used in

business intelligence (BI) systems data warehouse

SQL SELECT clause specifies which _____ are to be listed in the query results

columns

SQL DML CRUD

create, read, update, and delete

SQL is not a complete programming language, like Java or C#. Instead, it is called a ______

data sublanguage

data integrity problems

deletion anomaly, insertion anomaly, update anomaly produce incorrect and inconsistent information users lose confidence in information the system gets a poor reputation can only occur if data are duplicated.

SQL/Persistent Stored Modules (SQL/PSM) statements

extend SQL by adding procedural programming capabilities, such as variables and flow-of-control statements, that provide some programmability within the SQL framework.

ETL system

extract, transform and load

decomposition rule

if A --> (B, C), then it is true that A --> B and A -->C

the union rule

if A --> B and A --> C, then it is true that A --> (B, C)

2NF

if and only if it is in 1NF and all non-key attributes are determined by the entire primary key the only way a non-key attribute can be dependent on part of the primary key is if there is a composite primary key. This means that relations with single-attribute primary keys are automatically in 2NF

business intelligence (BI) systems

information systems used to support management decisions by producing information for assessment, analysis, planning, and control

<>

is NOT equal to

IS NOT NULL

is NOT equal to NULL

NOT IN

is NOT equal to one of a set of values

NOT BETWEEN

is NOT within a set of range of numbers (includes the end points)

A foreign key

is a column or composite of columns that is the primary key of a table other than the one in which it appears.

database application

is a set of one or more computer programs that serves as an intermediary between the user and the DBMS. Application programs read or modify database data by sending SQL statements to the DBMS. Application programs also present data to users in the format of forms and reports. Application programs can be acquired from software vendors, and they are also frequently written in-house. The knowledge you gain from this text will help you write database applications.

=

is equal to

IS NULL

is equal to NULL

IN

is equal to one of a set of values

Database design (as a process)

is the creation of the proper structure of database tables, the proper relationships between tables, appropriate data constraints, and other structural components of the database.

database system

is typically defined to consist of four components: - users - the database application - the database management system (DBMS) - the database + SQL (in the textbook)

BETWEEN

is within a range of numbers (includes the end points)

LIKE

matches a sequence of characters

If you are constructing an updatable database, then you need to be concerned about __________ and _______

modification anomalies and inconsistent data

Relations are categorized into ________ based on the kinds of problems that they have

normal forms

Database professionals use a set of principles, collectively called _______, or ____________, to guide and assess database designs.

normalization; normal forms

Inconsistent values

occur when different users, or different data sources, use slightly different forms of the same data value Inconsistent data values are particularly problematic for primary and foreign key columns. Relationships will be missing or wrong when foreign key data are coded inconsistently or misspelled.

multivalued dependency

occurs when a determinant is matched with a particular set of values

We can divide database systems and DBMS products into two classes:

personal database systems and enterprise-class database systems.

Data mining applications (function)

predict future performance.

Each row in a table is uniquely identified by a ______

primary key

Access uses a GUI style called ________ to simplify ad-hoc queries.

query by example (QBE)

This SQL statement is a _____ statement, which asks _________ to obtain specific data from a database

query; the DBMS;

ad-hoc queries

questions that can be answered using database data

data

recorded facts and numbers.

Table equivalent names

relation, file

SQL underscore (_) wildcard character +ex.

represents a single, unspecified character in a specific position in the character string SELECT * FROM SKU_DATA WHERE Buyer LIKE 'Pete%'; or WHERE SKU_Description LIKE '%Tent%';

SQL percent sign (%) wildcard character +ex.

represents any sequence of contiguous, unspecified characters (including spaces) in a specific position in the character string. SELECT * FROM SKU_DATA WHERE Phone LIKE '330 388 999_';

domain integrity constraint

requirement that all of the values in a column are of the same kind Ex. all first columns have the ID, all second columns have FirstName and so on

What is considered when creating a database design for new system development

requirements for a new system, such as desired data entry forms and reports, user requirements statements, use cases (scenarios of users interacting with an information system to obtain a desired result), and other requirements, are analyzed to create the database design

COUNT(*)

returns the number of cells (rows) in a range that contains numbers

The SQL WHERE clause specifies which ______ are to be listed in the query result

rows

■ The SQL WHERE clause specifies which _____ will be used to determine the groups. ■ The SQL HAVING clause specifies which ____ will be used in the final result

rows; groups;

A primary key should be

short and (if possible) numeric

the only reason for having relations is to...

store instances of functional dependencies.

online transaction processing (OLTP)

system and is used to record all sales transactions of the company (whether in a store, on the Web, or from mail order or phone order sales).

The SQL FROM clause specifies which _____ are to be used in the query

tables

At Microsoft, the current DBMS engine within Microsoft Access is called _________

the Access Database Engine (ADE)

SQL join operation (def + implicit/ explicit)

the SQL JOIN operator is used to combine two or more tables by concatenating (sticking together) the rows of one table with the rows of another table. If the JOIN operator is actually used as part of the SQL statement syntax, we refer to the join operation as an explicit join. If the JOIN operator itself does not appear in the SQL statement, we refer to the join operation as an implicit join.

database integrity

the data in the database is accurate and consistent at all times.

Users

the final component of a database system, employ a database application to keep track of things. They use forms to read, enter, and query data, and they produce reports to convey information.

The Internet (origin info)

the global computer network of networks—was created as the ARPANET in 1969 and then grew and was used to connect all the LANs (and other types of networks).

CROSS JOIN (def, alt name, result)

the result is what is mathematically known as the Cartesian product of the rows in the tables, which means that this statement will just stick every row of one table together with every row of the second table

Subqueries are very powerful, but as noted, they do have a serious limitation: ...

the selected data can come only from the top-level table. Therefore, we cannot use a subquery to display data obtained from more than one table. To do so, we must use an SQL join instead.

what is used to create the relationships between the tables in databases?

the value of primary keys

Row equivalent names

tuple, record

Data definition language (DDL) statements

used for creating tables, relationships, and other structures

Data manipulation language (DML) statements

used for querying, inserting, modifying, and deleting data.

SQL INSERT statement + ex

used to add rows of data to a table INSERT INTO EQUIPMENT_ITEM SELECT DISTINCT ItemNumber, EquipmentType, AcquisitionCost FROM EQUIPMENT_REPAIR;

Data control language (DCL) statements

used to grant database permissions (or to revoke those permissions) to users and groups so that the users or groups can perform various operations on the data in the database.

Transaction control language (TCL) statements

used to mark transaction boundaries and control transaction behavior.

functional dependency

when the value of one or more attributes determines the value of another attribute

data warehouse

which is a database system that has data, programs, and personnel that specialize in the preparation of data for BI processing

Guidelines for Assessing Table Structure

• Count rows and examine columns • Examine data values and interview users to determine: - Multivalued dependencies - Functional dependencies - Candidate keys - Primary keys - Foreign keys • Assess validity of assumed referential integrity constraints

SQL statement categories

■ Data definition language (DDL) statements ■ Data manipulation language (DML) statements ■ SQL/Persistent Stored Modules (SQL/PSM) statements ■ Transaction control language (TCL) statements ■ Data control language (DCL) statements

Three types of database design

■ Database design from existing data ■ Database design for new systems development ■ Database redesign of an existing database

information

■ Knowledge derived from data ■ Data presented in a meaningful context ■ Data processed by summing, ordering, averaging, grouping, comparing, or other similar operations

SQL provides two different techniques for querying data from multiple tables: (name them)

■ The SQL subquery ■ The SQL join

three constraints, taken as a whole, is to create database integrity:

■ The domain integrity constraint ■ The entity integrity constraint ■ The referential integrity constraint