Database Structures Quiz

Database Systems, Data Warehouses, and Data Marts

• Databases are collections of related data stored centrally or in multiple locations
• Data is organized hierarchically with files, records, and fields
• A Database Management System (DBMS) is software that creates, stores, maintains, and accesses database files, which improves efficiency
• DBMS facilitates communication between users and database files

Learning Outcomes

• Define a database and database management system (DBMS)
• Explain logical database design and the relational database model
• Define the components of a DBMS
• Summarize recent trends in database design and use
• Explain the components and functions of a data warehouse
• Describe the functions of a data mart
• Define business analytics, and describe its role in decision-making
• Explain big data and its business applications

Databases

• Databases are a collection of related data stored in a centralized or multiple locations
• Data hierarchy involves fields, records, and files
• DBMS software is used for creating, storing, maintaining, and accessing database files

Methods for Accessing Files

• Sequential access file structure: Records are organized and processed sequentially, often using numerical or primary key order. Ideal for backup & archive files.
• Random access file structure: Records can be accessed in any order irrespective of their physical location in the storage medium. Effective for quickly processing a small number of records.

Types of Data in a Database

• Internal data: Collected from within the organization and stored in internal databases
• External data: Comes from various resources and typically stored in a data warehouse

Methods for Accessing Files

• Indexed sequential access method (ISAM): Records are accessed sequentially or randomly, using indexing for efficiency. It's suitable for both small & large numbers of records.

Logical Database Design

• Physical view: Deals with how data is stored on storage devices (hard disks, magnetic tapes, CDs).

• Logical view: Focuses on how data is presented to users. It might have different presentations for different users.

• Data Models: Determine how data is created, represented, organized, and maintained. Models contain data structure, operations, and integrity rules

• Hierarchical Model: Data relationships form a tree-like structure. One-to-many relationships between records, e.g., a supplier has multiple product lines.

• Network Model: Similar to the hierarchical model but with multiple parent-child relationships.

• Relational Model: Data is organized in 2-dimensional tables with rows (records) and columns (fields).

• Primary Key is a unique identifier for each database record

• Foreign Key is a field in one table that matches the primary key of another table. This is fundamental for relational connections between tables.

• Normalization: Enhances database efficiency by organizing data. It also improves data quality by reducing redundancy. Multiple stages from First Normal Form (1NF) to Fifth Normal Form (5NF).

Relational Model

• Uses two-dimensional tables (rows, columns)
• Columns represent fields and contain data types
• A data dictionary stores field type details (default values and validation rules).
  • Retrieves data from tables using operations (Select, Project, Join, Intersection, Union, Difference)

Components of a DBMS

• Database engine
• Data definition
• Data manipulation
• Application generation
• Data administration

Database Engine

• Controls data storage, manipulation, and retrieval
• Converts user requests into physical (storage device) equivalents
• Interacts with other DBMS components, for seamless functioning

Data Definition

• Creates and maintains database dictionaries
• Defines file structures within a database
• Allows changes to database structures such as adding/deleting fields, adjusting field size & data types

Data Manipulation

• Used for adding, deleting, modifying, and retrieving records from a database
• Employ Structured Query Language (SQL)
• Supports query by example (QBE) to construct data requests

Application Generation

• Designs application elements (data entry screens, interactive menus) using databases
• Interfaces with programming languages

Data Administration

• Handles backup & recovery, security, and change management tasks
• Defines which users have permission to perform database updates and queries (e.g., CRUD operations)

Data Warehouses

• Collection of data from various sources
• Supports decision-making applications and business intelligence
• Stores multidimensional data (hypercubes).
• Extracts, Transforms, Loads (ETL) processes are used

Data Marts

• Smaller version of data warehouses, used by a single department or function
• Easier to create and faster data access, less expensive
• Targeted towards a particular user group
• Scope limited compared to full data warehouses

Business Analytics

• Uses data and statistical methods to gain insight into data
• Provides decision-makers with information to act upon

Types of Business Analytics Methods

• Descriptive: Reviews past events; Analyzes the data. Provides reports indicating what happened during a specific time period and how to prepare for the future.
• Proactive/Reactive: Prepares decision-makers for future events

Big Data

• Huge amounts of data that traditional computing methods struggle with
• Characterized by 3 Vs : Volume, Variety, Velocity

Who Benefits from Big Data?

• Various industries like retail, financial services, advertising, government, manufacturing, media, energy, and healthcare.

Factors Influencing Big Data Popularity

• Mobile technology
• Social networks
• Smartphone advancements

Key Terms

• Key terms from the document, such as allocation, big data, business analytics, creating, reading, updating, and deleting (CRUD).

Characteristics of Data in a Data Warehouse

• Data is subject-oriented: Subject matters are focused on.
• Data comes from diverse sources.
• Data is organized by time.
• Data captures aggregated data.
• Data is used for analytical purposes.