DBMS Lab Cycle 5

   5. The schema of book store database is as follows

customer (cust_id, cust_name, cust_phone)

book (book_id, title, auth_name, unit_price, pub_name, pub_year)

sale (sale_id, cust_id, book_id, order_date, quantity)

author(auth_name, country)

publisher(pub_name, pub_addr)

first attribute in each table is the primary key, foreign keys have same name as primary key.

i. Calculate the sales for a financial year 2021-22? 

ii. Display publisher wise total price of books published, except for a particular publisher PHI

iii. Write the SQL Query to Find the publisher wise total price of books published

iv. Write the SQL Query to display the name of the customer and title of the books purchased by them.

v.Find the title of books with author name and country published in year 2021;

vi. Get the titles and author name of all books written by authors not living in india.

vii.Write the SQL Query to display the cusomer ids and the total quantity of books purchased by them in the decreasing order of total quantity purcased.

TEST CASES

mysql> select * from customer;

+---------+-----------+------------+

| cust_id | cust_name | cust_phone |

+---------+-----------+------------+

|     100 | hari      |      12345 |

|     101 | john      |      23456 |

|     102 | ali       |      34567 |

+---------+-----------+------------+

mysql> select * from author;

+-----------+---------+

| auth_name | country |

+-----------+---------+

| Brown     | US      |

| Forouzan  | US      |

| Navathe   | India   |

| Samantha  | India   |

| Tanenbaum | US      |

| V Prasad  | India   |

+-----------+---------+

mysql> select * from publisher;

+----------+----------+

| pub_name | pub_addr |

+----------+----------+

| Pearson  | London   |

| PHI      | Delhi    |

| TMH      | UP       |

+----------+----------+

mysql> select * from book;

+---------+--------+-----------+------------+----------+----------+

| book_id | title  | auth_name | unit_price | pub_name | pub_year |

+---------+--------+-----------+------------+----------+----------+

|    2001 | DS     | Samantha  |        500 | PHI      |     2020 |

|    2002 | CP     | V Prasad  |        300 | TMH      |     2019 |

|    2003 | DBMS   | Navathe   |        900 | Pearson  |     2018 |

|    2004 | DC     | Forouzan  |        700 | TMH      |     2021 |

|    2005 | CN     | Tanenbaum |        800 | Pearson  |     2018 |

|    2006 | Python | Brown     |        800 | TMH      |     2021 |

+---------+--------+-----------+------------+----------+----------+

mysql> select * from sale;

+---------+---------+---------+------------+----------+

| sale_id | cust_id | book_id | order_date | quantity |

+---------+---------+---------+------------+----------+

|     301 |     100 |    2001 | 2021-01-02 |        3 |

|     302 |     100 |    2002 | 2019-04-02 |        4 |

|     303 |     100 |    2003 | 2019-04-02 |        2 |

|     304 |     101 |    2002 | 2021-05-02 |        2 |

|     305 |     101 |    2005 | 2021-05-02 |        1 |

|     306 |     102 |    2006 | 2022-01-02 |        2 |

+---------+---------+---------+------------+----------+

DBMS Lab Cycle 4

 Create view for following. 

i.            View (student_info) for student table(exp no:2) containing studentNo, studentName, department, address

ii.            View (student_mark) for student table(exp no:2) containing studentNo, studentName, maths, physics, chemistry, cProgramming.

iii.            Display the studentNo, studentName, totalMarks, address from student_info view and student_mark view without referring student table.  

iv.            Create a view (emp_dept) for exp no:3 containing employee name and department name. (NULL values are expected in the department name field for the employees having invalid department numbers).

  v.            Find the name of employees having invalid department numbers by referring the emp_dept view.

DBMS Lab Cycle 3

 3. Consider the following tables namely “Department” and “Employees”

Their schemas ar e as follows

dept (deptNO, deptName, deptLocation)

emp (empId, empName , deptNo, salary)

Where deptNo is the primary key in dept table and empId is primary key in emp table

Where deptNo in Employees is NOT a foreign key.

i. Display the employee name along with their department name (no need to display the

employees with invalid department details)

ii. There are some invalid department numbers (present in employee table but are not

existing in department table), display all employees (along employee name employee id

and their department number) who are part of such invalid department numbers .

iii. Display all the department numbers available with the department and employee tables

avoiding duplicates

iv. Display department numbers and name of all the departments where no employee exists.

