AES set ® € se an, In this chapter, students will learn s a CHAPTER 2 i AF cas sneen > oeosonn batabave snd Rs Uses + eresting a Table n stabace Management Acces Sete fet Properties Invovction to Access + Working with Olerent Components of Acces Opening Access and * Ereting Database + Spenng an Eesting + Renacng a Table re 5 Deerng abe Database Management Serres, ¢st5 Using Access \\ Traditionally, a file system was used for storing and organizing files. It is very difficult to Protect a file in a file system. It does not have a crash recovery mechanism. Hence, if a system crashes while entering some data then the content of the file will be lost. Thus, a database management system is used to store and retrieve data in an efficient manner. Scanned with CamScanner4 ts veto DAE? (cara is a collection of raw or unorganised facts that needs to be processed to Male, eaningful and organised. Example: 101, 68 ws (8 Jufrrmabin— } ‘When the data is processed, organised and structured in a specific way to Make Meaningful and useful, it is called informatior Example: Data - 110, 89 Information - The roll no of a student is 110 and his/her marks in Computers is 89, So the data 110 and 89 become useful for the student. Asbine Anlebnoe 2 A database is a collection of data organized in J manner that allows easy access, retrieval, and use of that deta) In a manual database, you might record data on paper and store it in a filing cabinet. Example: A telephone directory, a dictionary and a list of employees in a company and books in a library are examples of a database. Following is an example of student database 12011 Anuj Mahajan 2/2/2011 __| 3rd New Lane, Colaba 12012 | Ashish Verma 12/23/2010 | Princess Street, Marine Lines 12013 | Imran Sheikh 7/18/2010 | 29/2 Oyster, Cuffe Parade Examples of Database ® Hospital: Traditionally, all the data is stored in a database, Scanned with CamScanner® School: In schools, teachers use a database to maintain a record of students’ details such as personal information and academic performance. Library: In libraries, a database of all the books kept and issued by a library is maintained. It consists of the book's name, author and availability information. Supermarket: In supermarkets, all the details of a supermarket's products are kept in a database. All e-commerce websites also use databases to keep records of their products. IATABASE MANAGEMENT) SYSTEM (DBMS) / - Ton - & DBMS? Aloe LG Gotwae that helps to organise the data is called DBMS (Database Mandyement syn) fatabase Management system enables you to enter, organize, select and retrieve information i) from the databaseJfIt acts as an interface between the database and the application softwar€’i) that_uses the database) Basically, it provides tools to store, add, modify and delete data, search for specific data, and generate printed reports of data. There are several DBMS available in the market. For example (OpenOffice Base, MySOL, Oracle, Microsoft Access, etc slau Wa 4: a, ARMS BD a ORACLE } cae aa DATAGAGEI myst @ mongo DB Nowe Slane . Advantages of Database Management System Reduced Data Redundancy) Data redundancy occurs when the same piece of data is stored in two or more separate locations. In the below-given Library table, the records of students are stored who have issued the book from the library. $002 | James. 123, Sector 12, Chandigarh| 19-08-2020 |A tale of two cities $003 | Mela Thomas | 33, Sector 54, Chandigarh | 09-08-2019 |Great Expectations S002 | James 123, Sector 12, Chandigarh} 07-08-2020 | Quantum Physics $004 | Sharon Jones | A-89, Sector 45, Chandigarh} 19-08-2020 [Davia Copperfield Library Table In this table, the record of James is repeated two times which is not necessary and is @ wastage of space. This is called redundancy and is not allowed in a database. Scanned with CamScannerq 2.Gharing of bata) so mcr ng the same database to extract the d neg Multiple users can use 3{ Data security ) Ae In a DBMS, thé database administrator has complete control of Se and a , : “ 4 a . ns. ensure that data is accessible only with permissiot Data 4{ Backup and Recovery of ») i automatically takes backup and recovers a computer system after a crash o system failure, x7 LAT .) Disadvantages of Database Management Syste! However, there are very few disadvantages of using a DBMS. They are as follows: «(High Cost: The cost of the hardware and software requirements for the proper working OT DEMS ff very high, 2)In case the database fails, the whole data in a database is lost = (Database Failu © Performance: Performance of large databases is very slow. LN) AGCESS Access is a database management system that was launched by Microsoft. It is a part of the Microsoft Office suite, Itis used to help organizations such as companies and schools to collect, organise, manage, and access data in such a way that it can be easily accessed by users, Uses of Access El als Unk maw we Access. Access is useful in many ways. It is used to do the following tasks: Create databases and tables Y Store data in the form of tables Access data in a database as required w Print data in the form of reports) SLSMeomponents of Comp of Access (faz Databases in Access are composed of four objects: 1. Tables 2. Queries 3. Forms 4. Revors) ee =o Scanned with CamScannerTabi - te Let us arn about these components in detail, fable: A table is the main component of a database management system.)A table like spreadsheet software uses rows and columns to present the data. In other words, a table is a collection of data arranged in the form of rows and columns as shown in the Students table given below. Here, you can see that the data is stored in a tabular form. Field Roll Numbers Names Native City [Record || 1 Aditi Sharma Kolkata 2 Amit Chaudhary Mumbai ros Sara eae Delhi Eath row in a table is called data interrelated to each other Each a called a field. meee ena (Form: Aform {sa user eport: / a printable format.) ‘Gon A query is used to display the specific data as Vii by the user based on some conditions, ) OPENING ACCESS AND CREATING A DATABASE The steps to open MS Access are: 1. Click on Start. 2. Navigate programs and select Access 2016. The following window appears: r- Aupsins | for interface ae ie users the facility to enter data in tables and queries) A form has an attractive interface that accepts data from the user and allows the user to view the data stored in a table. A report is used to display the selected data in ‘Student Table record,) which contains ch cotimn in a table is ey DD eve annate 2002 Recent ated Y rr ostewss ey @ 00 a DB secon O teceecere cout WD Aecbe tesa trader 2222 @ testicon | A Scanné D Hype neve to search ied with CamScannerTXaa x} Sgninta get te most out oftice = Access Window . Here, choose Blank desktop database to create a new database. he following window appears: . Specify the filename in the File Name text box and click on Create. The following screen will appear: Scanned with CamScannerQuick Access Toolbar Ribbon Document Tabs Bar Navigation Pane — | | | Work Areo | | ms + tect sere 8 Nawe Le a eu Windmn. cess window: The following are the components of an Ac Record Navigation Bar = (Title bar(It is the topmost horizontal bar. It displays the name of the current database ye are working op «(Quick Access TodlbarY'The Quick Access Toolbar lets you access common commands) You can customize the commands depending on your preference. = (sinbom)rne Ribbon. contains all of the commands to perform common tasks in Access. It has rhultiple tabs, each with several groups of cor : e hab ins OTD Bor, @Ouiek Acne Tenth m Scanned with CamScanner7 « Guvigation Pane) The Navigation pane displays all of the objects contained in your jatabase. You c&n open, rename, and delete objects using the Navigation Pane. ™ (Document Tabs Bat: All open objects are displayed in tabs on the Document Tabs bar, To view an object, Click its tab, ® (Work sco) Here we create forms, tables, and reports. The work area differs according to the dafabase element selected. (Record Search Box) You can use the Record Search box to search for any term in he cUireNt Object. The first result will be selected automatically. To navigate through additional results, press the Enter key on your keyboard. ® Gecord Navigation Bar) The Record Navigation bar allows you to navigate records one at atime. Click the arrdws to navigate through the records. You can jump to a specific record by typing its ID number into the box. P22 NNO NETS Before you enter data or modify your objects, you will need to open your database. The steps to open an existing database are: 1. Select the File tab to go to Backstage view. 2. Click Open. 3. Click on Browse. 4 - The Open dialog box will appear. Locate and select the database, then click Open. Scanned with CamScanner- Nowe Ut Live views vr There are two views available for usage while working with tables in Access, These views assist in creating and changing the table structures. To shift between the views, click on the View button, present in the Views group either ‘on the Home tab or Field's tab in the Views group and select the desired view. 1 (batasheet View ey (r Datasheet view, the table is shown as a grid of rows and columns) The fields are isplayed as columns, and the recards are displayed as rows. 2 Gesi n Vie! : V Gn the Design view, you _can enter the field names, data types, and descriptions) The Design view is divided into two parts: 7 Field Grid Pane: In this pane, you can define the field names, , their data types, and description. . Field Properties Pane: This section is used to set properties for the fields defined. in the table. CRESS ALLL , The steps to close a database are: 1. Select the File tab to go to Backstage view. 2, Select Close. 3. If you have any unsaved objects, a dialog box will appear for each one asking if you would like to save it. Select Yes to save the object, No to close it without saving, or Cancel to leave your database open. fig, cv revmertto sre ranges to re cern aver vent we) (om) ene) Scanned with CamScannery CYA MATT Neel A data type is used to define the type of data that can be entered in a particular field, You can enter text (alphanumeric values), number (decimal or numeric, values) and date and time (date values). A field with a number data type will not allow you to enter text values. The common table below: (0 Text) | Gong Text) Field Type data types used in Access along with their descriptions are given in the Text or combinations of text and numbers that does not re uire (er lations, such as phone numbers, address. (max 255 choracters) G large amount of alphanumeric data (64,000 characters) umber ) (wumeric data with or without decimal values ) ate/Ti me) Gate and time values ) | 5) AutoNumber (eure vesINo ) — (as a contain only one of the two values (Yes/No, True/False, or Used to store a ynique value generated automatically by Access for h new record. ) n/Ofh (ae Object) Pictures, graphs, or other objects from another Windows-based application: Hyperlink) (a link address to a document or file on the Internet. ) CREATING A TABLE IN)ACCESS henever you create a new database, a default table - Tablel is automatically created. ju can add the fields for this table and rename it. Scanned with CamScannerThe steps to create a table are: 1. Select the Create tab and click Table from the Tables section. A new table with the name Table 1 is created, 2. The created table structure appears, as shown below in the design view. Here, you can design the structure of the table by defining the fields of the table. 3. Enter the required field names and select the appropriate data type. 4. The final table appears as shown: Scanned with CamScanner'yed in the lower half of the design view. It allows you to control some features of a field, such as its size and the values allowed in that field. For example, to allow decimal values in a field of Number data type, you must make its field size as ‘Single’ instead of the default ‘Long Integer’. The properties for a field having the Number data type are shown below: The Field Properties pane is displa General Lookup Field Size Longinteger oa New Values Increment ‘ "2 Format - Caption ; ; Indexed Yes (No Duplicates) _ Text Align General : Field Properties Pane Bs Id Size: It allows you to specify the maximum number of characters that can be entered he field, Scanned with CamScanneri id time are Format: It allows you to select the format in which text, numbers, and date ani displayed from the drop-down list. Decimal Places: It allows you to select how man y numbers can appear to the right of the decimal point. Input Mask: It provides a template with punctuation marks to make the data input easier. Caption: If the field has a difficult name, Caption text box. you can enter a more descriptive name in the Default value: It is used to enter the defa be edited later. Indexed: This property indicates whether the field has been indexed. Index helps to make sorting and searching through a field faster, Validation Rule: It allows you to establish a rule for Validation Text: It is used to s the validation rule. ult content for every record. This content can entering data in a field, Pecify the error message if someone enters data that violates MOTO aS a What owe Ua avo vices 2 [Inere are different views of » table namely Besign View and Datasheet Design View < Aut, In the Design View, you can define the fields of the. able while the Datasheet View is used to enter the records in the table The steps to open a table in the Design view are: 1. Select the table from the Tabl 2. Right-click on the table nai table in the Design View. les pane in the Home tab ime and select the Design View to open the Tebles pane Pe Scanned with CamScanner3. You can edit or change the field names or data types of fields in a table. w ye Table in Design View Datasheet View ‘Th the Datasheet view, you can enter the records in a tabie| The steps to enter records are: | 1. Select the table in which you wish to enter records from the Tables I pane. oe All Access Objects < 2. Click the View - Datasheet View option present in the Views section of the Home tab. The Datasheet View windows appears. 3. Enter the data value in each field. You can use the Tab key to move from one cell to another. Datasheet View of Table Scanned with CamScannerDELETING) RECORDS INVA TABLE! You can delete a record when it is not required. The steps to delete a record in a table are: 1, Open the table in Datasheet View and select the record/ records to be deleted. 2. Right-click and select the Delete Record option. im vl Qe Tp ow MU cmmey & oe ? 2 Fee be ) a ———— : . Aico Opec « SE) 3. Click Yes to delete the selected record/records, The selected record is deleted permanently. ié ] HO You Know? RENAMING) A TABLE : You can navigate through the records. of the table by using the arrow keys present at the bottom of the . is ith By default, in a database a table is saved wit Datasheet window: the name Tablet. However, you can rename it. Scanned with CamScannerThe steps to rename the table are: 1. Right-click on the table that you wish to rename from the Tables pane. 2. Click the Rename option from the drop-down menu. create 3. Type the new name and press the Enter key to confirm the name. 4. The table will be renamed. Scanned with CamScannerDELETING) A’ TABLE: just like the way you delete a record in a table, you can also delete an entire table if not needed. The steps to delete the table are: 1. Right-click on the table that you wish to delete from the Tables pane. 2. Click the Delete option to delete the selected table, A confirm deletion window appears to make sure if the user really wants to delete the table. he Dew All Access Objects® « P 3. Click Yes to delete the table from the database. SAVINGYAND)CLOSING A) DATABASE Once you have finished, you need to save your work and close the database. The steps to save and close the database are: 1. Select the File tab. The File ee ee Hom pes menu appears. ae — 2. Click Save to save the Diemer | oom database. If you want to save the database with a different name, click the Save Database As option from the File menu. Scanned with CamScannerro 4. A Save As window appears. Add a suitable file name in the File name textbox. Bivens 7 1 (B> Marc + Documents » © B Seach Documents Orgence > New folder e fanart tame . sys Boyes 1G cortom oftce Templates 1 deste nora Documents Sa | BYowsbser Downtnds in | > 8} Database? Petes Biter = tecatdax (ey Brena Peoune (EEE Seemnpe MeriotAecesOiubne 7 > Fidefolden Toot = 4. Click the Save option. 5. A database with the desired name i title bar, is saved and the database name appears on the Chm Database allows the users to add, access, change and delete data easily and effectively. A system that supports the organising of data is called DBMS, DBMS provides various techniques to check on the redundancy of data. Data consistency refers to managing data across different tables of a database. With DBMS, the data stored in one user can be shared among different users. €ss is a relational database management system. gives the user another way of looking at the data in > 3 & Scanned with CamScannereT SC ||| || 2 Tick (%) the correct answer, Conceptual Understanding, Which of the following software packages is suitable for database application? (a) Word (0) Excel (c) Access G (d) PowerPoint 2. Which of the database objects are used to store data in the form of rows and columns? (a) Tables 6 en (©) Query (@) Report 3. Which of the database objects are used to display data in a printable format from a table or a query? (2) Queries () Forms (©) Reports L @ Tables 4, What acts as a warehouse of data? (a) Files (b) Reports (©) Excel (@) Database v 5. Which term is also often used for columns? (a) Field (Row (©) Record (@) None of these Fill in the blanks. 1. The por! pane displays all of the objects contained in your database. 2. are statements that you can use to filter out relevant data according to your need from a table. 3. Data__ means duplication of data. 4 is a file that, when opened, creates a complete database application Scanned with CamScannerc Kk (¥) for right ones and (X) for wrong ones. 1 Data is one of the most important assets to any business. 2. A-system that supports the organizing of data is called DBMS. 3. DBMS provides various techniques to check on the redundancy of data, 4 Data consistency refers to managing data across different tables of a database. 5. The main components of a database are fields, tables and values. D. Short Answer Questions. 1. Define Database. 2. What are the uses of a database? 3. What are two ways of creating a Microsoft Access database? 4. What do you understand by data redundancy? E. Long Answer Questions. 1. State the advantages of working with database. 2. Write the steps to enter record in Access, 5. Define the following: (a) Report (b) Form (c) Table (@) Query Lab Time ‘Subject Enrichment, Perform the following tasks. Task 1: Create a database with the name ‘Customer’, Task 2: Create a table called ‘Customer _info' with the following fields: Cust_ID, FirstName, LastName, DOB, Task 3: Enter the following records into the tabl 159. _ Asif 03/03/2010 | 04/03/1995 | 0: 234 | Sneha | 435 | Amit eS i! Task 4: Save the table and close it, Also, close the database ‘Customer Scanned with CamScanner 2ESas