E-R DIAGRAMS

The overall logical structure of a database can be expressed graphically by E-R diagram. The entity-relationship model can be conveniently represented in a pictorial form using drawing tools.

1. Rectangles: represent entity set.

2. Ellipses: represent attributes.

3. Diamonds: represent relationships among entity sets.

4. Lines: link attributes to entity sets and entity sets to relationships.

5. Key attributes: have their names underlined.

6. Derived attributes: are shown in dotted ovals.

7. Double rectangular: represent weak entity types and their identifying relationship are shown by double diamonds.

8. Dotted line: The partial key of the weak entity type.

ENTITY TYPE AND ENTITY SET

Entity Type : An entity type defines a collection of entities that have the same attribute. An entity is an instance of an entity type. All entities having the same set of properties are grouped into an entity type.

Entity sets : An entity set is an collection of similar entities, which share same properties or attributes. Each entity set has a key and each attribute has a domain. The individual entities that constitute a set are said to be extension of the entity set.

ENTITY AND ATTRIBUTES

Entity : An entity is a "thing" in real world, with an independent existence. It may have a physical existence in the real world or it may be an object with a conceptual existence.

Each entity has a set of particular properties that completely describe it, in the context of the mini-world model.

Attributes : The entity properties are called attributes. Attributes are properties used to describe an entity; for example an Employee entity may have a Name, Employee number, Address, Sex, Birth Date etc.

Several types of attributes occur in the ER model. They are;

1. Simple : Each entity has a single atomic value for the attribute.

2. Composite : An attribute may be composed of several components.

3. Multi-valued : An entity may assume multiple values.

OVERALL SYSTEM STRUCTURE

Database systems are partitioned into modules for different functions. Some functions may be provided by the operating system. The components include;

1. File manager : Its manages allocation of disk space and data structures used to represent information on disk.

2. Database manager : The interface between low-level data and application programs and queries.

3. Query processor : Its translates statement in a query language into low-level instruction the database manager understands.

4. DML Precompiler : Converts DML statements embedded in an application program to normal procedure calls in a host language. The precompiler interacts with the query processor.

5. DDL Compiler : Converts DDL statements to a set of tables containing metadata stored in a data dictionary.

6. Data Files : Store the database itself.

7. Data dictionary : Stores information about the structure of the database. It is used heavily. Great emphasis should be placed on developing a good design and efficient implementation of the dictionary.

8. Indices : provide fast access to data items holding particular values.

                                                                                           Database System Structure

DATABASE ADMINISTRATOR (DBA)

A database administrator (DBA) directs or performs all activities related to maintaining a successful database environment. Responsibilities include designing, implementing, and maintaining the database system; establishing policies and procedures pertaining to the management, security, maintenance, and use of the database management system; and training employees in database management and use. A DBA is expected to stay abreast of emerging technologies and new design approaches. Typically, a DBA has either a degree in Computer Science and some on-the-job training with a particular database product or more extensive experience with a range of database products. A DBA is usually expected to have experience with one or more of the major database management products, such as Structured Query Language, SAP, and Oracle-based database management software.

Role of the DBA

1. Database Tuning: Tweaking any of several parameters to optimize performance, such as server memory allocation, file fragmentation and disk usage.

2. Database Security: Ensuring that only authorized users have access to the database and fortifying it against any external, unauthorized access.

3. Backup and Recovery: It is a DBA's role to ensure that the database has adequate backup and recovery procedures in place to recover from any accidental or deliberate loss of data.

4. Producing Reports from Queries: DBAs are frequently called upon to generate reports by writing queries, which are then run against the database.

TRANSACTION

A transaction is a unit of work that is performed against a database. Transactions are units or sequences of work accomplished in a logical order, whether in a manual fashion by a user or automatically by some sort of a database program.

A transaction is the propagation of one or more changes to the database. For example, if you are creating a record or updating a record or deleting a record from the table, then you are performing transaction on the table. It is important to control transactions to ensure data integrity and to handle database errors.

Properties of Transactions

Transactions have the following four standard properties, usually referred to by the acronym ACID:

