The study_value field is designed to store information about the study that each sample is associated with, and the allowable values for this field come from the dom_study table. This table is used to store metadata about scientific samples (e.g., plots, nets, nests, stations), including the study for which the samples are being collected ( study_value), the sample id, and location information. Figure 3 The first 19 lines of the 02_create_table_public_sample.sqlfile in pgAdmin.įigure 3 shows the columns in the newly created public.sample table. Let’s take a closer look at the code that creates the sample table (Figure 3). Next, run the second file, which will create the sample table in the public schema. The first file creates a sequence to use in the unique_id field in the sample table, run this file first. To create the sample table follow the steps provided in Lesson 002: Creating Tables in PostgreSQL using the files 01_create_seq_public_sample_uid_serial_seq.sql and 02_create_table_public_sample.sql located in the Lesson_004 folder of the clone of your fork of the learning-data-science repository. The sample table will be used to store information about scientific samples (e.g., plots, nets, nests, stations). To illustrate the use of foreign key constraints in my_first_database, we first need to create another table, the sample table. I’ll cover ER models in more detail in the next post in which we’ll create an ER model for my_first_database. As such, each combination of date and fruit entered into the database must be unique or else the database will throw an error. Note also that the primary key in Table 2 consists of two columns, date and fruit. The line connecting Table 1 to Table 2 with the three forks at the end indicates a one-to-many foreign key relationship, i.e, one instance of a value in Table 1 relates to one or more values in Table 2. An Entity-Relationship (ER) model illustrating the structure of Tables 1 and 2, and the foreign key (FK) relationship between the tables. A simple ER model diagram defining the relationship between Tables 1 and 2 is presented in Figure 2. In terms of databases, an ER model defines the relationships between the table and columns in a database, including database constraints. The foreign key relationship illustrated in Figure 1 can also be expressed in an Entity-Relationship (ER) model. The values entered into the “fruit” column in Table 2 must be present in the “data_value” column in Table 1. Example of a foreign key relationship between 2 tables. The only way to add a new value into the “fruit” column in Table 2 would be to first add it into the “data_value” column in Table 1. However, were you to attempt to add a value into the “fruit” column that is not in Table 1, ‘strawberry’ for instance, then the database would enforce the constraint and not allow you to save the data. As shown, the data in Table 2 is valid since all of the values are present in the “data_value” column in Table 1. Table 1 defines a list of acceptable values that can be entered into the “fruit” column in Table 2. Figure 1 provides a simple example of a foreign key relationship between 2 tables. If you are not familiar with version control using Git and GitHub, I recommend going back and working through the hands on tutorials in my series on version control starting with “ Version Control: An Overview.” Foreign Key ConstraintsĪ foreign key constraint defines a relationship between values in columns from 2 different tables such that the value in the column of the first table must be present in the related column of the second table. If you have previously forked and cloned the repository, but don’t have the files for this lesson, then follow the instructions here to update your fork with the latest files. If you have not already forked the learning-data-science repository, and cloned the fork to your computer, please do so now following the instructions provided in the post Learning Data Science. This lesson builds on earlier posts so, if this is your first time reading this blog and you’d like to follow along on your computer, then please go back and start from the post Learning Data Science and work through the lessons up to this point.įor this post we’ll be using the files in the Lesson_004 folder in the learning-data-science repository. Since data analysis and management are best learned by doing, the objective of this and future posts is to get readers hands on experience using PostgreSQL and R. In this post, Lesson 004, we’ll create 2 more tables, set up foreign key constraints, and insert data into the 2 tables. In the previous post I covered inserting data into a PostgreSQL table and we used the Lesson_003 files in the learning-data-science GitHub repository to insert data into the dom_study table.
0 Comments
Leave a Reply. |