Practical 1 – Relational Model
1. Use the following relational database schema for recording the information shown below.
Primary keys are underlined.
Employee (EmpNo: varchar(20), fname:char(20), lname:char(20),
address:varchar(40),salary:integer, DeptNo:varchar(20))
Department (DeptNo:varchar(20),DeptName:char(20),Location:varchar(20))
Project (ProjNo:char(5), Project_Name:varchar(20), DeptNo:varchar(20))
Works_On(EmpNo:integer, ProjNo:char(5),DateWorked:date,Hours:number(2))
2. Insert the following data.
EMPLOYEE
EmpNo fname lname address salary DeptNo
Emp01 John Scott Mysore 45000 003
Emp02 James Smith Bangalore 50000 005
Emp03 Edward Hedge Bangalore 65000 002
Emp04 Santhosh Kumar Delhi 80000 002
Emp05 Veena M Mumbai 45000 004
DEPARTMENT
DeptNo DeptName Location
001 Accounts Bangalore
002 IT Mumbai
003 ECE Mumbai
004 ISE Mumbai
005 CSE Delhi
, PROJECT
ProjNo Project_Name DeptNo
P01 IOT 005
P02 Cloud 005
P03 BankMgmt 004
P04 Sensors 003
P05 BigData 002
WORKS_ON
EmpNo ProjNo DateWorked Hours
Emp02 P03 02-OCT-2018 4
Emp01 P02 22-JAN-2014 13
Emp02 P02 19-JUN-2020 15
Emp02 P01 11-JUN-2020 10
Emp01 P04 08-FEB-2009 6
EMP02 P01 18-OCT-2018 18
Emp01 P05 02-SEP-2011 7
3. Answer the queries given below, using the tables in your database.
a. Find the names and addresses of employees assigned to the IT department.
b. Display the current salary of each employee, along with all the unique salary amounts.
c. List the names of employees from department 005 who spend more than 10 hours per week on
project P01.
d. Generate a list showing which projects each employee is working on, sorted by their
department number, and then by last name and first name in alphabetical order within each
department.
e. Calculate and show the new salaries of employees working on the ‘IOT’ project, assuming each
of them receives a 10% salary increase.
1. Use the following relational database schema for recording the information shown below.
Primary keys are underlined.
Employee (EmpNo: varchar(20), fname:char(20), lname:char(20),
address:varchar(40),salary:integer, DeptNo:varchar(20))
Department (DeptNo:varchar(20),DeptName:char(20),Location:varchar(20))
Project (ProjNo:char(5), Project_Name:varchar(20), DeptNo:varchar(20))
Works_On(EmpNo:integer, ProjNo:char(5),DateWorked:date,Hours:number(2))
2. Insert the following data.
EMPLOYEE
EmpNo fname lname address salary DeptNo
Emp01 John Scott Mysore 45000 003
Emp02 James Smith Bangalore 50000 005
Emp03 Edward Hedge Bangalore 65000 002
Emp04 Santhosh Kumar Delhi 80000 002
Emp05 Veena M Mumbai 45000 004
DEPARTMENT
DeptNo DeptName Location
001 Accounts Bangalore
002 IT Mumbai
003 ECE Mumbai
004 ISE Mumbai
005 CSE Delhi
, PROJECT
ProjNo Project_Name DeptNo
P01 IOT 005
P02 Cloud 005
P03 BankMgmt 004
P04 Sensors 003
P05 BigData 002
WORKS_ON
EmpNo ProjNo DateWorked Hours
Emp02 P03 02-OCT-2018 4
Emp01 P02 22-JAN-2014 13
Emp02 P02 19-JUN-2020 15
Emp02 P01 11-JUN-2020 10
Emp01 P04 08-FEB-2009 6
EMP02 P01 18-OCT-2018 18
Emp01 P05 02-SEP-2011 7
3. Answer the queries given below, using the tables in your database.
a. Find the names and addresses of employees assigned to the IT department.
b. Display the current salary of each employee, along with all the unique salary amounts.
c. List the names of employees from department 005 who spend more than 10 hours per week on
project P01.
d. Generate a list showing which projects each employee is working on, sorted by their
department number, and then by last name and first name in alphabetical order within each
department.
e. Calculate and show the new salaries of employees working on the ‘IOT’ project, assuming each
of them receives a 10% salary increase.
