Centralized vs. Distributed Databases

• Managing an organization involves data reflecting information about its activities.
• Data needs to be stored and updated to reflect real-time changes, including adding, modifying, and deleting data.
• The purpose of storing and updating data is to extract information for management's operational and decision-making processes.
• Common data extraction tasks include searching, statistical analysis, and reporting.
• A collection of data used to manage an organization's activities is called a database (CSDL).
• This data is digitized so that computers can store, access, update, and process it.

Centralized vs. Distributed Databases

• A database stored on a single computer is called a centralized database.
• A database distributed across multiple computers in a network is called a distributed database.
• Each computer (site) in a distributed network has independent processing capabilities and can execute local applications.
• Each site also participates in at least one global application, requiring data access across multiple locations using communication systems.
• Software systems (sets of programs) are developed to allow users to easily interact with the database through user-friendly interfaces; these are called Database Management Systems (DBMS).

Database Management System Functions

• Defining data.
• Updating and retrieving data.
• Ensuring security and safety of the database.
• Providing an interface for application programming.

Database System Components

• A database system includes the organization's database, the DBMS, and application software with interfaces to the database.
• It caters to the management needs.
• Two types of database systems are centralized and distributed.
• A centralized database system manages data in a central database on one computer.
• A distributed database system allows users to access data stored on different networked computers.

Relational Databases

• A relational database stores data in tables with relationships to each other.
• Each table consists of rows and columns.

Records and Fields

• Each row is called a record, which represents a collection of information about a specific entity managed in the table.
• Each column is called a field, representing an attribute of the entity managed in the table.
• Each field contains data of the same type, defining the data type of the field.

Keys

• A key of a table is one or more fields whose values uniquely identify a record in the table.
• No field can be removed from the key without losing the ability to uniquely identify records.
• If a table has multiple keys, one can be chosen as the primary key.
• The primary key is usually the key with the fewest fields.
• Updating table data must satisfy the requirement that no two records have the same key value, which is called a key constraint.

Foreign Keys and Referential Integrity

• A table (A) may have a field or group of fields (K) that form the primary key of another table (B).
• Table A is called the referencing table, and table B is called the referenced table.
• K is called the foreign key of table A.
• Tables A and B have a relationship through the foreign key K of table A.
• Ensuring full referential integrity between related tables is part of maintaining data integrity.
• This constraint applies to foreign keys and is called the foreign key constraint.
• The foreign key constraint requires that all values of the foreign key in the referencing table must exist among the key values in the referenced table.

Database Queries

• A database query is a statement expressing a user's request to the database.
• It can be a request to manipulate data (add, modify, delete records) or to extract data.
• Each DBMS has its query language.
• The most popular query language for relational DBMS is SQL (Structured Query Language).

SQL Structure

• The basic structure of an SQL query is:
  • SELECT <data to retrieve>
  • FROM <table name>
  • WHERE <selection condition>
• <data to retrieve> specifies the names of the data fields to be returned.
• <table name> specifies the table in the database from which to retrieve data.
• <selection condition> is a logical expression for selecting the records to return.

Joining Tables

• To combine data from tables based on a condition, SQL uses the JOIN keyword in the FROM clause.
• There are several types of JOIN, with INNER JOIN being the most common.

INNER JOIN Syntax

• The query syntax is:
  • SELECT <data to retrieve>
  • FROM <table 1> INNER JOIN <table 2> ON <join condition>
  • WHERE <selection condition>
• <join condition> is the condition to combine records from both tables.

Join Condition Details

• The <join condition> is generally expressed as:
  • table1.FieldA <comparison operator> table2.FieldB
• Where the comparison operator can be =, <, <=, >, >=, or <>.
• The most common join condition is equality of values in a common field between the two tables.