in agreement with your request, the last practical is scheduled for Monday the 11th
see you at aula M piano rialzato at 2:30 pm to start with brief conclusions concerning the gap filling experiments
we will then move to “Paolotti” to perform the last fiew exercises in the bioinformatics laboratory
tomorrow, Jun the 8th 2012 there will be no classes
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 🙂
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.
As promised during today’s lecture, here you have your homework:
please calculate the amount of water to be added to each of the primers in the list, in order to produce a working solution of 10 µM. You can produce a concentrated stock solution and then dilute the stock solution to a working solution of 10 µM, if it is needed.
Oligonucleotide datasheet containing all the information as provided by the supplier
Did you managed to find the right volume for each stock solution?
The Hamilton pipetter needs you to provide a coordinate and a volume separated by a tab for each of the primer. The information concerning each of the primers must be separated by a new line character, as shown in the example below
example of coordinate's file that you should pass to the Robot, to guide it (him?) doing the pipetting for you
Now try to translate the work that you performed to get this result into a list of actions (the algoritm).
For the most willing students, this is the moment for translating your algoritm into a Perl program and testing it at volume calculation for this short primer’s list.
Very soon you will find the solution on this blog. Stay tuned
