Introduction to Databases

List-Based Applications

An application is referred to as list-based when its primary purpose is to let a user create and store one or more lists of items. The items can be of various kinds and the information they constitute is referred to as data. For this reason, this type of application is also called a database.

A list that is part of a database application can be made of regular strings. Here is an example:

Here is another example:

A list can also be a combination of various lists. Here is an example:

This type of list is called a table. As you can see, this table includes a list for the formats of the videos that seems to present categories of items. A technique used to create this type of table consists of "connecting" disparate lists that can exchange information with each other. An application that includes tables that are connected is referred to as a relational database.

Borland C++ Builder provides a (very) high level of support for database applications. It comes with an application called Database Desktop that allows you to create Paradox or dBase tables necessary for your database. Paradox and dBase tables are the easiest and the most accessible you can use in a VCL application. You can also use an external application such as Microsoft Access, FoxPro, etc. These two types of systems are usually referred to as desktop databases because they would be installed on users individual computers. Borland C++ Builder also support server-based, also referred to as enterprise, databases. These are databases that reside on a server and users must connect to them to use the application. The systems used to create these types of databases include InterBase, Microsoft SQL Server, Sybase, Oracle, DB2, etc.

To make sure that we have everything we need for our lessons, we will use only Paradox and Microsoft SQL Server 2000 Desktop Engine, which will be referred to as MSDE for the rest of our lessons. The advantage of Database Desktop, as we mentioned already, is that it is already available with C++ Builder so you don't have to install or download it from somewhere.

To launch Database Desktop, you can click the Start button on the taskbar -> (All) Programs -> Borland C++ Builder 6 -> Database Desktop. If C++ Builder is already opened, on the main menu of C++ Builder, you can click Tools -> Database Desktop.

The advantages of MSDE include the fact that it is freely available and also it tremendously allows you to experiment with external databases. The techniques we will use for the MSDE can also be applied to an enterprise database such as Microsoft SQL Server. If you want to experiment with Microsoft SQL Server, you can down a trial version from the Microsoft web site.

To get MSDE, go to the Microsoft web site and do a search on MSDE. A link will guide to the free download. After downloading it, make sure you install it.

• To start Database Desktop, on the Taskbar, click Start -> (All) Programs -> Borland C++ Builder 6 -> Database Desktop

The Database Desktop is an application used to create Paradox or dBase tables and other files to use in a VCL application. Before creating these tables and/or files, you must designate a folder where you would save them. A folder used to hold he objects of a Database Desktop application is called a Working Directory. When you start creating a database application, you would need to know where the tables reside. A working directory can also be shared so that many people who work in the same project can store the common files in it.

When you install C++ Builder, it automatically creates various folders, both in Drive:\Program Files\Borland and in Drive:\Program Files\Common Files. It also creates a working directory named WorkDir as Drive:\Program Files\Common Files\Borland Shared\Database Desktop\WorkDir.  You also can create a folder of your choice to hold your tables and files.

1. Open Windows Explorer or My Computer
2. If you don't have it yet, create a folder named Programs
3. Inside of the Programs folder, create a sub-folder named College Park Auto Shop
    
   

If you work in a multi-user environment, you may want to hold some of your files privately while testing them so that other people cannot corrupt them. To do this, you can create and use a folder of your choice.

When C++ Builder gets installed, it also creates a private directory named PrivDir as Drive:\Program Files\Common Files\Borland Shared\Database Desktop\\PrivDir.

Because the full path of your working directory could be long to remember at times, you can create a name used to identity that directory. Such a name is called an alias. For example, instead of always trying to remember that your files are stored in Drive:\Customers Projects\Database Applications\College Park Auto Parts\Store Items, you can define a simple name such as cpap that you can use to identify this long path.

To create an alias, in Database Desktop, you can click Tools -> Alias Manager from the main menu and click New. To proceed, in the Alias Manager dialog box, you must provide a name and specify the full path of the folder that the alias will represent.

If you have created an alias that you don't need or don't use anymore, to delete it, display the Alias Manager dialog box. In the Database Alias combo box, select the alias and click Remove.

