exam question 2 show Normalization in this database

Lakukan tugas rumah & ujian kamu dengan baik sekarang menggunakan Quizwiz!

Entity (table)

- Can be thought of as a simple 2D grid of data consisting of: --Attributes/fields/columns --Instances/records/tuples/rows - Entities can handle multi-dimensional data --It is easiest to think of them as flat 2D objects consisting of columns and rows

Functional dependence Keys

2 Crucial Concept

Fourth Normal Form (4NF)

A table is in fourth normal form if it is in third normal form and there are no multivalued dependencies

Third Normal Form (3NF)

A table is in third normal form if it is in second normal form and the only determinants it contains are candidate keys

Nonkey Column

An attribute (column) that is not part of the primary key

Transitive Dependency

An attribute functionally depends on another nonkey attribute

Transitive dependency

An attribute functionally depends on another nonkey attribute.

Atomic Attribute

An attribute that cannot be further subdivided to produce meaningful components. EX: A person's last name attribute cannot be meaningfully subdivided.

atomic attribute

An attribute that cannot be further subdivided to produce meaningful components. For example, a person's last name attribute cannot be meaningfully subdivided

nonprime attribute

An attribute that is not part of a key

Non-key Attribute (SEE: NON-PRIME ATTRIBUTE)

An attribute that is not part of a key.

34) Denormalization is the process of joining previously normalized tables back together.

Answer: TRUE Diff: 1 Page Ref: 166-167

This process progressively breaks table into new set of relations based on identified dependencies

Normalization

Atomicity

Not being able to be divided into smaller units

Requirements for good normalized set of tables

Evaluate PK assignments and naming conversations. Identify new attributes and relationships, refine primary keys as required for data granularity, maintain historical accuracy and evaluate using derived attributes.

Boyce Codd Normal Form

Every determinant in table is a candidate key. - Considered special case of 3NF

Boyce-Codd Normal Form (BCNF)

Every determinant in the table is a candidate key

Boyce-Codd normal form

Every determinant in the table should be a candidate key. Equivalent to 3NF for one candidate key. Violated when table contains more than one candidate key.

Boyce-Codd normal form (BCNF)

Every determinant is a candidate key

Boyce-cod normal form

Every determinant is a candidate key

Normalization can rely on itself to make good designs

False

Not all relational tables satisfy 1NF requirements

False

Repeating Group

Group of multiple entries can exist for any single key.

Repeating Group

Group of multiple entries of same type can exist for any single key attribute occurence

Repeating group

Group of multiple entries of same type can exist for any single key attribute occurrence. Proves presence of data redundancies. Enable reducing data redundancies.

Partial Dependency

Functional dependence in which the determinant is only part of the primary key

Partial dependency

Functional dependence in which the determinant is only part of the primary key Assumption: one candidate key.

Levels of normalization

If a data model has passed the first level, we say that it is in first normal form If it has passed the second level, second normal form Etc.

yes, instructor-teacher

Is an associative entity needed, if so between what entities must it be introduced?

no, salary

Is the ERD in 3NF if not, What attribute is the problem?

Which of the following would be the best name for a column representing the charges per hour in a table named JOB?

JOB_CHG_HOUR

Assume you have the following table definition: 1NF (PROJ_NUM, EMP_NUM, PROJ_NAME, EMP_NAME, JOB_CLASS, CHG_HOURS, HOURS). Identify a transitive dependency.

JOB_CLASS --> CHG_HOURS

⋈

Join (bowtie) ex. Characters ⋈ (name) Appearances At most |R| * |S| tuples in result

Step 1 of conversion to 2NF:

Make new tables to eliminate partial dependencies

Conversion to second normal form

Make new tables to eliminate partial dependencies. Reassign corresponding dependent attributes. The table is in 1NF, includes no partial dependencies.

Step 1 of conversion to 3NF:

Make new tables to eliminate transitive dependencies (for every transitive dependency, write its determinant as PK for new table)

Conversion to third normal form

Makes new tables to eliminate transitive dependencies. Reassign corresponding dependent attributes. Table is in 2NF, contains no transitive dependencies.

Context ERD

Meaning that the entities (rectangles) and relationships are showing

Repeating Group

More than one entry at a single location in a table

Normalization

a process that enables a database designer to eliminate design problems by decomposing (or reorganizing) the existing table structures.

DBMS (Database Management System)

a program or collection of programs whose function is to manage a database on behalf of the people who use it.

Attribute

a property of an entity (For the entity "Person," attributes could include eye color and height).

In a real-world environment, we must strike a balance between design integrity and ________.

flexibility

Note

The primary key is often called the key in other studies on database management and the relational model. This text will continue to use the term primary key to distinguish between the different definitions of a key that you will encounter throughout this text.

database

a set of interrelated centrally coordinated files

In a normalized database, what is represented by each table?

a single subject

Database

a structure that can store information about multiple types of entities, the attributes of these entities, and the relationships among the entities.

____ keys are usually numeric, they are often automatically generated by the DBMS, they are free of semantic content (they have no special meaning), and they are usually hidden from the end users.

surrogate

