The hotel system
In this booklet we illustrate MS-Access by means of a
system for supporting a hotel reception. The system is
used as the main example in User Interface Design - a
Software Engineering Perspective, by Soren Lauesen.
If you know the book, skip this section and go straight
to Chapter 2.
Screens
The hotel system consists of the screens shown in Figure
1A.
Find guest. The Find guest screen allows the receptionist
to find a guest or a booking in the database. The
receptionist may enter part of the guest name and click
the Find guest button. The system then updates the
lower part of the screen to show the guests or bookings
that match. The receptionist may also find the guest by
his phone number, room number, or stay number (also
called booking number).
The receptionist can select the guest from the list and
click the buttons to see details of the booking or create
a new booking for the guest.
Room Selection. The Room Selection screen gives an
overview of available rooms in a certain period. Availability
is shown as IN when the room is occupied,
BOO when it is booked, etc. The receptionist may
specify the period of interest and the type of room, then
click the Find room button. The system updates the table
at the bottom of the screen to show the rooms of
interest. The receptionist can then choose a room and
book it for the guest – or check a guest into the room.
Stay. The Stay screen shows all the details of a booking,
for instance the guest and his address, the rooms
he has booked and the prices. When the guest is
checked in, the Stay screen also shows breakfast and
other services he has received. The system shows these
details on the Services tab. Here the receptionist can
record services that the guest has received. The system
uses the term Stay to mean a booking or a guest who
has checked in.
Breakfast list. The Breakfast screen shows the breakfast
servings for a specific date. It handles just two
kinds of breakfast: self-service breakfast in the restaurant
(buffet) and breakfast served in the room. The
waiter in the restaurant has a paper copy of the list and
records the servings here. Later the receptionist enters
the data through the Breakfast screen.
Service list. The Service list shows the price for each
kind of service. Hotel management uses this list to
change service prices or add new kinds of service.
Database
The system uses a database with several tables. They
are shown as an E/R data model on Figure 1B.
tblGuest has a record for each guest with his address
and phone number.
tblStay has a record for each stay (booking or checked
in) with a booking number (stay number) and the pay
method.
tblRoom has a record for each room in the hotel.
tblRoomState has a record for each date where a room
is occupied. It connects to the room occupied and the
stay that occupies it. If the room is occupied for repair,
it doesn’t connect to a stay.
tblRoomType has a record for each type of room (room
class) with a short description of the room type, the
number of beds, and the prices.
tblService has a record for each type of service with its
name and price per unit.
tblServiceReceived has a record for each delivery of
service to a guest. It connects to the type of service and
to the stay where the service is charged (there is an invoice
for each stay).
Fig 1A. Hotel system screens
2. Creating a database
Highlights
• Transform the data model to a database in MSAccess.
• Use lookup-fields to enter foreign keys and enumeration
types.
In this chapter you learn how to realize a data model as
a relational database in Microsoft Access. We assume
that you know about data modeling, tables, attributes,
and foreign keys as explained in User Interface Design.
The description below is based on Access 2000, but
there are only small differences from Access 97 and
Access 2003. We will mention the more important
ones.
In this and the following chapters we will use the hotel
system as an example, and you will construct several
parts of the system. However, the purpose is not to
construct the hotel system, but to show how MSAccess
works. This knowledge will enable you to construct
a functional version of your own system - for instance
the one you have designed when reading User
Interface Design.
2.1 Create a database in Access
In Microsoft Access a database consists of one single
file. The file contains all the tables of the database, the
relationships (the crow's feet), queries (computed tables),
forms (user windows), and many other things.
As a systems developer you will design tables and user
windows. As a user you will enter data into the tables
(usually through user windows) and get data out of the
tables, for instance through the same windows or
through printed reports.
In Access it is very easy to switch between the developer
role and the user role. As a developer you will
typically design some tables, then switch to the user
role to enter data into them, then switch back to the developer
role to change the design, design more tables,
etc. Access can to a large extent restructure the data
that already is in the database so that it matches the
new table design.
Warning: Make sure you follow the steps below
closely. Don't skip any of the numbered steps. The
result might be that you get stuck later in the text.
Create the database
1. Locate the Access program. Depending on the way
the system is set up, you may find it under Programs
-> Microsoft Access or Programs -> Microsoft
Office -> Microsoft Access.
2. In Access 97 and 2000: Open Access and ask for a
"blank" database.
In Access 2003: Open Access and click the New
icon (under the File menu). Then click Blank database
in the help area to the far right.
3. Access now asks where to store the new database.
Select the folder you want and give the database
the name hotel (or hotel.mdb).
The screen now shows the database window. It should
look like Figure 2.1A. (In Access 97 it looks slightly
different). We have selected the Tables tab, but there
are no tables or other things in the database as yet.
However, you see three icons that can create tables for
you. When you have created a table, it will appear in
the table window and you can then Open it and enter
data into it, or you can Design it, i.e. change the definition
of it. (In Access 97 the database window looks
like a traditional tab form. There are no create-icons,
but function buttons for the same purpose.)
Define a table
4. Double click on Create table in Design view.
Now you get a window as shown on Figure 2.1B. Here
you define the fields (attributes) of the table. The list of
fields runs downwards with one line per field. Initially
there are only empty lines. The table hasn't got a name
yet. Access asks for the name when you close the window.
The figure shows the finished guest table. You see the
field names to the left. In the middle column is the type
of the field - Data Type. The figure shows all the possible
types as a combo box. The most important data
types are Text, Number, Date/Time, and AutoNumber.
An AutoNumber is a counter that Access increases for
each new record, so that it serves as a unique key. The
value is a Long Integer (32-bit integer). We explain
more about data types in the next section.
5. Fill in all the field lines according to the attributes
in the guest table (see the figure). All the fields are
of type Text, except the guestID which is of type
AutoNumber.
Note that although we say phone number and passport
number, these fields are texts because the "numbers"
contain parentheses, dashes and maybe letters.
When you have chosen a data type, you can choose a
number of other field properties. They are in the lower
part of the window. On the figure you can see that the
name field is a text field with space for 50 characters.
You can also see that the user doesn't have to enter
anything in the name field (Required=No). You should
change this to Yes since it doesn't make sense to have a
guest without a name.
Try to use Access's help to find more information about
the data types and their properties. For instance, put the
cursor in the Data Type of a field and click F1. Or
point at one of the properties and click F1.
Lookup Wizard is not a field type. If you select Lookup
Wizard, it makes the field into a combo box where the
user can select a value instead of typing it into the
field. We will look closer at Lookup in section 2.4.
Key fields
Often you have to define a key field so that other tables
can refer to this one. In our case, guestID must be the
key field:
6. Right-click somewhere in the guestID line. Then
select Primary Key. Access now shows that the
field is the key.
You can remove the key property again by once more
selecting Primary Key. If the key consists of more than
one field, you first select all the fields by clicking on
their left-hand marker with Ctrl down. Then select
Primary Key by right-clicking inside one of the field
lines.
7. Close the window. Access asks you for the name
of the table. Call it tblGuest. (The prefix tbl will
help you remember that it is a table. As the system
grows, there will be guest windows, guest buttons
and many other things. Without discipline on your
part, it becomes a mess.)
If you have not defined a primary key, Access will
warn you and suggest that it makes one for you. Don't
let it - do it yourself. Or at least check what Access
makes in its excessive helpfulness.
Enter data
After these efforts, it is time to record some guests.
Fortunately it is easy:
8. Select the guest table in the database window.
Click Open or just use Enter.
Now the system shows the table in user mode (Datasheet
view) so that you can enter guest data.
9. Enter the guests shown on Figure 2.1C. You add a
new guest in the empty line of the table - the one
marked with a star. Notice that as soon you start
entering something, the record indicator changes to
a pencil and a new star line appears. The pencil
shows that you are editing the record, and the
record you see is not yet in the database.
On Figure 2.1C we originally entered a guest that got
guestID 4, later deleted this guest. Access will never
reuse number 4 for a guest.
Close and reopen the database
To feel confident with Access, it is a good idea to close
and open the database now.
10. Close the large Access window. (Not the small
database window inside the Access window.)
Notice that Access doesn't ask whether you want to
save changes. Access saves them all along, for instance
when you define a table or when you enter a record in
the table.
11. Find your database file (hotel.mdb) in the file folders.
Use Enter or double click to open it.
Access 2003 is very security concerned and asks you
several questions when you open the file. The dialog
may vary from one installation to another, but is
something like this:
2.2 Create more tables
You should now create the remaining tables for the
hotel. The data model on Figure 2.2 shows the tables
we will use. To simplify your job, we have shown all
the keys, including the foreign keys and the artificial
keys.
1. Close the guest table.
2. Create all the remaining tables in the same way as
you created the guest table (from the Tables tab
use Create table in Design view - or click New).
Make sure you define all the fields. Otherwise you will
get stuck when later constructing the user interface.
Here are a few notes about the various tables:
tblStay:
stayID is the primary key of tblStay. Make it an AutoNumber.
guestID is a foreign key that refers to the AutoNumber
in tblGuest. The foreign key must have a matching
data type - a long integer. Choose Data Type =
Number and Field Size = Long Integer. Warning:
Don't make the foreign key an AutoNumber. This
would cause Access to fill in the foreign key fields
automatically, and you cannot change the numbers
so that they point to the primary keys in the guest
table.
paymethod is an enumeration type. Make it an integer
(a 16-bit integer, not a long integer). Choose Data
Type = Number and Field Size= Integer. We will
use the value 1 to denote Cash, the value 2 to denote
Visa, etc. We will look closer at this in section
2.4.
state must also be an enumeration type. Make it an integer.
Here the value 1 will denote booked, 2 in, etc.
tblRoomType:
Contains one record for each type of room, for instance
one for double rooms, one for single rooms, etc. (In the
book User Interface Design, we added this table late in
the design process to illustrate the normalization concept.)
roomType is an artificial key. An AutoNumber is okay.
description is a short text, for instance "double room,
bath".
bedCount is the number of beds in the room, including
temporary beds.
price1 and price2 are the standard price and a possible
discount price. The price should be a decimal number.
Choose Data Type=Number, Field Size= Single,
Decimal Places =2.
tblRoom:
roomID is a natural key - the number on the door. So
don't use an AutoNumber. Use an integer.
roomType is a foreign key that refers to tblRoomType.
(You should by know how to deal with it.)
tblRoomState:
stayID and roomID are foreign keys. Ensure their types
match what they refer to. Notice that roomID refers
to a natural key, not to an AutoNumber.
date should be a Date/Time field with Format = Short
Date.
personCount is the number of persons staying in the
room. An integer should suffice.
state is similar to state for tblStay, although the values
are slightly different.
The key consists of two fields: roomID and date. It is a
bit tricky to specify this: select both fields by
clicking on the left-hand marker (hold down Ctrl
while selecting the second field). Then right-click
somewhere on the text inside the line.
Optional tables
The following two tables are needed for the full system.
However, you don't need to create them in order
to follow the tutorial.
tblServiceType:
serviceID is an artificial key. Should be an AutoNumber.
name and price should be obvious. The price should be
a decimal number. Choose Data Type=Number,
Field Size= Single, Decimal Places =2.
tblServiceReceived:
stayID and serviceID are foreign keys that refer to
AutoNumbers. The foreign keys must thus be long
integers.
roomID is an optional reference to a room. An integer
should suffice. (This reference is needed when a
waiter records a service for a specific room and the
guest has more than one room.)
date should be a Date/Time field. Choose Format =
Short Date.
quantity is the number of items the guest has got - an
integer should suffice.
Data is stored in the computer according to its type.
Here is a description of the most important types in the
data base. Visual Basic deals with almost the same
types (see section 6.2 and the reference card under
Declarations).
Text. The field can contain any characters. The Field
Size property defines the maximum number of characters.
The maximum cannot be above 255 characters.
Memo. Like a text field, but the maximum number of
characters is 65,535. Access takes more time to process
a memo field, so use text fields if adequate.
Number. The field can contain a number. The Field
Size property defines what kind of number:
Integer. A small integer. It must be in the range -
32,768 to +32,767 (a 16-bit integer).
• Long Integer. It must be in the range from around
-2,140 million to +2,140 million (a 32-bit integer).
• Single. A decimal number in the range from
-3.4*1038 to +3.4*1038 with an accuracy of 6 or 7
significant digits (a 32-bit floating point number).
• Double. A decimal number in the range from
-1.8*10308 to +1.8*10308 with 14 significant digits
(a 64-bit floating point number).
• Decimal. A very long integer with a decimal point
placed somewhere. Intended for monetary calculations
where rounding must be strictly controlled.
In the book we use Single or Double instead.
Numbers can be shown in many ways depending on the
format property of the field. You may for instance
show them with a fixed number of decimals, with a
currency symbol, etc.
Some formats show data in a way that depends on the
regional settings of the computer. If you for instance
specify the format of a number as Currency, the
number will show with a $ on a US computer and with
a £ on a British computer.
Date/Time. The field gives a point in time. In the
computer it is stored as the number of days since
30/12-1899 at 0:00. It is really a Double number, so the
number of days may include a fraction of a day. In this
way the field specifies the date as well as the time with
high precision. As an example, the number 1 corresponds
to 31/12-1899 at 0:00, the number 1.75 to
31/12-1899 at 18:00 (6 PM).
Usually we don't show a date field as a number, but as
a date and/or a time. The format property specifies this.
Also here you can choose a format that adapts to the
regional setting.
Yes/No. The field contains a Boolean value shown
either as Yes/No, True/False, or On/Off. The format
property specifies this.
AutoNumber. The field is a long integer (32 bits) that
Access generates itself as a unique number in the table.
Access numbers the records 1, 2, . . . as you enter the
records. However, you cannot trust that the sequence is
unbroken. For instance when you add a record and
undo the addition before having completed it, Access
uses the next number in the sequence anyway.
A foreign key is a field (or several fields) that refer to
something unique in another table - usually the primary
key. Be careful here. The foreign key and the primary
key must have the same type. However, when the
primary key is an AutoNumber, the foreign key must
be a long integer.
Changing a data type. Access is quite liberal with
changing a data type to something else - even if there
are data in the records. It can also change an AutoNumber
field to a number field, but not the other way
around. If you need to change field B to an AutoNumber,
create a new field C and make it an AutoNumber.
Then delete field B and rename field C to B.
If you for some reason want to store a record with an
AutoNumber of your own choice (for instance create a
stay with stayID=728), you need to append the record
with an INSERT query (see section 7.1). You cannot
just type in the stayID.
When we have several tables, we can make relationships
(crow's feet). Then we get an E/R model instead
of a simple collection of tables. The relationships allow
Access to help us retrieve data across tables, check referential
integrity, etc.
Figure 2.3 shows the hotel relationships in Access. It
resembles the crow's feet model quite well. You define
the relationships in this way:
1. Start in the database window and right-click
somewhere.
2. Choose Relationships.
Now you see an empty Relationship Window. You
have to tell Access which tables to show here. Sometimes
a Show Table window pops up by itself. Otherwise
you have to invoke it with a right-click in the relationship
window.
3. In the Show Table window, select the tables you
want to include. In the hotel system it is all the tables.
4. Click Add and close the window. Now the tables
should be in the relationship window.
5. Create the relationship between tblGuest and
tblStay by dragging guestID from one table to
guestID in the other.
6. An edit-relationship window pops up. If not, rightclick
on the relationship connector and choose the
edit window.
Access may complain:
Relationships must be on the same number of fields
with the same data types.
The cause is often that one end of the connector is an
AutoNumber and the other end a simple integer. It
must be a long integer to match the AutoNumber.
In the edit-relationship window, you can specify
foreign keys that consist of several fields. You can also
specify that the relationship has referential integrity, so
that all records on the m-side point to a record on the 1-
side.
7. In our case, all stays must point to a guest, so mark
the connector enforce referential integrity. (If
Access refuses this, it is most likely because you
have not defined the foreign key as a long integer.)
8. Close the relationship window. The relationship
connector now appears in the window between the
foreign key and its target.
The referential integrity makes Access show the connector
as 1-∞ (1:m). Based on referential integrity and
whether the connected fields are primary keys, Access
may also decide that it is a 1:1 relationship. It is not
important what Access decides in these matters. You
can later tell it otherwise when you want to use the
connector.
9. Create the remaining relationships too. Note that
there is no referential integrity between tblStay and
tblRoomState. It is on purpose - if the room is in
repair state there is no connected stay.
Partial integrity. Access provides a more relaxed
version of referential integrity. It allows the foreign key
to be either empty (Null) or point to a record on the 1-
side. This is the case for the relationship between
tblStay and tblRoomState. Give it partial integrity in
this way:
10. Open tblRoomState in design view. For stayID
(the foreign key) set the Default Value to empty
(delete all characters in the field). Also set
Required to No.
11. In the relationship window, right-click on the
connector and choose the edit window. Select
enforce referential integrity.
Note that you cannot see in the relationship window
whether the relationship has full or partial referential
integrity.
Deleting a relationship. If you need to delete a relationship,
click it and press Del.
2.4 Look-up fields, enumeration type
Your next task will be to fill in some data in all the tables.
However, some of the fields are cumbersome to
fill in correctly. As an example, the pay method field is
a code where 1 means Cash, 2 Visa, etc. The user
should not have to remember these codes, so we will
let the user choose the value from a list. It is an
enumeration-type field:
paymethod(Cash | Visa | . . . )
Figure 2.4 shows what we want when the user fills in
the paymethod field. We want the field to be a combo
box where the user can select the mnemonic text while
Access stores the number code. Here is how to do it:
1. Open tblStay in design view. (Select it and click
Design or use Ctrl+Enter).
2. Select the paymethod field and the data type
Lookup Wizard.
3. Access asks whether you (as a user) want to select
the values from a table or from a list of values that
you (as a designer) type in. Choose to type them
in. Then click Next.
4. Access asks how many columns your combo box
should have. Choose two and fill in the columns as
shown on the figure. Then click Next.
5. Access asks which column holds the value to store
in the table. In our case it is column 1.
6. Finally, Access asks for the column name that the
user will see. In our case, paymethod is okay.
Click Finish.
Fill in some stay records
You are now going to create some stay records and
connect them to a guest.
7. Close the table design window and open it in user
mode.
8. Also open tblGuest in user mode. Keep the two
tables side by side so you can see both. Make sure
you have created some guests. Otherwise do it
now.
9. Fill in a stay record using the combo box for
paymethod. Notice that what you see as a designer,
is the number stored in the database. The user
should not see the number, but the text. We can
arrange for this when the field becomes a text box
in the user window (see section 3.2.2).
10. Also fill in the foreign key guestID so that it refers
to one of the guests. Since there is referential
integrity, Access won't let you store the stay record
without a proper guestID. If you get into real
trouble, use Esc twice (see the Panic box for the
explanation).
11. Fill in a few more stay records in the same way.
How the look-up field works
Open tblStay in design mode and study the Lookup tab
for paymethod (bottom of Figure 2.4). The display
control property is Combo Box. It means that when the
user is to fill in the paymethod, he sees a combo box.
• For ordinary fields Display Control is Text Box. A
text box shows texts, numbers, etc. as a string of
characters. If you want to change the field back to
an ordinary field, just set Display Control to Text
Box.
The values the user can choose between are listed in
Row Source. You may edit the values here. Column
Count shows that these values are to be displayed as
two columns. Notice that Limit to List is No. It means
that the user can enter other values than those in the
list. In our case, it is not desirable, so set the property
to Yes. Sections 3.1.6 and 3.1.7 explain more about
combo boxes.
Undo the Lookup Wizard?
How do you make the field an ordinary field rather
than a lookup field? It doesn't help to make it an integer
or a text. Choose the Lookup tab at the bottom of the
table design window. Change Display Control to Text
Box. (See bottom of Figure 2.4.)
Panic? Undo data entry
When you enter data into the tables, Access checks
against the rules you have defined for the tables and the
relationships. For instance, when you enter the guestID
in tblStay, this ID must correspond to a guest in the
guest table. Access doesn't allow you to leave the
record before this is fixed. The reason is that Access
stores the record in the database as soon as you move
the cursor away from the record. And the database
must meet all the rules you have stated.
Sometimes you may not know what to type to satisfy
Access, and on the other hand you cannot leave the record
to look at what to type. Many users panic here and
even switch off the power to close down the system.
The solution is to use Esc twice:
• First Esc: Undoes the correction you made in the
field where the cursor is.
• Second Esc: Undoes all the changes you made to
the record where the cursor is. This means that the
database returns to a consistent state where all the
rules are met.
Populate the database
12. Define the other enumeration fields as lookup
fields in the same way (the state fields in tblStay
and tblRoomState).
13. Fill in some realistic data in all the tables. You
may for instance use data corresponding to the
situation in Figure 1A. Now you have test data for
the rest of the booklet.
Important: Compact the database
Access is very liberate with disk space and when you
change things, it consumes new blocks on the disk.
You may soon find that a simple little database uses
several megabytes. Fortunately, Access can compact
the database. Do that every now and then in this way:
14. Select Tools->Database Utilities->Compact and
Repair Database. That is all. You may check that
the file length actually became much smaller. (In
Access 97, the Compact and the Repair utilities are
separate.)
2.5 Dealing with trees and networks
E/R models can neatly describe complex relationships,
for instance as we saw it for the flight routes in User
Interface Design. Figure 2.5 shows the E/R model, but
Access cannot show such a model directly.
The problem is that Access identifies a relationship by
means of the two tables it connects. This means that
Access cannot have two connectors between the same
two tables. Also you cannot have a self-referential connector.
In the flight route model we need both of these.
As a compensation, Access offers shadow copies of a
table. The table and its shadow copies are the same table,
but they have different names. You can now create
connectors to the shadow copies and thus indirectly
create multiple connectors between the same two tables.
Figure 2.5 shows how to handle the flight routes in
Access by means of shadow copies.
1. Create a new database, FlightRoutes. Create the
tables City, Leg and Route in the usual way.
2. Open the relationship window and add all three tables
to the relationship window. Then add City and
Leg once more. The relationship window should
now contain also a City_1 and a Leg_1 as shown
on the figure.
3. Drag the connectors as shown. You now have two
connectors between City and Leg. One is determined
by City and the foreign key from. The other
is determined by City_1 and the foreign key to.
You also have a self-referential connector from
Leg to itself. It is determined by Leg_1 and the
foreign key next.
4. Try to fill in data for AA331 according to the figure.
Note that there are only one City table and one
Leg table to fill in. The shadow tables are not real
hotel. The data model on Figure 2.2 shows the tables
we will use. To simplify your job, we have shown all
the keys, including the foreign keys and the artificial
keys.
1. Close the guest table.
2. Create all the remaining tables in the same way as
you created the guest table (from the Tables tab
use Create table in Design view - or click New).
Make sure you define all the fields. Otherwise you will
get stuck when later constructing the user interface.
Here are a few notes about the various tables:
tblStay:
stayID is the primary key of tblStay. Make it an AutoNumber.
guestID is a foreign key that refers to the AutoNumber
in tblGuest. The foreign key must have a matching
data type - a long integer. Choose Data Type =
Number and Field Size = Long Integer. Warning:
Don't make the foreign key an AutoNumber. This
would cause Access to fill in the foreign key fields
automatically, and you cannot change the numbers
so that they point to the primary keys in the guest
table.
paymethod is an enumeration type. Make it an integer
(a 16-bit integer, not a long integer). Choose Data
Type = Number and Field Size= Integer. We will
use the value 1 to denote Cash, the value 2 to denote
Visa, etc. We will look closer at this in section
2.4.
state must also be an enumeration type. Make it an integer.
Here the value 1 will denote booked, 2 in, etc.
tblRoomType:
Contains one record for each type of room, for instance
one for double rooms, one for single rooms, etc. (In the
book User Interface Design, we added this table late in
the design process to illustrate the normalization concept.)
roomType is an artificial key. An AutoNumber is okay.
description is a short text, for instance "double room,
bath".
bedCount is the number of beds in the room, including
temporary beds.
price1 and price2 are the standard price and a possible
discount price. The price should be a decimal number.
Choose Data Type=Number, Field Size= Single,
Decimal Places =2.
tblRoom:
roomID is a natural key - the number on the door. So
don't use an AutoNumber. Use an integer.
roomType is a foreign key that refers to tblRoomType.
(You should by know how to deal with it.)
tblRoomState:
stayID and roomID are foreign keys. Ensure their types
match what they refer to. Notice that roomID refers
to a natural key, not to an AutoNumber.
date should be a Date/Time field with Format = Short
Date.
personCount is the number of persons staying in the
room. An integer should suffice.
state is similar to state for tblStay, although the values
are slightly different.
The key consists of two fields: roomID and date. It is a
bit tricky to specify this: select both fields by
clicking on the left-hand marker (hold down Ctrl
while selecting the second field). Then right-click
somewhere on the text inside the line.
Optional tables
The following two tables are needed for the full system.
However, you don't need to create them in order
to follow the tutorial.
tblServiceType:
serviceID is an artificial key. Should be an AutoNumber.
name and price should be obvious. The price should be
a decimal number. Choose Data Type=Number,
Field Size= Single, Decimal Places =2.
tblServiceReceived:
stayID and serviceID are foreign keys that refer to
AutoNumbers. The foreign keys must thus be long
integers.
roomID is an optional reference to a room. An integer
should suffice. (This reference is needed when a
waiter records a service for a specific room and the
guest has more than one room.)
date should be a Date/Time field. Choose Format =
Short Date.
quantity is the number of items the guest has got - an
integer should suffice.
Data is stored in the computer according to its type.
Here is a description of the most important types in the
data base. Visual Basic deals with almost the same
types (see section 6.2 and the reference card under
Declarations).
Text. The field can contain any characters. The Field
Size property defines the maximum number of characters.
The maximum cannot be above 255 characters.
Memo. Like a text field, but the maximum number of
characters is 65,535. Access takes more time to process
a memo field, so use text fields if adequate.
Number. The field can contain a number. The Field
Size property defines what kind of number:
Integer. A small integer. It must be in the range -
32,768 to +32,767 (a 16-bit integer).
• Long Integer. It must be in the range from around
-2,140 million to +2,140 million (a 32-bit integer).
• Single. A decimal number in the range from
-3.4*1038 to +3.4*1038 with an accuracy of 6 or 7
significant digits (a 32-bit floating point number).
• Double. A decimal number in the range from
-1.8*10308 to +1.8*10308 with 14 significant digits
(a 64-bit floating point number).
• Decimal. A very long integer with a decimal point
placed somewhere. Intended for monetary calculations
where rounding must be strictly controlled.
In the book we use Single or Double instead.
Numbers can be shown in many ways depending on the
format property of the field. You may for instance
show them with a fixed number of decimals, with a
currency symbol, etc.
Some formats show data in a way that depends on the
regional settings of the computer. If you for instance
specify the format of a number as Currency, the
number will show with a $ on a US computer and with
a £ on a British computer.
Date/Time. The field gives a point in time. In the
computer it is stored as the number of days since
30/12-1899 at 0:00. It is really a Double number, so the
number of days may include a fraction of a day. In this
way the field specifies the date as well as the time with
high precision. As an example, the number 1 corresponds
to 31/12-1899 at 0:00, the number 1.75 to
31/12-1899 at 18:00 (6 PM).
Usually we don't show a date field as a number, but as
a date and/or a time. The format property specifies this.
Also here you can choose a format that adapts to the
regional setting.
Yes/No. The field contains a Boolean value shown
either as Yes/No, True/False, or On/Off. The format
property specifies this.
AutoNumber. The field is a long integer (32 bits) that
Access generates itself as a unique number in the table.
Access numbers the records 1, 2, . . . as you enter the
records. However, you cannot trust that the sequence is
unbroken. For instance when you add a record and
undo the addition before having completed it, Access
uses the next number in the sequence anyway.
A foreign key is a field (or several fields) that refer to
something unique in another table - usually the primary
key. Be careful here. The foreign key and the primary
key must have the same type. However, when the
primary key is an AutoNumber, the foreign key must
be a long integer.
Changing a data type. Access is quite liberal with
changing a data type to something else - even if there
are data in the records. It can also change an AutoNumber
field to a number field, but not the other way
around. If you need to change field B to an AutoNumber,
create a new field C and make it an AutoNumber.
Then delete field B and rename field C to B.
If you for some reason want to store a record with an
AutoNumber of your own choice (for instance create a
stay with stayID=728), you need to append the record
with an INSERT query (see section 7.1). You cannot
just type in the stayID.
When we have several tables, we can make relationships
(crow's feet). Then we get an E/R model instead
of a simple collection of tables. The relationships allow
Access to help us retrieve data across tables, check referential
integrity, etc.
Figure 2.3 shows the hotel relationships in Access. It
resembles the crow's feet model quite well. You define
the relationships in this way:
1. Start in the database window and right-click
somewhere.
2. Choose Relationships.
Now you see an empty Relationship Window. You
have to tell Access which tables to show here. Sometimes
a Show Table window pops up by itself. Otherwise
you have to invoke it with a right-click in the relationship
window.
3. In the Show Table window, select the tables you
want to include. In the hotel system it is all the tables.
4. Click Add and close the window. Now the tables
should be in the relationship window.
5. Create the relationship between tblGuest and
tblStay by dragging guestID from one table to
guestID in the other.
6. An edit-relationship window pops up. If not, rightclick
on the relationship connector and choose the
edit window.
Access may complain:
Relationships must be on the same number of fields
with the same data types.
The cause is often that one end of the connector is an
AutoNumber and the other end a simple integer. It
must be a long integer to match the AutoNumber.
In the edit-relationship window, you can specify
foreign keys that consist of several fields. You can also
specify that the relationship has referential integrity, so
that all records on the m-side point to a record on the 1-
side.
7. In our case, all stays must point to a guest, so mark
the connector enforce referential integrity. (If
Access refuses this, it is most likely because you
have not defined the foreign key as a long integer.)
8. Close the relationship window. The relationship
connector now appears in the window between the
foreign key and its target.
The referential integrity makes Access show the connector
as 1-∞ (1:m). Based on referential integrity and
whether the connected fields are primary keys, Access
may also decide that it is a 1:1 relationship. It is not
important what Access decides in these matters. You
can later tell it otherwise when you want to use the
connector.
9. Create the remaining relationships too. Note that
there is no referential integrity between tblStay and
tblRoomState. It is on purpose - if the room is in
repair state there is no connected stay.
Partial integrity. Access provides a more relaxed
version of referential integrity. It allows the foreign key
to be either empty (Null) or point to a record on the 1-
side. This is the case for the relationship between
tblStay and tblRoomState. Give it partial integrity in
this way:
10. Open tblRoomState in design view. For stayID
(the foreign key) set the Default Value to empty
(delete all characters in the field). Also set
Required to No.
11. In the relationship window, right-click on the
connector and choose the edit window. Select
enforce referential integrity.
Note that you cannot see in the relationship window
whether the relationship has full or partial referential
integrity.
Deleting a relationship. If you need to delete a relationship,
click it and press Del.
2.4 Look-up fields, enumeration type
Your next task will be to fill in some data in all the tables.
However, some of the fields are cumbersome to
fill in correctly. As an example, the pay method field is
a code where 1 means Cash, 2 Visa, etc. The user
should not have to remember these codes, so we will
let the user choose the value from a list. It is an
enumeration-type field:
paymethod(Cash | Visa | . . . )
Figure 2.4 shows what we want when the user fills in
the paymethod field. We want the field to be a combo
box where the user can select the mnemonic text while
Access stores the number code. Here is how to do it:
1. Open tblStay in design view. (Select it and click
Design or use Ctrl+Enter).
2. Select the paymethod field and the data type
Lookup Wizard.
3. Access asks whether you (as a user) want to select
the values from a table or from a list of values that
you (as a designer) type in. Choose to type them
in. Then click Next.
4. Access asks how many columns your combo box
should have. Choose two and fill in the columns as
shown on the figure. Then click Next.
5. Access asks which column holds the value to store
in the table. In our case it is column 1.
6. Finally, Access asks for the column name that the
user will see. In our case, paymethod is okay.
Click Finish.
Fill in some stay records
You are now going to create some stay records and
connect them to a guest.
7. Close the table design window and open it in user
mode.
8. Also open tblGuest in user mode. Keep the two
tables side by side so you can see both. Make sure
you have created some guests. Otherwise do it
now.
9. Fill in a stay record using the combo box for
paymethod. Notice that what you see as a designer,
is the number stored in the database. The user
should not see the number, but the text. We can
arrange for this when the field becomes a text box
in the user window (see section 3.2.2).
10. Also fill in the foreign key guestID so that it refers
to one of the guests. Since there is referential
integrity, Access won't let you store the stay record
without a proper guestID. If you get into real
trouble, use Esc twice (see the Panic box for the
explanation).
11. Fill in a few more stay records in the same way.
How the look-up field works
Open tblStay in design mode and study the Lookup tab
for paymethod (bottom of Figure 2.4). The display
control property is Combo Box. It means that when the
user is to fill in the paymethod, he sees a combo box.
• For ordinary fields Display Control is Text Box. A
text box shows texts, numbers, etc. as a string of
characters. If you want to change the field back to
an ordinary field, just set Display Control to Text
Box.
The values the user can choose between are listed in
Row Source. You may edit the values here. Column
Count shows that these values are to be displayed as
two columns. Notice that Limit to List is No. It means
that the user can enter other values than those in the
list. In our case, it is not desirable, so set the property
to Yes. Sections 3.1.6 and 3.1.7 explain more about
combo boxes.
Undo the Lookup Wizard?
How do you make the field an ordinary field rather
than a lookup field? It doesn't help to make it an integer
or a text. Choose the Lookup tab at the bottom of the
table design window. Change Display Control to Text
Box. (See bottom of Figure 2.4.)
Panic? Undo data entry
When you enter data into the tables, Access checks
against the rules you have defined for the tables and the
relationships. For instance, when you enter the guestID
in tblStay, this ID must correspond to a guest in the
guest table. Access doesn't allow you to leave the
record before this is fixed. The reason is that Access
stores the record in the database as soon as you move
the cursor away from the record. And the database
must meet all the rules you have stated.
Sometimes you may not know what to type to satisfy
Access, and on the other hand you cannot leave the record
to look at what to type. Many users panic here and
even switch off the power to close down the system.
The solution is to use Esc twice:
• First Esc: Undoes the correction you made in the
field where the cursor is.
• Second Esc: Undoes all the changes you made to
the record where the cursor is. This means that the
database returns to a consistent state where all the
rules are met.
Populate the database
12. Define the other enumeration fields as lookup
fields in the same way (the state fields in tblStay
and tblRoomState).
13. Fill in some realistic data in all the tables. You
may for instance use data corresponding to the
situation in Figure 1A. Now you have test data for
the rest of the booklet.
Important: Compact the database
Access is very liberate with disk space and when you
change things, it consumes new blocks on the disk.
You may soon find that a simple little database uses
several megabytes. Fortunately, Access can compact
the database. Do that every now and then in this way:
14. Select Tools->Database Utilities->Compact and
Repair Database. That is all. You may check that
the file length actually became much smaller. (In
Access 97, the Compact and the Repair utilities are
separate.)
2.5 Dealing with trees and networks
E/R models can neatly describe complex relationships,
for instance as we saw it for the flight routes in User
Interface Design. Figure 2.5 shows the E/R model, but
Access cannot show such a model directly.
The problem is that Access identifies a relationship by
means of the two tables it connects. This means that
Access cannot have two connectors between the same
two tables. Also you cannot have a self-referential connector.
In the flight route model we need both of these.
As a compensation, Access offers shadow copies of a
table. The table and its shadow copies are the same table,
but they have different names. You can now create
connectors to the shadow copies and thus indirectly
create multiple connectors between the same two tables.
Figure 2.5 shows how to handle the flight routes in
Access by means of shadow copies.
1. Create a new database, FlightRoutes. Create the
tables City, Leg and Route in the usual way.
2. Open the relationship window and add all three tables
to the relationship window. Then add City and
Leg once more. The relationship window should
now contain also a City_1 and a Leg_1 as shown
on the figure.
3. Drag the connectors as shown. You now have two
connectors between City and Leg. One is determined
by City and the foreign key from. The other
is determined by City_1 and the foreign key to.
You also have a self-referential connector from
Leg to itself. It is determined by Leg_1 and the
foreign key next.
4. Try to fill in data for AA331 according to the figure.
Note that there are only one City table and one
Leg table to fill in. The shadow tables are not real
No comments:
Post a Comment