MySQL:First DataBase:Heights

Introduction
Before we delve into the finer details of MySQL I will demonstrate the basic functions of MySQL using a very simple Flatfile database (single table).

At this point don't worry too much about the details, but try to get an overall impression of how a database is created, how tables are created, how we add data to the tables, and how to view the data in table view.

Design: Planning and requirements analysis

 * When designing a database, a good place to start is to work out what we want out of it. In this case the database will be used to record the names and heights (in milli-metres) of people you know.
 * Reports required will give us a good indication of what data we need to store in the database.
 * Your contract is to design a database that will manage the heights of a list of people in an organisation.

The Logical database view
To build an electronic database we need to identify some basic structures. The logical view is how we see the database.


 * Enitities:
 * Entity instance - may be a person, place, object, event or concept (In a Relational database the entity instance as a row in the table)
 * Entity Type - the collection of entities (represented as a table) e.g. People, Cities, Timesheets, Enrolments
 * Relationships (This is how the entities link together) e.g. A Student can enrol in many courses.
 * Attributes
 * Properties of each entity (a column (field) in a table) e.g. FirstName, LastName, PhoneNumber

Height database
In this (Heights) database it is easy to identify the entities involved. In this case a single person is an entity instance, and a collection of people are described using the entity type (People). As there is only one entity you don't need to worry about any relationships !


 * Logical view (How we see the database). This is also a basic data dictionary.
 * Database: dbHeight
 * Table: ht_tblPeople
 * ID - A number that will uniquely identify each person
 * FullName - The full name of the person
 * Height - The height in mm of the person

Exercise 1: Using the command line interface to create a basic database
Enter the following commands into MySQL. After each command write down what happened.

Explain what each command does.

I have been using a Web provider that only allows me to create one database so I have had to add a prefix to all my tables to keep different database tables apart(e.g. here I may have used ht_tblPeople).

Exercise 2: Automating the database creation
When a developer is asked to create a database they expect to work with two versions of the database. The first is a development version (often called the dev version) and the second is the live version that the client will use. This means that a developer can create and test the database before it goes into production. So, if you had to type in each command at the prompt to create a database how could you be sure that the development and live versions were the same?

The solution is to create a script. Simply put a script is a file containing the commands you type next to the command prompt that you can run as many times as you like.

For a MySQL database you could create two main script files, plus an assortment of test, update and/or query files:
 * 1) One that creates the database,
 * 2) One that creates the table structures.
 * 3) Several that can add test data, update the data or query the database.

Exercise 2.1: Creating the database
For this example you will need to create a text file called Height00.sql in your MySQL working folder

Note the # allows us to place a comment line in the sql file.

Now use the MySQL SOURCE command to run the script, using the pattern SOURCE [enter your path]/Height00.sql
 * For example: If you have a file saved on H drive in a folder Mysql ...

Exercise 2.2: Creating the table
Create the following text file called Height01.sql in your MySQL working folder

Now use the MySQL SOURCE command to run the script.

Exercise 2.3: Adding some data and querying the database
Create the following text file called Height02.sql in your MySQL working folder

Now use the MySQL SOURCE command to run the script.

Note that you should only run this script once, otherwise you will add the same people to your database!!. If you want to empty the table first run Height01.sql to recreate the table.

Exercise 3: Automating Inserting data (HeightData.txt)
What happens if you want to import a lot of data from say a spreadsheet? MySQL has the capability to load data from a text file. By default the data is separated by tabs (I have shown this using [Tab] in the example.. so use the [Tab] button don't type in [Tab] !!!.

So create the following file (HeightData.txt) using notepad (Note some editors do not save the tab properly and the data is not added correctly to the table):

Now in Mysql enter..(use the correct path)