DBMS-Assignment 2

DBMS-Assignment 1

                                    Database Management Systems

Assignment 2 

Objectives :-To create more than one table, with referential integrity constraint, PK constraint.

The integrity constraints help us to enforce business rules on data in the database. Once an integrity constraint is specified for a table or a set of tables, all data in the table always conforms to the rule specified by the integrity constraint.

Referential integrity constraints designates a column or grouping of columns in a table called child table as a foreign key and establishes a relationship between that foreign key and specified primary key of another table called parent table.

The following is the list of constraints that can be defined for enforcing referential integrity.

1. Primary key: Designates a column or combination of columns as primary key.

Syntax: PRIMARY KEY (columnname[,columnname])

2. Foreign key: designates a column or grouping of columns as a foreign key with a table constraint.

Syntax: FOREIGN KEY (columnname[,columnname])

3. References: Identifies the primary key that is referenced by a foreign key. If only parent table name

is specified it automatically references primary key of parent table.

Syntax: columnname datatype REFERENCES tablename[(columnname[,columnname])

4. On delete Cascade: The referential integrity is maintained by automatically removing dependent

foreign key values when primary key is deleted.

Syntax: columnname datatype REFERENCES tablename[(columnname)][ON DELETE CASCADE]

5. On update set null: If set, makes the foreign key column NULL, whenever there is a change in the

primary key value of the referred table.

Syntax: Constraint name foreign key column-name references referred-table(column-name) on update

set null

Assignments :-

Set A

Consider the following entities and their relationships by giving .Create a RDB in 3 NF for the following and create it.

1 Hospital(hno int ,name varchar, city varchar)

Doctor(dno int , dname varchar, city varchar)

Relationship A many-many relationship between hospital & doctor.

Queries:-

Create table Hospital(hno int primary key , name varchar(50), city varchar(10));

Create table Doctor( dno int primary key, dname varchar(50) , city varchar(10));

Create teable Doc_Hosp(hno int references Hospital, dno int references Doctor, constraint pkey primary key(hno,dno));

2 Patient(pno int ,name varchar ,address text)

Bed(bedno int , roomno int ,description varchar)

Relationship A one-one relationship between patient and bed.

Queries:-

Create table Patient(pno int primary key , name varchar(50), address text);

create table Bed(bedno int primary key , roomno int , desc varchar(100), pno int references Patient unique);

3..Employee (empno int, name varchar, address text , city varchar, deptname varchar)

Project (pno int , pname varchar , status )

Employee and Project are related with many-to-many relationship with attribute - no of days

employee worked on that project.

Constraints: Primary key

Queries:-

Create table Employee(empno int primary key , name varchar(50), address text, deptname varchar(50));

Create table Project(pno int primary key, pname varchar(50), status boolean );

Create table Emp_Proj (empno int references Employee , pno int references roject , no_of_days int , constraint pkey primary key(empno,pno));

Set B :

1. Book (bookno int, name varchar , pubname varchar)

Author (authorno int , author_name varchar)

The relation between book & author is many-to-many with descriptive attribute date_of_publication.

Constraints: Primary Key

Author name & publisher name should not be null.

Query:-

Create table Book (bookno int primary key , name varchar(100), pubname varchar(100) NOT NULL);

Create table Author(authorno int primary key , author_name varchar(100) NOT NULL);

Create table Book_Author(bno int references Book , authorno int references Author , date_of_pub date , Constraint pkey PRIMARY KEY(bno,authorno));

2.Consider the following Bus transport system .many buses run on one route .Drivers are allotted to the

buses shiftwise.

Tables :

Bus ( Bus_no int ,capacity int ,depot_name varchar(20))

Route (Route_no int ,source varchar(20),destination varchar(20),no_of_stations int)

Driver(Driver_no int,driver_name varchar(20), licence_no int ,address varchar(20) ,age int ,salary

float)

Relationship :

Bus-Route : M-1

Bus-Driver : M-m with descriptive attribute date _of _duty allotted

Constraint :

1.Licence no is unique

2. Bus capacity is not null

3.Shift can be (Morning (1)or Evening (0))

Queries:-

Create table Route(Route_no int primary key ,source varchar(20),destination varchar(20),no_of_stations int );

Create table Bus ( Bus_no int primary key ,capacity int NOT NULL ,depot_name varchar(20), Route_no int references Route(Route_no));

Create table Driver (Driver_no int primary key ,driver_name varchar(20), licence_no int unique, address varchar(20) ,age int ,salary float);

Create table Bus_Driver(Bus_no int references Bus,Driver_no int references Driver, date _of _duty date , constraunt pke primary key (Bus_no, Driver_no));