III B.SC - DBMS - notes -Semester -V

Unit- I

Overview  of  Database  Management  System:  Introduction,  file-based system, Drawbacks of file-Based System ,Data and information, Database, Database management System, Objectives of DBMS, Evaluation of Database management  System,  Classification  of  Database  Management  System, DBMS  Approach,  advantages  of  DBMS,  Anis/spark  Data  Model,  data models, Components and Interfaces of Database Management System. Database Architecture, Situations where DBMS is not Necessary, DBMS Vendors and Their Products.

Q) Explain File Oriented approach. Write down Drawbacks of File-Based System.

Answer:

File Based System: The traditional file oriented approach to information processing has for each application a separate master file and its own set of personal file. In file oriented approach the program dependent on the files and files become dependent on the files and files become dependents upon the programs.

Disadvantages of file oriented approach:

1) Data redundancy and inconsistency: The same information may be written in several files. This redundancy leads to higher storage and access cost. It may lead data inconsistency that is the various copies of the same data may longer agree for example a changed customer address may be reflected in single file but not else where in the system.

2) Difficulty in accessing data : The conventional file processing system do not allow data to retrieved in a convenient and efficient manner according to user choice.

3) Data isolation : Because data are scattered in various file and files may be in different formats with new application programs to retrieve the appropriate data is difficult.

4) Integrity Problems: Developers enforce data validation in the system by adding appropriate code in the various application program. How ever when new constraints are added, it is difficult to change the programs to enforce them.

5) Atomicity: It is difficult to ensure atomicity in a file processing system when transaction failure occurs due to power failure, networking problems etc. (atomicity: either all operations of the transaction are reflected properly in the database or non are)

6) Concurrent access: In the file processing system it is not possible to access a same file for transaction at same time

7) Security problems: There is no security provided in file processing system to secure the data from unauthorized user access. Database: A database is organized collection of related data of an organization stored in formatted way which is shared by multiple users.

Q) Define a) Data  b) Information c) File d)DBMS e)Meta Data

Answer:

a) Data:- Data is the known facts or figures that have implicit meaning. It can also be defined as it is the representation of facts ,concepts or instruction in a formal manner, which is suitable for understanding and processing.  Data can be represented in alphabets(A-Z, a-z),in digits(0-9) and using special characters(+,-.#,$, etc)

e.g: 25, “mahaboob” etc.

b) Information:- Information is the processed data on which decisions and actions are based. Information can be defined as the organized and classified data to provide meaningful values.

Eg: “The age of mahaboob is 25”

c) File:- File is a collection of related data stored in secondary memory

d) DBMS:- Database Management System (DBMS): A database management system consists of collection of related data and refers to a set of programs for defining, creation, maintenance and manipulation of a database.

e) Meta Data: A data which describes data about data.

Q) Write a note on Database Management System

Answer:  A database management system (DBMS) consists of collection of interrelated data and a set of programs to access that data. It is software that is helpful in maintaining and utilizing a database.

A DBMS is general-purpose software i.e., not application specific. The same DBMS (e.g., Oracle, Sybase, etc.) can be used in railway reservation system, library management, university, etc.

DBMS is a complex system that allows a user to do many things to data as shown in Fig.

Q) What are the Objectives of DBMS? Explain it.

A) The main objectives of database management system are data availability, data integrity, data security, and data independence.

1.    Data Availability

Data availability refers to the fact that the data are made available to wide variety of users in a meaningful format at reasonable cost so that the users can easily access the data.

2.   Data Integrity

Data integrity refers to the correctness of the data in the database. In otherwords, the data available in the database is a reliable data.

3.   Data Security

Data security refers to the fact that only authorized users can access the data. Data security can be enforced by passwords. If two separate users are accessing a particular data at the same time, the DBMS must not allow them to make conflicting changes.

4.   Data Independence

DBMS allows the user to store, update, and retrieve data in an efficient manner. DBMS provides an “abstract view” of how the data is stored in the database.

In order to store the information efficiently, complex data structures are used to represent the data. The system hides certain details of how the data are stored and maintained.

Q) Explain the Evolution of Database Management Systems

File-based system was the predecessor to the database management system.

The chronological order of the development of DBMS is as follows:

–    Flat files – 1960s–1980s

–    Hierarchical – 1970s–1990s

–    Network – 1970s–1990s

–    Relational – 1980s–present

–    Object-oriented – 1990s–present

–    Object-relational – 1990s–present

–    Data warehousing – 1980s–present

–    Web-enabled – 1990s–present

1.    Early 1960s. Charles Bachman at GE created the first general purpose DBMS Integrated Data Store. It created the basis for the network model which was standardized by CODASYL (Conference on Data System Language).

2.    Late 1960s. IBM developed the Information Management System (IMS). IMS used an alternate model, called the Hierarchical Data Model.

3.    1970. Edgar Codd, from IBM created the Relational Data Model. In 1981 Codd received the Turing Award for his contributions to database theory. Codd Passed away in April 2003.

4.    1976. Peter Chen presented Entity-Relationship model, which is widely used in database design.

5.    1980. SQL developed by IBM, became the standard query language for databases.

6.    SQL was standardized by ISO.

