Database Management System

Form

A form is a window or screen of a database application that provides an easy and convenient way for users to enter, view, edit, or delete data in a table. It is designed to display one record at a time in a user-friendly layout, so the user does not have to work directly with the raw table.

Two uses of a Form:

1. Data entry and modification – It is used to enter new records into a table and to edit or update existing records easily.
2. Displaying and searching records – It is used to view records one at a time and to search for a particular record quickly without scrolling through the whole table.

Primary Key: A primary key is a field (or a combination of fields) in a database table that uniquely identifies each record in that table. It cannot contain duplicate values and cannot be left empty (NULL).

Two benefits of a primary key:

1. Uniqueness of records — It ensures that no two rows in the table have the same value in the key field, so every record can be identified distinctly.
2. Fast searching and no duplication — Because the primary key is unique and indexed, records can be searched and retrieved quickly, and duplicate data cannot be entered into the table.

Database: A database is an organised collection of related data stored in a structured way so that it can be easily accessed, managed and updated. For example, a school database stores data about students, teachers and marks.

DBMS: A Database Management System is a software package used to create, store, manage, retrieve and control a database. It acts as an interface between the user and the database. Examples: MS Access, MySQL, Oracle.

Fields and Records:

• Field: A single column of a table that stores one particular type of information about every item; it is the smallest unit of a database. In a student table, Roll_No, Name, Class and Marks are fields.
• Record: A single row of a table holding the complete set of related information about one item or person, i.e. a collection of all the fields for one entry. The row 1, Ramesh, 10, 450 is one record.

Here Roll_No, Name, Class and Marks are the fields (columns), and each row is a record.

64 characters

In MS-Access a field name can be up to 64 characters long. It may contain letters, numbers, spaces and most special characters, but not a period (.), exclamation mark (!), grave accent (`) or square brackets ([ ]).

OLE Object

In MS-Access the OLE Object data type is used to store pictures, images and other binary objects. (The Attachment data type can also hold images.)

Data redundancy is the unnecessary duplication of the same data in multiple places within a database. It wastes storage space and creates inconsistency, because if one copy is updated and another is not, the records no longer match.

It can be reduced by normalization — organizing tables so that each piece of data is stored in only one place. Splitting a large table into smaller related tables and linking them with a primary key–foreign key relationship removes repeated fields. Using a relational DBMS with properly defined keys also prevents the same record from being entered again.

Two methods of creating a table in MS-Access are:

1. Design View: the user defines field names, data types and properties manually.
2. Table Wizard / Datasheet View: the user picks ready-made sample fields, or types data directly into a blank datasheet.

A table can also be created by importing data from an external source.

64 characters

In MS-Access a field name can be up to 64 characters long. It may include letters, numbers, spaces and most special characters, except the period (.), exclamation mark (!), grave accent (`) and square brackets ([ ]).

A database is an organised collection of related data stored in a structured way so that it can be easily accessed, managed and updated. For example, a school database stores records of students, teachers and marks.

Two data types used in MS-Access:

1. Text: stores alphanumeric characters such as names and addresses.
2. Number: stores numeric values such as age or marks.

Field vs Record

Example: In a student table with fields Roll_No, Name, Address, the values 1, Sita, Pokhara together form one record.

Report: A report is a formatted and organized presentation of data retrieved from one or more tables of a database, designed to be displayed on screen or printed on paper for the user.

Why it is necessary to create a report in DBMS:

1. Presentable output – It arranges data in a neat, readable layout (headings, columns, totals) so that information can be easily understood.
2. Decision making – It summarizes and groups data (e.g., monthly sales, student marks) which helps managers take decisions.
3. Printing and record keeping – Reports can be printed and kept as permanent hard-copy records such as bills, mark sheets and salary slips.
4. Sharing information – Only the required fields are shown, so specific information can be supplied to the concerned person without exposing the whole database.

Query

A query is a request made to a database to retrieve, display, or manipulate data stored in tables. It is used to ask a question about the data and get the required result. In MS Access, queries are created using the Query Design window.

Types of Action query

An Action query performs an operation on the records of a table rather than just displaying them. Its types are:

1. Update query – makes changes (updates) to the data in existing records.
2. Append query – adds (appends) new records from one table to the end of another table.
3. Delete query – deletes records from one or more tables.
4. Make-table query – creates a new table from the selected records of an existing table.

Select Query vs Action Query

