RDBMS

Relational Database management System

It is relational model in which data is stored in multiple tables where tables are related to each other using keys (primary keys and foreign keys)

A relational database store and arrange the data in the tabular form like rows and columns.

Example: SQL Server, Oracle, MySQL

Application : School Database, Bank Database, Hospital Database

Key Points of RDBMS

• It is easy to use.
• The data manipulation can be done (INSERT, UPDATE, DELETE)
• It Provide Security.
• Helps into reduce redundancy (Supports Normalization)
• Support multiple users can access the database.
• It follows ACID (atomicity, consistency, isolation, and durability)

Relational Model

Types of relationship

 

        Table's are related to each other with the help of keys (primary key / Foreign key)

1. One to One relationship (1 : 1) single row of the first table can only be related to one and only one records of a second table. Similarly, the row of a second table can also be related to anyone row of the first table.
2. One to many or many to one relationship (1 : N or N : 1) - Any single rows of the first table can be related to one or more rows of the second tables, but the rows of second tables can only relate to the only row in the first table.
3. Many to many relationships (N : N) -Each record of the first table can relate to any records (number of records) in the second table. Similarly, each record of the second table can also relate to more than one record of the first table.

Data Modeling

it helps in to understand the structure, form, and relationships between different entities.

it will help you in to make decision about what data is necessary and how to collect and structure it.

Conceptual - What ?- identify entities/tables before direct implementation.

Logical - How ?- identify attributes / columns of entities their relationships.

Physical - Build / Implement ?- create the Database physically with proper relationship between tables.

Design

                       

Query for practice : 

Create Table Student 
(
Student_Id Int Identity(1,1) Primary Key,
Name Varchar(50) Not Null,
City Varchar(50) Null,
PhoneNumber Int
)
Insert Into Student(Name,City,PhoneNumber) Values ('Rahul','Pune',45454)
Insert Into Student(Name,City,PhoneNumber) Values ('Kiran','Mumbai',54545)
Insert Into Student(Name,City,PhoneNumber) Values ('Manish','Delhi',42121)
Insert Into Student(Name,City,PhoneNumber) Values ('Ketan','Pune',12112)

Select * from Student

---------------------------------------------------------
Create Table Class
(
Class_Id Int Identity(1,1) Primary Key,
ClassName Varchar(50) Not Null
)
Insert Into Class(ClassName) Values ('FY_IT')
Insert Into Class(ClassName) Values ('SY_IT')
Insert Into Class(ClassName) Values ('TY_IT')
Insert Into Class(ClassName) Values ('FY_CM')
Insert Into Class(ClassName) Values ('SY_CM')
Insert Into Class(ClassName) Values ('TY_CM')

Select * from Class
---------------------------------------------------------
CREATE TABLE Department
(
Department_Id [int] IDENTITY(1,1) Primary Key,
DepartmentName [varchar](50) NOT NULL
) 
Insert Into Department(DepartmentName) Values ('Computer')
Insert Into Department(DepartmentName) Values ('IT')
Insert Into Department(DepartmentName) Values ('Mechanical')

Select * from Department
---------------------------------------------------------
CREATE TABLE Subject
(
Subject_Id [int] IDENTITY(1,1) Primary Key,
SubjectName [varchar](50) NOT NULL
) 
Insert Into Subject(SubjectName) Values ('Basics of Comp')
Insert Into Subject(SubjectName) Values ('math')
Insert Into Subject(SubjectName) Values ('Physics')
Insert Into Subject(SubjectName) Values ('Engg Drawing')
Select * from Subject
----------------------------------------------------------
CREATE TABLE Faculty
(
Faculty_Id [int] IDENTITY(1,1) Primary Key,
FacultyName [varchar](50) NOT NULL,
City [varchar](50) NOT NULL
) 

Insert Into Faculty(FacultyName,City) Values ('Deshmukh A','Pune')
Insert Into Faculty(FacultyName,City) Values ('Jadhav D','Pune')
Insert Into Faculty(FacultyName,City) Values ('Pardeshi M','Delhi')
Insert Into Faculty(FacultyName,City) Values ('Pawar S','Pune')

Select * from Faculty
----------------------------------------------------------
-----Many to One
-----Multiple Student One Class 
-----One to Many
-----One Class Multiple Student

Create Table Student_Class 
(
Student_Class_Id Int Identity(1,1) Primary Key,
Student_ID Int Not Null foreign key References Student(Student_ID),
Class_ID Int Not Null foreign key References Class(Class_Id),
)

Insert Into Student_Class (Student_Id,Class_ID)values (1,1)
Insert Into Student_Class (Student_Id,Class_ID)values (2,1)
Insert Into Student_Class (Student_Id,Class_ID)values (3,1)
Insert Into Student_Class (Student_Id,Class_ID)values (4,1)

Select * from Student
Select * from Class
Select * from Student_Class

------Example Join
Select * from Student_Class SC
Inner Join Student S 
ON SC.Student_ID=S.Student_Id
Inner Join Class C
On C.Class_Id=SC.Class_ID

Select S.name,C.ClassName from Student_Class SC
Inner Join Student S 
ON SC.Student_ID=S.Student_Id
Inner Join Class C
On C.Class_Id=SC.Class_ID

-----Many to Many
-----Multiple Student Multiple Subject
Create Table Student_Subject 
(
Student_Subject_Id Int Identity(1,1) Primary Key,
Student_ID Int Not Null foreign key References Student(Student_ID),
Subject_Id Int Not Null foreign key References Subject(Subject_Id)
)
Insert Into Student_Subject(Student_Id,Subject_Id)values (1,1)
Insert Into Student_Subject(Student_Id,Subject_Id)values (2,1)
Insert Into Student_Subject(Student_Id,Subject_Id)values (3,1)
Insert Into Student_Subject(Student_Id,Subject_Id)values (4,1)
Insert Into Student_Subject(Student_Id,Subject_Id)values (1,2)
Insert Into Student_Subject(Student_Id,Subject_Id)values (2,2)
Insert Into Student_Subject(Student_Id,Subject_Id)values (3,2)
Insert Into Student_Subject(Student_Id,Subject_Id)values (4,2)

Select * from Student_Subject 

-----One to One
-----One Student Having One Passport & Aadhar Number

CREATE TABLE Student_Identity
(
Student_Identity_Id [int] IDENTITY(1,1) Primary Key,
PassportNumber [varchar](50) NULL,
AadharCardNumber BIGINT NULL,
Student_ID Int Not Null foreign key References Student(Student_ID) Unique
) 

Insert Into Student_Identity(PassportNumber,AadharCardNumber,Student_ID)values ('6546544JHHJDSDS',155812122121,1)
Insert Into Student_Identity(PassportNumber,AadharCardNumber,Student_ID)values ('343JNSJAJDEDOBA',554514525997,2)
Select * from Student_Identity