Transcript DB Table Development

IE 423 – Design of Decision
Support Systems
Database
development –
Building Tables
http://crimethinc.com/blog/wp-content/uploads/2007/02/table5big.jpg
Important Dates to Remember
St. Patrick’s Day – March 17th
By now you should have
Read Chapters 1,2,3, 4, 5 and 6 in Pol
and Ahuja
Also, read chapter 7 in Pol and Ahuja
DataBase Development
We are going to skip an important topic
 Normalization
…but, will get to this topic very
soon
DataBase Development
Remember that our earlier modeling efforts
resulted in the creation of schemas
In our modeling we defined entities, relations
and relationships
In DB development we are interested in




Defining the entities = Tables
…and their attributes = fields
…and the relationships among tables
Later we will add data to create instances =
records
DataBase Development
In its native form a table is a data grid
with –




Columns (fields)
Rows (records)
Fields have properties
Records represent instances of the entity
DataBase Development
Start Access


Either open an
existing DB,
or…
Create a new
DB
DataBase Development
Create a new DB


Select Blank
database
Then define the
name and location
for the DB file
DataBase Development
Four ways to create a table




Create
Create
Create
Create
data
a
a
a
a
table
table
table
table
in Datasheet view
in Design view
with the Table Wizard
while importing external
DataBase Development
Create a table in Datasheet view
DataBase Development
Create a new DB



Next you should
see something like
this….
Make the Table
Object is selected,
then
Select Create table
by entering data
DataBase Development
Create a table in
Datasheet view




Enter the field
names at the top of
the columns
Click close (X) when
finished defining the
fields
You will asked if you
want to save the
table definition…
If you click yes you
will be prompted for
a name for the table
DataBase Development
Create a table in
Datasheet view



… you will also be
told that the table
has no primary
key,…
And you will be
asked if you want
Access to create one
for you
This is important –
 but click No for
now
DataBase Development
Create a table
in Datasheet
view

Note: we have
a new Table
object
(People)
DataBase Development
Create a table in Design view



Creating a table in datasheet view is easy,
but…
It leaves a lot not done
For example, each field has a set of
properties, and we have not defined them
DataBase Development
Create a table in
Design view



Lets create a table
to store data about
houses
Click create table in
Design view
…and you will see
something like this
DataBase Development
Create a table in
Design view

Note our field
definition panel
(top) needs
three things –
 Field Name
 Data Type
 Field Description
DataBase Development
Create a table in Design view

Naming rules
 Max 64 Characters long
 Any combination of Letters,numbers spaces, except




Control characters (ASCII 0-31)
Period(.), Exclamation Point (!), Grave (`), Brackets ([])
Leading spaces
Quote marks

Remember CamelBack notation

Leszynski Naming conventions

( from:http://www.acc-technology.com/namconv.htm )






Tables
Queries
Forms
Reports
Macros
Modules
tblXxxxXxxx
qryXxxxXxxx
frmXxxxXxxx
rptXxxxXxxx
mcrXxxxXxxx
basXxxxXxxx
DataBase Development
Create a table in Design view

Field Types
 Text – max 255 characters (default)
 Number – storing numeric data – several specific
numeric types (field size*) – 1,2,4,or 8 max 16
bytes

Long Integer(4), Integer(2), Byte(1), Single(4),
Double(8), ….
 Autonumber – automatically adds a unique number
to record


Incremental
Random
DataBase Development
Create a table in Design view

Field Types
 Date/Time – to store date and time values
 Memo – similar to Text except allows very long
lengths (65,535 max)
 Yes/No – Boolean values
 Currency – values represent money
 OLE object
 Hyperlink – Internet address
 Lookup Wizard – we’ll come back to this
DataBase Development
Create a table in Design view



So, let’s build a table for Houses
By the way, try to make it a good design!
House Survey
?
House ID Number:_______
Address Number:_______________
Apt. Number:___________________________
Street Name:________________________District:_____________________________
City:____________________________ State: _____ ZipCode:___________________
House Type:________________________ Approximate Sqft:_________________
Number of Rooms:___________________ Number of Bedrooms:_______________
Number of Baths:____________________ Yard in Sqft:_________________
Garage: Yes:___ No:___ Basement: Yes:__ No:___ Number of Floors:________
DataBase Development
And don’t forget –
Define the table’s Primary Key
DataBase Development
MS Access Table Limits
Property
Limit
DB Size
2 Gigabytes
Table name length
64 characters
Field name length
64 characters
Fields in a table
255
Table size
2 Gigabytes
Characters in a record
4000
Characters in Field description 255
Characters in Text field
255
Characters in memo field
65,535
DataBase Development
MS Access Reference Card


From
http://www.wcu.edu/it/edtech/handouts/access_2003_quick_reference.pdf
DataBase Development
MS Acess Help and Tutorial from Microsoft

http://office.microsoft.com/en-us/access/CH062526191033.aspx