7.    1980s and 1990s. IBM, Oracle, Informix and others developed powerful DBMS

Q) Briefly explain the Classification of Database Management System

A) The database management system can be broadly classified into

(1) Passive Database Management System and (2) Active Database Management System

1. Passive Database Management System. Passive Database Management Systems are program-driven. In passive database management system the users query the current state of database and retrieve the information currently available in the database.

Traditional DBMS are passive in the sense that they are explicitly and synchronously invoked by user or application program initiated operations. Applications send requests for operations to be performed by the DBMS and wait for the DBMS to confirm and return any possible answers. The operations can be definitions and updates of the schema, as well as queries and updates of the data.

2. Active Database Management System. Active Database Management Systems are data-driven or event-driven systems. In active database management system, the users specify to the DBMS the information they need.

If the information of interest is currently available, the DBMS actively monitors the arrival of the desired information and provides it to the relevant users. The scope of a query in a passive DBMS is limited to the past and present data, whereas the scope of a query in an active DBMS additionally includes future data. An active DBMS reverses the control flow between applications and the DBMS instead of only applications calling the DBMS, the DBMS may also call applications in an active DBMS.

Active databases contain a set of active rules that consider events that represent database state changes, look for TRUE or FALSE conditions as the result of a database predicate or query, and take an action via a data manipulation program embedded in the system. Alert is extension architecture at the IBM Almaden Research, for experimentation with active databases.

Q) Explain DBMS Approach and its advantages.

DBMS is software that provides a set of primitives for defining, accessing, and manipulating data. In DBMS approach, the same data are being shared by different application programs; as a result data redundancy is minimized. The DBMS approach of data access is shown in Fig.

Advantages of DBMS

There are many advantages of database management system. Some of the advantages are listed are:

1. Data redundancy −→ Reduced in DBMS.

2. Data independence −→ Activated in DBMS.

3. Data inconsistency −→ Avoided in DBMS.

4. Centralizing the data −→ Achieved in DBMS.

5. Data integrity −→ Necessary for efficient Transaction.

6. Support for multiple views −→ Necessary for security reasons.

1.    Data redundancy means duplication of data. Data redundancy will occupy more space hence it is not desirable.

2.    Data independence means independence between application program and the data. The advantage is that when the data representation changes, it is not necessary to change the application program.

3.    Data inconsistency means different copies of the same data will have different values.

4.    Centralizing the data means data can be easily shared between the users but the main concern is data security.

5.    The main threat to data integrity comes from several different users attempting to update the same data at the same time. For example, “The number of booking made is larger than the capacity of the aircraft/train.”

6.    Support for multiple views means DBMS allows different users to see different “views” of the database, according to the perspective each one requires. This concept is used to enhance the security of the database.

Q) Explain ANSI/SPARK Data Model (American National Standard Institute/ Standards Planning and Requirements Committee)

The distinction between the logical and physical representation of data were recognized in 1978 when ANSI/SPARK committee proposed a generalized framework for database systems. This framework provided a three-level architecture, three levels of abstraction at which the database could be viewed.

A database management system that provides three levels of data architecture. 

1.    External level

2.    Conceptual level

3.    Internal level

1. External level : The external level is at the highest level of database abstraction . At this level, there will be many views define for different users requirement. A view will describe only a subset of the database. Any number of user views may exist for a given global or subschema.

for example , each student has different view of the time table. the view of a student of BCOM (CA) is different from the view of the student of BSC(MPCS).Thus this level of abstraction is concerned with different categories of users. Each external view is described by means of a schema called schema.

2. Conceptual level: At this level of database abstraction all the database entities and the relationships among them are included. One conceptual view represents the entire database. This conceptual view is defined by the conceptual schema.

The conceptual schemas hides the details of physical storage structures and concentrate on describing entities, data types, relationships, user operations and constraints.

It describes all the records and relationships included in the conceptual view. There is only one conceptual schema per database. It includes feature that specify the checks to relation data consistency and integrity.

3. Internal level: It is the lowest level of abstraction closest to the physical storage method used. It indicates how the data will be stored and describes the data structures and access methods to be used by the database . The internal view is expressed by internal schema.

The following aspects are considered at this level:

1. Storage allocation e.g: B-tree, hashing

2. Access paths eg. specification of primary and secondary keys, indexes etc

3. Miscellaneous eg. Data compression and encryption techniques, optimization of the internal structures.

Q) Explain briefly about Data Models.

The entire structure of a database can be described using a data model. It is a collection of conceptual tools for describing data, data relationships, data semantics, and consistency constraints.

Data models can be classified into following types.

1.   Object Based Logical Models.

2.   Record Based Logical Models.

3.   Physical Models.

1.Object Based Logical Models: These models can be used in describing the data at the logical and view levels. These models are classified into following types.

a.      The entity-relationship model.

b.      The object-oriented model.

c.       The semantic data model.

d.      The functional data model.

THE ENTITY-RELATIONSHIP MODEL:

·         The entity-relationship (E-R) data model is a collection of basic objects, called entities, and of relationships among these objects.

·         An entity is a “thing” or “object” in the real world that is distinguishable from other objects. For example, each person is an entity, and bank accounts can be considered as entities.