Potential problems in the design of a relational database are known as ____. a. update anomalies b. select anomalies c. modification anomalies d. relational anomalies

a. update anomalies

1NF

table contains no repeating groups.

Modality

the Minimum number of times than an instance of an entity can be associated with instances of another entity

primary key

the database attribute or combination of attributes that uniquely identifies a specific row in each table

database system

the database, the dbms and the application programs that access the database through the dbms

All the attributes in an entity should be completely determined by...

the entity instance's primary key

data model

abstract representation of database contents

To put an ERD into 3NF ... (2)

add another entity and a 1:M relationship, if dependent on another secondary attribute (which is not a FK) Or move attribute to other entity, if dependent on a FK

2nd Normal Form (2NF)

for every instance of an entity have a PK with 2 or more components, each attribute of the instance is completely determined by the totality of the instance's PK, not just by some of its components. - When Concatenated PK, each instance attribute is completely dependent on all PK's

second normal form

for every instance of an entity having a pk with 2 or more components, each attribute of the instance is completely determined by the totality of the instance's pk, not just some of its component

If all attributes of a relation are prime....

in 2NF and 3NF

Denormalization

increasing data redundancies to yield less I/O in order to increase processing speed.

external level schema

individual user views of portions of the database

Note, a secondary attribute is an attribute that ...

is not in the PK

Cardinality

is the maximum number of times that an instance of an entity (table) can be associated with instances of another entity (table)

Modality

is the minimum number of times that an instance of an entity can be associated with instances of another entity

Normalization Second Normal Form (2NF) A model is in 2NF if

it is in 1NF, and for every instance of an entity having a PK with 2 or more components, each attribute of the instance is completely determined by the totality of the instance's PK, not just by some of its components

Normalization Third Normal Form (3NF) A model is in 3NF if

it is in 2NF, and for every instance of an entity, each secondary attribute is dependent only on the PK, not on any other secondary attribute

A table is in fourth normal form (4NF) if ______.

it is in 3NF and has no independent multivalued dependencies

Another name for a prime attribute is a(n) ________ attribute.

key

Unnormalized relation or table

one that is not in 1NF

Dependencies based on only a part of a composite primary key are called ________ dependencies.

partial

A column is a nonkey column if it is not a part of the ____________________.

primary key

Database Modeling

The process of creating an ERD is called Database Modeling

Normalization Process

The process of removing repeating groups to produce a first normal form table. Sometimes refers to the process of creating a third normal form table

In an ideal (database design) world, the level of desired granularity is determined at the ________ design or at the requirements gathering phase.

conceptual

data warehouse

contains both detailed and summarized data for a number of years and is used for analysis rather than in real time

data dictionary

contains information about the structure of a database

data query language (DQL)

contains powerful commands that enable users to retrieve, sort, order, and display data

record layout

example customer number, customer name, address, credit limit, balance (alphanumeric or numeric)

Normalization is a ... step procedure

Three

The database relational model does not offer direct support ...

to many-to-many relationships, even though such relationships happen frequently in the realm that is being modeled

A 2NF table can exhibit ________ dependency.

transitive

use a unique index for JOB_DESCRIPTION

business intelligence

using a data warehouse for strategic decision making

online analytical processing (OLAP)

using queries to guide investigation of hypothesized relationships in data

data mining

using sophisticated statistical analysis, including artificial intelligence techniques

Improving ________ leads to more flexible queries.

atomicity

Candidate key

attribute or composite attribute in a table that can uniquely identify a table.

The normalization process involves assigning ________ to tables based on the concept of determination.

attributes

A(n) ____ is a column or collection of columns on which all columns in the table are functionally dependent. a. index key b. candidate key c. major key d. special key

b. candidate key

Data redundancy produces ________.

data integrity problems

update anomaly

data value updates are not correctly recorded

Lower normalization forms occur (and are even required) in specialized databases known as _______.

data warehouses

data heirarchy

database files record fields

delete anomaly

deleting a row has unintended consequences

________ yields increase performance in a database system.

denormalization

A ________ is any attribute whose value determines other values within a row.

determinant

In BCNF, every ________ in a table is a candidate key.

determinant

Dependencies can be depicted with the help of a dependency ________.

diagram

tuple

each row in a table & contains data about a specific occurence of the type of entity represented by that table

Repeating group steps

Eliminate repeating groups, identify primary key, identify all dependencies.

Step 1 of conversion to 1NF:

Eliminate the repeating groups (nulls)

Types of Relationships

One-to-one, One-to-many, Many-to-many

Unnormalized relation

- a relation (table) that contains a repeating group (or multiple entries for a single record)

A table that has all key attributes defined, no repeating groups, and an identified primary key is said to be in ________.

1NF

All relational tables satisfy the ______ requirements.

1NF

From a structural point of view, 2NF is better than ________.

1NF

Second Normal Form

1NF + Has no partial dependencies

2NF

1NF and has no partial dependencies

Update Anomalies

- identify the existence of potential problems

Alternate keys

- the candidate keys that are not chosen as the primary key.

Goal of Normalization

- to take a table or collection of tables and produce a new collection of tables that represents the same information but that is free of update anomalies.

