Database Concepts: Benefits and Hierarchy

• The following topics are excluded from this lesson: hierarchical model (pg 60), network model (pg 61), data-driven websites, distributed databases (pg 66-67), data marts, data lakes (pg 72), big data (section 3.9, pg 75-78), database marketing (pg 78-79), Tableau & PowerBI (pg 79-81).
• Big data (section 3.9, pg 75-78) is for self study.

Database Defined

• Databases are collections of related data, stored centrally or in multiple locations.
• Databases are often groups of files.
• Databases are a critical component of information systems.
• Consider a database like a filing cabinet.

Benefits of a Database

• Better handles data requests.
• Eliminates or minimizes data redundancy.
• Data and programs are independent.
• Improves data management.
• Variety of relationships among data maintained.
• Improved security.
• Reduces storage space.
• Generates more information from same data.

Data Hierarchy

• Data is structured and organized, involving fields, records, and files.
• Record: Group of related fields
• File: Group of related records

File Access Methods

• Sequential access: records are organized sequentially, used for backups and archives.
• Random access: records accessed in any order, fast for few record access.
• Indexed sequential access: combination, like a book index.

Data Models

• Data models determine how data is created, represented, organized, and maintained.
• Data models have 3 components: data structure, operations, and integrity rules.
• Data structure describes the organization of and relationships among records.
• Operations describe methods and calculations performed on data (e.g., updating and viewing).
• Integrity rules define database boundaries, such as min/max values.

Relational Model

• Relational models use 2-dimensional tables of rows and columns.
• Rows are records, columns are fields.
• Relational models require data to be structured in rows and columns unlike unstructured data with no predefined format.
• DLA 152 teaches how to automate turning unstructured data into structured.

Relational Database Model

• Defining each table and field within is required to design a relational database, thus defining the logical structure.
• A data dictionary stores collection of definitions.
• Field names are student name, admission date, age, major.
• Field data types are character (text), date, number.
• A default value is no major = undecided.
• A validation rule is student age cannot be negative.

Relational Database Model Tools

• Primary Keys uniquely identify each record in a relational database, like a student number.
• Foreign Keys are fields that match the primary key column of another table, used to cross-reference tables.
• Normalizing improves database efficiency by eliminating redundant data.

Database Management System (DBMS)

• DBMS is software for creating, storing, maintaining, and accessing database files.
• DBMS makes database use more efficient.
• Users make information requests with the DBMS.
• The DBMS returns info to the user, searches the database and retrieves the information.

Components of DBMS

• Database engine: the heart of DBMS software, responsible for data storage and retrieval.
• Data definition: creates and maintains the data dictionary; defines file structures in a database.
• Data manipulation: allows users to add, delete, modify, and retrieve records from a database.
• Application generation: designs application elements using a database, like data entry screens and interactive menus.
• Data administration: tasks include backup and recovery, security, and change management.

Object-Oriented Databases

• In object-oriented databases, both data and relationships are in a single object.
• Object-Oriented Programming (OOP) Concepts are utilized.
• A class is a blueprint that defines what objects can be created, what properties they have, and what methods they can perform.
• A object is a real instance of a class (a specific thing created from the blueprint).
• A property is a variable that belongs to an object and stores data about it.
• A value is the actual data assigned to a property of an object.
• A method is An action that an object can perform (a function inside the class).

OOP Concepts Illustrated Using a Database Storing Sims Characters & Their Actions

• All Sim characters have properties and skills.
• Each Sim character will have a name, age, mood and energy values.
• The actions (methods) the Sim can perform include eat, sleep, and walk.
• class SimCharacter: specifies properties, such as Name = "", Age = 0, Mood = "Happy", and Energy = 100.
• Methods (actions) such as Eat, Sleep, and Walk are defined.
• The Eat method increases energy by 20, Sleep sets energy to 100 and mood to "Refreshed", Walk decreases energy by 10.
• This does not create actual Sims, it is a blueprint.
• Creating Sims as objects can be done from the blueprint.
• Sim1 = SimCharacter(), Sim1.Name is set to “Alice”, Sim1.Age = 25, Sim1.Mood is "Tired".
• Now Alice and Bob exist as real Sims with unique values.
• A Sim "performing" actions is carried out by the methods.
• Methods update properties.
• Sim1.Eat(), Sim2.Sleep(), Sim1.Walk().

Key Takeaways for OOP Example

• Classes define what an object should have and can do.
• Objects are actual instances of a class like Alice and Bob.
• Properties store data like name, age, mood, and energy.
• Values are assigned to properties.
• Methods allow objects to perform actions like eat, sleep, and walk.

Data Warehouse

• A collection of data from different sources supports decision-making applications and generates business intelligence.
• Multidimensional data - "hypercubes" are stored.
• Data characteristics in a data warehouse include being subject-oriented, and integrated.
• It is subject oriented with focus on specific area, integrated with variety of sources, time variant categorized based on time.
• It is also categorized by type of data capturing aggregated data, purpose used for analytical purposes.

Components of a Data Warehouse

• Input: Data comes from external data sources, databases, transaction files, etc.
• Extraction, transformation, loading (ETL): Collecting, converting, and transferring data to meet data warehouse needs.
• Storage: Organized as raw data, summary data, and metadata.
• Collected information is organized.
• Raw data is info in original form
• Summary data is subtotals of various categories.
• Metadata tells users how, when, and by whom the data was collected and converted into its current form
• Output: Supports different types of analysis and generates reports for decision-making.
• Online analytical processing
• Data mining analysis
• Decision-making reports

Business analytics

• Using data and statistical methods to gain insight with predictive modelling.
• Business analytics is forward looking - focused on the future, not what happened in the past.

Types of Business Analytics

• Descriptive Business Analytics covers past events and how to prepare for future.
• Predictive Business Analytics predicts what might happen in future.
• Prescriptive Business Analytics recommends actions and shows outcomes of those decisions.
• Web analytics analyzes web data and effectiveness/efficiency of website.
• Mobile analytics analyzes mobile device usage, advertising, and in-app/performance.
• It analyses advertising and marketing to determine which ads led to app installs.
• It also analyses in-app usage and app performance.