·         A relationship is an association among several entities. For example, a depositor relationship associates a customer with each account that she has.

The overall logical structure (schema) of a database can be expressed graphically by an E-R diagram, which is built up from the following components:

• Rectangles, which represent entity sets

• Ellipses, which represent attributes

• Diamonds, which represent relationships among entity sets

• Lines, which link attributes to entity sets and entity sets to relationships

2.Record Based Logical Models: These models can also be used in describing the data at the logical and view levels. These models can be classified into,

a. Relational model.

b. Network model.

c. Hierarchal model.

a. Relational Model:

·         The relational model uses a collection of tables to represent both data and the relationships among those data.

·         Each table has multiple columns, and each column has a unique name. Figure presents a sample relational database comprising three tables:

·         It shows how tables are linked, what type of links are between tables, what keys are used, what information is referenced between tables. It's an essential part of developing a normalised database structure to prevent repeat and redundant data storage

b. Network Model: It is a modified version of hierarchical database. In network database structure each node may have several parents.

» It is very difficult to develop this type of database structures.

» It is useful for one-to-one and one-to-many record relationships.

» The relationships should be pre-determined. The records in the database are organized as a collection of arbitrary graph.                             

c. Hierarchical Model: - In this database structure, records are logically organised into a hierarchy of relationships and involve an inverted tree like structure. The tree consists of hierarchy of nodes and the uppermost tree is called parent. Every element can have any number of lower level elements, called children, but every node will have only one parent..

 

3. Physical Models: These models can be used in describing the data at the lowest level, i.e. physical level. These models can be classified into 

a. Unifying model 

b. Frame memory model

Q) Explain the Components and Interfaces of Database Management System.

A database management system involves five major components: data, hardware, software, procedure, and users.

1.   Hardware:  The hardware can range from a single personal computer, to a single mainframe, to a network of computers. The particular hardware depends on the requirements of the organization and the DBMS used. Some DBMSs run only on particular operating systems, while others run on a wide variety of operating systems.

A DBMS requires a minimum amount of main memory and disk space to run, but this minimum configuration may not necessarily give acceptable performance.

2.   Software:  The software includes the DBMS software, application programs together with the operating systems including the network software if the DBMS is being used over a network. The application programs are written in third-generation programming languages like “C,” COBOL, FORTRAN, Ada, Pascal, etc. or using fourth-generation language such as SQL, embedded in a third-generation language. The target DBMS may have its own fourth-generation tools which allow development of applications through the provision of nonprocedural query languages, report generators, graphics generators, and application generators. The use of fourth-generation tools can improve productivity significantly and produce programs that are easier to maintain.

3.   Data:  A database is a repository for data which, in general, is both integrated and shared. Integration means that the database may be thought of as a unification of several otherwise distinct files, with any redundancy among those files partially or wholly eliminated. The sharing of a database refers to the sharing of data by different users, in the sense that each of those users may have access to the same piece of data and may use it for different purposes.

Any given user will normally be concerned with only a subset of the whole database. The main features of the data in the database are listed later:

1. The data in the database is well organized (structured)

2. The data in the database is related

3. The data are accessible in different orders without great difficulty

4.   Procedure:  Procedures are the rules that govern the design and the use of database. The procedure may contain information on how to log on to the DBMS, start and stop the DBMS, procedure on how to identify the failed component, how to recover the database, change the structure of the table, and improve the performance.

5.   People Interacting with Database:  Here people refers to the people who manages the database, database administrator, people who design the application program, database designer and the people who interacts with the database, database users.

A DBMS is typically run as a back-end server in a local or global network, offering services to clients directly or to Application Servers

Q) Who is DBA. What are the responsibilities of DBA?

DBA-Database Administrator: Database Administrator is a person having central control over data and programs accessing that data. The database administrator is a manager whose responsibilities are focused on management of technical aspects of the database system. The objectives of database administrator are given as follows:

1. To control the database environment

2. To standardize the use of database and associated software

3. To support the development and maintenance of database application projects

4. To ensure all documentation related to standards and implementation is up-to-date

The summarized objectives of database administrator are shown in Fig..

Responsibilities of Database Administrator (DBA)

The responsibility of the database administrator is to maintain the integrity, security, and availability of data. A database must be protected from database and from hardware or software failures that corrupt data. Protection from accidents that cause data inaccuracy is a part of maintaining data integrity.

Protecting the database from unauthorized or malicious use is termed as database security. The responsibilities of the database administrator are summarized as follows:

1. Authorizing access to the database.

2. Coordinating and monitoring its use.

3. Acquiring hardware and software resources as needed.

4. Backup and recovery. DBA has to ensure regular backup of database, incase of damage, suitable recovery procedure are used to bring the database up with little downtime as possible

Q) Explain briefly about different types of Database Users

Database users are the people who need information from the database to carry out their business responsibility. The database users can be broadly classified into two categories like application programmers and end users.

1.   Sophisticated End Users

Sophisticated end users interact with the system without writing programs. They form requests by writing queries in a database query language. These are submitted to query processor. Analysts who submit queries to explore data in the database fall in this category.

2.   Specialized End Users

