PART-II: MCS-043 Question 1: (a) Create an appropriate database using Oracle for a study centre management system. (Perform Normalization till the required levels) (10 Marks) (b) Perform following queries using SQL: (5 Marks) (i) Find the name of the programmes those are been activated in the given study centre. (ii) Find the no of students in BCA programme for the current year. (iii) Display the corresponding Regional Centre Code if the study centre code is given. (iv) Display the list of all the academic counsellors of MCA programme. (v) Display the total no. of assignments received in the current session for the MCSL-045 course. (c) Write appropriate triggers, exceptions and functions for the above study centre management system database schema and describe them briefly. (5 marks)
Quote:Create database MCAAM; Use MCAAM; 1 NF Normalization: Create table MCAAssignmentMangement( Student_enrolment bigint, Student_name varchar(100), Student_marks int, Assignment_id varchar(100), Assignment_name varchar(100), Assignment_code varchar(50), Assignment_counsellor varchar(100), Semester int, Counsellor_Evaluating int, Counsellor_name varchar(100), Studycenter_id int, Studycenter_name varchar(100) ); select * from MCAAssignmentMangement; 2nd NF Normalization: Create Table MCAAssignmentDetials( Student_enrolment bigint, Student_marks int, Assignment_name varchar(100), Assignment_code varchar(50), Assignment_counsellor varchar(100), Studycenter_id int, Studycenter_name varchar(100) ); 3Rd NF Normalization: Create table AssignmentDetials( Student_enrolment bigint, Student_marks int, Assignment_code varchar(50), Assignment_counsellor varchar(100), Studycenter_id int ); i) SELECT COUNT(Assignment_code) FROM MCAAssignmentMangement WHERE Assignment_code>0; ii) Select Assignment_counsellor from MCAAssignmentMangement Where Counsellor_Evaluating>1; iii) Select Student_enrolment, Student_name from MCAAssignmentMangement where Student_marks > 40 AND Semester = 1; iv) Select Student_enrolment, Student_name from MCAAssignmentMangement where Student_marks >= 90% AND Assgnemt_code=MCS011; v) SELECT COUNT(*) FROM MCAAssignmentMangement WHERE Assignment_code = MCSL045;
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)
Submit an article or tip