COM 111 LECTURE SCHEDULE 14

COM 111 LECTURE SCHEDULE 14

Introduction to Database, Logical and Physical Data Concepts, Data Base Management System and its Architecture, Database Models

Data can be defined as a set of isolated and unrelated raw facts, represented by values, which have little or no meaning, simply because they lack a context for evaluation. However, when the data are processed and converted into a meaningful and useful form, it is information. Hence, the information can be defined as a set of organized and validated collection of data.

 Database:

A database can be defined as a collection of related data in an organized way. The organized information serves as a base from which the desired information can be retrieved, conclusions can be drawn and decision can be made. A dictionary, a telephone book, a collection of recipes and a TV guide are all common examples of non-computerized databases. The examples of computerized databases include customer files, employee registers, books catalogue and sales transactions.

Basic database terms:

Field/ Column:

A field represents one related part of a table and is the smallest logical storage in a database. It holds one piece of information about an item or subject. Example is Code, Dept, Name,  Address, City,  and Phone.

Record/ Row:

A record is a collection of multiple related fields that can be treated as a unit. Example, fields Code, Dept, Name, Address, City,  and Phone for a particular employee is a record.

A table is a collection of logically related multiple records. Example: a collection of the employee records of a company form employee table. A table can be referred to as a file. The collection of multiple related files (tables) form the database.

Datatype:

A data type determines the type of data that can be stored in a column. The most common datatypes are Character, Numeric and Date and Time.

Character datatype:

It is used to store numbers, characters, special characters, or combination of any of these.

Numeric datatype:

It is used to store only numeric values.

Date and Time datatype

It is used to store date and time values.

LOGICAL DATA CONCEPTS

The conceptual model can be represented using Entity-Relationship model (E-R model). The E-R model views the real world as a set of basic objects called entities, their characteristics known as attributes and associations among these objects known as relationships. The entities, attributes and relationships are the basic constructs of an E-R model.

Entity:

An entity is an object or concept about which you want to store information. Entities are usually recognizable concepts, either concrete or abstract, such as person, places, things, or events which have relevance to the database.

Some specific examples of entities are EMPLOYEES, CUSTOMERS.

 

Attribute:

A key attribute is the unique, distinguishing characteristic of the entity. For example, an employee's social security number might be the employee's key attribute. Attributes describe the entity of which they are associated. A particular instance of an attribute is a value. 

Relationships:

Relationships illustrate how two entities share information in the database structure. A Relationship represents an association between two or more entities.

Entities, Attributes and Relationships    

       

           

 

                                                                                                         

One-to-One Relationship (1:1)

      In this, one record in a table is related to only one record in another table. For example, a department cannot be headed by more than one head and a head can be the head of only one department.          

 

One-to-Many Relationship (1:M)

            In this, one record in a table is related to many records in another table. For example, a father may have more than one child but the child has only one father.

 

 

Many -to- Many Relationship (M:M)

            In this, one record in a table is related to many records in second table, and one or more records in the second table can be related to one or more records in the first table. For example, a customer can buy many items and the same item can be bought by many customers.

 

 

PHYSICAL DATA CONCEPTS

          This refers to the manner in which data are physically stored on the hardware (hard disk). It involves the physical organization of the records of a file for storage and retrieval of data. There are three types of files:

1.      Sequential

2.      Direct (and)

3.      Indexed sequential

Sequential Files:

          In this, data are stored and retrieved in a logical order in a sequence. The records are stored one after another in an ascending or descending order, based on the key field of the records.

Example is Magnetic tapes.

Sequential files are easy to organize and maintain. It is also relatively cheaper, but searching time is  very long. Making changes to this file is also difficult.

Direct Files:

          These files facilitate accessing any record directly or randomly without having to traverse the sequence of the records. They are also known as relative files. The item can be stored anywhere in the file.

Example: compact discs (CD)

Direct files take less amount of time for searching. Updation, addition and deletion of record becomes much faster, but it is very expensive.

Indexed sequential

          It is a combination of sequential and direct files. The indexed file organization uses a separate index file, which contain the key and the location of the corresponding record. Here the records are arranged in a sequential order and the index table is used to access the records without searching the entire file.

Index file requires less space than the data file and the speed of data retrieval is faster. They are also expensive. 

DATA BASE MANAGEMENT SYSTEM (DBMS)

DBMS can be defined as a collection of interrelated data and a set of programs to access that data.

Need for DBMS:

The following factors influence the enterprises to move towards the DBMS technology to support their business data processing activities. The below mentioned problems can be solved by introducing DBMS systems.

• Data redundancy and inconsistency 
• Difficulty in accessing data 
• Data isolation 
• Integrity problems 
• Atomicity problems 
• Concurrent-access anomalies 
• Security problems

File System Approach versus Database System Approach

Traditionally, data were stored and processed on multiple files with the help of a program for each application. This is called as File-based approach. A file may be defined as systematic self-contained collection of records. It may consists of data or it may contain a sequence of basic statements. Each user works with a different program that handles its own independent data. As a result, space is wasted and potentially different values and item may be stored in different files.

            DBMS is a software system that enables users to define, create and maintain the database and provides controlled access to this database. This approach contains entities, attributes and relationships of the information.