1. On the main menu of Database Desktop, click Tools -> Alias Manager...
2. In the Alias Manager, click New
3. Change the Name to CPAS
4. Accept the Driver Type as STANDARD
5. In the path, enter the complete path to the College Park Auto Shop folder you created
    
   
6. Click OK
   You should receive a message asking you whether to update the new list of aliases
    
   
7. Click Yes

A database starts from a list of items. The list is also called a table. In a VCL application, probably the easiest way to create a table is by using Database Desktop.  Because the tables created with Database Desktop are mostly Paradox and dBase objects, their tables are considered as files.

As a file, the primary characteristic of a table is its name. The name must follow the rules of the operating system's file. Although the name of a table can start with a digit or an underscore, you should avoid using fancy names for tables as this can cause confusion when you start involving the names of these tables in your expressions.

To create a table, on the main menu of the Database Desktop window, you can click File -> New -> Table... This would display the Create Table dialog box. It allows you to specify the type of table you want to create. By default, it present you the option to create a Paradox 7 table. If you prefer another version of Paradox or you want to create a dBase table, you can click the arrow of the Table type combo box and select from the list:

If you have Corel WordPerfect, you can also use its version of Paradox to create a table. To do this, when the Welcome dialog box of Paradox displays, you can first click New Database

Second, you must specify the name and location of the new database. If you were not ready to create a database, you could close the Welcome dialog box. In both cases, once in Paradox, on the main menu, you could click File -> New -> Table... or, from the Project Viewer, you could right-click the Tables node and click New... When you select to create a new table, you can use a wizard to guide you.

1. On the main menu of Database Desktop (or Corel Paradox), click File -> New -> Table... (if you are using Corel Paradox, click Blank Table)
2. In the Create Table dialog box, accept Paradox 7 (or Paradox 7 - 10) and click OK

Earlier, in our introduction, we saw that a table was a good technique to arrange a list of items by their categories. Here is the same example seen above:

The categories of items of a table are also referred to as columns. In this example, the first column, from left is labeled Video Title. The second column is labeled Director. The label in the top section of a column is also called its caption.

In VCL applications, a column is also called a field. To reduce confusion and to synchronize the rest of these lessons with the VCL documentation, a column will be referred to as a field.

As an integral part of a table, every field must be explicitly created. The primary information you must provide for a field is a name and there are rules you must follow. If you are creating a Paradox table:

• The name of a field can start with a letter, a digit or an underscore
• After the first character (as a letter, a digit or an underscore), the name can contain other letters, digits, underscores, spaces
• The name of a field must have a maximum of 25 characters
• The (complete) name of a field must be unique among the names of other fields of the same table

After respecting these rules, you can define your own. In our databases, here are the rules we will follow:

• The name of a field will start with a letter. Here are examples: salary, DOB, name
• The name of a field will not contain space
• When the name is a combination of names, each first character will be in uppercase. Here are examples: firstName, FullName, DateOfBirth

To specify the name of a field, under the Field Name, type the desired name and press either Enter or Tab.

• In the first box under Field Name, type WorkOrderID and press Tab

When you have created a table and present it to the users for data entry, it is a good idea to control and know the kind of data that a user can enter for each category. For example, it is certainly a good idea to prevent the user from entering a first name where a salary is expected. The type of data that can be entered under a field is referred to as its data type.

To specify the data type used for a field, under the Type column, if you are using Database Desktop, you can right-click or press the Space bar. If you are using Corel Paradox, you can click the arrow of the combo box under Field:

To be able to exercise a great deal of control on the type of information that a user can enter in a table, you can apply an appropriate data type. Some data types are very explicit while some other can be vague. To support these types, both Paradox and dBase provide a list of available data types. The data types of a Paradox table are:

