BTMA 331 01 - database environments and ERDs

Database environments and ERDs

Limitations of file processing systems

• Program-data dependence

• Duplication of data

• Limited data sharing

• Lengthy development times

• Excessive program maintenance

Program data dependence

File descriptions are stored within each program, so any change to a file structure requires changes to all programs that access it.

Duplication of data

Applications developed independently often result in duplicate data files, which can lead to inconsistent formats and values.

Limited data sharing

 Each application has its own private files, limiting cross-department access.

Lengthy development times

Developers must create new file formats and logic from scratch for each new application

Excessive program maintenance

As much as 80% of development budgets could be used on maintenance in such systems​.

what is the solution to these limitations of file processing systems?

DBMS, database management system

DBMS

A software system that is used to create, maintain, and provide controlled access to user databases

Architecture of database environment

1. End users use application programs to interact with the database.

2. System developers build these programs and interact with the user interface and DBMS.

3. DBAs use modeling tools to define data structure and rules, stored in the Repository.

4. The DBMS acts as the mediator, ensuring all users and applications access the Database properly and securely.

5. Metadata stored in the Repository guides how data is stored, accessed, and validated.

repository

centralized storehouse of metadata

Metadata are defined as data that

 describe the properties or characteristics of end-user data and the context of that data

Metadata includes

names, definitions, length or size, allowable values, data source, storage location, ownership, and usage

Metadata helps users understand

data meaning and distinguish between similar items

Typical functions of a DBMS

• Data Storage Management

• Data Manipulation Management

• Data Definition Services

• Data Dictionary/System Catalog Management

• Database Communication Interfaces

• Authorization / Security Management

• Backup and Recovery Management

• Concurrency Control Service

• Transaction Management

• Database Access and Application Programming Interfaces

Data Storage Management

It provides a mechanism for management of permanent storage of the data.

The internal schema defines how the data should be stored by the storage

management mechanism and the storage manager interfaces with

the operating system to access the physical storage.

Data manipulation management

A DBMS furnishes users with the ability to retrieve, update and delete existing

data in the database.

Data definition services

The DBMS accepts the data definitions such as external schema, the

conceptual schema, the internal schema, and all the associated mappings in

source form

Data dictionary/system catalog management

The DBMS provides a data dictionary or system catalog function in which

descriptions of data items are stored and which is accessible to users.

Database communication interfaces

The end-user's requests for database access are transmitted to DBMS in the

form of communication messages.

Authorization/security management

The DBMS protects the database against unauthorized access, either

international or accidental. It furnishes mechanism to ensure that only

authorized users an access the database.

Backup and recovery management

The DBMS provides mechanisms for backing up data periodically and recovering

from different types of failures.

Concurrency control service

Since DBMSs support sharing of data among multiple users, they must provide a mechanism for managing concurrent access to the database. DBMSs ensure that the database kept in consistent state and that integrity of the data is preserved.

Transaction management

A transaction is a series of database operations, carried out by a single user or application program, which accesses or changes the contents of the database. Therefore, a DBMS must provide a mechanism to ensure either that all the updates corresponding to a given transaction are made or that none of them is made.

Database access and application programming interfaces

All DBMS provide interface to enable applications to use DBMS services. They provide data access via

Structured Query Language (SQL). The DBMS query language contains two components: (a) a Data

Definition Language (DDL) and (b) a Data Manipulation Language (DML)

Schema

description of the overall structure of the database, including tables, fields, relationships OR a logical container or namespace within a database that holds and organizes database objects such as tables, views, stored procedures, and functions.

examples of schemas

• Text statements listing relations and attributes.

• Graphical representations using rectangles and fields​.

why do we need three schema architecture?

promotes data independence, security, and flexibility in database management systems. By separating the data into three levels, it allows for easier adaptation to changing user needs, technology advancements, and security requirements.

three schema architecture composed of?

external, conceptual, and internal schema

external schema

User-specific views of the data

conceptual schema

A comprehensive enterprise-wide view of the data

internal schema: made up of logical and physical schema

logical schema: Representation for a specific DBMS type (e.g., relational)

physical schema: How data are stored physically on storage devices​

degree of a relationship refers to

how many entity types participate in that relationship

unary degree

A relationship involving only one entity type. For example, an employee might supervise other employees.

binary (degree 2)

The most common relationship, involving two entity types. For example, a Customer places an Order.

ternary (degree 3)

A relationship involving three entity types. For example, a Supplier provides Parts to a Customer.

cardinality constraints define how many

instances of one entity type can be related to an instance of another entity type. These constraints help specify how the data is connected and can define the minimum and maximum number of relationships possible.

cardinality: mandatory one

An entity must be associated with exactly one instance of another entity.

cardinality: mandatory many

An entity must be associated with one or more instances of another entity.

cardinality: optional one

An entity may or may not be associated with an instance of another entity.

cardinality: optional many

An entity may or may not be associated with one or more instances of another entity.

attribute

An attribute is a property or characteristic of an entity or a relationship. It provides additional information about the entity.

composite attribute

can be broken down into smaller, more detailed components. For example, the attribute Full Name could be split into First Name, Middle Name, and Last Name.

multivalued attribute

can have multiple values for a single instance of an entity. For example, a person can have multiple phone numbers, so the attribute Phone Number might be a multivalued attribute for an Employee.

derived attribute

an attribute whose value can be calculated from other existing attributes. For example, an employee's Average Salary can be calculated from their salary history or individual salary values.

Multivalued attributes or separate entity instances can be used to

represent time-dependent data, with each instance marked by a time stamp.

Multivalued attributes are shown as

repeating groups in tables and need to be broken down during normalization.

Composite attributes are

not atomic and should be split into simpler parts.

Derived attributes are

calculated from other attributes (e.g., age from birthdate).