Intensive SQL Homework Assignment

Introduction

The following computer science department and undergraduate course descriptions schema are extracted from an university with some modifications to make suitable for this homework. This information includes Staff and courses. The descriptions of faculties from computer science department includes their position, phone, email address, education, research interests, and selected publications.

To find the time when the final exam for a course is scheduled, do the following:

1. Determine the beginning time for a course. Use lecture beginning time rather than the recitation times.

2. Read to find the time and day for your final exam using the beginning time for the course.

The database you created should contain the following ten tables:

 

table department : department information

deptabbre varchar(10) : department abbreviation such as CSci and Phys

location varchar(20) : building name such as EE/CSci Bldg

office varchar (20) : room number

 

table faculty : faculty information

ssn char (9) : social security number

fname varchar(10) : first name

mname varchar(10) : middle name

lname varchar(10) : last name

emailaddr varchar(20) : email address

deptcode varchar(10) : department abbreviation such as CSci and Phys

deptposition varchar (20) : faculty position such as assistant professor

phone char(7) : phone number

 

table course : course information

course char(4) : course id number

title varchar(255) : course title

deptabbre varchar(10) : department abbreviation such as CSci and Phys

description varchar(255) : course description

 

table section: course sections

call char(6) : course call number

section_no int : section number

quarter varchar(6) : quarter which a course is offered

year varchar(4) : year which a course is offered

lweekday varchar (5) : lecture meeting days

llocation varchar(20) : lecture meeting place (building)

lroom varchar(6) : lecture meeting room

lstart char(4) : lecture starting time

lend char(4) : lecture ending time

rweekday varchar(5) : recitation meeting days

rlocation varchar(20) : recitation meeting place (building)

rroom varchar(6) : recitation meeting room

rstart char(4) : recitation starting time

rend char(4) : recitation ending time

deptabbre varchar(10) : department abbreviation such as CSci and Phys

course char(4) : course id number

fssn char(9) : faculty social security number

fid int : final exam id

 

finalschedule : final exam schedules

fid int : final exam id

quarter varchar(6) : quarter which a course is offered

year varchar(4) : year which a course is offered

timebegin varchar(4) : lecture starting time

timeend varchar(4) : lecture ending time

daybegin varchar(3) : lecture days

examtime char(9) : final exam time

examdate varchar(15) : final exam date

 

publication : publication list

pid int : publication id

title varchar(255) : publication title

journal varchar(50) : journal that a paper is appeared

volume int : volume number

month char(4) : month of the publication

year char(4) : year of the publication

 

prerequisite : prerequiistes of course information

deptabbre varchar(10) : department abbreviation such as CSci and Phys

courses char(4) : course id number

preabbre varchar(10) : prerequisite's department abbreviation

precourse char(4) : prerequisite course id number

 

publishedby : relationships between faculty and publication

pid int : publication id

fssn char(9) : faculty social security number

 

education : education background of each faculty

fssn char(9) : faculty social security number

degree varchar(10) : degree received

major varchar(20) : major study area

year char(4) : year

school varchar(50) : school that a degree is received

 

research : research interests of the faculties

fssn char(9) : faculty social security number

researcharea varchar(255) : faculty research area

description varchar(255) : description on research area. not filled.

 

 

Part A

Using the above table information and the corresponding ER Diagram, create a DB2 database with the tables described above. Handle in soft and hardcopy of the SQL DDL statements as well as all the DML statements that propulate sample data.

 

Part B

Develop SQL statements to implement the following queries.

Verify your queries. There are no partial credits.

1. List all the courses (course number and title) offered by computer science department.

2. List all course sections taught by Professor Sinn.

3. Give the final examination date and time for CSci 160 section 1.

4. List all the prerequisite course(s) for CSci 135.

5. List all the course sections of CSci 590.

6. List all computer science course(s) which is taught by the professor who teach more than one section of a course.

7. List all the courses which have at least one common prerequisite course(s).

8. List all the course sections which have the same final examination date and time.

9. List all the name(s) of the faculty who teaches at least one course.

10. List all the name(s) of faculty who teach exactly two courses. (Note that if a faculty teaches two sections of the same course, count it as one course.)

11. List all the faculty member's name(s) who do not teach.

12. List all the name(s) of the faculty who work for computer science department.

13. List all the name(s) of the faculty who teach at least one course located in the building whose department is not located.

14. List all the faculty name(s) who teach at least one course section and total count of course section(s) teaching.

15. List all the faculty name(s) who teach a course and total count of course(s) teaching. Exclude a course(s) with more than one section which is taught by the same instructor from counting.

16. List the name of the faculty who teaches every course in computer science department.

17. List all the name(s) of the faculty who has no publication.

18. List all the papers published (authored or co-authored) by Professor Sinn.

19. List all the name(s) of the faculty who co-author for at least one paper

20. List all the name(s) of the faculty who co-author for at least one paper and taught for at least one common course (different section).

Handle in both soft and hardcopy of SQL queries with sample output. Also, resubmit part a.