prerequisite fields. The latter contradicts the first normalisation rule - no repeating fields are allowed.
A typical example to illustrate the use of a relational database is that of student enrolments at the university. There will be a student table containing the student number (the key field), surname, name, year of first registration, activity status (currently registered or not) and current degree code (foreign key). Similarly, there will be a course table which contains course codes (key field), course names, level of course (1st year, 2nd year etc.), type of course (first semester, second semester, full-year, summer term), responsible department (code), number of credits, etc. There will be many other “primary” or entity tables such as lecturers, venues, lecture and tutorial groups etc. The power of the relational model is that it allows any type of relationship. E.g. in a small university, different students could be assigned individual tutors, who are senior students, for the various courses. This assignment (in effect, a relationship) could be stored in a “tutor assignment table” containing at least the following fields “tutored student” (foreign key = student number), “tutor” (foreign key = student
number), “tutored course” (foreign key = course code). This would be very difficult to
achieve in the network model. Note that it is quite possible, indeed very likely, that there will be many relationship tables with exactly the same fields (e.g. “students enrolled in course”
and “tutors assigned to course”; or “required courses for degree” and “optional courses for degree”).
7.3.4 The Object-Oriented Model
The object-oriented (OO) approach was introduced in the chapter on software. Remember that an object encapsulates both the data and the methods or operations that can be performed 82 [Free reproduction for educational use granted]
© Van Belle, Eccles & Nash
Section II
7. Databases
on the data. Although business databases were not initially considered to be good candidates for OO, more and more applications are benefiting from the use of object-oriented database management systems (OODMBS).
Two particular strengths of the OO model are the capability to handle multimedia more easily and the feature of inheritance. Multimedia, such as pictures or sound, are more easily handled by OODBMS because it is much easier to store the methods, or procedures, to access and edit the data within the object than it is to include those methods permanently as part of the DBMS. As such, OODBMS are popular for engineering and scientific applications. However,
product and human resource databases can equally benefit from picture and photograph fields.
The other advantage is that the OO model allows specialised objects to inherit object
attributes and methods from their parent. A banking example can illustrate this. A generic
“bank account” object would have the attributes of “owner details”, “account number”,
“balance”, “interest” etc. Specific types of accounts, such as home loans, cheque and saving accounts, inherit the common account attributes and methods. In addition, each account type would have its own specific attributes and methods.
BA N K A CCO U NT O BJEC T
A ttributes
§
Balance
§
Interest R ate
§
Custom er nam e(s)
§
Branch
§
Account num ber …
O perations / Methods
§
M ake a deposit
§
Do a w ithdrawal
§
Get custom er details
IN H ERITA N CE
of attributes and
m ethods
H O M E LO A N ACC O UN T
CHEQ UE A CCO UN T
O BJECT
O BJECT
Attributes
Attributes
§
M ortgage property details
§
O verdraft lim it
§
Term of account in years
§
Charge schedule
§
Statem ent details
§
Last cheque num ber used
§
Insurance prem ium (Rand)
§
Statem ent details
O perations / M ethods
O perations / M ethods
§
Print annual statem ent
§
Print m onthly statem ent
§
A llocate insurance
§
Calculate credit & debit
prem ium , interest charges
interest, cheque charges
§
Calculate new m onthly
§
O rder new chequebook
paym ent
7.3.5 Free-form databases
Another attempt to deal with unstructured data and information is the free-form database.
Textual (and sometimes multimedia) information can be entered in logical chunks and related to other pieces of information, often using hypertext principles. These databases feature powerful indexing and searching tools. Most free-form databases are stand-alone end-user Discovering Information Systems
83
7. Databases
Section II
applications but, in a way, one could consider the entire web to be one huge distributed free-form database. A free-form database differs from a knowledge-based database in that the
latter must be able to interpret the knowledge to allow for limited reasoning, inference, specialisation or inheritance.
7.4 Database management
In order for a database to provide and secure efficient data storage and retrieval, there must be careful control of what data is to be included and how it will be maintained, who has access to the database, and how errors can best be prevented or resolved.
Normally, a specialist job function will be created for a Database Administrator (DBA), the person who is responsible for looking after the computerised corporate database(s). The main responsibility of this person is to define the standard data elements and structure of the database (i.e. table and field structure). She will also look after the integrity and safety of the contents of the database, and may be responsible for the maintenance and fine-tuning of the DBMS software.
An important component of the DBMS is the data dictionary or data encyclopaedia. This describes in detail all data elements in the database, i.e. all data tables, field names (and synonyms or aliases), descriptions/definitions, field types (text, numeric values, monetary units, boolean, dates), field lengths, key fields, relationships and dependencies between fields and tables, etc.
The database approach distinguishes a number of different views or perspectives of the organisational data.
§ The physical view (or data description) of the data is concerned with where and how files, tables and records are stored. A large database may be split or distributed physically over different computers or storage devices; what may seem to be fields of the same
record may actually be stored separately; often used records or fields may be stored
differently to less frequently used data; various different indexing methods may be used to assist in locating records more quickly etc. The physical data view is the concern of the DBMS (and the DBA) only.
§ The logical data view or conceptual schema represents the conceptual structure of the data, as seen from an organisation-wide point of view. This would entail all the data fields that are required for each table, their data dictionary names and descriptions, their key fields and the relations between tables. System developers need to know what data is
available in order to design their system, and users need it in order to define their
information requirements. Most of this information is available in the DBMS’s data
dictionary.
§ The subschema represents the partial or limited view of the data by an individual user or application. A student marks processing application (or lecturer) should not be concerned with a student’s personal or financial details, hence the DBMS will only enable access to the academic record components of the student database.
84 [Free reproduction for educational use granted]
© Van Belle, Eccles & Nash
Section II
7. Databases
7.5 Database Architectures
Various models or architectures can be used to design an information system. This architecture is a decision that must be taken up front about the overall functioning of an information system. It affects the choice of hardware, software and the network configuration.
Although there are many ways to look at database architectures – indeed, the above database models are also an architectural decision – we will look at the decision which is related to where most of the data processing should take place
Most business systems have multi-user, networked databases. The user operates a workstation that requires access to the corporate database. The latter is normally located on a separate, larger computer. With the evolution in technology, specifically the increasing power of the user workstations, a decision has to be made about which computer will perform the bulk of the data processing.
7.5.1 Centralised processing
In a centralised processing environment, the entire processing load is performed by the central computer, usually a mainframe, to which a number of terminals are connected. This central and powerful computer is therefore usually referred to as the host computer. The host controls the data storage and the communication. The application program itself (e.g. a
booking system) also runs on the host computer, which performs the various checks and does all or most of the data formatting. The terminal itself does almost no data processing, apart from the most basic screen display formatting instructions.
Although this model was very popular in the early days of mainframe computing, it is now very much on the wane. It is still found in travel agents (airline booking systems), libraries and Computicket offices.
7.5.2 Distributed processing
In the distributed computing environment, there is no single host computer that controls all of the organisation’s data. All data resides on individual workstations or, more often, on the file servers of the LANs of the functional departments or local branches that actually need the data. These departments’ computers are then responsible for all the data processing. This is the exact opposite of centralised computing. Distributed processing is made possible by the development of powerful microcomputers with high-capacity hard disks.
In reality, there are two versions of distributed processing. The more common approach is to divide the entire corporate database in smaller partitions or sections. These partitions are then distributed on the local network or data servers across the organisations. An alternative approach is to keep local copies of the entire database on the different local servers. On a regular basis, usually during the night shift, the servers contact head office and co-ordinate their changes so that, by the next morning, all distributed databases reflect the changes made throughout the organisation.
As an example, a national bank could split its customer account database into regional
databases, perhaps one for each province. The transaction details for each account would be Discovering Information Systems
85
7. Databases
Section II
stored in the database of the province where each customer’s regular branch is located. Only when a customer performs a banking transaction at a branch outside her home province,
would there be a need for one regional database to make telecommunications contact with
another.
7.5.3 The client-server model
The client-server model is a compromise between the two extremes of centralised and distributed forms of processing. Here the processing workload is shared between a central computer and the local workstation.
Generally, the central computer is responsible for maintaining the database. It runs the database management system and performs all related functions such as backup, data
integrity, access control and security. This computer issues the required data to the various workstations that ask for it, and is therefore called the server. The local workstation, the client, receives the data from the server and is responsible for the presentation of the data. The term client can also refer to the software that resides on the local workstation i.e. the software responsible for communicating with the server and formatting the data for on-screen display and editing.
An ideal example of the client-server model is the World-wide Web on the Internet. Various web servers around the world hold data available for Internet users to see. Anyone connecting to the Internet gains access to the web by means of a web browser, which constitutes the client software. The web browser initiates the contact with the web server, who sends the requested data (a multimedia document in html format) to the client. The client’s web browser then interprets the various formatting instructions found in the document to present it in the manner most appropriate for the client computer.
Thin and fat clients
Depending on the amount of processing done by the client, we can distinguish between a thin and a fat client. A thin client does little else than the formatting and display of the data; the real business application software, as well as the data management function, is performed by the server. This is quite close to the old centralised processing model.
At the opposite end of the scale is the fat client, where the application software resides on the client computer. All data is processed and checked locally and only the final data records are transmitted back to the data server. The server runs only the database management system software; all application software is run on the client.
Two-tier versus three-tier
The client-server model described above is the traditional two-tier (or two-level) model, with one large server connected to many clients. In a large organisation, it may not be feasible to split the processing load between the server and the client. What can be done if the database is very large and needs to be accessed by many users but needs to be kept centralised? If the organisation’s applications are too complex to be delegated to the client, none of the above models will work. There is too much work for both the server and the client, but the need for 86 [Free reproduction for educational use granted]
© Van Belle, Eccles & Nash
Section II
7. Databases
one central database precludes the option of distributed processing.
This is where the case for a three-tier client-server model can be made. In the three tier model, an extra level (or tier) is inserted. At the highest level is the enterprise or central database server, which runs the data management system to perform all the pure data-related processing tasks. This server communicates with a number of intermediate, local servers that run the specific business applications. These servers are usually organised by functional department or geographic business unit. Finally, the client is responsible for the presentation and local editing of the data. (In some cases, more than three computers may be involved in data processing, in which case this is known as an n-tier model.)
A simplistic example is the situation of the inventory database of a large, national
organisation. Because this data is vital to most functional units within the organisation, it makes sense for the database to reside on one central data server. Each of the applications which makes use of this database, such as billing, receiving, stock control, etc. could be located on the various departmental servers located throughout the organisation. One of these servers might even be a web server that encodes all data in such a way that it can be made available via the internet to customers who wish to check stock availability. Finally, each user will interact from his personal computer with the application residing on his departmental server.
7.6 South African Perspective
The Automated Fingerprint Identification Systems (AFIS) is a mobile system connected to the police database in Pretoria, that can track down a person’s criminal record in a matter of seconds. The system is being used by police on the East Rand to identify suspected criminals, as well as to assist in recovering stolen cars. Assistant commissioner Gert de Lange
commented in a newspaper interview that by enabling police to check 100 cars
simultaneously, crime can be targeted with a minimum of inconvenience for motorists.
7.7 Beyond the Basics
Amazon.com has over 20 million customers and has revolutionized the bookselling business, becoming a household name almost overnight. Amazon.com succeeded because of the way it
presents information to customers, not just because it was one of the first companies to sell books via the Web. In fact, it would be difficult to label Amazon.com a bookseller or even a retailer. It has no brick-and-mortar stores, no warehouses, and little inventory of its own.
What Amazon.com does have is information (lots of it) about customers and what they've
purchased, driven by a foundation of very, very large databases. Amazon.com uses this
information to ensure that customers have a valuable online shopping experience. For
example, Amazon.com proactively recommends books to customers based on previous books
they've purchased, a profile they've filled out, and buying trends among customers who have similar profiles. The same information capacity is used to support sales of products such as CDs and clothes.
Amazon.com is an example of a new breed of company whose core business is information
and whose chief competitive advantage is the sophistication of its information systems.
Companies like Amazon.com are dramatically changing the business landscape because they
Discovering Information Systems
87
7. Databases
Section II
know how to integrate databases, operational systems, and the Web to cement relationships with their customers, suppliers, and partners.
7.8 Exercises
7.8.1 Database management system
Pete Anderson owns a small chemist’s shop in Wynberg, and recently replaced his cash tills with a PC-based sales system from Mikrotek Systems that makes use of a product price file, containing product codes, descriptions and prices, which is updated by his bookkeeper on a weekly basis.
Pete is so impressed by the accuracy and speed of this system, that he has been looking at other computer systems that might be suitable for his business. For a reasonable price he can buy a separate customer accounting system from Aardvark Computing, which will keep track of his customers’ account details and balances, and print monthly statements. As part of the same deal, Aardvark will also provide him with a stock control system, to be used for
ordering stock and keeping track of what is still available in his storeroom (i.e. has not been moved into the shop for display or sales).
As he explains it to you, stock orders would be entered on the stock system. When new stock is received, the quantity available in the store would be increased, and when stock is moved onto the shop floor, the quantity available in the store would be decreased. Below a certain stock level, new orders would be automatically generated.
The sales system accumulates totals of sales per day for each product, so that Pete will be able to identify fast-moving lines and move stock out of the storeroom as needed.
When a sale is made on account, the cashier will simply write the customer’s account number on a copy of the sales docket, which the bookkeeper will then enter into the customer accounting system the following day.
Pete’s son is studying IS, and says that it would be better to install a single integrated database system which combines all of his business data. Pete is not sure whether his son really knows what he is talking about, and has asked you to explain why using a database system would be even more efficient than the solution outlined above, for managing his stock, sales and
accounts.
• Describe three examples of how business activities and record keeping could be
enhanced by the use of an integrated database
• Outline two “queries” or reports that could be useful in managing the business, which combine data from two or more of the subsystems (stock, sales, customers).
• Design a set of database tables to be used in a new integrated database system that
includes stock, sales and customers. Base your solution on the empty tables provided
below.
Note: Pete needs to keep a record of account holders’ names, addresses and telephone
numbers. In addition, account sales to customers must be recorded in the database. Each sale 88 [Free reproduction for educational use granted]
© Van Belle, Eccles & Nash
Section II
7. Databases
should produce an invoice document which contains an invoice number, date, customer
details, and details of items purchased (item description, quantity sold and value (quantity x price)). Pete will also keep a record of every product that he stocks (item description, quantity in stock, price). To assist you in getting started, the optimal solution will consist of 4
tables as labelled below. Complete the exercise by entering all the required fields in the tables and drawing lines to indicate the links between the tables. You can indicate primary keys by putting a "P" at one end of the line linking the appropriate fields in the tables.
Customer Table
Stock Table
Invoice Table
Invoice Details Table
Discovering Information Systems
89
Case Study
Section II
CASE STUDY: GREENFINGERS GARDEN SERVICES
Refer back to the Greenfingers Garden Services case study that was introduced at the end of Chapter 3.
(a) Identify the business entities (people, objects or events) about which you would want to store data. List some of the data fields that would be appropriate for each of these entities, and their data type (eg text, number, graphic, etc).
(b) It is preferable to capture data as early in an automated business process as possible, since paper-based recording and later data capture can introduce human error. With this in
mind, who are the people who would be responsible for data capture in a new
computerised information system, and where and when should this data be captured.
(Hint: consider the point at which data is currently first recorded on paper.)
(c) Apart from a standard PC with keyboard and mouse for office use, when would other
devices such as PDAs or cellphones be appropriate, and what sort of interface would they use?
(d) The classification of applications by function distinguishes general-purpose applications, business applications, scientific applications and miscellaneous applications. Give
examples of how each of these application types could be used within Greenfingers
Garden Services to support business activities and planning.
(e) Communication of information between work teams, branches, consultants, clients and central administration is a major problem in the existing manual system. Use a diagram to show how a combination of data communication methods could support the exchange of
information between them.
(f) Suggest a suitable email address for the business, and list the ways in which email could be used to enhance business communications.
(g) How would the implementation of a DBMS make Alice Cooper’s business administration
easier?
<