DATABASE

Data vs. Information

  • Data: Data refers to a collection of facts that include text, numbers, and dates. For example, "Murray 35000 7/18/86" represents raw data.

  • Information: Information is the interpretation and meaning given to data. For example, "Mr. Murray is a sales person whose annual salary is $35,000 and whose hire date is July 18, 1986" turns the data into meaningful information.

Hierarchy of Database

  • Database: A structured collection of related data, such as a personnel file or department file.

  • Table: The main structure in a relational database, composed of fields and records, representing a subject or object (person, place, thing, or event).

  • Record: A collection of data about an individual item, such as a specific person, place, thing, or event.

  • Field: A single item of data common to all records.

  • Data: The individual items of data stored in the database.

  • Character: The smallest unit of data, such as a letter or number.

What is a Database?

A database is a structured collection of related data that allows for easier access, retrieval, and use of information. It can be thought of as a digital filing cabinet, address book, or telephone directory. In Microsoft Access, a database is a collection of related tables that store data in an organized way. You can sort, extract, and summarize information related to the data stored in the database.

Database Management System (DBMS)

A DBMS is a software package/system that facilitates the creation and maintenance of a computerized database. It organizes the storage of data, controls the creation, maintenance, and use of database storage structures for organizations and their end users. Examples of DBMS tools include Microsoft Access, MySQL, PostgreSQL, MariaDB, Microsoft SQL Server, and Oracle Database.

Things you can do with a Database

  • Create a database

  • Information storage

  • Information retrieval

  • Information management

  • Information analysis

  • Print and share information

Database Terminologies

  • Data: A number or value found and stored in the database.

  • Information: Data that has been processed to make it relevant and meaningful to the person viewing it.

  • Table: The main structure in the database composed of fields and records, representing a subject as an object (person, place, thing, or event).

  • File: An organized collection of data about an entity.

  • Record: A collection of data about an individual item, referring to a specific person, place, thing, or event.

  • Field: A single item of data common to all records, the smallest structure of data in a relational database.

  • Keys: Fields that serve specific purposes within the table, such as the primary key and foreign key.

  • Relationship: Connections or associations between two or more tables.

Designing a Database

  • Define the purpose of your database.

  • Determine the tables that you need in the database.

  • Determine the fields that you need in the database.

  • Identify unique field values that will allow Access to connect information stored in separate tables.

  • Determine the relationships between tables.

  • Test the design by entering sample data.

Characteristics of a Well-Designed Database

  • Easy to modify.

  • Easy to retrieve information from.

  • Facilitates the development and building of user applications.

  • Simplifies data manipulation, focusing on programming rather than solving problems associated with a poorly designed database.

  • Maintains structure easily, with changes to any table or columns not affecting other tables or columns.

  • Easy to add and delete data.



UNIT 1: INTRODUCTION TO DATABASE SYSTEMS

Overview of Database Systems
Introduction to Database System

  • Data: Known facts (unprocessed) that can be recorded and have implicit meaning.

  • Information: A group of data that collectively carries a logical meaning.

  • Data and Information: Understanding the relationship between data and information.

What is Database Management System (DBMS)

  • A software package/system to facilitate the creation and maintenance of a computerized database.

  • DBMS organizes the storage, creation, maintenance, and use of database storage structures for organizations and end users.

Database System

  • Combines DBMS software with the data itself, including Software, Hardware, and Users.

Database System Approaches
Manual Approach / Manual File Handling System

  • Cards and papers are used for information handling.

  • Suitable for a small number of items.

  • Files stored in cabinets, indexed manually for data access.

  • Limitations: Prone to human error, labor-intensive, inefficient for large-scale data, difficult to update and retrieve, limited cross-referencing.

File-Based Approach

  • Early attempt to computerize manual filing systems.

  • Collection of application programs managing their data.

  • Disadvantages: Data redundancy, inconsistency, isolation, dependence, maintenance, sharing, security issues, incompatible file formats.

