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