MIS 4113 Midterm

What is 1NF?

1NF sets the very basic rules for an organized database by meeting the set of conditions for a relation & containing a defined primary key

What are all of the comparison operators?

= <> (is NOT equal to) < & > <= & >= IN & NOT IN BETWEEN & NOT BETWEEN LIKE & NOT LIKE IS NULL & IS NOT NULL

What is a key?

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

What is a composite key?

A key that consists of two or more columns

What is a candidate key & its relationship to a primary key?

A key that determines all of the other columns in a relation & has the potential to become a primary key

What is an ad-hoc query?

A query created to obtain information as the need arises, as compared to a predefined and stored query

What is a subquery?

A query within a query, or a nested query

What is a database & what does it store?

A self-describing collection of integrated tables used to store data & relationships

What is a referential integrity constraint?

A statement that limits the values of the foreign key to those already existing as primary key values in the corresponding relation e.g. SKU in ORDER_ITEM must exist in SKU in SKU_DATA

What is the missing value problem?

A value that has never been provided, appears as NULL

What do the following commands do? AND, OR, NOT

AND — used when you have a compound WHERE statement OR — find either of two different entries (or both) in a field NOT — negates or reverses a condition

What is a surrogate key?

An artificial column added to a relation to serve as a primary key

What is an entity?

An identifiable aspect that users want to track e.g. customers, computers, sales, etc.

What was the first personal computer product to become widely available?

Apple II — 1977

What do the following commands do? COUNT, MIN, MAX, SUM, AVG

COUNT — counts the number of rows in a table MIN — calculate the minimum value of all values MAX — calculate the maximum value of all values SUM — calculate the sum of all numerical values AVG — calculate the average of all numerical values

What are the steps for assessing table structure?

Count rows & examine columns Examine data values & interview users to determine — multivalued dependencies functional dependencies candidate/primary/foreign keys Assess validity of assumed referential integrity constraints

CHAPTER FOUR

DATABASE DESIGN

What is the relationship between data, information, & database?

Databases record data — recorded facts & figures — & they do so in such a way that we can produce information — knowledge derived from the data e.g. the data on a STUDENTs, CLASSes, & GRADEs could product information about each student's GPA

What are the advantages of normalization?

Eliminate modification anomalies Reduce duplicated data Eliminate data integrity problems Save file space Single table queries will run faster

How do you join two tables with a subquery?

Embedded within the WHERE clause & must be enclosed within parentheses using DML statements, typically SELECT

What do the following commands do? GROUP BY, TOP, *

GROUP BY — group rows into groups according to common values TOP — restrict the number of rows returned in a query * — select all the fields from a table

CHAPTER TWO

INTRO TO SQL

CHAPTER ONE

INTRODUCTION

What is the distinction between implicit JOIN & explicit JOIN?

Implicit — the SQL JOIN operator is not used as part of the SQL statement e.g. FROM RETAIL_ORDER, ORDER_ITEM Explicit — the SQL JOIN operator is used as part of the SQL statement e.g. FROM RETAIL_ORDER JOIN ORDER_ITEM

Why are databases considered self-describing?

It stores a description of itself

What does the null value mean?

May indicate that a value is — Inappropriate Appropriate but unknown Appropriate & known, but has never been entered

What are the major DBMS products?

Microsoft Access (ADE) Oracle Corp. MySQL Microsoft SQL Server IBM DB2 Oracle Corp. Oracle Database

CHAPTER THREE

NORMALIZATION

What are null values?

NULL — values that are missing or does not contain any values NOT NULL — enforces a column to not accept NULL values

What is the inconsistent values problem?

Occur when different users or different data sources use slightly different forms of the same data value through different coding or different spellings

What is an insertion anomaly?

Occurs when inserting vital data into the database is not possible because of the absence of other data

What is the multivalued, multicolumn problem?

Occurs when multiple values of an attribute are stored in more than one column & the solution is to use a separate table to store the multiple values

What is a functional dependency? What is a determinant?

Occurs when the value of one (set of) attribute(s) determines the value of a second (set of) attribute(s) The determinant is the attribute on the left side of the functional decency

What is a primary key & foreign key?

Primary key — a candidate key selected as the primary means of identifying rows in a relation, may be single or composite Foreign key — a column or composite of columns that is the primary key of a table other than the one in which it appears

What are data manipulation language (DML) statements used for?

Queries — SQL SELECT statement Inserting data — SQL INSERT statement Modifying data — SQL UPDATE statement Deleting data — SQL DELETE statement

What are the characteristics of a relation?

Row contain data about an entity Columns contain data about attributes of the entities All entries in a column are of the same kind Each column has a unique name Cells of the table hold a single value The order of the rows & columns are unimportant No two rows may be identical/duplicate

What do the following commands do? SELECT, FROM, WHERE, JOINS, ORDER BY

SELECT — selects fields FROM tables FROM — select the table WHERE — extract filtered data under specified conditions JOINS — combines two or more fields from multiple tables ORDER BY — sort the results of a statement

What are wildcard characters?

SQL asterisk (*) — select all fields from a table SQL underscore (_) — represents a single, unspecified character in a specific position in a character string SQL percent sign (%) — represents any sequence of contagious, unspecific characters in a specific position in a character string

What do the following commands do? SUBQUERIES, INNER JOIN, UNION, INTERSECT, EXCEPT

SUBQUERIES — when the fields you need come from the top-level table only & not from tables contained in the actual subquery INNER JOIN — a table formed by concatenating two tables using an equal condition UNION — the result is all the combined row values in one or both tables INTERSECT — the result is all the row values common to both tables EXCEPT — the result is all the row values in the first but not the second table

What is metadata?

Self-describing data — data about data

What is SQL?

Structured Query Language is an international standard language for creating databases & database structure, & processing databases SQL was developed by the IBM Corp. in the late 1970's

What are the alternative terms for tables, columns, & rows?

Table - Relation - File Column - Attribute - Field Row - Tuple - Record

What is denormalization?

The joining of the data in normalized tables prior to storing the data

What are all the practical problems in designing databases?

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

What is 2NF?

The relation must be in 1NF & not contain any non-prime attribute that is functionally dependent on any proper subset of any candidate key Steps to determine if a table is in 2NF & how to put it into 2NF — 1. Identify candidate keys 2. If there are no composite keys, then this table is in 2NF 3. If there are composite keys, identify non-prime attributes (columns other than the ones in the candidate keys) 4. Determine the functional dependencies & if there is a non-prime attribute that is determined by a subset of a composite candidate key, then it's not in 2NF 5. Move the columns of the functional dependency into a separate relation while leaving the determinant in the original as the foreign key

What is 3NF?

The relation must be in 2NF & have no non-key attributes be determined by another non-key attribute How to put it into 3NF — Move the columns of the functional dependency into a separate relation while leaving the determinant in the original relation as a foreign key

What is BCNF?

The relation must be in 3NF & every determinant (of any functional dependency) is a candidate key Steps to determine if a table is in BCNF & how to put it in BCNF — 1. Write down all of the functional dependencies 2. Determine if all the determinants of the functional dependencies are candidate keys & if not, then it's not in BCNF 3. Move the columns of the functional dependency into a separate relation while leaving the determinant in the original relation as a foreign key

What is a domain integrity constraint?

The requirement that all of the values in a column are of the same kind

When do we use the keyword IS NULL?

To check for null values

What is the purpose of a database?

To help people track things of interest to them

What is the example of unneeded normalization used in this chapter?

ZIP codes — they never change & any anomalies are likely to be caught by normal business practices