CSE 444 Homework 3

Objectives:

To gain experience with views and writing XQuery expressions.

Related Reading:

Chapter 6.7, 4.6-4.7

Number of points:

100

Due date:

Wednesday, March 3rd, 2004 beginning of class

Turnin:

At the beginning of class on 3/3/04 turnin a printout (i.e. typed) for all questions.

Homework Problems:

For the decomposition problems, please give all intermediate steps and what functional dependency caused you to take that intermediate step.

1. Specifying/Updating Views [20 points]

Use the following schema to answer the following questions:

Student(SSN, FName, LName, Class)
Professor(SSN, FName, LName, Office, Salary)
Section(CourseId, Quarter, Year, Prof_SSN)
Transcript(SSN, CourseId, Quarter, Grade)

This schema is part of a database schema for a university. Each Student is identified by their social security number and the database includes their first name, last name and class standing. Each Professor is identified by their social security number and the database includes their first name, last name, their office number and salary. The Section table has information about one section of a class. Each Section is uniquely identified by the course id and the quarter (assume a course cannot have more than one section per quarter and a quarter is identified in YYQQ form where YY are the last two digits of the year and QQ is the quarter within the year, e.g. '04WI'). Each Section also includes the year the section took place in and the social security number of the professor that taught this section. Lastly, Transcript associates a student with a section and contains the grade the student made in that section.

  1. Specify a view that has the name (first and last) of all students who have ever passed (got a grade of 2.0 or higher) 'CSE444'.
  2. Is the view you specified in part (a) updatabale? Why or why not?

2. Querying Views [30 points]

For the following 3 query-view pairs, consider whether it is possible to use the view to answer the query. If yes, show the query that uses the view. If not, explain why not.
  1. QUERY1.
    SELECT DISTINCT FName, LName, Count(*)
    FROM Professor, Section
    WHERE Professor.SSN = Section.Prof_SSN
    GROUP BY LName, FName

    VIEW1.
    CREATE VIEW NumClassesTaught AS
    SELECT DISTINCT FName, LName, Count(*) FROM Professor, Section
    WHERE Professor.SSN = Section.Prof_SSN AND Section.Year > 1990
    GROUP BY LName, FName

  2. QUERY2.
    SELECT DISTINCT FName, LName, Year
    FROM Professor, Section
    WHERE Professor.SSN = Section.Prof_SSN AND Section.CourseId = 'CSE444'

    VIEW2.
    CREATE VIEW ClassesTaughtByProf AS
    SELECT DISTINCT FName, LName, CourseId
    FROM Professor, Section
    WHERE Professor.SSN = Section.Prof_SSN

  3. QUERY3.
    SELECT Professor.SSN
    FROM Student, Professor, Section, Transcript
    WHERE Student.SSN = 711711777 AND Transcript.SSN = Student.SSN AND Transcript.CourseId = Section.CourseId AND Transcript.Quarter = Section.Quarter AND Professor.SSN = Section.Prof_SSN

    VIEW3.
    CREATE VIEW StudentsTaughtByProf AS
    SELECT Student.SSN As Student, Professor.SSN AS Professor, Transcript.CourseId AS Course, Transcript.Quarter AS Quarter
    FROM Student, Professor, Section, Transcript
    WHERE Transcript.SSN = Student.SSN AND Transcript.CourseId = Section.CourseId AND Transcript.Quarter = Section.Quarter AND Professor.SSN = Section.Prof_SSN

3. Writing XML Queries [50 points]

For this part of the assignment, you will be writing XQueries and feed them into Galax. The two data instances are Mondial and Reed. Download mondial-3.0.xml and reed.xml (the .dtd files give you an idea of the structure, but is not necessary for the assignment). Write XQueries to answer the following English queries:

  1. For each instructor, list all the courses he/she teaches in the format of [subject] [course number]: [title]. (e.g. <course> ANTH 211 Introduction to Anthropology </course>)
  2. Find all the "over crowded provinces" in China, where "over crowded provinces" are defined to be the ones whose population density is greater than that of China itself.

For this question you need to run galax: a brief tutorial on how to do this is Here. Alternatively, you can easily install it yourself on any machine you want, from here (get version 0.3.0 for Win32).