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 (1:M1:M).

    • Many-to-many (M:NM:N or M:MM:M).

    • One-to-one (1:11:1).

  • 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 B\text{B} are related to one instance of A\text{A}?

    • Question 2: How many instances of A\text{A} are related to one instance of B\text{B}?

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 (19601960s):

    • 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 (1:M1:M) relationships.

    • Limitations: Difficult to represent M:NM:N relationships, structural level dependency, no ad hoc queries (access is record-at-a-time), and access paths are predefined (navigational access).

  • Network Model (19691969):

    • Represent complex data relationships more effectively.

    • Improved database performance and established standards.

    • Depicts both 1:M1:M and M:NM:N relationships.

  • Relational Model (19701970):

    • 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 (19761976):

    • 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 (19781978–19831983):

    • Aimed for more semantics in the data model.

    • The birth of the internet significantly influenced these shifts.

  • Object-Oriented (OO) Model (19901990s):

    • 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 (20092009):

    • 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 33Vs):

    • 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: G12987G12987.

    • 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: 44 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? (1:M1:M, M:NM:N, 1:11:1).

    • 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).