Database Programming

If commands are executed within a transaction, the commitwill make the changes made by the commands permanent. The commands can also be undone using a rollback or by closing the session without an explicit commit.

For example;

begin transaction

go

update customer set Balance = Balance * 0.15

update product set OnHand = 0

delete vendor

go

commit transaction

If the process gets as far as the commit, then all of the commands will happen. If something goes wrong, such as the server rebooting, then any changes will be undone.

The ANSI/SPARC architecture is divided into three levels, known as the internal, conceptual, and external levels.

The internal level is the one closest to physical storage, i.e., it is the one concerned with the way the data is physically stored.

The external level is the one closest to the users i.e., it is the one concerned with the way the data is viewed by individual users.

The conceptual level is a "level of indirection" between the other two.

If the external level is concerned with individual user views, then the conceptual level is concerned with a community user view. In other words, there will be many distinct external views, each consisting of a more or less of a abstract representation of some portion of the total database, and there will be precisely one conceptual view, consisting of a similarly abstract representation of the database in it's entirely. Likewise, there will be precisely one internal view, representing the total database as physically stored. When we describe some representation as abstract here, we merely mean that it involves user-oriented constructs such as logical records and fields instead of machine-oriented constructs such as bits and bytes.

The external level: The external level is the individual user level. A given user can be either an application programmer or an end user of any degree of sophistication. The DBA is an important special case. Unlike other users, however, the DBA will need to be interested in the conceptual and internal levels also.

The conceptual level: The conceptual view is a representation of the entire information content of the database, again (as with an external view) in a form that is somewhat abstract in comparison with the way in which the data is physically stored. It will also be quite different, in general, in the way from which any particular user views the data. Broadly speaking, the conceptual view is intended to be the view of the data "as it really is", rather than as users are forced to see it by the constraints of the particular language or hardware they might be using.

The internal level: The third level of the architecture is the internal level. The internal view is a low level representation of the entire database; it consists of many occurrences of each of many types of internal record. "Internal record" is the ANSI/SPARC term for the construct that we have been calling a stored record.

Batch processing is not transaction processing. Batch processing involves processing several transactions at the same time, and the results of each transaction are not immediately available when the transaction is being entered;[1]there is a time delay. Transactions are accumulated for a certain period (say for day) where updates are made especially after work.

There are a number of differences between real-time and batch processing. These are outlined below:

Each transaction in real-time processing is unique. It is not part of a group of transactions, even though those transactions are processed in the same manner. Transactions in real-time processing are stand-alone both in the entry to the system and also in the handling of output.

Real-time processing requires the master file to be available more often for updating and reference than batch processing. The database is not accessible all of the time for batch processing.

Real-time processing has fewer errors than batch processing, as transaction data is validated and entered immediately. With batch processing, the data is organised and stored before the master file is updated. Errors can occur during these steps.

Infrequent errors may occur in real-time processing; however, they are often tolerated. It is not practical to shut down the system for infrequent errors.

More computer operators are required in real-time processing, as the operations are not centralised. It is more difficult to maintain a real-time processing system than a batch processing system.

Generally, ERD's look like this:

adapted from another professor.

Developing an ERD

Developing an ERD requires an understanding of the system and its components. Before discussing the procedure, let's look at a narrative created by Professor Harman.Consider a hospital:Patients are treated in a single ward by the doctors assigned to them. Usually each patient will be assigned a single doctor, but in rare cases they will have two.Heathcare assistants also attend to the patients, a number of these are associated with each ward.

Initially the system will be concerned solely with drug treatment. Each patient is required to take a variety of drugs a certain number of times per day and for varying lengths of time.The system must record details concerning patient treatment and staff payment. Some staff are paid part time and doctors and care assistants work varying amounts of overtime at varying rates (subject to grade).The system will also need to track what treatments are required for which patients and when and it should be capable of calculating the cost of treatment per week for each patient (though it is currently unclear to what use this information will be put).

How do we start an ERD?

1. Define Entities: these are usually nouns used in descriptions of the system, in the discussion of business rules, or in documentation; identified in the narrative (see highlighted items above).

2. Define Relationships: these are usually verbs used in descriptions of the system or in discussion of the business rules (entity ______ entity); identified in the narrative (see highlighted items above).3. Add attributes to the relations; these are determined by the queries,and may also suggest new entities, e.g. grade; or they may suggest the need for keys or identifiers.

What questions can we ask?a. Which doctors work in which wards?b. How much will be spent in a ward in a given week?c. How much will a patient cost to treat?d. How much does a doctor cost per week?e. Which assistants can a patient expect to see?f. Which drugs are being used?4. Add cardinality to the relations

Many-to-Many must be resolved to two one-to-manys with an additional entityUsually automatically happensSometimes involves introduction of a link entity (which will be all foreign key) Examples: Patient-Drug5. This flexibility allows us to consider a variety of questions such as:a. Which beds are free?b. Which assistants work for Dr. X?c. What is the least expensive prescription?d. How many doctors are there in the hospital?e. Which patients are family related?

6. Represent that information with symbols. Generally E-R Diagrams require the use of the following symbols:

Reading an ERD

It takes some practice reading an ERD, but they can be used with clients to discuss business rules.

These allow us to represent the information from above such as the E-R Diagram below:ERD brings out issues:Many-to-ManysAmbiguitiesEntities and their relationshipsWhat data needs to be storedThe Degree of a relationship

Now, think about a university in terms of an ERD. What entities, relationships and attributes might you consider? Look at this simplified view. There is also an example of a simplified view of an airline on that page.You can investigate more about ERDs by viewing these sources available on the Internet:E-R Diagrams, Tables and their Meaning

Entity-Relationship Diagrams

Entity-Relationship Diagrams

Entity-Relationship Diagrams

4.1 Lecture: Entity Relationship Analysis

Five Entities related to Agent

Crosswalk Exercise - ALMRS Customer with the Standard

Guidelines

Domain Analysis

ERDIAG

STRUCTURE OF DBMS

DBMS (Database Management System) acts as an interface between the user and the database. The user requests the DBMS to perform various operations (insert, delete, update and retrieval) on the database. The components of DBMS perform these requested operations on the database and provide necessary data to the users

1. DDL Compiler -

Data Description Language compiler processes schema definitions specified in the DDL. It includes metadata

information such as the name of the files, data items, storage details of each file, mapping information and constraints etc.

2. DML Compiler and Query optimizer -

The DML commands such as insert, update, delete, retrieve from the application program are sent to the DML compiler for compilation into object code for database access. The object code is then optimized in the best way to execute a query by the query optimizer and then send to the data manager.

3. Data Manager -

The Data Manager is the central software component of the DBMS also knows as Database Control System.

The Main Functions Of Data Manager Are: --

• Convert operations in user's Queries coming from the application programs or combination of DML Compiler and Query optimizer which is known as Query Processor from user's logical view to physical file system.

• Controls DBMS information access that is stored on disk.

• It also controls handling buffers in main memory.

• It also enforces constraints to maintain consistency and integrity of the data.

• It also synchronizes the simultaneous operations performed by the concurrent users.

• It also controls the backup and recovery operations.

4. Data Dictionary -

Data Dictionary is a repository of description of data in the database. It contains information about

• Data - names of the tables, names of attributes of each table, length of attributes, and number of rows in each table.

• Relationships between database transactions and data items referenced by them which is useful in determining which transactions are affected when certain data definitions are changed.

• Constraints on data i.e.

range of values permitted.

• Detailed information on physical database design such as storage structure, access paths, files and record sizes.

• Access Authorization - is the Description of database users their responsibilities and their access rights.

• Usage statistics such as frequency of query and transactions.

Data dictionary is used to actually control the data integrity, database operation and accuracy. It may be used as a important part of the DBMS.

5. Data Files - It contains the data portion of the database.

6. Compiled DML

- The DML complier

converts the high level Queries into low level file access commands known as compiled DML.

A database system is composed of four components;

• Data

• Hardware

• Software

• Users

which coordinate with each other to form an effective database system.

1. Data -

It is a very important component of the database system. Most of the organizations generate, store and process 1arge amount of data. The data acts a bridge between the machine parts i.e.

hardware and software and the users which directly access it or access it through some application programs.

Data may be of different types.

•

User Data -

It consists of a table(s)

of data called Relation(s)

where Column(s)

are called fields of attributes and rows are called Records for tables. A Relation must be structured properly.

•

Metadata

-

A description of the structure of the database is known as Metadata.

It basically means "data about data". System Tables store the Metadata

which includes.

- Number of Tables and Table Names

- Number of fields and field Names

- Primary Key Fields

•

Application Metadata

-

It stores the structure and format of Queries, reports and other applications components.

'

2. Hardware -

The hardware consists of the secondary storage devices such as magnetic disks (hard disk, zip disk, floppy disks), optical disks (CD-ROM),

magnetic tapes etc. on which data is stored together with the Input/Output devices (mouse, keyboard, printers), processors, main memory etc. which are used for storing and retrieving the data in a fast and efficient manner. Since database can range from those of a single user with a desktop computer to those on mainframe computers with thousand of users, therefore proper care should be taken for choosing appropriate hardware devices for a required database.

3. Software -

The Software part consists of DBMS which acts as a bridge between the user and the database or in other words, software that interacts with the users, application programs, and database and files system of a particular storage media (hard disk, magnetic tapes etc.) to insert, update, delete and retrieve data. For performing these operations such as insertion, deletion and updation

we can either use the Query Languages like SQL, QUEL, Gupta SQL or application softwares

such as Visual 3asic, Developer etc.

4. Users - Users are those persons who need the information from the database to carry out their primary business responsibilities i.e.

Personnel, Staff, Clerical, Managers, Executives etc. On the basis of the job and requirements made by them they are provided access to the database totally or partially.

The various types of users which can access the database are:-

•

Database Administrators (DBA)

•

Database Designers

•

End Users

•

Application Programmers