Case Studies in Database Design and Implementation

Peter R. Whitehouse

Subject Coordinator, Information Technology Education
St. Joseph's College,
Gregory Terrace, Brisbane

p.whitehouse@mailbox.uq.edu.au

Database system design and implementation are complex art forms. A major element of the Board Of Senior Secondary School Studies (BSSSS) subject 'Information Processing and Technology' (IPT) is relational database design methodologies. The syllabus concentrates on robust methodologies such as Object-Role Modelling (ORM).

It would appear a natural progression from database design studies coupled with extensive work using relational database languages to System Development. This assignment puts into practice that which students learn theoretically, in a contextually relevant manner. This assignment also provides students with real experience in planning, time lining, cooperating within a group, delegating and communicating on many levels.

Whilst St. Joseph's College, a private catholic boys college, has no entrance pre-requisites, it is generally the more able students who elect to study IPT. Saying that, however, we get a diverse range of student ability participating in this complex discipline.

Students completing this assignment have usually previously completed a major algorithmic languages system development assignment whilst in Year 11

As a MAJOR assessment item (15% of Exit), students are required to develop a relational database system for a target audience. In addition, they are required to provide user and technical documentation to support their product.

The assignment is divided into a number of discrete PHASES. Each phase must be completed, corrected and returned before the next phase is to be attempted. This phasing (or staging) of the assignment forces students to consider a production time line. Failure to keep to the phase time line incurs mark penalties for completion of that phase. Students unable to complete a phase are welcome to forego the marks in order that the phase is completed for them (in consultation with their teacher).

This assessment instrument involves the student in the many and varied tasks that typify system development, working either singly or in groups of up to 3. Considerable class time is devoted to this assignment, and it is expected that students at least match the amount of class time with their own time (most however choose to use more of their own time than has been allocated in class).

Student groups select a topic of interest, either from the list of suitable topics presented with the item or one of their own devising. The major concession they have to make is that a topic must be selected such that there is an 'expert' in that field available for interview/consultation. Groups then progress sequentially through the following PHASES on their way to a completed database system.

The Formal Proposal requires students to informally specify their proposed system. During this phase, they develop their understanding of how the system might look, what services may be offered within the system and the general 'look and feel'. To do this, students draw on their experience of windows applications, previous assignments and a little common knowledge.

This phase requires students to identify their target audience - this will be important later as they produce their View Definition and Menu system, as well as their User Documentation.

Also required at this stage are examples of 'primary data sources'. If a group, for example, decided to develop a system on 'public transport', they would need to collect timetables, ticket stubs and other physical data sources - these become the basis of the database design stage coming up next. If students have difficulty finding 'primary data sources', they are generally discouraged from pursuing the system. Students that are going to propose an 'unknown' topic are generally asked to pre-propose (ie. sound out the idea informally BEFORE the proposal hand in). Since the database design process is fact-based, it is important to determine whether the students have access to significant primary data, and/or can interview a 'expert' for their system - experience has shown that students need such support to model their systems successfully.

Elementary Sentences, or 'Fact Types' are formulated using ORM (Object-Role Modelling) as the next phase of the assignment. Each object to be used in the system is formally defined (in a data dictionary), and all relationships are listed and instantiated. Using the 'primary data sources', students identify what will be stored in the system, and the relationships that bind them.

Although this is generally the most important step in the ORM process, students find it difficult to 'tease' elementary (non-reducible) fact types from real-world examples and often require considerable help doing so.

A Conceptual Schema is then drawn, including as complete a picture of constraints and inter-fact type relationships as is possible. Students use a CASE (Computer Aided Software Engineering) Tool to complete this process - Asymetrix InfoModeler, as the schema formation process is intensely graphical (and impractical in other graphics packages).

Table Design algorithms are applied to the corrected Conceptual Schema to arrive at a relational table designs. This stage requires students to define tables, keys and inter-table relationships, producing tables in ONF (Optimal Normal Form). A revised data dictionary is required, with sensible field names ensuring column types reflect system requirements, ensure union compatibility and naming consistency for like fields between tables.

The algorithm for producing tables in ONF from a conceptual schema is simple, and provides students with little difficulty. Using the CASE Tool, students can verify their manually generated relational table designs - I force students to manually apply the algorithm to their own diagram, it appears to provide them with a better basis to later apply 'controlled redundancy' (the balance between the amount of redundancy with the number and complexity of their tables) and it forces them to constantly evaluate their model against their proposal.

