Ch.9 Cumbia

RECIPE

(Product_ID, Item_Number, Quantity_Used)

ITEM SALE

(Receipt_Number, Product_ID, Quantity_Sold)

INVOICE ITEM

(Vendor_ID, Invoice_Number, Item_Number, Quantity_Added)

Second Normal Form (2NF)

A relation is in second normal form if every nonprimary key is functionally dependent on the whole primary key.

Well-Structured Relations

A relation that contains a minimum amount of redundancy and allows users to insert, modify and delete rows without error or inconsistencies.

Referential Integrity

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.

Logical Database Model

Abstract representation of data requirements.

Data Integrity

Accuracy and consistency of stored data.

Foreign Key

An attribute that appears as a nonprimary key attribute in one relation and as a primary key attribute (or part of a primary key) in another relation.

Transforming E-R Diagrams

Converting E-R diagrams into relational schema.

Logical Data Model Development

Creating models based on user interface needs.

Recursive Foreign Key

Foreign key referencing the same relation's primary key.

Data Type

Format specifications for database attributes.

Supertype/Subtype

Hierarchical relationship between general and specific entities.

Primary Relationship Key

Key that uniquely identifies a relationship.

Attribute Length

Maximum size of data stored in an attribute.

Coding Scheme

Method for representing data values.

Data Storage Technologies

Methods for efficient and secure data processing.

Redundant Relations

Multiple relations describing the same entity type.

Data Requirements

Necessary information for database functionality.

Third normal form (3NF)

Nonprimary key attributes do not depend on each other (no transitive dependencies).

Decimal Places

Number of digits after the decimal point.

Composite Key

Primary key formed from multiple attributes.

Data Maintenance Problems

Problems that can occur in relations due to functional dependencies.

Data Normalization

Process to minimize redundancy in databases.

Database Administrators

Professionals managing database systems.

Attributes

Properties or characteristics of an entity.

Minimum and Maximum Values

Range limits for attribute data entries.

Unary Relationship

Relationship between instances of the same entity type.

Binary Relationship

Relationship involving two different entity types.

Response Time Expectations

Required speed for data retrieval and processing.

Homonyms

Same attribute name for different attributes.

Physical Database Design Data Type factors

Selecting a data type balances four objectives: Minimize storage space, Represent all possible values of the field, Improve data integrity for the field, Support all data manipulations desired on the field.

Relations from logical database design

Translated into specifications for computer files, typically tables in a relational database.

Primary Key

Unique identifier for records in a table.

E-R Diagram

Visual representation of entities and relationships.

E-R Diagrams

Visual representations of entity relationships.

Functional Dependency

A constraint between two attributes in which the value of one attribute is determined by the value of another attribute.

Default value

A default value is the value a field will assume unless an explicit value is entered for the field.

Calculated Field

A field that can be derived from other database fields. Also known as computed or derived field.

Physical Table

A named set of rows and columns that specifies the fields in each row of the table.

Relation

A named, two-dimensional table of data consisting of named columns and an arbitrary number of unnamed rows.

Null value control

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

Normalization

A process for converting complex data structures into simple, stable data structures.

Third Normal Form (3NF)

A relation is in second normal form and there are no functional dependencies between two or more nonprimary key attributes.

File Organization

Arrangement of records for efficient access.

Nonprimary Key Attributes

Attributes that are not part of the primary key.

Nonkey Attributes

Attributes that do not contribute to unique identification.

View Integration

Combining multiple user interface requirements.

Normalized Data Relationships

Connections between data elements in tables.

Data Element

Basic unit of information in a database.

Range control

Both numeric and alphabetic data may have a limited set of permissible values.

Dependencies between non-keys

Consider the following: STUDENT1(Student_ID,Major) STUDENT2(Student_ID,Adviser) Because STUDENT1 and STUDENT2 have the same primary key, the two relations may be merged: STUDENT(Student_ID,Major,Adviser)

Entity Type

Category of objects with similar characteristics.

View Integration Problems

Challenges in merging different database views.

Merge Relations

Combine redundant relations into a single relation.

System Inputs and Outputs

Data entering and exiting the information system.

Relational Database Model

Data organized in tables with relationships.

Denormalization effects

Denormalization can be used to improve performance but can increase the chance of errors and inconsistencies that normalization avoided.

Attribute Definition

Description of data characteristics in a database.

Technical Specifications

Detailed requirements for database implementation.

Synonyms

Different names for the same attribute.

User Views

Different perspectives of data for various users.

Address Attributes

Different types of address fields in student records.

Second normal form (2NF)

Each nonprimary key attribute is identified by the whole key (full functional dependency).

Unique Rows

Each row is unique, guaranteed by the relation having a nonempty primary key value.

Weak Entity

Entity that cannot be uniquely identified alone.

Associative Entity

Entity that resolves many-to-many relationships.

Simple Entries

Entries in cells referred to as simple, having a single value at the intersection of each row and column.

Normalized Tables

Stable structures with minimal data redundancy.

Database Design Process

Steps to create effective database structures.

Secondary Memory

Storage devices like hard disks and tapes.

Physical Records

Stored data structures based on logical models.

Normalized Relations

Tables structured to eliminate redundancy.

Physical Database Design

Technical specifications for data storage.

One to many relationship

The VENDOR relation has a one to many relationship with the INVOICE relation.

Designing Physical Tables

The design of a physical table has two goals: Efficient use of secondary storage and Data processing speed.

Referential integrity

The most common example of referential integrity is cross-referencing between relations.

Normalized relations

The primary deliverable from logical database design.

Denormalization

The process of splitting or combining normalized relations into physical tables based on affinity of use of rows and fields.

Interchangeable Rows

The rows may be interchanged or stored in any sequence.

Interchangeable Columns

The sequence of columns can be interchanged without changing the meaning or use of the relation.

Fields

The smallest unit of application data recognized by system software.

Transforming E-R Diagrams into Relations

This can be accomplished in four steps: Represent entities, where each entity type in the E-R diagram becomes a relation.