Database Design and Implementation Tutorial

By Sergey Skudaev

Introduction

In this tutorial you will learn how to design data model and implement it in MS access database. MS Access tutorial step by step explain you how to create database tables using create table script and manually. You will learn how to write sql queries to insert data into database, how to search and display data etc.

Part  I  Data Model and Database

The data in a relational database is organized in the tables. Every table has a column or field whose values uniquely identify each row. This field is called the Primary Key. A field in one table whose value matches the primary key in some other table is called a foreign key. A table may contain more than one foreign key. Together, a primary key and foreign key create a parent / child relationship between the tables. There are three types of relationships:

One to one

One to many

Many to many

Managers table :
managerid
fisrtname
lastname

Employees table:
employeeid
managerid
firstname
lastname

Managers table related to employees table as one to many. One manager may have many employees. Manager id is a foreign key in the employees table. It is very important to understand in which table to place foreign key. If you place employeeid in managers table your data model implementation will be wrong because one employee will be able to have many managers, but one manager will be able to have only one employee. Let us take a look at a database that has patient table and tests table. One patient may take many different tests, one test may be taken by many different patients. The relationship is many to many. To implement many to many relationships we have to create the third table test_results that has patientid and testid as foreign key fields. The result of the test may be unique for each test and each patient that is why it is a good idea place test result not in test table, but in table that contains patientid and testid. The same is true for test date.

To better understand how to design a database and implement it, let us develop a Credit Card Management application in MS ACCESS

 >> PART II

My eBooks on Amazon.com