Specialized end users write specialized database application that does not fit into data-processing frame work. Application involves knowledge base and expert system, environment modeling system, etc.

3.   Naive End Users

Naıve end user interact with the system by using permanent application program Example: Query made by the student, namely number of books borrowed in library database.

4.   System Analysts

System analysts determine the requirements of end user, and develop specification for canned transaction that meets this requirement

Q) Explain Database Architecture

Database architecture essentially describes the location of all the pieces of information that make up the database application. The database architecture can be broadly classified into two-, three-, and multitier architecture.

1.   Two-Tier Architecture

The two-tier architecture is a client–server architecture in which the client contains the presentation code and the SQL statements for data access. The database server processes the SQL statements and sends query results back to the client. The two-tier architecture is shown in Fig. 1.9. Two-tier client/server provides a basic separation of tasks. The client, or first tier, is primarily responsible for the presentation of data to the user and the “server,” or second tier, is primarily responsible for supplying data services to the client

Presentation Services: “Presentation services” refers to the portion of the application which presents data to the user. In addition, it also provides for the mechanisms in which the user will interact with the data. More simply put, presentation logic defines and interacts with the user interface. The presentation of the data should generally not contain any validation rules.

Business Services/objects

“Business services” are a category of application services. Business services encapsulate an organizations business processes and requirements. These rules are derived from the steps necessary to carry out day-today business in an organization. These rules can be validation rules, used to be sure that the incoming information is of a valid type and format, or they can be process

rules, which ensure that the proper business process is followed in order to complete an operation.

Application Services

“Application services” provide other functions necessary for the application.

Data Services

“Data services” provide access to data independent of their location. The data can come from legacy mainframe, SQL RDBMS, or proprietary data access systems. Once again, the data services provide a standard interface for accessing data

Three-tier Architecture

A “Multitier,” often referred to as “three-tier” or “N-tier,” architecture provides greater application scalability, lower maintenance, and increased reuse of components. Three-tier architecture offers a technology neutral method of building client/server applications with vendors who employ standard interfaces which provide services for each logical “tier.” The three-tier architecture is shown in Fig. 1.10.

From this figure, it is clear that in order to improve the performance a second-tier is included between the client and the server. Through standard tiered interfaces, services are made available to the application.

A single application can employ many different services which may reside on dissimilar platforms or are developed and maintained with different tools. This approach allows a developer to leverage investments in existing systems while creating new application which can utilize existing resources. Although the three-tier architecture addresses performance degradations of the two-tier architecture, it does not address division-of-processing concerns. The PC clients and the database server still contain the same division of code although the tasks of the database server are reduced. Multiple-tier architectures provide more flexibility on division of processing.

Q) Explain the Situations where DBMS is not Necessary

It is also necessary to specify situations where it is not necessary to use a DBMS. If traditional file processing system is working well, and if it takes more money and time to design a database, it is better not to go for the DBMS. Moreover if only one person maintains the data and that person is not skilled in designing a database as well as not comfortable in using the DBMS then it is not advisable to go for DBMS.

DBMS is undesirable under following situations:

–    DBMS is undesirable if the application is simple, well-defined, and not expected to change.

–    Runtime overheads are not feasible because of real-time requirements.

–    Multiple accesses to data are not required.

Compared with file systems, databases have some disadvantages:

1. High cost of DBMS this includes:

– Higher hardware costs

– Higher programming costs

– High conversion costs

2. Slower processing of some applications

3. Increased vulnerability

4. More difficult recovery

Q) List out the DBMS Vendors and their Products

Some of the popular DBMS vendors and their corresponding products are given Table 1.1.

UNIT-2

Entity-Relationship Model: Introduction, the building blocks of an entity relationship diagram, classification of entity sets, attribute classification, relationship degree, relationship classification, reducing ER diagram to tables, enhanced entity relationship model (EER model), generalization and specialization, IS A relationship and attribute inheritance, multiple inheritance, constraints on specialization and generalization, aggregation and composition, entity clusters, connection types, advantages of ER modeling.

Q) Explain the Building Blocks of an Entity–Relationship Diagram

ER diagram is a graphical modeling tool to standardize ER modeling. The modeling can be carried out with the help of pictorial representation of entities, attributes, and relationships. The basic building blocks of Entity-Relationship diagram are Entity, Attribute and Relationship.

1. Entity

An entity is an object that exists and is distinguishable from other objects.

In other words, the entity can be uniquely identified.

The examples of entities are:

– A particular person, for example Mahaboob Basha Shaik is an entity.

– A particular department, for example Computer Science Department.

– A particular place, for example Kurnool city can be an entity.

2. Entity Type

An entity type or entity set is a collection of similar entities.

Some examples of entity types are:

– All students in MasterDegreeCollege, say STUDENT.

– All courses in MasterDegreeCollege, say COURSE.

– All departments in MasterDegreeCollege, say DEPARTMENT.

An entity may belong to more than one entity type. For example, a staff working in a particular department can pursue higher education as part-time.

Hence the same person is a LECTURER at one instance and STUDENT at another instance.

3. Relationship

A relationship is an association of entities where the association includes one entity from each participating entity type whereas relationship type is a meaningful association between entity types.

The examples of relationship types are:

