[Main Page]      [Literature]      [Software]      [People]      [Conferences]      [Courses]      [Web-DB]

Homework Assignment #11:    Data Dictionary

Reading Assignment

Please read the following chapter from the book by Elmasri and Navathe (3rd Edition):

• Chapter 17, "Database System Architectures and the System Catalog"

Exercise 11 (12 Points)

Please write SQL queries for answering the following questions from the Oracle data dictionary. This exercise assumes that you have the homework results database installed. Part f) builds on the previous homework.

    a)

How many tables/views etc. has the data dictionary? How many of these table names end in "S", i.e. are presumably a plural form? You can use two SQL queries to answer these questions (i.e. it is not necessary to get both answers in a single query).

    b)

Which data dictionary table has the largest number of columns and what is this largest number? (Of course, it is possible that the output consists of several tables with the same number of columns.)

    c)

Which tables owned by the user "SBRASS" can you access? If you work at home, you can replace this user by "SYSTEM" (of course, you should not be logged in as "SYSTEM" or another user with DBA privileges).

    d)

Which indexes do you own (i.e. database objects of type "INDEX")?

    e)

Print the foreign keys on the table "RESULTS". I.e. each output row should give the name of a foreign key constraint, one column in the table "RESULTS" which participates in the foreign key, and the name of the referenced table. If a foreign key consists of more than one column, it should result in multiple output rows. Please order the output by the constraint name.

    f)

List all grants you have made for the view "SCOTTSTEST" in Homework 10. If you have already deleted this view, you can make any grant on any table/view and check that it is recorded in the data dictionary.

Due Date:

Please submit your solution on next Wednesday (April 19) before the lecture.

Stefan Brass (sbrass@sis.pitt.edu), April 12, 2000

Original URL: http://www2.sis.pitt.edu/~sbrass/db00_2/hb_ddict.html   [HTML 3.2 Checked]