Skip to main content

Database Management System

Data refers to raw facts and figures.

  • Information refers to processed form of data.

Database is an organized collection of inter-related data.

  • Types of database:
    • Manual database
    • Electronic database

Database management system is a digital system that allows you to create, modify, process, store, and retrieve information in an organized form.

  • Examples of DBMS:

    • Ms-Access
    • MySQL
    • MongoDB etc.

  • Features of DBMS:

    • Provides security and integrity
    • Reduces redundancy
    • Organization of large volume of data can be done easily
    • Provides data sharing facility

Components of Database​

  • Table: A table is a collection of rows and columns that builds the database.
  • Record: A record, or row, consists of information about a single item in the database. A record is also called a tuple.
  • Field: A field, or column, contains information about a specific attribute for all records.

MS Access

Ms-Access is a relational database management system developed by Microsoft Corporation. (which is distributed along with the Ms Office application package)

Features of Ms-Access:​

  • Easy to create complex databases
  • Data can be analyzed and modified using queries with ease
  • Creates elaborate reports from your data
  • Makes customized data entry forms
  • Presents your data dynamically on the WWW (World Wide Web)

Major objects of Ms-Access​

  • Tables : Building block of database that holds data in form of row and column.
  • Queries : Request to DBMS to access and modify data.
  • Forms : Graphical interface to enter and also display information.
  • Reports : Presentation of information in desired format.

MS-Access Data type:​

Data TypeDescriptionMax Space
Short TextAlphanumeric data255 characters
Long TextLarge amounts of alphanumeric dataUp to 64,000 characters
Number (Byte)Numeric data1 byte
Number (Integer)Numeric data2 bytes
Number (Long Integer)Numeric data4 bytes
Number (Single)Numeric data4 bytes
Number (Double)Numeric data8 bytes
Date/TimeDates and times8 bytes
CurrencyMonetary data, stored with 4 decimal places of precision8 bytes
AutoNumberUnique value generated by Access for each new record4 bytes (default)
Yes/NoBoolean data, true/false values1 bit
OLE ObjectObjects (such as Word documents, Excel spreadsheets, pictures, etc.)Up to about 2 GB
HyperlinkLinks to other files, including web pagesUp to 2048 characters
AttachmentFiles attached to the recordsUp to 2 GB
CalculatedResults of a calculation based on other fields in the same tableDependent on result type
Lookup WizardCreates a field that allows you to choose a value from another table or listDependent on source type

Field description column can be used to help you remember the use and purpose of a particular field. (Optional)

Field Properties Pane​

Field properties pane displays list of properties associated with each field data type.

  • It has:
    • Field size
    • Caption: Alternative name given for any field.
    • Default value
    • Format: Allows displaying in different format(g. for date)
    • Input mask: Specifies pattern of data
    • Validation rule: Specifies criteria that data entered into the field must meet.
    • Validation text: The message displayed when data entered does not meet the validation rule.
    • Required
    • Indexed: Speeds up searching

Primary Key​

Primary key is the special field or group of field in the table that uniquely identifies each record from the database. Primary keys are unique and not null.

  • Importance of primary key:
    • To identify each record uniquely
    • To reduce and control duplication of records
    • To set relationships between tables (foreign key is also required for this)

In Design view, click on field you want to apply primary key to, click on primary key button from tools group in design tab.

Foreign Key​

A foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table. The foreign key establishes a link between the data in the two tables.

  • It ensures referential integrity of the data.
  • It helps maintain the consistency and accuracy of the data.
  • It allows you to create a relationship between tables, enabling complex queries and data analysis.

Views in Ms-Access​

  • Design view: Related with table structure. You can add, edit, or delete fields and their properties.
  • Datasheet view: Related with records. You can add, modify, search or delete records.

You can switch view using view dropdown button from tools group in design tab.

Query​

Query is the question or request for the database.

  • Types of query:
    • Select Query: Used to select and display relevant information.
    • Action Query: Used to make changes to or remove many records in just one operation.
      • Update Query
      • Delete Query
      • Insertion can be done using SQL statement too.

Forms​

Form is Ms-Access database objects that is primarily used to create an interface for entering data in a table or multiple linked tables.

  • Form wizard can be used from forms group in create tab.
  • The Form Wizard enables you to select the fields from a table or multiple linked tables, layout, and user interface for the form.

Report​

Report is Ms-Access database objects used to present information in an effective and organized format that is ready for printing.

  • The Report Wizard provides you with more flexibility such as you can choose the tables and fields, group the data, sort the data, summarize the data, choose a layout and orientation, apply a style, and title your report.