problems to storing all information in one table

-update anomally -insert anomaly -deletion anomaly

26) The standard sales order line item pattern is a classic example of unneeded normalization.

/Answer: FALSE Diff: 2 Page Ref: 164

Disadvantages of Normalization:

1. More tables (typically) 2. Non-intuitive table design (possibly) 3. Query complexity (multi-table joins) 4. Slower queries (occasionally)

A table that is in 1NF and includes no partial dependencies is said to be in ________.

2NF

A table whose primary key consists of only a single attribute is automatically in ________ when it is in 1NF.

2NF

transitive dependency

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

Transitive Dependency

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

Partial Dependency

A dependency of a column on only a portion of the primary key

Dependency Diagram

A diagram that indicates the dependencies among the columns in a table

prime attribute

A key attribute, that is, an attribute that is part of a key or is the whole key. See also key attribute.

Primary key

A non-null (non-blank) attribute or set of non-null attributes that uniquely identifies each instance/row of an entity - Can be simple or composite 1. Best practices: every entity should have a primary key --Can never be null 2. Common practice: go simple when possible --Use a simple PK unless a composite PK offers an additional modeling advantage that can't easily be implemented in another way

Boyce-Codd Normal Form (BCNF)

A relation is in Boyce-Codd normal form if it is is second normal form and the only determinants it contains are canidate keys; also called third normal form in the text

Second Normal Form (2NF)

A relation is in second normal form if it is in first normal form and no nonkey attribute is dependent on only a portion of the primary key

dependency diagram

A representation of all data dependencies (primary key, partial, or transitive) within a table

Dependency Diagram

A representation of all data dependencies (primary key, partial, or transitive) within a table.

dependency diagram

A representation of all data dependencies (primary key, partial, or transitive) within a table.

Fourth normal form (4NF)

A table is in 4NF if it is in 3NF and contains no multiple independent sets of multivalued dependencies.

Boyce-Codd normal form (BCNF)

A special form of third normal form (3NF) in which every determinant is a candidate key. A table that is in BCNF must be in 3NF. See also determinant.

Boyce-Codd normal form (BCNF)

A special type of third normal form (3NF) in which every determinant is a candidate key. A table in BCNF must be in 3NF. See also determinant.

BCNF

A special type of third normal form (3NF) in which every determinant is a candidate key. A table in _____ must be in 3NF

Boyce-Codd Normal Form (BCNF) (SEE: DETERMINANT)

A special type of third normal form (3NF) in which every determinant is candidate key. A table in BCNF must be 3NF.

Database

A usually large collection of data organized especially for rapid search and retrieval

Density of M:N relationship

Actual/possible ratio - This traditional associative entity technique for representing M:N relationships works well for low ratios --Alternative techniques are available for high density scenarios

Associative entity

Added to the ERD to resolve a many to many relationship between two entities.

Conversion to first normal form 1NF

All key attributes are defined, there are no repeating groups in the table, all attributes are dependent on the primary key. Some tables contain partial dependencies.

Data modeling

All names should be limited in length. Should be short and meaningful nouns. Abbreviate names of entities linked through the composite key. Attribute names can be prefixes or suffixes.

Second normal form

All non-key attributes are dependent on the entire key

Normalization process ensures:

All tables are 3NF, higher forms are not likely to be encountered in business environment, works one relation at a time.

ERD - Entity Relationship Diagram

An ERD shows the relationships between the data

Data model

An abstract representation (often graphical) of the data (entities and attributes) and the associations (relationships) between them - Many styles/formats of graphical data models have been proposed over the years --The entity-relationship (ER) model is among the most popular

9) When examining data values as a part of assessing table structure, you should try to determine functional dependencies.

Answer: TRUE Diff: 1 Page Ref: 161-162 Fig 4-1

57) When you are given a set of tables and asked to create a database to store their data, the first step is to ________. A) assess the existing tables' structure and content B) design the database structure C) create one or more new tables D) move the data into the new database E) design the applications that will use the database

Answer: A Diff: 1 Page Ref: 161-162 Fig 4-1

49) A null value in a column may indicate that there is no appropriate value for that attribute.

Answer: TRUE Diff: 1 Page Ref: 171

92) When accessing table structure, how do you determine how many rows are in a table?

Answer: To determine how many rows are in a table, use a SELECT query that contains the SQL COUNT(*) function. This function will return the number of rows in the table. Diff: 1 Page Ref: 161

93) When accessing table structure, how do you determine the number and types of columns in a table?

Answer: To determine the number and types of how many rows are in a table, use a SELECT query that contains the SQL * expression. This expression will return all the columns in the table, and the data types can be determined by examining the values in each column. Diff: 1 Page Ref: 161

determinant

Any attribute in a specific row whose value directly determines other values in that row

Determinant

Any attribute in a specific row whose value directly determines other values in that row.

Clicker Questions (SEE SLIDE 32) Is the ERD in 3NF if not what attribute is the problem? A. Yes B. No Salary C. No, Completiondate D. No, rank

C. No, Completiondate

