1 / 19

CTFS Workshop

CTFS Workshop. Shameema Esufali Asian data coordinator and technical resource for the network shameemaesufali@gmail.com. CTFS Workshop Relational database basics. Why relational databases? Why MySQL? What about R?. Relational Theory.

jeaves
Download Presentation

CTFS Workshop

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. CTFS Workshop Shameema Esufali Asian data coordinator and technical resource for the network shameemaesufali@gmail.com

  2. CTFS WorkshopRelational database basics Why relational databases? Why MySQL? What about R?

  3. Relational Theory In order to work with MySQL it is necessary to understand the basics of relational theory. i.e how and why data is stored and managed in a relational database. The guiding principle behind a relational database is to store data once and only once.

  4. What is a Relation? A table. Columns are fields (attributes) of data related to other fields on the same row (tuple).

  5. Primary Key • Identifies the row of a table without duplicates. • Tells you what the row contains • Eg. If treeid is the primary key then the row has information about that tree

  6. Candidate Primary Key Any attribute(s) which together would serve as the primary key. Must uniquely identify a row of data. Each part of the key must be essential to unique identification. No redundancy.

  7. Foreign Key A foreign key is a column in a table that matches the primary key column of another table. Its function is to link the basic data of two entities on demand, i.e. when two tables are joined using the common key.

  8. First Normal Form One piece of information per column. No repeated rows. Eliminate fused data eg Code1,Code2 Wrong! Right

  9. Second Normal Form Each column depends on the entire primary key. Wrong Right

  10. Third Normal Form Each column depends ONLY on the primary key. i.e. there are no transitive dependencies Wrong Right

  11. Fourth Normal Form The table must contain no more than one multi-valued dependency

  12. Entity Relationship diagram (ERD) Shows in a diagram how entities (tables) are related to one another. One to One One to many Many to many

  13. One to one • Extension of number of attributes in a single table • Rarely required

  14. One to Many • Most common • Requires two tables. • Linked by Foreign Key

  15. Many to many • Need to break down to one to many • Requires three tables • Associative table provides common key

  16. Reassembling data Data was broken down into tables to preserve integrity How can we put it together to derive information? Use Structured Query Language (SQL) to JOIN tables using a common attribute

  17. Joins Primary Key in Parent • Two tables may be joined when they share at least one common attribute • The Primary key of the Parent table is stored in the Child table as a cross reference. This is called a Foreign Key. Foreign Key in Child

  18. Table joined on Foreign Key GenusID The Genus ID in the Species table is used to pick up information for the corresponding Genus. It looks for a row with the matching Primary Key

  19. Extend to join many tables With SQL you can join as many tables as you need to in order to get the set of information you need. Thus the previous example can be extended to include Family which is a parent table of Genus and/or extended in the another direction to include Tree which is a child of Species as long as there is a linking attribute. This attribute is called a Foreign Key.

More Related