(X) Database Notes

Introduction Data : A raw fact about anything which does not give any complete meaning is called data. Information: The processed data which gives some or complete meaning is called information. Database : An organized collection of related data that can be used for multiple purposes is called database. Example of database: Telephone directory, Student’s record, Dictionary, data of SLC results etc. DBMS : The software collection which helps to manage the database is called DBMS. Examples of DBMS: i) MS-Access ii) Oracle iii) FoxPro iv) Microsoft SQL-Server etc. Advantages of DBMS: - Provides an organized way of storing data - Facilitates quick and efficient retrieval of information - Reduces data redundancy - Improves the consistency of data - Allows sharing of data Disadvantages of DBMS: - Cost of hardware and software installation is high - High risk of data leakages and hacking Differences between the Database and DBMS:
Database DBMS
(a) It is collection of related data or information. (b) It is application software to manage database.
b) It is a part of DBMS. b) It is a software system which contains database.
c) Example: Telephone directory, Dictionary etc. c) Example: MS-Access, Oracle etc
MS-Access: It is a Relational Database Management System(RDBMS) developed by Microsoft Corporation, USA. It is used to create database tables, forms, queries and reports as user requirements. The Access database consists of several different components. Each component is called an object. Access consists of seven objects. The various objects of MS-Access are Tables, Queries, Forms, Reports, Pages, Macros and Modules. All objects of a database are stored in a single file, and the extension of database file is .accdb. Field:
  • A field is a smallest unit of database, which store one type of data. For Example: Std_Name is a field that stores name of a student.]
  • A field is recognized by unique name called field name.
Record:
A group of related fields that describes a person, place, or thing is called a record. It is also called tuple.

Field Property Area
  1. Field Size: Field size property is used to set the maximum size for the data stored in the filed set to the Text or Number Data type.
  2. Format: Format allows you to display the data in a different format. For example: Predefined formats for date and time formats are: General date 8/5/2020, Long date Wednesday, August 5 , 2020 etc.
  3. Caption: Caption field is used when you want to display an alternate name for the field to make the field name more explanatory.
  4. Default Value: A default value is one that display automatically for the field when you add a new record to the table.
  5. Data Validation: Data validation enables you to limit values that can be accepted into a field.
  6. Validation text: Validation text allows to type text to be displayed if the user input value in prohibited by the validation rule.
  7. Required: You can enter 'Yes' value for 'Required'if a field should always receives a value during your data entry.
  8. Index: Index is one of the important properties of database that speed up searching and sorting of records using on the field.
  9. Input Mask: An input mask is a string of characters that indicates the format of valid input values. Input mask is used when you need consistent input values. For example, You can use input mask in field called mobile number which will check it is ten digits or not.
Four objects of MS-Access: 1. Table 2. Query 3. Form 4. Report Table: The basic element of a database which consists of vertical columns and horizontal rows is called table. Since entire data is managed and kept in a table for the future retrieval process , it is also called the primary object of database. The purpose of table in MS-Access is to store information. There are THREE parts in a table.
  1. Field Name : A field name can be up to 64 characters long including space.
  2. Data type : The data type determines the kind of values that users can store in the field.
  3. Description : The field description is optional. It helps you describe the field and is also displayed in the status bar when you select this field on a form.
Data type Type of data Memory size
1. Text or
Short Text		 (Default) Text or combinations of text numbers, as well as numbers that don’t require calculations, such as phone number Up to 255 characters
1. Memo or
Long Text		 Lengthy text or combinations of text and numbers Up to 65,535 characters
3. Number Numeric data used in mathematical calculations 1,2,4 or 8 bytes
4. Date/Time Date and time values for the years 100 through 9999 8 bytes
5. Currency Currency values and numeric data used in mathematical calculation involving data with one to four decimal places 8 bytes
6. AutoNumber A unique sequential (incremented by 1) number or random number 4 bytes
7. Yes/No Yes and No values and fields that contain only one of two values (Yes/No, True/False, or On/Off) 1 bit
8. OLE object An object (such as a MS-Excel, MS-Word document, graphics , sounds or other binary data) Up to 1 GB
9. Hyperlink A link address to a document Up to 2048 characters
10. Lookup Wizard Displays data from another table 4 bytes
Primary key: A field that uniquely identifies each record in the database is called primary key. For example: Bank account number, student registration number etc. MS-Access neither permits duplicate values in primary key not does it permit null values. Two uses or advantages of Primary Key: 1. It is used to uniquely identify the each records in a database. 2. It is used to prevent data redundancy in the primary key field. Secondary Key (Foreign Key): The key which refers to the primary key of another table is called Foreign Key, and used to establish the relationship between the two tables. Query: An important object of database that allows user to view data and change data of the database with the different criteria is called query. There are 2 types of queries. (a) Select Query: A select query retrieves information that you want from one or more tables in a database. Then, the select query presents the information that you retrieved in a format you want. (b) Action Query: It performs an action on data in a table. You can use an action query to insert new records, to update existing records, or to delete existing records. Types of Action Query: i. Delete Query ii. Update Query iii. Append iv. Make table Query Uses | Importance of Query: 1. It reduces the time to see the result. 2. It is used to update, delete the records very easily.

