Database Concepts - Introduction

Content:

• Introduction to DBMS
• FIle System vs DBMS
• Key Concepts in DBMS
• Database Schema
• Data Constraints
• Meta-data or Data Dictionary
• Database Instance
• Query
• Data Manipulation
• Database Engine
• Relational Data Model
• Attributes
• Tuple
• Domain
• Degree
• Cardinality
• Important Properties of a Relation
• Keys in a Relational Database
• Candidate Key
• Primary Key
• Composite Primary Key
• Foreign Key

Introdution to DBMS:

In a program(eg python program) we store data in variable, but its temporary and when we need permanent storage of data then either file system or DBMS. Both DBMS and File System are software that helps us to store data on the Secondary Device, mainly on Hard Disk but the only difference is in their features they offer with the data storage.

File system VS DBMS

File System	DBMS
A file can be understood as a container to store data in a computer. Files can be stored on the storage device of a computer system. File system is a software that helps in storage of file on the system. The data stored is permanent.	Database is a collection of inter-related data which helps in efficient retrieval, insertion and deletion of data from database and organizes the data in the form of tables, views, schemas, reports etc. The software which is used to manage database is called Database Management System (DBMS).
If the data maintained by the file system is large then its becomes difficult to find and access the data.	The access time of the data in a DBMS does not depend on the size of data, so the access is easy.
File system can not handle data redundancy I.e duplication of data	DBMS can handle Data Redundancy
With file System Controlled data sharing can not be achieved.	With DBMS controlled data sharing can be achieved also allowing to set user privileges
Example: NTFS, FAT 32, EXT4	Example: Mysql, Oracle, postgresql.

Key Concepts in DBMS:

Database Schema:

Database Schema is the design of a database. It is the skeleton of the database that represents the structure (table names and their fields/columns), the type of data each column can hold, constraints on the data to be stored (if any), and the relationships among the tables.

Data Constraint:

It is a restrictions or limitations on the type of data that can be inserted in one or more columns of a table. This is done by specifying one or more constraints on that column(s) while creating the tables.Example of contraints are NULL, UNIQUE, PRIMARY KEY etc.

Meta-data or Data Dictionary:

The database schema along with various constraints on the data is stored by DBMS in a database catalog or dictionary, called meta-data. A meta-data is data about the data.

Database Instance:

The state of the database at any instance is called as database Instance.The database instance changes once any modification/manipulation is done on the database.

Query:

A query is a request to a database for obtaining information in a desired way. Query can be made to get data from one table or from a combination of tables. To perform a query we need query language like SQL(Structured Query Language)

Data Manipulation:

it Means modification od data. Modification of database consists of three operations viz. Insertion, Deletion or Update.

Database Engine:

Database engine is the underlying component or set of programs used by a DBMS to create database and handle various queries for data retrieval and manipulation.

Relational Data Model:

A data model describes the structure of the database, including how data are defined and represented, relationships among data, and the constraints. The most commonly used data model is Relational Data Model.

In relational model, tables are called relations that store data for different columns. Each table can have multiple columns where each column name should be unique. A table consists of a collection of relationships

Terminologies in relational data model:

ATTRIBUTE:

The columns in a table is called as Attributes.

TUPLE:

Each row of data in a relation (table) is called a tuple.

DOMAIN:

It is a set of values from which an attribute can take a value in each row. Usually, a data type is used to specify domain for an attribute.

DEGREE:

The number of attributes in a relation is called the Degree of the relation.

CARDINALITY:

The number of tuples in a relation is called the Cardinality of the relation.

Important Properties of a Relation:

• Each attribute in a relation has a unique name.
• Sequence of attributes in a relation is immaterial.
• Each tuple in a relation is distinct. Thus, each tuple of a relation must be uniquely identified by its contents.
• Sequence of tuples in a relation is immaterial. The tuples are not considered to be ordered, even though they appear to be in tabular form.
• All data values in an attribute must be from the same domain (same data type).
• Each data value associated with an attribute must be atomic (cannot be further divisible into meaningful subparts).
• A special value “NULL” is used to represent values that are unknown or non-applicable to certain attributes

Keys in a Relational Database:

Relational data model imposes some restrictions or constraints on the values of the attributes and how the contents of one relation be referred through another relation. These restrictions are specified at the time of defining the database through different types of keys as given below:

Candidate Key:

A relation can have one or more attributes that takes distinct values. Any of these attributes can be used to uniquely identify the tuples in the relation. Such attributes are called candidate keys as each of them are candidates for the primary key.

Primary Key:

Out of one or more candidate keys, the attribute chosen by the database designer to uniquely identify the tuples in a relation is called the primary key of that relation. The remaining attributes in the list of candidate keys are called the alternate keys.

Composite Primary Key:

If no single attribute in a relation is able to uniquely distinguish the tuples, then more than one attribute are taken together as primary key. Such primary key consisting of more than one attribute is called Composite Primary key.

Foreign Key:

A foreign key is used to represent the relationship between two relations. A foreign key is an attribute whose value is derived from the primary key of another relation. This means that any attribute of a relation (referencing), which is used to refer contents from another (referenced) relation, becomes foreign key if it refers to the primary key of referenced relation. The referencing relation is called Foreign Relation. In some cases, foreign key can take NULL value if it is not the part of primary key of the foreign table. The relation in which the referenced primary key is defined is called primary relation or master relation.

References:

• NCERT Class 11