Database Applications

Database Applications

• Enterprise information includes sales, accounting, and human resources.
• Sales tracks customers, products, and purchases.
• Accounting manages payments, receipts, and assets.
• Human Resources handles employee information, salaries, and taxes.
• Manufacturing manages production, inventory, orders, and supply chains.
• Banking and finance manages customer information, accounts, loans, and transactions.
• Banking and finance also deals with credit card transactions.
• Finance handles sales and purchases of financial instruments as well as storing real time market data
• Universities handle registration and manage grades.
• Airlines manage reservations and schedules.
• Telecommunication companies record calls, texts, and data usage.
• Telecommunication companies use records to generate monthly bills and maintain prepaid balances.
• Web-based services are used for order tracking, personalized recommendations, and online advertisements.
• Document databases are useful for web based services
• Navigation systems maintain location data for points of interest and routes for various transport.

Early Database Applications and Issues

• Early applications built on file systems led to data redundancy and inconsistency.
• Data redundancy and inconsistency happened because data was stored in multiple file formats.
• Accessing data was difficult and required new programs for each task.
• Multiple files and formats lead to data isolation.
• Integrity was compromised as constraints were embedded in program code rather than explicitly stated.
• Updating data was not atomic, meaning failures could lead to inconsistent database states of partial updates.
• An example of failures is a transfer of funds from one account to another should either complete or not happen at all
• Concurrent access by multiple users risked data inconsistencies.
• An exmaple of concurrent access is two people reading a balance (say 100) and withdrawing money (say 50 each) at the same time
• Security was challenging as user access controls were difficult to implement.
• Database systems provide solutions to the problems caused by file systems.

Data Models Explained

• A data model is a set of tools used for describing data, relationships, constraints, and semantics.
• The relational model is a way to structure data.
• The Entity-Relationship model is mainly used for database design.
• Object-based data models cover object-oriented and object-relational approaches.
• Semi-structured data, like XML, is another data model.
• Older models include network and hierarchical models.

Relational Model Details

• Data is organized into tables in the relational model.
• Tables have columns and rows.

Database Architecture

• The view level, logical level, and physical level are parts of a database system's architecture.

Schemas and Instances

• Schemas are the logical structure of database including types and variables.
• An example of Schemas is database consists of a set of customers and accounts in a bank and the relationship between them which is analogous to type information .
• Physical schemas represent physical structure.
• Instance represents the specific content of database at a certain point in time.
• An Instance is analogous to the value of a variable.

Physical Data Independence

• Physical Data Independence enables modifications to the physical schema without altering the logical schema.
• Applications depend on the logical schema for data access.
• Interfaces between levels should be well-defined to minimize the impact of changes.

Data Definition Language (DDL)

• DDL is a notation to define the database schema.
• A DDL compiler creates table templates stored in a data dictionary.
• Data dictionaries contain metadata.
• Metadata includes database schema, integrity constraints, primary keys, and authorization details.

Data Manipulation Language (DML)

• DML is used to access and update data within a data model.
• DML is known as a query language.
• Procedural DML requires users to specify what data is needed and how to get it.
• Declarative DML requires users to specify what data is needed without specifying how to get it.
• Declarative DMLs are easier to use compared to procedural DMLs
• Non-procedural DMLs are referred to as Declarative DMLs
• A query language is the portion of DML for data retrieval.

SQL and Database Access

• SQL is a nonprocedural query language that operates on tables.
• SQL cannot compute complex functions, and is usually embedded in some higher-level language to do so
• Programs can access databases via language extensions or application program interfaces like ODBC/JDBC.
• The universal Turing machine is more powerful than non-procedural query languages like SQL.
• SQL doesn't support actions like user input, output to displays, or network communication directly.
• Host languages such as C/C++, Java, or Python is where Computations and actions must be written with embedded SQL queries.
• Application programs interact with the database in this setup.

Database Design

• Database design involves deciding on database schema, is reffered to as logical design.
• Database also requires that we find a "good" collection of relation schemas.
• A business decision is what attributes to record in a database.
• A computer science decision is what relation schemas to have and how to distribute attributes.
• Physical design involves determining physical layout.