• Alpha: This is equivalent to the various strings available in C++, VCL, or even STL. This is used for field where the user would be entering regular strings. This is the most general database. Its fields can hold 1 to 255 alphabetical characters, non-readable characters, and symbols of any kind. After selecting this type, the A letter would display under type
• Memo: This is used for a field that would hold text longer than the Alpha data type or a VCL's AnsiString can handle. The text is the same type you use in Notepad: no format. This means that a Memo field can include long paragraphs of text. During data entry, if the user enters a long text, only the first 240 characters would be stored in the table. The rest of the text would be stored in an MB file but the text would be readily available in the table
• Formatted Memo: Like the Memo data type, the Formatted Memo type is used for a field that would hold text longer than the Alpha or the AnsiString data types can handle. Unlike the Memo type, the Formatted Memo can handle text that includes various fonts, styles, and different individual sizes of characters
• Number: The Number data type allows the user to enter a combination of digits to make up a recognizable number. The number can display - on its left to indicate that it is negative. If the number is negative, it can include the character known as the decimal separator
• Money: This is for a field that would receive currency values
• Short: This is equivalent to the C++'s short integer. It allows a user to enter a natural number in the range of -32767 to 32768
• Long Integer: This is equivalent to the C++' long data type, designed to hold very large numbers in the range -2147483648 to 2147483647
• Autoincrement: This is used if you want the table to enter automatically incrementing numbers for this field during data entry. This type is mostly appropriate for a relational database
• Logical: This is used for a field whose data would hold True or False values
• Date: This is used for a field that would receive date value that can range from January 1, 9999 BC to December 31, 9999 AD
• Time: This is used for a field that would receive time values
• Timestamp: A timestamp includes both date and time
• Graphics: This is appropriate for a field that would hold pictures. During data entry, the user can specify the picture for the record but the picture is stored in a separate file
• OLE: This is used if the field will hold documents created in mostly unknown applications. In our databases, we will not use this data type
• BCD: The Binary Coded Decimal (BCD) is used for numeric fields involved in a calculation and that require a high level of precision. In our databases, we will not use this data type

1. Click the box under Type and press the Space bar (if you are using Corel Paradox, click the box under Type and click the arrow of the combo box)
2. Click +
3. Complete the table with other fields as follows:
    

   Field Name	Type
   WorkOrderID	+
   Address	A
   City	A
   State	A
   ZIPCode	A
   Make	A
   Model	A
   CarYear	S
   ProbDesc	M
   PartName1	A
   UnitPrice1	A
   Quantity1	S
   SubTotal1	A
   PartName2	A
   UnitPrice2	A
   Quantity2	S
   SubTotal2	A
   PartName3	A
   UnitPrice3	A
   Quantity3	S
   SubTotal3	A
   PartName4	A
   UnitPrice4	A
   Quantity4	S
   SubTotal4	A
   PartName5	A
   UnitPrice5	A
   Quantity5	S
   SubTotal5	A
   JobPerformed1	A
   JobPrice1	A
   JobPerformed2	A
   JobPrice2	A
   JobPerformed3	A
   JobPrice3	A
   JobPerformed4	A
   JobPrice4	A
   TotalParts	A
   TotalLabor	A
   TaxRate	A
   TaxAmount	A
   OrderTotal	A
   Recommendations	M

4. To insert a field we forgot, click Address to select it
5. Press Ins
6. Enter the Field name as CustomerName and its Type as A

When performing data entry, you may want to control the number of characters that a user can enter enter a certain field. This is referred to as the size of a field. Most fields have a size but you can control only the size of string-based fields. This means that, only if you are creating an Alpha field should you pay attention to the size. Ignore this information for all the other type.

To specify the size of a field, under Size, type the desired number.

