5. Data Management

Lesson No. 5 Data Management Using Access
For IT Online MOCK EXAM   link 1 link 2

Importance of Lesson No. 5 in Exam


Online Exam   (Weight-age: 14 marks)

• Short/Long Answers  -  1 question may come (2 marks)
• MCQ1 Select one Answer - 2 or 3 questions
• Fill in the blanks -  1 or 2 questions
• True or False - 1 or 2 questions
• MCQ2 Select two Answers   - 1 question   
• MCQ3 Select three Answers -  1 question  (may come)

For Objective Questions Practice LINK 1 LINK 2

For Appearing an IT TEST  link 1 link 2

Important Terms

Data – Collection of facts (unorganized)

Information – Processed data (manipulated data to produce results)

Database – Collection of related information grouped together

RDBMS – Relational Database Management System E.g. Oracle, MySQL, Access, MS SQL server

MS Access is an RDBMS software.

Database Objects – Table, Query, Form, Report

Table

Table – Grid (collection) of rows and columns, where data is stored.

Record – Rows/Tuple  in a database table (collection of fields)

Field – Stores attribute value. Column in a table containing discrete element of information

Primary Key – is a column used to uniquely identify rows in the table. Used for relating a table to another one. Cannot have duplicate values. Applied to one column in MS Access.(Not compulsory)

Foreign Key – is a column from another table(primary key column) used to relate tables.

Relationship

One to One – a single record in one table is associated with one record of another table and vice versa.

One to Many – a single record in one table is associated with more than one record in another table.

Many to Many – several records in a table are associated with several records in another table.

MS Access – Data Types for Columns

Text – (default data type) For textual data max 255 characters

Memo – For textual data max 65536 characters

Number – To store numeric data, types – Integer,

                      Long, Single, Double

Date/Time - To Store Date and Time values.

Currency– Used for Currency. Max 15 digits and 4 decimal places

AutoNumber – To give Auto number starts from 1

Yes/No – To store True/False. Takes 1 bit

OLE Object – To store binary files- audio, video, pictures upto 1 GB

Hyperlink – Contains links to other files

Attachment– For attaching files.

Calculated– To store result of expressions involving other column data

Lookup Wizard– To store list of options, which can be chosen

Views in MS Access

Design View – Used to define table, reports, forms. You can modify structure.

Datasheet View – To view, add, edit, delete table rows.

Tables can be created in Design view and and Datasheet view

MS Access Queries & its Types

Query – Used to get specific information from tables, search a database for a specific record.

 IT is fundamental means of accessing and displaying data from tables.

 Used to create, view, change and analyze data tables.

Select Query(default) – To retrieve data from one or more tables.

Parameter Query – Used with other query type. It displays dialogue box prompting for information

Cross Tab Query – To get summarized information. (count, sum, average and other aggregate functions and groups records)

Action Query – To make changes to, or move records. DELETE, UPDATE, APPEND and Make Table queries.

Queries can be created by two ways – Using Wizard, Using Design View

Options used in Query

Criteria option – To apply logical expressions/conditions on a query. It is associated with fields in the query design indicates how to filter records in query output.

Sort option – To sort the rows ascending or descending

QBE – Query By Example

SQL Structured Query Language

SQL – Language used to access data from database. Add, retrieve, modify, delete database. Developed by IBM

Types of SQL statements/queries

DDL (Data Definition Language) – Create, Alter objects

DML (Data Manipulation Language) – Select, Insert, Update, Delete

DCL (Data Control Language) – GRANT and REVOKE

TCL (Transaction Control Language) - Commit, Rollback and Savepoint

A SQL statement/query is made up of SQL clauses.

SELECT – To query a table, to select a table and access data from the database.

SQL clauses

SELECT – To query a table, to select a table and access data from the database.

FROM – specifies the name of table (compulsory clause in Select)

ORDER BY – To sort the table rows in ascending or descending order. Keywords – ASC and DESC

WHERE – To define criteria for rows to selected for output

GROUP BY – To group records on values of a field

INSERT – To add the records in database

Comparison of SQL and MS-Access

SQL is designed for Multi user computer system, while Access is designed for Single computer system.

SQL can support as a developer tool while Access cannot support as a developer tool.

In SQL procedures are supported while in Access procedures are not supported.

Locking of data is available in SQL.

SQL provides security for structure

MS Access - Reports

Report – Reports are based on table or query.

