Ch 1 - Fundamentals of Database Management Systems

Database

a collection of related data items within a specific business process or problem setting

Database management system (DBMS)

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

Database system

combination of a database and a DBMS

Data

stored representations of meaningful objects and events

Structured data

numbers, text, dates

Unstructured data

images, video, documents

Information

data processed to increase knowledge

Metadata

data that describes properties and context of user data

File approach to data management

Data definitions included in each application, hard to manage concurrency control, duplicate or redundant information will be stored, danger of inconsistent data.

Database approach to data management

Applications now directly interface with the DBMS to access the data, loose coupling between applications and data, data stored and managed centrally by a DBMS.

Data persistence

Data outlives application.

Data safety

Data is safe from failures, malicious users etc.

Concurrent access

Data is concurrently accessible by multiple users.

Airline reservation system

An example of a database management system that requires information on flights/customers, pending airline reservation tickets, ticket purchase history, and trends/preferences.

Data querying

Facilities provided for data querying and retrieval.

Data availability

Find availability of seats, list flights out of a certain airport, price range.

Purchase history generation

Generate a purchase history and sales figures per airline/state.

Geographical Information System (GIS)

An application that manages geographical data.

Customer Relationship Management (CRM)

An application that manages customer data.

Data redundancy

Duplicate or redundant information stored in files.

Concurrency control

Managing simultaneous operations without conflicting.

Loose coupling

Minimal dependency between applications and data.

Raw data

Data that has not been processed.

Catalog

A collection of metadata.

Data management

The process of storing, organizing, and maintaining data.

File Approach

A method of data management that involves using files such as Word documents and Excel files to store data.

DB Approach (SQL)

A method of data management that uses Structured Query Language (SQL) to interact with a database.

Procedure FindCustomer

A SQL procedure that retrieves customer information based on the name 'Bart'.

SELECT * FROM Customer WHERE name = 'Bart'

A SQL statement that selects all columns from the Customer table where the name is 'Bart'.

SQL is declarative

SQL specifies what data to retrieve rather than how to obtain it.

open file Customer.txt

A command in the file approach to open a text file containing customer data.

Read(Customer)

A command to read data from the Customer file.

EOF(Customer)

End Of File indicator for the Customer file, used to determine when to stop reading data.

display(Customer)

A command to show the customer data that matches the criteria.

Converting from file to DB approach

The process of changing data management from a file-based system to a database system, which can be complex and costly.

Costs of converting to a database

Includes expenses related to new personnel, installation, management complexity, conversion, and backup/recovery needs.

Logistics

The planning and execution of a project, in this case, related to the database system implementation.

DBMS

Database Management System, software that interacts with end users, applications, and the database itself to capture and analyze data.

File-based vs. database approaches

A comparison of using files for data management versus using a database system.

Advantages of DB systems

Benefits of using a database system, which may include improved data integrity, reduced redundancy, and easier maintenance.

iClicker Quiz

A quiz format used to assess understanding of database concepts.

Elements of a database system

Components that make up a database system, including database model, instances, and architecture.

Database model

A framework that provides a description of the database data at different levels of detail.

Database instances

Specific occurrences of data items, their characteristics, relationships, constraints, and storage details.

3-Layer Architecture

A design pattern for database systems that separates the user interface, application logic, and data storage.

Catalog

A collection of metadata that describes the structure of the database.

Users

Individuals or applications that interact with the database system.

Languages

Programming languages used to interact with the database, such as SQL.

Data model

A data model provides a clear, unambiguous description of the data items, their relationships, and various data constraints from a particular perspective.

3-Layer Architecture

Specified during database design.

Catalog

Not expected to change too frequently.

Users

Stored in the catalog.

Languages

Typically changes on an ongoing basis.

Elements of a Database System

A database model comprises different data models, each describing the data from different perspectives.

Database model

Represents data in the database at a particular moment.

Database instances

Also called the current set of instance.

Conceptual Data Model

Provides high-level description of data items (e.g., flights), their characteristics (e.g., flight number), and relationships (a flight flies out of an airport).

Business needs

Shared with information architect: What data to store, how are the different entities related, what are the constraints, if any, etc.

Implementation-independent

User-friendly, close to how business user perceives the data.

Entity relationship (ER) model

Usually represented using an entity relationship model, or an object-oriented model.

Logical data model

One of the types of data models developed during a DB design process.

External data model

One of the types of data models developed during a DB design process.

Data items

The individual pieces of data that are stored in a database.

Data relationships

The connections between different data items.

Data constraints

Rules that define the properties and limits of the data.

Database model

Provides description of the database data at different levels of detail

Database instances

Specifies data items, their characteristics and relationships, constraints, storage details etc.

Data model

e.g., the relational data model

3-Layer Architecture

Student (number, name, address, email), Course (number, name), Building (number, address)

Catalog

Not expected to change too frequently

Users

Stored in the catalog

Languages

Several types of data models are developed during a DB design process

Conceptual Data Model

Provides high-level description of data items (e.g., flights), their characteristics (e.g., flight number), and relationships (a flight flies out of an airport)

Business rules

Statements that define or constrain some aspect of business

Good business rule

Declarative - what, not how; Precise - clear, agreed-upon meaning; Atomic - one statement; Consistent - internally and externally; Expressible - structured, natural language; Distinct - non-redundant; Business-oriented - understood by business personnel

Logical Data Model

Mapping of conceptual data model to a specific implementation environment

Data model types

Can be a hierarchical, CODASYL, relational, object-oriented, extended relational, XML, or NoSQL model

Internal data model

Can be mapped to an internal data model that represents the data's physical storage details

Data retrieval

Describes which data are stored where, in what format, which indexes are provided to speed up retrieval, etc.

Database model

A structure that defines how data is organized, stored, and manipulated within a database.

External data model

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

Database instances

Contains various subsets of the data.

Data model

Items in the logical model, also called views, tailored toward the needs of specific applications or groups of users.

3-Layer Architecture

A framework that separates the database system into three layers: external, conceptual/logical, and physical/internal.

Catalog

Data stored and managed centrally by a DBMS.

Conceptual data model

A user-friendly, implementation-independent model that focuses on data items and their characteristics.

Logical data model

Based upon the implementation environment, detailing how data items relate to one another.

External Layer

The top level of the three-layer architecture, which provides views for one or more applications.

Conceptual/Logical Layer

The middle layer of the three-layer architecture, consisting of both the conceptual and logical data models.

Physical/Internal Layer

Specifies how the data are stored or organized physically.

View

Part of the database that an application is interested in; the rest of the database is hidden.

Data access and security

Controls that manage who can access data and how data can be accessed.

Application programmer

A person who develops applications that interact with the database.

Database administrator

A person responsible for managing and maintaining the database system.

Schema

The structure that defines the organization of data in the database.

Invoicing

An application that has access to certain views or parts of the data.

CRM

Customer Relationship Management application that interacts with the database.

GIS

Geographic Information System application that accesses specific views of the data.

Statement 1

The middle layer of the three-layer architecture consists of both the conceptual data model and the logical data model. The logical data model is physically implemented in the internal layer.

Statement 2

The top level of the three-layer architecture is the external layer. Views for one or more applications always offer a window on the complete logical model.