Clicker Question (SEE SLIDE 13) Is an associative entity needed, if so between what entities must it be introduced A.Yes, course-section B. Yes, section-instructor C. Yes, Instructor-teaching assistant D. No, section-student

C. Yes, Instructor-teaching assistant

What is the trade-off of denormalization?

Can increase performance but results in greater data redundancy

Atomic Attribute

Cannot be further subdivided

Atomic attribute

Cannot be further subdivided

What does cardinality quantify?

Cardinality quantifies an Entity Relationship

Atomicity

Characteristic of an atomic attribute

Key

Column A (or a collection of columns) is the primary key for a relation (table) R, if: Property 1. All columns in R are functionally dependent on A. Property 2. No subcollection of the columns in A (assuming A is a collection of columns and not just a single column) also has Property 1.

Functionally Determines

Column A functionally determines column B if B is functionally dependent on A

Functional Dependence

Column B is functionally dependent on column A (or on a collection of columns) if a value for A determines a single value for B at any one time.

Functionally Dependent

Column B is functionally dependent on column A (or on a collection of columns) if a value for A determines a single value for B at any one time.

|X|

Count of tuples in relation X

N-ary relationships

Create a new associate entity (super bridge) with N FKs to point to the N "sides" of the relationship

Join two tables without using the join operator

Cross Product + Selection

Cross product (Cartesian Product) |R| * |S| tuples in R x S

Clicker Question (SEE SLIDE 15) Which of these would be the primary key for the registration table? A. SectionID B. StudentID C. CouseID D. SectionID and StudentID

D. SectionID and StudentID

Clicker Question (SEE SLIDE 24) Which attribute can have multiple values? A. fname B. Iname C. dept D. course ID

D. course ID

3. Many-to-many (M:N)

Each record in X can be associated with many records in Y, and vice versa - Typical implementation: --Create a new associative entity (aka, composite entity, bridge table) consisting of two FKs to the associated record in X and Y

A table that is in first normal form is better than a table in second normal form.

False

Converting to third normal form always avoids the problems related to dependencies.

False

Functional dependencies can be determined by looking at sample data

False

If B is functionally dependent on A, you can also say that B functionally determines A.

False

If the primary key of a table contains only a single column, the table is automatically in first normal form

False

In a dependency diagram, the arrows below the boxes indicate the normal dependencies.

False

Second normal form represents an improvement over ____________________ normal form

First

3 Stages of Normalization

First normal form (1NF) Second normal form (2NF) Third normal form (3NF)

Question: On which columns is QuotedPrice functionally dependent?

For any combination of an order number and a part number, there can be only one row in the OrderLine table. Thus, any combination of an order number and part number in the OrderLine table is associated with exactly one quoted price. Consequently, QuotedPrice is functionally dependent on the combination (formally called the concatenation) of OrderNum and PartNum.

Normalization

Helps eliminate redundancy by optimizing database design. Breaks things down in a systematic way to eliminate data anomalies. One table at a time. 1:1 relationship

3NF

If a table is already in 2NF, it is in 3NF if there are no determinant relationships between non-key attributes except for candidate keys.

4NF

If a table is already in 3NF, it is in 4NF if there are no potential multi-valued attributes.

partial dependency

In normalization, a condition in which an attribute is dependent on only a portion (subset) of the primary key.

Third normal form

In second normal form and has no transitional dependencies

Granularity

Level of detail represented by the values stored in a table's row

Granularity

Level of detail represented by the values stored in a table's row.

Normalization process

Objective is to ensure table conforms to the concept of well-formed relations. Necessary data is stored in one table. All nonprime attributes in a table are dependent on the primary key. Table void of insertion, update, and deletion anomalies.

Multivalued dependencies

Occurs when one key determines multiple values of two other attributes and those attributes are independent of each other.

In 1NF some tables contain:

Partial dependencies which should be used with caution

Normalization

Process for evaluating and correcting table structures to minimize data redundancies

Normalization

Process for evaluating and correcting table structures to minimize data redundancies, reducing data anomalies

Denormalization

Produces a lower normal form

Denormalization

Produces a lower normal form. Results in increased performance and greater data redundancy.

Data-modeling checklist: Business rules

Properly document and verify business rules with end users. Ensure rules are written precisely; identify attributes, relationships, and constraints. Identify source of business rules; justified, dated, and signed off by an authority.

record

Row of an entity- also called an instance of the entity

nonkey attribute

See nonprime attribute.

Normalization and database design

Should be part of the design process. Entities must meet required the normal form before creating structures.

ERD- Entity Relationship Diagram

Shows the relationships between the data

Fourth normal form

Table is in 3NF, has no multivalued dependencies. All attributes must be dependent on primary key, but independent of each other. No row may contain two or more multivalued facts about an entity.

key attribute

The attribute(s) that form(s) a primary key. See also prime attribute.

Degree of a relationship

The number of entities involved in the relationship 1. Unary - one entity 2. Binary - two entities 3. N-ary - N entities

Cardinality of a relationship

The number of records (min and max) in entity Y that can be associated with each record in entity X, and vice versa

What is the objective of normalization?

To ensure that all tables are in at least 3NF

