Database Concepts L2
Learning Outcomes and Fundamentals of Data Modeling
Data modeling is the first step in the database design journey, serving as a critical bridge between real-world objects and the computer database.
A model is defined as an abstraction of a real-world object or event.
Data models are simple representations of complex real-world data structures, useful for supporting a specific problem domain.
Data modeling is an iterative and progressive process used to create a specific data model for a determined problem domain.
Learning objectives for this topic include:
Understanding why data models are important.
Identifying basic data-modeling building blocks.
Defining business rules and their influence on database design.
Exploring emerging alternative data models and their purposes.
Tracing the evolution of major data models.
Classifying data models by their level of abstraction.
The Importance of Data Models
Data models serve as a communication tool between database designers, programmers, and end-users.
Different stakeholders (designers, programmers, end-users) often see data in different ways, which can lead to:
Database designs that do not reflect the actual operations of an organization.
Failures in meeting end-user needs.
Issues with data efficiency requirements.
Communication must be frequent and clear to avoid these failures.
Data modeling clarifies communication by reducing complexities to easily understood abstractions that define entities, relations, and data transformations.
Key benefits include:
Providing an overall view of the database.
Organizing data for various users.
Acting as an abstraction for the creation of high-quality databases.
Basic Data Modeling Building Blocks
Entity: A unique and distinct object used to collect and store data.
Attribute: A specific characteristic of an entity.
Relationship: An association among entities. There are three primary types:
One-to-many ().
Many-to-many ( or ).
One-to-one ().
Constraint: A set of rules applied to the data to ensure data integrity.
Naming Conventions for Entities and Attributes
Entity Names:
Must be descriptive of the objects within the business environment.
Should use terminology that is familiar to the users.
Attribute Names:
Must be descriptive of the data they represent.
Benefits of Proper Naming:
Facilitates communication between all involved parties.
Promotes self-documentation of the database structure.
Business Rules
Definition: A brief, precise, and unambiguous description of a policy, procedure, or principle within an organization.
Functions:
Enable the definition of the basic building blocks of the data model.
Describe the main and distinguishing characteristics of the data.
Sources of Business Rules:
Company managers.
Policy makers.
Department managers.
Written documentation (e.g., procedure manuals).
Direct interviews with end-users.
Importance for Designers:
Helps understand the nature, role, and scope of data and business processes.
Facilitates the development of appropriate relationship participation rules and constraints.
Ensures the creation of an accurate data model.
Acts as a standardization tool for the company's view of data.
Translating Business Rules into Data Model Components
Nouns in a business rule typically translate into entities.
Verbs translate into relationships among entities.
Relationships are bidirectional and can be identified by asking specific questions:
Question 1: How many instances of are related to one instance of ?
Question 2: How many instances of are related to one instance of ?
Standard Database Concepts and Languages
Schema: The conceptual organization of the entire database as viewed by the database administrator.
Subschema: The portion of the database seen by specific application programs producing information from the database.
Data Manipulation Language (DML): The environment in which data can be managed and is used to work with data in the database.
Schema Data Definition Language (DDL): Enables the database administrator to define the various schema components.
The Evolution of Data Models
Hierarchical Model (s):
Developed to manage large amounts of data for complex manufacturing projects.
Represented by an upside-down tree containing segments (equivalent to a file system's record type).
Depicts only one-to-many () relationships.
Limitations: Difficult to represent relationships, structural level dependency, no ad hoc queries (access is record-at-a-time), and access paths are predefined (navigational access).
Network Model ():
Represent complex data relationships more effectively.
Improved database performance and established standards.
Depicts both and relationships.
Relational Model ():
Introduced by E.F. Codd; based on the concept of a "relation."
Relation/Table: A matrix composed of intersecting tuples (rows) and attributes (columns).
Allows for ad hoc queries via SQL (Structured Query Language).
Provides set-oriented access and structural independence.
Entity Relationship (ER) Model ():
A graphical representation of entities and their relationships within a database structure.
Entity Relationship Diagram (ERD): Uses symbols to model components.
Connectivity: Terms used to label relationship types.
Notations include Chen, Crow's Foot, and UML Class Diagram notations.
Semantic and Extended Relational Models (–):
Aimed for more semantics in the data model.
The birth of the internet significantly influenced these shifts.
Object-Oriented (OO) Model (s):
Contains data and relationships along with the operations performed on them.
Class: A collection of similar objects with shared structure and behavior, organized in a class hierarchy.
Inheritance: An object inherits methods and attributes from its parent class.
UML (Unified Modeling Language): Use of symbols to graphically model a system.
NoSQL and Big Data ():
Designed to address Big Data problems.
Schema-less key-value data models.
High performance and scalability for large, sparse data stores.
Big Data and NoSQL Databases
Big Data Characteristics (The Vs):
Volume: Huge amounts of web and sensor-generated data that conventional structures cannot handle.
Velocity: The speed at which data is generated and processed.
Variety: Different types of data formats.
Big Data Challenges:
Volume makes conventional structures unusable.
Sparse and unstructured data are inconsistent with expensive OLAP tools.
Key Technologies:
Hadoop.
Hadoop Distributed File System (HDFS).
MapReduce.
NoSQL.
NoSQL Features:
Not based on the relational model.
Support distributed database architectures.
High scalability, high availability, and fault tolerance.
Geared toward performance rather than strict transaction consistency.
Data stored in key-value stores (Key = Attribute identifier, Value = Attribute value/data).
Degrees of Abstraction and the ANSI/SPARC Architecture
Data Abstraction: The concept that database design starts at a high-level view and detail increases as implementation approaches.
The ANSI/SPARC Framework defines four main levels of abstraction:
External Model:
The end-user view of the data environment.
Represented by an external schema (specific subset of the database).
High degree of abstraction.
Independent of both hardware and software.
Conceptual Model:
A global view of the entire database as seen by the organization (Designer's View).
Represented by a conceptual schema.
Uses macro-level view of data environment.
Software and hardware independent.
Logical Design: The task of creating a conceptual data model.
Internal Model:
The database as seen by the DBMS (Database Management System).
Internal Schema: Specific representation using database constructs from the chosen software.
Software dependent but hardware independent.
Logical Independence: The ability to change the internal model without affecting the conceptual model.
Physical Model:
The lowest level of abstraction.
Describes how data is saved on physical storage media (disks, tapes).
Requires definition of physical storage and access methods.
Physical Independence: Changes in the physical model (e.g., moving to a new storage drive) do not affect the internal model.
Data Model Basic Terminology Comparison
Comparison of terms across different models:
Real World Example: Vendor file cabinet.
File Processing: File.
Hierarchical Model: Segment type.
Network Model: Record type.
Relational Model: Table.
ER Model: Entity set.
OO Model: Class.
Specific Instance comparison:
Real World Example: Global supplies.
File Processing: Record.
Hierarchical Model: Segment occurrence.
Network Model: Current record.
Relational Model: Row (tuple).
ER Model: Entity instance.
OO Model: Object.
Data Attribute comparison:
Real World Example: Johnny Ventura.
File Processing: Field.
Hierarchical Model: Segment field.
Network Model: Record field.
Relational Model: Table attribute.
ER Model: Entity attribute.
OO Model: Object attribute.
Identification comparison:
Real World Example: .
File Processing: Index.
Hierarchical Model: Sequence field.
Network Model: Record key.
Relational Model: Key.
ER Model: Entity identifier.
OO Model: Object identifier.
Questions & Discussion
Quick Concept Check:
Why is understanding and documenting business rules important in modeling a database? (Answer: To understand the scope of data, develop constraints, and standardize the company view).
What is the difference between Object-Oriented Data Model (OODM) and Extended Relational Data Model (ERDM)? (Answer: OODM focuses on objects and behavior; ERDM extends relational models to support OO features and complex data while focusing on better management).
What challenges do XML databases address? (Answer: Efficient exchange of unstructured data and managing diverse data types).
How many layers of abstraction are defined by ANSI/SPARC? (Answer: layers).
Revision Questions:
What made the relational model so popular? (Note: Ad hoc queries and set-oriented access).
What are the three types of relationships? (, , ).
What are the advantages of OO models over relational models? (Note: Handling complex objects and behavior through inheritance).
What is the difference between logical and physical data independence? (Note: Logical refers to Internal vs Conceptual models; Physical refers to Physical vs Internal models).