Objectives of the unit:
You will:
Understand how to set up a database by using fields, data types and validation rules.
Know how to import data into a database.
Be able to create a database from scratch by following a list of instructions.
Able to create queries.
Database Terms - and their Meanings
Before learning the language you will have to learn some words and their meanings. Basic stuff, but you cannot continue without them. Feel free to skip any word if you already know the meaning.
Database
A database is a collection of data that is organized in a specific way. The data is stored in a way that makes it easy to access using queries. For example, a database called 'Simpsons'.
Table
A database can have multiple tables in it. All the data is stored in the tables. A table has a set of fields in it. This field makes the design or the structure of the database.
The database 'Simpsons' has two tables - called 'Character' and 'Quote'
Field
Each table has a set of fields.
Our example table 'Character' has four fields - id, name, iq and sex.
Data Type
Each field can have only one kind of data. That is called the Data Type of that field. This can be a string, an integer, a date, etc.
When creating a table, you have to specify the kind of data that can be stored in that field. One field can be used to store a number, another for a string. Since every table has a number of fields, different types of data can be stored in a table.
The data type in our example table 'Character' are...
id - integer
name - string
iq - integer
sex - string(actually it is enum - 'm' or 'f' - more on this later)
Row
All the terms till now dealt with the structure - row refers to the data. It can also called a record. The collection of a set of values for all fields in one table is together called a row. Yeah, even I cannot understand that definition. But an example will make things clearer - the first row of the Character table has these values...
All this together is called a row.
Primary Key
Primary key is a field that will uniquely identify each row in the table. The value of this field will be unique through the table. In our example table, 'id' is the primary key. This will be 1 for the first row, 2 for the second and so on.
Relation
There will be many cases when the data in one table can be related to the data in another table. This connection between two tables is called a relation.
Foreign Key
When there is a relation between 2 tables, these tables will be connected by inserting the primary key of one table into the corresponding row of the other table. The field used in such a way to connect the 2 tables is called the foreign key.
For example, the 'Quote' table has 3 fields’ id, character_id, and quote. The character_id field will have the id of the character who spoke the quote - as shown here..