Team:Bielefeld-Germany/Project/Database

From 2012.igem.org

(Difference between revisions)
Line 6: Line 6:
             </span>
             </span>
         </div>
         </div>
-
        <div id="grey_bg">
 
</html>
</html>
__TOC__
__TOC__
Line 22: Line 21:
Additionally we want to realize the following particularities:
Additionally we want to realize the following particularities:
-
* For the <strong>Plates table</strong> we want to write a script witch checks if the date of construction is older than two weeks. If the week is longer ago than these two weeks, the entry will bee marked in red automatically. So we have a easy way to check if all our plate are fresh.  
+
** For the <strong>Plates table</strong> we want to write a script witch checks if the date of construction is older than two weeks. If the week is longer ago than these two weeks, the entry will bee marked in red automatically. So we have a easy way to check if all our plate are fresh.  
-
* For the <strong>BioBricks table</strong> we want to name our BioBricks automatically. Therefore we write a script, witch 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, witch assigned a BioBrick number, according to the affiliation to either eukaryotic, bacterial, fungal or others.  
-
* For the <strong> Eppis table</strong> it is very important, that you can always find out were a probe is. So we decides that we have to write a script witch controls if you insert a place of storage to you database entry, otherwise this entry couldn´t bee saved.  
+
** For the <strong> Eppis table</strong> it is very important, that you can always find out were a probe is. So we decides that we have to write a script witch controls if you insert a place of storage to you database entry, otherwise this entry couldn´t bee saved.  
-
* One other very important point was, that the editing of entries is secured against mistakes, for example that one person make an entry and the other deleted it by mistake. Therefore we write a delete script witch deleted an entry not really. It saves the entry as a copy in the datebase but just shows ne newest copy of an entry. Witch this script t is possible to rebuild an entry if it is necessary.  
+
** One other very important point was, that the editing of entries is secured against mistakes, for example that one person make an entry and the other deleted it by mistake. Therefore we write a delete script witch deleted an entry not really. It saves the entry as a copy in the datebase but just shows ne newest copy of an entry. Witch this script t is possible to rebuild an entry if it is necessary.
 +
 
 +
** We did the same for the editing script.  
-
* We did the same for the editing script.
 
*<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 Eppi with Plates and BioBrick with Sequencing.
 +
* And of course is the User table combined to all tables because of the foreign keys.
In the graphic below you can see our very easy E-R-Model.
In the graphic below you can see our very easy E-R-Model.
Line 41: Line 44:
-
*<strong>  logically design </strong>: Creating the logically design for the database. For example for our table 'Eppi' we opt for the following logic:
+
*<strong>  logically design </strong>: After this we think about the logically design of our database. In this phase the conceptual design is implemented with the help of a concrete data model. For Example as a relational design. We decided to use such a design.
 +
For the Eppi table the relational design is shown exemplary.
 +
 
{|  
{|  
-
|eppi ( || ID INT(11),
+
|Eppi ( || ID INT(11),
|-
|-
|      ||DataID INT(11),
|      ||DataID INT(11),
Line 57: Line 62:
| ||Location TEXT,
| ||Location TEXT,
|-
|-
-
| ||Coments TEXT,
+
| ||Comments TEXT,
|-
|-
| ||Empty ENUM ('N', 'J'),
| ||Empty ENUM ('N', 'J'),
Line 63: Line 68:
| ||Create_User INT(11),  
| ||Create_User INT(11),  
|-
|-
-
| ||Creat_Date DATETIME,
+
| ||Create_Date DATETIME,
|-
|-
| ||Edit_Date DATETIME,  
| ||Edit_Date DATETIME,  
Line 73: Line 78:
| ||Delete_Date DATETIME )
| ||Delete_Date DATETIME )
|}
|}
 +
=Programming the Database=
=Programming the Database=
Line 78: Line 84:
== 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 therefor 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 stand 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. So that we don´t have to design individually each table we use Cascading Style Sheets (CSS. This is a style sheet language used for describing the look and formatting of a document written in a markup language like 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 therefor 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 stand 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. So that we don´t have to design individually each table we use Cascading Style Sheets (CSS. This is a style sheet language used for describing the look and formatting of a document written in a markup language like HTML.
-
== Important Functions ==
+
== Realization of the design==
-
Here we listed some important functions we used.  
+
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).  
 +
[[File:Bielefeld2012_Login.png|300px|thumb|right|Figure 1: Screen shot of our Login!!]]
-
Here you can see want we planed to edit I the next weeks.  
+
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.  
 +
[[File:Bielefeld2012_plates.png|center|thumb|800px|Figure 1: Screen shot of our Plates table with example entries.]]
 +
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 can 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)
<html>
<html>
-
  </div>
+
 
 +
<!-- Flexslider CSS and JS -->
 +
<link rel="stylesheet" href="http://2012.igem-bielefeld.de/includes/wiki/flex/css/flexslider.css" type="text/css">
 +
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.6.2/jquery.min.js"></script>
 +
<script src="http://2012.igem-bielefeld.de/includes/wiki/flex/js/jquery.flexslider.js"></script>
 +
 
 +
<!-- Load the flexslider -->
 +