– Teaches is the relationship type between LECTURER and STUDENT.

– Buying is the relationship between VENDOR and CUSTOMER.

– Treatment is the relationship between DOCTOR and PATIENT.

4. Attributes

Attributes are properties of entity types. In other words, entities are described in a database by a set of attributes.

The following are example of attributes:

– Brand, cost, and weight are the attributes of CELLPHONE.

– Roll number, name, and grade are the attributes of STUDENT.

– Data bus width, address bus width, and clock speed are the attributes of MICROPROCESSOR .

5. ER Diagram

The ER diagram is used to represent database schema. In ER diagram:

– A rectangle represents an entity set.

– An ellipse represents an attribute.

– A diamond represents a relationship.

– Lines represent linking of attributes to entity sets and of entity sets to relationship sets.

Example of ER diagram

Let us consider a simple ER diagram as shown in Fig. 2.1.

In the ER diagram the two entities are STUDENT and CLASS. Two simple attributes which are associated with the STUDENT are Roll number and the name. The attributes associated with the entity CLASS are Subject Name and Hall Number. The relationship between the two entities STUDENT and CLASS is Attends.

Q) Explain the Classification of Entity Sets. (OR) What are the different types of Entity Sets

Entity sets can be broadly classified into:

1. Strong entity.

2. Weak entity.

3. Associative entity.

1. Strong Entity

Strong entity is one whose existence does not depend on other entity.

Example

Consider the example, student takes course. Here student is a strong entity. In this example, course is considered as weak entity because, if there are no students to take a particular course, then that course cannot be offered.

The COURSE entity depends on the STUDENT entity.

2. Weak Entity

Weak entity is one whose existence depends on other entity. In many cases, weak entity does not have primary key.

Example:  Consider the example, customer borrows loan. Here loan is a weak entity. For every loan, there should be at least one customer. Here the entity loan depends on the entity customer hence loan is a weak entity.

3. Associative entity

Many-to-many relationship can be modeled as an associative entity in the ER diagram.

Q) What do you mean by Attribute? Explain Attribute Classification

Attribute is used to describe the properties of the entity. This attribute can be broadly classified based on value and structure. Based on value the attribute can be classified into single value, multivalue, derived, and null value attribute.

Based on structure, the attribute can be classified as simple and composite attribute.

Value based classification:

1. Single Value Attribute: Single value attribute means, there is only one value associated with that attribute.

Example: The examples of single value attribute are age of a person, Roll number of the student, Registration number of a car, etc.

Representation of Single Value Attribute in ER Diagram

2. Multivalued Attribute: In the case of multivalue attribute, more than one value will be associated with that attribute.

Representation of Multivalued Attribute in ER Diagram

Examples of Multivalued Attribute

1. Consider an entity EMPLOYEE. An Employee can have many skills; hence skills associated to an employee are a multivalue attribute

3. Derived Attribute: The value of the derived attribute can be derived from the values of other related attributes or entities.

In ER diagram, the derived attribute is represented by dotted ellipse.

Representation of Derived Attribute in ER Diagram

Example of Derived Attribute

1. Age of a person can be derived from the date of birth of the person. In this example, age is the derived attribute

4. Null Value Attribute

In some cases, a particular entity may not have any applicable value for an attribute. For such situation, a special value called null value is created.

Example

In application forms, there is one column called phone no. if a person do not have phone then a null value is entered in that column.

Structure based classification:

1. Simple Attribute: Simple attribute is one which can be further subdivided into attributes.

2. Composite Attribute: Composite attribute is one which can be further subdivided into simple attributes.

Example

Consider the attribute “address” which can be further subdivided into Street name, City, and State.

Q) Explain Relationship Degree.

Relationship degree refers to the number of associated entities. The relationship degree can be broadly classified into unary, binary, and ternary relationship.

1. Unary Relationship

The unary relationship is otherwise known as recursive relationship. In the unary relationship the number of associated entity is one. An entity related to itself is known as recursive relationship.

Example

In this example, Husband and wife are referred as roles

2. Binary Relationship

In a binary relationship, two entities are involved. Consider the example; each staff will be assigned to a particular department. Here the two entities are STAFF and DEPARTMENT.

3. Ternary Relationship

In a ternary relationship, three entities are simultaneously involved. Ternary relationships are required when binary relationships are not sufficient to accurately describe the semantics of an association among three entities.

Example:  Consider the example of employee assigned a project. Here we are considering three entities EMPLOYEE, PROJECT, and LOCATION. The relationship is “assigned-to.” Many employees will be assigned to one project hence it is an example of one-to-many relationship.

4. Quaternary Relationships

Quaternary relationships involve four entities. The example of quaternary relationship is “A professor teaches a course to students using slides.” Here the four entities are PROFESSOR, SLIDES, COURSE, and STUDENT. The relationships between the entities are “Teaches.”

Q) What are the Relationship Classification?

Relationship is an association among one or more entities. This relationship can be broadly classified into one-to-one relation, one-to-many relation, many-to-many relation and recursive relation.

1. One-to-Many Relationship Type:

The relationship that associates one entity to more than one entity is called one-to-many relationship. Example of one-to-many relationship is Country having states. For one country there can be more than one state hence it is an example of one-to-many relationship.