A table that is in first normal form may contain problems that will require you to restructure it.

True

Data-modeling: ER model

Validated against expected processes: inserts, updates, and deletions. Evaluate where, when, and how to maintain a history. No redundant relationships. All that is needed is here, and all that is there is needed.

white hat hacker

Who is a hacker that works to help society?

Foreign Key

a field in a table that links to a primary key field in another table to form a relationship

An Associative entity is also called ...

an Intersection Entity or a Linking Table

Relationship

an association between entities

foreign key

an attribute that is a primary key in another table and is used to LINK tables

An alternate key is a ____. a. foreign key b. primary key c. column that could be a primary key but was not chosen d. row that could be a primary key but was not chosen

c. column that could be a primary key but was not chosen

1st Normal Form (1NF)

look at instance value for the Primary Key and it should drive every instance for the secondary instances. - for every instance of an entity, each attribute of the instance is completely determined by the instances PK - each instance attribute is completely dependent upon the PK

An attribute that is part of a key is known as a(n) ________ attribute.

prime

normalization

process that enables a database design to eliminate design problems by decomposing (or reorganizing) the existing table structure

A selection removes...

rows

minimal data redundancy and data inconsistencies

usually only stored once, so this is minimized

Unnormalized tables yield no simple strategies for creating virtual tables, which are also known as ________.

views

what you know

what authentication method does tippie college of business use for students

2nd Normal Form

•All attributes (non-key columns) are dependent on the key •Make junction tables (with primary keys from different tables) •No partial key dependencies * No non-key column is dependent on only a portion of the primary key

3rd Normal Form

•All fields (columns) can be determined only by the key in the table and no other column (attribute)

Normalization process

- Sequential application of increasingly higher standards / conditions / rules - A database is said to be in Kth normal form if all tables satisfy the first K rules: 1. *1NF: first normal form* - PK for every table - One value per field (no compound fields or lists) - One field per value (no repeating values) 2. *2NF: second normal form* - One subject per table 3. *3NF: third normal form* - No "computed" values (math, logical, or lookup) 4. *Higher normal forms* - Database theoreticians have proposed additional rules of normalization beyond 3NF to deal with specialized circumstances - In practice, few database practitioners normalize past 3NF

De-normalization

- Sometimes, a database practitioner intentionally violates a rule in an attempt to improve performance (query speed) --Pre-computed values --Intentional redundancy - This should only be done if the improved performance is critical

First normal form

- Tables without repeating groups.

Functional Dependence

- a column (attribute) B is functionally dependent on another column A (or possibly a collection of columns) when each value for A in the database is associated with exactly one value of B.

Candidate key

- a column or a collection of columns on which all columns in the table are functionally dependent. - the definition for primary key also defines a candidate key.

Functional Dependence

- a formal name for what is basically a simple idea.

Entity

- a person, place, thing, or event

Normalization

- a progression in which a table that is in first normal form is better (freer from problems) than a table that is not in first normal form, a table that is in second normal form is better than one that is in first normal form, and so on.

To correct update anomalies

- convert tables to various types of normal forms.

Normalization Process

- enables you to identify the existence of potential problems, called update anomalies, in the design of a relational database.

Relational Integrity

- if a foreign key in one table matches the primary key of another table, then values entered in the foreign key must match the value of the primary key or be null.

A table is in 1NF when (3 things):

-All key attributes are defined -No repeating groups in table -All attributes are dependent on PK

A table is in 2NF when (2 things):

-It is in 1NF and -It includes no partial dependencies

A table is in 3NF when (two things):

-It is in 2NF and -It contains no transitive dependencies

A table is in the Fourth Normal Form (4NF) when (2 things):

-It is in 3NF and -No multiple sets of multivalued dependencies 37

When can a table be in 3NF and fail to meet BCNF (2 reasons):

-No partial dependencies, nor does it contain transitive dependencies -A nonkey attribute is the determinant of a key attribute

Entity

-Something about which data needs to be kept -Can be a thing or event -Entities end up as a table in a database

Advantages of a database system

-data integration -data sharing -minimal data redundancy and data inconsistencies -data independence -cross functional analysis

66) You have been given two tables, CUSTOMER and SALE. You want to check the referential integrity constraint: SALE.CustomerNumber must exist in CUSTOMER.CustomerNumber You run the following SQL query: SELECT CustomerNumber FROM SALE WHERE CustomerNumber NOT IN (SELECT CustomerNumber FROM SALE, CUSTOMER WHERE SALE.CustomerNumber = CUSTOMER.CustomerNumber) What is shown in the results of this query? A) All values of CustomerNumber that match the constraint. B) All values of CustomerNumber that violate the constraint. C) All values of CustomerNumber where SALE.CustomerNumber = CUSTOMER.CustomerNumber. D) A and C E) B and C

/Answer: B

Things to avoid:

1. *Multi-value fields* - Divide multi-item fields/attributes into separate fields --EX. Name becomes FirstName, LastName - Simplifies user queries --EX. Find customers who live in Austin, Find customers who live in apt. - Simplifies some data validation rules --EX. Street is required but Apt. is optional 2. *Repeating fields* - Pull fields/attributes out of tables that are shared by multiple records --Reduces physical size of database and helps control data inconsistencies 3. Computed fields 4. Multi-level dependencies

