Here is the Correct Solution File of Assignment No. 03 SEMESTER Spring 2021 CS403- Database Management System.
Note:
Please Don't copy and paste the assignment directly rename your file name and change font size and font style.
a) a) Map the ER diagram to Relational data model and link the relations through primary key and
foreign key.
Visitor(vis_ID(PK),vis_name,vis_email,vis_Gender,vis_contact,vis_address,vis_CNIC,emp_ID)
stu_visitor(stu_ID(FK),vis_ID(FK))
Admin(emp_ID,Ad_name,ad_email,ad_Gender,ad_contact)
Feevoucher(vo_ID(PK),fdate,details,amount,stu_ID(FK),emp_ID)
Attendence(att_ID(PK),adate,atime,hw_ID(FK),bill_ID(FK))
Student(stu_ID(PK),stu_name,stu_email,stu_Gender,stu_contact,stu_address,stu_CNIC,stu_roomallocated,visitorsDetails)
Complaints(comp_ID(PK),type,cdate,ctime,Details,stu_ID(FK),hw_ID(FK))
MessBill(bill_ID(PK),bdate,btime,Amount,emp_ID)
HostelStaff(emp_ID,hs_name,hs_email,hs_Gender,hs_contact,hs_role)
HostelWarden(hw_ID(PK),hw_name,hw_email,hw_Gender,hw_contact,hw_address)
b b) After mapping ERD to relational data model, you are required to write the SQL commands for each of the following statements.
1. 1) Create a table named MessBill. You also have to define primary key and foreign key (if any).
CREATE TABLE MessBill (
bill_ID int NOT NULL,
Date date,
Time time(7),
emp_ID int,
PRIMARY KEY
(bill_ID),
FOREIGN KEY
(emp_ID) REFERENCES Admin(emp_ID)
);
2. 2) Change the name of column from Amount to billAmount in MessBill table.
sp_renameMessBillAmount’ , ‘billAmount’;
3. 3) Insert one record in Admin table having data against each column (emp_ID: emp01, Ad_name: Ahmad, ad_Gender: Male, ad_email: ahmad@gmail.com, ad_contact: 03001234567).
INSERT INTO Admin
(emp_ID,Ad_name,ad_Gender,ad_email,ad_contact)
VALUES
(‘emp01’,’Ahmad’,’Male’,’ahmad@gmail.com’,03001234567);
4. 4) Delete all rows of table named Student.
DELETE FROM STUDENT;
5. 5) Show the student id and student name of all female students.
SELECT stu_ID,stu_name FROM student WHERE
stu_Gender=’female’;
0 Comments