In short, a Select Query only shows data, while an Action Query changes data.

Data is a collection of raw facts, figures or symbols that has no meaning by itself. Example: 45, 78, 90 or the names of students.

Information is processed, organized and meaningful data that is useful for decision making. Example: "The average marks of the class is 71" or a result sheet prepared from the marks.

Validation rule: A validation rule is a field property in MS Access that sets a condition which the data entered in the field must satisfy. For example, the rule >=0 And <=100 for a marks field allows only values from 0 to 100.

Validation text: Validation text is the error message displayed when the entered data breaks the validation rule. For example, "Marks must be between 0 and 100."

Query is the object of MS-Access used to retrieve data from a table.

A primary key is a field (or a combination of fields) in a database table that uniquely identifies each record in that table. It cannot contain duplicate or NULL values.

DBMS (Database Management System) is a software that is used to create, store, manage, retrieve and manipulate data in a database in an organized way. It acts as an interface between the user and the database, and provides facilities like data security, backup, and controlled access.

Two examples:

1. MySQL
2. Oracle

Four data types used in MS-Access:

1. Text (Short Text) – Stores letters, numbers and symbols that are not used in calculations. Example: Name = "Ramesh", Phone = "9801234567".

2. Number – Stores numeric data used in mathematical calculations. Example: Age = 15, Marks = 450.

3. Date/Time – Stores dates and times. Example: Date of Birth = 12/05/2009.

4. Currency – Stores monetary values with up to four decimal places. Example: Salary = Rs 25000.50.

Select query is a query that is used to retrieve (select) and display the required data or records from one or more tables of a database based on a given condition. It does not make any change in the data stored in the table; it only shows the records that match the specified criteria. For example:

SELECT * FROM Student WHERE Marks > 80;

This displays the records of all students whose marks are greater than 80.

The query used to make changes in many records in one operation is the update query, and the query used to remove many records in one operation is the delete query.

• Update query changes the values of one or more fields in many records at once. For example:

UPDATE Student SET Marks = Marks + 5 WHERE Grade = "A";

• Delete query removes many records from a table in one operation. For example:

DELETE FROM Student WHERE Marks < 40;

The Caption property sets a descriptive label for a field that is displayed in Datasheet View and on forms and reports instead of the actual field name.

DBMS (Database Management System) is a software that is used to create, store, manage, and retrieve data in a database. It acts as an interface between the user and the database, and provides facilities like data storage, data security, and controlled access to data.

Two examples of DBMS:

1. MySQL
2. Microsoft Access (Oracle and SQL Server are also valid examples)

Query

A query is a request made to a database to retrieve, insert, update, or delete data. It is written in a database language such as SQL (Structured Query Language) and is used to ask the database for specific information.

Types of Query

1. Select Query – Retrieves records from one or more tables based on a given condition. Example: SELECT * FROM Student WHERE Marks > 80;
2. Action Query – Performs an action on the data rather than just displaying it. It includes:
   • Update Query – Modifies existing records. Example: UPDATE Student SET Marks = 90 WHERE Roll = 5;
   • Append (Insert) Query – Adds new records to a table. Example: INSERT INTO Student VALUES (6, 'Ram', 75);
   • Delete Query – Removes records from a table. Example: DELETE FROM Student WHERE Roll = 6;
   • Make-Table Query – Creates a new table from the result of a query.

Form vs Report

KILL "pathname"

The KILL statement in QBASIC deletes a file from the disk. The file name (pathname) is written in double quotes, e.g. KILL "DATA.TXT".

Date/Time is the data type used to store date of birth in MS-Access.

Two elements of a database are the table and the field (records, queries, forms and reports are also elements).

A database is made up of tables, and each table consists of fields (columns) and records (rows).

Four features of MS-Access:

1. Relational database management: stores data in multiple related tables and links them through primary and foreign keys.
2. Tables, queries, forms and reports: separate objects for storing data (table), extracting data (query), entering data (form) and printing data (report).
3. Data validation and primary key: validation rules and input masks control the data entered, and a primary key uniquely identifies each record.
4. Wizards and easy interface: Table, Query and Form Wizards with a graphical interface let even non-programmers create a database.

A record is one complete row of the table — all the related data about one student. So 00100202S, Aarambha Shrestha, 91 is a record (and 00100203T, Suhisha Rayamajhi, 99 is another).

A field is one column of the table — a single category of data. So Symbol No., Name and Marks are the fields.

