8.-Database Management System with MS-Access.pptx

DBMS

1. Welcome

2. Database Management System Chapter 8

3. Database Terminologies Data: Raw components of information It may be numbers, figures, characters or symbols Smallest unit of information Raw facts given to the computer for processing. It does not give any clearing meaning. It can be defined as raw components which may be numbers, figures which does not give any clear meaning For example: Ram, apple, eat, 4

4. Database Terminologies Information: It means processed, analyzed or rearranged data An organized collection of related data which gives complete sense The result of data processing is called information For example: Ram eat 4 apples a day Data processing: The process of getting useful information from data by processing data with the help of database software is called data processing

5. Database Terminologies Database: Purpose of database is used to store, organize and retrieve data It is a collection of data that is organized so that its contents can be easily be accessed, managed and updated. Database ensures the storage, preservation and timely retrieval of data For example: dictionary, telephone diary, attendance register, mark ledger, encyclopedia

6. Advantages of Computerized Database over Manual Database It allows us to store and manage a large amount of data easily and effectively It reduces the data redundancy i.e. duplication of data It allows us to retrieve variety of information correctly in short period of time It allows us to update large volume of data easily and accurately in short period of time

7. Components/ Elements of Database Table: heart of database Tables are the building block of database . A single table is used to store data of a specific purpose or subject such as telephone directory, book records in library etc . Since entire data is managed and kept in a table for the future retrieval process it is also called the primary object of database. Table stores large volume of data into rows called records and column called field . When fields and records are combined forms a complete table.

8. Components/ Elements of Database Table- Book Records Code Book Name Level Author Published Year B501 Science Secondary Syam Bata 2007 AD C321 Math Higher Secondary Suresh Thapa 2010 AD A876 Major English Bachelor Arun Sharma 2011 AD B213 Micro Economics Master Purna Rana 2001 AD

9. Components/ Elements of Database Record: It is the collection of multiple related fields which give complete information about person or thing It is also called tuple. Here , the combination of Code, Book Name, Level, Author, Year of Publication values forms a record.

10. Components/ Elements of Database Field: A field or column contains information about a certain type for all records. It is also known as attributes. For example, in Table – Book Records, Code, Book Name, Level, Author, Year of Publication are the fields of book records

11. DBMS (Database Management System) Database management system (DBMS) is a computerized system that stores data, processes them and provides information in an organized form. DBMS basically deals with the creation of database, its management and retrieval process . Creating, modifying, updating, appending, organizing, sorting, removing, and retrieving are the major tasks performed easily, efficiently and accurately using Database management system A collection of software that manages data stored in the database For example: MS- Access, FoxPro, MS-SQL, Oracle, dbase, My-SQL

12. Features of DBMS Large volume of data can be stored and updated easily Provides data integrity: Integrity makes sure that database accepts only the valid data Easy in data administration or data management Provides the data sharing facility Reduces the data redundancy (duplication of data) Supports centralized control security: Database administrator has complete control over database Controls data inconsistency: Any changes made in one file is automatically done in all the files containing that information

13. Disadvantages of DBMS Required hardware and software are expensive Higher operating cost Costly and time consuming procedure Requires technical manpower to handle Difficult to recover backup

14. Database Vs DBMS Database DBMS 1.It is a collection of facts or data 1. It is a software to manage database 2. It consists of data 2. It manages data stored in a database 3. It is a part of DBMS 3. It is a software system which contains database 4. For example: result sheet, salary sheet 4. For example: FoxPro, DBASE

15. MS-ACCESS MS-Access is DBMS software developed by Microsoft Corporation A complete database package of MS-Office developed by Microsoft corporation It is a relational DBMS where data are arranged in the form of tables 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 .

16. Features of MS-ACCESS Creates sophisticated database quickly Analyze and modified your data easily with queries Makes customized data entry forms It uses tables to store the data It is easy to search and find the data It can handle huge volume of data Easy to view and design form and reports using Access Queries in Access help to view any information we desire

