ISM 3201 Exam 2 Study Guide CH 4-6

Understand and explain various functional dependencies, including: -Full/Full-key dependency -Transitive dependency

*-Functional dependence:* the attribute B is fully functionally dependent on the attribute A if each value of A determines one and only one value of B *-Functional dependence (generalized definition):* attribute A determines attribute B if all of the rows in the table that agree in value for attribute A also agree in value for attribute B *-Fully functional dependence:* if attribute B is functionally dependent on a composite key A but not on any subset of that composite key, attribute B is fully functionally dependent on A *-Partial dependency:* an attribute is dependent on only a portion (subset) of the primary key *-Transitive dependency:* an attribute is dependent on another attribute that is not part of the primary key

Converting between the normal forms

*1NF (all key attributes defined, no repeating groups, all attributes dependent on primary key)* -eliminate repeating groups -identify the primary key -identify all dependencies *2NF (in 1NF, no partial dependencies)* -make new tables to eliminate partial dependencies -reassign corresponding dependent attributes *3NF (in 2NF, no transitive dependencies)* -make new tables to eliminate transitive dependencies -reassign corresponding dependent attributes

Describe the requirements for BCNF and 4NF

*Boyce-Codd Normal Form (BCNF) Requirements:* -has to be in 3NF -every determinant is a candidate key *Fourth Normal Form (4NF) Requirements:* -has to be in BCNF -no independent multivalued dependencies

Understand how ERD components affect database design and implementation

*Database design must conform to design standards* -These standards guide you in developing logical structures that minimize data redundancies, thereby minimizing the likelihood that destructive data will occur *High processing speed may limit the number and complexity of logically desirable relationships* -In many organizations, high processing speeds are often a top priority in database design. High processing speed means minimal access time. *Maximum information generation may lead to loss of clean design structures and high transaction speed* -Complex information requirements may dictate data transformations and they may expand the number of entities and attributes within the design. Therefore, they may have to sacrifice some of its "clean" design structures and high transaction speed to ensure maximum info generation.

CH. 6 Explain what is meant by normalization and its role in the database design process

*Normalization:* a process for evaluating and correcting table structures to minimize data redundancies, thereby reducing the likelihood of data anomalies; it involves assigning attributes to tables based on the concepts of determination and functional dependency

Constructs of the EERM cont. 2

*disjoint (nonoverlapping) subtypes:* unique and nonoverlapping subtype entity sets *overlapping subtypes:* a condition in which each entity instance (row) of the supertype can appear in more than one subtype *completeness constraint:* specifies whether each entity subpertype occurrence must also be a member of at least one subtype; this constraint can be partial or total *partial completeness:* a condition in which some supertype occurrences might not be members of any subtype *total completeness:* a condition in which every supertype occurrence must be a member of at least one subtype *specilization:* the grouping of unique attributes into a subtype entity *generalization:* the grouping of common attributes into a supertype entity

Constructs of the EERM cont.

*specialization hierarchy:* a hierarchy based on the top-down process of identifying lower-level, more specific entity subtypes from a higher-level entity supertype; specialization is based on grouping unique characteristics and relationships of the subtypes -The relationships are described in terms of "is-a" relationships *A specialization hierarchy provides the means to:* -Support attribute inheritance -Define a special supertype attribute known as the subtyoe discriminator -Define disjoint or overlapping constraints and complete or partial constraints *inheritance:* enables an entity subtype to inherit the attributes and relationships of the supertype -all entity subtypes inherit their primary key attribute from their supertype *subtype discriminator:* the attribute in the supertype entity that determines to which subtype the supertype occurrence is related

Consideration for surrogate keys

-Designers use surrogate keys when the primary key is considered to be unsuitable for some reason -At the implementation level, a surrogate key is a system-defined attribute that is generally managed and created via the DBMS -A system-defined surrogate key is usually numeric and its value is automatically incremented for each new row

Explain the importance of normalization in databases