Database Approach

  • Shared corporate resource with integrated data and minimal duplication.

  • Separates data from application programs (data independence).

  • Contains data and a description of the data (Metadata/Data Dictionary).

  • Advantages: Control of data redundancy, data consistency, more information, data sharing, improved data integrity, security, and enforcement of standards.

Database Management System (DBMS)
Functions of DBMS

  • Defining Database: Specifies data types, structures, and constraints.

  • Constructing Database: Stores data on a controlled storage medium.

  • Manipulating Database: Querying, updating, and generating reports.

  • Sharing Database: Allows concurrent access by multiple users and programs.

Services of DBMS

  • Data storage, retrieval, and update.

  • User-accessible catalog.

  • Transaction support service to minimize data inconsistency.

  • Concurrency control.

  • Recovery services for database failures.

  • Authorization services for security.

DBMS Environment Components

  • Hardware

  • Software

  • Data

  • Procedure

  • People

Database Users
Actors on the Scene

  • Database Administrators: Authorize access, coordinate and monitor use, acquire resources.

  • Database Designers: Define content, structure, constraints, functions/transactions, communicate with end-users.

  • End-Users: Query, report, and update database content.

Categories of End-Users

  • Casual Users: Access database occasionally.

  • Naive or Parametric Users: Large section, e.g., bank tellers.

  • Sophisticated Users: Analysts, scientists, engineers, using advanced tools.

  • Stand-Alone Users: Maintain personal databases, e.g., tax programs, address books.

Workers Behind the Scene

  • DBMS System Designers and Implementers: Design and implement DBMS modules and interfaces.

  • Tool Developers: Design and implement tools for database modeling, design, and performance.

  • Operators and Maintenance Personnel: Maintain the hardware and software environment for the database system.


UNIT 2: DATABASE SYSTEM CONCEPTS AND ARCHITECTURE

Module 5: Data Models, Schemas, and Instances
Categories of Data Models

  • Hierarchical Model: Developed in the 1960s, representing data in an upside-down tree structure with one-to-many relationships.

  • Network Model: Created in 1969, allows more complex relationships with multiple parent nodes.

  • Relational Model: Introduced by E.F Codd in 1970, uses tables (relations), rows (tuples), and columns (attributes) to store data.

  • Entity-Relationship Model: Graphical representation of entities and relationships, popularized by Peter Chen in 1976.

  • Object-Oriented Model: Defines a database as a collection of objects with attributes and methods, incorporating both relational and non-relational elements.

Data Model

  • A representation of real-world objects, events, and their associations.

  • Collection of concepts to describe and manipulate data, relationships, and constraints.

  • Graphical representations for better abstraction of complex real-world objects and events.

Importance of Data Models

  • Facilitate interaction among designers, programmers, and end-users.

  • Enhance understanding of the organization's database design.

Data Model Basic Building Blocks

  • Entity: Anything about which data is collected and stored (e.g., CUSTOMER).

  • Attribute: Characteristics of an entity (e.g., customer last name).

  • Relationship: Association among entities (e.g., customers served by agents).

Types of Relationships in Data Models

  • One-to-Many (1:M): One painter paints many paintings, but each painting is painted by only one painter.

  • Many-to-Many (M:N): An employee learns many job skills, and each skill may be learned by many employees.

  • One-to-One (1:1): Each store is managed by one manager, and each manager manages only one store.

Business Rules

  • Defined policies, procedures, or principles within an organization.

  • Examples:

    • A customer may generate many invoices.

    • An invoice is generated by only one customer.

    • A training session cannot have fewer than 10 employees or more than 30 employees.

Schemas, Instances, and Database State
Schema and Instance

  • Instances: Collection of information stored at a particular moment, modifiable through CRUD operations.

  • Schema: Overall description of the database structure.

    • Logical Schema: Database design at the logical level.

    • Physical Schema: Database design at the physical level.

    • View Schema: Design of the database at the view level.