17. Objects of MS-ACCESS TABLE: All databases must have at least one table. Primary building block of database that stores large volume of data in the form of rows and columns All data are stored and managed in a table. Every table in a database focuses on one specific subject. Table stores large volume of data into rows and column. Importance of table: It holds main information To store the data about specific topic It facilitates grouping and storing the relevant data

18. Starting MS-Access Step 1: Press Windows Key and R at the same time. Run window appears. Step 2: Type ‘ msaccess ’ as shown in the figure downside. Step 3: Finally click on OK button.

19. Creating a new database file When MS-Access is started, it gives user a choice of either creating a new database or opening an existing database. Follow the below steps to create a new database file: Step 1: Click on the Blank desktop database

20. Creating a new database file A Blank desktop database dialog box will appear. Step 2: Type the File Name. Step 3: Choose the required folder where you want to save. Step 4: Click on Create button.

21. Creating Table in MS- ACCESS Follow the below steps to create a table: Step 1:From Create tab, click on Table Design button of Tables group. Step 2:Add the required Field Name and Data Type. Step 3: Right click on the Table1 tab in the above figure. Step 4:Type Table name. Click on OK.

22. Table Datasheet and its Formatting Table datasheet is simply the display of records in row and column format. Using the datasheet view, you can add, modify, search or delete records. There are mainly two views of Table. Design View Related with table structure. You can add, edit or delete field and its properties. Datasheet View Related with records. You can add, modify, search or delete records.

23. Switching to Datasheet View Follow the below steps to switch from Design View to Datasheet View: Step 1: Click on the Design Tab. Step 2: Click on View drop-down button from Tools group. Step 3: Click on Datasheet View.

24. Adding Fields in a Table Follow the below steps to add a field in an existing table: Step 1: Open the database and open the table. Step 2: Right click on the field above which you want to add a new field. Step 3: Click on Insert Rows. A new blank field is inserted above the selected field. Step 4: Type Field Name and Data Type .

25. D atatypes Data type determines the kind of values that you can store in the field. It is the characteristics of a field that determine the kind of data that can be stored in a field After you enter the valid field name, it allows a user to select the data type for the field. You can use the Data Type property to specify the type of data stored in a table field such as text for name field, number for class field, Date and time for DOB field etc. Each field can store data consisting of only a single data type.

26. D atatypes MS-Access supports the following data types:

27. D atatypes

28. D atatypes

29. D atatypes

30. Field Description You can enter the description of each field in the Description column. It helps you to remember the use and purpose of a particular field. This is an optional part in a database. It is displayed in the status bar when you select this field on a form.

31. Primary Key Primary Key is a special field or group of fields in the table that uniquely identifies each record from the database. To distinguish one record from another, table must contain a unique field named as primary key. The primary key does not accept duplicate value for a field and it does not allow a user to leave the field blank or null. The primary key is an identifier such as a student ID, a Product code, Exam roll no. etc. Hence , primary key is unique to each record. Importance of Primary Key: To identify each record of a table uniquely. To reduce and control duplication of the record in a table. To set the relationship between tables.

32. Primary Key Setting the Primary Key Follow the below steps to create a primary key field: Step 1: Open the table in Design View. Step 2: Click on the field in which you want to apply primary key. Step 3: Click on Design tab. Step 4: Click on Primary Key button from Tools group. A key icon will be displayed on the left side of the primary key field.

33. Field Properties Pane Field properties pane displays list of properties associated with each field data type. To control over the contents of a field we can set the field size, format, validation rule etc. Field properties are a set of characteristics or attributes assigned in the table that provides additional control over how a field works. The field property of a field data type determines how the values in the field are stored and displayed.

34. Field Properties Pane

35. Field Properties Field Size: You can use the Field Size property to set the maximum size for data stored in the field that is set to the Text or Number data type. This property sets the maximum size of the data stored in the field. Default field size of text field data type is 255 In text field data type a user can store maximum of 255 characters Default number field data type is long integer

36. Field Properties Caption: Caption field property is the alternative name given for any field. This helps to make field name more explanatory. It is the common field property of all the field data types The maximum size for this is 2048 characters. Default value: Default Value field property is one that is displayed automatically for the field when you add a new record to the table. It is used when a same entry is frequently used in a table. Hence , it prevents us to enter the same value again and again. It increases the accuracy of data to be entered in a table Note: not for auto number, OLE object

