ER diagram Mutations, signaling pathways, and disease.

ER diagram

Mutations, signaling pathways, and disease. There is much interest in prioritizing genetic mutations, discovered through sequencing experiments, based on their likely affect on human disease. One way that mutations can affect disease is through disruption of protein-protein interactions in signaling pathways. In these pathways, which transmit information from one part of a cell to another, proteins form complexes through interaction of their three-dimensional shapes. If a mutation disrupts a binding site on a protein, it can affect the ability of that protein to successfully bind to its interaction partners. This can then disrupt the ability of the signals to be successfully transmitted and may lead to disease. The goal of this question is to design a database which can be used for storing information to help prioritize mutational affects.

A. Design and draw an ER diagram that captures the information below. Create entities and relations corresponding to the bold face names in the statements. Be sure to indicate:

• the attributes for each entity (and relation attributes if any),
• keys for each entity and relation (underline key fields),
• relationship class (one-to-many, many-to-many, etc.),
• participation constraints (total, partial),
• any constraints that cannot be captured in the ER diagram.
• Use the symbols on the in your diagram.

1. Each signaling pathway has an id, a name, and a function.
2. Each protein has an id, a name, a sequence, and a three-dimensional structure (stored as an
3. external code from the protein data bank - PDB).
4. Proteins have pairwise interactions. Each protein can have zero or more interactions. No
5. more than one interaction can involve the same two proteins.
6. Each protein-protein interaction participates in exactly one signaling pathway or in none.
7. Each pathway has at least one interaction.
8. A single nucleotide variation or SNV, has an id, a chromosome number and nucleotide
9. position (where it occurs), a reference nucleotide, and a variant nucleotide.
10. A binding site has an id and a three-dimensional shape description (text).
11. Each protein contains zero or more binding sites. Each binding site is contained in exactly
12. one protein.
13. An SNV can alter zero or more binding sites. Each binding site can be altered by one or
14. more SNVs. When an SNV alters a binding site, a magnitude value is included to quantify
15. the degree of alteration.
16. A disease has an id and a name.
17. A signaling pathway may be involved with zero or more diseases. Each disease is involved
18. with at least one pathway.

B.Write the create table statement for the SNV entity.

C. Write the create table statement for the interact relationship. Carefully consider here your answers to part A. Assume that deletes in a foreign key table (if any) should cascade

into this table but that updates should not. Write these assumptions explicitly.