CDT - Building Database Tables Lesson

Building Database Tables Lesson

Introduction:

Tables are the foundation of relational databases and are used to store information. Tables can be customized to include multiple data types and default values.

There are several reasons for creating relational databases (or linked databases), instead of just creating one huge database. We don’t want to repeat the same data on every

  

A Quick Summary!

A quick summary of the previous lesson, as databases are so different from what you have learned previously!

Databases store data in tables. Instead of rows and columns, database tables have records and fields. Each record relates to a unique person, object, or event. For example, each record on the Customers table would store information about a unique customer.

Fields determine what kind of information is kept about each record. For example, an individual record on the Customers table may have fields for a customer’s first name, last name, address, and phone number(s).

 

Creating a Relational Database

You created your first database in a previous lesson, consisting of just one table (worksheet), known as a basic flat-file spreadsheet database. It serves as our master table.

Now we will create two additional tables to create a relational database.

1. Open the master database we created in a previous lesson. (linked in Resources under the Book Icon above)
2. Add two additional worksheets by clicking on the + (plus sign) on the navigation bar.
3. Name one worksheet Violations and the other Addresses.
4. Do you remember the key field we used for our master database? Yes, License Numbers! All linked databases need to contain this field, as that’s our unique identifier.
5. Copy column A (our key field, License Number) to column A in both Violations and Addresses worksheets.
6. Add additional fields and data, as shown in the Traffic Violations Database Handout. Some fields can be copied from the Master table (Address, City, State, and Zip Code).
7. Adjust the column widths, if necessary.
8. Select all data in Violations and Addresses worksheets and format as table.
9. Name each table using Table Name option under Table Tools/Design; Violations table should be named VIOLATIONS and Addresses table should be named ADDRESSES.

 

You can quickly identify any redundant data. Be careful with deleting, though. Always have a backup file, just in case there is a problem.

• Under Table Tools, click Remove Duplicates.
• Make sure Select All option is highlighted.
• Click OK.

 

 

  

Creating a Database and Building Tables

Create a database (workbook) and build two tables (worksheets, be sure to format data as table and name tables; also, check for duplicates and then save) using the following information:

• Database Name: Bay View Middle School
• Table 1: Students (use Student ID, FirstName, LastName, Address, City, State, Zip Code fields)
• Table 2: StudentPhones (use Student ID, PhoneType, Phone Number fields)
• Records – use the table below:

D	First Name	Last Name	Address	City	State	Zip Code	Phone Type	Phone Number
345891	Sarah	Wilkes	123 Green Street	Summerville	GA	12345	Cell	770-234-1098
568123	Jose	Rivera	420 Berry Road	Summerville	GA	12345	Home	770-207-5678

Check your work by reviewing the demo video below.

  

 

  IMAGES CREATED BY GAVS