-Normalization should be part of the design process, so it is important to make sure that proposed entities meet the required form before the table structures are created -An ERD is created through an iterative process -Normalization focuses on the characteristics of specific entities; that is, normalization represents a micro view of the entities within the ERD

Describe how relationships between entities are defined, refined, and incorporated into the design process

A relationship is an *association between entities*. The entities that participate in a relationship are *participants* and each relationship is identified by a name thar describes the relationship. To define relationships between entities, you would have to specify that: -A [ENTITY NAME] may generate many [ENTITY NAME] -Each [ENTITY NAME] is generated by [ENTITY NAME]

Interpret and construct ERDs

Building and ERD: -Create a detailed narrative of the organization's description of operations -Identify business rules based on the description of operations -Identify main entities and relationships from the business rules -Develop the initial ERD -Identify the attributes and primary keys that adequately describe entities -Revise and review the ERD

CH. 5 Describe the main extended entity-relationship (EER) model constructs and how they are represented in ERDs and EERDs

EERM: the result of adding more semantic constructs, such as entity supertypes, entity subtypes, and entity clustering to the original ER model Constructs of the EERM: *entity supertype:* a generic entity type that contains the *common* characteristics of entity subtypes *entity subtypes:* a subset of an entity supertype that contains the *unique* characteristics of each entity Two criteria help the designer determine when to use subtypes and supertypes: -There must be different, identifiable kinds or types of the entity in the user's environment -The different kinds or types of instances should each have one or more attributes that are unique to that kind or type of instance

Discuss methods to improve database design

Minimize data entry errors Evaluate naming conventions Refine attribute atomicity *-atomic attribute:* an attribute that cannot be further subdivided to produce meaningful components *-atomicity:* not being able to be divided into smaller units Identify new attributes Identify new relationships Refine primary keys as required for data granularity *-granularity:* the level of detail represented by the values stored in a table's row *-atomic data:* data stored at its lowest level of granularity Maintain historical accuracy Evaluate using derived attributes

CH. 4 Identify the characteristics of entities

The characteristics of entities are called *attributes* In this, there are: *Required Attribute:* an attribute that must have value and can't be left empty *Optional Attribute:* does not require a value and can be left empty *Domain:* a set of possible values for a given attribute *Identifiers (primary key):* one or more attributes that uniquely identify each entity instance *Relational Schema:* the organization of a relational database as described by the database administrator *Composite Identifier:* a PK composed of more than one attribute *Composite Attribute:* an attribute that can be further subdivided to yield additional attributes *Simple Attributes:* an attribute that cannot be subdivided *Single-valued Attributes:* an attribute that can only have a single value *Multi-valued Attributes:* attributes that can have many values *Derived Attributes:* an attribute whose value is calculated from other attributes

Describe the characteristics of good primary keys and how to select them

The characteristics of primary keys are: *Unique values* -The PK must uniquely identify each entity instance and guarantee unique values, it cannot contain nulls *Nonintelligent* -The PK should not have embedded semantic meaning other than to uniquely identify each instance *No change over time* -If an attribute has semantic meaning, it might be subject to updates, which is why names do not make good primary keys *Preferably single-attribute * -A primary key should have the minimum number of attributes possible *Preferably numeric* -Unique values can be better managed when they are numeric because the database can use internal routines to implement a counter-style attribute that automatically increments values with the addition of each new row *Security-compliant* -The selected primary key must not be composed of any attribute(s) that might be considered a security risk or violation

Discuss the normalization process

The objective of normalization is to ensure that each table conforms to the concept of well-formed relations or tables that have the following characteristics: -each relation (table) represents a single subject -each row/column intersection contains only one value and not a group of values -no data item will be unnecessarily stored in more than one table -all nonprime attributes in a relation (table) are dependent on the PK--the entire primary key and nothing but the primary key -each relation (table) has no insertion, update, or deletion anomalies, which ensures the integrity and consistency of the data