Documents
Resources
Learning Center
Upload
Plans & pricing Sign in
Sign Out

Databases

VIEWS: 5 PAGES: 27

									Databases
 Norie de la Cruz
            What is a database?

 In the broadest sense a database is an
  organized collection of data
 In a more esoteric sense, a database is a
  model of a model of reality -- Kroenke
 Examples: tally sheets, library card
  catalogue, MS Access, RGD
    For this project we will focus on Relational
        Databases and Relational Database
                Management system



 “a set of tables that are interrelated in certain
  ways” – J. Tisdall
            Relational Database
            Management System
 Integrated data – all application data stored in one
  place
 Reduced data duplication – keep similar pieces of
  information in one place so that the user can
  employ them in multiple places
 Program/Data independence – modularization of
  data component
 Processing information – Databases catagorize,
  sort, filter and pool items in multiple ways without
  duplicating the data.
       Some relational database
              concepts
 Tables – holds the data, composed of rows
  and columns
 Row – A unique collection of related data
 Column – also called fields, categories to
  which individual data items belong
                                Tables

Band            Peak era    Genre           Best album    Worst song


Led Zeppellin   Late 70’s   Rock            LZ 4          Lemon Song



Duran-duran     Early       Rock            Rio           New moon on
                80’s                                      Monday


Beatles         Early       Rock and roll   Abbey Road    I wanna hold
                70’s                                      your hand


Rush            Early 80    Art Rock        Hemispheres   Bytor and the
                                                          snow dog
       More Relational Database
              Concepts

 Relationships – how data within tables fit
  together
 Keys – data identifiers and pointers
             Relationships and keys

id   First    Last name   Role     Band
     name                          Key
                                          Band id Band
1    John     Lennon      Vocals   3              name

2    Paul     McArtney    Bass     3
                                          1      Led ..

3    Ringo    Starr       Drums    3      2      Duran

4    Jimmy    Page        Lead     1      3      Beatles
                          Guitar                 …
5    Geddy    Lee         Vocals   4      4      Rush …
      And now for the fun part…
           data modeling
 Remember, ideally in programming actual
  coding constitutes ~15% of development
  time – same is probably true in constructing a
  database
 Recall, a database is a model of a model of
  reality
    Data Modeling – Tools of the
               trade
 Entity-Relationship diagrams
 Semantic Object Models
                  Data Modeling

 Entity- something that can be identified in the
  user’s environment, something the users want to
  track.
 Attributes – properties of entities
 Identifiers – a quality that points to an instance of
  an entity
 Relationships – how entities are associated with
  each other
   Data Modeling – three types of
          relationships
 One-to-one, e.g., capital to state
 One-to-many, e.g., author to monograph
 Many-to-many, e.g., thespians to plays
         The Entity-Relationship
                Diagram

                                                        Trade
                                                        • Volume
                                           M        M
                            State
                            • Name                         M
                    1   1   •Size
Capital
                            • Population
•Name                                           M
•Location
• Main Attraction               1

                                                           M
                                M                   M
                                                         Product
                            National Park                • Capacity
                            • Name                       • Price
                            • Main Attraction
     Optimizing your design – you want to get
    to the information you need as efficiently as
                      possible

Exercise: Take these concepts and create E-R diagrams for dbs
  that will be used to study protein structure and function, and
  a db used to study phylogeny:

     Gene                    Chromosome             Homology
     Sequence                Species                 Synteny
     Intron                  Splice variant
     Exon                    Protein
     Physiological Process   Protein Domain
                              Biochemical Function
E-R diagram for DB focused on
      structure - function
                   Sequence                          1        Protein Domain
                                             1
   Introns             1            Exon                      M        1
                                M                                                 1
      M                 1
                                        M                                       Structure
                 Gene                                    Protein
             1   • chromosome   1
                 • species                                         M
                                                          1

                                                                            M
                                                 M
                                                                   Function
                                                      1
                                    Splice Variant                      M



                                                                           M

                                                                   Process
                      E-R diagram focused on
                       phylogenetic studies
                                                                1
                   Homology                      Sequence
                                     M                                  1
                                         1
                                                       1
                                                                    M       Intron
                                                       1    1
                                                                                     0
                             M               M                      1
          Species                                   Gene                                 Protein
          • classification
                                                   M                                     M
              M

          M                                                                                           M