Difference between select and action query

Select Query Action Query
(i) It does not make change to database. (i) It makes change to database.
(ii) While running Select query no alert message appear. (ii) While running Action query alert message appears.
Form: Form is a database object used primarily to displays records on screen, to make it easier to enter new records and to make changes to the existing records. Importance of Form: 1. It is used to view the records individually. 2. It is easy to add new records. Report: A report is one of the components of MS-Access. It is used to display the data as per the format selected by the user. It is a summary of the data contained in one or more tables on queries. Importance of Report : 1. It is used to summarize the records. 2. It is in ready position for printing in paper. Difference between Form and Report:
Form Report
i. It is used to enter, view and edit information. i. It is used only to view information.
ii. They are usually displayed on screen. ii. Report can be previewed on the screen, but they are usually printed.
Sorting:
  • A process of arranging the data items in some sequence or order according to the given criteria is called sorting.
  • It can arrange data in ascending or descending order.
  • Sorting helps to find the data quickly.
  • Sorting helps to reduce data redundancy.
Filtering : Filtering means showing only those records that meet certain criteria. Sample Question for 1 marks:
  1. Which is the logical data type of MS Access?
    2. Ans: Yes / No
  2. Which data type is used to store audio, video , pictures etc?
    3. Ans: OLE Object
  3. Which data type is used to store alpha numeric character or special symbols in MS Access?
    4. Ans: Text (In beginning Access 2013 Version Text is renamed as Short Text)
  4. Which data type is used to displays data from another table?
    5. Ans: Lookup Wizard
  5. Which data type is used to store salary of an employee?
    6. Ans: Currency
  6. Which data type is used to store marks obtained by student in computer science?
    7. Ans: Number
  7. Which data type is used to store email id of a user?
    8. Ans: Hyperlink
  8. Which data type is used to store link address?
    9. Ans: Hyperlink
  9. Which view is used to modify a table in MS Access?
    10. Ans: Design View
  10. Write down the criteria for a query which display all records having Name start with "P".
    11. Ans: Like " P* "
Sample Question for 2 marks:
  1. What is DBMS. Give any two examples.
  2. What is database? Give any two examples.
  3. Define field and record.
  4. What is report? Write its use.
  5. Define form. Write any two uses of it.
  6. Define Query. Write its uses.
  7. What is primary key? List any two advantages of it.
  8. What is data sorting? List any two advantages of using it.
  9. What types of work is done in MS Access using form and query object?
  10. Define 'Sorting' and 'Filtering'.
  11. Explain the view of a table in Database.
    12. Ans: A database view is a subset of a database and is based on a query that runs on one or more database tables. Database views are saved in the database as named queries and can be used to save frequently used, complex queries. There are two types of database views: dynamic views and static views.
  12. Define freezing and unfreezing the column. How is it done?
    13. Ans: Freezing the column means fixed the column while scrolling the page and unfreezing is vice-versa. Freezing and unfreezing rows and columns from scrolling is useful for larger sheets. Click View. In the View tab, click Freeze Panes and select the freeze option you want to choose. i.e. Select View > Freeze Panes > Freeze Panes.
  13. Mention the process to add records in a datasheet.
  14. What is data type? Name any four data types used in MS Access.
  15. Differentiate between select query and action query.
  16. Explain the method of inserting new records into table using query.
    17. Follow the below steps to insert a new record using INSERT SQL statement: Step 1: Let’s assume you have already created the below table “Marks”. If not, create this table first where you are going to insert new record using query. Step 2: Click Create tab and click Query Design from Queries Group. Step 3: Close the Show Table window. Step 4: From the Design tab, click on the SQL View button in Results group. A SQL View window will appear. Step 5: Type the below INSERT statement in the SQL View window.
  17. List the cases in which update query can be used.
  18. Explain the process of creating form using wizard.
  19. Differentiate between form and report.
  20. What are the components of database management system?
    21. Ans: (i) Hardware     (ii) DBMS     (iii) Data    (iv) Procedures    etc.