CS 5984: Computation for the Life Sciences

Fall Semester, 2002
Homework Assignment 3

The point value (if any) of each step is given in square brackets [ ]. The solutions should be turned in on paper by Monday, Nov 11, 2002, in class, before class starts.

1. [10] Design an E/R diagram for the following situation: we wish to record genes and functional categories they belong to. Each gene has a sequence, a GenBank accession number, and an annotation. Each functional category has a name and any number of "sub" functional categories. In turn, these subcategories could have subsubcategories, and so on. A given functional category can be a subcategory of more than one category. Be sure to underline key attributes, place arrows appropriately, and double border any weak sets (if applicable).
   
   After drawing the diagram, convert it into relations. Specify the keys for your relations suitably.
   
   
2. [20] Design an E/R diagram for the following situation: Doctors prescribe drugs for patients. A given doctor can prescribe many drugs for a certain patient. A patient may be treated by many doctors and can be prescribed the same drug by different doctors. A prescription can involve more than one patient (e.g., a mother and her baby), more than one drug, but is associated with a unique doctor. Choose your own attributes (balloons), taking care to ensure that all entity sets have keys.
   
   After drawing your diagram, convert it into relations. Specify the keys for your relations suitably.
   
   
3. [20] Browse through the BioCarta website (http://biocarta.com), especially the database of pathways (e.g., Click "Browse All Pathways"). Pick some pathway of interest (e.g., AKT Signaling Pathway) and describe how you would model the information in the pathway diagram in terms of an E/R diagram. The pathway that you pick is just to give you an idea for what to include in the E/R diagram, and your database schema should not be restricted to only this pathway.
   
   After you have modeled the E/R diagram, browse through some additional pathway diagrams and convince yourself that your diagram is general enough. For full credit, include some screen dumps of the BioCarta website (esp. the pathways that you explored) in your answer sheet.