37. Field Properties Format: Format field property allows you to display data in a format different from the way it is actually stored in a table. It is also known as display layout for a field. This property customize the way numbers, date/time and text that are displayed and printed It allows a user to display data in different formats. It only changes the way we see the data on screen and it has no effect on the way the value is actually stored in the table

38. Field Properties Input Mask: Input mask field property specifies the pattern of data that you wish to enter into the specific field. It is simply the control over the data entry. Applicable to text and date/time

39. Field Properties Validation Rule: It is used to limit the values that can be entered into a field It is used to enable us to limit or control the entry of data according to specified criteria. It ensures the data to be entered in a field must be as the specified condition or within the range of values It accepts maximum 2048 characters Importance: Since it sets the range of data or criteria for data to be entered in a field, it helps us to enter accurate data in the field.

40. Field Properties Validation Rule :

41. Field Properties Validation Rule :

42. Field Properties Validation Rule :

43. Field Properties Validation Rule :

44. Field Properties Validation Text: Validation Text is the error message that appears if the data entered is invalid according to the specified validation rule. It is the message for prohibited value entered in validation rule field

45. Field Properties Required: You can use required property to specify whether a value is required in a field or not . So, Yes and No are the options for required property. If this property is set to Yes for a field, the field must receive value during data entry. If this property is set to No for a field, the field can be left blank.

46. Field Properties Indexed: You can use the Indexed property to set an index on a field. It speeds up searching and sorting of records based on a field . For example, if you search for specific student name in a SName field, you can create an index for this field to speed up the search for the specific name. By default, indexed property is set as No.

47. Objects of MS ACCESS Queries : It is one of the most important objects of MS-Access It is a way of retrieving and editing information according to the condition asked It takes data from tables as source of records It is used as the source of records for forms, reports  A query is simply the question that we ask about the data stored in the table . Query is also used to perform various actions on the data. For example, "Show the names of students who have scored greater than or equal to 80 marks in science." "Update the salary of employees by 10%."

48. Objects of MS ACCESS Functions of query: To perform mathematical calculation on data To modify mass records at once To analyze required data of the table To retrieve records from the table To sort records of table based on the condition Importance of query: It displays the result of different tables It sorts and filters the data It manipulates the data according to users choice It can be used as a source or records for forms and reports

49. Objects of MS ACCESS Forms : FORM is a database object which is designed to enter new records and edit existing records easily Forms are the graphical interface used to enter data into the tables or multiple linked tables . Forms are also used to display the information in specific manner, as well as it permits user to add, modify and delete data in a table.

50. Objects of MS ACCESS Advantages of form over table: Forms are easier to enter data than table Forms are easier to make modifications It is easier to focus on just needed information Importance of form: It allows the modification of data directly into the table It allows viewing the table in user defined manner It allows applying mathematical formula for calculations It allows to control and enhance the way data looks on screen

51. Objects of MS ACCESS Report: Reports are the presentation of information in desired format. It is an effective way of presenting our data in a printed format or on the screen softcopy With Access, we can create reports of any table or query. Importance of report: It is required to present the summarized information It helps to present information of a table or query in more attractive and easy to read and understand way It is required to present information in a user’s layout

52. SORTING Sorting: The process of arranging all the records in a table either ascending or descending order is called sorting. Once the data are sorted, it is often simpler to read and understand and access of data are fast. Filtering: It is the process of viewing required record of a table that matches the specified criteria Formatting the table: The process of changing the appearance of a table using different options. For example: change the width, row height, hiding columns etc.

53. QUERY Vs FILTER QUERY FILTER 1. It can be used to update large volume of records all at once 1. It can not update large volume of records all at once 2. A query can be saved as a separate object 2. A filter can not be saved as a separate object 3. It allows us to perform mathematical calculations on numeric data 3. It does not allows us to perform mathematical calculations on numeric data 4. It can retrieve information from multiple linked tables 4. It can retrieve information only from a single table

54. Thank You!