1. Atomicity: ensures that all operations within the work unit are completed successfully; otherwise, the transaction is aborted at the point of failure, and previous operations are rolled back to their former state.

2. Consistency: ensures that the database properly changes states upon a successfully committed transaction.

3. Isolation: enables transactions to operate independently of and transparent to each other.

4. Durability: ensures that the result or effect of a committed transaction persists in case of a system failure.

DATABASE LANGUAGES

Data Defunition Language (DDL)

Data Definition Language is used to define the database conceptual schema. In most DBMS, the DDL also defines user views and sometimes storage structures. The DBMS will have a DDL compiler whose function is to process DDL statements in order to identify descriptions of the schema constructs and to store the schema description in the DBMS catalog. In other DBMSs, separate languages -View Definition Language (VDL), Storage Definition Language (SDL) - may exist for specifying views and storage structures.

DDL statements are used to alter/modify a database or table structure and schema. These statements handle the design and storage of database objects;

CREATE – create a new Table, database, schema

ALTER   – alter existing table, column description

DROP    – delete existing objects from database

Data Manipulation Language (DML)

A data manipulation language (DML) is a family of computer languages including commands permitting users to manipulate data in a database. This manipulation involves inserting data into database tables, retrieving existing data, deleting data from existing tables and modifying existing data. DML is mostly incorporated in SQL databases.

DML statements affect records in a table. These are basic operations we perform on data such as selecting a few records from a table, inserting new records, deleting unnecessary records, and updating/modifying existing records.

DML statements include the following:

SELECT – select records from a table

INSERT – insert new records

UPDATE – update/Modify existing records

DELETE – delete existing records

OBJECT-BASED LOGICAL MODELS

Object-Relational Database(ORD)

An Object-Relational Database (ORD) or Object-Relational Database Management System (ORDBMS) provides a relational database management system that allows developers to integrate a database with their own custom data-types and methods. The term object-relational database sometimes also refers to external software products running over traditional DBMS to provide similar features - systems more correctly described as object-relational mapping systems.

Whereas traditional RDBMS or SQL-DBMS products focused on the efficient management of data drawn from a limited set of data-types (defined by the relevant language standards), an object-relational DBMS allows software-developers to integrate their own types and the methods that apply to them into the DBMS.

ORDBMS-technology aims to allow developers to raise the level of abstraction at which they view the problem domain.

OBJECT-BASED LOGICAL MODELS

Entity-Relationship Model

The (E-R) data model is based on a perception of a real worker that consists of a collection of basic objects, called entities, and of relationships among these objects.

The entity-relationship model for data uses three features to describe data. They are;

1. Entities, which specify distinct real-world items in an application.

2. Relationships, which connect entities and represent meaningful dependencies between them.

3. Attributes, which specify properties of entities and relationships.

Record-Based Logical Models

Relational Model

The relational model was introduced in an academic paper by E.F.Codd in 1970 as a way to make database management systems more independent of any particular application.

The relational database model is based upon the conception of implementing the database with the mathematical set theory. In this model, data is collection of tables called Relation in the set theory. The tabular representation of data contains rows and columns in which rows represent set of attributes of individual entity and columns represent the attribute of entity.

Three key terms are used extensively in relational database models; relations, attributes, and domains. A relation is a table with columns and rows. The named columns of the relation are called attributes, and the domain is the set of values the attributes are allowed to take.

All relations in a relational database have to adhere to some basic rules to qualify as relations. First, the ordering of columns is immaterial in a table. Second, there can't be identical tuples or rows in a table. And third, each tuple will contain a single value for each of its attributes.

Components of the Relational Model

1. Collections of objects or relations that store the data.

2. A set of operators that can act on the relations to produce other relations.

3. Data integrity for accuracy and consistency.

RECORD-BASED LOGICAL MODELS

 Network Model

Network model was developed with the view to eliminate the problems faced by hierarchical database during 1980s. The network model (defined by the CODASYL specification) organises data using two fundamental constructs, called records and sets. Records contain fields (which may be organised hierarchically, as in the programming language COBOL). Sets define one-to-many relationships between records: one owner, many members. A record may be an owner in any number of sets, and a member in any number of sets.

