Building_Database_Tables.md

Data Management

For Scientific Research

Photo: © Stanza. Used with permission.

Session 5: Building Database Tables

Today we will use an example to review data models and create a database design using MySQL Workbench.

Start MySQL Workbench

Please open up MySQL Workbench so you can follow along.

---

We will use the design to automatically create the database tables.

As we design the database on the screen, you should also create the same database design in MySQL Workbench on your own computer.

Example: Sample Testing

Structured Narrative:

1. Technician prepares prepared sample from sample using preparation method.

2. Technician performs lab test on prepared sample using test method.

The entities have been italicized. We will assume that all necessary computer interactions (i.e. data entry steps) are included in the "prepares" and "performs" actions.

Identifying the Entities and Relationhsips

And putting them in a table...

Entity	Relationship	Entity	Relationship	Entity
Technician	prepares	prepared sample	from	sample
		->	using	preparation method
Technician	performs	lab test	on	prepared sample
		->	using	test method

Conceptual Data Model

Canine AT-AT Model

Photo: John Nolan / Katie Mello / Star Wars Blog

Logical Data Model

Working together, we will create this EER diagram in MySQL Workbench.

Discussion

Explain our EER diagram.

Photo: John Nolan / Katie Mello / Star Wars Blog

Generating Database Tables

Using the "Forward Engineer" feature, we can save a schema file and create tables. The tables can be explored in "SQL Editor".

Logical Data Model Modified

In order to use these tables for generating a website with a framework (like CakePHP), we will need to make our table names plural to conform with the framework's conventions.

Logical Data Model Expanded

By adding more tables, we can track additional entities:

• Measurement Units
• Sample Type (Matrix)
• Clients
• Sample Disposal
• Instruments
• Analytes

And even so, we still are not tracking containers or "splits".

---

Database with Web Interface

With a few shell commands, we can create a functioning web application from our database schema. More on that later!

In the Coming Sessions...

• Structured Query Language (SQL)
• Database Applications and Web Frameworks
• Embedded SQL

Action Items (videos, readings, and tasks)

	MySQL WB: Creating an Entity-Relationship Diagram MySQL WB: Basic Relationships MySQL WB: Modeling Relationships in a MySQL ERD MySQL WB: Building a Database from an ERD
	PCfB textbook: Chapters 14 and 15 Skim: SAMS Teach Yourself SQL on 10 Minutes: Ch. 1-4 Optional- Skim: RDDaI3CE textbook: Chapters 7-8 Optional- Skim: SQLCE3 textbook: Chapters 1-2
	Normalize your Logical Data Model (ERD) in MySQL WB "Forward Engineer" your ERD to create a schema file and tables Post your schema and EER diagram(s) in your project wiki Refer to the example in the "Data Management" project wiki

See Also

• MySQL WB Reference Manual
• SQL Integrity Constraints
• Database Schema
• Schema
• SQLite vs MySQL vs PostgreSQL: A Comparison Of Relational Database Management Systems

Questions and Comments

Image: © Nevit Dilmen / Wikimedia

Some Parting Words

If you welcome chaos, chaos is gonna organize itself for you, in front of your own eyes.

--Philippe Petit on Creative Chaos

Image: Wiros / Wikimedia