CS403 Assignment No 4, Database management System Discussion Ideas 21st July 2014

[Vuhelper]

CS403 Assignment No 4, Database management System Discussion Ideas Last date 21st July 2014


The objective of this assignment is
Giving the idea of the most commonly used RDBMS for designing physical database systems i.e. SQL SERVER.
It is used to design systems physically either through Query Analyzer OR Enterprise Manager.
To familiarize the students with the use of Query Analyzer / Enterprise Manager.
Important things to consider before doing this assignment:
1. This Assignment is the continuation of third Assignment. Taking in consideration the same case study and same database of wireless adhoc network.
2. Now you have to complete your next phase of the Database Development Process which is physical database design.
3. You have to insert rows in the table and write an SQL query in Query Analyzer.
Task Description:
In this assignment you will focus only on the tasks given below:
Noe_Info
Node_id Node_role Protocol_id
01 Source 11
02 Source 12
03 Destination 13
04 Destination 14
Protocol_Info
Protocol_id Protocol_name Protocol_version
11 AODV 2.1
12 DSR 1.1
13 DSDV 1.5
14 AODV 1.1
15 DSR 1.2

1. Open the Query Analyzer/SQL server management studio, and perform the below tasks in already created database (Assignment#03). If you did not create a database for assignment#03 then creates a database along with all tables and entity integrity constraints as mentioned in assignment#03 file.
2. Insert above rows in each table i.e. Node_info and Protocol_info.
After inserting rows, Perform the below tasks:
3. Write an SQL query to apply Right outer join between table Node_Info and Protocol_Info and show the result.
4. Write an SQL query to apply inner join between table Node_Info and Protocol_Info and show the result.
5. Write an SQL query to apply subquery (nested query) between table Node_Info and Protocol_Info , which shows the following results;
Protocol_id Protocol_name Protocol_version
11 AODV 2.1
6. Take screenshots of your work, and paste them in your solution file (.DOC file).
Hint: You can use aggregate function i.e. max function to perform the 5th task.
Note: Use all concepts and techniques you have studied so far discussed particularly in lectures 28-32