Software System Construction:Business data implementation

10.3 Business data implementation

The logical database design developed in chapter 9, Technical Design, will be implemented into MS Access without change, i.e., the logical design and the physical design will be the same. Although Access has sophisticated reporting and form-building facilities we will not use these, as the Ticket Manager application will be delivered purely for the Internet, with the assumption that any internal accesses will be made via an Intranet. Input forms and reports will instead be generated using ColdFusion. Where more sophisticated web interfaces are needed, such as a theatre seating plan, then the recommended approach would be to use a 'heavier' programming language such as Java or C++ (which are outside the scope of this book). We will therefore be concerned purely with the basic Access facilities of database building and query generation.

Although there are numerous types of Access objects, we are concerned only with tables, fields (the columns in a table), queries, and indexes. To avoid

confusion it helps greatly to use a naming convention for Access objects (table 10.1). Agreeing naming conventions before work commences is essential to the development of code that is maintainable; it helps the original programmer and is essential when maintaining code developed by someone else.

10.3.1 Defining tables

The physical database implementation is a simple copy of the physical design in chapter 9 with the naming conventions added for tables and fields, including the seatHoldTime attribute in the SeatAtPerformance table needed to support the seat locking strategy:

tblProductionType fldProductionTypeID fldProductionType tblLocation fldLocationID fldLocation tblTheatre fldTheatreID fldTheatreName fldWebSiteURL fldLocationID Foreign: fldLocationID → tblLocation tblProduction fldProductionID fldProductionTitle fldVideoClip fldProductionTypeID fldTheatreID Foreign: fldProductionTypeID → ProductionType Foreign: fldTheatreID → tblTheatre tblPerformance fldPerformanceID fldPerformanceTime fldProductionID Foreign: fldProductionID → tblProduction Unique: fldProductionID, fldPerformanceTime tblPartOfTheatre fldPrtOfTheatreIDfldTheatreID fldPartOfTheatreDesc fldMaxSeatingCapacity Foreign: fldTheatreID → tblTheatre Unique: fldTheatreID, fldPartOfTheatreDesc tblSeat fldSeatID fldPartOfTheatreID fldRowNumber fldSeatNumber fldSeatStatus Foreign: fldPartOfTheatreID → tblPartOfTheatre Unique: fldPartOfTheatreID, fldRowNumber, fldSeatNumber tblPriceList fldPriceListID fldPartOfTheatreID fldProductionID fldTicketPrice fldPriceCurrency Foreign: fldPartOfTheatreID → tblPartOfTheatre Foreign: fldProductionID → tblProduction Unique: fldPartOfTheatreID, fldProductionID tblTheatreClub fldTheatreClubIDfldClubName fldMembershipFee fldClubDiscountPercent tblCustomer fldCustomerID fldName fldAddress (o) fldTelephoneNumber fldEmail tblMember fldCustomerID fldDateJoined fldUserID fldPassword fldTheatreClubID Foreign: fldTheatreClubID → tblTheatreClub Foreign: fldCustomerID → tblCustomer Unique: fldUserID tblicketTransaction fldTicketTransactionIDfldTicketTransactionTime fldCreditCardType fldCreditCardNumber fldCreditCardExpiryDate fldTransactionTotalAmount fldTransactionCurrency fldCustomerID Foreign: fldCustomerID → tblCustomer

tblSeatAtPerformance fldSeatAtPerformanceID fldPerformanceID fldSeatID (0) fldTicketTransactionID fldPriceAchieved seatHoldTime Unique: fldSeatID, fldPerformanceID Foreign: fldTicketTransactionlD → tblTicketTransaction Foreign: fldSeatID → tblSeat Foreign: fldPerformanceID → tblPerformance

The first step is to open up Access and create a blank database, theatre.mdb. Once the empty database has been created then tables can be added. Let's start by adding the table tblTheatre (figure 10.2). The primary key field, fldTheatreID, is denoted by the key symbol and is of type 'AutoNumber'. Fields of this type are incremented automatically by the database every time a new record is added. This is useful as it ensures that every record has a unique identifier and it saves the developer the work of finding out the last value of the field fldTheatreID and then incrementing it before adding a new record.

The remaining fields in tbl Theatre are given data types as appropriate: theatre name is text, but location ID is a number as it will refer to the key field in the Location table. The key field in the Location table will be of the type autoNumber, as is the case with all the tables in the database, and is therefore numeric.

Some of the tables require that unique indexes be added to ensure the integrity of the database. For every UNIQUE clause in the entity and attribute list there should be a corresponding unique index in Access. For example, tblPerformance has a primary key of fldPerformanceID. This field is indexed automatically by Access since it is the primary key and must be unique (figure 10.3). In the interests of system performance, Access will automatically index fields that are foreign keys, such as fldProductionID. However, Access does not know that the combination of fldProductionID and fldPerformanceDate must be unique. We ensure that duplicates cannot be added to the database by adding a unique index, idxProdPerf, to the table tblPerformance.

