5. Data Management

Lesson No. 5 Data Management Using Access

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) – Commit, RollBack
TCL (Transaction Control Language)
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



2 comments:

  1. Visit http://exza.in

    Since ITOnlineExam.com not working due to Bandwidth limit got exceeded yesterday....

    ReplyDelete

Bitcoins