<script type="text/javascript" charset="utf-8">
 +
  $(window).load(function() {
 +
    $('.flexslider').flexslider({
 +
          animation: "fade",  // slide or fade
 +
          controlsContainer: ".flex-container" // the container that holds the flexslider
 +
    });
 +
  });
 +
</script>
 +
 
 +
<!-- Slider Container and Images -->
 +
<div style="padding:5px; z-index:1;">
 +
 
 +
        <div class="flexslider">
 +
            <ul class="slides">
 +
                  <li>
 +
                    <div class="frontpage-slide">
 +
                        <img src="">
 +
                        <div class="slide-right-col">
 +
                            <div class="slide-heading">
 +
                                The Add Script
 +
                            </div>
 +
                         
 +
 
 +
 
 +
                        </div>
 +
                        <div class=clear></div>
 +
                    </div>
 +
                </li>
 +
                <li>
 +
                    <div class="frontpage-slide">
 +
                        <img src="">
 +
                        <div class="slide-right-col">
 +
                            <div class="slide-heading">
 +
                              The Editing Script
 +
                            </div>
 +
 
 +
 
 +
                           
 +
                        </div>
 +
                        <div class=clear></div>
 +
                    </div>
 +
                </li>
 +
                <li>
 +
                    <div class="frontpage-slide">
 +
                        <img src="">
 +
                        <div class="slide-right-col">
 +
                            <div class="slide-heading">
 +
                                The Deletion Script
 +
                            </div>
 +
                           
 +
 
 +
                        </div>
 +
                        <div class=clear></div>
 +
                    </div>
 +
                </li>
 +
                <li>
 +
                    <div class="frontpage-slide">
 +
                      <img src="h">
 +
                        <div class="slide-right-col">
 +
                            <div class="slide-heading">
 +
                                The Login Script
 +
                            </div>
 +
                         
 +
 
 +
 
 +
                        </div>
 +
 
 +
                        <div class=clear></div>
 +
                    </div>
 +
                </li>
 +
                <li>
 +
                    <div class="frontpage-slide">
 +
                      <img src="h">
 +
                        <div class="slide-right-col">
 +
                            <div class="slide-heading">
 +
                                The Search Script
 +
                            </div>
 +
                         
 +
 
 +
 
 +
                        </div>
 +
 
 +
                        <div class=clear></div>
 +
                    </div>
 +
                </li>
 +
               
 +
               
 +
             
 +
            </ul>
 +
        </div>
 +
 
 +
    </div>
 +
 
</html>
</html>
 +
 +
 +
 +
 +
 +
 +
 +
{{Team:Bielefeld/Sponsoren}}
{{Team:Bielefeld/Sponsoren}}

Revision as of 19:52, 25 September 2012

Database

Contents


One important thing while working in such a bit group like ours is the organization of probes. Therefore we designed our own database where we can make an entry for each probe we use. On this page you can find all information about this dadabase.

Design of the database

  • initial design  : First of all we think about the tables and the listings of the different tables we need. Here you can see our first abstract thoughts. This part of the design of a database is called the initial design of a database.
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 erect a 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 Plates table we want to write a script witch checks if the date of construction is older than two weeks. If the week is longer ago than these two weeks, the entry will bee marked in red automatically. So we have a easy way to check if all our plate are fresh.
    • For the BioBricks table we want to name our BioBricks automatically. Therefore we write a script, witch assigned a BioBrick number, according to the affiliation to either eukaryotic, bacterial, fungal or others.
    • For the Eppis table it is very important, that you can always find out were a probe is. So we decides that we have to write a script witch controls if you insert a place of storage to you database entry, otherwise this entry couldn´t bee saved.
    • One other very important point was, that the editing of entries is secured against mistakes, for example that one person make an entry and the other deleted it by mistake. Therefore we write a delete script witch deleted an entry not really. It saves the entry as a copy in the datebase but just shows ne newest copy of an entry. Witch this script t is possible to rebuild an entry if it is necessary.
    • 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 Eppi with Plates and BioBrick with Sequencing.
  • And of course is the User table combined to all tables because of the foreign keys.

In the graphic below you can see our very easy E-R-Model.

Figure 1: E-R-Model of our database.


  • logically design : After this we think about the logically design of our database. In this phase the conceptual design is implemented with the help of a concrete data model. For Example as a relational design. We decided to use such a design.

For the Eppi table the relational design is shown exemplary.

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 therefor 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 stand 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. So that we don´t have to design individually each table we use Cascading Style Sheets (CSS. 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).

Figure 1: Screen shot of our Login!!

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.

Figure 1: Screen shot of our Plates table with example entries.

On top of the page you see the search tool. With using _ for one letter after the search text and * for any number of letters after the search text you can 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)

  • The Add Script
  • The Editing Script
  • The Deletion Script
  • The Login Script
  • The Search Script






55px Logo merck.jpg BioCircle.JPG Bielefeld2012 Evonik.jpg Bielefeld2012 Baxter.png Logo knauer.jpg Logo iit.jpg Bielefeld2012 BIEKUBA.jpg Logo biometra.jpg Logo bio-nrw.png Bielefeld2012 Logo ERASynbio.jpg