Introduction to Databases and DBMS

Introduction to Databases

• Databases and database technology significantly impact computer usage across various fields.
• Databases play a critical role in business, e-commerce, engineering, medicine, genetics, law, education, and library science.
• A database is a structured collection of related data that represents facts with implicit meaning.
• A database reflects a real-world aspect, called the mini world or universe of discourse (UoD).
• Changes in the mini world update the database.
• Size and complexity does not dictate the nature of a database.
• Databases can be manual or computerized; complex databases use DBMS.

Database Management System (DBMS)

• A DBMS is a program suite that allows users to create and maintain databases.
• It defines, constructs, manipulates, and shares databases.
• Defining a database includes specifying data types, structures, and constraints.
• DBMS tasks include:
  • Data Definition: Creation, modification, and removal of data organization definitions.
  • Data Updation: Insertion, modification, and deletion of actual data.
  • Data Retrieval: Retrieving data for various applications.
  • User Administration: Registering/monitoring users, ensuring data integrity/security, managing concurrency, and recovering from failures.
• Key DBMS functions include protecting and maintaining data over time.
• Protection against hardware/software malfunctions and unauthorized/malicious access is ensured.
• DBMS must adapt as requirement changes to maintain large databases.
• A database combined with DBMS software forms a database system.

University Database Example

• A university database maintains information on students, courses, and grades.
• It's organized into five files:
  • STUDENT: Data on each student.
  • COURSE: Data on each course.
  • SECTION: Data on each course section.
  • GRADE_REPORT: Grades students receive in sections.
  • PREREQUISITE: Course prerequisites.

Advantages of DBMS

• Controls redundancy by storing all data in one database file.
• Enables data sharing among authorized users.
• Centralized nature simplifies maintenance.
• Reduces development and maintenance time.
• Provides backup and recovery mechanisms against failures.
• Offers multiple user interfaces like graphical or program interfaces.

Disadvantages of DBMS

• Requires high-speed data processors and large memory, increasing hardware/software costs.
• Large disk and memory space is required for efficient operation.
• Adds complexity and requirements to system.
• Failures have a high impact due to centralized data storage; damage may cause permanent data loss.

Database vs. File System Approach

• FILE SYSTEM:
• Used to manage and organize the files stored in the hard disk of the computer
• Redundant data is present
• Query processing is not so efficient
• Data consistency is low
• Less complex, does not support complicated transactions
• Less security
• Less expensive in comparison to DBMS
• Does not support crash recovery
• DBMS
  • A software to store and retrieve the user’s data
  • No presence of redundant data
  • Query processing is efficient
  • Due to the process of normalization, the data consistency is high
  • More complexity in managing the data, easier to implement complicated transactions
  • Supports more security mechanisms
  • Higher cost than the File system
  • Crash recovery mechanism is highly supported

Database Users

• Users are categorized as "Actors on the scene" (those who use and control database content) and "Workers behind the scene" (those who design/maintain the DBMS).

Actors on the Scene

• Database Administrator (DBA): Oversees/manages the database system, setting access rights and acquiring upgrades; accountable for security/performance.
• Database Designers: Identifies data and its organization, interacting with users to create database views meeting data/processing needs.
• End Users: Access the database for querying, updating, and reporting.
  • Casual end users: Occasionally use the database (middle/high-level managers).
  • Naive/Parametric end users: Frequently query/update via standard transactions (bank tellers, reservation clerks).
  • Stand-alone users: Maintain personal databases (tax package users).
• System Analysts and Application Programmers:
  • System Analysts: determines naive/parametric user needs and develops canned transaction specifications.
  • Application Programmers: implement, test, document, and maintain programs based the specifications from systems analysts.

Workers Behind the Scene

• DBMS System Designers and Implementers: Design/implement DBMS modules, including catalog, query processing, interface, data access, concurrency control, and security.
• Tool Developers: Create tools for database modeling/design and system performance.
• Operators and Maintenance Personnel: Manage hardware/software environment for the database system, also known as system administration personnel.

