ITM 408 Chapters 1 & 2

Three KPIs of a DBMS

- response time denoting the time elapsed between issuing a database request and the successful termination thereof - throughput rate representing the transactions a DBMS can process per unit of time - space utilization referring to the space utilized by the DBMS to store both raw data and metadata

If the relation is not in second normal form, we must:

- decompose it and set up a new relation for each partial key together with its dependent attribute types - keep a relation with the original primary key and any attribute types that are fully functional dependent on it

File approach

- duplicate or redundant information will be stored - danger of inconsistent data - strong coupling between applications and data - hard to manage concurrency control - hard to integrate applications aimed at providing cross-company services

Database designer

translates the conceptual data model into a logical and internal data model

Interactive Queries

typically executed from a front-end tool

Network DBMS

use a network data model more flexible that tree-like model procedural and record-oriented no query processor included

Relational DBMS

use the relational data model and are the most popular in the industry declarative and set oriented query processor included data independence is available

Data Definition Language (DDL)

used by the DBA to express the database's external, logical, and internal data models

Data Manipulation Language (DML)

used to retrieve, insert, delete, and modify data

Object-Relational DBMS

uses a relational model extended with object-oriented concepts, such as user-defined types, user defined functions, collections, inheritance, and behaviors

External data model is also called what?

views

Business user

will run these applications to perform specific database operations

Embedded DML Statement

applications use them to interact with DBMS

ACID

Atomicity, Consistency, Isolation, Durability

Sensor DBMS

Manages sensor data such as biometric data obtained from wearables, or telematics data which continuously record driving behavior

Metadata

refers to the data definitions

N-Tier DBMS Architecture

A straightforward extension of the client-server architecture communication between these various servers is then handled by middleware

Entity Integrity

All primary key entries are unique, and no part of a primary key may be null

multi-valued attribute

an attribute type that can have multiple values

Semi-Structured Data

Data that have a certain structure, but the structure may be very irregular or highly volatile

A key attribute type

an attribute type whose values are distinct for each individual entity can also be a combination of attribute types

Attribute value

an instance of an attribute type

Data Independence

Changes in data definition have minimal to no impact on the application using the data

Consistency

assures that a transaction brings the database from one consistent state to another

Database model

provides the description of the database data at different levels of detail and specifies the various data items

Catalog

the heart of the DBMS contains the data definitions, or metadata, of your database application

Cloud DBMS Architecture

DBMS and database are hosted by third party cloud provider

Open source DBMS

DBMSs for which the code is publicly available and can be extended by anyone

Advantage of Database: Managing Data Redundancy

Duplication of data can be desired in distributed environments to improve data retrieval performance DBMS is now responsible for the management of the redundancy by providing synchronization facilities to safeguard data consistency Compared to the file approach, the DBMS guarantees correctness of the data without user intervention

Entity Integrity Purpose

Each row will have a unique identity, and foreign key values can properly reference primary key values

Logical Data Independence

Implies that software applications are minimally affected by changes in the conceptual or logical data model

Structured Data

Individual characteristics of data items can be identified and formally specified.

Database Management System (DBMS)

the software package used to define, create, use, and maintain a database

Unstructured Data

there are no finer-grain components in a file or series of characters that can be interpreted in a meaningful way by a DBMS or application

Object-Relational DBMS is also known as

Extended Relational DBMS

Physical Data Independence

Implies that neither the application, views, or logical data model must be changed when changes are made to the data storage specifications in the internal data model

A functional dependency X → Y in a relation is a transitive dependency if

there is one or more attribute types Z that is neither a primary key nor a subset of a primary key, and both X → Z and Z → Y hold

Federated DBMS

Provides a uniform interface to multiple underlying data sources Hides the underlying storage details to facilitate data access

Database state

Represents the data in the database at a particular moment

XML DBMS

Uses the XML data model to store data; represents data in a hierarchical, nested, way.

Database

a collection of related data items

Database System

a combination of a DBMS and a database

Database Management System Architecture

a conceptual specification of the various modules that interact with one another to make up a DBMS.

Normalization

a process of analyzing given relations to ensure they do not contain any redundant data

logical data model

a translation or mapping of the conceptual data model toward a specific implementation environment

NoSQL Databases

abandon the well-known and popular relational database schema in favor of a more flexible, or even schema-less, database structure scale more easily store unstructured information

