Understanding Databases: Key Concepts

Importance of Databases

• Vital for Internet Web applications and smartphone apps
• Enable tracking of data of interest

Characteristics of Databases

• Tables store data in rows (records) and columns (fields)
• Multiple tables can exist in a database, each storing distinct data
• Each row holds data about an occurrence or instance
• Data and relationships are stored
• Referred to as Big Data when enormous datasets are present

Table and Column Naming

• Table names are written in ALL CAPS
• Column names start with an initial capital letter
• Compound names have a capital letter on each word

Key Concepts for Data and Relationships

• Primary key: Identifies each table row
• Surrogate key: Automatically assigned by the database for use as primary key
• Composite key: Multiple columns are used for a primary key
• Foreign key: Links between two tables, which creates relationships

Databases and Information

• Data refers to the the recorded facts and figures
• Information is defined as knowledge derived from data
• Meaningful information presented in context
• Processed through summing, ordering, averaging, computing, or similar operations
• Databases facilitate the creation of information from data

Common Database Applications

• Sales Contact Manager tracks customer activity
• Patient appointment systems organize medical schedules
• Customer Relationship Management (CRM) manages customer contacts
• Enterprise Resource Planning (ERP) include all departments within an organization
• E-commerce companies use web data for tailored marketing
• Extract insights from data

Components of a Database System

• Users
• Database application
• Database management system (DBMS)
• Database itself
• Structured query language (SQL) is an internationally recognized standard
• The Structured query language (SQL) creates, processes and administers

Functions of Application Programs

• Creating and processing forms
• Processing user queries
• Generating reports
• Executing application logic
• Application control

Functions of a DBMS

• Creating databases and tables
• Establishing supporting structures like indexes
• Modifying, reading and maintaining database data
• Defining and enforcing rules
• Controlling concurrency
• Performing backup and recovery

Database Definition

• A self-describing collection of integrated tables
• Tables are integrated because they store data about relationships between rows
• Self-describing due to storing its own description
• Metadata defines data about data

Microsoft Access

• A personal database system comprised of DBMS and application generator
• Intended for individual users and small workgroups

Key Features of Microsoft (MS) Access

• Database Design capabilities for tables, forms, reports, and queries
• Data management for storing and manipulating data
• Querying and Reporting tools to extract, analyze, and present data
• Integration with other Microsoft Office Applications

Advantages of MS Access

• Easy-to-use interface for users without extensive database experience
• Cost-effective through inclusion in the Microsoft Office suite
• Customizable databases and applications

Disadvantages of MS Access

• Limited scalability for small to medium-sized databases
• Potential security vulnerabilities if not configured and protected correctly
• Instability when multiple users access and modify the database simultaneously

Components of an Enterprise-Class Database System

• Applications on corporate network (client/server)
• E-commerce applications
• Web portal with reporting
• DBMS
• XML Web Services applications
• Mobile apps

Three Types of Database Design

• From existing data
• New systems development
• Database redesign

History of Databases

• Before 1970, data was stored in separate files with limited integration
• Early databases in the 1970s provided related tables
• Relational models emerged in 1978-1985
• Microcomputer DBMS products became available in the 1980s and 1990s
• Data warehouses emerged to enable data analysis and BI
• Relational databases all built on relational database model designed by IBM Engineer E. F. Codd in 1970

Database Management Systems Developments

• Object-Oriented Programming (OOP) emerged, many products are developed based on it
• Data warehouses store amounts of information and used for online analytical processing (OLAP)
• Hypertext Transfer Protocol (HTTP) is used to connect Web pages over the Internet
• Application Programming Interface (API) is used by Web pages in programming languages to connect to a DBMS
• Web Database Application includes web-based interference with use of API
• eXtensible Markup Language (XML) was designed for problems that occur when HTML is used in business transactions
• NoSQL (“Not only SQL) refers to databases not using the relational model

Objects and Classes

• Object is an abstract representation of a rel-world entity with unique identity
• Class is a similar collection of objects with shared structure(attributes and behaviours)
• Relational databases similar to a Rolodex for names numbers and addresses

Popular Relational Database Systems

• MySQL is the most popular open source system
• Oracle, is object oriented written in C++
• Microsoft (MS) SQL Server for enterprise levels
• Microsoft Azure is a cloud computing platform
• PostgreSQL uses procedural language

Key Concepts: SQL vs NoSQL

• SQL programming language for interaction with relational databases
• Designed to create and modify structure
• NoSQL, class of DBMS that generally does not use SQL, is not faster than SQL
• MongoDB the most popular well supported NoSQL and extremely simple to install and implement