Docstoc

dbms Q No 1 Consider the following

Document Sample
dbms Q No 1 Consider the following Powered By Docstoc
					Q.No.1:Consider the following set of requirements for a university database that is used to keep track of students‟ transcripts. This is similar but not identical to the database shown in figure 1.
STUDENT
NAME SMITH BROWN STUDENT NUMBER 17 8 CLASS 1 2 MAJOR COSC COSC

COURSE
COURSE NAME INTO TO COMP.SCIENCE DATA STRUCTURE DATABASE DISCRETE MATH COURSE NO COSC1310 COSC3320 COSC3380 MATH2410 CREDIT HOURS 4 4 3 3 DEPARTMENT COSC COSC COSC MATH

SECTION
SECTION ID 85 92 102 112 119 135 COURSE NO MATH2410 COSC1310 COSC3320 MATH2410 COSC1310 COSC3380 SEMESTER FALL FALL SPRING FALL FALL FALL YEAR 91 91 92 92 92 92 INSTRUCTOR KING ANDERSON KNUTH CHANG ANDERSON STONE

GRADE REPORT
STUDENT NO 17 17 8 8 8 8 SECTIION ID 112 119 85 92 102 135 GRADE B C A A B A

PREREQUISITE
COURSE NO COSC3380 COSC3380 COSC3320 PERQUISITE NO COSC3320 MATH2410 COSC1310

(a) The University keeps track of each student’s name, student number, social security number, current address and phone, permanent address and phone , birth date, sex, class(freshman, sophomore……,graduate), major department,minor department(if any) and degree program (B.A,B.S….Ph.D). Some user applications need to refer to the city, state and zip of the student’s permanent address and to the student’s last name. Both social security number and student number have unique values for each student.

(b) Each department is described by a name, department code, office number ,office Phone and college. Both name and code have unique values for each department.

(c) Each course has a course name, description, course number, number of semester Hours, level and offering department. The value of course number is unique for each course.

(d) Each section has an instructor, semester, year, course and section number. the section number distinguishes different sections of the same course that are taught during the same semester/year,its values are 1,2,3……Up to the number of sections taught during each semester. (e) A grade report has a student, section, letter grade and numeric grade (0,1,2,3or4)

Design an E-R schema for this application, and draw and ER diagram for that schema. Specify key attributes or each entity type and structural constraint on each relational type. Note any unspecified requirements and make appropriate assumptions to make the specification complete. Ans:The E-R diagram for the schema can be given as follows:

Last_name Ss_no Name Student_no

First_name Degree_program Minor Major

Phone_no

Student Date of birth

Has

Address sex Street State Permanent Current City Level City State Zip Street Zip Joins

Grade_report Section_no Grade

Student_no

Letter_grade Course_name Course College Managed by Has Department Phone_no Year Office_no Dept_no Dname Section Instructor Section Number Course_no Semester Course_desc

Numeric_grad e

Prerequisit e Course_no No_of_sem
+ifsem

P no

Dept_no

Q.No.2:Map the above E-R diagram to relational model and define the schema using MS-SQL /ORACLE/DB2. Ans:Student (Ss_no, student_no, first_name, last_name, dob, phone_no, sex, major, minor, current_add, permanent_add, c_no) Course (c_no,no_sem_hrs,c_desc,c_name,level,dept_no) Grade (student_no,sec_no,n_gr,c_gr) Section (sec_no,c_no,sem_no,year,instructor) Department(dept_no,dept_name,off_ph,off_no,college_name) Prerequisite (c_no,perquisite_no) Database of the schema-using Oracle: 1. Create table student (ss_no number (4), student_no number(4), first_namevarchar2(10),last_name varchar2(10), dob date , phone_no number(10), sex varchar2(6) check (sex in (‘male’,’female’)), major varchar2(4), minor varchar2(4),current_add varchar2(40), permanent_add varchar2(40), degree_program varchar2(10), constraint c1 primary key (ss_no,student_no),c_code varchar2(8)references course (c_code));

2. Create table course (c_no varchar2(8) primary key,no_sems_hrs number(2), C_desc varchar2 (6),level number(1),dept_no varchar2(4) references department(dept_no)); 3. Create table grade_report (student_no number(4) refrences student (student_no), sec_no number(4) references section(sec_no), n_grade number(1) ,c_grade varchar2(1)); 4. Create table section (sec_no number(4) primary key ,c_no varchar2(8) references course (c_no), sem varchar2(4), year date , instructorvarchar2(25) ); 5. Create table department (dept_no varchar2 (4), dept_name varchar2(8) unique, off_no number(4), off_ph number(15), college_name varchar2(25), constraint c2 primary key (dept_no)); 6) Create table prerequisite (c_no varchar2 (8) references course (c_no), prerequesite_no varchar2(8) references course(c_no));

