Chapter – 7 Database and DBMS (MS-ACCESS)

Data: It is the collection of facts and figures collected from different like questionaries’ and queries.  Data are information to the computer which are processed to get desired result and are the piece of information.

Information: The data which gives meaning or suitably arranged for communicating interpretation, and processed by human being by automated means such as computer is called information.

Database

A database is a collection of data that is organized so that its contents can easily be accessed, managed and updated. Examples of database system are

·       Banking software

·       Airlines systems

·       University systems

·       Telecommunication systems

·       Finance

·       Sales

·       Manufacturing

·       Human resources

Basic terms:

File

A file is a collection of electronic information store in computer.

Record: A record is a information about an element such as a person, animal, students, place etc.

DBMS

A database management system (DBMS) is a computer program designed to manage a database (a large set of data), and run operations on the data requested by clients. For example Oracle, Microsoft Access, Ms. SQl, MySQl, dBASE, dBase, FoxPro,  SYSBASE  etc. are the well-known database management system.

RDBMS

Relational Database Management System (RDBMS) is method of viewing information from several, separate database that relate to one another through the keyword or values. The main advantage of RDBMS is that we can simultaneously use more than one database to see information stored in them.

Importance of database:

·       Database stores the data.

·       Computerized database saves data from being lost.

·       Computerized database protects from unauthorized access and use of the data.

·       Data can be stored in a small area or space in computerized database.

Features of DBMS:

·       Easy to access data.

·       Ease to modify data.

·       Delete existing data.

·       Organize the data in proper sequence.

·       Sorting and indexing of data.

·       Easy queries in data.

·       Retrieve the data easily.

·       Print the formatted reports, labels etc.

·       Linking between two or more databases.

·       It can be used as SQl (Structured Query Language)

Microsoft Access

It is a RDBMS software.

Features of Microsoft Access:

·       It provides very useful database features such as tables, forms, reports and queries.

·       It works as front end development tool.

·       This provides end-user applications developer environment.

·       It shares data with other application such as Microsoft Word or Excel.

·       It support three computer programming language such as: SQL, Macros and Visual BASIC etc.

Objects of MS Access

Table

A table is collection of data about a specific topic, such as products or suppliers. Using a separate table for each topic means that you store that data only once, which makes your database more efficient, and reduces data entry errors.

Table is the primary object of Ms Access. A table stores data in tabular form. A table is made up of rows and columns.

Some parts of table are as follows:

Fields (column): The title name of the column that holds data is known as field. A table can have many fields.

Records (rows): The collection of data horizontally for each field is known as record. A record is complete information about an entity.

Data: The actual information stored under a filed title in a row is called data.

Index: A table can have index attached with it. The index is used to keep the data in organized for and helps faster retrieval of data.

Primary key: A primary key is a field which is uniquely identified. All the records of a table are uniquely identified on the basis of primary key. The column that has been defined as the primary key can’t hold null value. For example a student’s roll no. can be defined as primary i.e can’t be null and repeat.

Compound key: The primary key based on more than one field is known as composite key. It consists of multiple columns, because one column is not sufficiently unique.

Foreign key: Foreign key is the linking pin between two tables. It shows the relation between any two tables.

Queries: A query is an object of MS-Access which is used to retrieve records from database, search or question that you make for a record or item. The record is stored in the database file. Queries help to investigate about data records.

Forms

Forms present the data from a table or a query in a way we want it to be displayed. The fields in the table or query are made available to place on the forms we create.

Uses of form:

·       Displaying and editing data.

·       Controlling application flow.

·       Accepting input.

·       Displaying messages.

·       Printing information.

Reports

Reports are the printed results of data processing. Reports are specially formatted collections of data, organized according to your specification for summarizing and printing listings of database data. While forms are designed to be used onscreen, reports are designed to be viewed and printed.

Relationship: In a database we can establish relation between any two or more tables. Because no data are isolated, but they are associated or related with each other entity in one or another ways. we can generate reports and manipulate data using such relationship between tables.

Data Type of Ms Access

Data type defines the nature of the data to be stored in the column. Access provided different types if data that can be stored.

Text : (Default) Text or combination of text and numbers, up to 255 characters.

Memo : Use for lengthy text and numbers, such as notes or descriptions. Stores up to 65,536 characters.

Number: Use for data to be included in mathematical calculations.

Date/Time: Use for dates and times . It occupies 8 Bytes.

AutoNumber :Use for unique sequential (incrementing by 1) or random numbers that are automatically inserted when a record is added.

Yes/No :Use for data that can be only one of two possible values, such as Yes/No, True/False, On/Off. Null values are not allowed.

OLE Object :Use for OLE objects (such as Microsoft Word documents, Microsoft Excel spreadsheets, pictures, sounds, videos or other binary data) that were created in other programs using the OLE protocol.

Hyperlink : Text or combination of text and numbers stored as a text and used as a hyperlink.

Lookup Wizard Use to create a field that allows you to choose a value from another table or from a list of values using a combo box— -choosing this option in the data type list starts a wizard to define this for you.

Currency : To store monetary value. It occupies 8 Bytes memory.

Field Properties:

Format: It allows choosing different format of data type available.

Caption: It used to display alternative name for the field to make the field name more explanatory. It can contain up to 2,048 characters.

Default Value: Default value is the one that enter automatically in the database and can be changed as per required.

Validation Rule: It is used to set the limitation while entering data in the database. It helps us to customize the data entry and checks for error entry of data.

Validation Text: If wrong data is entered which do not supported by given validation rule, it is used to display messages on the screen and work as a precaution of wrong data entry.

Required: If required is set as Yes then field should always receive a value during data entry.

Indexed: You can choose whether you want to index the table. The available index options are unique index and duplicate index.

Input Mask: It is usually used to set control the data entry in the database. Using this option we can customize the data entry by setting special commands using the following symbols:

Field size property is set to the maximum size for data stored in the field set to the text or number data type. For next data, the default field size is 50 and maximum size is depending on the type of data types.

Data Sorting

The process of arranging the data of table either in ascending or descending order is known as data sorting.