History of Database Applications

• Early database systems maintained records in corporations, universities, hospitals, and banks using hierarchical and network systems.
• Problems included lack of data abstraction, program-data independence, and limited programming interfaces, making queries expensive to implement.
• Relational databases separated data's physical storage from its conceptual form and relational data models introduced high-level query languages.
• Object-oriented databases (OODBs) are used in specialized areas like engineering, multimedia, and manufacturing. Many object-oriented concepts incorporated into relational DBMSs lead to ORDBMSs.
• XML serves as the primary standard for data interchange on the Web, combining document and database modeling concepts.
• The success of traditional database systems spurred development in new applications.

Data Models, Schemas, and Instances

• A data model contains concepts describing database structure, including data types, relationships, and constraints.
• Data models specify operations for data retrieval and updates.
• Data models include
  • High-level/conceptual data models are close to user perception, using entities, attributes, and relationships.
  • Representational/implementation data models are understandable but somewhat removed from data storage.
  • Low-level/physical data models detail data storage on computer media, including formats, ordering, and access paths.
• Database schema describes the database and what variables it contains.
• Schema diagram: visual depiction of the database schema
• Database instance represents variable values at a specific time.

DBMS Architecture

• Depends on its architecture.
• Client/server architecture manages numerous networked PCs, web, and database servers.
• Clients are connected via a network.
• Users are connected to the database to complete requests, impacting DBMS architecture.

Types of DBMS Architecture

• Can be single-tier or multi-tier, but logically it is classified into two-tier and three-tier architectures.
• One-Tier Architecture: Database is directly accessed by the user. Handy tools are not offered for end user.
• Two-Tier Architecture: Client-server-based. Client-side applications interact directly with the server-side database using APIs like ODBC/JDBC.
• Three-Tier Architecture: introduces a layer between client and server. Client-side applications interact with an application server that communicates with the database, common to large web applications.

Three-Schema Architecture and Data Independence

• Three-schema architecture separates user applications from the physical database.
• Composed of:
  • Internal Level: Defines physical storage using a physical data model.
  • Conceptual Level: Describes overall database structure, entities, data types, relationships, operations, and constraints.
  • External/View Level: Includes external schemas or user views, each tailored to a specific user group.

Data Independence Types

• Logical Data Independence: conceptual schema changes without altering external schemas or applications.
• Physical Data Independence: internal schema changes without altering the conceptual schema.

Structure of DBMS

• Creating, deleting, and manipulating databases with particular software is referred to as DBMS.
• Managing data access, querying, storage, and retrieval are also part of DBMS.
• Programmers, Database Managers, and end users are given access to consolidated data through user interfaces (UIs) in DBMS.
• The location of the data no longer needs to be known by end users thanks to database management systems.
• Relational and non-relational DBMS components are offered through the internet as a database as a service (DBaaS)

The Three Parts of a Database System

• Query Processor: Processes user queries, making data access simple for the database system.
  • The DDL Interpreter: interprets DDL statements used in schema definitions, creating tables of meta-data in the data dictionary.
  • The DML Compiler: This transforms DML statements into machine compatible low-level instructions to execute the query.
  • The Embedded DML Pre-compiler: This will pre-compile the DML commands into standard SQL used before query evaluation.
  • The Query Optimizer: Executes the question's or query's evaluation strategy and returns the result.
• Storage Manager: A pathway for the queries made, keeping consistency through data control instructions (DCL)
  • It includes:
    • Integrity Manager: Manages integrity constraints
    • Authorization Manager: Verifies the authentication and query
    • File Manager: Manages structure and all data files.
    • Transaction Manager: Maintains database consistency, also controls concurrent usage.
    • Buffer Manager: Transfers data and manages cache and main/primary memory.
    • Recovery Manager: Ensures database consistency despite failures.
    • Scheduler: To coordinate/synchronize tasks among concurrent users.
    • Command processor: to process auth’d queries.