A network database model is a database model that allows multiple records to be linked to the same owner file. The model can be seen as an upside down tree where the branches are the member information linked to the owner, which is the bottom of the tree. The multiple linkages which this information allows the network database model to be very flexible. In addition, the relationship that the information has in the network database model is defined as many-to-many relationship because one owner file can be linked to many member files and vice versa.

                                                                                                                                                                           Network Model

Advantages of Network Database

Network database offer many advantages other than ones offered by hierarchical database. Some of them are mentioned below:

1. Simplicity: Similar to the hierarchical model, this model is simple and the implementation is effortless.

2. Ability to Manage More Relationship Types: The network model has the ability to manage one-to-one (1:1) as well as many-to-many (N: N) relationships.

3. Flexibility in data access: Data items can be navigated in more than one way providing the much desired flexibility of the data access.

4. Data Integrity: In a network model, there's always a connection between the parent and the child segments because it depends on the parent-child relationship.

5. Data Independence: Data independence is better in network models as opposed to the hierarchical models.

6. Standards: Universal standards have been developed and enforced in these types of databases.

Disadvantages of Network Database

Although network databases displayed significant improvement over hierarchical databases yet they suffered from disadvantages as mentioned:

1. Functional Flaws: Because a great number of pointers is essential, insertion, updates, and deletion become more complex.

2. Lack of Structural Independence: A change in structure demands a change in the application as well, which leads to lack of structural independence.

3. System Complexity: Each and every record has to be maintained with the help of pointers, which makes the database structure more complex.

RECORD-BASED LOGICAL MODELS

Hierarchical model

In Hierarchical database data is organized into a tree-like structure, implying a single upward link in each record to describe the nesting, and a sort field to keep the records in a particular order in each same-level list. In the hierarchical model two main data structures concepts: record and parent child relationship.

Record: A collection of field or data items values that provide information on an entity. Each field has a certain data type such as integer, real or string. Record of the same type are grouped into record types.

Parent child relationship type (PCR): It is the 1:N relation between two record types. The record type 1side is parent record type and one on the N side is called child record type of the PCR type. An occurrence (or instance) of the PCR type consists on one record of the parent record type and a number of records (zero or more) of the child record types.

A hierarchical database schema consist number of hierarchical schemas and each hierarchical schema consists numbers of record type and PCR types.

                                                         Above  figure shows A Hierarchical Schema

A hierarchical schema is displayed as a hierarchical diagram.

•  Record type names are displayed in rectangular boxes and 
•  PCR types are displayed as lines connecting the parent record type to the child record type.

Properties of Hierarchical Schema

1. One record type, called the root of the hierarchical schema, does not participate as a child           record type in any  PCR type.

2. Every record type except the root participates as a child record type in exactly one PCR type.

3. A record type can participate as parent record type in any number (zero or more) of PCR           type.

4. A record type that does not participate as parent record type in any PCR type is called a leaf     of the hierarchical schema.

5. If a record type participates as parent record type in any PCR type, then it is child.

Advantages of Hierarchical Database

1. Simplicity: Data naturally have hierarchical relationship in most of the practical situations.       Therefore, it is easier to view data arranged in this manner. This makes, this type of database      more suitable for the purpose. The design process, consequently, is simple.

2. Security: These database systems can enforce varying degree of security features unlike flat-       file systems.

3. Database integrity: Because of its inherent parent-child structure, database integrity is highly     promoted in these systems.

4. Efficiency: For 1:M type relationships, these types of database are very efficient.

Disadvantages of Hierarchical Database

1. Complexity of implementation: The actual implementation of a hierarchical database                 depends on the physical storage of data. This makes the implementation complicated.

2. Difficulty in management: The movement of a data segment from one location to another           causes all the accessing programs to be modified making database management a complex         affair.

3. Structural Dependence: The database has a rigidly defined relationship and hence any                 change in any part of the structure of the database would require change in the programs           accessing it. This makes maintenance very difficult.

