Database Management System (DBMS)

MS-ACCESS (DATABASE MANAGEMENT SYSTEM)

Types of software :

1.      System software (which includes)

a)      Operating system

b)      Language processors

c)      Utility programs /device drivers etc.

Examples of operating systems:

MS-DOS, WINDOWS10/11, UNIX, LINUX, ANDROID, IOs etc.

 

2.       Application software:

Examples of application software:

MS-WORD (Word processor)

MS-EXCEL ( Spreadsheet )

MS-ACCESS (RDBMS)

MS-POWERPOINT (Presentation )

 

QBASIC – High Level Programming Language

C - Middle Level Programming Language

HTML – To create hypertext documents (web pages)

Web browsers – Microsoft Edge, Mozilla Firefox, Google Chrome etc.

 

Data – Data relates to certain facts about certain event, task or transaction. Data are basically treated as raw or un processed facts.

Processing – The systematic manipulation of data to generate information is termed as processing.

Information (output) – The result of processed data is referred as information.

MS-ACCESS- Microsoft access is one of the most popular database management program used in windows environment.

Features of MS-ACCESS

a)      Storage of multiple databases.

b)      Using databases individually or in group.

c)      Creating queries and generating reports.

d)      Designing and printing sophisticated databases etc.

 

Database Management System ( DBMS )

A DBMS is a software which provides an environment that is both convenient and efficient to use in storing and retrieving database information.

A collection of data and a set of programs makes the environment for database management system.

 

Why use  DBMS ? ( Advantages)

a)    Easy to add new data.

b)    Easy to modify data.

c)     Able to delete old records.

d)    Able to arrange data in a necessary sequence.

e)    Data can be sorted, indexed and organized the way a user wants.

f)      Queries or search can be done easily in a database.

g)    Necessary data can be easily filtered and retrieved etc.

What is a database?

A database is an organized collection of meaningful and inter-related data. 

*note* the (files/database) created in MS-ACCESS will have the extension of .mdb

for example students.mdb,        employee.mdb,        report.mdb etc.

Database/ RDBMS objects

a) Table

b) Queries

c) Forms

d) Reports

        Table- A table is a collection of data about a specific topic. Table is where data is stored as a record.

             Advantage of table- Table makes a database more efficient and reduces data entry errors.

             Components of a table - A table consists of columns and rows.

             Column- Each column represents a field, a field stores only a specific category of information.

             Row – each row in a table is called record and it consists of number of related fields.

 Example of a table in a database, consisting of columns, rows, fields and records:

 

COLUMNS

   

Sno.	Name	Class
1	Arya	VII
2	Biku	VIII
3	Chunki	IX

 

 

Fields- An item or a field of a record is a unit of meaningful information about an entity or a field is a piece of meaningful information in a table of a database. ( a number of fields forms a record)

          Records- A record is a collection of fields or related data items. A record is an information        about a  particular entity in a field.  (each record should have a field to uniquely identify it)

           Queries- Queries allows to manipulate the data of a table that meets a certain criteria or            condition and displays the information on screen in a simple format.

            Uses of query: To bring data from multiple tables and sort it in a particular order.

                                       To perform calculations on group of records.

     Forms- Forms are used  mainly for data entry or to display existing data in a user friendly format. Forms are used to view, enter or update information in a database. Forms present one record at a time on the screen.

         Reports- Reports are specially formatted  collection of data organized according to your specification for summarizing and printing listings of database data. Reports are designed to be viewed and printed.

           Uses of report:

a)      Creating mailing labels

b)      Add a logo or picture

c)       Show totals in a chart

d)      Group t records into categories and calculate totals etc.

Data Type – each field of a database file should be classified into data type or field type to represent the nature of record in a field. For e.g. text, number etc. 

Types of fields (data types)

i)       Short Text

ii)     Long Text

iii)    Number

iv)   Date/time

v)     Currency

vi)   Auto number

vii)  Yes/no

viii)Ole object

ix)   Hyperlink

x)   Attachments

x)     Lookup wizard

 Short Text – (default) text or combination of text and numbers , up-to 255 characters.

 Long Text - Lengthy text or combinations of text and numbers. Up to 64,000 Characters

 Number – Used for data to be included in mathematical calculations. 1,2,4 or 8 Bytes

 Date/Time – Used for dates and time data. 8 Bytes

Currency- Used for currency values. 8 Bytes

Auto Number – A unique sequential (increment by 1) number or random number assigned by Microsoft access whenever a new record is added to a table. Auto number fields cannot be updated.

Yes/No – yes and No values and fields that contain only one of two values (Yes /No , True/False or On/Off )

OLE (Object Linking and Embedding) – Used for OLE objects (such as MSWord documents, MS-Excel spread sheets, pictures, sounds). Up to about 2 GB

