We published an on-line version of the slides used today to introduce some fundamental command you’ll routinely use during the practicals.
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.
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 🙂
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.
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 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:
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
Dear students,
we are approaching the end of this perlfect course… so we’d like to remind you about the two last practicals:
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.