Database Fundamentals & Data Management

Database Fundamentals

• A database is an organized collection of logically related data
• Databases are designed for efficient information storage, retrieval, and management
• Data refers to stored representations of meaningful objects or events
• Structured data is organized in a predefined format, like rows and columns in a table
• Metadata describes the properties and context of data
• Metadata aids users in understanding and managing the data

Data Management Issues

• Program-data dependence arises when each program using a file includes its own metadata
• Duplication of data occurs when different systems or programs maintain separate copies of the same data
• Lack of standardization leads to frequent updates needed for individual programs
• Storing the same data in multiple places results in unnecessary storage space
• Duplication makes data harder to update and maintain
• Data changes in one file but not in others causes conflicting/inconsistent information
• Redundancy can lead to errors and difficulties in trusting data accuracy
• Databases need regular backups and recovery plans, increasing operational costs
• Moving from an old to a new database system requires time, effort, and resources
• Implementing a database system can cause organizational conflict

Database Types and Systems

• A personal database is a small, user-specific database for storing personal information
• Enterprise Resource Planning (ERP) integrates major business functions using a centralized database
• Two-tier and N-tier client/server databases separate the database and client (user interface)
• Enterprise applications are large-scale and handle various business operations, often using databases
• A data warehouse is a large, centralized repository for reporting and decision-making
• Data warehouses collect, store, and organize information from operational databases

Database Design and Modeling

• Data modeling is the first step in database design, bridging real-world objects and the computer database
• Data models are used in the database design phase and offer graphical representations of data structures
• An entity is a person, place, thing, or event that data is collected and stored about
• An attribute is a characteristic of an entity and corresponds to fields in file systems
• A relationship describes associations between entities (one-to-many, many-to-many, one-to-one)
• A business rule is a brief, precise, and unambiguous description of a policy within an organization

Database Models

• End users are crucial contributors to the development of business rules
• The hierarchical model was developed in the 1960s for managing large amounts of data, represented as an upside-down "tree"
• The network model represents complex data relationships and improves database performance
• A segment is an equivalent of a file system's record type
• A schema is the conceptual organization of the entire database from the database administrator's view
• A subschema defines the portion of the database that application programs can access
• Data Manipulation Language (DML) defines the environment for managing and using data
• Schema Data Definition Language (DDL) allows the database administrator to define schema components

Database Notations

• Chen Notation represents relationships with a diamond connected to related entities
• Crow's Foot Notation uses symbols to represent connectivities (e.g., "1" for a short line, "M" for a three-pronged foot)

Modern Database Concepts

• Extensible Markup Language (XML) is the standard protocol for data exchange
• Inheritance is the ability of an object to inherit attributes/methods from classes above it
• The relational model was a major breakthrough that replaced standard transmission databases
• The Entity Relationship Model is widely accepted for data modeling; introduced by Chen in 1976
• The Enhanced Entity Relationship Model (EERM) adds semantic constructs to the original ER model

Entity Relationships

• Entity Supertype contains common characteristics and is related to subtypes with unique characteristics
• Relationships within the specialization hierarchy are "is-a" relationships
• Inheritance enables an entity subtype to inherit attributes/relationships of the supertype
• A Subtype Discriminator determines which subtype a supertype occurrence is related to
• A disjoint constraint is a subtype containing a unique subset of the supertype entity set
• An overlapping constraint is a subtype with nonunique subsets of the supertype entity set
• Completeness Constraint specifies if each supertype occurrence must be a member of at least one subtype
• Partial completeness means not every supertype occurrence is a member of a subtype
• Total completeness means every supertype occurrence must be a member of at least one subtype
• Specialization is the top-down process of identifying specific entity subtypes from a supertype
• Generalization is the bottom-up process of identifying a higher-level entity supertype from subtypes
• Entity Clustering combines multiple interrelated entities into an abstract object
• A Primary Key guarantees entity integrity and database implementation efficiency

Keys and Data Management

• Natural keys are real-world identifiers used to uniquely identify objects
• Composite Primary Keys identify composite entities where each key combination is unique
• Surrogate Primary Keys are designer-created keys to simplify entity identification
• Foreign keys work with primary keys to implement relationships
• Implementing 1:1 Relationships involves using foreign keys with primary keys
• Maintaining History of Time-Variant Data manages data changes
• Fan Traps occur when entities have two 1:M relationships producing unintended associations
• Redundant Relationships should remain consistent across the model

Big Data and Database Roles

• Big Data is finding new methods to manage large amounts of web and sensor-generated data
• Database Designers reconcile differences and verify business rules
• The Model helps in understanding the complexities
• Relational Database Management Systems perform functions provided by the hierarchical model
• Object-Oriented Data Models serve as the basis for OODBMS and are semantic
• CASE Tools are software tools that help design and develop databases
• A Repository stores all metadata, describing the structure, relationships, and properties
• Database Management Systems manage and control access to the database
• Data/Database Administrators maintain the database
• System Developers design and create the database
• Specialization provides means to support attribute inheritance
• Entity subtypes inherit all relationships
• Primary key's function is to guarantee entity integrity
• Entity Clusters cause the key attributes of combined entities to be unavailable
• Primary Key guarantees integrity