Hyperlink – Used for hyperlinks. Up to 2048 Characters

Attachments -To attach any supported type of file Up to about 2 GB

Lookup wizard –Used to create a field that allows you to choose a value from another table Dependent on the data type of the lookup field 

Field properties- Field properties are the attributes assigned to a field. Field properties are set to control over the contents of a field 

 List of field properties:

i)       Field size

ii)     Format

iii)    Caption

iv)   Default value

v)     Validation rule

vi)   Validation text

vii)  Input mask

viii)Decimal places

ix)   Required

x)     Allow 0 length

xi)   Indexed

Field size –  Determines the size of the field. For text data , maximum is 255 characters and for numeric data it varies depending on field setting as byte, integer , long integer, single and double.

Format – It allows you to display data in a different format than the way you stored in the database.

Input Mask – input mask allows to have control over data entry by defining validation for each character that is entered in the field. You can use input mask wizard for additional information or you can even create input masks as required.

Caption – caption field is used when you want to display an alternate name for the field to make the field name more explanatory. It can contain up-to 2048 characters.

Default value – default value is the one that is displayed automatically for the field when you add a new record to the table. 

Validation Rule – The data validation rule enables to limit values that can be accepted into a field. Validation rule can be automatic or you can define a method for this purpose.

Validation Text- Validation Text is the error message that appears if the data entered is invalid according to the specified validation rule.

Required – you can enter a Yes value for required if field should always receive a value during data entry time.

Indexed – Indexed property is used to set an index on a field. It speeds up searching and sorting of records based on a field.

Starting Microsoft Access

When we start MS-ACCESS, Access will provide windows with three options.

1)      Blank Access Database – to create a new database format.

2)      Access Database Wizard Page and Project - to create a database from wizard.

3)      Open an existing file – to open the existing file from any storage device.

 

Creating a new database:

When creating a new database, the database dialogue box will display three options for creating tables:

Create table in design view: 

     Create table using wizard: 

     Create table by entering data:

      Entering and Editing Data 

    Table Datasheet and its Formatting 

     Table datasheet is simply the display of records in row and column format. Using the datasheet view, you can add, modify, search or delete records. There are mainly two views of Table. 

a) Design View – Related with table structure. You can add, edit or delete field and its properties. 

b) Datasheet View – Related with records. You can add, modify, search or delete records   

Primary key- A primary key is a special field or group of fields that contains unique data for each record. It identifies the record in a database. The primary key does not accept duplicate value for a field and it does not allow a user to leave the field blank or null. 

Uses/Importance of Primary Key 

 To identify each record of a table uniquely. 

 To reduce and control duplication of the record in a table. 

 To set the relationship between tables.

 (When a primary key is set, the filed will get a small key symbol)

Field	Data type
Roll no.	Number
Name	Text

 Γ

 

Table wizard – table wizard is a facility that provides many pre-defined sample table formats.

Closing the database- After working with the database in order to retain changes , a database should be saved and closed.

Opening an existing database – The process of retrieving the saved database is referred as opening a database.

Adding a field – The process of creating a new field to accommodate new data or records in a database. (Fields can be added column or row wise) 

Deleting a field – The process of removing unwanted field from a database.

Adjusting column width (size) and row height – It is a process of increasing or decreasing the column width (size or row height to accommodate data in the cell.

Cell – the intersection of column and rows is called a cell.

Editing  the table structure – The process of changing the field name, data type, field size etc.

Hiding a field – If a table is too long, all fields may not be seen on the screen, one can temporarily hide one or more fields of a table to work with the table in a convenient manner.

Importance of hiding a field:

a)      To view only those records upon which a user wants to work.

b)      To take the printouts of only certain fields.

Sort – The process of arranging records of a field in a database is called sorting. Sorting can be done in two ways:  a) Ascending (a-z)                            b) Descending (z-a) 

Filter – Filter means to find records based on whether they contain a specific value in a particular field. Filtering data allows to select and restrict to display the records based on criteria that you specify.

Filters can be applied in four ways:

a)      Filter by form

b)      Filter by selection/ filter excluding selection

c)      Filter for input

d)      Advance filter / sort

Filter by form – This technique allows you to choose a type of value that you want the filter records to contain in empty fields.

Filter by selection – This technique retrieves only records that contain the selected value in a data sheet or a form.

Filter for input – filter for input is used for filtering records that uses a value or expressions that you enter to find only records that contain the value or satisfy the expression.

Advanced filter – advanced filter allows you to apply more than one criteria or condition on multiple fields.

Arithmetic, Relational and logical functions used in MS-ACCESS

Arithmetic operators: + addition, - subtraction, * multiplication, / division, ^ exponential, % percentage.

