Relational Database Design: DBMS Concepts

• Course learning outcome involves designing relational database applications using data modeling techniques

Topics Covered

• Flaws in file system data management
• Database systems vs file system
• DBMS Architectures
• How a DBMS functions within the database system
• Common uses of database systems
• Meaning of the term database
• Meaning of Database Management System (DBMS)
• Components of the DBMS environment
• Typical functions of a DBMS
• Advantages/disadvantages of DBMSs

Database Applications Examples

• E-commerce purchases
• Credit card purchases
• Social media
• Banking systems
• Electronic health records
• Logistics and supply chain management
• Customer relationship management (CRM) systems
• Student information systems

Data vs. Information

• Data consists of raw, unprocessed facts and serves as the building blocks of information
• Information is processed data that reveals meaning
• Accurate, relevant, and timely information is key to good decision making; good decision making is essential for survival in a global environment
• Data becomes information when context is added with reference values like limits and correlated data

Evolution of DB Systems

• 1st Generation : Flat files (1960s - 1980s)
• 2nd Generation : Hierarchical and Network (1970s - 1990s)
• 3rd Generation : Relational (1980s - present)
• 4th Generation : Object-oriented, Object-relational, Data warehousing, Web-enabled (1990s - present)

Hierarchical DBMS

• Organizes data in a tree-like structure
• Supports one-to-many parent-child relationships
• Prevalent in large legacy systems

Network Databases

• Data is organized in a graph (lattice)
• A parent can have many children whereas a child can have many parents
• Bachmann diagrams are used
• Record types define properties
• Set types define relationships like parent-child using linked lists
• Querying is done by graph navigation
• CODASYL is an example

File-based Systems

• Features a collection of application programs that perform services for end users, such as generating reports
• Each program in a file-based system defines and manages its own data

Historical Context

• Managing data through file systems is now largely obsolete
• Understanding file systems helps to grasp the complexity of database design
• Identifying problems from file systems can prevent issues in DBMS

Manual File Systems

• Involves collection of file folders kept in a file cabinet
• Organization within folders was based on data's expected use
• Adequate for small amounts of data with few reporting requirements
• Finding and using data in growing collections became time-consuming

Conversion to Computer File System

• Complex technically, often needing DP specialists
• DP specialists created file structures, wrote software, and designed application programs
• This resulted in the creation of numerous “home-grown” systems
• Initially, computer files mirrored the design of manual files

Before Databases

• Information was stored in files where:
• Each field provides a single piece of information about a student
• Fields are separated by commas
• A record represents a collection of related fields
• Each record is represented as a separate line

Limitations of File-Based Approach

• Separation and Isolation: Programs maintain their own isolated data sets
• Duplication: The same data is held by different programs which leads to wasted space and possible inconsistency
• Data dependence file structure is defined in the program code
• Incompatible file formats programs are written in different languages and therefore cannot access each other's files
• Fixed Queries/Proliferation of application programs programs written to satisfy particular functions and require a new program for each new requirement
• Excessive Program Maintenance

Solution: Database Approach

• Involves a central repository of shared data
• Data is managed by a controlling agent
• It is stored in a standardized, convenient form

Database Defined

• A shared collection of logically related data and a description of this data designed to meet the information needs of an organization

Database Characteristics

• Persistent: Lasts a long time while only storing potentially relevant data thus having relevance of intended usage
• Inter-related: Consists of clusters of data about a topic like a student loan alongside with relationships that connect entities
• Shared: Has many users and uses, supporting hundreds/thousands of data entry screens and reports with many people simultaneously using it
• Meta-data provides a description of the data to enable data independence

University Database Entities

• Students
• Faculty
• Courses
• Offerings
• Enrollments

University Database Relationships

• Faculty teach offerings
• Students enroll in offerings
• Offerings made of courses

Water Utility Database Entities

• Customers
• Meters
• Bills
• Payments
• Meter readings

Water Utility Database Relationships

• Bills sent to customers
• Customers make payments
• Customers use meters

Database Applications

• Banking processes all transactions
• Airlines uses reservations, schedules database systems
• Universities use database systems to manage registration, grades
• Sales uses database systems to mange customers, products, purchases
• Manufacturing use DB to mange production, inventory, orders, supply chain
• Human resources use database systems to store employee records, salaries, tax deductions

Uses of Databases

