BMGT 402 Midterm

First Normal Form (1NF)

- All key attributes are defined - There are no repeating groups in the relation - All attributes are dependent on the primary key

Network Model

- Created to represent complex data relationships more effectively - Resembles hierarchical model, but allows a record to have more than one parent - Disadvantages: cumbersome, structural dependence, lack of ad hoc query capability placed burden on programmers to generate code for reports

DBMS Functions

- Data Dictionary Management - Data Storage Management - Data Transformation and Presentation - Security Management - Multiuser Access Control - Backup and Recovery Management - Data Integrity Management - Database Access Languages and Application Programming Interfaces - Database Communication Interfaces

Relational Model

- Developed by E. F. Codd (IBM) in 1970 - Model foundation is a mathematical concept known as a relation (or table)

DBMS Advantages

- Enables data to be shared - Presents the end user with an integrated view of data - Provides more efficient and effective data management - Improves sharing, security, integration, access, decision-making, productivity, etc.

DBMS Disadvantages

- Increased Costs - Management Complexity - Maintaining Currency - Vendor Dependence - Frequent Update/Replacement Cycles

Hierarchical Model

- The first data model (1960) - Basic logical structure is represented by an upside-down "tree" - Disadvantages: complex to implement, difficult to manage, lacks structural independence, no standards

Structural Dependence

-Access to a file is dependent on its own structure -All file system programs are modified to conform to a new file structure

Extended Relational Data Model (ERDM)

-Includes many of OO model's best features (e.g. inheritance) -Also often described as an object/relational database management system (O/R DBMS) -Primarily geared to business applications

Database Management System (DBMS)

-Is a collection of programs that manages the database structure -Controls access to data stored in the database -Acts as the intermediary between the user and the database

Second Normal Form (2NF)

-The relation is already in first normal form (1NF) -The relation contains no partial dependencies •If a relation has a single attribute as the primary key, it is automatically in 2NF if it is in 1NF

Third Normal Form (3NF)

-The relation is already in second normal form (2NF) -The relation contains no transitive dependencies

Determinant

A -> B A is known as the ______.

Dependent

A -> B B is known as the ______.

Business Rule

A brief, precise, and unambiguous description of a policy, procedure, or principle

Field

A character or group of characters (alphabetic or numeric) that has a specific meaning; used to define data

Attribute

A characteristic of an entity

Update Anomaly

A circumstance in which redundant data in a relation may not be properly updated.

Relational Database

A collection of normalized relations with distinct relation names

File

A collection of related records

Operational Database

A database is designed to support an organization's daily operations

Normalization

A design process for evaluating and correcting data structures to reduce data anomalies

System Catalog

A detailed system data dictionary that describes all objects in a database

NoSQL (Not Only SQL)

A distributed database system that efficiently stores structured and unstructured data

Partial Dependency

A functional dependency in which an attribute is dependent on only a portion (subset) of the primary key (only occurs when the primary key is a composite key)

Transitive Dependency

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

Natural Key (or Natural Identifier)

A generally accepted identifier for real world objects

Entity Relationship Diagram (ERD)

A graphical representation to model database components: -Entity instances (or entity occurrences) describe rows in the relational table -Attributes describe particular characteristics -Connectivity describes the relationship classification between entities (i.e. 1:1, 1:M, M:N)

Repeating Group

A group of multiple entries of the same or multiple types of a single key attribute occurrence

Composite Key

A key consists of two or more attributes

Record

A logically connected set of one or more fields that describes a person, place, or thing

Relation

A matrix (table) consisting of row/column intersections

Big Data

A movement to find new and better ways to manage large amounts of web-generated data and derive business insight from it, while simultaneously providing high performance and scalability at a reasonable cost.

Attribute

A named column of a relation

Entity

A person, place, thing, idea, or event about which data will be collected and stored

Design Trap

A problem that occurs when a relationship is improperly or incompletely identified and therefore is represented in a way that is not consistent with the real world.

Recursive Relationship

A relationship that is found within a single entity type. For example, an EMPLOYEE is married to an EMPLOYEE or a PART is a component of another PART.

Dependency Diagram

A representation of all data dependencies (e.g. full functional, partial, and transitive)

Constraint

A restriction placed on data

Relational Database Schema

A set of all relation schemas for the database

Database

A shared, integrated computer structure that stores data

Data Model

A simple representation of a complex, real-world data structures (often graphical)

Primary Key

A single attribute or combination of attributes that: -Uniquely identifies each entity instance -Guarantees entity integrity -Works with foreign keys to implement relationships

Derived Attribute

A value may be calculated from other attributes

Model

An abstraction of a more complex real-world object or event

Relationship

An association between entities

Ternary Relationship

An association is maintained between three entities

Binary Relationship

An association is maintained between two entities

Unary Relationship

An association is maintained within a single entity

Composite Attribute

An attribute that can be further subdivided to yield additional attributes. For example, a phone number such as 615-898-2368 may be divided into an area code (615), an exchange number (898), and a four-digit code (2368).

Atomic Attribute

An attribute that cannot be further subdivided to produce meaningful components

Composite Entity

An entity created as a bridge between the two entities (also known as associative entity or bridge entity)

Hadoop

