Database and Database Users Comprehensive Study Guide

Fundamental Database Definitions

• Database: Defined as an organized collection of related data.

• Entity: This represents a real-world object or concept that possesses an independent existence. Examples include specific items like an employee or a project that is described within the database.

• Attribute: This represents a property of interest that provides further description for an entity. For instance, an employee’s name or their salary are attributes of the employee entity.

• Database Management System (DBMS): A software package or system designed to facilitate both the creation and the maintenance of a computerized database.

• Database System: This term refers to the combination of the DBMS software and the data itself. Occasionally, the application programs that interact with the system are also included in this definition.

Types of Databases

Databases are primarily categorized based on the logic governing how the data is stored.

• Relational Databases:

  • In these systems, data is stored in tables consisting of rows and columns.

  • Tables are linked to one another through the use of ‘Keys.’

  • These databases utilize Structured Query Language (SQL).

  • They are strictly organized by a Schema, which serves as a pre-defined blueprint.

  • These are ideal for applications where data consistency and accuracy are critical, such as banking, payroll systems, or the Moodle gradebook.

• NoSQL Databases:

  • These utilize various formats to store big data that does not fit neatly into traditional tables, including Documents (JSON), Key-Value pairs, or Graphs.

  • Non-Relational DBMS do not require a fixed, predefined schema.

  • These are best suited for social media feeds, real-time data, and mobile applications where the structure of data changes rapidly.

Database Management System (DBMS) Functionality

A DBMS is a software system that facilitates the processes of defining, constructing, manipulating, and sharing databases among various users and applications.

• Data Definition: This allows users to specify data types, structures, and constraints, collectively known as metadata.

• Meta-data Storage: The DBMS stores metadata in the form of a database catalog or a data dictionary.

• Constructing (Loading): This is the process of storing the actual data on a secondary storage medium, such as a Hard Drive or Solid State Drive (SSD), which is controlled by the DBMS.

• Manipulating: This enables users to perform several actions:

  • Querying to retrieve specific data.

  • Updating existing records.

  • Inserting new records.

  • Deleting records.

  • Generating reports.

• Sharing: This functionality allows multiple users and programs to access the database simultaneously.

• Data Security: This involves protecting the database from unauthorized access through mechanisms such as user authentication and encryption.

• Data Integrity: The DBMS enforces rules, known as constraints, to ensure data remains accurate. An example of a constraint is ensuring a ‘Salary’ column cannot contain negative numbers.

• Concurrency Control: This involves managing multiple users accessing the same data at the same exact time to prevent data conflicts.

• Backup and Recovery: The system provides tools to back up data and restore it in the event of a system failure.

Simplified Database System Environment

The environment consists of interactions between users, software, and stored data, structured as follows:

• Users/Programmers: Interact with the system through Application Programs and Queries.

• DBMS Software: Contains two primary sub-components:

  • Software to Process Queries/Programs.

  • Software to Access Stored Data.

• Stored Data: Consists of two main segments:

  • Stored Database Definition (Meta-Data).

  • The Stored Database itself.

Classification and Examples of DBMS

DBMS software is largely grouped into RDBMS (Relational) and non-relational DBMS.

• Relational DBMS (RDBMS):

  • Used specifically for relational databases.

  • Examples include:

  • Microsoft SQL Server

  • MySQL

  • Oracle

  • PostgreSQL

  • Microsoft Access

• Non-Relational DBMS:

  • Used for NoSQL databases.

  • Examples include:

  • MongoDB

  • Redis

  • Apache Cassandra

  • Apache HBase

  • Apache Couch DB

Main Characteristics of the Database Approach

• Self-Describing Nature: The database contains the data as well as a complete definition of its structure (metadata), which is stored in a Data Catalog.

• Insulation Between Programs and Data: This is also known as Program-Data Independence. It allows the structure of the data to be changed without necessitating the rewriting of the application programs that use that data.

• Data Abstraction: Users are presented with a ‘logical’ view of the data, while the complex details of physical storage remain hidden by the DBMS.

• Support for Multiple Views: The system allows different users to see different segments or perspectives of the data.

• Sharing and Multi-user Transactions: The system is designed to allow many users to access the same data simultaneously without the risk of data corruption.

Categories of Database Users

Database users are categorized into two primary groups based on their relationship with the system.

Actors on the Scene

These are individuals who use, control, design, and maintain database content and applications.

• Database Administrators (DBA): Responsible for security, performance tuning, managing backups, and granting access permissions to other users.

• Database Designers: Responsible for identifying the specific data to be stored and choosing appropriate structures (the schema) to represent that data.

• Application Programmers: Software developers who write the code in languages such as Java, Python, or C# to interact with the database.

• End Users: People who use the system to perform their jobs, subdivided into:

  • Naive (Parametric) Users: Use pre-built applications, such as a bank teller using a ‘deposit’ button.

  • Sophisticated Users: Knowledgeable individuals, such as engineers or scientists, who write their own SQL queries for data analysis.

  • Casual Users: People who access the database occasionally but require different information with each access, such as a manager checking monthly reports.

  • Stand-alone Users: Individuals who maintain personal databases using ready-to-use packaged applications.

Workers Behind the Scene

These individuals design and develop the DBMS software and manage the underlying computer systems.

• System Designers and Implementors: They design and implement the DBMS package itself in the form of modules and interfaces, including testing and debugging them.

• Tool Developers: They create software tools for modeling, designing databases, performance monitoring, prototyping, test data generation, and user interface creation to facilitate the building of applications.

• Operators and Maintenance Personnel: They are responsible for the physical running and maintenance of the database system’s hardware and software environment.

Advantages of the Database Approach

• Redundancy Control: Minimizing the duplication of data within the system.

• Restricting Unauthorized Access: Ensuring only permitted users can view or modify data.

• Backup and Recovery: Providing reliable methods to restore data after failures.

• Economies of Scale: By consolidating data into a single system, an organization can save money on hardware and personnel costs.

• Database: An organized collection of related data.

• Entity: A real-world object or concept that exists independently, like an employee or project described within the database.

• Attribute: A property of interest that describes an entity, such as an employee’s name or salary.

• Database Management System (DBMS): Software that helps create and maintain a computerized database.

• Database System: The combination of DBMS software and the data itself, sometimes also including application programs that interact with it.

Types of Databases

Databases can be categorized based on how data is stored.

• Relational Databases:

  • Data is stored in tables, organized in rows and columns.

  • Tables are linked by ‘Keys.’

  • Uses Structured Query Language (SQL).

  • Organized by a Schema, like a blueprint for the data.

  • Good for applications needing data accuracy, like banking or payroll systems.

• NoSQL Databases:

  • Stores big data that doesn’t fit neatly into tables, using formats like JSON or Key-Value pairs.

  • No need for a fixed schema.

  • Best for applications like social media or real-time data where data structure changes often.

Database Management System (DBMS) Functionality

A DBMS helps with defining, constructing, manipulating, and sharing databases.

• Data Definition: Lets users specify data types and structures.

• Meta-data Storage: Stores metadata (data about data) in a catalog.

• Constructing: Involves storing actual data on devices like Hard Drives or SSDs.

• Manipulating: Enables actions like:

  • Retrieving specific data.

  • Updating records.

  • Inserting new entries.

  • Deleting records.

  • Generating reports.

• Sharing: Allows multiple users to access the database at the same time.

• Data Security: Protects against unauthorized access with user authentication and encryption.

• Data Integrity: Maintains accuracy by enforcing rules (constraints), like ensuring salaries aren’t negative.