Q.No.3:Define student view/teacher view/academic section view and write sub schema for these views . Ans: Student View: Create view vt_student as select s.ss_no, s.student_no, s.first_name, s.last_name, c.c_no, sc.instructor, d.dept_no from student s,course c, section sc where s.c_code= c.c_code and c.c_no=sc.c_no and s.major=d.dept_no and s.minor=d.dept_no and s.s_no=’&enter student number:’; Teacher‟s View: Create view vt_teacher as select sc.instructor , s.first_name,c.c_name From student s, course c.section sc where c.c_no=sc.c_no and c.c_no=s.c_no and sc.instructor=’&enter teacher’s name:’; Academic View: Create view ac_view as select c.c_name,sc.sec_no,sc.instructor, d.dept_name,count(*) from course c ,section sc, department d where c.dept_no =d.dept_no and c.c_no =sc.c_no group by c.c_no,c.c_name,sc.instructor,d.dept_name;

Q.No.4:Express integrity constraints in SQL for the above schema. Ans:Student: Since student_no and ss_no can uniquely determine the Whole tuple.(student no ,ss_no) are the composite primary key for student table.c_code is the foreign key for student referencing course table. Course: c_no is unique identifier for the course; hence it is primary key of Course table while dept_no is used as the foreign key member to Refer departmental details of particular course. Department: Dept_no is the primary key while dept_name has unique Constraint i.e department name is unique. Section : sec_no is the primary key.c_no is the foreign key referencing Course table. Grade_report :No such primary key.student_no and sec_no are the foreign Key referencing student and section table. Prerequisite :Both attribute c_no and prerequisite_no are foreign key Referencing course table.

Q.No.-5:a) Specify the following queries in SQL on the database schema as shown below: STUDENT Name Student Number Class Major

COURSE Course Name Course Number Credit Hours Department

PREREQUSITE Course Number SECTION Section Identifier Course Number Semester Year Instructor Perquisite Number

GRADE REPORT Student number Section Identifier Grade

Database creation of the above database schema is as follows: 1) Create table student (name varchar2 (25), student_no number (4) primary key, class number (2), major varchar2 (4)); 2) Create table course (course_name varchar2 (25), course_no varchar2 (10) primary key, credit_hrs number (2), department varchar2 (4));

3) Create table prerequsite (course_no varchar2 (4) references course (course_no), perquisite_no varchar2 (24) references course (course_no); 4) Create table section (section_id number (3) primary key, course_no varchar2 (4) references course (course_no), semester varchar2 (8), year date, instructor varchar2 (20)); 5) Create table grade_report (student_no number (4) references student (student_no), section_id number (3) references section (section_id), grade varchar2 (1) check (grade in (‘A’,’B’,’C’)));

Retrieve the names of all senior students majoring in „COSC‟ (computer science). Ans:Select name, major from student where class=5 and major=’COSC’;

i.

Retrieve the names of all course taught by Professor King in 1985 and 1986. Ans:Select c.course_name from course c, section s where s.course_no=c.course_no and s.instructor=’King’ and to_char (year,’yyyy’) between 1985 and 1986;

ii.

iii. For each section taught by Professor king, retrieve the course number, semester year number of students who took the section. Ans:Select sc.course_no, sc.semester, sc.year, count (*) from student sc, grade_report g where instructor=’King’ and sc.section_id=g.section_id group by sc.course_no, sc.semester, sc.year.

iv. Retrieve the name and transcript of each senior student (class=5)Majoring in COSC.A transcript include course_name, course_no, credit hours, semester, year and grade for each course completed by the student. Ans:Select s.name, c.course_name, c.course_no, c.credit_hrs, sc.semester, sc.year, g.grade from student s, course c, section sc,grade_report g where g.student_no=s.student_no and g.section_id=sc.section_id and sc.course_no=c.course_no and s.class=5;

v.

Retrieve the names and major departments of all straight –A students (Students having A grade in their entire course).
Select name, major from student where student_no in (Select student_no from grade_report having (student_no, count (*))=any (select student_no,count (*) From grade_report where grade=’A’ group by student_no) group by student_no);

Ans:-

vi. Retrieve the names and major departments of all students who do not have grade of A in any of their course. Ans:Select name, major from student where student_no in (Select student_no from grade_report having (student_no, count (*))=any (select student_no, count (*) From grade_report where grade<>’A’ group by student_no) group by student_no);

Q.No.5:(b) Write SQL update statements to do the following on the database schema shown in above schema. (i) Insert a new student <‟Johnson‟, 25,1,‟MATH‟> in the database. (ii) Change the class of student “Smith” to 2. (iii) Insert a new course <‟Knowledge Engineering‟,‟COSC4390‟,‟COSC‟> (iv) Delete the record for the student whose name is „Smith and whose student number is 17. Ans:(i). Insert into student (name, student_no, class, major) values (‘Johnson’,25,1,’MATH’); Commit; (ii) Update student set class =2 where name=’Smith’; Commit; (iii) Insert into course (course_name, course_no, and department) values(‘Knowledge Engineering’,’COSC4390’,’COSC’); Commit; (iv) Delete from student where name=’Smith’ and student_no=17;


				
DOCUMENT INFO
Shared By:
Stats:
views:1135
posted:1/23/2010
language:English
pages:13