An open-source framework, built in Java, to store and process data

Simple Attribute

Attribute that cannot be subdivided

Multi-valued Attributes

Can have many values (not allowed in relational models)

Business Intelligence

Captures and processes business data to generate information to support decision making

Entity Occurrence (Entity Instance)

Corresponds to a row in relational environment

Cloud Database

Created and maintained using cloud data services that provide defined performance measures for the database

Metadata

Data about data, through which the end-user data is integrated and managed

Data Dependence

Data access changes when data storage characteristics change

Data independence

Data storage characteristics are changed without affecting the program's ability to access the data

Unstructured Data

Data that exist in their original, raw state; that is, in the format in which they were collected

Structured Data

Data that has been processed

Semistructured Data

Data that has been processed to some extent

Time-variant Data

Data whose values are a function of time

Deletion Anomaly

Deleting rows may cause a loss of data that would be needed for other future rows

Normal forms

Denote standardized structures meeting specific characteristics

Connectivity

Describes the relationship classification -1:1, 1:N, or M:N

Distributed Database

Distributes the database across different sites

Participants

Entities within a relationship are called _____

Existence Independence

Entity can exist apart from one or more related entities

Existence Dependence

Entity exists in database only when it is associated with another related entity occurrence

Strong (or Identifying) Relationship

Exists when the primary key of the related entity does contain a primary key component of the parent entity; represented with a solid line

Weak (or Non-Identifying) Relationship

Exists when the primary key of the related entity does not contain a primary key component of the parent entity; represented with a dashed line

Cardinality

Expresses minimum and maximum number of entity occurrences associated with one occurrence of related entity

Structural Independence

File structure is changed without affecting the application's ability to access the data

Weak Entity

Fulfills two criteria: -Existence-dependent (associated with another related entity occurrence) -Primary key partially or totally derived from parent entity in relationship

Relational Schema

Graphical representation of relations, attributes, and relationships

Single-value Attribute

Holds a single value for each occurrence of an entity type

General-purpose Database

Holds a wide variety of data used in multiple disciplines

Centralized Database

Holds data at a single site

Discipline-specific Database

Holds data focused on specific subject areas

Data Warehouse

Holds data in a format optimized for decision support

Principle of Determination

If you know the value of attribute(s) A, you can determine the value of attribute(s) B

Optional Participation

In ER modeling, a condition in which one entity occurrence does not require a corresponding entity occurrence in a particular relationship; occurs when the minimum cardinality is 0

Relationship Degree

Indicates the number of entities or participants associated with a relationship

Workgroup Database

Multiuser Database; Supports a small number of users or a single department

Enterprise Database

Multiuser Database; Supports many users across many departments

Insertion Anomaly

Occurs when certain attributes cannot be inserted into the database without the presence of other attributes

Data Anomaly

Occurs when not all required changes in redundant data are made successfully

Fan Trap

Occurs when one entity is in two 1:M relationships, as the "1" side, to other entities

Redundant Relationship

Occurs when there are multiple relationship paths between related entities

Mandatory Participation

One entity occurrence requires a corresponding entity occurrence in a particular relationship; occurs when minimum cardinality is 1

Functional Dependency

One or more attributes determines one or more other attributes

Key

One or more attributes that determine other attributes

Identifiers (primary keys)

One or more attributes that uniquely identify each entity instance

Physical Model

Operates at the lowest level of abstraction, describing the way data are saved on storage media such as magnetic, solid-state, or optical media

Database System Environment

Organization of components that define and regulate the collection, storage, management, and use of data in a system, including: -Hardware -Software -People -Procedures -Data

Composite Identifier

Primary key composed of more than one attribute

End-user Data

Raw facts of interest to end user

Data

Raw facts, such as a telephone number, a birth date, a customer name, and a year-to-date (YTD) sales value

Variety

Refers to the idea of the multiple formats of source data

Volume

Refers to the large amount of data being stored

Velocity

Refers to the speed at which data grows and at which it must be processed to gain insights

Logical Design

Refers to the task of creating a conceptual model

Conceptual Model

Represents a global view of the entire database by the entire organization

Internal Model

Represents the database as "seen" by DBMS

External Model

Represents the end users' view of the data environment

Tuple

Row of a relation

Data Dictionary

Stores definitions of data elements and their relationships (stores metadata)

Multiuser Database

Supports multiple users at the same time

Single-user Database

Supports one user at a time

Performance

The higher the normal form, the fewer the data redundancies and inconsistencies, but the decreased ______.

Granularity

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

Degree

The number of attributes in a relation

Cardinality

The number of tuples in a relation

Domain

The set of allowable values for one or more attributes

Online Analytical Processing (OLAP)

Tools can be used to retrieve, process, and model data within the data warehouse

Logical Independence

When changes in the internal model do not impact the conceptual model

Physical Independence

When changes in the physical model do not impact the internal model

Denormalization

a design process by which a relation is changed from a higher-level normal form to a lower-level normal form

Relation Schema

a named relation followed by the attribute names in parentheses Examples (note the primary key is bold + underlined and the foreign key is italicized): •Branch(branchNo, street, city, postcode) •Staff(staffNo, fName, lName, position, sex, DOB, salary, branchNo)