Workshop: Databases Part I (Oct 4)

1 Leave a comment on paragraph 1 0 On October 4th, with a lot of the students attending our DH praxis seminar (and, of course, other people not attending it), I participated to the workshop “Databases Part I”, with the two digital fellows Ian Phillips and Tahir Butt.

2 Leave a comment on paragraph 2 0 The workshop was nice and very useful: Ian and Tahir were very nice and always available to help anybody having trouble (as me, when I was not able to launch the installation of the SQLite Studio program).

3 Leave a comment on paragraph 3 0 With the help of the post “Fun Times with SQLite! Or, a Beginner’s Tutorial to Data Management and Databases with SQL“, we were introduced to the world of databases by briefly discussing what a database is (a collection of data that is structured to allow for manipulation), and more specifically what is the definition of relational database (databases where the data is contained in different tables). We were then immediately introduced to SQL (Structured Query Language), the programming language for interacting with data in a relational database we will employ for the workshop, and required to download SQLiteStudio, the database manager we will be using.

4 Leave a comment on paragraph 4 0 At this point, Ian led us in a step-by-step process to create our first database. After creating and name it, we learned how to create the content of our databases: in other words, how to create tables and fill them with the content we need to store. For the workshop, academic programs and students were the data we need to store in different tables.

5 Leave a comment on paragraph 5 0 The first step was to manage and discover how the SQL language works with different kinds of data. For instance, the Primary Key is the unique identifier for each record in the table while, talking about the functions, Autoincrement is set to increment automatically for each new record added to the table, Not Null establishes that no record will be allowed in the table without a value and Varchar defines the characteristics of the data inserted (a string of characters).

6 Leave a comment on paragraph 6 0 After having established the structure of the table, we inserted some data such as the names of the academic programs: this allowed us to see each of the row of the table with the appropriate academic program we wanted to insert there.

7 Leave a comment on paragraph 7 0 We were then learned how to add a new ‘column’ to our table, which until now have only two columns: one marked by ‘id’, which listed the number of students enrolled in each program, and another by ‘program’, where the different academic programs were listed. After that, we need to populate the new column with data for each existing record, and we did this by using the Update command in the SQL code, which allowed us to modify what we previously created. We also discovered other commands, such as Where – to control and define which records will be updated (it works by relating the update of a field with the content in another field of the table, allowing us to operate simultaneously on different part of our table).

8 Leave a comment on paragraph 8 0 After having created a table for the academic programs, we were required to set up a new table, this time for student, and we used the same syntax that we used to create the programs table, but with one extra element: a foreign key. This is a very important function, because it allows us to establish a relationship between two different tables: the students and the programs tables (more specifically, it is linked to the Primary Key of another table). This is specified by the command References, which links one field in one table to another field in another table: this relationship requires that all records in one table point to a valid primary key in the other one.

9 Leave a comment on paragraph 9 0 At this point, all was able to do some queries using the command Select, which – combined with From and Where – allows to focus on specific data. For instance, we were shown all the students from the Student table; all the students which the corresponding ID is 3; all the students whose name start with a specific letter. It is also possible to coordinate data from two different tables.


Source: https://dhpraxisfall16.commons.gc.cuny.edu/2016/10/15/workshop-databases-part-i-oct-4/

Need help with the Commons? Visit our
help page
Send us a message
Skip to toolbar