Database Management System (CS403)
Assignment No.4 (Graded)
Total Marks: 15
Important things to consider before doing this assignment:
Node_Info
Protocol_Info
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
Read more at http://vustudents.ning.com/group/cs403databasemanagementsystems/forum/topics/database-management-system-cs403-assignment-no-4-graded-deadline?xg_source=activity#Lt2ZMuD8QTaM4P2u.99
- This Assignment is the continuation of third Assignment. Taking in consideration the same case study and same database of wireless adhoc network.
- Now you have to complete your next phase of the Database Development Process which is physical database design.
- 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:Node_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 |
- 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.
- Insert above rows in each table i.e. Node_info and Protocol_info.
After inserting rows, Perform the below tasks:
- Write an SQL query to apply Right outer join between table Node_Info and Protocol_Info and show the result.
- Write an SQL query to apply inner join between table Node_Info and Protocol_Info and show the result.
- 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 |
- 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
Read more at http://vustudents.ning.com/group/cs403databasemanagementsystems/forum/topics/database-management-system-cs403-assignment-no-4-graded-deadline?xg_source=activity#Lt2ZMuD8QTaM4P2u.99