All of the tables in the TicketManager are added in similar fashion. In practice, the development team may be using a computer-aided software engineering (CASE) tool, such as Computer Associates' ERWin or Rational Rose for UML (see appendix C). Many software development toolsets will generate SQL statements automatically from the graphical database design, thus enabling a seamless transition from design to construction.

10.3.2 Defining relationships

We have dealt with the object identifiers that ensure uniqueness through the primary key and have addressed the data integrity aspects of the UNIQUE clause through adding unique indexes. But, how does Access know about the FOREIGN clauses in the entity and attribute list? By using the relationship  window we can define the relationships visually using drag and drop. One benefit of this approach is that Access will add indexes automatically to improve performance. A more significant benefit is that it allows referential integrity rules to be defined. Referential integrity rules will ensure that, for example, a performance cannot be added to a non-existent production. It will allow a rule to be established so that a production cannot be deleted without all the performances for that production having been deleted (cancelled) first. Figure 10.4 shows a subset of the relationships in the TicketManager application.

The '1' symbol and the infinity symbol represent one-to-many relationships. By right-clicking on the relationship line the properties can be examined (figure 10.5). Figure 10.5 shows the relationship between the Production and Performance tables. The field has the same name in both tables (this is not a requirement but it is a good guideline) and the 'enforce referential integrity' option is ticked. This will ensure that all performances are associated with a production that exists in the Production table.

Notice in figure 10.5 that it is possible to specify a referential integrity option for update and delete. If a production is deleted and the option box to cascade delete related records is not checked then the delete will fail if there are any related records. For example, if a production has performances scheduled then it would be necessary to delete the performances first. As some of these performances might already have seats booked it would then be necessary to delete the bookings before the performance could be deleted. If the cascade delete option has been checked in all the subsidiary tables then a single delete of a production will cascade down and delete all the performances and all the bookings for that production - a powerful facility indeed and one to be used with caution.

10.3.3 Subtypes

The entity Member is a subtype of entity Customer. These are recognized as one-to-one relationships by Access because they share the same primary key, fld CustomerID (figure 10.6), and can therefore only be associated on a one to one basis. Using a query we can join Customer to Member to get a list of members and their attributes. Such a query would ignore Customers who are not members as these records would not have a corresponding record in the Member table. This query represents a user view of the database, a view in which only members exist and the user of the view is unaware of the fact that the data is actually stored in the database as two separate tables.

10.3.4 Populating the database

The database design is implemented directly from the entity and attribute list, with each UNIQUE clause becoming a unique index and each FOREIGN clause being defined as a relationship to Access with appropriate referential integrity rules. The end result is a set of empty data structures. The next task is to populate the database with test data, e.g., a few theatres, productions, and performances. In the early stages of development small volumes of data with high variety are needed to test and develop the application. Later on, larger volumes of data for system testing will be needed. To avoid having to re-key the data in every time the database design changes (and it will) it is worth investing some time in building a test data bank that can be imported into the empty database structure as needed. A little bit of effort in the early stage will save a lot of time later on.

10.3.5 SQL and queries

SQL can be used to build the theatre database structure using the data definition language (DDL) to CREATE tables. Typically, DDL will be generated automatically by the developer's toolset (e.g., ERWin), thus avoiding the need to hand-build the database. This is usually a better way to build a database since it allows the database to be deleted and rebuilt from SQL DDL statements at a single stroke. As anyone who has hand-built an Access database will know, rebuilding the database each time the design changes is a labourintensive and error-prone activity. The SQL DDL statement that could be generated by a development toolset (or written by hand) to add the location table is as follows:

CREATE TABLE tblLocation (fldLocationID INTEGER NOTNULL, fldLocation CHAR(40) NOT NULL, PRIMARY KEY (fldLocationID)

SQL is also used as a data manipulation language (DML), to add, change, and delete records in the database and provide read access. Having established a database we will want to access the data in different ways. Consider a requirement to list all of the productions by theatre. This can be written as an Access query, such as qryListTheatre Production. The SQL code can be written by hand or generated through an Access query wizard. Either way, a SQL SELECT statement will be the result:

SELECT fldTheatreName, fldWebsiteURL, fldProductionTitle FROM tblTheatre, tblProduction WHERE tblTheatre.fldTheatreID = tblProduction.fldTheatreID ORDER BY fldTheatreName, fldProductionTitle In the above example we have performed a join of the Theatre and Production tables where only those Theatres with one or more productions will be output by the query. This is known as an inner join. The join condition is specified in the WHERE clause, which is used to match the column values in one table with the column values in another table. This query can be thought of as traversing the relationship from Theatre to Production. Joins can involve many tables and can be written in different ways. The above example is intended as a simple illustration only. Remember that it is a requirement of the relational model that the rows in a table are not in any pre-existing order. To get the output in the sequence of theatre name the ORDER clause is used (table 10.1).