First Normal Form

1. All key attributes are defined 2. There are no repeating groups in the table 3. All attributes are dependent on primary key

Dependency Diagram

1. Depicts all dependencies found within a given table structure

Advantages of Normalization:

1. Duplicate data is minimized - Reducing storage requirements - Simplifying maintenance 2. Pre-calculated values are eliminated - Reducing data requirements - Improving quality/accuracy 3. Fewer secondary indices per table (since each table is for a single "subject" - Making insert, update, and delete operations faster

What is the normalization process (4 things)?

1. Each table represents a single subject 2. No data item will be unnecessarily store d in more than one table 3. All nonprime attributes in a table are dependent on the PK 4. Each table is void of insertion, update, deletion anomalies

Normalization process

1. Each table represents a single subject 2. No data item will be unnecessarily stored in more than one table 3. All attributes in a table are dependent on the primary key 4. Each table void of insertion, update, deletion anomalies

8 issues to address, in order, to produce a good normalized set of tables:

1. Evaluate PK assignments 2. Evaluate naming conventions 3. Refine attribute atomicity 4. Identify new attributes 5. Identify new relationships 6. Refine PKs as required for data granularity 7. Maintain historical accuracy 8. Evaluate using derived attributes

4 characteristics of the Boyce-Codd Normal Form (BCNF)

1. Every determinant in table is a candidate key (which has the same characteristics of a PK but for some reason was not chosen as such) 2. When table contains only one candidate key, the 3NF and the BCNF are equivalent 3. BCNF can be violated only when table contains more than one candidate key 4. Most designers consider BCNF as a special case of 3NF

Conversion to 2NF (2 Steps)

1. Identify all key components 2. Identify the dependent attributes

Conversion to 3NF (3 Steps)

1. Identify each new determinant 2. Identify the dependent attributes 3. Remove the dependent attributes from transitive dependencies

Techniques in business intelligence

1. OLAP- online analytical processing 2. data mining

Three basic types of relationships

1. One-to-many (1:N) 2. One-to-one (1:1) 3. Many-to-many (M:N)

Advantages of databases:

1. Organized (structured) collection of data 2. Enables more effective decision making 3. Supports multiple users 4. Improved data quality 5. Well maintained: full backups and security 6. Has GUI and/or programming language for ease of use

Steps to Design a Relational Database

1. Store all data in one uniform table 2. Vary the number of columns 3. SOLUTION- a set of tables aka: relational database

3 things about improving table design:

1. Table structures should be cleaned up to eliminate initial partial and transitive dependencies 2. Normalization cannot, by itself, be relied on to make good designs 3. Valuable because it helps eliminate data redundancies

three levels of schemas

1. conceptual schema 2. external level schema 3. internal level

Basic Requirements of a Relational Database

1. every column in a row must be single valued: one value per cell 2. Primary keys cannot be null - ensures that every row represents something and can be identified aka: entity integrity rule 3. Foreign keys if not null, must have values that correspond to the value of a primary key in another table- aka: referential integrity rule 4. all nonkey attributes must describe a characteristic of the object identified by the primary key-

two approaches to database design

1. normalization 2. semantic data modeling

Summary: 1NF

1NF - each instance attribute is completely dependent upon PK

Second normal form (2NF

1NF and No partial dependencies

Assume the following table definition and dependencies. How many tables would be produced by converting to BCNF? TABLE(STU_ID, STAFF_ID, CLASS_CODE, ENROLL_GRADE) STU_ID + STAFF_ID --> CLASS_CODE, ENROLL_GRADE CLASS_CODE --> STAFF_ID

Third Normal Form

2NF + Has no transitive dependencies

Summary: 2NF

2NF - when concatenated PK, each instance attribute is completely dependent upon all PKs

3NF

2NF and contains no transitive dependencies

Third Normal Form

2NF and no transitive dependencies

Given the following table definition, how many tables would be produced by the conversion to 2NF? 1NF (PROJ_NUM, EMP_NUM, PROJ_NAME, EMP_NAME, JOB_CLASS, CHG_HOURS, ASSIGN_HOURS) with PROJ_NUM & EMP_NUM AS PRIMARY KEY IDENTIFIERS

How many steps does the normalization procedure have?

A table in ________ contains no transitive dependencies.

3NF

A table that is in 2NF and contains no transitive dependencies is said to be in ________.

3NF

A table where all attributes are dependent on the primary key and are independent of each other, and no row contains two or more multivalued facts about an entity, is said to be in ________.

3NF

Assume the following table definition and dependencies. In which normal form is this table? TABLE(A, B, C, D) A + B --> C, D C --> B

3NF

Best form of normalization

3NF

For most business database design purposes, ________ is as high as you need to go in the normalization process.

3NF

Most designers consider Boyce-Codd normal form (BCNF) as a special case of ________.

3NF

Objective of normalization is to ensure all tables in at least...

3NF

Tables in ________ will perform suitably in business transactional databases.

3NF

Which level of normalization is needed for business database design purposes?

3NF

Which normalization form is best-suited for business transactional databases?

3NF

Fourth Normal Form

3NF + Has no multivalued dependencies

Summary: 3NF

3NF - each instance attribute is dependent only on PK and not a secondary attribute

4NF

3NF and contains no multivalued dependencies.

Candidate Key

A Candidate Key is a field (attribute) that could have been selected as the primary key but was not.

Primary Key

A Primary Key is the field (attribute) or combination of fields (attributes) that uniquely identifies a row in a relational table.

Alternate Key

A candidate key that was not chosen to be the primary key

Determinant

A column in a table that determines at least one other column

transitive dependency

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

partial dependency

A condition in which an attribute is dependent on only a portion (subset) of the primary key

Partial Dependency

A condition in which an attribute is dependent on only a portion (subset) of the primary key.

Interrelation Constraint

A constraint that involves more than one relation

Prime Attribute (SEE: KEY ATTRIBUTES)

A key attribute; that is, an attribute that is apart of a key or is the whole key.

prime attribute

A key attribute; that is, an attribute that is part of a key or is the whole key

Prime Attribute

A key attribute; that is, an attribute that is part of a key or is the whole key. See also key attributes.

Primary Key

A minimal collection of columns (attributes) in a table on which all columns are functionally dependent and that is chosen as the main direct access vehicle to individual rows. Also see candidate key

Candidate Key

A minimal collection of columns (attributes) in a table on which all columns are functionally dependent but that has not necessarily been chosen as the primary key

Normalization First Normal Form (1NF)

A model is in 1NF if, for every instance of an entity, each attribute of the instance is completely determined by the instance's PK In other words, 1NF holds when there are no instance attributes that have multiple values

Partial Dependency

A non-key characterisitc is dependent on only part of the key, not the whole thing. To eliminate, pull out the elements that are dependent and put them in their own table.

Transitive dependency

A nonkey attribute functionally depends on another nonkey attribute

denormalization

A process by which a table is changed from a higher level normal form to a lower level normal form. Usually done to increase processing speed. Potentially yields data anomalies.

Denormalization

A process by which a table is changed from a higher-level normal form to a lower-level normal form, usually to increase processing speed. Denormalization potentially yields data anomalies.

denormalization

A process by which a table is changed from a higher-level normal form to a lower-level normal form, usually to increase processing speed. This process potentially yields data anomalies

Denormalization

A process by which a table is charged from a higher-level normal form to a lower-level normal form, usually to increase processing speed. Denormalization potentially yields data anomalies.

normalization

A process that assigns attributes to entities in such a way that data redundancies are reduced or eliminated.

normalization

A process that assigns attributes to entities so that data redundancies are reduced or eliminated

Normalization

A process that assigns attributes to entities so that data redundancies are reduced or eliminated.

atomicity

A property of transactions that states that all parts of a transaction must be treated as a single logical unit of work in which all operations must be completed (committed) to produce a consistent database.

ER diagrams (ERDs)

A standardized set of notation for representing entities (attributes, PKs, and FKs) and the relationships between them

surrogate key

A system-assigned primary key, generally numeric and auto-incremented.

Note:

A table (relation) is in first normal form (1NF) when it does not contain repeating groups.

table

A table in a relational database stores information about only one entity.

3NF

A table is in 3NF when it is in 2NF and no nonkey attribute is functionally dependent on another nonkey attribute; that is, it cannot include transitive dependencies

Third Normal Form (3NF)

A table is in 3NF when it is in 2NF and no nonkey attribute is functionally dependent on another nonkey attribute; that is, it cannot include transitive dependencies.

third normal form (3NF)

A table is in 3NF when it is in 2NF and no nonkey attribute is functionally dependent on another nonkey attribute; that is, it cannot include transitive dependencies.

4NF

A table is in 4NF if it is in 3NF and contains no multiple independent sets of multivalued dependencies

Fourth Normal Form (4NF)

A table is in 4NF if it is in 3NF and contains no multiple independent sets of multivalued dependencies.

First Normal Form (1NF)

A table is in first normal form if it does not contain a repeating group

1NF

A table is in first normal form if it does not contain any potential multi-valued attributes. Business rules determine if an attribute is potentially multi-valued e.g. if a table stores three addresses for a single customer, the attribute is potentially multi-valued

Associative Entities

An associative entity is a relationship turned into an entity

Nonkey Attribute

An attribute (column) that is not part of the primary key

Foreign key

An attribute or set of attributes for a "child" instance/row of an entity that identifies the unique "parent" instance/row 1. The parent can either be in the same of different entity than the child 2. FKs can be null under certain conditions (acceptable orphans) 3. Typically the foreign key of the child identifies the primary key of the parent --But exceptions are possible

atomic attribute

An attribute that cannot be further subdivided to produce meaningful components. For example, a person's last name attribute cannot be meaningfully subdivided into other name components; therefore, the last name attribute is atomic.

Atomic Attribute

An attribute that cannot be further subdivided to produce meaningful components. For example, a person's last name attribute cannot be meaningfully subdivided.

Non-prime Attribute

An attribute that is not part of a key.

Nonprime attribute

An attribute that is not part of a key.

nonprime attribute

An attribute that is not part of a key.

78) A table designed to store PhoneNumber01, PhoneNumber02 and PhoneNumber03 contains ________. A) the multivalued, multicolumn problem B) the inconsistent values problem C) the missing values problem D) the general-purpose remarks column problem E) None of the above is correct.

