Try Out Database Creation

• Start up Microsoft Access by clicking on its icon in the Start menu.
• Go to File -> New. Select OK in the dialogue box that opens.
• Enter MyNewDatabase in the 'File New Database' Dialogue box that appears and click on 'Create'
• You have now created a database. The database will open up automatically

Screenshot 3a: New database creation in access

Now that we have created a database, we are ready to create tables and fill them up with information. Before we move into creating tables, it is important to discuss the language used to communicate with the Relational Data Base Management System or RDBMS. While a GUI is used to perform almost all database operations nowadays, learning this language is indispensable. Knowing SQL, the standard language used to program databases, gives the user a lot more control and makes for speedy information retrieval. After all, typing commands in SQL is like talking to the database directly while using the GUI is similar to using an interpreter to translate our commands.

So how do we create a table using SQL? Take a look at the following code:

 CREATE TABLE GymMembers
(
MemberID int(6) PRIMARY KEY,
MemberName varchar(60),
MemberAddress varchar,
MemberExpire Date,
MemberGender char(1),
)
CREATE INDEX IDIndex
ON GymMembers (MemberName ASC)

This code is generic SQL. It creates a table named GymMembers. Let us look at the different parts of the statement

 CREATE TABLE GymMembers
(
MemberID int(6) PRIMARY KEY
MemberName varchar(60),
MemberAddress varchar,
MemberExpire Date,
MemberGender char(1)
)  

The above line specifies that the GymMembers table should have five columns, namely: MemberID, MemberName, MemberAddress, MemberExpire, andMemberGender. A primary key is a unique ID for each row; it helps the user easily reference information. The primary key directive establishes the MemberID column as the primary key of the table. The directive that follows the column name (int(3) and so on) has to do with Data Typing. Data Typing is a way of specifying that only a particular type of information belongs in the column. For example, MemberExpire AS Date indicates that the column MemberExpire should always contain a date. Further, it is possible to constrain the values of a column. That is, you may specify that a gender column is a single alphabetical character that should either be 'F' or 'M' or that a salary column should always contain a number greater than zero. The following table shows some data types that are available on SQL

Data Type	Description
integer(size) int(size) smallint(size) tinyint(size)	Hold integers only. The maximum number of digits are specified in parenthesis.
decimal(size,d) numeric(size,d)	Hold numbers with fractions. The maximum number of digits are specified in "size". The maximum number of digits to the right of the decimal is specified in "d".
char(size)	Holds a fixed length string (a sequence of letters, numbers, and special characters). The fixed size is specified in parenthesis.
varchar(size)	Holds a variable length string (can contain letters, numbers, and special characters). The maximum size is specified in parenthesis.
date(yyyymmdd)	Holds a date

 
CREATE INDEX IDIndex
ON GymMembers (MemberName ASC)

The index directive sets up an index for this table on MemberName; each member's name will be stored in alphabetical order with a reference to the actual physical location of that member's row in the table. Searching for a member's information using his or her name becomes faster; once a member's index entry is found, the program can directly jump to his or her row.

A sequence directive directs the DBMS to automatically increment the value of the column every time a new row is entered. This way, the user does not have to keep track of the number of rows in the table. Sequences are not SQL standard and are implemented in different ways in different DBMS.

Once the CREATE TABLE SQL command is processed by the DBMS, the table is created and the user gets a confirmation message. The table is now ready for data entry. Creating a table using a GUI is usually a lot easier than using SQL. To create a table in Microsoft Access, the user simply opens the database, clicks on 'Tables' in the object tab on the left hand side, clicks on one of the create table tabs and then specifies the information about each column.

Screenshot 3a: New table creation in access