v. Display department number, employee id and employee name of employees working at

location Cochin

vi. Display the details of those who draw the salary greater than the average salary of

employee’s from sales department

vii. The names of employees whose salary is greater than the salary of all the employees in

department 1003

viii. Give all employees in the 'Sales' department a 20% raise i n salary.

ix. Retrieve the ids of employees getting salary greater than the average
salary of their department.

x. For each department that has more than 1 employee, retrieve the
department-No and the number of employees getting salary more
than Rs. 40000.

 

TEST CASES

mysql> select * from emp;

+-------+---------+--------+--------+

| empId | empName | deptNo | salary |

+-------+---------+--------+--------+

|  2001 | Hari    |   1006 |  30000 |

|  2002 | Devi    |   1002 |  40000 |

|  2003 | Gupta  |   1003 |  50000 |

|  2004 | Ravi    |   1004 |  60000 |

|  2005 | Rani    |   1007 |  70000 |

|  2006 | John    |   1003 |  40000 |

+-------+---------+--------+--------+

6 rows in set (0.39 sec)

mysql> select * from dept;

+--------+-------------+--------------+

| deptNo | deptName    | deptLocation |

+--------+-------------+--------------+

|   1001 | HR                   | Calicut      |

|   1002 | Marketing        | Cochin       |

|   1003 | Sales                | Trivandrum   |

|   1004 | Testing             | Bangalore    |

|   1005 | Development   | Bangalore    |

+--------+-------------+--------------+

5 rows in set (0.01 sec)

NS2 - Program 4

 #  TCP Variant Tahoe

# ========================

#

#     (0)tcp0,ftp0     (5)tcpSink0

# \   /

#   \2Mb /2Mb

# 2Mb    \   3Mbps /   2Mb

# (1)---------(3)---------------(4)------------(6)tcpSink1

#   tcp1,ftp1  / \

#   /2Mb \2Mb

# /   \

#    (2)tcp2,ftp2     (7)tcpSink2

#   

#

# [.001s]

#  tcp0 [1000b]----------> tcpsink0

# [.001s]

#  tcp1 [1000b]----------> tcpsink1

# [.001s]

#  tcp2 [1000b]----------> tcpsink2

#

#  All 10ms DropTail

# 

set ns [new Simulator]

set f0 [open tahoe0.tr w]

set f1 [open tahoe1.tr w]

set f2 [open tahoe2.tr w]

set nr [open taho.tr w]

$ns trace-all $nr

set nf [open taho.nam w]

$ns namtrace-all $nf

#Define a 'finish' procedure

proc finish {} {

global ns nr nf f0 f1 f2

$ns flush-trace

   #Close the NAM trace file

   close $nf

close $nr

close $f0

close $f1

close $f2

exec nam taho.nam &

exec xgraph tahoe0.tr tahoe1.tr tahoe2.tr -bg white -t ConjestionWindowAnalysis_Tahoe -x Seconds -y ConjestionWindowSize

   exit 0

}

#for{set i 0} {$i<12} {incr i}{

#set n($i) [$ns node]

#}

set n0 [$ns node]

set n1 [$ns node]

set n2 [$ns node]

set n3 [$ns node]

set n4 [$ns node]

set n5 [$ns node]

set n6 [$ns node]

set n7 [$ns node]

#creating links

#for {set i 0} {$i<9}{incr i}{

#$ns duplex-link $n($i) $n([expr$i+1]) 1Mb 10ms DropTail

#} 

$ns duplex-link $n0 $n3 2Mb 10ms DropTail

$ns duplex-link $n1 $n3 2Mb 10ms DropTail

$ns duplex-link $n2 $n3 2Mb 10ms DropTail

$ns duplex-link $n3 $n4 3Mb 10ms DropTail

$ns duplex-link $n4 $n5 2Mb 10ms DropTail

$ns duplex-link $n4 $n6 2Mb 10ms DropTail

$ns duplex-link $n4 $n7 2Mb 10ms DropTail

$ns duplex-link-op $n0 $n3 orient right-up

$ns duplex-link-op $n1 $n3 orient middle

$ns duplex-link-op $n2 $n3 orient right-down

$ns duplex-link-op $n3 $n4 orient middle

$ns duplex-link-op $n4 $n5 orient right-up

$ns duplex-link-op $n4 $n6 orient middle

