Lecture 31: Databases II – Logical Database Models

Objectives of this lecture

q Learn some of the logical database models, namely: Entity – Relationship, Hierarchical, Network and Relational models.

What is a logical data model?

q A logical data model is a design tool used in the design of a database system to emphasize features of interest to the user and makes interaction with a database management system transparent.

q Logical data models interpret the data is the context of the application.

q Logical data models are used for documenting and integrating data resources, designing information systems and designing and implementing the physical databases.

q A good logical data model is independent of the DBMS

q There are many approaches to logical data models. We explore a few of them:

Entity-Relationship Model

q Recall that a data entity is a real or abstract object described by a collection of attributes. A collection of entities is called an entity set. For example, a collection of all students taking a particular course forms an entity set.

q The link or bond among different entities is called a relation. The relationship among entities can be category (similar entities) or connection (dissimilar entities). The collection of relationships is called a relationship set.

q The interaction of entity sets with relationship sets is the basis for entity-relationship (E.R) model.

q Consider two entity sets x and y, Where x = {x1, x2, x3, …xn} and

Y = {y1, y2, y3, …yn}.

If each xi is related to exactly one yi and each yi is related to exactly one xi, then a one-to-one relationship exists between entity sets x and y

q When viewed collectively, all the entities in set x form a relationship with the entities in set y and vice-versa. This collection of relationships form the relationship set between x and y

q Entity sets are usually represented by a rectangle and relationship sets are represented by a diamond. The following example shows two entity sets; Student and Student_ID, with identifier as the relationship set. The two arrows show that the relation is one-to-one.:

q Relationships can also be many-to-one, one-to-one or many-to-many. I.e., many entities can be related to one entity, one entity can be related to many entities, or an empty can be related to many entities.

q The following figure shows an example of many-to-many relationship.

Entity Relationship Entity

q To implement E-R modes, one must convert each relationship set and each entity set to a data table. The set of all data tables forms the database for the E-R model.

The Hierarchical & Network Models

q A database model in the form of a tree where the nodes of the tree are the segments and the branches of the tree are the links is called a hierarchical data model

q Each child must have a link that points to its parents.

q The tree has a hierarchical structure determined by a sequence key that is part of the segment.

q A parent may or may not point to a child even though it is linked to it. The following figure illustrates the hierarchical model.

q The hierarchical model is like the entity relationship model. In each case entities (segments) are related (linked) to entities.

q The Network data Model is like hierarchical model except that the relationships are not based on a tree structure. Instead any segment can be linked to another segment.

The Relational Model

q The most popular logical data model in use today is the relational model, which is noted for its consistency, simplicity and data independence.

q It is composed of relations attributes, domains, keys, tuples and their representations.

q A relation is a table of rows and columns. Each column of the table is an attribute.

q The domain of each attribute is the collection of values that can be assigned to a particular attribute.

q A principal key is one or more attribute values that uniquely identify an entity instance.

q A tuple is an ordered sequence of elements. Example the sequence (p,q,r,s,t,u) is a 6-tuple. Each row is an entity instance represented by a tuple.

q Typically, relations have a representation consisting the relation name, followed by a list of attributes in form of a tuple with the principal key highlighted.

e.g., student (ID-NO, Name, Major, Average)

q The following shows a typical relational data model called an instance table

q The following table defines another relation for the entity courses with attributes couseID, Instructor, CORE, where core means a course is required for all major.

q In relational databases, new relations can be generated from others. For example, the relation “must enroll in” can be defined between the relation student and course.

q In this case, the student is called the parent entity and the course is called the child entity.

q The relationship can be achieved by transferring the principal key of the parent entity to the child.

q The following shows the diagrammatic representation and the resulting instance table for the new relationship, taking major to be the principal key.

q Relational model also has relational operators both unary and binary that can be used to form relations from other relations

Examples:

select: is unary operator that chooses certain rows of a relation based on same condition

q e.g., select [from student] Average>3.0)

q Project : This is another unary operator that chooses entities according to some attributes with duplication removed.

q e.g., project [from student] (major)

MAJOR

History

English

Physics

q Join: is a binary operator which combines the existing relation according to same condition.

q e.g., join [student] (English major) and (instructor) [course]