Database System

 

Database System

Database

The database is a collection of related data. The component of a file in a file system is a data item, which is the smallest name unit of data that has meaning in the real world. For example, last name, first name, address etc.

Benefits of Database

a. Reduced the amount of time you spend managing data

b. Analyse data in a variety of ways

c. Promote a disciplined approach to data management

d. Turn disparate information into a valuable resource

e. Improve the quality and consistency of information

Database Management System(DBMS)

A database Management System(DBMS) is software designed to store, retrieve, define, and manage data in a database. DBMS software primarily functions as an interface between the end user and the database, simultaneously managing the data, the database engine, and the database schema in order to facilitate the organization and manipulation of data.

Features of a DBMS

1. Data Security

Data security is paramount in an era where data breaches and cyberattacks are rampant. DBMS provides robust mechanisms for securing sensitive information. Access control, authentication, and encryption are tools at its disposal.

2. Data Integrity

Data integrity ensures that data remains accurate and consistent throughout its lifecycle. DBMS enforces data integrity constraints, such as primary keys, foreign keys, and check constraints, preventing erroneous or inconsistent data insertion.

3. Data Recovery

Data loss can be catastrophic for businesses. DBMS offers additional features of the ER model in DBMS data features, including backups and transactions logs. Backups allow organizations to restore data to a previous state in case of hardware failure or data corruption.

4. Concurrency Control

In multi-user environment, simultaneous access to the database can lead to conflicts and inconsistencies. DBMS employs concurrency control mechanisms to manage concurrent transactions effectively.

5. Scalability

As organizations grow, their data requirements expand exponentially. DBMS offers scalability features to accommodate this growth. DBMS can adapt to changing data needs, whether horizontal scaling(adding more servers) or vertical scaling(upgrading hardware), ensuring optimal performance.

Relationship

A relationship, in the context of databases, is a situation that exists between two relational database tables when one table has a foreign key that references the primary key of the other table. Relationships allow relational databases to split and store data in different tables, while linking disparate data items.

Types of Relationship

One to One Relationship

When a single instance of an entity is associated with a single instance of another entity then it is called as one to one relationship. Ex:

One to Many Relationship

When a single instance of an entity is associated with more than one instance of another entity then it is called as one to many relationship. Ex:

Many to Many Relationship

When more than one instance of an entity are associated with more than one instance of another entity it is called as many to many relationship. Ex:

Primary Key

A primary key is the column or columns that contain values that uniquely identify each row in a table. A database table must have a primary key for Optim to insert, update, restore, or delete data from a database table.

Foreign Key

A foreign key is a field that directly identifies another table. As you might imagine, this makes using foreign keys a very useful skill and one of the aspects of SQL that helps to set it apart from other programs.

Differentiate between Primary key and Foreign key

The difference between Primary key and Foreign key are as given below.

Primary Key	Foreign Key
Primary Key Can NOT Accept Null Values.	Foreign Key Can Accept Multiple Null Values.
Only One Primary Key in a Table.	More than One Foreign Key in a Table.
Primary Key Uniquely Identify a Record in the Table.	Foreign Key is a Field in the Table that is Primary Key in Another Table.
Primary Key is Clustered Index. Ex: CREATE TABLE[country]( [id] INT IDENTITY(1,1) NOT NULL, [name] VARCHAR(50) NOT NULL, UNIQUE NON CLUSTERED([name], CONSTRAINT[PK_country] PRIMARY KEY CLUSTERED([id]) );	Foreign Key is Non-Clustered Index. Ex: CREATE TABLE[reg]( [country] VARCHAR (50) NOT NULL, [name] VARCHAR (50) NOT NULL, CONSTRAINT [FK_reg_country] FOREIGN KEY ([name] REFERENCES [dbo].[country] ([name]) );