Class Schedule
22 AUG | intro
27 AUG | clients
29 AUG | servers
05 Sep | networks
10 Sep | basics lab
12 Sep | structural layer
17 Sep | presentational layer
19 Sep | working with layers
24 Sep | behavior layer
26 Sep | images & design
01 Oct | website lab
03 Oct | object layers
08 Oct | graphics
10 Oct | document markup lab
15 Oct | spreadsheets
17 Oct | formulas & functions
22 Oct | thoughts about data display
18 Oct | Fall Break
24 Oct | database tools
29 Oct | spreadsheets lab
31 Oct | relational databases
05 Nov | tables |
05.01 |
next session
07 Nov | relationships
12 Nov | input & output
14 Nov | SQL
19 Nov | complex queries
26 Nov | databases lab
21 Nov | Thanksgiving
28 Nov | presentation design
03 Dec | presentation delivery
05 Dec | presentation lab
12 Dec | 0800-1100 | final in class presentation
This work
is licensed under a
Creative Commons Attribution-NonCommercial-ShareAlike 3.0 Unported License.
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.
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
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.
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.
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
In the import process, you will be asked if you want to allow the Wizard to create a Primary Key for you
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.
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 |
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 |
field name | things to think about |
tblAuthor_ID | this will be the Primary Key |
tblAuthor_Name | last name and first name |
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 |
Concentrate on setting up the table design correctly. You will not be adding live data to this table until a subsequent session and task.
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
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