2. One-to-One Relationship Type

One-to-one relationship is a special case of one-to-many relationship. True one-to-one relationship is rare. The relationship between the President and the country is an example of one-to-one relationship. For a particular country there will be only one President.

3. Many-to-Many Relationship Type

The relationship between EMPLOYEE entity and PROJECT entity is an example of many-to-many relationship. Many employees will be working in many projects hence the relationship between employee and project is many-to-many relationship

4. Many-to-One Relationship Type

The relationship between EMPLOYEE and DEPARTMENT is an example of many-to-one relationship. There may be many EMPLOYEES working in one DEPARTMENT. Hence relationship between EMPLOYEE and DEPARTMENT is many-to-one relationship. The four relationship types are summarized and shown in Table 2.1.

Q) Explain how to Reducing ER Diagram to Tables (OR) Explain Mapping Algorithm.

Mapping Algorithm:

The mapping algorithm gives the procedure to map ER diagram to tables.

The rules in mapping algorithm are given as:

v  For each strong entity type say E, create a new table. The columns of the table are the attribute of the entity type E.

v  For each weak entity W that is associated with only one 1–1 identifying owner relationship, identify the table T of the owner entity type. Include as columns of T, all the simple attributes and simple components of the composite attributes of W.

v  For each weak entity W that is associated with a 1–N or M–N identifying relationship, or participates in more than one relationship, create a new table T and include as its columns, all the simple attributes and simple components of the composite attributes of W. Also form its primary key by including as a foreign key in R, the primary key of its owner entity.

v  For each binary 1–1 relationship type R, identify the tables S and T of the participating entity types. Choose S, preferably the one with total participation. Include as foreign key in S, the primary key of T. Include as columns of S, all the simple attributes and simple components of the composite attributes of R.

v  For each binary 1–N relationship type R, identify the table S, which is at N side and T of the participating entities. Include as a foreign key in S, the primary key of T. Also include as columns of S, all the simple attributes and simple components of composite attributes of R.

v  For each M-N relationship type R, create a new table T and include as columns of T, all the simple attributes and simple components of composite attributes of R. Include as foreign keys, the primary keys of the participating entity types. Specify as the primary key of T, the list of foreign keys.

v  For each multivalued attribute, create a new table T and include as columns of T, the simple attribute or simple components of the attribute A. Include as foreign key, the primary key of the entity or relationship type that has A. Specify as the primary key of T, the foreign key and the columns corresponding to A.

Q) How to map a) Regular entity b)composite attribute and c) multi-valued

a) Regular Entity

Regular entities are entities that have an independent existence and generally represent real-world objects such as persons and products. Regular entities are represented by rectangles with a single line.

Mapping Regular Entities

– Each regular entity type in an ER diagram is transformed into a relation.

The name given to the relation is generally the same as the entity type.

– Each simple attribute of the entity type becomes an attribute of the relation.

– The identifier of the entity type becomes the primary key of the corresponding relation.

Example 1:  Mapping regular entity type tennis player

 

This diagram is converted into corresponding table as

b) Converting Composite Attribute in an ER Diagram to Tables

When a regular entity type has a composite attribute, only the simple component attributes of the composite attribute are included in the relation.

Example

In this example the composite attribute is the Customer address, which consists of Street, City, State, and Zip.

C) A multivalued attribute is having more than one value. One way to map a multivalued attribute is to create two tables.

Example

In this example, the skill associated with the EMPLOYEE is a multivalued attribute, since an EMPLOYEE can have more than one skill as fitter, electrician, turner, etc.

Q) How to Converting “Weak Entities” in ER Diagram to Tables

Weak entity type does not have an independent existence and it exists only through an identifying relationship with another entity type called the owner For each weak entity type, create a new relation and include all of the simple attributes as attributes of the relation. Then include the primary key of the identifying relation as a foreign key attribute to this new relation.

The primary key of the new relation is the combination of the primary key of the identifying and the partial identifier of the weak entity type. In this example DEPENDENT is weak entity.

Q) How to Converting Binary Relationship to Table

A relationship which involves two entities can be termed as binary relationship.

This binary relationship can be one-to-one, one-to-many, many-to-one, and many-to-many.

Mapping one-to-Many Relationship

For each 1–M relationship, first create a relation for each of the two entity type’s participation in the relationship.

Example

One customer can give many orders. Hence the relationship between the two entities CUSTOMER and ORDER is one-to-many relationship. In one-to-many relationship, include the primary key attribute of the entity on the one-side of the relationship as a foreign key in the relation that is on the many side of the relationship.

Here we have two entities CUSTOMER and ORDER. The relationship between CUSTOMER and ORDER is one-to-many. For two entities CUSTOMER and ORDER, two tables namely CUSTOMER and ORDER are created as shown later. The primary key CUSTOMER ID in the CUSTOMER

relation becomes the foreign key in the ORDER relation.

Binary one-to-one relationship can be viewed as a special case of one-to-many relationships. The process of mapping one-to-one relationship requires two steps. First, two relations are created, one for each of the participating entity types. Second, the primary key of one of the relations is included as a foreign key in the other relation.

