Relational Database | Meaning, Relational Model, Types, Queries

Meaning of Relational Database A relational database comprises organized data items presented in formally defined tables, allowing versatile access and reassembly of data without necessitating a reorganization of the database tables. E. F. Codd introduced the relational database concept at IBM in 1970. The structured query language (SQL) serves as the standard interface for users […]

Meaning of Relational Database

A relational database comprises organized data items presented in formally defined tables, allowing versatile access and reassembly of data without necessitating a reorganization of the database tables. E. F. Codd introduced the relational database concept at IBM in 1970.

The structured query language (SQL) serves as the standard interface for users and application programs interacting with a relational database.

 

In this database model, tables contain data categorized into predefined fields. Each table, also referred to as a relation, consists of columns representing various data categories, while each row holds a distinct data instance for the defined columns. For instance, a business order entry database may have a table describing customers with columns like name, address, and phone number, and another table detailing orders with columns such as product, customer, date, and sales price.

 

Users can tailor their database view to meet specific needs. For instance, a branch office manager might seek a report on customers who made purchases after a certain date, while a financial services manager could obtain a report on outstanding accounts, both using the same underlying tables.

 

The manipulation of relations within the database is accomplished using SQL statements. Key SQL commands include SELECT for extracting data, UPDATE for modifying data, DELETE for removing data, and INSERT INTO for adding new data. Additionally, SQL includes commands for database and table creation, modification, and deletion, such as CREATE DATABASE, ALTER DATABASE, CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, and DROP INDEX.

 

The SELECT statement, a fundamental SQL command, is employed to retrieve data from a database. The resulting data set, known as the result-set, is stored in a result table. The syntax for a basic SELECT statement involves specifying column names and the corresponding table. For instance:

 

Relational Model – Integrity Constraints

Integrity Constraints in Relation Databases

Integrity constraints (IC) are conditions defined in a database schema that regulate the permissible data in a database instance. A legal instance is one that satisfies all the specified integrity constraints on the database schema. The Database Management System (DBMS) allows only legal instances to be stored in the database.

 

The relational model accommodates various types of integrity constraints, which contribute to the validity and consistency of stored data. Constraints, often expressed as rules, uphold the integrity of the database by preventing violations. These constraints can be applied to individual attributes or relationships between tables, ensuring that authorized user modifications (updates, deletions, insertions) do not compromise data consistency.

 

For instance, consider the example where a blood group must be restricted to values ‘A,’ ‘B,’ ‘AB,’ or ‘O’ exclusively, excluding any other values.

 

Types of Integrity Constraints

Different categories of integrity constraints include:

  1. Domain Integrity

Domain integrity encompasses the definition of a valid set of values for an attribute. It involves specifying the data type, length, allowance of null values, uniqueness, default value, range, and/or specific values for the attribute.

 

  1. Entity Integrity Constraint

This constraint asserts that the value of a primary key attribute in any database relation cannot be null. For instance, in a “STUDENT” relation, the “Stu_id” primary key must not contain null values, while other attributes may allow null values.

 

  1. Referential Integrity Constraint

This constraint stipulates that if a foreign key exists in a relation, the foreign key value must either match a primary key value in its home relation or be null.

 

  1. Key Constraints

Key constraints declare that a specific minimal subset of fields in a relation serves as a unique identifier for a tuple. There are four types of key constraints:

  1. Candidate Key
  2. Super Key
  3. Primary Key
  4. Foreign Key

 

Querying Relational Database

Basics of SELECT Queries in MS-Access

Various RDBMS vendors offer a graphical user interface (GUI) to assist users in query development, aiding novices in grasping overarching concepts without delving into syntax details. To facilitate this learning approach, our course commences with Microsoft Access, known for its exceptionally user-friendly interface.

 

Begin by downloading an Access database and examining its tables. Throughout this lesson, we will utilize a baseball statistics database to illustrate SELECT query fundamentals.

 

  1. Open the MS-Access database.
  2. Familiarize yourself with the “Navigation Pane” on the left side of the application window, just beneath the “Ribbon.”
  3. The Navigation Pane provides access to stored objects such as tables, queries, forms, and reports. Open the baseball_stats.accdb database, and note the displayed tables (PLAYERS, STATS, and TEAMS).

To view table contents:

– Double-click on a table name in the Navigation Pane to open it.

– Open all three tables and review their content.

 

Now, let’s write a simple SELECT query:

  1. Click on the “Create” tab.
  2. Click on the “Query Design” button in the “Queries” group on the left side of the “Create Ribbon.”
  3. In the “Show Table” dialog, double-click the STATS table, then close the dialog.
  4. Double-click on PLAYER_ID, YEAR, and RBI fields in the STATS table to add them to the design grid.

 

To set criteria and sort records:

  1. Access the SQL view using the “View” drop-down list.
  2. Set the Criteria value for the RBI field to >99.
  3. Test the query.
  4. Return to Design View and sort the records by selecting Descending under the RBI column.
  5. Test the query.
  6. Add criteria for the YEAR field (>1989) to limit results to seasons post-1989.
  7. Test the query.
  8. Further narrow results to the 1990s by setting YEAR criteria to >1989 And <2000.
  9. Test the query.
  10. Introduce an “OR” condition for the YEAR field to identify 100-RBI seasons since 1989 or prior to 1960.
  11. Test the query.

It’s evident that the query output lacks user-friendliness. In the next part of the lesson, we’ll explore how to enhance the output by incorporating a join between the two tables to include player names in the results.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top