Short Text (called Text in older versions of MS Access)

This data type stores letters, numbers (as characters) and special symbols, up to 255 characters.

Design view

Design view is used to create a table's structure and to modify it, such as adding or deleting fields, changing data types and setting field properties.

A database is an organised collection of related data stored systematically so that it can be easily accessed, managed and updated.

Examples: a school's student record, a telephone directory, a library catalogue (any two).

Data sorting is the process of arranging the records of a table in ascending (A to Z, small to large) or descending order according to the values of one or more fields.

Two advantages:

• It makes records easy to read and compare, and gives meaningful reports.
• It makes searching for and finding a particular record faster.

• Query: it is used to retrieve, filter, sort and calculate data from one or more tables according to given conditions (criteria), and also to update or delete records.
• Form: it is used to enter, view, edit and delete the records of a table in a user-friendly, attractive screen layout, one record at a time.

Table

A table is the database object in MS Access that stores data in rows (records) and columns (fields).

Indexed

Setting the Indexed property of a field makes MS Access build an index for it, so searching and sorting on that field become faster.

Data sorting: Data sorting is the process of arranging the records of a table in a particular order, either ascending (A to Z, smallest to largest) or descending (Z to A, largest to smallest), based on one or more fields.

Why a primary key is necessary: A primary key is a field that uniquely identifies every record of a table. It is necessary because it does not allow duplicate or empty (null) values, so each record can be identified, searched and related to other tables without confusion.

Query: A query is a database object in MS Access used to retrieve, display, and sometimes modify data from one or more tables according to given conditions (criteria).

Why an update query is necessary: An update query changes the values of existing records in a table in one operation. It is necessary because many records can be corrected or revised together (for example, raising the price of all products by 10%) quickly and accurately, instead of editing each record manually.

Yes/No is the logical data type of MS-Access. It stores only one of two values: Yes/No, True/False or On/Off.

A Database Management System (DBMS) is software used to create, store, organise, retrieve and manage data in a database. Examples are MS-Access and MySQL.

Four objects of MS-Access: Table, Query, Form and Report (Macro and Module are also objects).

A foreign key is a field in one table that refers to the primary key of another table. It is used to link the two tables and set up a relationship between them.

A primary key is necessary in a record because it uniquely identifies each record in a table. It does not allow duplicate or empty values, so it prevents confusion between records and makes searching and relating tables possible.

The default extension of an MS-Access database file is .accdb (in older versions of MS-Access it is .mdb).

The default field size of the Text data type in MS-Access is 255 characters (in recent versions, the default for new text fields is set to 255).

A Relational Database Management System (RDBMS) is a DBMS that stores data in the form of related tables (rows and columns) and uses common key fields to link the tables.

Two examples of DBMS: MS-Access and MySQL (Oracle and SQL Server are also examples).

Two advantages of a computerized database:

1. Fast storage and retrieval — records can be searched, sorted and retrieved in seconds, instead of searching through paper files by hand.
2. Reduced redundancy and better security — data is stored in one organised place, so duplication and inconsistency are reduced, and access can be controlled with passwords and user permissions.

A query is a database object in MS Access that is used to retrieve, display, update or delete the required records from one or more tables according to given conditions (criteria).

Two advantages of query:

1. It displays only the required records and fields from one or more tables, so the user finds information quickly.
2. It can perform calculations and modify many records at once (for example with update or delete queries), which saves time and effort.

A table is the basic object of a database in which data is stored in rows (records) and columns (fields).

Two importance of table:

1. It is the main storage place of data; all other objects such as queries, forms and reports take their data from tables.
2. It stores related data in an organised way, so data can be easily searched, sorted and updated.

(b) Primary key

A primary key is a field (or set of fields) whose value is unique for every record in a table, so it uniquely identifies each record. Candidate keys and unique keys are also unique, but only one of them is chosen as the primary key.

(c) SELECT

The SELECT command is used to retrieve (query) records from a database table. INSERT adds new records, UPDATE modifies existing records and ALTER changes the structure of a table.

Database Management System (DBMS) is a software package used to create, store, organize, retrieve and manage data in a database. It acts as an interface between the user and the database, allowing data to be stored, modified and accessed easily and securely. Examples: MS Access, MySQL, Oracle.

One advantage: It reduces data redundancy (duplication of data), because all related data is stored in a single organized database and need not be entered and maintained in several places.