Module 1 Database Management

Database

organized collection of data— facts about people, events, things, or ideas— related to a specific topic or purpose

Difference between file systems and databases

file systems are simple large collections of unstructured files
databases are collections of structured, relational, or complex data that needs to be accessed and monitored constantly

Information

data that is organized in a useful manner

Purpose of a Database

to store large amounts of data in such a way that it is efficient and fast to retrieve it

Is database a single software?

No, it is a system

Databases are made for the storage of ___ amounts of ___

large, data

A database models a real world enterprise using ___ and ___

entities, relationships

DBMS

stands for Databases Management Systems (software package designed to store and manage databases)

Database System

The DBMS software together with the data itself. (Sometimes applications are also included

Mini-world

Some part of the real world about which data is stored

in a database

File system

collection of individual files

accessed by application programs

Drawbacks of File Systems

Data Isolation - files are not related when stored

Data Incompatibility/Redundancy - Data is incompatible across operating systems

How can you define a database?

data types, structures, constraints

What is another word for loading the database contents?

constructing

How can you manipulate the database?

Retrieval: Querying, generating reports

Modification: Insertions, deletions and updates to its content

Accessing the database through Web applications

What are the advantages of using a DBMS

Data Abstraction and Independence
Redundancy control and avoidance

Inferences and actions from the stored data

Data integrity

Security

Efficient Access

Data Administration

Rapid Application Development

Concurrent Access

What is data abstraction/independence when it comes to Advantages of using a DBMS?

Data models are used to present users with a conceptual view of the database while hiding the complexities of the underlying data

What do duplicates do in a DBMS?

wastes disk space, causes inconsistencies, requires updates, increases complexity, and reduces speed

How does data abstraction work?

Programs refer to the data model constructs rather than data storage details

Data abstraction allows for this, which describes the data of interest to that user.

Support of multiple views of data

Large amounts of data requires this to save resources

Redundancy control and avoidance

What does data integrity guarantee?

that the data stored is accurate and consistent with its purpose and design

Using rules and triggers can do two things, what are they?

validate user input
triggers action based on conditions of data

What does security allow in a DBMS?

Access control with user accounts

Levels of Access
Encryption

Efficient Access

Using different interfaces for different types of users

What are some actions that can be done using data administration?

Performance monitoring, data backup or recovery, capacity planning, database tuning, troubleshooting and testing

What was done thanks to rapid application development?

database APIs for different systems/programming languages

Concurrent access

allows multiple users to access/update the database

Concurrency Control

guarantees that each transaction is correctly executed or aborted

Recovery subsystem

ensures that each completed transaction is permanently recorded in the database

Queries

a question made to the database about the data it stores

Relational Algebra

More operational, very useful for representing execution plans

Relational Calculus

More nonoperational and declarative, lets users describe what they want, rather than how to compute it.

Transaction

the propagation of one or more changes to the database through a work unit

What are the properties of transactions (ACID)?

Atomicity
Consistency
Isolation
Durability

Atomicity (all-or-nothing)

ensures that all operations within the work unit are completed successfully (or will be reverted

Consistency

ensures that the database properly changes states upon a successfully committed transaction

Isolation

enables transactions to operate independently of and transparent to each other.

Durability

ensures that the result or effect of a committed transaction persists in case of a system failure.

DBMS catalog stores what?

the description (or meta-data) of a particular database

DBMS catalog allows the software to..?

work with different database applications (program data independence)

Actors on the Scene

Those who actually use and control the database content, and those who design, develop and maintain database applications

Workers Behind the Scene

Those who design and develop the DBMS software and related tools, and the computer systems operators

Database Administrators

Responsible for authorizing access to the database, for coordinating and monitoring its use, acquiring software and hardware resources, controlling its use and monitoring efficiency of operations.

Database Designers

Responsible to define the content, the structure, the constraints, and functions or transactions against the database. They must communicate with the end-users and understand their needs

End-users

They use the data for queries, reports and some of them update the database content.

Categories of End-users

Casual

Naive/Parametric

Sophisticated
Stand-alone

Casual End-user

access database occasionally when needed

Naive/parametric end-user

They use previously well-defined functions in the form of “canned transactions” against the database

(users of mobile apps, bank tellers, social media users)

sophisticated end-user

They are thoroughly familiar with system capabilities, use tools in the form of software packages

stand-alone end user

maintain personal databases using ready-to-use packaged applications

Hierarchical database model

data records are linked as collection trees, with one-to-many relationships (1 parent)

Network Database Model

Data records are linked as graphs, with many-to-many relationships (more than 1 parent)

Entity Relational Model

Most widely used, models are easy to understand (more semantics), but limited to conceptual model (no implementation)

Relation

A table with rows and columns, used by Entity Relational Model

Schema

describes a relation’s attributes(columns)

Object-oriented and extended relational models

more semantics, support for complex objects, support for inheritance, unstructured data (xml)

NOSQL database model

non-relational, less semantics, grows horizontally, lack of specific standards, designed for programmers/big data

What are the main costs of using a DBMS?

High initial investment, overhead for providing many functions (security, generality, etc)

When would a DBMS be unnecessary

if database is simple, well defined, and not expected to change
if access to data by multiple users isnt required

When would a DBMS be infeasible

in embedded systems where a general DBMS may not fit in available storage

Data Model

concepts describing structure of database, operations, and constraints

Data Model Constructs

define database structure (elements/groups of elements) and relationships among them

Data Model Constraints

specify restrictions on valid data

Data Model Operations

specify database retrievals and updates

Conceptual Data Models

(High Level, Semantic), provide concepts that are close to the way many users perceive data

Examples of Conceptual data models

Entity Based (ER Diagrams)

Object Based (UML diagrams)

Physical Data Models

(Low Level, Internal), provide concepts that describe details of how data is stored physically

Implementation Data Models

(Representational), provide concepts that mix details of how data is stored physically with details of how many users percieve data

Self Describing Data Model

(semi-structured/unstructured) include XML, key-value stores, and other NOSQL Systems

Database Schema

Defines the database implementation of a data model in a specific DBMS

What does Database Schema include?

the database structure, data types, keys, and the constraints on the database

What is another word for Schema?

Implementation Model or Intension

Schema Diagram

Illustrative Display of a database schema

What is another word for Schema Diagram?

entity-relationship Diagram

Schema Construct

A component of the schema or an object within the schema

Database State

Actual data stored in a database at a particular moment in time

database state is also known as what?

Database instance/occurence/snapshot

The term instance is also applied to what?

individual database components

Initial Database State

Refers to the database state when it is initially loaded into the system

Valid State

A state that satisfies the structure and constraints of the databas

What is the difference between a database schema vs a database state?

The database schema changes very infrequently.

The database state changes every time the database is updated

What is another word for State?

extension

What is the external schema (Top of the three-schema architecture)?

Describes how users see the data (described as three different view boxes going to and from the conceptual/logical schema)

What is the conceptual (logical) schema (middle of the three-schema architecture)?

Describes the structure-constraints for the database using a conceptual or implementation data model (takes info to/from the internal schema)

What is the internal (Physical) schema (bottom of the three-schema architecture)?

Defines how the data is stored in files and indexes used

Creating new entities/relationships is conceptual, internal, or external?

conceptual

Using a different storage medium is conceptual, internal, or external?

internal

Changing the type of index on an entity is conceptual, internal, or external?

internal

Connecting to the database using java is conceptual, internal, or external?

external

What are the steps in database design?

Requirements Gathering
Creating conceptual model
choose appropriate DBMS
Map conceptual model into Implementation Model
Implement the database Schema using the DBMS language

Data Definition Language

Used by the DBA and database designers to specify the conceptual schema of a database.

Data Manipulation Language

Used to specify database retrievals, insertions, deletions, and modifications (updates)

High-Level or Non-procedural Languages

The user only specifies what data is needed (these include the relational language SQL)

Low Level or Procedural Languages

The user specifies what data is needed and how to get it (recordsets) in a programming language like C++, Java, etc

View Definition Language

Allow us to create user views

What are views?

“virtual” tables containing subsets of records available to certain users

Data Control Language

Allow us to control the access of a databases (define privileges)

Storage Control Language

Allow us to map elements to disk and define ways to store data (indexes, hashes, etc.)

Transaction Control Language

Allow us to control transaction commits and rollbacks