Databases: Data, DBMS, and Data Models

Learning Objectives

• Understand data as a resource within an organization.
• Data, Database, and DBMS should be defined.
• Understand the motivation and objectives for the database approach.
• Data independence should be defined.
• Database transactions, backup, and recovery knowledge is expected.
• Databases have improved security of data by inter-relating it through a Data Model.
• Relational, Hierarchical, and Network data models are major ones.

Data

• Dates refers to raw facts: concerning people, places, events, or other objects/concepts.
• Data by itself is useless unless it is somehow processed.
• Data that is processed to reveal meaning becomes information.
• A lack of data leads to inadequate information and ill-informed decisions.
• Data represents a valuable corporate resource needing adequate integrity and security controls.
• Data management generates, stores, and retrieves data properly.

Traditional File Systems

• Databases are often contrasted to traditional file systems though the latter are now rarely used.
• Issues with traditional systems included:
  • Requires extensive programming in third-generation language (3GL).
  • Had data and structural dependence and data redundancy.
• Requests needed a DP specialist to write programs, organizing data according to expected use, and the retriever required 3GL coding.
• The same data was often stored in many locations, e.g., agent details could be found in both the CUSTOMER and AGENT files.

Problems with Traditional File Systems

• Data Dependence: Changes in file's data characteristics requires modification of data access programs.
• The program must tell it what to do and how making file systems cumbersome from programming.
• Structural Dependence: Changes in file structure requires modification of related programs.
• Data Redundancy: May have different and conflicting versions of same data, data inconsistency and lack of data integrity.
  • Results of uncontrolled data redundancy can cause Data anomalies via modification, insertion, and deletion.

File Terminology

• Field: A group of characters with specific meaning.
• Record: Logically connected fields that describe a person, place, or thing.
• File: A collection of related records.

Database Systems

• A database consists of related data stored in a single repository providing advantages over file systems:
• Eliminating inconsistency, data anomalies, data dependency, and structural dependency problems.
• Stores data structures, relationships, and access paths.
• Centralized control of data can mean the data already exists for many applications.
• Data is no longer related by application programs, but by the structure defined in the database.

Objectives of Database Technology

• Data Independence
• Minimal Data Redundancy
• Increased Data Sharing
• Improved Data Quality
• Improved Security of Data
• Improved Access to Data
• Reduced Program Maintenance
• Inter-relate data thru the model

Data Independence

• It is the property of being able to change the logical or physical structure of data without requiring code changes to application programs that manipulate that data
• Results in a more complex structure.
• Data is stored independently of the programs and data descriptions are not embedded in them.
• The database structure can be changed with no impact on application programs through the database catalog or dictionary (the metadata).

Sharing of Data

• The DBMS has the ability to support multiple concurrent users of the same data and to ensure that the data remains consistent at all times.
• Transactions must be made logically serial to avoid concurrency problems, often achieved using record locking.
• A transaction can lock a record to prevent its update by another transaction until the update has completed.

Data Integrity

• Validation or integrity rules should be defined and automatically invoked at run time by the DBMS.
• ANSI/ISO suggest that 100% of all enterprise rules should be held in the database and specifically none in application programs.

Security

• Protecting data against accidental or intentional use by unauthorized users.
• Each user requires identification with a user-id and password.
• Users can be restricted in the data they can see and what actions they can perform on that data.
• The DBMS encrypts and decrypts data as it is stored and retrieved, restricting users access to data via Views.

Database and DBMS

• A database is a shared integrated computer structure that houses a collection of end-user data and metadata.
• A database management system (DBMS) is a collection of programs that manages the database structure, controls access to the data stored in the database, and includes a query language.

DBMS Functions

• Manages Data Dictionary: Stores the definitions of data and their relationships (metadata) in a data dictionary.
• Manages Data Storage: Creates and manages the complex structures required for data storage.
• Data Transformation & Presentation: Transforms entered data to conform to the data structures that are required to store the data.
• Manages Security: Creates a security system and enforces security within that system.
• Multi-user access control: Creates complex structures that allow multiple-user access to the data.
• Manages Backup and recovery : Performs backup and data recovery procedures to ensure data safety.
• Manages Data Integrity: Promotes and enforces integrity rules to eliminate data integrity problems.
• Provides Database Language and Application programming interfaces: Provides access to the data via utility programs and programming languages interfaces.
• Manages Database communication interfaces: Provides end-user access to data within a computer network environment.

Database Models

• Collection of logical constructs used to represent data structure and relationships within the database.
• Conceptual models: logical nature of data representation
• Implementation/Physical models: emphasis on how the data are represented in the database
• Conceptual Models consists of: Context Model, Key Model and a Fully attributed Model
• Implementation/Physical Models include: Hierarchical, Network and Relational

Implementation Models

• Hierarchical: Logically represented by an upside-down tree: each parent has many children and each child has only one parent.
• Network: Each record can have multiple parents, composed of sets and sets have two records: owner record and member record.
  • Member may have several owners.

Relational Database Model

• Perceived by user as a collection of tables for data storage, where a table is a series of row/column intersections and tables are related by sharing common entity characteristics.
• Tables are known as Relational Tables.

Relational Database Model, Advantages

• Structural independence
• Improved conceptual simplicity
• Easier database design, implementation, management, and use
• Ad hoc query capability with SQL
• Powerful database management system

Relational Database Model, Disadvantages

• Substantial hardware and system software overhead
• Conceptual simplicity allows untrained people to potentially generate poor designs
• Ease of development may promote 'islands of information' as departments 'go their own way'

Relational Database Terminology

• The EMPLOYEE relation: EMPLOYEE (emp_num, emp_Iname, emp_fname, emp_initial, emp_hiredate, job_code) is known as the "table name".
• Each row is known as a TUPLE
• Each column known as an ATTRIBUTE

Review

• Information is derived from data, which are usually stored in a database
• A DBMS is software that implements and manages a database
• Databases were developed to address the weaknesses of file systems
• A DBMS presents to the user a single data repository that promotes data sharing as well as enforces data integrity, eliminates redundancy and promotes data security