The View Definition and Menu System provides students many challenges and rewards. Using MS Access, students create tables, forms, queries, reports and macros necessary to present database services to the end-user. The relational table structure and inter-table relationships defined in the previous phase are the basis of the system, with the GUI interface generally representing their proposal (although, usually by this stage, students have a better idea of what the system can look like, and the interface undergoes significant refinement during this stage).

Emphasis in this phase is providing consistent user-service. Students agonize over colours and fonts, graphics and layout of fields with varying degrees of success.

Documentation throughout this assignment is an ongoing process. Since all phases are computer generated, they all contribute (in part) to the final documentation. Students are required to provide documentation on two levels:

• User documentation involves language aimed at the end-user of the system. The 'user manual' includes instructions for use, system application and limitations.
• Technical documentation requires a more formal form of communication, suitable for another system developer. Students include much of the previously corrected system design phases, and other documentation describing system structure, such that another developer could take this system further without the massive lead time required to internalize the function of other 'legacy' systems.

Students have the option to produce either a traditional word processed manual, or more recently, an HTML web or Windows Help file. These must, however, be printed to complete BSSSS requirements for Exit.

Students

Students begin this item in Year 12, after approximately 2 terms of relational database language studies and in conjunction with relational database design studies. By this stage, students have had extensive experience manipulating existing databases using SQL, simple table creation and an understanding of the relational model of data. The assignment is timed to coincide within 2 weeks to actual content coverage in class and complements the theoretical work being done in class. Experience has shown that students will believe they can tackle anything, given enough time. I, on the other hand, would prefer to equip them well first so their progress through the project is as independent and predictable as possible due to the complexity of the development process and the intensity of requests for assistance.

Central to the success of this assignment is the rate of knowledge acquisition students are presented with. As this assignment 'mirrors' the development of the topic in class, content is guaranteed to precede 'need' by at least 2 weeks, usually longer. This is seen as advantageous as students not only learn about the database design process, but are forced to put that theory into practise immediately for marks. Prior to the beginning of the assignment, students have already completed a MS Access Tutorial and are familiar with some of the capabilities of the RDBMS (Relational DataBase Management Systems). They are also very familiar with the relational database language SQL (with extensive practicum covering Micro SQL and MS Access SQL)

The Elementary Sentences require students to find 'Primary Data Sources' - it is rare that students would be given the go-ahead if they had difficulty producing this. Students are, however, usually surprised how abundant such data sources are once they start to look for them. The success of ORM relies on the quality of these sources, and knowledge of how they all fit together. To this end, it is usually suggested that students choose systems with an appropriate 'expert' available for interview

Elementary Sentences are then entered into InfoModeler and Conceptual Schema fragments are formed and linked. InfoModeler provides an intuitive graphical environment for 'massaging' schema fragments into a clear and coherent model.

MS Access is used as the platform for system development for a number of reasons:

• Cost: It commonly comes 'bundled' with MS Office, and integrates satisfactorily with that suite of programs
• Ease of developing simple systems: for the sorts of systems general students want to develop, Access is ideal for 'quick' interface creation and underlying data management. It is when students want to do something out of the 'ordinary' that Access proves difficult, with students being forced to descend into Access BASIC modules instead of Macros and simpler structures. Access handles graphics and other media satisfactorily, and provides a relatively large collection of layout tools for creating and controlling on-screen forms and reports.
• Query Capability - the Access query engine uses MS SQL (a curious 're working' of ANSI SQL), and allows the majority of the SQL learned by students earlier in the year to be used 'as is'. My students generally avoid the 'quirky' QBE (Graphical Query By Example) in favour of 'raw' SQL statements.

The following marking scheme (and weightings) have been applied to the assignment:

Phases 1-4

The 'design' phases of the assignment are assessed using COMPLETION marks - that is, students are given a set of instructions and a deadline. Should they decide to complete the phase as per the instructions by the due date, they are awarded ALL of the marks for that phase. Should they decide not to do the above, then they forfeit the marks but must complete the phase anyway.

Phases 5&6

The 'implementation' phases of the assignment are CRITERIA BASED as shown above - here students are assessed on how well they completed each of the required elements in system building and documentation.

Group Work

Each student completes a PEER ASSESSMENT form on the other member(s) of the group. The form attempts to identify strengths, weaknesses and relative contribution to the various stages of the assignment made by each group member. That contribution is then used to allocate final marks to the assignment. Since close teacher monitoring has taken place, realistic peer evaluation is monitored, and mediated.

Examples