COSC6340  Data Management

News:

• Please read through this web page
• Project 2 will be extended to Saturday, 3/13, 11pm, no late submission accepted
• Grade for Midterm0 is available, also check out the grading rule, and solution.
• Grade for project 2 is available. Read the grading rule. If any question and complaints, please send me emails or visit me during my office hours.
• Final Grade is available. May everyone have a great summer!
• In project 1:
  • when you are prompted to input something,  simply input something like a number, for the script to continue execution, or remove '\& flash'.
  • The two copies of your report should be identical. Send me the soft copy, bring the hard copy to Dr. Eick in the following calss.
  • If a pet is taken to and back from a clinic at the same day, one day instead of zero is counted, and number of days is always equal to (endDate-startDate+1).
  • For question 9, a pet 'visited' a clinic if a pet has a clinicNo.
  • For question 11, a clinic can have multiple managers
  • For question 12, 'did not bring it to any clinic(e.g., no record of examination or treatment)' is same to 'did not bring it to any clinic(e.g., no record of examination)'
  • For question 16, average cost of some type = (total cost of this type)/(no. of treatment of this type), means a pet of this type will not be included when averaging the cost if it hasn't been treated at all.
  • Please follow parts in order as following: <task name, SQL statements, Result, Explanation>, Explanation part is optional, you may not explain it if you think it's unnecessary and I can understand well.
• In project 2:
  • For question 4, since there are no duplicate records in Pharmacy Table, there will be no duplicate in Pharmacy_History Table. However, when you insert some new records into Pharmacy and remove them to Pharmacy_History, be careful not to insert records that already appear.
  • For question 2,  given a penNo, "returns the total number of pets currently staying in a pen" means "returns the total number of pets that is/had been in a pen", i.e., simply ignore "currently". If a pet has no out_Date, interpret it as "staying in pen".
  • For question 4, you don't need to do union, and PHAR_HISTORY table has primary key drugNo. For question 4 and 5, you can assume that no more update/insert to any records that had been updated/inserted.
  • For question 2 and 3, you can output all penNo (not petNo) with number of pets. Ex: p001 Number1; p002 Number2; ...
  • For question 2, if a pet stayed in any pen for multiple times, these times will be counted independently, instead of taking as one stay.
  • For question 4 and 5, you should show the proof that your trigger works by outputing the Pharmacy, Items, and PHAR_HISTORY, UNUSUAL_ITEM tables after operations, with specifying what operations are performed, how they are performed,  and what result these operations get.
  • For question 5, if a record is updated from 70 to 85, it should be included in UNUSUAL_ITEM table, and if an item is updated from 80 to 70, it should be removed from the UNUSUAL_ITEM table.
  • Other than the normal report, one .sql script file should be stored and submitted for every task and submitted together with the report.

Information:

Section: 09464
Class meets: TU/TH 11:30a-1p
Class room: 232 PGH
Course Web: 6340 Database

Instructor: Dr. Christoph F. Eick
Email: ceick@uh.edu
Office: 501E PGH
Office hours: Mon 3:30pm-5:00pm, Tue 1:00pm-2:00pm, 3:30pm-5:00pm, Thu 9:30am-11:00am

Teaching Assistant: Zhenghong Zhao
Email: zhenzhao@cs.uh.edu
Office: 518 PGH
Office hours: MO/TH 4:30pm-6:00pm.

Lab Projects:

Useful Information on how to install and use ORACLE
Some other links for using ORACLE SQL

MS SQL Server 2000
MS SQL Server 2000 is now running on computers in 563 PGH --- try those computers that face the wall furthest away from the door. login ID: cosc6340, password: tiger.

Oracle 9i
To install Oracle on your own computer, go to Oracle website, register as a user, download, and follow our Installation Instructions. Moreover, Oracle 9 is also installed on machines in the 3rd and 5th floor labs in the PGH building.

Lab1 (preliminary draft, still subject to change till Feb. 10, 2004)
Lab2 (preliminary draft)

How to Submit:

Send your electronic report as a .zip file to zhenzhao@cs.uh.edu before deadline. Submit your hard copy to Dr. Eick in the next class from deadline. No late submission will be accepted.

Since I am TA-ing two courses, and both of them require submitting homeworks to the same email address, your submission should have a subject including ‘6340 project 1’ or ‘6340 project 2’. Otherwise, a missing may happen.

you should name your zip file as following: your last four SSN+’database’+project number, for example, a student has SSN 123-45-6789 should name his zip file as 6789database1.zip.

These are serious rules that should be followed.

Project Rules: Refer to Dr. Eick's Web page for detail information. <important>.

• Please do not e-mail us with grading questions. If you want us/me to explain why I took points off, you can talk to me/us during our office hours.
• All homeworks and project reports are due at the date specified. No late submissions will be accepted after the due date. This policy will be strictly enforced.
• Only machine written solutions to homeworks and assignments are accepted (the only exception to this point are figures and complex formulas) in the assignments. If we are not capable to understand your solution, you will receive a low score. Moreover, students should not throw away returned assignments or tests.
• Students can help each other to understand, but not to work through homeworks and projects. The like-mindedness in assignments should be explained.
• Students are responsible to protect their own files from being cheated.