Handling Databases PDF - The City School 2021-2022

# Handling Databases ## The City School 2021-2022 ## Handling Databases ### Student Learning Outcomes After going through this chapter students will be able to: 1. Understand the concept of databases. 2. Familiarize with the MS Access's user interface. 3. Understand the concept of data types a...

# Handling Databases ## The City School 2021-2022 ## Handling Databases ### Student Learning Outcomes After going through this chapter students will be able to: 1. Understand the concept of databases. 2. Familiarize with the MS Access's user interface. 3. Understand the concept of data types and data fields. 4. Work with creating and organizing tables in MS Access 5. Understand the purpose of primary and foreign key by creating relationship between tables. 6. Create queries to retrieve specific data from the tables. 7. Create, design and enter data in the tables through forms 8. Use mathematical operators in queries. 9. Create reports using the wizard. ### ISTE Student Standard Coverage | Standard | Description | |---|---| | Empowered Learner | 1a 1d | | Computational Thinkiner | 5a 5b 5c 5d | | Creative Communicator | 6d | ## 5.1. Overview A structured set of data held in a computer, especially one that is accessible in various ways is called Database. A database is usually controlled by a database management system (DBMS). Together, the data and the DBMS, along with the applications that are associated with them, are referred to as a database system, often shortened to Database. The common language among electronic database is SQL. It works at the backend of most user interfaces in any application. MS Access is one of the softwares which use the same structure of SQL. Relational Database Management System (RDBMS). ## 5.2. Getting Started with MS Access Microsoft Access is a database management system (DBMS) from Microsoft that combines the relational Microsoft Jet Database Engine with a graphical user interface and software-development tools. Access is an easy-to-use tool for creating business applications either from templates or from scratch. With its rich and intuitive design tools, Access can help in creating appealing and highly functional applications in a minimal amount of time. ## 5.3. Datatypes in MS Access Every field in a table has properties. Properties define the field's characteristics and behaviour. The most important property for a field is its data type. A field's data type determines what kind of data it can store. MS Access supports different types of data, each with a specific purpose. Here are some of the most common data types you will find used in a typical Microsoft Access database. | Types of Data | Description | |---|---| | Short Text | Text or combinations of text and numbers, including numbers that do not require calculating (e.g. phone numbers). | | Long Text | Lengthy text or combinations of text and numbers. | | Number | Numeric data used in mathematical calculations. | | Date/Time | Date and time values for the years 100 through 9999. | | Currency | Currency values and numeric data used in mathematical calculations involving data with one to four decimal places. | | AutoNumber | A unique sequential (incremented by 1) number or random number assigned by Microsoft Access whenever a new record is added to a table. | | Yes/No | Yes and No values and fields that contain only one of two values (Yes/No, True/False, or On/Off). | ## 5.4. Creating Database in MS Access Follow the steps below to create your first database in MS Access: 1. To create a database from a template, we first need to open MS Access. 2. Select Blank Database. Enter the name and click the Create button. 3. Access will create a new blank database and will open up the table which is also completely blank. 4. After selecting a template related to your requirements, enter a name in the File name field and you can also specify another location for your file if you want and save it with your relevant name. ## 5.5. Understanding Tables in MS Access All tables are composed of horizontal rows and vertical columns, with small rectangles called cells in the places where rows and columns intersect. In Access, rows and columns are referred to as records and fields respectively. A field is a way of organizing information by type. Whereas, A record is one unit of information. Every cell on a given row is part of that row's record. Each record has its own ID number. Within a table, each ID number is unique to its record and refers to all of the information within that record. The ID number for a record cannot be changed. ## 5.6. Understanding Tables in MS Access MS Access uses table relationships to join tables when you need to use them in a database object. There are several reasons why you should create table relationships before you create other database objects, such as forms, queries and reports. ## 5.7. Constraints in MS Access In a database when we define any field as a Primary Key (PK) there are two rules or constraints applied to that field automatically. * **Not Null:** This constraint enforces that this field cannot be left unused. In Database terminology, NOT NULL is not considered as empty or zero, it just defined as the data has not been entered yet. * **Unique:** This constraint enforces that the data entered in this field cannot be repeated or all the values entered should be unique to each other ## 5.8. Creating Relationships in MS Access For creating relationships in our database, we need another table and will name it tblHRData using Table Design as shown in the image. Make sure to select the relevant data type according to fields. Now we will work with One-to-One Relationship in MS Access. This relationship is used to relate one record from one table to one and only one record in another table. 1. Go to the Database Tools tab, click on the Relationships option. 2. Select tblEmployee and tblHRData holding the shift key and then click on the Add button to add them to our view and then close the Show Table dialogue box. 3. To create a relationship between these two tables, use the mouse, and click and hold the EmployeelD field from tblEmployee and drag and drop that field on the field we want to relate by hovering the mouse right over EmployeeID from tblHRData. When you release your mouse button, Access will then open the Edit Relationships window. 4. This window relates EmployeeID of tblEmployee to EmployeeID of tblHRData. Now click on the Create button and now these two tables are related. 5. The relationship is now saved automatically and there's no need to click on the Save button. Now that we have the most basic of relationships created, let us now go to the table side to see what has happened with this relationship. ## 5.9. Queries in MS Access Database tables can hold a lot of records, in some cases, millions or billions of them. Queries allow you to pull information from one or more tables based on a set of search conditions that you define. A query is a special "question" you apply to the database to find specific data and get the information you want. When planning a query that uses more than one table, you should go through these four steps: 1. **Pinpoint:** Knowing precisely what question you want to answer is essential to building a useful query. 2. **Identify:** Know every type of query result you want included in your query results. 3. **Locate:** Know the fields that you need to include in your query and the tables they are contained in. 4. **Determine:** Know the criteria the information in each field needs to meet and the information you need to display. ## Creating Queries in MS Access * **Example 1:** For this task, let's continue working with the Employee table we have used before. Let us suppose that we need the data of our employees with their first name, last name and their salaries which are higher than $3500. - **Pinpoint:** Employees who earn more than $3500 - **Identify:** FirstName, LastName, StartSalary - **Locate:** tblEmployee (EmployeeID, FirstName, LastName) tblHRData (StartSalary) - **Determine:** StartSalary>$3500 1. Click on the Query Design option from the create tab. 2. Select and add tbleEmployee and tblHRData. 3. For the first column select the employee's first name. In the second column select employee's last name. In the third column select StartSalary. We can get the data from both tables accurately as both table's EmployeelD fields are related to each other using PK and FK. 4. After selecting the fields for a query, you can see the name of fields and other options for Sort and setting a criterion. Select the Ascending option from the first column (FirstName). 5. In the third column enter the criteria for salaries as preferred. In the example, we have entered >3500. Which means that employees who have salaries greater than 3500 will be listed in ascending order after executing the queries. 6. Save the query by using the right click on the query tab and save it with any preferred name. In the example, we have saved the query with the name of Salaries Greater than 3500. Close the tab and run the query from the objects panel. * **Example 2:** Assume that we need the list of employees who have joined after the year 2018. - **Pinpoint:** Employees who joined after 2018 - **Identify:** FirstName, LastName, HireDate - **Locate:** tblEmployee (EmployeeID, FirstName, LastName) tblHRData (HireDate) - **Determine:** HireDate>12/30/2018 1. Add the preferred columns along with the field of HireDate from tblHRData. 2. And enter the criteria as >12/30/2018 and press enter. 3. Save the query with an appropriate name. 4. You will see list of employees who have joined after 2018.