Team:Bielefeld-Germany/Project/Database
From 2012.igem.org
(→Design of the database) |
|||
Line 10: | Line 10: | ||
__TOC__ | __TOC__ | ||
- | One important thing while working in such a big | + | One important thing while working in such a big team like ours is the organization of samples. Therefore we designed our own database in which we can make entries for each sample we use. This page gives you detailed information on how it was designed and implementet. |
=Design of the database = | =Design of the database = | ||
Line 18: | Line 18: | ||
[[File:Bielefeld2012_Tabellen_Datenabnk.jpg|500px|thumb|right|Figure 1: The initial design of the database.]] | [[File:Bielefeld2012_Tabellen_Datenabnk.jpg|500px|thumb|right|Figure 1: The initial design of the database.]] | ||
- | We need the User table for the login and for the allocation of entries to the team members. In the User table we create | + | We need the User table for the login and for the allocation of entries to the team members. In the User table we create an ID specific for each team member. This ID can be used as a foreign key in the other tables. |
Additionally we want to realize the following particularities: | Additionally we want to realize the following particularities: | ||
- | :* For the <strong> Eppis table</strong> (and with Eppi we always mean reaction tubes) it is very important, that you can always find out were a sample is. So we decided that we have to write a script which controls if you insert a place of storage to | + | :* For the <strong> Eppis table</strong> (and with Eppi we always mean reaction tubes) it is very important, that you can always find out were a sample is. So we decided that we have to write a script which controls if you insert a place of storage to your database entry, otherwise this entry could not be saved. |
- | :* For the <strong>Plates table</strong> we want to write a script which checks if the date of construction is older than two weeks. If the entry is older than these two weeks, the entry will | + | :* For the <strong>Plates table</strong> we want to write a script which checks if the date of construction is older than two weeks. If the entry is older than these two weeks, the entry will be marked in red automatically. So we have an easy way to check if all our plates are fresh. |
:* For the <strong>BioBricks table</strong> we want to name our BioBricks automatically. Therefore we write a script, which assigned a BioBrick number, according to the affiliation to either eukaryotic, bacterial, fungal or others. | :* For the <strong>BioBricks table</strong> we want to name our BioBricks automatically. Therefore we write a script, which assigned a BioBrick number, according to the affiliation to either eukaryotic, bacterial, fungal or others. | ||
- | :* One other very important point was, that the editing of entries is secured against mistakes, for example that one person | + | :* One other very important point was, that the editing of entries is secured against mistakes, for example that one person creats an entry and and another person deletes it by mistake. Hence, we write a script which is able to conserve the entries, even though if they were deleted. It saves the entry as a copy in the datebase but just shows the newest copy of an entry. With this script it is possible to rebuild an entry. |
:* We did the same for the editing script. | :* We did the same for the editing script. | ||
Line 36: | Line 36: | ||
*<strong> conceptual design</strong>: In this phase of the design we try to find relations between the different tables and build a entity-relationship-model. This model helps to visualize the entities (our different tables) together with their attributes (the entries belonging to a table) and the relationship between these entities. | *<strong> conceptual design</strong>: In this phase of the design we try to find relations between the different tables and build a entity-relationship-model. This model helps to visualize the entities (our different tables) together with their attributes (the entries belonging to a table) and the relationship between these entities. | ||
- | :* We want to combine the tables | + | :* We want to combine the tables Epp itable with Plates table and BioBrick table with Sequencing table. |
:* And of course the User table is combined to all tables because of the foreign keys. | :* And of course the User table is combined to all tables because of the foreign keys. | ||
Line 44: | Line 44: | ||
- | *<strong> logically design </strong>: After this we | + | *<strong> logically design </strong>: After this we thought about the logically design of our database. In this phase the conceptual design is implemented with the help of a precise data model. For example a relational design. We decided to use such a design. |
- | For the Eppi table the relational design is shown | + | For the Eppi table the relational design is shown exemplarily. |
{| | {| | ||
Line 83: | Line 83: | ||
== Used Languages == | == Used Languages == | ||
- | For the programming language we used PHP. PHP is a general-purpose server-side scripting language and is originally designed for Web development to produce dynamic Web pages. It is one of the first developed server-side scripting languages to be embedded into an HTML source document and can also be used with many relational database management systems (RDBMS). As RDBMS and therefore for the construction of the tables in the database we used MySQL. MySQL is the world's most used open source RDBMS for the use in web applications. The SQL phrase stands for Structured Query Language. For the design of the tables and the look of them we use HTML. HTML | + | For the programming language we used PHP. PHP is a general-purpose server-side scripting language and is originally designed for Web development to produce dynamic Web pages. It is one of the first developed server-side scripting languages to be embedded into an HTML source document and can also be used with many relational database management systems (RDBMS). As RDBMS and therefore for the construction of the tables in the database we used MySQL. MySQL is the world's most used open source RDBMS for the use in web applications. The SQL phrase stands for Structured Query Language. For the design of the tables and the look of them we use HTML. HTML stands for HyperText Markup Language and it is the main markup language for displaying web pages and other information that can be displayed in a web browser. We used Cascading Style Sheets (CSS) hence we do not have to design each table individually. This is a style sheet language used for describing the look and formatting of a document written in a markup language like HTML. |
== Realization of the design== | == Realization of the design== | ||
- | [[File:Bielefeld2012_Login.png|200px|thumb|right|Figure 1: Screen shot of our Login | + | [[File:Bielefeld2012_Login.png|200px|thumb|right|Figure 1: Screen shot of our Login!]] |
If you enter to our database you first have to log in, therefore you get a User name and a password (both saved in the User table). | If you enter to our database you first have to log in, therefore you get a User name and a password (both saved in the User table). | ||
Line 110: | Line 110: | ||
- | On top of the page you see the search tool. With using _ for <strong>one</strong> letter after the search text and * for any number of letters after the search text you | + | On top of the page you see the search tool. With using the underscore ( _ ) for <strong>one</strong> letter after the search text and the asterisk ( * )for any number of letters after the search text you specific can look of entries in the selected table. |
== Important Functions == | == Important Functions == |
Revision as of 16:38, 26 September 2012
Contents |
One important thing while working in such a big team like ours is the organization of samples. Therefore we designed our own database in which we can make entries for each sample we use. This page gives you detailed information on how it was designed and implementet.
Design of the database
- initial design : First of all we considered which tables and which listings of the different tables were necessary. Here you can see our first abstract thoughts. This part of the design of a database is called the initial design of a database.
We need the User table for the login and for the allocation of entries to the team members. In the User table we create an ID specific for each team member. This ID can be used as a foreign key in the other tables.
Additionally we want to realize the following particularities:
- For the Eppis table (and with Eppi we always mean reaction tubes) it is very important, that you can always find out were a sample is. So we decided that we have to write a script which controls if you insert a place of storage to your database entry, otherwise this entry could not be saved.
- For the Plates table we want to write a script which checks if the date of construction is older than two weeks. If the entry is older than these two weeks, the entry will be marked in red automatically. So we have an easy way to check if all our plates are fresh.
- For the BioBricks table we want to name our BioBricks automatically. Therefore we write a script, which assigned a BioBrick number, according to the affiliation to either eukaryotic, bacterial, fungal or others.
- One other very important point was, that the editing of entries is secured against mistakes, for example that one person creats an entry and and another person deletes it by mistake. Hence, we write a script which is able to conserve the entries, even though if they were deleted. It saves the entry as a copy in the datebase but just shows the newest copy of an entry. With this script it is possible to rebuild an entry.
- We did the same for the editing script.
- conceptual design: In this phase of the design we try to find relations between the different tables and build a entity-relationship-model. This model helps to visualize the entities (our different tables) together with their attributes (the entries belonging to a table) and the relationship between these entities.
- We want to combine the tables Epp itable with Plates table and BioBrick table with Sequencing table.
- And of course the User table is combined to all tables because of the foreign keys.
In the graphic you can see our very easy E-R-Model.
- logically design : After this we thought about the logically design of our database. In this phase the conceptual design is implemented with the help of a precise data model. For example a relational design. We decided to use such a design.
For the Eppi table the relational design is shown exemplarily.
Eppi ( | ID INT(11), |
DataID INT(11), | |
Caption TEXT, | |
Content TEXT, | |
Experiment VARCHAR(255), | |
Where2 VARCHAR(255) , | |
Location TEXT, | |
Comments TEXT, | |
Empty ENUM ('N', 'J'), | |
Create_User INT(11), | |
Create_Date DATETIME, | |
Edit_Date DATETIME, | |
Delete ENUM ('N', 'J') | |
Delete_User INT(11), | |
Delete_Date DATETIME ) |
Programming the Database
Used Languages
For the programming language we used PHP. PHP is a general-purpose server-side scripting language and is originally designed for Web development to produce dynamic Web pages. It is one of the first developed server-side scripting languages to be embedded into an HTML source document and can also be used with many relational database management systems (RDBMS). As RDBMS and therefore for the construction of the tables in the database we used MySQL. MySQL is the world's most used open source RDBMS for the use in web applications. The SQL phrase stands for Structured Query Language. For the design of the tables and the look of them we use HTML. HTML stands for HyperText Markup Language and it is the main markup language for displaying web pages and other information that can be displayed in a web browser. We used Cascading Style Sheets (CSS) hence we do not have to design each table individually. This is a style sheet language used for describing the look and formatting of a document written in a markup language like HTML.
Realization of the design
If you enter to our database you first have to log in, therefore you get a User name and a password (both saved in the User table).
After the successful log in you have the opportunity to enter to either the Eppi, the Plates, the Sequencing or the BioBrick table. The User table is only visible for the administrator.
On top of the page you see the search tool. With using the underscore ( _ ) for one letter after the search text and the asterisk ( * )for any number of letters after the search text you specific can look of entries in the selected table.
Important Functions
Here we listed some important scripts we used. (click to the black arrows to watch the next one)
55px |