This article explains the concept of Database and MS-Access intoduction.
Highlights: 1. What is Database? 2. What is DBMS? 3. MS-Access Introduction 4. Creating Database in MS-Access 5. Creating tables in MS-Access using design view 6. Adding data in Database 7. Retrieving (Selecting) data from Database 8. Primary key and Foreign key 9. Updating records in MS-Access
1. What is Database?
A databaseis an organized collection of information or data stored electronically in a computer system. Using database, we can easily store, retrieve and manage large amount of data. Data is stored in the form of tables in a database. Table consists of rows (records) and columns (fields). SQL or Structured Query Language is used to operate on the data stored in a database.
2. What is DBMS?
Database Management System (DBMS) is software designed to store, retrieve, define, and manage data in a database. It allows users to create their databases as per their requirement. It also provides the interface to perform the various operations on databases like table creation, table deletion, updation etc. Some of the examples of DBMS are MS-Access, MySQL, Oracle etc.
3. MS-Access Introduction
Microsoft Access is a Database Management System (DBMS) software from Microsoft that allows you to store , retrieve and manage data.
To use MS Access, you will need to follow these four steps −
Database Creation − Create your MS-Access database and specify what kind of data you will be storing.
Data Input − After your database is created, enter your data into the database.
Query − This is a fancy term to basically describe the process of retrieving data from the database.
4. Creating Database in MS-Access
Step 1: Open MS-Access in your computer Step 2: Select “Blank Database” from the given templates and give the name of Database as shown below.
Step 3: Now, click on “Create” to create a new database. Following screen will get opened with a new table as Table1. You can create more than 1 table in a DB.
5. Creating tables in MS-Access using design view
Follow below steps to create a table in DB: Step 1: Go to “Create” tab in MS-Access. Step 2: Click on “Table Design” in “Tables” group.
Step 3: Following screen will get opened. You have to enter the column (field) names and specify the data type of each column. Data type may be text, number, date/time etc.
Step 4: Right click on “Roll number” field and make it as Primary key which we will explain in next section. Step 5: Finally save your table by pressing Ctrl+S and give the name of table as follows:
6. Adding data in Database
Now, you can add data in the created table as follows: Step 1: Click on View -> Datasheet view in “Home” tab.
Step 2: All the columns which you created will appear on the screen. Enter the data in the space provided below columns.
7. Retrieving (Selecting) data from Database
We have added 3 rows in the above table. Suppose we want to retrieve (select) the names of students of class 9 only, for this we have to run a Select query with below steps: Step 1: Go to “Create” tab. Step 2: Select “Query Design” in “Designs” group as shown below:
Step 3: Following screen will get opened. Add the table on which you want to run the query.
Step 4: In the bottom, select the columns which you want to display and in “Criteria” option, write “=9” under Student class column because we want to retrieve only class 9 students. Then, click on “Run” to run this query.
Step 5: Following table will get opened with students of only class 9.
8. Primary key and Foreign key
Primary key is a column (field) of a table which uniquely identifies each row. Primary key must contain UNIQUE values, and cannot contain NULL values. A table can have only one column as primary key and all other columns are called as Secondary keys. For example, in the Students database, we can create “Roll number” column as Primary key because each student has a unique roll number.
A FOREIGN KEY is a key used to link two tables together. A FOREIGN KEY is a field in one table that refers to the PRIMARY KEY in another table. The table containing the foreign key is called the child table, and the table containing the Primary key is called the parent table.
“Students” table:
Roll no
LastName
FirstName
Class
1
Hansen
Ola
9
2
Svendson
Tove
9
3
Pettersen
Kari
9
“Address” table:
Roll no
Address
1
Gurugram
2
Hisar
3
Gurugram
Notice that the “Roll no” column in the “Address” table points to the “Roll no” column in the “Students” table.
The “Roll no” column in the “Students” table is the PRIMARY KEY in the “Students” table.
The “Roll no” column in the “Address” table is a FOREIGN KEY in the “Address” table.
The FOREIGN KEY is used to prevent actions that would destroy links between tables.
The FOREIGN KEY also prevents invalid data from being inserted into the foreign key column, because Foreign key column can only contain values which are present in Primary key column of another table. In the above example, the Roll no values in the “Address” table can only be 1, 2 or 3. If we try to add some other value, then it will show the error that Foreign key can contain only values present in Primary key of “Students” table.
9. Updating records in MS-Access
To update some records (rows) of the table, you have to run an “Update” query as below: Step 1: Go to “Create” tab. Step 2: Select “Query Design” in “Designs” group as shown below:
Step 3: Following screen will get opened. Add the table on which you want to run the update query.
Step 4: Click on “Update” in “Query Type” group in “Design” tab. Add, all the columns of table and suppose we want to update students of class 9 to class 10. For this, write “10” in “Update to” option and “=9” to “Criteria” option under the Student class column. Finally click on “Run” to run this query.
Step 5: Now, if we see Student data table, class 9 of all the students will be updated to class 10.
