We published an on-line version of the slides used today to introduce some fundamental command you’ll routinely use during the practicals.
Perl & Genomics
Bioinformatics for Genomics Course – University of Padua
Author Archives: Andrea Telatin
Practicals, 2014 edition
Hello guys, we are going to start the 2014 edition of the Genomics practicals today, in Aula M Pr at the Vallisneri Building. We’ll move to the Computer Room later on.
Today we’ll have see how to work in a Linux environment, mastering the command line interface (shell). Should you be curious to see past practicals on same argument, here you are: a post on this and another one and don’t forget the autocompletion.
See you later,
Andrea and Giovanni
Last lab guys!
We are approaching the end of a cycle… during this genomics laboratory you put your hands on genome sequencing and finishing, and of course you had a nice «primer» on Perl programming, perhaps with a boost in your primer design skills as an extra bonus.
It’s time to sum up, with a «self service» laboratory…
Due to the remarkable success of this year laboratory, that has been an experiment I wanted to try, we will repeat the 20 hours “Perl practical” next year. So stay tuned 🙂
See you this afternoon,
Andrea
Refresh your Perl, the size you want.
Last laboratory: queries on a database
Welcome back guys!
Please, start reviewing what databases are so that we all have a common background when talking about tables and fields. Then we prepared a very short introduction to the SQL language:
These tasks are quite common, but not so useful to understand how databases works so we’ll just have a look to them.
Today you’ll play with the Linux machine with a good level of independence. Tomorrow we’ll use the CIV computer room where there is the possibility to use a projector for a more “guided” and commented experience. Consider this as tomorrow we will practice more with queries… while only today you’ll have the possibility to play with Perl if you wish to do so 🙂
MySQL: add a new record
We can add a new record with a statement like this one:
INSERT INTO reagents (name, supplier, amount) VALUES ('Trizma Base', 12, 100);
Note that we list the fields we want to populate and then the values in the same order. See more examples.
MySQL: creating a new table
The very nice thing about MySQL is that… it’s very similar to fluent English. So if we want to create a table we must decide in advance the list of fields we need.
For each field we need to specify the type (will we store a number? a date? a name?). A list of fields types is available here.
Remember that MySQL is case-insensitive, while table names and field names are case-sensitive. It’s common to type SQL commands in upper case though, and to choose lowercase names. The “create table” command requires a name and a list of fields.
CREATE TABLE reagents ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), supplier INT, amount INT, arrived DATE);
A single command ends with “;” in MySQL shell (this is not always true for SQL languages), so that we can split a single command across multiple lines. We choosed a list of fields and some of them store a number, another a date, the name is a string (varchar).
Each table has to have a “Primary Key“, a unique identifier for the record. A common practice is to use an integer with the “auto_increment” keyword that means: every time you create a record, the “id” field will be incremented.
If we later want to add a new field there’s a simple command to do this:
UPDATE reagents ADD (casnumber VARCHAR(20));
MySQL: first commands into the shell
MySQL has its own shell. We can enter using the following command:
mysql -h servername -u username -p
Where “servername” can be omitted if we installed MySQL in our computer. The prompt switches from “$” to “mysql>” and we can type SQL commands.
We want to:
- list the databases
- select a particular database
- list the tables in that database
- list the fields of a table
Databases: the very basic introduction
Databases… a whole ‘big thing’ in the bioinformatics field. Databases are the “backbone” of all the public data banks of course, but can be very useful for many other aspects. Even our scaffolding program uses a database for fast data retrieval.
Databases stores data, as a very simple “text file”. But they have a powerful engine to quickly retrieve data using selected criteria. Consider our “pick primer” program: it takes a while to take the sequence of both contigs because it has to parse the file containing all of them. If we decided to store all sequences into a database we could perform a simple query to get them.
This blog is powered by a quite complex database. Continue reading
Last soupper
Dear students,
we are approaching the end of this perlfect course… so we’d like to remind you about the two last practicals:
- 7th june – 2.30 pm, aula di bioinformatica via Paolotti
- 8th june – 2.30 pm, aula CIV, Vallisneri
Note that these dates are the correct ones, while we noted that the official Molecular Biology timetable seems to be wrong.
Attendance to these classes is not compulsory. People that decide to join us are requested to stay until the end of the practicals and to attend both.
Cheers,
Andrea and Elisa
PS: The test will cover some Perl basics, the general laboratory strategy (assembly, scaffolding, gap filling) and what you did in the wet lab. Don’t be afraid about Perl: the more you know the better it is but we don’t expect you to write a whole program from scratch. Reviewing past posts about our “pick primer” program is highly recommended though.