Computer Programming web Web programming Tips



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.

designing database tables

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

 >> PART II




Our dog needs urgent surgery, and the cost is overwhelming.

Any help, big or small, would mean the world to us. Thank you for supporting Oscar on his journey to recovery!



Oscar Story.

Oscar wasn’t just any puppy—he was a gift from a mother who trusted us with her smallest one.

For five years, my wife worked at the Indian Medical Center in Arizona, deep in Navajo Nation. Near her clinic, she often saw a homeless dog wandering the area. Over time, she began feeding her, and the dog grew fond of her. Then, one day, that same dog brought her newborn puppies to my wife—as if proudly showing them off.

Among them was the smallest, most delicate pup. My wife couldn’t resist. She brought him home, and we named him Oscar.

Oscar thrived in the house provided by the medical center, enjoying the big backyard where he lived. I built him a sturdy wooden doghouse, and we often took him on walks along the Window Rock Trail. He became our adventure companion, making the vast desert feel like home.

After my wife’s contract ended, we moved back to Florida, bringing Oscar with us. He adjusted to his new surroundings, but he never lost his adventurous spirit.

Now, Oscar faces a tough challenge—he needs urgent surgery, and the cost is overwhelming. We want to give him the best care possible, just as he’s given us years of joy and loyalty.

Any help, big or small, would mean the world to us. Thank you for supporting Oscar on his journey to recovery!



My  eBooks  on  Amazon

US    UK    BR    CA
US    UK    BR    CA
US    UK    BR    CA
US   UK   BR   CA