WHAT IS DATABASE?

Spread the love

1.     WHAT IS DATABASE?

A database is an organized collection of data, generally stored and accessed electronically from a computer system. Where databases are more complex they are often developed using formal design and modeling techniques.
1B.  EXPLAIN WHAT YOU UNDERSTAND BY TABLE, QUERY, FORM AND REPORTS IN MS ACCESS
Tables
To store your data, you create one table for each type of information that you track. Types of information might include customer information, products, and order details. To bring the data from multiple tables together in a query, form, or report, you define relationships between the tables.

Queries

A query can help you find and retrieve the data that meets conditions that you specify — including data from multiple tables. You can also use a query to update or delete multiple records at the same time and to perform predefined or custom calculations on your data.

Forms

You can use a form to easily view, enter, and change data one row at a time. You can also use a form to perform other actions, such as sending data to another application. Forms typically contain controls that are linked to underlying fields in tables. When you open a form, Access retrieves the data from one or more of those tables, and then displays the data in the layout that you chose when you created the form. You can create a form by using one of the Form commands on the Ribbon, the Form Wizard, or create a form yourself in Design view.

Reports

You can use a report to quickly analyze your data or to present it a certain way in print or in other formats. For example, you may send a colleague a report that groups data and calculates totals. Or, you may create a report with address data formatted for printing mailing labels.


2.    OUTLINE THE PROCEDURE FOR CREATING DATABASE IN MS ACCESS
Start Access.

1.     Organize your data into structured tables.

If you intend using Access database, chances are that you already have a fair idea about the table structures you want to create. Think of this as an important preparatory step as it is the structure of your tables that decides the success of your Access application.
Some questions that will help you get this right include:
  • What kind of data do you have? Business records, Personal files, data that supports your Cultural society? Ideally, each type will contain its own database.
  • What is common among the data you have? Can you group them into separate tables based on this common factor? Eg: Income, Expenses, Address List, etc.
  • What is unique across each table? What can you use to identify one row of data from each table? Eg: Name, Id, etc. This will be the primary key and can be one or more columns. (Don’t worry if you don’t find something unique, as Access can auto-generate a primary key.)
  • What are the connecting links across tables? These are potential foreign keys (you will learn more on this below).
We suggest you have a rough draft of your table structure on paper. Don’t worry about perfection; your tables will evolve as you work with Access and gain mastery over it. Once you’re ready, launch Microsoft Access, choose File -> New -> “Blank Database” and click on the “Create” key to kick-start your first Microsoft Access database!
Microsoft Access
Hint: Choose an appropriate name that reflects the type of your database; this sounds way better than simply calling it, “My first database”! 

2.     Create the Tables

Once you successfully launch and create your first database, Access opens up the “Table Tools” View to create tables, because really, Access is all about tables!
Here, we will create a simple budget application with 3 tables: Regular Expenses, Monthly Income and Monthly Expenses. The columns highlighted in blue are unique, and can hence be used as the primary key. (Do note that where more than one column is highlighted in blue, the combination together is unique.)
To add the Regular Expenses table, use the right-hand side tab.
  • The first column (ID) is auto-generated by Access and serves as the default primary key. (We can change this later.)
  • Click on “Click to Add” to enter the next Column name. You will be given a (drop-down) choice for type of column. Choose Text, and enter “Expense Name”.
  • Again Click to Add next column, choose Text and enter “Type”.
  • Again Click to Add next column, choose Number and enter “Amount”.
  • Finally, save your newly created table (using Control Key + “S” keys). When prompted for a name, type “Regular Expenses”.
And there, freshly created, is your very own first Microsoft Access table. Hurray!
From the CREATE tab, use Create -> Table and follow the above steps to similarly create tables “Monthly Income” and “Monthly Expenses”.

3.     Enter data into tables

In this step, we will manually enter data into the tables. But do note that Access offers several other effortless ways to import data into tables (from an Excel sheet, from a text file, etc.).
Here, we must mention that Access wins over Excel in data validation if the tables are rightly designed. To understand the relevance of table design, we will simply add data to the “Regular Expenses” table and explore the challenges.
Here is the data we intend to add into our tables:
The “Datasheet View” is best used to add/delete/alter table data, and opens up any time you double click on a table’s name in the left-side tab. You can also access it using DESIGN Tab -> View -> Design View. Once you’re in this view, choose the table you will work with (“Regular Expenses”) in the left-side tab. On the right-side tab,
  • Click on the second column (Expense Name) and type “Rent”.
  • Click on the third column (Type) and type “Fixed”.
  • Click on the fourth column (Amount) and type “2000”.
  • You have completed the first row. Repeat for remaining rows.
  • Repeat the same for “Income” and “Monthly Expenses” table.
Did you notice how Access automatically sorts the data in increasing order of amount? If you want to change this, you can right-click on the Amount field and change the sort order.
As you are sure to have observed, Access automatically generates a unique number for the first column (ID), as this treats this as the default primary key. This is the column that is typically used to retrieve unique rows of data from the table.
But this value may be difficult for us to remember as it has no relevance to our data. Also, we have already identified our unique primary keys and should capture this in the table design. The next step will show you how to re-design the table to take care of this.

4.     (Re)Design your Tables

Once you’ve created your tables, we bet you’re curious to see if they turned out okay. In fact, it’s important to do this so you can include any additional rules for data validation and accuracy. You can verify this in the “Design View”.
The more flexible Design View of Access is easily accessed using the DESIGN Tab -> View -> Design View. (Alternately, you can click on the table name in the left-side tab, and choose “Design View”.)
If you explore the “Regular Expenses” table (screenshot above), you’re sure to catch some inconsistencies. Let’s fix this as follows:
  • By default, the auto-generated ID column is set as the primary key, and hence the small key icon to its left. We change this by highlighting (single click) the Expense Name column, and choosing the “Primary” Key icon on the top tab. (You can also right-click on the column name and choose “Primary Key”). The key icon will change accordingly.
  • We no longer need the ID column. So right click on it and choose “Delete Rows”. This will automatically delete this column (and all previously inserted values for it) from this table.
  • The Amount column is set to data type Number. Change this to Currency.
Now our table looks good. You can similarly edit the other 2 tables in Design view, to alter their primary key, set currency data type and add additional data into all the tables so we can play with it in successive steps.

5.     Define Table Relationships

Any time you want to simply “view” your table data, the Datasheet View can be used. But we reckon you’d like to view the combined results of all your tables. This can be cumbersome to do in Excel but is effortless in Access. The key is in defining table relationships.
Relationships define how our tables are “connected” to each other. These connecting links are called “foreign keys” in Database jargon.
To define table relationships in Access, go to DATABASE TOOLS Tab and click on Relationships. You will see a dialog box that lists all tables. Double click on each table until they are seen in the background relationships view. If a table appears more than once, you can right-click on the extra table and select “Hide Table”.
Once all 3 tables are visible, you can move them around the screen as you please.
But right now, there is nothing special about this view. Our tables are simply listed, disconnected. To change this, click on REGULAR EXPENSES.Expense Name field, and drag it towards MONTHLY EXPENSES.Expense Name field.

Leave a Reply

%d bloggers like this: