Processing is the act of doing something (e.g. organising and manipulating) with data to produce output. Processing is at the centre of our model of an information system :
There are two distinct methods of processing data : and real-time processing. In a computerised information system most of the processing is carried out by a hardware device known as a microprocessor
Batch Processing Using batch processing input data is grouped together into batches. No data is processed until a complete batch has been put together. Then all the data in a batch is processed in one go. There may be a long time delay between data being input into the system and the data being processed. In this type of system the important data that the computer stores all the time is kept in a file called the master file. The data in the master file is sorted into order using one of the fields in the records in the file, known as the primary key field. The primary key field must uniquely identify each record in the file.
example of batch processing:
Transaction Processing (Real Time Processing) For some applications the master file needs to be kept up to date all of the time. For example in a travel agents whenever a seat is booked on a flight the number of seats that remain available on the flight must be reduced by one immediately. If this update was not done until the end of a day (as might happen in a batch processing system) then the flight could become overbooked. Transaction processing systems are used whenever the master file must be kept up to date. A transaction processing system operates like this : • When a transaction is entered it is placed in a queue of transactions waiting to be made. The transactions are processed in the order that they are placed into the queue. If there are many people using the system at the same time then there could be lots of transactions being made from different computer terminals. • The computer system will process one transaction at a time. Once the system starts processing a transaction it will not process any other transactions until the current transaction is finished. When a transaction is processed the master file is updated immediately. Therefore the master file is always kept up to date.
Transaction processing systems need to use direct access files. In a direct access file any record in the file can be updated directly, without having to read through all of the records that come before it in the file. Serial access media such as magnetic tape can not be used. The time required to find the record in the master file that a transaction related to would make processing incredibly slow. Transaction processing is interactive, i.e. processing takes place as a "conversation" between the user and the computer : The computer responds to the user’s input by outputting some data before the user can input any more data.
Several factors can lead to a poor database design -- lack of experience, a shortage of the necessary skills, tight timelines and insufficient resources can all contribute. In turn, poor database design leads to many problems down the line, such as sub-par performance, the inability to make changes to accommodate new features, and low-quality data that can cost both time and money as the application evolves.
Sin #1: Poor or missing documentation for database(s) in production
Documentation for databases usually falls into three categories: incomplete, inaccurate, or none at all. To make matters worse, it is almost never centralized in one place. Without proper, centralized documentation, understanding the impact of a change is difficult at best, impossible at worst. This causes developers, DBAs, architects, and business analysts to scramble to get on the same page. They are left up to their own imagination to interpret the meaning and usage of the data.
Sin #2: Little or no normalization
There are times to denormalize a database structure to achieve optimized performance, but sacrificing flexibility is terrible. Despite the long-held belief by developers, one table to store everything is not always optimal. The “one table” approach may make data access easier, but invariably there will be many NULLs for columns that do not apply to a record, and special application code will be needed to handle it. Another common mistake is repeating values stored in a table. This can greatly decrease flexibility and increase difficulty when updating the data.
Sin #3: Not treating the data model like a living, breathing organism
The sin arises when changes creep into the database due to critical production issues. Inevitably, the model is then left languishing on the side if there is not a process to update the model along with the database. As more changes occur in the database, the model becomes useless.
Undocumented data can also lead to security and compliance risks, poor understanding of future changes and the inability to adapt to future needs of the business. Although the design-then-build practice may be a utopia that is never reached, the changes need to find their way back to the model.
Sin #4: Improper storage of reference data
Two main problems with reference data. It is either stored in many places or, even worse, embedded in the application code. It is almost never captured in the data model with the rest of the documentation. This causes huge problems when a reference value needs to change. Reference values provide valuable documentation which should be communicated. Your best chance is often via the model. It may not be practical to store reference values in the data model if you have large volumes, but there is no excuse not to point to them from the model. The key is to have it defined in one place and used in other places.
Sin #5: Not using foreign keys or check constraints
Customers always complain all the time about the lack of referential integrity (RI) or validation checks defined in the database when reverse engineering databases. For older database systems, it was thought that foreign keys and check constraints slowed performance, thus, the RI and checks should be done via the application. This might have been the case in the past, but DBMSs have come a long way.
Sin #6: Not using domains and naming standards
Domains and naming standards are probably two of the most important things you can incorporate into your modeling practices. Domains allow you to create reusable attributes so that the same attributes are not created in different places with different properties. It is extremely important to have a common set that everyone can use across all models. Naming standards allow us to clearly identify those attributes consistently.
Having a set of standards also ensures consistency across systems and promotes readability of models and code. You don’t want short, cryptic names that users need to interpret. Given the advanced nature of the latest vendor releases, the days of limited column length name are over when building new databases. Always have a common set of classwords to identify key types of data and use modifiers as needed.
Sin #7: Not choosing primary keys properly
The simplest principle to remember when picking a primary key is SUM: Static, Unique, Minimal. It is not necessary to delve into the whole natural vs. surrogate key debate; however, it is important to know that although surrogate keys may uniquely indentify therecord, they do not always unique identify the data. There is a time and a place for both, and you can always create an alternate key for natural keys if a surrogate is used as the primary key.
Arca Databases are stored in a single file. The two main structures of a database are TABLES and INDEXES.
Tables are the structures that store your data in the database. Each table is composed of a number of FIELDS, also known as COLUMNS insome database engines. Suppose you want to store information about the users of your application: you could create a table named USERS, with the following fields: NAME, AGE and ADDRESS. Information about each user would be stored in what we call ROWS or RECORDS in the database. Your database table could be represented by the image below, containing records for 3 users:
It is important to learn that each field in the database has a TYPE associated with it. Common types are TEXT, NUMERIC and BLOB. Some database engines are very strict about the type of data that can be stored in a field, even limiting the number of characters by using types as CHAR[20]. Arca however uses a loosely typeless database engine, so the type information is only used as a hint about the contents of each field, to aid the conversion to Lingo types when you retrieve your data. It is entirely possible to store any kind of data supported by Arca in any field of the database, regardless of the declared TYPE for each field. You can even create your own types, or leave the type property blank when creating your tables. This is a feature inherited from the SQLite engine used in Arca. We however recommend that you use the TEXT type to store Lingo strings, the NUMERIC type to store floats and integers, and the BLOB type to store media and pictures (binary data.) This will make it easier for other developers working with your database to identify immediately which type of data was intended to be stored on each field, and it will give the Xtra a hint on how to convert the data back to Lingo. This can be important if you want to interpret numeric data as a string. Take for example the value "20.67". If you store this value on a field of type NUMERIC the Xtra will assume that you want to interpret it as a numeric Lingo value, and will return the float 20.67 in the results. If however you store the same information in a field of type TEXT the Xtra will return this result as a Lingo string, "20.67".
Field names and types are defined when you create a table. But in order to create a table you must first create the database file, of course. Database files can be created with the createdb function in the Arca Xtra, or the Arca Database Browser application. There is no need to specify a specific 3 letter extension for the database filename, but you may specify one if you want to. The following script will create an unencrypted database file named "mydata" in the same directory of the current Director movie:
Arca 2 introduces support for encrypted database files. Files can be encrypted or decrypted at any time using the changekey function, but you can also specify an optional encryption key when creating the database:
Databases are empty when they are created: they have no tables with data stored, or indexes. It is possible to create tables and indexes using Lingo as well, but this requires knowledge of the SQL language. It is generally much easier to use the Arca Database Browser application to create your database and design the tables and indexes, however. For reference, you can create the users table described above using Lingo or JavaScript syntax with the following command:
gDB.executeSQL("CREATE TABLE users(name text, age numeric, address text)")
We however recommend using the Arca Database Browser application, as it provides wizards that help you define the table fields and types before creation. If you have not downloaded it yet, please get the Arca Database Browser from the DOWNLOAD section at http://xtras.tabuleiro.com. All screenshots of data contained in this guide were taken with it. The database browser tool is available in versions for Windows, MacOSX and Linux.
Creating a database with the Arca Database Browser is simple: just select the CREATE DATABASE menu entry. Choose a name and location for your database file. The browser will open the CREATE TABLE wizard, where you can define the name of your table, and click the ADD button to define fields and types.
The Arca Database Browser also lets you create indexes, with the CREATE INDEX button. Indexes do not store data, and you do not use them directly. They are used internally by the database engine to speed up certain search operations. In order to create an index you have to define the table and the field to be indexed, and the indexing order (Ascending or Descending). Indexes can also be UNIQUE, and in this case the indexed field does not allow duplicate data to be inserted in different records or rows (for example you could not have two employees with the same userid value if the userid field is being indexed as UNIQUE.) The figure below shows the Create Index window in the Arca Database tool:
Of course, you can create indexes using scripting as well. Remember that almost all operations in Arca can be done using the executeSQL command. The syntax to create the index above would look like this:
gDB.executeSQL("CREATE INDEX idx1 ON users(age ASC)")
For most Director applications however indexes are really not necessary: Arca is very fast and optimized, and it already maintains an internal index using a special property called ROWID (more on this later.) You may need to create an index however if your table contains lots of rows (10,000 for example), and you always search for data using an expression that will benefit from an index. In our example it would make sense to index the table using the AGE field if your application usually tries to find users that are older than 18, or present a list of users sorted by age.
A tip: the Arca Database Browser application also lets you see the database structure, including all tables and indexes, and also the SQL commands that were used internally to create them, as you can see in the figure below. You can study the SQL commands used (also called the schema of the database) if you want to recreate tables or indexes later using Lingo, or to learn a bit more of SQL.
An Arca database may have several tables and indexes, depending on your needs. All data is stored in the same database file, no matter how many tables you use. Let's now check how to insert, delete and update data in your databases.
This blog was created on 10/1/2011 as requested by our beloved lecturer, Pn. Sharin Hazlin Huspi. She is our lecturer for the subject of Technology and Information System. We have five members it our group. We are Chin Chin, Wenhui, Poh Cai, Norhildayu and Nurhamiza. We are all students of University Technology Malaysia from the Faculty of Management and Human Resource Development (FPPSM). While, our group has a name called IT-evolution. The idea of our group name was inspired by the dynamic changes of Information Technology in today world. By blogging, we are hoping that we could understand this subject more in-depth. Simultaneously, we would like to share some of our opinion regarding on the topic which will be covered week by week.
