In this tutorials we will teach Relational Database Management System step by step with examples
drop database home
create database home
use home
One to One
create table member(
id varchar(5) primary key,
name varchar(25) not null,
nic varchar(25) unique,
contact varchar(20) unique
)
create table bike(
number varchar(10) primary key,
model varchar(20) not null,
member_id varchar(5) not null,
constraint unique(member_id),
constraint foreign key(member_id) references member(id)
)
insert into member values(‘M001′,’John’,’323234V’,’233323′)
insert into member values(‘M002′,’Peter’,’5666234V’,’2332323′)
insert into member values(‘M003′,’James’,’676777V’,’2313323′)
insert into bike values(‘gpA-11′,’Moda’,’M001′)
insert into bike values(‘gpA-12′,’Hondaa’,’M002′)
insert into bike values(‘gpA-14′,’Honda’,’M003′)
One to Many
create table member(
id varchar(5) primary key,
name varchar(25) not null,
nic varchar(25) unique,
contact varchar(20) unique
)
create table bike(
number varchar(10) primary key,
model varchar(20) not null,
member_id varchar(5) not null,
constraint foreign key(member_id) references member(id)
)
insert into member values(‘M001′,’John’,’323234V’,’233323′)
insert into member values(‘M002′,’Peter’,’5666234V’,’2332323′)
insert into member values(‘M003′,’James’,’676777V’,’2313323′)
insert into bike values(‘gpA-11′,’Moda’,’M001′)
insert into bike values(‘gpA-12′,’Moda’,’M001′)
insert into bike values(‘gpA-13′,’Hondaa’,’M002′)
insert into bike values(‘gpA-14′,’Hondaa’,’M002′)
Many to Many
create table member(
id varchar(5) primary key,
name varchar(25) not null,
nic varchar(25) unique,
contact varchar(20) unique
)
create table bike(
number varchar(10) primary key,
model varchar(20) not null
)
create table member_bike(
member_id varchar(5) not null,
bike_number varchar(15) not null,
constraint foreign key(member_id) references member(id),
constraint foreign key(bike_number) references bike(number),
constraint primary key(member_id,bike_number)
)
insert into member values(‘M001′,’John’,’323234V’,’233323′)
insert into member values(‘M002′,’peter’,’565656V’,’223111′)
insert into member values(‘M003′,’jim’,’562321V’,’2231511′)
insert into bike values(‘MM-001′,’Hondab’)
insert into bike values(‘MM-002′,’auxisb’)
insert into member_bike values(‘M001′,’MM-001’)
insert into member_bike values(‘M001′,’MM-002’)
insert into member_bike values(‘M002′,’MM-001’)
insert into member_bike values(‘M002′,’MM-002’)
insert into member_bike values(‘M003′,’MM-001’)
insert into member_bike values(‘M003′,’MM-002’)