1 / 16

Database Design

Database Design. Presented to Molecular Biology Bioinformatics Meeting by Norbert Kremer April 29, 2002. Database Features. Efficient storage and retrieval of data relevant to a specific purpose Data integrity Security Performance, indexing Best design depends on application.

phil
Download Presentation

Database Design

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. Database Design Presented to Molecular Biology Bioinformatics Meeting by Norbert Kremer April 29, 2002

  2. Database Features • Efficient storage and retrieval of data relevant to a specific purpose • Data integrity • Security • Performance, indexing • Best design depends on application

  3. Database Technologies • Manual Systems • Shoebox full of index cards • Yellow stickies • Mechanical Systems • Hollerith Cards • Indexing holes

  4. Database Technologies • Automated Systems • Flat file, fields in fixed or delimited columns • Early database systems: network, hierarchical • Client-server paradigm: relational database management systems • Object databases, e.g. AceDB • Object-relational, e.g. Oracle 9 • XML, eg Neocore and Tamino

  5. RDBMS Features 1/2 • Data stored as tables with keys • Sound mathematical foundations in set theory, T.Codd, IBM Research Labs • Relational algebra – closure • Best for highly structured data

  6. RDBMS Features 2/2 • SQL is most common relational language • SQL is non-procedural, relationally complete • Robust, mature technology, performance and scalability, security, data integrity

  7. Relational Table Structure • Examples showing • One big table • Multiple tables with keys • Normalization Process • Data elements depend on the “key, the whole key, and nothing but the key”

  8. Data Modeling • Identify and define data elements • Discover logical relationships between data elements

  9. Software Engineering Methodology

  10. Data Integrity • Constraints enforced by DBMS • Transaction processing

  11. Data Integrity - Constraints • Constraints enforced by DBMS • Central location eases maintenance • Uniform application in all code • Cannot be subverted by malicious or lazy programming • Foreign keys

  12. Data Integrity - Transactions • ACID Properties • Atomic • Consistent • Isolated • Durable

  13. Data Integrity - Transactions • ACID Properties • Atomic Logical unit of work, complete all or roll back • Consistent All constraints observed • Isolated Transactions are independent • Durable “Commit” is irreversible

  14. Software Engineering Methodology

  15. PowerDesigner Demo • Graphical communication among analysts, architects, developers, end users • Centralized maintenance of database schema and data dictionary • Forward engineering (SQL script from model) • Reverse engineering (model from existing database) • Medline example, showing modeling process for “associative tables”

  16. References • WinSQL graphical database client for any ODBC-compliant database (just about all) Get the free Lite version from http://www.indus-soft.com/winsql/ • ODBC driver for MySQL (also need the usual MySQL client installed) http://www.mysql.com/downloads/api-myodbc-3.51.html • Powerdesigner 9 Trial The trial is fully functional for 45 days. See http://www.sybase.com/products/enterprisemodeling/powerdesigner

More Related