CS403 Assignment PDF

Preview

Summary

CS403 Assignment No 3 Solution 2021 Fall. ...

Description

Assignment No 3 CS403 Database Management System Question Our third assignment is the continuation of our second assignment. The solution of the second assignment, i.e. the Entity relationship diagram is given below. This ERD will serve as an input to solve the third assignment.

a) After mapping ERD to relational data model, you are required to write the SQL commands for each of the following statements.

1. Insert one record in Employee table having data against each column: (Emp_ID: emp01, Emp_Name: Ahmad, Emp_Gender: Male , Emp_Address: Lahore, Emp_DoB: 01-01-1990, Email: [email protected], Contact#: 03001234567, Pro_ID: PRJ001)

INSERT INTO Employee (Emp_ID,Emp_Name,Emp_Gender,Emp_Address,Email,Contact#,Emp_DOB,Pro_ID) VALUES

(‘emp01’ , ‘Ahmad’ , ‘Male’ , ‘Lahore’ , ‘[email protected]’ , 0300123456, ‘1990-01-01’ , ‘PRJ001’);

2. Show the names of all Managers whose Gender is Male. Select Mgr_Name From manager Where Mgr_Gender=’Male’

3. Show the records of all Clients whose Project ID is PRJ002.

Select*from Client where pro_ID=’PRJ002’

4. Update the project id from PRJ01 to PRJ001 in Client relation.

UPDATE Client SET pro_ID = PRJ001 WHERE Pro_ID=’PRJ01’

5. Change the name of Column from Email to Cl_Email in Client relation.

CLIENT_rename ‘Client_Email’ , ‘Cl_Email’ , ‘Column;...