In general, the data in a DBMS are integrated as well as shared.

Benefits of DBMS:

• Reduction in data redundancy
• Reduction in inconsistency
• Sharing of data
• Improvement in data security
• Better interaction with users
• Maintenance of data integrity
• Efficient system

Database Administrator (DBA):

The database administrator is a person having central control over data and programs accessing that data. Duties of the database administrator include:

·         Installing and upgrading the DBMS and application tools

·         Allocating system storage and planning future storage requirements for the database system

·         Creating primary database storages structures after application developers have designed an application

·         Creating tables, views, once application developers have designed an application

·         Modifying the database structure, as necessary, from information given by application developers.

·         Enrolling users and maintaining system security

·         Controlling and monitoring user access to the database

·         Monitoring and optimizing the performance of the database (performance tuning)

·         Planning for backup and recovery of database information

·         Maintaining archived data on tape

·         Backing up and restoring the database

·         Contacting DBMS vendor for technical support  

DBMS ARCHITECTURE:

            The purpose of DBMS is to provide users with an abstract view of the database. This means that the system does not provide all the details of data, rather it hides the details of how the data are stored and maintained.

View:

            A database can be accessed by many users, and each of them may have a different perspective or view of the data.  A view is a subset of the database that contains virtual data derived from the database files but it does not exist in physical form.

Schema:

            The database schema refers to the overall structure of the database, that is, all the information that is going to be represented in a database like name of the tables in the database, data types, relationship between the tables, etc.

A DBMS is a three-layered architecture namely:

1. Internal level
2. Conceptual level
3. External level

Internal level:

This is the lowest level of abstraction, describes how the data are actually stored. The physical level describes complex low-level data structures in detail. It is also called as physical level.

Conceptual level:

            This level of abstraction deals with the logical structure of the entire database and also known as logical level. It describes what data are stored in the database, the relationships among the data, and complete view of the users’ requirements without any concern for the physical implementation. It hides the complexity of physical structures.

External level:

            It is the highest level of abstraction that deals with the user’s view of the database and also known as view level. Most of the users does not require the entire data stored in the database. This level describes a part of the database for a particular group of users. It permits users to access data in a way that is according to their needs, so that the same data can be seen by different users in different ways, at the same time. In this way, it provides a powerful and flexible security mechanism.

                                                           DBMS Architecture

DATABASE MODELS:

            A database model is an abstract model that describes how the data are organized and represented. It has two parts:

i.                    A mathematical notation for describing data and relationships

ii.                  A set of operations used to manipulate that data

There are four basic models like:

1.                  Hierarchical data model

2.                  Network data model

3.                  Relational data model

4.                  Object-oriented data model

1. Hierarchical data model:

Hierarchical database model is one of the oldest database models, dating from late 1950s. One of the first hierarchical databases – Informatiion Management System (IMS) – was developed jointly by North American Rockwell Company and IBM.  This model organizes the data in a tree-like structure in which each child node can have only one parent node.

In the above figure, a company’s organizational structure is depicted as at the top,  General Manager (GM) of the company is defined. Under him, several Deputy General Managers (DGM) of different departments are represented. Each DGM looks after a couple of departments and each department will have a manager and many employees.

When represented in hierarchical database model, there will be separate rows for representing the GM, each DGM, each department, each Manager and each Employee. The row position implies a relationship to other rows.

 

Advantages of Hierarchical database model

• Simplicity
• Data security
• Data integrity
• Efficiency

Disadvantages of hierarchy model

• Implementation complexity
• Database management problems
• Lack of structural independence
• Programming Complexity

2. Network database model:

The network model was presented by CODASYL (Conference on Data Systems Languages). It requires the hierarchical tree with a graph thus allowing more general connections among the nodes. The main difference of the network model from the hierarchical model is its ability to handle many-to-many (n: n) relationships. In other words, it allows a record to have more than one parent.

 

Advantages of network data model

• Conceptual simplicity
• Capability to handle more relationship types
• Ease of data access
• Data integrity
• Data independence

Disadvantages of network model

• System complexity
• Absence of structural independence

3. Relational Database model:

This model was developed by E.F.Codd of IBM in 1970. The difference between hierarchical and network database models, and relational model is in terms of flexibility. It is easy and quick.

 

Advantages of relational data model

• Structural independence
• Conceptual simplicity
• Design, implementation, maintenance and usage ease
• Ad-hoc query capability 

Disadvantages of relational data model

• Hardware overheads
• Ease of design can lead to bad design

4. Object –oriented database model:

Object oriented model represents an entity as a class. A class represents both object attributes as well as the behaviour of the entity. For example, a book class will have not only the book attributes such as Title, Author, Publisher, Year of publishing, Distributor, Price, etc. but also procedures that imitate actions expected of a book such as update price.

Advantages of Object Oriented data model

• Capability to handle large number of different data types
• Object-oriented features improve productivity
• Data access

Disadvantages of object oriented data model

• Difficult to maintain
• Not suited for all applications