Try Out Table Creation

• Open up the HealthyGym database by double clicking it.
• Click on 'Create Table using the design view'.
• Wait for the Design view to appear. Type 'IDField' under 'Field Name'
• Use the <TAB> key to move to the data type field.
• Click on the down arrow and select 'Number' from the list that appears
• Move to the next field using tab and type a small description - 'This column contains an ID'
• Use tab to move to the next row. Enter 'NameField'
• Move to the data type field and select 'Text'
• The lower half of this view contains a section entitled 'Field Properties'. This should show '50' next to 'Field Size'. Change this to 60.
• Tab to the next field and enter 'This is a name field'
• Now, right click on the first row and select Primary Key. This makes the IDField column the Primary Key.
• To set up an automatic sequence, change the data type of the IDField column to 'Autonumber'. Basically, you need to tab over to the Data Type field and select 'Autonumber'.
• Now, go to File -< Save As and type 'SampleTable' under 'Save Table To.'
• You have now created a table. Close the table. Move back to the Healthy Gym Database window. SampleTable should appear at the bottom of the list of tables. You may now open it to enter data by double clicking on it.
• To move to the design view and make more changes, you should click on the set square icon on the top left hand side of the toolbar.
• Close the table again and delete it by right clicking on it and selecting 'delete'. Answer 'Yes' to the pop up confirmation box.

Just as we deleted the sample table in Access using the GUI, we may delete a table using SQL. Deletion is achieved by the following command:

 DROP TABLE GymMembers
COMMIT

GymMember is the name of the table; the commit statement is a standard in many RDBMS that use SQL. Commit is a sort of confirmation that actually causes all the SQL data modification commands above it to be executed.

The Database design process should comply with certain broad guidelines. A well designed database stores information in the smallest amount of space possible and responds to queries very fast. Let us say that we need to add some information about the services that each member has subscribed for to the GymMembers example above. Consider the following table

Fig 3a: Data Redundancy

The above table stores the word Cardio four times and the words Weights, Steam, and Massage three times. If the membership of this gym grew to, say, 500 people, the same words would appear many hundreds of times. Not a serious problem for such a small data store, but what if the table contained the subscribers to, say, a telephone exchange and the services they are subscribed to? Going back to our example, the GymEnrollment table contains redundant or unnecessarily repeated data. How would a good design eliminate this problem and streamline the database? Take a look at the following tables.

Fig 3b: First Normal Form

GymEnrollment has now been split into two tables. Each member now has a unique ID. This ID is called a Primary Key. Assigning such unique IDs or primary keys makes a database comply to the 1st Normal form. Lookup is easier now; all we have to do is search for ”Steam” in the second table to get a list of members who have subscribed for this service. However, it still looks untidy and numbers repeat a whole lot. Also, if the last member enrolled in a particular service leaves the gym, the service will no longer be listed in the database!

Fig 3c: Second Normal Form

Look at the above set of tables. A new Service table has been created with unique IDs for each service offered. Now, the new MemberService table contains less repeats and is much clearer. We have separated the old GymService table because it depended on two sets of keys - memberID and serviceID - not a good practice. By doing this, we have achieved the 2nd normal form. To get the design compliant to the 3rd normal form, a single table should contain data only about a single subject.Let us say that the Gym expanded to many cities. Rather than adding branch name and location columns to the GymMembers table, we would create a GymBranch table with a unique primary key and use this primary key to indicate the branch a Gym member uses. This concept is called a foreign key. Using such keys makes the database comply with the 3rd Normal Form. Making a database comply to these normal forms will ensure that it is efficient.

Fig 3d: 3rd Normal Form