Database Engine Components

• The database system is divided into modules with specific responsibilities.
• Key components are the storage manager, query processor, and transaction management component.

Storage Manager Details

• The storage manager interfaces between low-level data storage and application programs/queries.
• Interacting with the OS file manager is a task of the storage manager.
• Efficient storing, retrieving and updating of data is a task of the Storage manager.
• It's components include an authorization and integrity manager, transaction manager, file manager, and buffer manager.

Storage Manager Data Structures

• The storage manager implements data structures as part of physical system implementation.
• The storage manager manages store the database.
• The storage manager mananges the data dictionary, which stores metadata about the database, specifically the schema.
• Indices enables fast access to data by providing pointers to data items that hold a particular value.

Query Processor Components

• The DDL interpreter interprets DDL statements and stores definitions in the data dictionary.
• The DML compiler translates DML statements into a low-level evaluation plan.
• The DML compiler performs query optimization picking the lowest cost evaluation plan available.
• The query evaluation engine executes low-level instructions from the DML compiler.

Query Processing Steps

• Query processing involves parsing, translation, optimization, and evaluation.

Transaction Management

• A transaction is a set of operations performing a single logical function in a database application.
• The transaction-management component ensures the database remains consistent despite system failures.
• The concurrency-control manager manages concurrent transactions to ensure database consistency.

Database Architecture Types

• Centralized databases have one to a few cores and shared memory.
• Client-server databases have one server machine serving multiple clients.
• Parallel databases use shared memory, shared disk, or shared nothing architectures.
• Distributed databases feature geographical distribution and schema/data heterogeneity.

Database Application Architectures

• Two-tier architecture involves the application residing at the client machine and invoking database functions at the server.
• Three-tier architecture involves a client front-end and an application server communicating with the database.
• The client end communicates with an application server, usually through a forms interface.
• The application server in turn communicates with a database system to access data.

Database Users

• Application programmers, naive users, sophisticated users, and database administrators are all different types of database users.

Database Administrator (DBA)

• A DBA has central control over the database system.
• The DBA defines schema, storage structure, and access methods.
• The DBA modifies schema and physical organization.
• The DBA grants authorization for data access.
• The DBA performs routine maintenance, including backup and disk space management.
• The DBA monitors jobs running on the database.

History of Database Systems

• In the 1950s-60s, magnetic tapes were used for storage, offering only sequential access and used punched cards for input.
• The late 1960s-70s saw the introduction of hard disks allowing direct access to data.
• During the late 1960s-70s network and hierarchical data models gained widespread use.
• Ted Codd defined the relational data model.
• With the relational data model, Ted Codd went on to win the ACM Turing Award.
• In the late 1960s-70s IBM Research began System R prototype.
• In the late 1960s-70s UC Berkeley (Michael Stonebraker) began the Ingres prototype
• In the late 1960s-70s Oracle releases first commercial relational database.
• High-performance transaction processing was a focus in the late 1960s-70s was a focus.
• The 1980s involved research relational prototypes evolving into commercial systems.
• SQL became an industrial standard in the 1980s
• Parallel and distributed database systems were developed by Wisconsin, IBM, Teradata in the 1980s.
• Object-oriented database systems were created in the 1980s
• The 1990s was the era of large decision support and data-mining applications.
• The 1990s saw large multi-terabyte data warehouses emerge.
• The 1990s saw Emergence of Web commerce.
• The 2000s was the big data storage systems era.
• Big data storage systems such as Google BigTable, Yahoo PNuts, and Amazon were created in the 2000s.
• "NoSQL" systems were created in the 2000s.
• Big data analysis went beyond SQL in the 2000s.
• Map reduce and friends were used in the 2000s.
• SQL was reloaded in the 2010s
• SQL front end to Map Reduce systems were created in the 2010s
• Massively parallel database systems were created in the 2010s
• Multi-core main-memory databases were created in the 2010s