4. Complexity of programming: Programming a hierarchical database is relatively complex           because the programmers must know the physical path of the data items.

5. Poor portability: The database are not easily portable mainly because there is little or no             standard existing for these types of databases.

Three levels of the Architecture

The architecture is divided into three levels, known as the internal, conceptual, and external level , respectively:

1. The internal level has an internal schema is the one closest to physical storage. i.e., it is the one concerned with the way the data is physically  stored and describes the physical storage structure  of the database. This is known as the physical level.

2. The external level includes a number of external schema or use views is the one closest to the users - that describe the view of different user groups i.e., it is the one concerned with the way the data is seen by individual users. This is known as the user logical level.

3. The conceptual level has a conceptual schema  is a high-level description of the whole database. The conceptual schema hides the details of physical storage structures and concentrates on describing entities, data types, relationships, user operations and constraints. This is known as community logical level.

_________________________________________________________________________________

Three-schema architecture supports the following DBMS characteristics:

_________________________________________________________________________________

• Program-data independence

• Support for multiple views of the data

• Defines DBMS schemas at three levels:

           (i) Internal schema at the internal level to describe data storage structures and access paths that typically uses a physical data model.

               (ii) Conceptual schema at the conceptual level to describe the structure and constraints for the whole database that uses a conceptual or an implementation data model.

              (iii) External schema at the external level to describe the various user views. Usually uses the same data model as the conceptual level.

_________________________________________________________________________________

One important characteristic of the database approach is the insulation of programs and data. Users can define two types of data independence:

_________________________________________________________________________________

                     1. Logical data independence: It is the capacity to change the conceptual schema without having to change external schemas or application programs.

                     2. Physical data independence: It is the capacity to change the internal schema without having to change the conceptual (or external) schemas. Changes to the internal schema may be needed because some physical files had to be reorganised.

_________________________________________________________________________________

DATA MODELS

Data models include a set of basic operations for specifying retrievals and updates on the database. Data models also allow the database designer to specify a set of valid user defined operations on the database objects. Depending on the nearness of the data models with the users, data models can be classified in the following categories:

• High-level or conceptual data models: This class of models provides concepts that are based on entities and relationship. It uses concepts such as entities, attributes and relationships.

1. Entity: An entity represents a real-world object or concept, such as employee or a project that is described in the database.
2. Attribute: An attribute represents some property that describes an entity, such as the employee's name or salary.
3. Relationship: A relationship among two or more entities represents an interaction among the entities (e.g., relationship between an employee and a project).

• Low-level or physical data models: this model describes the details of how data is stored in the computer (useful for computer specialists).

• Representational or implementation data models: These models provide concepts that are between the above two extremes which may be understood by end users-record-based, object-oriented etc.

DATA INDEPENDENCE

There are two kinds of data independence, physical and logical. Applications implementation older systems-pre-relational or even pre-database systems - tend to be data-dependent. For these applications it is impossible to change the storage structure (how the data physically recorded) or access strategy (how it is accessed) without affecting the application, probably drastically.

Characteristics of Database Management System

1. Self Describing: DBMS Systems create a sub layer with the operating system and handles all the functions related with the DBMS separately and specifically.

2. Insulation from programs and Data Abstraction.

3. Support of Multiple views to Database.


                          Advantages of Database

1.Redundancy can be reduced:There are provisions in all database models that reduntant storage of the data can be avoided . In non-database systems each application has its own private files. This fact can often lead to considerable redundancy in stored data, with resultant waste in storage space.

2. Security can be enforced: The DBA (Data Base Administrator) can ensure that the only means of access to the database through the proper channels, and hence can define security constraints or rules for accessing the sensitive data. No one can access or modify or store database without login. Different constraints can be established for each type of access (retrieve, insert, delete, etc) to each piece of information in the database.

3. Allowing Multiple User Interface: DBMS has the capability to provide concurrent execution of various parts of the database. In this approach, Deadlock and other anomalies are also handled by the DBMS.