Q) Describe briefly about Enhanced Entity–Relationship Model (EER Model)

The Enhanced ER model is the extension of the original ER model with new modeling constructs. The new modeling constructs introduced in the EER model are supertype (superclass)/ subtype (subclass) relationships. The supertype allows us to model general entity type whereas the subtype allows us to model specialized entity types.

Enhanced ER model = ER model + hierarchical relationships.

1. Supertype or Superclass

Supertype or superclass is a generic entity type that has a relationship with one or more subtypes. For example PLAYER is a generic entity type which hasa relationship with one or more subtypes like CRICKET PLAYER, FOOTBALL PLAYER, HOCKEY PLAYER, TENNIS PLAYER, etc.

2. Subtype or Subclass

A subtype or subclass is a subgrouping of the entities in an entity type that is meaningful to the organization. A subclass entity type is a specialized type of superclass entity type. A subclass entity type represents a subset or subgrouping of superclass entity type’s instances. Subtypes inherit the attributes and relationships associated with their supertype.

Consider the entity type ENGINE, which has two subtypes PETROL ENGINE and DIESEL ENGINE.

Consider the entity type STUDENT, which has two subtypes UNDERGRADUATE and POSTGRADUATE.

Q) Explain Generalization and Specialization.

Generalization is the bottom-up process of defining a generalized entity type from a set of more specialized entity types.

Generalization is the process of minimizing the differences between entities by identifying common features. It can also be defined as the process of defining a generalized entity type from a set of entity types.

Specialization is the top-down process of defining one or more subtypes of a supertype.

Specialization is a process of identifying subsets of an entity set (the superset) that share some distinguishing characteristics. In specialization the superclass is defined first and the subclasses are defined next. Specialization is the process of viewing an object as a more refined, specialized object. Specialization emphasizes the differences between objects.

For example consider the entity type STUDENT, which can be further classified into FULLTIME STUDENT and PARTTIME STUDENT. The classification of STUDENT into FULLTIME STUDENT and PARTTIME.

STUDENT is called Specialization.

Q) Explain ISA Relationship and Attribute Inheritance.

IS A relationship supports attribute inheritance and relationship participation.

In the EER diagram, the subclass relationship is represented by ISA relationship. Attribute inheritance is the property by which subclass entities inherit values for all attributes of the superclass.

Consider the example ofEMPLOYEEentity set in a bank. The EMPLOYEE in a bank can be CLERK, MANAGER, CASHIER, ACCOUNTANT, etc. It is to be observed that the CLERK, MANAGER, CASHIER, ACCOUNTANT inherit some of the attributes of the EMPLOYEE.

In this example the superclass is EMPLOYEE and the subclasses are CLERK, MANAGER, and CASHIER. The subclasses inherit the attributes of the superclass. Since each member of the subclass is an ISA member of the superclass, the circle below the EMPLOYEE entity set represents ISA relationship.

Q) Explain Multiple Inheritance.

A subclass with more than one superclass is called a shared subclass. A subclass inherits attributes not only of its direct superclass, but also of all its predecessor

superclass, that is it has multiple inheritance from its superclasses. In multiple inheritance a subclass can be subclass of more than one superclass.

Example of Multiple Inheritance

Consider a person in an educational institution. The person can be employee, alumnus, and student. The employee entity can be staff or faculty. The student can be a graduate student or a postgraduate student. The postgraduate student can be a teaching assistant. If the postgraduate student is a teaching assistant, then he/she inherits the characteristics of the faculty as well as student class. That is the teaching assistant subclass is a subclass of more than one superclass (faculty, student). This phenomenon is called multiple inheritance and is shown in the Fig. 2.2.

Q) Explain briefly about Constraints on Specialization and Generalization

The constraints on specialization and generalization can be broadly classified into disjointness and completeness. The disjointness constraint allows us to specify whether an instance of a supertype may simultaneously be a member of two or more subtypes.

In disjointness we have two categories (1) Overlap and (2) Disjoint.

In completeness we have two categories (1) Total and (2) Partial.

The completeness constraint addresses the question whether an instance of a supertype must also be a member of at least one subtype.

1. Overlap Constraint

Overlap refers to the fact that the same entity instance may be a member of more than one subclass of the specialization.

Example of Overlap Constraint

Consider the example of ANIMAL entity, which can be further subdivided into LAND ANIMAL and WATER ANIMAL. Consider the example of Frog and Crocodile which can live in both land and water hence the division of ANIMAL into LAND and WATER animals is an example of overlap constraint.

2. Disjoint Constraint

Disjoint refers to the fact that the same entity instance may be a member of only one subclass of the specialization.

Example of Disjointness Constraint

Consider the example of CATALOGUE. The CATALOGUE is a superclass, which can be further subdivided into BOOKS, JOURNALS, and PERIODICALS.

This falls under disjointness because a BOOK entity can be neither JOURNAL nor PERIODICAL.

4. Partial Specialization

Partial completeness refers to the fact that an entity instance in the superclass need not be a member of any subclass in the specialization. With partial specialization, an instance of a supertype may or may not be a member of any subtype.

Example of Partial Specialization