IT is used to display records in the prescribed form.

It is a printable presentation of data gathered from a query.

They are used to view, format, print and to summarize data.

The data displayed on report cannot be edited.

Report design has total 3 sections.

Reports can be created from Design view and Report Wizard

MS Access - Form

Form – Forms provide users with an easy-to-read interface where they can enter table data.

It displays data from one or more table.

Data can be inserted, updated, or deleted from a table using a Form object.

Data entry forms are primary means of entering data into tables of the database.

SHORT QUESTION ANSWERS

Q. Explain the following terms:

Data   -    collection of raw facts, numbers about things

Database - It is an organized collection of data of an organization stored for easy retrieval.

In Relational Database, data is stored in series of tables which are related to each other through common columns.

Table -  It is a grid of rows and columns or the collection of records.

Field   -   It is used to store same type of data. It is a single column of the table. It is the smallest element of the table.  E.g.   GRNO,  FNAME,  DOB, etc.

Record - It is collection of data about various fields in the table. A record is  a row in the table storing information of a particular entity/object.

DBMS  - It stands for Database Management System.

It is an application software package for defining, creating, editing, deleting and controlling database. It controls the interaction between the databases and application programs/users.

It ensures the integrity and security of the data.

RDBMS  - It stands for Relational Database Management System. It is DBMS which follows Relational Model.

E.g.  MS Access,  MYSQL, Oracle,  Sybase, SQL Lite, HANA, Dbase

MICROSOFT  ACCESS

Q. What is MS Access?

It is a database application developed by Microsoft Corporation available in Microsoft Office Suite. It is powerful program which can be used to create and maintain database and create an application.

Versions - MS Access 2003, 2007, 2010, 2013, 2016

Q. Give the features of MS Access

Features:

·       Easy to learn ,  user friendly GUI

·       Data is stored in the form of tables

·       Retrieval of data is possible at any point

·       Provides filter options

·       Supports complex data types. Files can be stored like photos, documents, spreadsheets, etc.

·       Provides WYSIWYG forms design interface.

·       You can easily paste Excel tables into Access

·       Provides tools for analyzing data like formulas

·       You can save data reports as PDF, XPS format.

·       Security is provided by means of password. Specific permissions can be given for editing, viewing database.

Q. List the different Access Data types

Microsoft Access Data Types

Data type	Description	Storage
Text	Use for text or combinations of text and numbers. 255 characters maximum
Memo	Memo is used for larger amounts of text. Stores up to 65,536 characters.
Byte	Allows whole numbers from 0 to 255	1 byte
Integer	Allows whole numbers between -32,768 and 32,767	2 bytes
Long	Allows whole numbers between -2,147,483,648 and 2,147,483,647	4 bytes
Single	Single precision floating-point. Will handle most decimals	4 bytes
Double	Double precision floating-point. Will handle most decimals	8 bytes
Currency	Use for currency. Holds up to 15 digits of whole dollars, plus 4 decimal places. Tip: You can choose which country's currency to use	8 bytes
AutoNumber	AutoNumber fields automatically give each record its own number, usually starting at 1	4 bytes
Date/Time	Use for dates and times	8 bytes
Yes/No	A logical field can be displayed as Yes/No, True/False, or On/Off. In code, use the constants True and False (equivalent to -1 and 0). Note: Null values are not allowed in Yes/No fields	1 bit
Ole Object	Can store pictures, audio, video, or other BLOBs (Binary Large OBjects)	up to 1GB
Hyperlink	Contain links to other files, including web pages
Lookup Wizard	Let you type a list of options, which can then be chosen from a drop-down list	4 bytes

Q. Explain the Database Objects

Database Objects (MS Access)

1)               Table

It is a collection of records containing columns(fields) and rows(records).

2)               Query

A query is used to retrieve information from the database. It is the question about the data stored in the tables.

Example –

   Select customerid, firstname from Customer

3)               FORM

Forms are used to accept users entry. Data can be viewed, edited, or printed through forms.

4)               REPORT

It is used to display data in a prescribed format. Data in reports cannot be edited. It can be based on a query or a table.

5)               Macros

It is used to automate repetitive tasks. You can store a series of commands or mouse actions in the macro.

6)               Modules

Modules are small user defined functions which perform a specific task.

For Objective Questions Practice LINK 1 LINK 2

For Appearing an IT TEST  link 1 link 2