Build Your Own Database

Project Overview: Learn about what a database is and how they’re used in the real world. Design and build your own database for a school library using Google Sheets. You’ll learn concepts such as tables, rows, columns, unique identifiers (unique ID), shared IDs, etc. 

Materials: 

• A computer and a google account!

Instructions: 

1. Go to sheets.google.com and click “Blank spreadsheet” in the top left of the screen. Name the sheet something like “My First Database” or “Library Database”. 

2. Within a spreadsheet, you can have multiple sheets. Sheets are organized as tabs in the bottom. Rename the first sheet to “Books”. Add the following column headers: BookID, Title, Author, Genre, Page Count, Checked Out. 

3. Populate this sheet with at least 10 books. 

   1. BookID is our unique identifier. This means each book in our database has a different ID. (e.g. BK0001, BK0002, BK0003, …)

   2. The Checked Out column should be of value True or False (Or Yes or No). 

4. Add a new sheet and name it “Students”. Add the following column headers: StudentID, StudentName, StudentEmail, Grade, HomeroomTeacher. 

5. Populate this sheet with at least 10 students. 

   1. StudentID is the unique identifier. This means each student in our database has a different ID. (e.g. 475010, 488130, 499660, etc.)

6. Add a new sheet and name it “Book Checkouts”. Add the following column headers: CheckoutID, StudentID, StudentName, BookID, Title, Date, DueDate, Returned. 

7. Populate this sheet with at least 5 checkouts. Refer to the other two sheets to populate StudentID and BookID. Leave StudentName and Title blank for now. 

8. In the first cell below the StudentName column of the Book Checkouts table, enter the following formula: =VLOOKUP(B2, Students!A:B, 2, FALSE). Press Enter, and the Student names should be populated automatically. To do the same for the remaining rows, just drag the small blue square at the bottom right corner down. 

   1. What is this formula doing? It’s looking up the Student ID and finding it in the Students sheet, and returning the corresponding name. 

9. In the first cell below the Title column of the Book Checkouts table, enter the following formula: =VLOOKUP(C2, Books!A:B, 2, FALSE). Press Enter, and the Titles will populate automatically. To do the same for the remaining rows, just drag the small blue square at the bottom right corner down. 

   1. What is this formula doing? It’s looking up the Book ID and finding it in the Books sheet, and returning the corresponding Title.