$ns duplex-link-op $n4 $n7 orient right-down

#create a tcp agent and attach it to nodes

set tcp0 [new Agent/TCP]

$ns attach-agent $n0 $tcp0

set ftp0 [new Application/FTP]

$ftp0 attach-agent $tcp0

$ftp0 set packetSize_ 1000

$ftp0 set interval_ 0.001

set tcpsink0 [new Agent/TCPSink]

$ns attach-agent $n5 $tcpsink0

$ns connect $tcp0 $tcpsink0

set tcp1 [new Agent/TCP]

$ns attach-agent $n1 $tcp1

set ftp1 [new Application/FTP]

$ftp1 attach-agent $tcp1

$ftp1 set packetSize_ 1000

$ftp1 set interval_ 0.001

set tcpsink1 [new Agent/TCPSink]

$ns attach-agent $n6 $tcpsink1

$ns connect $tcp1 $tcpsink1

set tcp2 [new Agent/TCP]

$ns attach-agent $n2 $tcp2

set ftp2 [new Application/FTP]

$ftp2 attach-agent $tcp2

$ftp2 set packetSize_ 1000

$ftp2 set interval_ 0.001

set tcpsink2 [new Agent/TCPSink]

$ns attach-agent $n7 $tcpsink2

$ns connect $tcp2 $tcpsink2

#setting colour

$tcp0 set fid_ 1 #to set flow ID

$tcp1 set fid_ 2

$tcp2 set fid_ 3

#flow ID 1 --->RED

$ns color 1 Red

$ns color 2 Green

$ns color 3 Blue

proc record {} {

global ns tcp0 tcp1 tcp2 f0 f1 f2

set time 0.01

set wnd0 [$tcp0 set cwnd_]

set wnd1 [$tcp1 set cwnd_]

set wnd2 [$tcp2 set cwnd_]

set timenow [$ns now]

puts $f0 "$timenow $wnd0"

puts $f1 "$timenow $wnd1"

puts $f2 "$timenow $wnd2"

$ns at [expr $timenow + $time] "record"

}

$ns at 0.1 "record"

$ns at 0.5 "$ftp0 start"

$ns at 1.0 "$ftp1 start"

$ns at 1.5 "$ftp2 start"

$ns at 25.0 "$ftp0 stop"

$ns at 25.5 "$ftp1 stop"

$ns at 26.0 "$ftp2 stop"

$ns at 27.0 "finish"

$ns run

NS2 - Program 3

 #    pgm3DV.tcl 

#    (6)-------(5)null0,null1

#    /     /  \

#   /        /    \ 

# (7) (11) (4)

#     / / \   \

#    / / \    \

#   (8) (9) (10) (3)

#     \ /      \ | 

#      \  /         \ (2)

#       \/        \ /

#     cbr0(0)------------(1)cbr1

# |Time| State | Nodes |

# |====| ===== | ===== |

# | 10 | Down  | 11-05 |

# | 13 | Down  | 07-06 |

# | 20 |  Up   | 07-06 |

# | 30 |  Up   | 11-05 |

# _______________________

# All 1Mb 10ms DropTail

#

set ns [new Simulator]

$ns rtproto DV

set nf [open trace1.tr w]

$ns trace-all $nf

set nm [open out1.nam w]

$ns namtrace-all $nm

#Define a 'finish' procedure

proc finish {} {

global ns nf nm

$ns flush-trace

#Close the NAM trace file

close $nf

close $nm

exec nam out1.nam 

exit 0

}

#for{set i 0} {$i<12} {incr i}{

#set n($i) [$ns node]

#}

set n0 [$ns node]

set n1 [$ns node]

set n2 [$ns node]

set n3 [$ns node]

set n4 [$ns node]

set n5 [$ns node]

set n6 [$ns node]

set n7 [$ns node]

set n8 [$ns node]

set n9 [$ns node]

set n10 [$ns node]

set n11 [$ns node]

#creating links

#for {set i 0} {$i<9}{incr i}{

#$ns duplex-link $n($i) $n([expr$i+1]) 1Mb 10ms DropTail

#} 

$ns duplex-link $n0 $n1 1Mb 10ms DropTail

$ns duplex-link $n1 $n2 1Mb 10ms DropTail

$ns duplex-link $n2 $n3 1Mb 10ms DropTail

$ns duplex-link $n3 $n4 1Mb 10ms DropTail

