We talked about what is database and why do people use it ? at this post. Now, we will create a sample database by using Microsoft Access 2007. The reason behind choosing MS Access is it provides to create a database without any command so, it is a good way to learn basics of database for beginners. Okay, let’s begin..
When open up MS Access, you can see Blank Database at the top. Click it, give a name and choose a path to your database on the right side then, click create button. Now, your database created and a table opened up automatically. Before continue, let’s see some basic terms.
A table is consist of columns and rows. A column represent a one feature of a table. Columns are also called attributes. A row represent a one instance or we can say a member of a table. For example;
(It is datasheet view of the table)
employees is a table.
emp_no, name, surname, gender and age are attributes of the table.
And each row represent just one members’ informations. Therefore, rows are also called records.
Now, we can continue to design our first table. A table already exist when we create the database. Let’s add some attributes (columns) into the table. Click to Design View under View tab on the toolbar. MS Access ask you to save your table. Give your table a name then click Ok button. Now you see two columns named Field Name and Data Type. Here we can add new columns with its data type we want to use. There is one thing important. You can see an existing column has field name is ID and the data type is AutoNumber and there is key on the left side of it. It means that column is primary key but what is primary key ? Let’s see..
Primary Key is a column which uniquely identfy each row. It have to be unique and not-null. As seen at the picture above, emp_no is the primary key of the table. Each employee have a unique number. Primary Key provides each row to be unique. People use these primary keys in a day even they don’t realize that. For example, students have student id which is the different number for each student and they can reach all their informations with the student id on student information system. So, let’s back again our database, we assign a unique number each employee with using emp_no as primary key. When we call emp_no 14, we reach the employee Jeffrey Bates, Male, 32 years old.
After create columns, now we can enter the data into our table. Click to Datasheet View under the View tab to enter data into the table. Now we can see the columns we have created. Enter the related data under each column. There is no need to enter a value into primary key column because its datatype chosen as AutoNumber that means it is automatically increasing for each row. Our first table is ready after all. Click Table under Create menu on the toolbar to create a new table if you need more than one table. Create the other tables as the same way. Then let’s see how to create relationships between tables..
After create all the tables, click Relationships under Database Tools menu on the toolbar. All the created tables will shown up a box. Choose the tables you want to create relationship between and click Add button. The tables will shown up like this;
but there is no relationship between them. Before begin to create relationships, let’s see some relationship types briefly.
On the picture above, one-to-one relationship seen between employees and salaries tables. It means, each employee have just one salary and each salary belongs to just one employee. An employee do not take more than one different salaries, and a salary do not given more than one employee.
There is many-to-many relationship between employees and department but why there is not a relationship directly between employees and departments ? Because Microsoft Access do not permit to create many-to-many relationship directly. So, how we can create many-to-many relationship ?
As seen on the picture, there is one more table as emp_dept to connect employees and departments tables to each other. There is one-to-many relationship between employees and emp_dept and many-to-one relationship between emp_dept and departments tables. So, many-to-many relationship is created between employees and departments by this way.
Now, let’s continue to create relationship. Drag a column of a table to another tables’ column which you want to create relationship. Then, a box will open again. Make preferences and click create. The relationship between two tables will shown up. Create relationships between other tables by this way and that’s all.