Relational operators: = equal to, > greater than, < less than, >= greater than equal to, <= less than equal to, <> not equal, & text concatenation.

Logical operators:

AND – returns TRUE if all its arguments are TRUE, returns FALSE if one or more arguments are FALSE.

OR – Returns TRUE if any argument is TRUE, returns FALSE if all arguments are FALSE.

NOT – Reverses the value of its argument. Use NOT when you want to make sure a value is not equal to one particular value.

 

Examples of DBMS/RDBMS

a)      Dbase III+

b)      Dbase IV

c)      Sybase

d)      Foxprow

e)      Oracle etc.

m

 

 

What is a key field?

A common field on which two tables are linked is known as key field.

What do you mean by foreign key?

The common field in child table that maintains relation with master table is foreign key.

What are the elements/objects  of a database?

The major six elements of a database are  Tables, Queries, Form, Report  Macros, Modules

What is a field?

A field in a database is a piece of information about a subject. Each field is arranged as a column in table.

What is a record?

A record is complete information about a subject. A record is a collection of fields and presented as a row in a table of database. .Table ,Row ,Record Field,Database

Tuple – The record row of a table is called a tuple.

Types of queries :

Select query – A select query is the most common type of query, it retrieves selected data from one or more tables and displays the result in a data sheet where data can be edited.

Parameter query – A parameter query is such a query that allows to enter criteria for retrieving records during execution of query and displays the result on the basis of the condition.

Crosstab query – a crosstab query is used to calculate and restructure data for easier analysis of the data.

Action query- An action query makes changes to many records in just one operation. Types of action queries are :

Delete query- A delete query deletes a group of re3cords from one or more tables.

Update query – An update query makes global changes in data of a table or more linked tables.

Append query – An append query adds a group of records from one or more tables to the end of one or more tables.

Make table query – A make table query creates a new table from all or part of the data in one or more tables.

SQL queries – An SQL (Structured Query language) is a query created by using SQL statement, to query, update and manage databases.

What is a crosstab query wizard?

A wizard that helps us to present the data in a 2-dimensional tabular format is the crosstab query wizard. You can use this to create queries that will depict the data in particular fashion, i.e. item wise sales made or customer wise sales made etc.

What are the differences between a form and a report?

Forms are primarily used to edit overview data whereas reports are used primarily to print or view data.

In a form your usually navigate from one record to another, whereas in reports summarized data are possible to present.

What is a macro?

A macro is an object in MS-Access that is used to execute one or more database commands automatically. Macros are useful in tasks such as printing month-end reports, adding new record to a table, printing letters to customers periodically.

What is a module?

A module object in Access is a program written using VBA (Visual Basic for Application) to automate and customize database function.

What are link tables?

Links of the tables in other databases or files created in Access database so that the data can be viewed and edited in Microsoft Access in addition to the original software

What is datasheet view?

Datasheet view is a window that displays data from a table or query in row and column format where you can edit fields, add, delete data as well as search for data.

What is the use of Validation Rule property of a field?

Validation rule property allows specifying what validation must be made for the data entered in the field. if anything is specified in the validation rule property, only the data that satisfies the condition specified can be entered, all other data is rejected.

What is 'Input Mask' field property?

Input mask specifies the format in which the data must be entered. For example if phone number must be displayed as (977) 428-0628 enter the input mask property as !(999) 000-0000;;_

What is the use of Validation Text property of a field?

When the data entered in a field does not confirm to the validation rule that is specified, an error message is displayed on the screen that informs the user what is wrong. Validation text is used to display custom error message instead of default error message.

What is Dynaset?

A dynaset is a subset of the data stored in database that is displayed by the query

What is the difference between using a filter and a query to find records?

We can use a Filter to quickly limit the records we are already viewing in a Datasheet or a form to those that match the criteria that we specify. In contrast to this, we can use a Query to view selected fields and records from one or more tables, then save the query to open it later or use it as the basis of a form or a report

What are the different sections of a report?

A report is divided into many sections: The Report header: The Page header: The detail band The Page footer The report footer:

What is a macro?

A macro is a set of instructions, which can be executed repeatedly. it is useful for automating certain routine tasks like printing reports etc. The macro once created can be executed from buttons or any other controls.

What is the difference between Print Preview and Layout Preview?

A report may be seen in two ways: Print Preview and Layout Preview. When a report is viewed in Print preview, it appears almost exactly as it would as when printed. MS-Access does the entire necessary math for calculated and summary fields. In contrast if you view the report in layout view, Access does not do all the calculations. Hence this is a quick way to check your work as you design the repot.

What are command buttons? How to use command buttons?