$ns duplex-link $n4 $n5 1Mb 10ms DropTail

$ns duplex-link $n5 $n6 1Mb 10ms DropTail

$ns duplex-link $n6 $n7 1Mb 10ms DropTail

$ns duplex-link $n7 $n8 1Mb 10ms DropTail

$ns duplex-link $n0 $n9 1Mb 10ms DropTail

$ns duplex-link $n10 $n11 1Mb 10ms DropTail

$ns duplex-link $n9 $n11 1Mb 10ms DropTail

$ns duplex-link $n1 $n10 1Mb 10ms DropTail

$ns duplex-link $n11 $n5 1Mb 10ms DropTail

$ns duplex-link $n8 $n0 1Mb 10ms DropTail

#create a udp agent and attach it to node n0

set udp0 [new Agent/UDP]

$ns attach-agent $n0 $udp0

set udp1 [new Agent/UDP]

$ns attach-agent $n1 $udp1

#Create a CBR traffic source and attach it to udp0

set cbr0 [new Application/Traffic/CBR]

$cbr0 attach-agent $udp0

$cbr0 set packetSize_ 1000

$cbr0 set interval_ 0.005

set cbr1 [new Application/Traffic/CBR]

$cbr1 attach-agent $udp1

$cbr1 set packetSize_ 1000

$cbr1 set interval_ 0.005

#create a Null agent(a traffic sink) and attach it to node n1

set null0 [new Agent/Null]

$ns attach-agent $n5 $null0

set null1 [new Agent/Null]

$ns attach-agent $n5 $null1

#link statistics

$ns rtmodel-at 10.0 down $n11 $n5

$ns rtmodel-at 30.0 up $n11 $n5

$ns rtmodel-at 13.0 down $n7 $n6

$ns rtmodel-at 20.0 up $n7 $n6

#Connect the traffic source to the sink

$ns connect $udp0 $null0

$ns connect $udp1 $null1

$ns at 5.0 "$cbr0 start"

$ns at 5.0 "$cbr1 start"

$ns at 45.0 "$cbr0 stop"

$ns at 45.0 "$cbr1 stop"

$ns at 45.1 "finish"

$ns run

======countDV.awk

#!/usr/bin/awk -f

BEGIN{

send = 0

recv = 0

drop = 0

route = 0

#print" Count calculation in Distance Vector Algorithm"

}

{

if( $1=="+" && ($3=="1"||$3=="0") && $5=="cbr")

{

send+=1

}

if( $1=="r" && $4=="5" && $5=="cbr")

{

recv+=1

}

if( $1=="d" )

{

drop+=1

}

if( $1=="r" &&  $5=="rtProtoDV")

{

route+=1

}

}

END{

print "count of send packets"

print send

print "count of received packets"

print recv

print "count of dropped packets"

print drop

print "count of routed packets"

print route

print "Normalized Overhead"

print route/recv

print "Packet Delivery"

print recv/send

}

======

ns pgm3DV.tcl 

ns pgm3LS.tcl 

awk -f countDV.awk trace1.tr > DV.dat

awk -f countLS.awk trace2.tr > LS.dat

NS2 - Program 2

 

#  

# (0)tcp0,ftp0      (4)tcpSink0

# \       /

#   \ 1Mbps     / 2Mbps

#    \ 3Mbps    /

#    (2)----------------(3)

#    /    \

#   / 1Mbps     \ 2Mbps

# / \

# (1)udp0,cbr0      (5)null0

# 

# [.005s]

#  udp0 [1000b]----------> null0

# [.005s]

#  tcp0 [1000b]----------> tcpsink0

#  All 10ms DropTail

# 

set ns [new Simulator]

set nf [open trace.tr w]

$ns trace-all $nf

set nm [open out.nam w]

$ns namtrace-all $nm

#Define a 'finish' procedure

proc finish {} {

global ns nf nm

$ns flush-trace

#Close the NAM trace file

close $nf

close $nm

exec nam out.nam 

exit 0

}

set n0 [$ns node]

set n1 [$ns node]

set n2 [$ns node]

set n3 [$ns node]

set n4 [$ns node]

set n5 [$ns node]

#creating links

$ns duplex-link $n0 $n2 1Mb 10ms DropTail

$ns duplex-link $n1 $n2 1Mb 10ms DropTail

$ns duplex-link $n2 $n3 3Mb 10ms DropTail

