Data Warehousing Concepts Quiz

Database Systems, Data Warehouses, and Data Marts

• Databases are collections of related data stored centrally or in multiple locations.
• Data hierarchy structures and organizes fields, records, and files.
• DBMS (Database Management System) software manages, creates, maintains, and accesses database files, making database use more efficient.
• A user requests information.
• DBMS searches the database.
• DBMS retrieves the requested information and returns it to the user.
• The sequential access file structure organizes and processes records numerically or sequentially.
• It is based on a primary key, like Social Security numbers or account numbers.
• Sequential access is often used for backup and archive files.
• Internal data is collected within an organization.
• It is stored within the organization's databases.
• External data comes from various sources.
• External data is stored in a data warehouse.
• Random access file structure allows data access in any order regardless of physical location in storage media.
• Random access is effective when a limited number of records need daily or weekly processing.
• Indexed sequential access method (ISAM): Records are sequentially or randomly accessed based on the number being accessed.
• The indexed sequential access (ISAM) method utilizes an index to quickly locate records in a large number of instances.

Logical Database Design

• A physical view describes how data is stored on media such as hard disks, magnetic tapes, or CDs.
• A logical view describes how information appears to users and how information is organized and retrieved.
• A database model defines how data is created, represented, organized, and maintained.
• Data models consist of data structure, operations, and integrity rules.
• A hierarchical model organizes file relationships in a tree-like fashion.

Network Model

• A network model resembles a hierarchical structure but arranges records differently.
• Multiple parent and child records are incorporated into the model.

Relational Model

• A relational model uses two-dimensional tables, where rows are records and columns are fields.
• Data dictionary definitions store data types, default values, and validation rules for fields.
• A primary key uniquely identifies each record.
• A foreign key links records in separate tables.
• Normalization improves database efficiency by removing redundant data and ensures that only related data exists within each table.
• Normalization can be classified into various forms ranging from 1NF to 5NF.
• Relational Operations to combine data include select, project, join, intersection, union, and difference

Components of a DBMS

• Database engine manages data storage, manipulation, and retrieval.
• Database engine translates logical requests from users to physical equivalents.
• Database definition maintains the data dictionary and defines files' structure within the database and manages its structure changes (e.g., adding, deleting, changing field sizes).
• Data manipulation adds, deletes, modifies, and retrieves records within the database using structured query language (SQL) or Query-by-Example (QBE).
• Application generation designs application elements such as data entry screens, interactive menus, and interfaces with other programming languages.
• Data administration is used for backup and recovery tasks, security management, and user permission management for database CRUD operations.

Recent Trends in Database Design and Use

• Data-driven websites provide interfaces to databases and retrieve and allow data entry for users, thus improving access to information and providing more current information from various data sources.
• Distributed databases store data across multiple servers throughout an organization.
• Approaches to setting up distributed databases include fragmentation, replication, and allocation to organize and divide data storage locations.
• Objects within object-oriented DBMS contain data and relationships with their associated attributes and methods, creating a more organized structure. Data attributes and methods are encapsulated into a single unit. Encapsulation allows creation of new objects that can be modified and quickly.

Data Warehouses

• Data warehouses collect data from various sources used for businesses intelligence, supports decision-making applications, and are called hypercubes due to storing multidimensional data.
• Characteristics of data in a data warehouse include subject-orientation, origin from diverse sources, categorization by time, data aggregation, and use for analytics.

Input/Output Data Warehouses

• External data sources, Databases, Transaction files, ERP, and CRM systems feed input into the data warehouse.
• Output data generated using the data warehouse includes online transaction processing (OLTP) that facilitates transaction-oriented applications and online analytical processing (OLAP) to generate business intelligence by performing multidimensional analyses.

Data Mart

• Data marts are smaller data warehouse versions used by specific departments or functions for faster access and improved response time.

Business Analytics (BA)

• BA uses data and statistical methods to gain data insights for decision-making.
• BA methods such as descriptive analytics review past events and analyze data to determine what occurred during a specified time period to prepare for the future.
• A proactive strategy prepares decision-makers for future events.

Big Data Era

• Big data involves massive amounts of data, which conventional methods cannot efficiently manage.
• Big data is characterized by the "three V's": Volume, Variety, and Velocity.
• Industries including Retail, Financial Services, Advertising, Public Relations, Government, Manufacturing, Media and Telecommunication, Energy, and Healthcare benefit from big data analysis.

Factors in the Growth and Popularity of Big Data

• Factors in the growth and popularity of big data include advanced mobile and wireless technologies, popular social networks, and improvements in smartphone and handheld device technologies.

Summary

• Database systems integrate files for quicker data retrieval. Data access methods include sequential, random, and indexed sequential access.
• Components of DBMS include the database engine, data definition, data manipulation, application generation, and data administration.
• Recent trends in databases utilize data-driven websites, natural language processing, distributed databases, and object-oriented databases
• Data marts are focused on business functions for specific user groups within an organization.
• Industries benefit from detailed big data analytics and gain a strong competitive advantage.

Test your knowledge on the benefits and characteristics of data warehouses. This quiz covers topics such as data mining, file structures, and data models. Enhance your understanding of how data warehouses support decision-making processes.