• Transactional (or production): Supports day-to-day operations
• Data warehouse: Stores data used to generate information required to make tactical or strategic decisions with "data massaging", often used to store historical data, and has a quite different structure

Types of Databases based on Number of Users

• Single-user/Desktop: Supports only one user at a time
• Multi-user: Supports multiple users at the same time

Types of Databases based on Scope

• Workgroup: Supports as small group of users or single department
• Enterprise: Supports a large group of users or entire organization

Database Location

• Centralized Supports data located at a single site
• Distributed Supports data distributed across many sites while connected through network

DBMS Defined

• A software system that enables users to define, create, and maintain the database and that provides controlled access to this database.
• DBMS is a collection of programs that manages database structure and controls access to data
• DBMS makes data management more efficient and effective

DBMS Environment Components

• Hardware can range from a PC to a network of computers
• Software includes DBMS, operating system, network software, and application programs
• Data is used by the organization and has description called the schema
• Procedures refer to instructions and rules for the design and use of the database and DBMS
• People involve database designers, DBAs, application programmers, and end-users

Personnel

• Functional users: indirectly use the system
• Information system users: DBA, Analyst/Programmer/Management are some roles

Database Specialists roles

• Database administrator (DBA) - More technical with DBMS specific skills
• Data administrator - Less technical and more focused on planning

DBMS Data Functions

• Performs functions that guarantee integrity and consistency of data
• Manages data dictionary
• Manages data storage via entries, report defs etc
• Transforms & Presents data
• Security management enforces user security and data privacy within database
• Multi-user access control creates structures that allow multiple users to access the data
• Backup and recovery management provides backup and data recovery procedures
• Data integrity management promotes and enforces integrity rules to eliminate data integrity problems
• Data query language Lets user specify what must be done without having to specifically design what it is to be done.
• Database communication interfaces: Allow DB to accept end-user requests within a computer network environment

DBMS Effects

• End users have better access to better-managed data
• Operations are more integrated
• Data inconsistency reduces
• Possible to produce quick answers to ad hoc queries

DBMS Architectures

• Generally, a DBMS is divided into two parts
  • Client handles the main business and data processing logic and interfaces with the user
  • Server manages and controls access to the database
• Two-tier architecture is a client-server architecture

Three-Tier Architecture benefits

• The model has a client (user interface layer), application server (business logic and data processing layer) and database server (DBMS)

Advantages for Three-tier Design

• Thin client
• Simplified application maintenance
• Added modularity
• Easier load balancing

Cloud Database

• Typically a database that runs on a cloud computing platform, and access is provided as a service, for example the Amazon EC2 and Rackspace
• Conceived for the purpose of online data management by using a variety of distributed servers

Methods to run a database in Cloud

• Virtual machine image
• Database-as-a-service (DBaaS)

Virtual Machine Images for Cloud

• Cloud platforms let users buy virtual-machine instances for a limited time
• Users can upload their machine image with the database installed or use pre-installed ready-made machine images

Database-as-a-Service

• Application owners do not install and maintain the database themselves
• The service provider takes responsibility for installing and maintaining the database, and application owners are charged according to their usage of the service

Common Characteristics of DBaaS

• Most DB services offer web-based consoles
• DB services contain a DB-manager component, which controls the service API
• Service API is exposed to user, allows users to do maintenance and scaling operations on their DB instances
• Service providers are responsible for installing, patching, and updating the underlying software stack and ensuring the overall health and performance of the database
• All services commit to a certain level of high availability

DBaaS Benefits

• Cloud database systems makes sharing data simple and convenient
• Easy access to files and data
• A cloud database is cheaper than maintaining an actual dedicated server
• Virtual data storage enables manipulating data wherever, whenever

AI Usage For Databases

• Modern relational databases can integrate AI/ML extensions in databases
• PostgreSQL offers PL/Python to write Python-based AI/ML scripts to perform machine learning tasks
• Oracle provides machine learning capabilities with Oracle Machine Learning (OML)
• Microsoft SQL Servers supports R and Pyton for running machine earning models

Train Models Outside of DB and Deploy

• Train machine learning models using external tools (like TensorFlow, PyTorch, Scikit-learn) and deploy them to interact with the relational database
• Save the trained model as a serialized file, and load it into database via stored procedures or custom functions
• The model can be deployed as a REST API call and called from the database via triggers or external scripts