1. Click CustomerName and press Enter twice. Under Size, type 50 and press the down arrow key
2. Complete the table with other fields as follows:
    

   Field Name	Type	Size
   WorkOrderID	+
   CustomerName	A	50
   Address	A	50
   City	A	30
   State	A	30
   ZIPCode	A	10
   Make	A	30
   Model	A	40
   CarYear	S
   ProbDesc	M	240
   PartName1	A	50
   UnitPrice1	A	12
   Quantity1	S
   SubTotal1	A	12
   PartName2	A	50
   UnitPrice2	A	12
   Quantity2	S
   SubTotal2	A	12
   PartName3	A	50
   UnitPrice3	A	12
   Quantity3	S
   SubTotal3	A	12
   PartName4	A	50
   UnitPrice4	A	12
   Quantity4	S
   SubTotal4	A	12
   PartName5	A	50
   UnitPrice5	A	12
   Quantity5	S
   SubTotal5	A	12
   JobPerformed1	A	50
   JobPrice1	A	12
   JobPerformed2	A	50
   JobPrice2	A	12
   JobPerformed3	A	50
   JobPrice3	A	12
   JobPerformed4	A	50
   JobPrice4	A	12
   TotalParts	A	12
   TotalLabor	A	12
   TaxRate	N
   TaxAmount	A	12
   OrderTotal	A	12
   Recommendations	M	240

3. To save the table, click the Save As... button (if you are using Corel Paradox, click the Create button)
4. Set the File Name to WorkOrders
5. Locate the Programs\College Park Auto Shop folder you created and display it in the Save combo box
    
   
6. Click Save
7. Return to the Database Desktop

Data entry consists of populating a table with the necessary values for its different fields. There are at least three techniques you can use to perform this operation: The Database Desktop, graphical objects created using a Borland C++ Builder application, or SQL.

Data Entry With the Database Desktop

To enter new data in a table using the Database Desktop, you can first open the table by clicking File -> Open -> Table... from the main menu and selecting the table. After selecting the desired table, you must first put it into edit mode. To do this, you can press F9 or click Table -> Edit Data from the main menu.

1. On the main menu of the Database Desktop, click File -> Open -> Table...
2. Locate the College Park Auto Shop folder you created and display it in the Look In combo box. Select WorkOrders.db and click Open
3. To put the table into edit mode, on the main menu of Database Desktop, click Table -> Edit Data
4. Create the following work order:
    

   CustomerName	Gertrude Monay
   Address	1488 East Acacia Rd
   City	Silver Spring
   State	MD
   ZIPCode	20906
   Make	Dodge
   Model	Neon
   CarYear	1998
   PartName1	A/C Compressor Clutch
   UnitPrice1	125.85
   Quantity1	1
   SubTotal1	125.85
   PartName2	Condenser Fan
   UnitPrice2	70.95
   Quantity2	1
   SubTotal2	70.95
   JobPerformed1	Changed the compressor clutch
   JobPrice1	54.50

5. Close the table and close the Database Desktop

Probably the best option for data entry is to create a Windows application using Borland C++ Builder. This allows you to add visual controls that are more indicative and user-friendly. Fortunately, Borland C++ Builder makes this process particularly easy so much that you don't even have to write a single line of code but you can create a completely functional user interface using rapid application development RAD.

1. Start Borland C++ Builder with its default form
2. To create a form ready for the database, on the main menu, click Database -> Form Wizard...
3. In the first page of the wizard, accept all defaults and click Next
    
   
4. In the second page of the wizard, in the Drive Or Alias Name combo box, select CPAS. Notice that the College Park Auto Shop folder where the table was saved becomes selected in the Directories list
    
   
5. In the Table Name combo box, select WorkOrders.db and click Next
6. In the third page, click the select all fields button
    
   
7. Click Next
8. In the fourth page of the wizard, click the Vertically option and click Next
    
   
9. In the fifth page, accept the Left default option and click Next
10. In the sixth page, accept the default option and click Finish
11. On the View toolbar, click the View Unit button
12. In the View Unit dialog box, select Project1.cpp and click OK
13. On the main menu, click Project -> Remove From Project...
14. In the Remove From Project dialog box, select Unit1.cpp
     
    
15. Click OK
16. To save the project, on the Standard toolbar, click the Save All button
17. Locate the main folder where you will save the project and display it in the Save In combo box
18. Click the Create New Folder button
19. Type CPAS1 and press Enter twice to display it in the Save In combo box
20. Change Unit1.cpp to WorkOrders and press Enter
21. Change the Project file to CPAS and press Enter
22. Change the form's Caption with College Park Auto-Shop
23. Execute the application to see the result