Database management
To complete a simple relational model for a basic grants management tracking system. In a research environment, grants are given by foundations (such as the National Science Foundation or National Institute of Health) to universities. Usually there is a lead or principle investigator that manages the grant. The following paragraphs provide an English description of each entity with attributes. It will also describe the relationships between entities. Using draw.io (see Lab One), specify:
- The relationships between tables
- The cardinality between the tables
- Identify the keys - primary and foreign keys
- Specify the data types and potential constraints for each field. See the slides for data constraints. For example, grant_amount >
-
This is what I have so far, pls help me to connect the table together, like ( 1 to 1, 1 to many, many to 1 ,...)
-
-
Relationship Descriptions
- A Principle Investigator can manage several grants. A PI works for a specific organization.
- An organization can employ many researchers and PIs
- A grant can be worked on by many researchers.
- And a researcher can be involved in many grants. But there may be researchers "on the bench" - not assigned to a grant.
- A grant can belong to a specific program (but not required)
- A program can be assigned to many grants.
Principle Investigator A Pl is some one who runs the research project and owns funding responsibility. A Pl has a name, employee ID. organization, email address, and total award amount. Name Cliburn Doherty Schafer Smith Empl_ID 98902 98901 98903 ABC 101 Organization Email Total_award UOP cliburn@pacific.edu 85000 UOP doherty@pacific.edu 105500 UOP schafer@pacific.edu 65000 LLNL smith@linl.gov Smith@llnl 150000 Organization An organization represents the entity that employs the Pl and researchers involved in working on the grant. It has a name, address, and type (domain is a specific set of values - Research, Education, Corporate). Org_name UOP LLNL Address 3501 Pacific Ave Stockton CA 95211 Livermore, CA 1600 Amphitheatre Parkway, Mountain View, California 94043 Type Education Research Google Corporate Grant The grant is an awarded amount of money to fund the research efforts. A grant has attributes for start and end dates, grant title, principle investigator, amount, and description. Grant_title PI Start_date End_date Descr Amount VR In Education 98902 10/1/19 12/31/22 Use VR in classrooms 55000 Program Education Research Cyber Security Internet of Things 98903 12/1/19 9/30/20 Threat Detection 45000 DBMS Normalization 98901 1/1/90 12/31/94 Enhanced normalization 30000 Data Science Education Research Reverse Classroom 09002 1/1/17 12/31/19 Teaching with a reversed classroom 20000 Researcher Researchers allocated time to specific grants. A researcher has a name, employee id, organization, grant they are assigned to email address Name Email Organization Grant_title EmpL_ID VR In UOP Jose jose@pacific.edu Education 989100 Jennifer UOP Internet of "989101 Things jen@pacific.edu Austin UOP 989102 austin@pacific.edu Reverse Classroom Reverse Classroom VR In Wendy UOP 989103 wendy@pacific.edu Michelle UOP michelle@pacific.edu Education 989104 Program A program is a category for different types of grants. For example, programs may include things like Genetic Blood Research, Young Scientist's Education, etc. A program has a name, organization, description Program_name Education Research Cyber Security Data Science Organization UOP UOP Descr Various research related to teaching Computer security topics Data analytics related research LLNL Principle Investigator | Empl_ID(String) Organization PK Org_name (String) Address (String) Type (String) Name (String) Organization (String) Email (String) Total_award (String) Grant Researcher PK Grant_title(String) FK Empl_ID Grant_title Name Start_date End_date Organization Email Descr Amount Program PK Program_name Organization Descr Principle Investigator A Pl is some one who runs the research project and owns funding responsibility. A Pl has a name, employee ID. organization, email address, and total award amount. Name Cliburn Doherty Schafer Smith Empl_ID 98902 98901 98903 ABC 101 Organization Email Total_award UOP cliburn@pacific.edu 85000 UOP doherty@pacific.edu 105500 UOP schafer@pacific.edu 65000 LLNL smith@linl.gov Smith@llnl 150000 Organization An organization represents the entity that employs the Pl and researchers involved in working on the grant. It has a name, address, and type (domain is a specific set of values - Research, Education, Corporate). Org_name UOP LLNL Address 3501 Pacific Ave Stockton CA 95211 Livermore, CA 1600 Amphitheatre Parkway, Mountain View, California 94043 Type Education Research Google Corporate Grant The grant is an awarded amount of money to fund the research efforts. A grant has attributes for start and end dates, grant title, principle investigator, amount, and description. Grant_title PI Start_date End_date Descr Amount VR In Education 98902 10/1/19 12/31/22 Use VR in classrooms 55000 Program Education Research Cyber Security Internet of Things 98903 12/1/19 9/30/20 Threat Detection 45000 DBMS Normalization 98901 1/1/90 12/31/94 Enhanced normalization 30000 Data Science Education Research Reverse Classroom 09002 1/1/17 12/31/19 Teaching with a reversed classroom 20000 Researcher Researchers allocated time to specific grants. A researcher has a name, employee id, organization, grant they are assigned to email address Name Email Organization Grant_title EmpL_ID VR In UOP Jose jose@pacific.edu Education 989100 Jennifer UOP Internet of "989101 Things jen@pacific.edu Austin UOP 989102 austin@pacific.edu Reverse Classroom Reverse Classroom VR In Wendy UOP 989103 wendy@pacific.edu Michelle UOP michelle@pacific.edu Education 989104 Program A program is a category for different types of grants. For example, programs may include things like Genetic Blood Research, Young Scientist's Education, etc. A program has a name, organization, description Program_name Education Research Cyber Security Data Science Organization UOP UOP Descr Various research related to teaching Computer security topics Data analytics related research LLNL Principle Investigator | Empl_ID(String) Organization PK Org_name (String) Address (String) Type (String) Name (String) Organization (String) Email (String) Total_award (String) Grant Researcher PK Grant_title(String) FK Empl_ID Grant_title Name Start_date End_date Organization Email Descr Amount Program PK Program_name Organization Descr