Synteny                                                                                            Function
              M
                  M              1

              Chromosome
         The Entity-Relationship
                Diagram
                        M
      Alliances
      •Name
      •HQ                                                   Trade
                            M
                                                            • Volume
                                               M        M
                                State
                                • Name                         M
                    1       1   •Size
Capital
                                • Population
•Name                                               M
•Location
• Main Attraction                   1

                                                               M
                                    M                   M
                                                             Product
                                National Park                • Capacity
                                • Name                       • Price
                                • Main Attraction
               From E-R to the Tables

 Entities are captured as tables        State ID   State Name    Capital Id         …


 The attributes are the fields          1          England       1                  …


 Keys point to data in other            2          France        3                  …


  tables that are relevant to that       3          US            2                  …

  entity
                                     Capital ID      Capital          Location   …       …
                                                     Name             …

                                     1               London           …          …       …


                                     2               Washington       …          …       …
                                                     DC

                                     3               Paris            …          …       …
             From E-R to the Tables – one to many and
              many to many relationships are captured
                       through join tables
                                                                     Country          Country       Volu        Product
       Park ID               Park Name         State   …             1 ID             2 ID          me          ID
                                               ID
       1                     Yosemite          3       …             1                3             $500        1
                                                                                                    M
       2                     Grand Canyon      3       …
                                                                     2                1             $2          2

Alliance         Name               HQ                     …
ID
1                NATO               Brussels               …   A-S   Als          St            State          State      Capital Id   …
                                                               ID    ID           ID            ID             Name
2                EU                 Brussels               …   1     1            1
                                                                                                1              England    1            …
3                NBTDBDUS           Paris                  …   2     1            3
                                                                                                2              France     3            …
                                                               3     2            1
                                                               4     2            2             3              US         2            …
Capital ID       Capital          Locatio      …       …
                 Name             n…                           5     3            2
1                London           …            …       …       6     3            2

2                Washingto        …            …       …
                                                               7     3            2
                 n DC
                                                                     Product ID                         Product name      Price …
3                Paris            …            …       …
                                                                     1                                  Beef              $0.25


                                                                     2                                  Wine              $ 40
         Building and accessing the DB
               programmatically

 APIs
 SQL
                Building and accessing the DB
                  programmatically -- API

Perl code for connecting to DB:
use DBI;

$db = 'physician';
$user = 'root';
$passwd = 'WYGymf01';

$dbh = DBI->connect('DBI:mysql:physician_data_transfer', $user, $passwd) or die
   "Cannot open db\n";
                    Building and accessing the DB
                      programmatically -- API

Perl code for pulling data from DB:
#create db handle
$dbh_synonym_data = DBI ->connect('DBI:mysql:physician_data_transfer', $user, $passwd) or die
     "Cannot open db\n";
# command to be executed
$sql_datafetch_command = 'SELECT synonyms FROM emphasis';
# create handle
$sth_datafetch = $dbh_synonym_data -> prepare($sql_datafetch_command);
# get data
$sth_datafetch->execute();

while (@rows = $sth_datafetch->fetchrow_array) {
 ### do something with data in row.
 } # end for each
        Building and accessing the DB
          programmatically -- API

Using API and sequence commands you can
  control the db programmatically
 Build tables
 Update the data
 Create new dbs
 Etc…
    Obtaining data from the DB --
               reports
 Pull data in a format that is readily useable
 Customizable interfaces available
               Interacting with records --
                         forms
   Build in error
    checking
   User friendly interface
    to the data
        Database Administration

 Referential integrity
 Data QC
 Security
 Concurrency
 Update
                 References

 Database processing: fundamentals, design,
  and implementation by Kroenke, D.M.
 Oracle9i for dummies by McCullogh-
  Dieter,C.
 Mastering Perl for Bioinformatics by
  Tisdall,J.

								
To top