$ns duplex-link $n3 $n4 2Mb 10ms DropTail

$ns duplex-link $n3 $n5 2Mb 10ms DropTail

#set up a tcp, connection

set tcp0 [new Agent/TCP]

$ns attach-agent $n0 $tcp0

set ftp0 [new Application/FTP]

$ftp0 attach-agent $tcp0

$ftp0 set packetSize_ 1000

$ftp0 set interval_ 0.005

set tcpsink0 [new Agent/TCPSink]

$ns attach-agent $n4 $tcpsink0

#create a udp agent and attach it to node n1

set udp0 [new Agent/UDP]

$ns attach-agent $n1 $udp0

#Create a CBR traffic source and attach it to udp0

set cbr0 [new Application/Traffic/CBR]

$cbr0 attach-agent $udp0

$cbr0 set packetSize_ 1000

$cbr0 set interval_ 0.005

#create a Null agent(a traffic sink) and attach it to node n1

set null0 [new Agent/Null]

$ns attach-agent $n5 $null0

#Connect the traffic source to the sink

$ns connect $udp0 $null0

$ns connect $tcp0 $tcpsink0

$ns at 1.0 "$ftp0 start"

$ns at 1.1 "$cbr0 start"

$ns at 4.5 "$ftp0 stop"

$ns at 4.5 "$cbr0 stop"

$ns at 5 "finish"

$ns run

====throTCP.awk

#!/usr/bin/awk -f

BEGIN{

data = 0

#print"throughput calculation"

}

{

if( $1=="r" && $4=="4" && $5=="tcp")

{

data+=$6

print $2,data*8.0/$2/1000000

}

}

END{

#print "completed"

}

=====throUDP.awk

#!/usr/bin/awk -f

BEGIN{

data=0

#print"throughput calculation"

}

{

if( $1=="r" && $4=="5" && $5=="cbr")

{

data+=$6

print $2,data*8.0/$2/1000000

}

}

END{

#print "completed"

}

======

ns pgm2.tcl

awk -f throTCP.awk trace.tr > tcp.dat

awk -f throUDP.awk trace.tr > udp.dat

xgraph tcp.dat udp.dat -bg white -t ThroughputAnalysis -x Throughput -y Seconds

NS2 - Program 1

 Click Here to download

Program 1:

# 

# (0) tcp0, ftp0

# \

#   \ 2Mbps

#    \ 3Mbps

#    (2)----------------(3) tcpSink0,tcpSink1

#    /

#   / 1Mbps

# /

# (1) tcp1, ftp1

# 

#  tcp0 --> tcpsink0

#  tcp1 --> tcpsink1

#  All 10ms DropTail

# 

set ns [new Simulator]

set nf [open trace.tr w]

$ns trace-all $nf

set nm [open out.nam w]

$ns namtrace-all $nm

#Define a 'finish' procedure

proc finish {} {

global ns nf nm

$ns flush-trace

close $nf

close $nm

exit 0

}

set n0 [$ns node]

set n1 [$ns node]

set n2 [$ns node]

set n3 [$ns node]

#creating links

#$ns <link_type> $n0 $n1 <bandwidth> <delay> <queue­type>

$ns duplex-link $n0 $n2 2Mb 10ms DropTail

$ns duplex-link $n1 $n2 1Mb 10ms DropTail

$ns duplex-link $n2 $n3 3Mb 10ms DropTail

#set up a tcp, connection

set tcp0 [new Agent/TCP]

$ns attach-agent $n0 $tcp0

set ftp0 [new Application/FTP]

$ftp0 attach-agent $tcp0

set tcp1 [new Agent/TCP]

$ns attach-agent $n1 $tcp1

set ftp1 [new Application/FTP]

$ftp1 attach-agent $tcp1

set tcpsink0 [new Agent/TCPSink]

$ns attach-agent $n3 $tcpsink0

set tcpsink1 [new Agent/TCPSink]

$ns attach-agent $n3 $tcpsink1

$ns connect $tcp0 $tcpsink0

$ns connect $tcp1 $tcpsink1

#Schedule an event: $ns at <time> <event>

$ns at 1.0 "$ftp0 start"

$ns at 1.1 "$ftp1 start"

$ns at 5.0 "$ftp0 stop"

$ns at 5.1 "$ftp1 stop"

$ns at 5.2 "finish"

$ns run