Consider the PERSON specialization into EMPLOYEE and STUDENT. This is an example of partial specialization because there can be a person who is unemployed and does not study

Q) Explain briefly about Aggregation and Composition.

Relationships among relationships are not supported by the ER model. Groups of entities and relationships can be abstracted into higher level entities using aggregation. Aggregation represents a “HAS-A” or “IS-PART-OF” relationship between entity types. One entity type is the whole, the other is the part.

Aggregation allows us to indicate that a relationship set participates in another relationship set.

Consider the example of a driver driving a car. The car has various components like tires, doors, engine, seat, etc., which varies from one car to another.

Relationship drives is insufficient to model the complexity of this system. Partof relationships allow abstraction into higher level entities. In this example engine, tires, doors, and seats are aggregated into car. Composition is a stronger form of aggregation where the part cannot exist without its containing whole entity type and the part can only be part of one entity type.

Consider the example of DEPARTMENT has PROJECT. Each project is associated with a particular DEPARTMENT. There cannot be a PROJECT without DEPARTMENT. Hence DEPARTMENT has PROJECT is an example of composition.

Composition is a stronger form of aggregation where the part cannot exist without its containing whole entity type and the part can only be part of one entity type.

Consider the example of DEPARTMENT has PROJECT. Each project is associated with a particular DEPARTMENT. There cannot be a PROJECT without DEPARTMENT. Hence DEPARTMENT has PROJECT is an example of composition.

Q) Explain briefly about Entity Clusters.

EER diagrams are difficult to read when there are many entities and relationships.

One possible solution is to group entities and relationships into entity clusters. Entity cluster is a set of one or more entity types and associated relationships grouped into a single abstract entity type. Entity cluster behaves like an entity type; hence entity clusters and entity types can be further grouped to form a higher level entity cluster. Entity clustering is a hierarchical decomposition of a macrolevel view of the data model into finer and finer views, eventually resulting in the full detailed data model.

To understand entity cluster, consider the example of Hospital Management.

In hospital, the DOCTORS treat the PATIENT. The DOCTORS are paid by the MANAGEMENT which builds buildings. The DOCTORS can be either general physician or specialist like those with MS or MD. The patient can be either inpatient or outpatient. It is to be noted that only outpatient will be allotted bed. If we have to represent the earlier ideas, it can be done using EER diagram as shown in Fig. 2.3. The EER diagram is found to be complex; the same idea is represented using Entity Clusters as shown in Fig. 2.4. Here the DOCTOR specialization is clustered into DOCTORS entity and the PATIENT specialization is clustered into simply PATIENT. At the first glance, it may look like reduction of EER model to ER model, but it is not so. Here the entities as well as relationships are clustered into simply entity set.

Q) Explain Connection trapes and its types.

Connection Traps

Connection trap is the misinterpretation of the meaning of certain relationships.

This connection traps can be broadly classified into fan and chasm trap.

Any conceptual model will contain potential connection traps. An error in the interpretation of the meaning of the relationship may cause the database to be incapable of storing certain information. Both the fan and chasm trap arise when the relationships appear to exist between entity types, but the links between occurrences may be ambiguous or not exist. Related groups of entities could become clusters.

1. Fan Trap

Fan trap occurs when the model represents a relationship between entity types but the pathway between certain entity occurrences is ambiguous. Fan trap occurs when 1–M relationships fan out from a single entity. In order to understand the concept of Fan trap, consider the following example

Contractor works in a team. . . . . . . . . Statement (1)

Team develops projects. . . . . . . . . . . . Statement (2)

Statement (1) represents M–1 relationship. Statement (2) represents 1–M relationship. But the information about which contractors are involved in developing which projects is not clear.

Consider another example of Fan trap.

Department is on Site. . . . . . . . . Statement (1)

Site employs Staff. . . . . . . . . . . . . . . Statement (2)

Statement (1) represents M–1 relationship, because many departments may be in a single site. Statement (2) represents 1–M relationships. However which staff works in a particular department is ambiguous. The fan trap is resolved by reconstructing the original ER model to represent the correct association

2. Chasm Trap

A chasm trap occurs when a model suggests the existence of a relationship between entity types, but the pathway does not exist between certain entity occurrences. It occurs where there is a relationship with partial participation, which forms part of the pathway between entities that are related. Consider the relationship shown later.

A single branch may be allocated to many staff who oversees the management of properties for rent. It should be noted that not all staff oversee property and not all property is managed by a member of staff. Hence there exist a partial participation of Staff and Property in the relation “oversees,” which means that some properties cannot be associated with a branch office through a member of staff. Hence the model has to modified as shown later.

Q) What are the Advantages of ER Modeling.

An ER model is derived from business specifications. ER models separate the information required by a business from the activities performed within a business. Although business can change their activities, the type of information tends to remain constant. Therefore, the data structures also tend to be constant. The advantages of ER modeliSng are summarized later:

1. The ER modeling provides an easily understood pictorial map for the database design.

2. It is possible to represent the real world problems in a better manner in ER modeling.

3. The conversion of ER model to relational model is straightforward.

4. The enhanced ER model provides more flexibility in modeling real world problems.

5. The symbols used to represent entity and relationships between entities are simple and easy to follow.