Homework answers / question archive / Your database in Microsoft Access must have at least: * Two tables * Two forms * Two queries * Two reports (created from queries) Note: I need to add a FK to link the tables

Your database in Microsoft Access must have at least: * Two tables * Two forms * Two queries * Two reports (created from queries) Note: I need to add a FK to link the tables

Computer Science

Share With

---

Your database in Microsoft Access must have at least: * Two tables * Two forms * Two queries * Two reports (created from queries) Note: I need to add a FK to link the tables.
 

Social Determinants of Health: African American Database

 

Table1: The primary key is HcCxID

HealthcareContext (HcCxID, Access, HealthInsurance, HealthBehaviors, HealthStatus, Utilization, StateFIPSCode)

 

Table 2: The primary key is PhyInfraID

PhysicalInfrastructure (PhysInfraID, Environment, Crime, Housing, FoodAccess, Transportation, CountyFIPSCode)

 

Table 3: The primary key is PersonID

Person (PersonID, FirstName, LastName, SSN, DOB, Gender, Race, Ethnicity)

 

Statement 1: Identify all keys in each data table (i.e., primary key, alternate key, secondary key, and foreign key) when applicable.

 

Primary Key

HealthCareContext: (HcCxID)
PhysicalInfrastructure: (PhysInfraID)

Person (PersonID)

 

Alternate Key

HealthCareContext: Access
PhysicalInfrastructure: Environment

Person: SSN and FirstName, LastName, DOB

 

Secondary Key

HealthCareContext: HealthInsurance, HealthBehaviors, HealthStatus, Utilization, StateFIPSCode
PhysicalInfrastructure: Crime, Housing, FoodAccess, Transportation, CountyFIPSCode

 

Foreign Key

HealthCareContext: StateFIPSCode
PhysicalInfrastructure: CountyFIPSCode

Person: PersonID

 

Statement 2: List DBDL for each of your tables using the format described in the “Database Design Language (DBDL)” section (p. 181) in Chapter 6 of Concepts of Database Management. For an example, review Figure 6-1.

 

HealthcareContext (HcCxID, Access, HealthInsurance, HealthBehaviors, HealthStatus,    Utilization, StateFIPSCode)

                AK          Access

                SK           HealthInsurance, HealthBehaviors, HealthStatus, Utilization, StateFIPSCode

                FK           StateFIPSCode

 

PhysicalInfrastructure (PhysInfraID, Environment, Crime, Housing, FoodAccess, Transportation,                 CountyFIPSCode

AK          Environment

                SK           Crime, Housing, FoodAcess, Transportation, CountyFIPSCode

                FK           CountyFIPSCode

 

Person (PersonID, FirstName, LastName, SSN, DOB, Gender, Race, Ethnicity)

                AK          SSN and FirstName, LastName, DOB

                SK           N/A

                FK           PersonID

 

 

Explanation:

 

A.

• The StateFIPSCode and CountyFIPSCode entries are present in both the HealthcareContext and PhysicalInfrastructure tables, the table is not normalized properly because of this duplication of data. This results in update anomalies because if the StateFIPSCode or CountyFIPSCode is changed in one table, it must also be changed in the other table for the change to take effect. In addition, deleting a record in one table would result in the deletion of all records that corresponded to that record in the other table. 
• This might result in the loss of data if, for instance, a user deleted a HealthcareContext record by accident without intending to do so. Last but not least, adding a new record to either one of the tables would necessitate adding new records to the other table as well. This could be troublesome if, for instance, a user failed to insert a new HealthcareContext record when introducing a new PhysicalInfrastructure record. This is just one example of how this could occur.
• The table could be normalized in a number of ways, one of which is to establish a separate table for the StateFIPSCode and the CountyFIPSCode columns. Because of this, the StateFIPSCode and CountyFIPSCode entries in each table would be able to be updated independently of one another. In addition to this, it would prevent the loss of data in the event that a record was deleted inadvertently from one table because the other table would still retain either the StateFIPSCode or the CountyFIPSCode. Finally, it would make it possible to add new records to each table without also needing to add records to the other table. This would be a huge time saver.

 

B.

• A primary key in a database table is an identifier that can only be used once for a certain row. A row in one table can be uniquely identified by a column (or collection of columns) in another table if that other table contains a foreign key. A secondary key is an alternative key that can be used to identify a specific row within a database table. This key can be used in place of the primary key.
• In situations where the primary key of one table needs to be referenced in another table, a foreign key can be of great assistance. As an illustration, one of the columns in the HealthCareContext table is a foreign key that refers to another table's CountyFIPSCode column. This column can be found in the PhysicalInfrastructure table. Because of this, we are able to verify that the data contained in both databases is accurate and that we can simply query both tables in order to obtain information that is pertinent to our needs.
• When the primary key cannot be used or when its use would be inappropriate, one might make use of a secondary key instead. One example of a secondary key is the HealthInsurance column found in the HealthCareContext table. This field can be used to identify a specific row in the database. This is due to the fact that the HealthInsurance column contains information that is absent from the main key (HcCxID).

 

C.

• The three tables each have a different primary key, which are PersonID, HcCxID, and PhysInfraID, respectively. The Person table has alternate keys of SSN and FirstName+LastName+DOB, while the HealthcareContext table has an alternate key of Access+HealthInsurance+HealthBehaviors+HealthStatus+Utilization+StateFIPSCode. The PhysicalInfrastructure table has an alternate key of Environment+Crime+Housing+FoodAccess+Transportation+CountyFIPSCode. 
• The Person table has a foreign key of PersonID, which corresponds to the PersonID in the HealthcareContext table. The HealthcareContext table has a foreign key of HcCxID, which corresponds to the HcCxID in the PhysicalInfrastructure table. The PhysicalInfrastructure table has a foreign key of PhysInfraID, which corresponds to the PhysInfraID in the Person table. The Person table is the central table in this schema, as it contains the most information about an individual. 
• The HealthcareContext table contains information about the healthcare context in which a person lives, and the PhysicalInfrastructure table contains information about the physical infrastructure in a person's county. The PersonID, HcCxID, and PhysInfraID columns are all primary keys in their respective tables. The PersonID column is a foreign key in the HealthcareContext table, and the HcCxID column is a foreign key in the PhysicalInfrastructure table. The PhysInfraID column is a foreign key in the Person table.