• Disk Storage: A physical implementation of Datastructures in the form of disk storage as part of a DBMS
• Contains:
  • Includes Data dictionary. Contains: table names, data contraints, relationships etc. Also useful for: predicting code changes, and controlling accuracy by documenting/storing design details.
  • It also stores: data and index files

Centralized and Client-Server Architectures for DBMS

• DBMS architectures have followed computer system trends.
• Earlier systems utilized mainframe computers for all functions, with users accessing via terminals with limited processing power.
• Client/server DBMS designs have emerged to utilize the user side's computing capability.
• The client/server model uses PCs, workstations, and a smaller amount of mainframe machines that are connected via computer networks.
• Specialized servers offer their resources to client machines. File servers are an instance of the client architecture. Clients can use processing power to run local apps from specialized server.

Logical vs Physical Tier Client-Server Architecture

• Logical two-tier architecture involves dedicated servers for printing, files, and database management.
• Physical two-tier architecture distributes clients and servers across sites, with data handled by the DBMS located on the Server side.

Client/Server Architecture Functionality

• Client: User interface capabilities and local processing. When a client requires access to data, functionality is provided by accessing a separate database.
• Server: A system containing hardware and software that can provide services to the machines including DBMS.
• Software components are distributed over client and server.
• The query/transaction functionality is handled by server, and the processes the user client handles using specific SQL. When DBMS access is requred, client establishes connection to server side.

Entity Relationship Model

• The Entity Relationship Model identifies entities and also represents their relationship.
• ER model visually describes the database in schema, and overall logical enterprise.
• ER diagram helps represent objects and the relationship between them.
• ER models make conversion to relations more simplified, require no technical knowledge, and offer logical data visualization.

ER Model Components

• Entities: real world construct with physical or conceptual existence that is also an entity type
• Attributes: properties that define the entity
• Relationships: interaction/relationship among entities. represented by lines
• Strong Entity: the primary/independable type, identified by rectangle symbol
• Weak Entity: depends upon primary type, and does not define attributes
• Key Attribute: The main attribute that is part of a set that uniquely identifies each element.
• Composite Attribute: composed of other attributes to describe student element
• Multivalued Attribute: An attribute that is comprised of more than one value -Derived Attribute: Attributes used to derive others
• Relationships Type: A set of associations, or Enrolled in
• Relationship Set: relation of the similar relation type (IE Enrollments as a whole)
• Relationship Set: Defines degree of relationship, and identifies how many distinct entities participate.
  • Unary: only 1 relationship (One to one person)
  • Binary: 2 relationships
  • N-Ray - N sets - n relationships

Cardinality

-The number of times an relationship can be set is defined by cardinality.

• One-to-one sets in entity take part once (male can only marry one female)
• One-to Many: Mapping is one, but can accommodate many others (A surgeon can treat multiple)
• Many to One : only takes in the relationship one time. (class can only take course) 4 tables here.
• Many to Many : all aspects are numerous. (Student can take course)

SQL Constraints

• SQL sets rules for data in tabular format, limiting values to maintain uniformity
• Constraints are column or table level, using parameters to follow SQL
• parameters can not be “NULL” or “UNIQUE”
• Primary key is the two and a table and also can have foreign keys - creates “checks” , or establishes a default
• Also create a quick index to retrieve data.

Participation

Participation Constraint is applied to the entity participating in the relationship set.

• total participation : (each element is in the relationship), as shown by double line in ER Diagram
• partial paticipation, entity can exist and also may NOT participate in a relationship"
• ER diagrams can be made using certain steps including but limited to" rectangle for entities, diamond for relationships, ellipse for attributes etc.

Enhanced ER Model

Enhanced ER Model addresses complexities of modern data processing.

• The complexity is reduced by improving ER, diagrams the now can depict subclass and superclasses.
• A Superclass represents the inheritance to a subset
• With Generalization, subclass entities will inherit everything.
• The subclasses can either be disjoint or overlapped.