Client-Server DBMS Architecture

active clients request services from passive servers

Hierarchical DBMS

adopt a tree-like data model procedural and record-oriented no query processor included

Multimedia DBMS

allow for the storage of multimedia data such as text, images, audio, video, 3D games, CAD designs, etc. provide content-based query facilities streaming facilities stored as a binary large object (BLOB)

Multi-user Systems

allow multiple users to simultaneously interact with the database in a distributed environment

Foreign Key

an attribute type of one entity type that is referring to the primary key of another entity type

composite attribute type

an attribute type that can be decomposed into other meaningful attribute types

Object-Oriented DBMS

based upon the object-oriented data model no impedance mismatch not very popular in the industry

Backup and recovery facilities

can be used to deal with the effect of loss of data due to hardware or network errors, or bugs in system or application software

A simple or atomic attribute type

cannot be further divided into parts

NoSQL Database

classified according to data model into key-value stores, tuple, or document stores, column-oriented databases, and graph databases

The layers of three-layer architecture

conceptual/logical layer external layer internal layer

External Data Model

contains various subsets of the data items in the logical model tailored towards the needs of specific applications or group users

DDL Statement

create data definitions that are stored in the catalog

database state is also known as what?

current set of instances

Categorization based on

data model degree of simultaneous access architecture usage

A database model is also called?

database schema

If the relation is not in third normal form, we need to

decompose the relation and set up a relation that includes the non-key attribute types that functionally determine the other non-key attribute types

relationship type

defines a set of relationships among instances of one, two or more entity types

view

describe the part of the database that a particular application or user group is interested in, hiding the rest of the database

Information architect

designs the conceptual data model - closely interacts with the business user

Application developer

develops database applications in a programming language such as Java or Python

Durability

ensures that the database changes made by a transaction declared successful can be made permanent under all circumstances

Isolation

ensures that the effect of concurrent transactions should be the same as if they would have been executed in isolation

The three-layer architecture

essential element of every database application and describes how the different underlying data models are related

Online Transaction Processing (OLTP) DBMS

focus on managing operational or transactional data

Semantical rules

focus on the semantical correctness or meaning of the data

Conceptual/logical layer

focuses on the data items, their characteristics, and relationships without bothering too much about the actual physical DBMS Implementation

Online Analytical Processing (OLAP) DBMS

focuses on using operational data for tactic or strategical decision-making

single-valued attribute

has only one value for a particular entity

partial dependency

if an attribute type A from X can be removed from X and the dependency still holds

full functional dependency

if removal of any attribute type A from X means that the dependency does not hold anymore

internal layer

includes internal data model, specifies how the data is stored or organized physically

external layer

includes views offering a window on a carefully selected part of the logical data model

DBMS should avoid _____________________

inconsistencies!

A relation is in the second normal form (2 NF) if

it satisfies 1 NF and every non-key attribute type is fully functionally dependent on the entire primary key

A relation is in the third normal form (3 NF) if

it satisfies 2 NF and no non-key attribute type is transitively dependent on the primary key

cardinalities

minimum or maximum number of relationship instances that an individual entity can participate in

Single-user system

only one user at a time is allowed to work with the DBMS

Conceptual Data Model

provides a high-level description of the data items with their characteristics and relationship

Entity Type

represents a business concept with an unambiguous meaning to a particular set of users

Attribute type

represents a property of an entity type

relationship

represents an association between two or more entities

Atomicity

requires that a transaction should either be executed 2 in its entirety or not all

Database Administrator (DBA)

responsible for the implementation and monitoring of the database

Integrity rules

specified as part of the conceptual\logical data model and stored in the catalog

Syntactical rules

specify how the data should be represented and stored

The first normal form (1 NF)

states that every attribute type of a relation must be atomic and single valued

In the database approach where is data stored?

stored and managed in the DBMS

In-memory DBMS

stores all data in internal memory instead of slower external storage such as disk-based storage

Spatial DBMMS

supports the storage and querying of spatial data key building block for geographical information system

Mobile DBMS

the DBMSs running on smartphones, tablets, and other mobile devices they should always be online, have a small footprint, and be able to deal with limited processing power, storage, and battery life

Centralized DMBS Architecture

the data maintained on a centralized host system. all queries have to the processed by this single host