4. Backup and Recovery: Database Management Systems have proper mechanism to Backup the whole database and recover when any disaster comes to picture.

5. Reduced application development time: The effort required to develop user's utility programs reduces considerably and so does the time.

                Disadvantages of Database

1. Cost issues: Installing and maintaining a database is expensive, particularly in a large organisation. In addition, there are costs associated with training people to use it correctly.

2. Security issues: Although database can be structured to restrict access, it's always possible unauthorised users will get past the safeguards. And when they do, they may have access 

to all the files, not just a few. In addition, if a database is destroyed by fire, earthquake, theft, or hardware or software problems, it could be fatal to an organisation's business 

activities - unless steps have been taken to regularly make backup copies of the files and store them elsewhere.

3. Privacy issues: Databases may hold information they should not and be used for unintended purposes, perhaps intruding on people's privacy. Medical data,for instance, may be used inappropriately in 

evaluating an employee for a job promotion.

Components of Database Systems

Components of Database Systems are;

• Data
• Hardware
• Software
• Database users

DATA

Data is a collection of meaningful facts or figures expressed in a form that its users could understand. It can be recorded and processed for further use. The Data stored in the system is portioned into one or more databases. Generally we assume that there is just one database, containing totality of all stored data in the system.

Data stored in the database is both integrated and shared

Integrated: The data is accurate, consisted and up to date - because each updating change is made only in one place.

Shared: Individual pieces of data in the database can be shared among different users, in the sense that each of those users can have access to the same piece of data, which can use for different purposes. The same piece of data can be effectively access by different users at the same time that is Concurrent Access. Sharing of dta, concurrent or otherwise, is partly a consequence of the fact that the database is integrated.

HARDWARE

The hardware components of the system consist of :

• The secondary storage volumes - mostly magnetic disks - that are used to hold the stored data, together with the associated I/O devices (disk drives,etc.), device controllers, I/O channels.

• The hardware processor and associated main memory that are used to support the execution of the database system software.

SOFTWARE

A software act as an interface between the physical database itself-i.e., the data as physically stored - and the users of the system. Data base management system (DBMS) software or Data base manager, a program that controls structure of a database and access to the data. All requests for access to the database are handled by the DBMS. For adding and removing files, retrieving data from and updating data in such files or tables, and so forth, are all facilities provided by the DBMS.

DATABASE USERS

A primary goal of a database system is to provide an environment for retrieving information from and storing information into the database.

Any entity whether a human or a software system that interacts with a database is known as a database user. However, the role of a user with respect to a database depends on the responsibilities it carries and the operations  it performs on the database. Accordingly, following classws of users exist in a database system:

• Database Administrators (DBA): The user is responsible for creating. manipulating and authorizing access to the database. They are also responsible for coordinating and monitoring its use. A DBA has complete control on every aspect of the database. Sometimes a DBA is also called as Super-User.

• Database Designers: They are responsible for identifying the data to be stored and foe choosing appropriate data structures to represent and store this data.

• End User: People whose jobs require access to the database for querying, updating, and generating reports are end-users. They carry out their jobs either through application programs or users programs.

• System Analysts: System analysts determine the requirements of the end users, and develop specifications for transactions.

• Application Programmers: Application pragrammers implement specifications and programs developed by the analysts so that the specification becomes perational. The application program are written in the host language for example Pascal, C++, Cobol. Analysts and programmers nowadays are called software engineers.

Database System

A database system is just a computerised record-keeping system. The database is a kind of an electronic filling cabinet. The user can perform the following operations..

• Addition of new files to the database
• Insertion of  data into existing files
• Retrieving of data from existing files
• Updation of data in existing files
• Removing of data from existing files

A large repository of data which may be structured or unstructured, manually operated or computerized, consisting of a single file or a number of files is refered to as a database.They used data base management systems (DBMS) software which control the steucture of the database and access to the data.Database stores a collection of related sets of data items (i.e. both data and relations between them) along with necessary data/information associated with it (metadata). In a traditional manual system a database is stored on paper while in a computer-based system it is stored on secondary devices in electronic form.