Database Concepts and Relational Databases

Database Concepts

• A database is a collection of related files holding records about people, locations, or things.
• An entity represents a generalized category like a supplier or part.
• Attributes are specific characteristics of each entity, such as supplier name, address, or part description.

Databases and Transaction Processing

• Companies leverage the web to make information from internal databases accessible to customers and partners.
• Middleware facilitates this connection through web servers, application servers, and database servers.
• Web interfaces provide user familiarity and reduce the need to redesign legacy systems.

Relational Databases

• Relational databases organize data into two-dimensional tables (relations) with columns and rows.
• A separate table is created for each entity, such as CUSTOMER, SUPPLIER, PART, and SALES.
• Fields (columns) within tables store data representing attributes.
• Rows store data for individual records, also known as tuples.
• A key field uniquely identifies each record, with the primary key being the most important.

Establishing Relationships

• Entity-relationship diagrams visualize the relationships between tables in a relational database.
• Referential integrity rules ensure consistent relationships between linked tables.

Database Management Systems (DBMS)

• DBMS is software designed to create, store, organize, and access data within a database.
• It separates the logical view (how end users see data) from the physical view (how data is structured and organized).
• Examples include Microsoft Access, DB2, Oracle Database, Microsoft SQL Server, and MySQL.

Operations of a Relational DBMS

• Select: Creates a subset of records based on specific criteria.
• Join: Combines tables to produce more comprehensive information than is available from individual tables.
• Project: Creates a new table containing only selected columns from an existing table.

Big Data

• Big data refers to massive amounts of unstructured and semi-structured data often collected from the internet and other sources.
• It's characterized by its volume, variety, and velocity.
• Big data sets offer more patterns and insights than smaller datasets, including customer behavior and weather patterns.
• New technologies and tools are needed to manage and analyze big data.

Non-Relational Databases

• NoSQL databases handle large datasets that are not easily structured into tables and columns.
• They use a more flexible data model and don't require as much structuring.
• They can manage unstructured data, such as social media content and graphics.
• Examples include Amazon's SimpleDB and MetLife's MongoDB.

Cloud Databases and Distributed Databases

• Cloud databases are relational database engines offered by cloud computing services, with pricing based on usage.
• They appeal to small and medium-sized businesses.
• Amazon Relational Database Service (RDS) offers engines like MySQL, Microsoft SQL Server, and Oracle Database.
• Distributed databases are stored across multiple physical locations, like Google's Spanner cloud service.

Blockchain

• A blockchain is a distributed database of transactions that operates on a network without central authority.
• It keeps a growing list of records called blocks.
• Once recorded, blocks are immutable (cannot be changed), providing high security and reducing transaction processing costs.

Business Intelligence Infrastructure

• A set of tools to obtain useful information from internal and external systems and big data, including:
  • Data warehouses: store current and historical data for decision-making.
  • Data marts: subsets of data warehouses focused on specific users.
  • Hadoop: an open-source framework for big data processing.
  • In-memory computing: processes data directly in RAM for faster analysis.
  • Analytical platforms: tools for data consolidation, analysis, and decision-making.

Data Warehouses

• Data warehouses consolidate and standardize data from multiple sources, both operational and transactional databases.
• Data can be accessed but not altered.
• Data marts are focused subsets of data warehouses tailored for specific user groups.

Hadoop

• Hadoop is a free software framework that handles large datasets by breaking down tasks into smaller sub-problems and distributing the processing to numerous inexpensive computers.
• It ultimately combines results into a smaller, easier-to-analyze dataset.
• Key services include Hadoop Distributed File System (HDFS) and MapReduce.

Analytical Tools

• Analytical tools help consolidate, analyze, and use insights from data to improve decision-making. They include:
  • Querying and reporting software.
  • Online Analytical Processing (OLAP) for multidimensional data analysis.
  • Data mining for discovering hidden patterns and relationships.

Online Analytical Processing (OLAP)

• OLAP allows users to view the same data from multiple perspectives by defining dimensions for each aspect of the information, such as product, price, cost, region, or time period.
• It enables users to quickly get answers to ad-hoc questions.

Data Mining

• Data mining unearths hidden patterns and relationships in large databases and infers rules to predict future behavior.
• It identifies various types of information, including:
  • Associations: Links between occurrences, such as system changes related to COVID-19.
  • Sequences: Events linked across time.
  • Classifications: Patterns describing groups an item belongs to.
  • Clustering: Discovering unclassified groupings.
  • Forecasting: Using historical data to predict future values.

Text Mining

• Text mining extracts key elements, discovers patterns, and summarizes large, unstructured datasets, primarily text files.
• Sentiment analysis mines text comments to measure customer sentiment.

Web Mining

• Web mining analyzes the web for useful patterns and information.
• E.g., identifying website visitors, popular products, and trends.

Explore the fundamental concepts of databases, entities, and attributes. This quiz covers relational databases and how they organize data into tables for effective transaction processing and information accessibility. Test your knowledge of middleware and web interfaces related to databases.