Command buttons are referred to as controls. The control is capable of executing a set of instruction when the user clicks on it. In order to create and use command buttons select View > Toolbox. In the ToolBox select the command button and place it on the form by clicking and dragging. The command button wizard is started which allows you to specify the event.

Define entity:

An entity defines any person such as student, teacher; place such as classroom, building; thing such as computer, lab equipment; concept such as course, attendance for which data will be collected.

What are the different ways to add a table in Access database?

Three are five different ways you can add a table in Access database. They are: Datasheet View, Design View, Table Wizard, Import Table, and Link Table.

Datasheet View: In datasheet view you can enter the field names and records as in Excel sheet. Access defines correct field types and sizes based upon the data you enter for the field. A table is added when you save the datasheet.

Design View: In design view you will specify the field name, data type and various required options for the field. A table is added when you save the design.

Table Wizard: Table wizard provides you sample tables and sample fields from which you can select the required fields. After completing the wizard a table is added in database.

What are the ways to create a new form?

You can create a new form in any of the following three ways:

Using AutoForm: You can create a form

based on a single table or query quickly

using Autoform.

Using Form Wizard: You can create a form

based on multiple talbes or queries using

Form Wizard.

Design View: You can create a custom

form on your own in Design View

What are the different options that you can use to filter records?

The various options available to filter records are:

Filter by Form

Filter by Selection

Filter for Input

Advanced Filter/Sort

 Questions:

1.     Define data

2.     What is processing

3.     Define information

4.     What is a database? Give an example of database

5.     What is DBMS /RDBMS

6.     Write four advantages of DBMS 

7.     Give five examples of DBMS software

8.     What is MS-Access

9.     Mention four features of MS-Access

10.                        List the objects of database

11.                        Define with example a table.

12.                        Mention the advantage of a table

13.                        What is a column in a table

14.                        What is a row in a table

15.                        Define with example a field

16.                        Define with example a record

17.                        Define query

18.                        In how many ways queries can be created

19.                        Mention the uses of query

20.                        What are the types of queries

21.                        Define select query

22.                        Define parameter query

23.                        Define cross tab query

24.                        What is action query mention its types

25.                        Define SQL (select or structured query language)

26.                        Mention two differences between filter and query

27.                        Write one similarity between filter and query

28.                        What is delete query

29.                        What is update query

30.                        What is append query

31.                        What is a make table query

32.                        Define a form

33.                        What are the uses of a form

34.                        In how many ways forms can be created

35.                        What is a report

36.                        Mention the uses of a report

37.                        In how many ways report can be created

38.                        What are different sections of a report? Can you update data of a table                 using a report?

39.                        What is data structure or database design

40.                        What is design view

41.                        What is datasheet view

42.                        Define with examples data types

43.                        List the data types used in MS-ACCESS

44.                        Define text

45.                        Define memo

46.                        Define number

47.                        Define date and time

48.                        Define currency  

49.                        Define auto number

50.                        Define OLE object

51.                        Define hyperlink

52.                        Define lookup wizard

53.                        What are field properties

54.                        List field properties used in access

55.                        Define field size

56.                        Define format

57.                        Define option

58.                        Define default value

59.                        Define validation rule

60.                        Define validation text

61.                        Define input mask

62.                        Define decimal places

63.                        Define required

64.                        Define zero length

65.                        Define indexed

66.                        In how many ways can a table be created

67.                        Why do we need to save our database

68.                        What is table wizard

69.                        Why is it important to close the database

70.                        What do you understand by adjusting column width and row height

71.                        Why is it necessary to hide a field

72.                        Mention two importance of hiding a field

73.                        What is sorting in how many ways can sorting be done

74.                        What do you understand by filtering records

75.                        Mention the advantages of filtering data.

76.                        In how many ways can filter be applied

77.                        Define filter by selection

78.                        Define filter by form

79.                        Define filter  for input

80.                        Define advanced filter

81.                        Define with example a primary key

82.                        Mention the importance of primary key in a table

83.                        What is foreign key mention its uses

84.                        Why is the fieldname and data type that is added to a table if you let MS-                Access to create a primary key itself

85.                        What do you understand by formatting a table

86.                        Show the difference between sorting and filtering

87.                        For what purpose the field property i.e. caption is used in MS-Access

88.                        Differentiate between field and record

89.                        Write the method of creating report

90.                        Write the difference between select and update query

91.                        Differentiate between text and memo

92.                        For what purpose field size is used

93.                        Name the type of data for defining the field SALARY and DATE OF                             JOINING-

94.                        Define tuple with example

95.                        Mention any two methods to create forms

96.                        List the method of creating query

  97.                       While designing table structure which data types are suitable to store                       information about students name, date of birth address and phone no.

  98.                       Give an example of database and list any four DBMS packages.