G8LP-4RDBMS- MS Access

Lesson Plan - 1 Computer Science RDBMS- MS Access Topic- Managing data using Sort and Filter Class: Period: Mode: Classroom/Lab Teacher: ____________________________________________________________________________________ Learning Support Assistant: ____________________________________________________________ S.M.A.R.T. Learning Objectives By the end of this session, students will be able to: 1. Sort data in MS Access alphabetically and numerically. 2. Apply different types of filters in MS Access. 3. Create a query using a single table. Resources • video https://youtu.be/_OdsZR_rL1U (4.45). • Use the eContent to show the animated demos of the lesson. Session Conduction Engage: Ask questions and discuss the previously taught topic in MS Access. Ask which one can store more spreadsheets or databases. Give them an example of storing student marks in a spreadsheet. Now if we have to arrange students’ names in ascending order what should we do. Then next we want to see only those students’ names who have scored more than 60 in computers. What tool we should use. Once they answer, tell them the sort and filter tools we have used in MS Excel can be used in MS Access. However, what makes a database powerful from a spreadsheet is the ability to pull information from one or more tables by defining a set of search conditions. This is known as building a query. Using a query is like asking a detailed question from your database.

Concept introduction: Tell them the importance of sorting and filtering in organizing data. Sort data means putting it in order. Filtering data hide unimportant data and focus only on the data we are interested in. Next use queries for handling data in a database. Most importantly queries can be designed using both single and multiple tables. We can also do the calculation using a query. For example, instead of storing age in a table, because every year that age has to be updated; instead, store the person's date of birth and then use a query to calculate the person's age. We can find the gross amount of a product sold by multiplying unit price with total quantity. Concept Demo/Explanation: Create a database storing students’ grade information in the final exam. Use the steps given in the book for sorting and filtering data in different ways. Create a query using query wizard and query design view. Emphasize how quickly a query can retrieve data from tables. Steps for MS Access 2016 and 2019 are the same. Concept Practice: Ask students to store details of their 5 friends (Name, Cities, Hobbies) in MS Access. Let them perform some tasks. • Cut the first record and paste it as the last record. • Copy the third record and paste it after the first record. • Sort the data based on the name. • Filter data who are having hobbies like music. • Find friends who live in a particular city using a query. Optional Activity: Students can watch the video https://youtu.be/_OdsZR_rL1U and discuss it with their teacher. Home Assignments 1. Revise the topic covered. 2. Practice the interactive exercises in Edusoft Smart App. 3. Solve any additional exercises on playground.edusoft.co.in. Guided Assignments Ask students to visit https://support.microsoft.com/en-us/office/access-video-training-a5ffb1ef- 4cc4-4d79-a862-e2dda6ef38e6 and find many resources under it which can be very useful. Evaluation After completing the lesson solve the exercises given in the book.

Lesson Plan - 2 Computer Science RDBMS- MS Access Topic-Relationships in MS Access Class: Period: Mode: Classroom/Lab Teacher: ____________________________________________________________________________________ Learning Support Assistant: ____________________________________________________________ S.M.A.R.T. Learning Objectives By the end of this session, students will be able to: 1. Use query on multiple tables 2. Apply referential integrity. 3. Understand what is a relationship. Resources • Video https://support.microsoft.com/en-us/office/video-create-table- relationships-access-basics-part-2-a93d9491-8724-4cd3-96df-ce504914692f (5.08) • Use the eContent to show the animated demos of the lesson. Session Conduction Engage: Give a quick recap of the previous topic with a question-answer session. Most importantly revise the relationship topic i.e., primary key and foreign key. Ask what is primary and foreign keys are used for. How to declare columns as a primary key? A foreign key can be the same as the primary key or different? Relate it with today’s topic by saying in Access, if we have to create a query on multiple tables then it’s quite a simple procedure to perform but users need to first create a relationship between the two tables.

Concept introduction: An example of using multiple queries in a table: there is a student database in which one table stores student information (ID, Name, Class, Address, Age, Phone Number), and the other table stores marks obtained by students in the final exam (ID, Total Marks, Grade). Both the table will have a primary key as student id which is the same in this scenario. Now let’s find out students who got A+ grades in class 8. It can be done by creating a relationship between two tables using a foreign key. A foreign key column in a table point to a column in another table's primary key column to cross- reference the two tables. Now in our example, in the mark obtained table student id is both primary key and foreign key. Now create a query in the query design view of MS Access 2016 after adding tables a thread will appear between two related columns showing the relationship. Stress on how MS Access 2016 provides not only a user-friendly interface for creating multiple queries but also is much faster in giving results. Queries are used for creating reports. We can provide security of data using referential integrity. Tell them what will referential integrity ensure in our example. If there is a student id that exists in the student table it must be present in the marks obtained table, if the student id is getting changed in the student’s table it should get changed in the mark’s table too. Give real-life examples of relationships • One-to-One- 1. One family lives in one house, and the house contains one family. 2. One person has one passport, and the passport can only be used by one person. 3. One student gets one book at a time. • One-to-Many- 1. One class has many students. 2. One newspaper has many readers. 3. One building with many people. 4. A bank customer has more than one account and that account will have the same customer. 5. One department with many teachers. Note that there is vice versa too which is known as many to one. Some examples are as follows:

1. Many readers reding one newspaper. 2. Many students in one class. 3. Many people live in one house. 4. Many teachers in one department. • Many to Many 1. One customer can order many products and the product can be ordered by customers. Like Colgate can be purchased by many customers same customers can purchase oral b too. 2. One teacher could teach many subjects, but one subject could also be taught by many teachers. 3. Many projects can be assigned to many students. Concept Demo/Explanation: Show how to use multiple tables for writing queries using the author-book example from the book. Show to create, edit and delete relationships. Show how to update foreign key values using referential integrity in author and book tables. If the author code is deleted from the book table it will not get deleted. Create some more tables to show relationships between them using one-to-one. one-to- many and many-to-one. Concept Practice: Give students some activities related to query using relationships. Optional Activity: Watch https://support.microsoft.com/en-us/office/video-create- table-relationships-access-basics-part-2-a93d9491-8724-4cd3-96df-ce504914692f in class and discuss further. Practical Application: Ask students to complete the lab activities. Home Assignments 1. Revise the topic covered. 2. Practice the interactive exercises in Edusoft Smart App. 3. Solve any additional exercises on playground.edusoft.co.in Guided Assignment Ask students to visit https://edu.gcfglobal.org/en/access/designing-a-multitable- query/1/ and have a group discussion in class with their findings.

Evaluation After completing the lesson solve the exercises given in the exercise.