Database Fundamentals and Models

• Database fundamentals and database models are explored

What is a Database?

• A database is a collection of grouped and related data
• It is stored efficiently for information use and manipulation in decision-making

Advantages of a Database

• Data redundancy is controlled, as copies are not stored unless necessary
• Data consistency is improved by reducing inconsistency risks
• Data integrity refers to the correctness, validity, and precision of the data
• Security provides protection against unauthorized users
• Accessibility provides access for users
• Concurrency allows simultaneous data access without compromising operation

Disadvantages of a Database

• Costly implementation due to physical equipment and software
• Specialized personnel are required for administration and design
• Short-term profitability is lacking due to personnel and equipment costs

Database Architecture

• External level (view) shows the database as seen by users
• Conceptual level (logical) defines the structure and relationships of data
• Internal level (physical) specifies how data is stored on physical media

Physical Storage Units

• .frm is the format file.
• .MYI contains the indexes.
• .MYD stores the data.
• .log is the action log of the database.

Elements of a Database

• Entities are persons, objects, or events for which data is collected and maintained
• Fields, also known as attributes, are a type of information with a name, field type, and characteristics
• Records include all info about an individual/object, that can contain any number of registries
• Tables/files are basic units allowing the computer to differentiate information groups, stored in Registries (rows) and Fields (columns)
• Keys are fields used to locate, access, or identify specific records

Types of Keys

• Primary keys uniquely identify a member of an entity
• Foreign keys in a table match a primary key in another table to establish relationships
• Secondary keys are used to access records and are not necessarily unique, allowing queries across different fields

Base de Datos en Youtube

• YouTube uses MySQL and includes tables for users, videos, and comments
• Redundancy is avoided by preventing duplicate user emails
• Consistency is maintained by immediately reflecting information changes
• Data integrity is ensured through Gmail access and required data fields
• Accessibility allows access from any geographic location with internet devices
• Concurrency supports thousands of simultaneous user connections
• Security prevents unauthorized video/comment edits

Data Modeling

• Data modeling represents complex data structures and helps understand complexity in the real world
• Base data models represent structure, characteristics, restrictions and transformation

Hierarchical Modeling

• Information is stored in a tree-like structure where parent nodes can have multiple children
• The parent node is called the root.
• Tree structures are used for logical data representation, implemented with trees and pointers.

Hierarchical Model Characteristics

• The tree organizes into levels.
• The root node is level 0.
• Lines represent hierarchical associations.
• Parent nodes can have unlimited children, while child nodes have one parent.
• Each node must have a parent, except the root.
• Nodes without descendants are leaves.
• Height is the number of levels.
• Size is the number of nodes.
• Only 1:1 or 1:N interrelations are allowed.
• Trees traverse in preorder: subtree left and subtree right.

Hierarchical Model Example

• In a banking database, customer info can be organized hierarchically.

Hierarchical Database Managers

• Information Management System (IMS) by IBM.
• Adabas (Adaptable Database System).
• GT.M is a high-performance database engine.
• Hierarchical models are used mainly in banks.

Hierarchical Model Advantages

• Useful for applications managing high volumes of shared information, creating stable structures
• Easy to see the structure
• The links can be modified by changing a small amount of code
• Navigation is quick
• Implementation is simple and fast.
• Relationships can be predefined.

Hierarchical Model Disadvantages

• Duplication of records
• Referential integrity
• It is possible to delete a father node without deleting the children node
• Complicated data structure (unidirectional data)
• Allows denormalization, leading to redundancy
• Storage of trees in files is complex, necessitating storage of various record types and pointers, thus increasing computational cost.
• The Language of Manipulating Data (LMD)is difficult to implement or use.
• Requires children nodes to hace parents

Network Model

• Uses network data structures, known as plex structures
• Entities are represented as nodes/records, relations as arcs
• Implementation is done through links and pointers
• Each component can be linked to any other.
• A child has more than one parent

Characteristics of Network Model

• Records represent nodes and are the basic access/manipulation unit
• A set is a collection of two or more registries establishing a link, comprising an owner and a member

Set Rules

• Sets require an owner, a member, many-to-many relationships, and a connector record
• Sets form through an owner record and any number of member records.
• Owners cannot be members of themselves and cannot belong to multiple instances of the same group

Implementation Details

• Boxes/cells represent records, lines represent links
• The model is flexible but implementing it is inefficient
• This is a theoretical model

Network Model Database Managers

• IDMS (Integrated Database Management System) for mainframes

Network Model Advantages

• More powerful than the hierarchical model.
• Flexible access to information compared to hierarchical.
• Redundancy through a connector registry type.

Network Model Disadvantages

• An owner in a given set cannot be the same type as the member
• Slower access and more design complexity than hierarchical bases
• Requires knowledge of physical data directions

Relational Model

• A data model based on set theory
• Data is stored in relations (tables) lacking inherent order
• It is advantageous to understand and use that model
• Data retrieval/storage uses queries
• Tables consist of rows (records/tuples).
• The most common language to build the questions is SQL

Relational Model Components

• Relations consist of tuples and attributes within domains
• Number of attributes in a relation defines its degree

Relational Model Example

• Tables show Client accounts with a name and account number
• Domains show column types
• Grade shows the cardinality of data

Relational Database Managers

• Microsoft SQL Server
• Microsoft Access
• MySQL
• InterBase
• Oracle

Relacional Database Model Advantages

• Tools prevent record duplication.
• Referential integrity is maintained; deleting a record removes related dependent records.
• Normalization enhances comprehensibility and applicability.
• Efficient for managing large data collections.
• Security restrictions and user permission
• All data, whether entities or connections, is in tables.
• Reduces dynamic data administration issues.
• Irrelevant data storage.

Relational Database Model Disadvantages

• Cannot support certain relations on the real world
• There are deficiences in graphic data, multimedia, CAD and the geographic information systems