Answer: A Diff: 1 Page Ref: 168-170

72) A classic example of unneeded normalization is when we are dealing with ________. A) ZIP codes B) sales orders and line items C) association patterns D) multivalued dependencies E) general purpose remarks columns

Answer: A Diff: 2 Page Ref: 164-165

79) A form of multivalued dependency is found in ________. A) the multivalued, multicolumn problem B) the inconsistent values problem C) the missing values problem D) the general-purpose remarks column problem E) None of the above is correct.

Answer: A Diff: 2 Page Ref: 168-170

67) The advantages of normalization include ________. A) the elimination of modification anomalies B) the elimination of duplicated data C) more complex SQL for multitable subqueries and joins D) A and B E) A, B, and C

Answer: A Diff: 3 Page Ref: 162 Fig 4-2

99) What are null values, and why are they a problem?

Answer: A null value is a missing value, a value that has never been input into a cell in a table. Null values are a problem because they are ambiguous. A null value can represent three different conditions: (1) the value is inappropriate, (2) the value is appropriate but unknown, or (3) the value is appropriate and known but not entered into the database. Diff: 1 Page Ref: 171-172

63) The SQL function COUNT ________. A) counts the number of columns in a table B) counts the number of rows in a table C) counts the number of tables in a database D) A and C E) B and C

