EXE 12 - Part 1 - To create more than one table, with referential integrity constraint, PK constraint

1. Create tables for the information given below by giving appropriate integrity constraints         as specified.

 Create the following tables :

Table Name Property

Columns	Column Name	Column Data Type	Constraints
1	Pnumber	Integer	Primary key
2	description	varchar (50)	Not NULL
3	Area	Char(10)

Table Name Owner

Columns	Column Name	Column Data Type	Constraints
1	Owner-name	Varchar(50)	Primary key
2	Address	varchar (50)
3	Phoneno	Integer

 // [4 byte capacity -2147483648 to +2147483647]

Relationship - A one-many relationship between owner & property. Define reference keys
accordingly.

Ans.

1]
create table owner(
oname varchar(50) primary key,
address varchar(50),
phoneno bigint
);

create table property(
pnumber integer primary key,
description varchar(50) not null,
area char(10),
oname varchar(50),
foreign key (oname) references owner(oname) on delete cascade
);


insert into owner values('dee','pune',9876451395);
insert into property values(1,'rural','pune','dee');
insert into property values(2,'rural','satara','dee');

select *from owner;
select *from property;
****************************************

2. Create the following tables :

Table Name Hospital 

Columns	Column Name	Column Data Type	Constraints
1	Hno	Integer	Primary key
2	Name	varchar (50)	Not null
3	City	Char(10)

 

Table Name Doctor 

Columns	Column Name	Column Data Type	Constraints
1	Dno	Integer	Primary key
2	Dname	varchar (50)
3	City	Char(10)

Relationship- A many-many relationship between hospital & doctor.

Ans.

1]
create table hospital(
hno integer primary key,
name varchar(50) not null,
city char(10)
);

create table doctor(
dno integer primary key,
dname varchar(50),
city char(10)
);
create table hos_doc(
hno integer references hospital(hno),
dno integer references doctor(dno)ON DELETE CASCADE ON UPDATE CASCADE,
primary key(hno,dno)
);

insert into hospital values(11,'abc','pune');
insert into  hospital values(22,'abc1','pune');
insert into  hospital values(33,'pqr','nashik');

insert into  doctor values(102,'x','pune');
insert into doctor values(103,'y','nashik');
insert into  doctor values(104,'z','nagar');

insert into  hos_doc values(11,102);
insert into  hos_doc values(11,103);
insert into  hos_doc values(22,103);
insert into  hos_doc values(22,104);


select *from hospital;
select *from doctor;
select *from hos_doc;

****************************************

3. Create the following tables :

Table Name Patient

Columns	Column Name	Column Data Type	Constraints
1	pno	Integer	Primary key
2	Name	varchar (50)	Not null
3	Address	varchar (50)

 Table Name Bed 

Columns	Column Name	Column Data Type	Constraints
1	Bedno	integer	Primary key
2	Roomno	integer	Primary key
3	description	Varchar(50)

 

Relationship a one–to-one relationship between patient & bed.

Ans.

create table bed(
bedno int,
roomno integer ,
description varchar(50),
primary key(bedno)
);

create table patient(
pno integer primary key,
name varchar(50) ,
address varchar(50),
bedno int unique  ,
 FOREIGN key (bedno) references bed(bedno)
);

insert into bed values(101,11,'abcd');
insert into bed values(102,33,'abcd');

insert into patient values(1,'x','pune',101);
insert into patient values(2,'y','pun e',102);

select *from bed ;
select *from patient;