DATABASE MANAGEMENT SYSTEM

RELATIONAL MODEL IN DBMS

A relational model in DBMS is an approach to logically represent and manage the data stored in a database by storing data in tables.

An RDBMS, which stands for Relational Database Management System, is a type of DBMS (Database Management System) designed specifically to work with relational databases.

Relational Database is a  specific type of database that structures data in tables with rows and columns. Tables are linked together through relationships defined by foreign keys, allowing you to manage complex data with interconnected elements.

From 1970 to 1972, E.F. Codd published a paper to propose using a relational database model.RDBMS is originally based on E.F. Codd's relational model invention.

 

All modern database management systems like SQL, MS SQL Server, IBM DB2, ORACLE, My-SQL, and Microsoft Access are based on RDBMS.

 

Structure:

1. Tables/Relation: Data is organized into two-dimensional tables, also called relations. Each table represents a specific entity or concept, like customers, orders, or products.

• Each relation in a database is identified by a unique name.
• A relation does not contain duplicate tuples.
• The tuples within a relation have no particular order.
• All attributes in a relation are atomic, meaning each cell in the relation holds exactly one value.

 

2. Rows:Tables consist of rows and columns. Each row (tuple) represents a single record or data instance within that entity. 

• No two tuples have identical entries in all fields.
• Every tuple in the relation has the same structure and the same number of entries.
• The order of the tuples is irrelevant; they are identified by their content rather than their position.

3. Columns: Columns (attributes) define the properties or characteristics of those records. For example, a Customer table might have columns for customer ID, name, address, and phone number.

• Each attribute in a relation must have a name.
• Attributes can have null values.
• Default values can be assigned to attributes, which are automatically inserted if no other value is provided.
• Attributes that uniquely identify each tuple in a relation are called the primary key.

The schema of this table can be represented as :

Student(Rollno,Name,Phone)

Relationships:

• Primary Key: Each table has a primary key, a unique identifier for each record in the table. This ensures no duplicate data exists within a table.
• Foreign Keys: Tables can be linked together using foreign keys. A foreign key in one table references the primary key of another table, establishing relationships between data in different tables. This allows you to efficiently retrieve and manage interconnected data.

Benefits:

• Simplicity and Flexibility: The relational model is easy to understand and use. Data is organized logically, and the structure can be adapted to various data types and relationships.
• Data Integrity: Relational databases enforce data integrity through constraints like primary and foreign keys, minimizing data redundancy and inconsistencies.
• Declarative Queries: Users interact with the database using Structured Query Language (SQL), a declarative language. You specify what data you need, and the database management system takes care of retrieving it.
• Normalization: Data can be normalized to eliminate redundancy and improve data integrity. This involves breaking down tables into smaller, more focused tables with well-defined relationships.

Degree:

The total number of attributes that comprise a relation is known as the degree of the table.

Cardinality:

The total number of tuples/row  at any one time in a relation is known as the table's cardinality. The relation whose cardinality is 0 is called an empty table.

Domain:

The domain refers to the possible values each attribute can contain. It can be specified using standard data types such as integers, floating numbers, etc. 

Data Integrity

Entity integrity ensures that a table contains no duplicate rows.

Domain integrity ensures that a column's entries are valid by restricting their type, format, or range.

Referential integrity ensures that rows referenced by other records cannot be deleted.

User-defined integrity enforces specific business rules set by users, which are separate from entity, domain, or referential integrity.

Common Use Cases:

The relational model is widely used across various applications due to its versatility. Here are some examples:

• Inventory Management: Track product information, stock levels, and order details.
• Customer Relationship Management (CRM): Store customer data, track interactions, and manage sales pipelines.
• Financial Management: Manage financial transactions, accounts, and budgets.
• Human Resources: Maintain employee data, track payroll, and manage benefits.

 

Question: Write the schema for the table below:

Schema : department (dept name, building, budget)