home & schedule | class blog | syllabus | contact | grades

Create four tables from imported data.
Create one table using design view.
These do not have to be turned in, but they will be needed during the subsequent relationships session.

Start building your tables

This is the start of your Task 05. Start to create these tables now and we will use them during the next class when we add relationships to them.

When you are ready, start to design the tables you want to use in your database

• you will have specific guidance about what these tables should look like so start to design them, concentrating on design and formats
• do not spend too much time populating the tables with data at this time though you may wish to enter some sample data in table fields to see if the formatting you have selected is appropriate for the data you wish to use

Import data

Start by importing the data in these worksheets into your database. Use the import tool to allow them to create the first four tables for you.

Using the wizard, some visuals

Tables:

you will have at least these four tables in your database. You can use the examples demonstrated today as models for how to modify the four imported tables.

Fields:

if the import tool doesn't do it for you, ensure that the fields in the worksheets are in your tables and that the field properties are correct

• if the import tool adds data fields to your tables that are not included in the list below, you may use them or delete them
• but be sure to include at least these data fields, though you may desire or need additional fields
• consider how each of the fields should be described in the design view

Primary Key:

In the import process, you will be asked if you want to allow the Wizard to create a Primary Key for you

• say YES and allow the wizard to add an autonumber field for the primary key.
• it should result in a field size Long Integer
  • for the event that you may have to join a Number field to an AutoNumber field in a many-to-one relationship
  • in such cases, the Number field size must be Long Integer in both the Primary and Foreign Key fields

Tables to create

The import tool will import Excel worksheets and then create tables with

fields	based on Excel column headings
records	based on Excel rows

but your finished tables will require you to create additional fields in the tables after the import process.

Book Table

should include the following fields,
but you will have to pay attention to the field properties after the import phase

field name	things to think about
tblBook_ID	this will be the Primary Key
tblBook_Title	with appropriate data type
tblBook_Date	the publication or copyright date
	be careful about the field property - should it be number, text, or date/time?
tblBook_Shelf Location	its Library of Congress number
	be careful about the field property - is it a number?
tblBook_Language	with appropriate data type
tblBook_Topic	with appropriate data type
tblBook_RetailPrice	with appropriate data type
tblBook_Copies	with appropriate data type
tblBook_tblPublisher_ID	this will be a Foreign Key
	this will link this table to others
	be sure to make its field size Long Integer

Publisher Table

should include the following fields,
whether in the import phase or by you creating them after the import phase

field name	things to think about
tblPublisher_ID	this will be the Primary Key
tblPublisher_Name	with appropriate data type
tblPublisher_City	with appropriate data type
tblPublisher_State	two letter abbreviation
tblPublisher_ZipCode	with appropriate data type and input mask
	if there is no data for this field, you may leave it blank or make it up for new publishers you add to the table
tblPublisher_Phone	with appropriate data type and input mask
	if there is no data for this field, you may leave it blank or make it up for new publishers you add to the table
tblPublisher_Country	two letter abbreviation

Author Table

should include the following fields,
but you will have to pay attention to the field properties after the import phase

field name	things to think about
tblAuthor_ID	this will be the Primary Key
tblAuthor_Name	last name and first name

Connection Table

should include the following fields,
but you will have to pay attention to the field properties after the import phase

field name	things to think about
tblConnection_ID	this will be the Primary Key
tblConnection_tblBook_ID	this will link this table to the book table
	be sure to make its field size Long Integer
tblConnection_tblAuthor_ID	this will link this table to the book table
	be sure to make its field size Long Integer

this table will become important when we get to relationships

One more table

You have one table that you will have to create from scratch using your choice of table creation tools

Concentrate on setting up the table design correctly. You will not be adding live data to this table until a subsequent session and task.

Order Table

field name	things to think about
tblOrder_ID	this will be the Primary Key
	you will have to create a primary key for this table and you might as well allow the wizard to use an autonumber for this purpose
tblOrder_BookTitle	This is a deliberately deceptive field
	Ask yourself what other primary key field can identify the title of a book
	this will be a Foreign Key
	a copy of a Primary Key from another table
	you must identify the title of the book you will order
	the data in this field in the Order Table will come from a field in another table, but we'll discuss that when we relate tables
	you will probably want to rename this field as you decide how to relate tblOrder to another table
tblOrder_NumberOfBooksOrdered	with appropriate data type
tblOrder_DateOfOrder	make the current date the default value for the date the book was ordered
tblOrder_DateOrderReceived	with appropriate data type
tblOrder_DiscountPrice	with appropriate data type
	add in your description of this field that it will always be no more than 80% of the retail price of a book
	this field is included to allow us to do some calculations in queries

we have not yet at this time decided what field will link the Order Table to another table;
that will come during the discussion of relationships

Don't forget - every table needs a primary key

In all these tables, you may or may not add data fields at a later date as you construct relationships between the tables. For today, however, concentrate on creating the tables.

Consider using a standard naming structure for your tables and fields within the tables.

tables | 05.01 | next session