Announcements for G22.2433, Spring '97

This page will contain important information regarding the course that the students should consult every day. Typically, this info will also be sent to the course mailing list.

May 6, 1997

Solutions to Final Practice Questions(postscript file)

April 30, 1997

As announced previously on the mailing list, the final for this course will be on Thursday, May 8, between 5 and 7 pm in the regular class room.

April 18, 1997

This homework is worth 15% of your grade.

  1. Build a relational model for the ER diagram you created for your first assignment. Create:

  2. Create these tables in oracle. (Put all the sql statements in a text file, and type @<your-file-name> at the SQL prompt of the splplus program. It will process all the commands in that file. Populate the tables with some small amount of data using the insert into <tablename> values <valuelist> command.
  3. Make a list of at least half-a-dozen queries that you can ask of your database. (Don't create trivial queries. At least 3 of them MUST involve joins and/or nested queries.) Create sql statements for them. After interactively verifying that the queries really do what they intended, stick them back in the original sql file.
  4. Finally, create a set of sql statements that will drop all the tables that you created. (We want your assignment script to be idempotent.)

Hand into the TA, by May 1, 1997:

  1. an electronic copy (by email) of the sql file with:

    We expect this file to be commented nicely so that we can tell what you are trying to do. Use the "Rem" command at the beginning of a line to put comments.

  2. a paper copy of the file and the output generated by the file.

This assignment drops dead at 5pm on May 1, 1997. If you don't hand in your assignment by then, YOU WILL GET NO CREDIT FOR THIS ASSIGNMENT.

Helpful hints:

  1. typing spool <filename> will spool all oracle output to the filename. Turn spooling off with spool off
  2. Look at the files demobld.sql and demodrop.sql for examples of how to create and drop tables. They are in directory /usr/app/oracle/product/7.3.2/sqlplus/demo on acf5.nyu.edu.
  3. oracle has fairly extensive online help. typing help spool at the sql prompt, for example will get you help on spool.
  4. tablenames are case insensitive, but data is case sensitive. Also, you can't use "-" (minus) as a character in your table/column names. Use the underscore, "_". Use single quotation to quote char strings. Example: select * from mytable where helpstring = 'this string';
  5. sqlplus expect a semi-colon at the end of each sql statement.

April 2, 1997

Solutions to Mid-term Exam (postscript file)

March 11, 1997
Answers to Sample Questions
Assume that we have an employee-project schema with the following relations: (Keys are in all capitals.)

Other constraints:

Express the following queries in relational algebra:

Relational Algebra
(Temporary relations have been used whenever necessary for clarity.)

Q1
Retrieve the name and address of all employees who work for the ``Research'' department.

tex2html_wrap_inline100

Q2
For every project located in "Stafford", list the project number, the controlling department number, and the department manager's last name, address birthdate.

tex2html_wrap_inline102
tex2html_wrap_inline104
tex2html_wrap_inline106
tex2html_wrap_inline108

Q3
Find the names of employees who work on *all* the projects controlled by department number 5.

tex2html_wrap_inline110
tex2html_wrap_inline112
tex2html_wrap_inline114
tex2html_wrap_inline116

Q4
Retrieve the name of all employees who have no dependents.

tex2html_wrap_inline118
tex2html_wrap_inline120
tex2html_wrap_inline122
tex2html_wrap_inline124

Write the following queries in SQL:

Q0
Retrieve the birthdate and address of the employee whose name is "John B. Smith".

Select bdate, address
from Employee
where fname = 'John' and minit = 'B' and lname = 'Smith'

Q1
from above.

Select fname,lname,address
from Employee, Department
where dname = 'Research' and dnumber = dno

Q2
from above.

Select pnumber, dnum, lname, address, bdate
from Project, Department, Employee
where dnum = dnumber and mgrssn = ssn and plocation = 'Stafford'

Q3
from above.

Select fname,lname
from Employee
where not exists

((select pnumber from Project where dnum = 5)
except
(select pno from Works_On where ssn = essn))

Q4
from above.

Select fname,lname
from Employee
where not exists

(Select * from Dependent where ssn = essn)

Q5
Find the sum of the salaries of all the employees, the maximum, the minimum and avergae salaries.

Select sum(salary), max(salary), min(salary), avg(salary)
from Employee

Q6
Retrieve the total number of employees in the "Research" department.

Select count(*)
from Employee, Department
where dno = dnumber and dname='Research'

Q7
For each dept, retrieve the dept number, the number of employees in the dept and their average salary.

Select dno, count(*), avg(salary)
from Employee
group by dno

Q8
For each project on which more than two employees work, retrieve the project number, the project name and the number of employees who work on that project.

Select pnumber, pname, count(*) from Project, Works_On
where pnumber = pno
group by pnumber, pname
having count(*) > 2

Q9
For each dept having more than 5 employees, retrieve the department number, and the number of employees making more than $40,000.

Select dname, count(*)
from Department, Employee
where dnumber = dno and salary > 40000 and
dno in

(Select dno from Employee
group by dno
having count(*) > 5)

group by dname

The following answer is incorrect (why?):

Select dname, count(*)
from Department, Employee
where dnumber = dno and salary > 40000
group by dname having count(*) > 5

Home Work 1
Due Feb 27, 1997

This homework is worth 10% of your final grade.

For this homework, pick a real-world enterprise of your choice and design an E-R diagram of your enterprise. If you need help with picking an enterprise to model, talk to your TA or insructor.

Hand in the following:

  1. An E-R diagram of your database.
  2. An informal reason-for-existence description of your database. What problem is your database trying to solve?
  3. A detailed description of each entity and its role in the database, with the reasoning behind the choice of attributes and primary key.
  4. A detailed description of each relationship, with the reasoning behind the choice of mapping cardinalities.
  5. A detailed description of each user view, with the reasoning behind the choice of views. Why does it make sense to provide these views?
  6. A description of how users can find their way through the different parts of the system, i.e., what are some typical paths of information flow?
  7. A set of sample queries showing how your database may be used effectively. (These queries can be described in English. That is, you don't have to write them in relational algebra or SQL.)

Your E-R design must include at least 6 entities and 5 relationships, out of which at least one relationship must be many-to-many and at least two must be one-to-many.

Comments? email me at: sridhar@cs.nyu.edu

About this document ...

Announcements for G22.2433, Spring '97

This document was generated using the LaTeX2HTML translator Version 96.1 (Feb 5, 1996) Copyright © 1993, 1994, 1995, 1996, Nikos Drakos, Computer Based Learning Unit, University of Leeds.

The command line arguments were:
latex2html -no_navigation -no_subdir -split 0 announce.

The translation was initiated by Sridhar Ramaswamy on Tue May 6 16:21:50 EDT 1997


Sridhar Ramaswamy
Tue May 6 16:21:50 EDT 1997