Assignment 6, due Mar 28

Always, on every assignment, please write your name legibly as it appears on your University ID and on the class list! All assignments will be due at the start of class on the day indicated, and unless there is what insurance companies call "an act of God" - something outside your control; the only exceptions to this rule will be by advance arrangement.

For those taking the course by video link, assignments may be submitted electronically by E-mail to Rajiv Raman. Please do not use obscure attachment formats! Plaintext E-mail is preferred to HTML, Word, RTF or other even more obscure formats!

1. For fault tolerance, we insist that our database contain a transaction log. The database contains hospital records. Physicians have unlimited access to the medical records of their patients, but only anonymous access to records of other patients, and may not examine that patient's financial records, because we don't want patient care to be biased by considerations of how it will be paid for. The billing department may access patient's financial records and may look at the billable procedures performed, but may not examine detailed medical records except in anonymous form. Administrators may access medical and financial records, but not patient names, in order to determine the real cost of each procedure in order to set the billing rates. Information relating to the costs of procedures is public.

   a) Work out what the entities are in this data base and the relationships applicable to each class of entity. For the sake of illustrating your answer, Doctor Smith sees John for an office visit on November 10, for which John is charged $75 dollars. During that office visit, Doctor Smith determines that John's symptoms are psychosomatic and require no treatment.

   b) Identify the users, and in terms of your answer to part a, identify which users should have access to which relations.

   c) Propose a cryptographic solution to the security constraints posed above, in terms of your solution to part a. Identify each key required, and which data should be encrypted with that key. Which users should be able to encrypt and store that data. Which users should be able to decrypt and examine that data. Clearly identify whether any of the constraints posed require public key cryptography, or whether symmetric key which keys are given to which users?k to prevent

   d) Suppose all access to the database is through a trusted application program, and this application has strong user authentication of some kind. Is there any need for cryptography?

   e) Under the security constraints posed above, who should have access to the transaction logs?