Answer: B Diff: 1 Page Ref: 161

65) The SQL keyword TOP ________. A) limits the number of columns retrieved from a table B) limits the number of rows retrieved from a table C) limits the number of tables retrieved from a database D) A and C E) B and C

Answer: B Diff: 1 Page Ref: 161

75) For a number of reasons, ________ is not often an advantage for a read-only database. A) updating B) normalization C) denormalization D) A and B E) B and C

Answer: B Diff: 1 Page Ref: 165-166

81) You are creating a PRODUCT table using existing data from multiple sources. Examining the data, you find that you have "large red hat", "large hat, red", "red hat large" and "hat, large, red." This is an example of ________. A) the multivalue, multicolumn problem B) the inconsistent values problem C) the missing values problem D) the general-purpose remarks column problem E) None of the above is correct.

Answer: B Diff: 1 Page Ref: 168-170

82) The problem of misspelled data entries is really the same as ________. A) the multivalue, multicolumn problem B) the inconsistent values problem C) the missing values problem D) the general-purpose remarks column problem E) None of the above is correct.

Answer: B Diff: 1 Page Ref: 170-172

86) A missing value is called a(n) ________. A) empty value B) null value C) missing value D) Any of A, B, or C can be used. E) None of the above is correct.

Answer: B Diff: 1 Page Ref: 171

64) To limit the number of rows retrieved from a table, use the SQL construct ________. A) SELECT * B) SELECT TOP n * C) SELECT COUNT(TOP n) D) SELECT COUNT(*) E) SELECT COUNT *

Answer: B Diff: 2 Page Ref: 161

80) When a table is created using existing data from multiple sources, you are likely to find that the different sources code data in slightly different ways. This is an example of ________. A) the multivalued, multicolumn problem B) the inconsistent values problem C) the missing values problem D) the general-purpose remarks column problem E) None of the above is correct.

Answer: B Diff: 2 Page Ref: 168-170

What is shown in the results of this query? A) All values of CustomerNumber that match the constraint. B) All values of CustomerNumber that violate the constraint. C) All values of CustomerNumber where SALE.CustomerNumber = CUSTOMER.CustomerNumber. D) A and C E) B and C

Answer: B Diff: 3 Page Ref: 161

69) The presence of one or more foreign keys in a relation prevents ________. A) the elimination of modification anomalies B) the elimination of duplicated data C) more complex SQL for multitable subqueries and joins D) A and B E) A, B, and C

Answer: B Diff: 3 Page Ref: 162

68) The disadvantages of normalization include ________. A) the elimination of modification anomalies B) the elimination of duplicated data C) more complex SQL for multitable subqueries and joins D) A and B E) A, B, and C

Answer: C Diff: 1 Page Ref: 162 Fig 4-2

59) The second step in assessing table structure includes ________. A) counting rows B) examining columns C) examining data values D) A and B E) B and C

Answer: C Diff: 2 Page Ref: 161-162 Fig 4-1

76) ________ is the process of joining two or more tables and storing the result as a single table. A) Querying B) Normalization C) Denormalization D) A and B E) B and C

Answer: C Diff: 2 Page Ref: 166

83) When a table is created using existing data from multiple sources, you are likely to find that some data values have never been provided. This is an example of ________. A) the multivalued, multicolumn problem B) the inconsistent values problem C) the missing values problem D) the general-purpose remarks column problem E) None of the above is correct.