Scribd
Upload
32 views34 pages

Understanding ER Model Components

The document discusses data modeling using the Entity-Relationship (ER) model. It defines key concepts of the ER model including entities, attributes, relationships and cardinality. It provides examples of modeling an airport database and converting the ER model to relational tables. Questions at the end ask the reader to: 1) Develop an ER model for a railway reservation system and airport database. 2) Convert the ER models to relational schemas.

Uploaded by

gunif
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPT, PDF, TXT or read online on Scribd
32 views34 pages

Understanding ER Model Components

The document discusses data modeling using the Entity-Relationship (ER) model. It defines key concepts of the ER model including entities, attributes, relationships and cardinality. It provides examples of modeling an airport database and converting the ER model to relational tables. Questions at the end ask the reader to: 1) Develop an ER model for a railway reservation system and airport database. 2) Convert the ER models to relational schemas.

Uploaded by

gunif
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPT, PDF, TXT or read online on Scribd

Data Modeling : ER Model

Why We Model

We build models of complex systems because we cannot comprehend any such system in reality. Need to develop a common understanding of the problem and the solution Cannot afford a trial-and-error approach to communicate the desired structure and behavior of our systems

Why We Model

to visualize and control systems architecture to understand the system we are building. to manage risk

How We Model

The choice of which model we use has a depend on how a problem is attacked and how a solution is shaped No single model is sufficient; every complex system is best approached through a set of independent models. The best models are connected to reality

DATA MODEL

It is an abstraction of the reality : ignores unnecessary details It represents operational data about real world events, entities, activities, etc. Model may be at various levels depending of requirements :

logical or physical external, conceptual, internal is easy to understand has a few concepts permits top-down specifications

model offers concepts, constructs and operations must capture meaning of data which help us in interpreting and manipulating data

ENTITY-RELATIONSHIP (ER) MODEL

Represents overall logical structure of information


grouping of data elements inter-relationships between groups. a few concepts

simple and easy-to-use permits top-down approach for controlling details useful as a tool for communication between designer and user during requirements analysis and conceptual design

ENTITY

An object that exists Distinguishable from other objects. Examples : a book, an item, a student, a purchase order

(a/an above indicates that we are referring to one of these)

ENTITY SET

A set of similar entities Example : set of all books in a library


set of all customers

entity set also called entity type or entity class

ATTRIBUTE

an entity has a set of attributes attribute defines property of an entity attribute has value for each entity value may change over time

ATTRIBUTE

Example : entity set BOOK has the following attributes


TITLE ACC-NO PUBLISHER YEAR PRICE ISBN AUTHOR

a particular book has value for each of the above attributes

ATTRIBUTE

an attribute may be multi-valued, i.e., it has more than one value for a given entity; e.g., a book may have many authors an attribute which uniquely identifies entities of a set is called primary key attribute of that entity set.

EXAMPLE : A COLLEGE

STUDENT : rollno, name, hostel-no., date-of-birth COURSE DEPT : courseno, name, credits : name, tel-phone TEACHER : empno, name, rank, room-no., tel-phone

RELATIONSHIP

Represents association among entities e.g., a particular book is a text for particular course
book Database Systems by C.J. Date is text for course identified by code CS644

e.g., student GANESH has enrolled for course CS644

RELATIONSHIP CARDINALITY

Cardinality : how many entities of an entity set participate in a relationship.


a relationship set R between entity sets A and B may be one of the following
one-to-one : one entity in A associated with at most one entity in B
one-to-many : one entity in A may be associated with zero/more number of entities in B. However, one entity in B can be associated with at most one entity from A. many-to-one : reverse of above definition (like a mathematical function)

many-to-many : one entity in A may be associated with any number of entities in B, and vice-versa.

E-R Diagram : Examples

Add some attributes to entities here Courses may have another course as pre-requisite

EXTENDED E-R MODEL

Generalization

To generalize from two or more entity sets and factor out commonality. Example : given two entities Faculty and Non-faculty,we can define a general entity called Employee Common attributes are factored out to define Employee entity;

Specialization

It is known as subset hierarchy specialization allows classification of an entity in subsets based on some distinguishing attribute/property we may have several specialization of same entity the subsets may have additional attributes

Going from E-R to Relational Data Model

Need to match ER model concepts : entity, relationship, attribute With Relational model concepts : relation, attribute

entity

Student

Student ( rollno, name, . )

rollno name .

E-R to Relational

1:1

Dept

Mgr MName

DNo

DEPT (DNo , . , MName ) MGR (MName , , DNo ) also keys; in one or both

E-R to Relational
1

Bank Branch Loc

Client

BName

ACno

Name

BKBranch (BName , Loc , . ) Client (Name , , BName , Loc , ACno ) The relationship is included in the entity on many side; It includes primary key of entity on one side and Relationship attributes, if any

E-R to Relational

Std

Reg Grade

Course

R#

C#

STD (R# , . ) COURSE (C# , ) REG (R# , C# , Grade)

Exercise: Airport database


keeps track of airplanes, their owners, airport employees and pilots Each airplane has a registration number, is of a particular plane type and is stored in a particular hanger. Each plane type has a model number, capacity and weight. Each hanger has a number, capacity and location. The database also keeps track of who owns which plane. Persons have name, address and phones. A person buys a plane on a particular date and cost. Each plane undergoes service many times. A service information contains date of work, nature, hours spent, cost, etc. Pilots and employees are persons. Pilots have a license number with validity and salary. Employees have a number, rank and salary. Each pilot is authorized to fly certain types of planes. Employees are involved in servicing of planes.

Q1. Prepare E-R model for above problem. Q2. Convert to the relational model

Exercises

Prepare E-R models and convert to relational schema

Railway Reservation

30 days in advance
trains, stations, quotas, coaches passengers, tickets, wait-list, etc.

Exercise : Portfolio Management

for individual investors


investments are made in shares, debentures, bonds, National saving certificates, various schemes like PPF, ELSS, mutual funds, etc. these may be acquired at public issue time, purchased from market, obtained as bonus (free), on rights-basis, etc. some investments have regular returns; e.g., yearly, 6-monthly, etc. at fixed or announced rates these will be sold in market or re-deemed, converted, etc. how, when and how much invested, what returns already obtained are important for the investor to know how good are his investments. At year end, he may wants to know the market value of his investments

Portfolio

Exercise :
Draw E-R diagram convert to relational scheme check if all relations are in 3NF

Common questions

Powered by AI

Cardinality defines the number of associations between entities and influences database design by determining how tables are linked. For instance, a many-to-many relationship in an ER model necessitates a linking table in a relational database to facilitate indirect connections, ensuring proper enforcement of data integrity and query efficiency .

Primary key attributes ensure integrity by uniquely identifying each record in a table, preventing duplicate entries and enabling precise retrieval and manipulation of data. They establish a stable reference point for relationships between tables, maintaining database consistency .

Achieving third normal form (3NF) eliminates data redundancy and dependency inconsistencies by ensuring that tables are properly structured. This aligns with data modeling principles by promoting data integrity, simplifying data management, and making the database more robust and scalable, reflecting a disciplined approach to data abstraction and normalization .

A top-down approach in data modeling using the ER model offers advantages such as focusing on high-level system design before detailing specific attributes and relationships. This approach aids in managing complexity, ensuring comprehensive coverage, and providing a clear framework for the subsequent addition of granular details .

Using multiple models is necessary because no single model can comprehensively capture all aspects of a complex system. Different models allow for independent examination of various facets of the system, which helps in managing complexity and ensuring a complete understanding. Each model provides unique insights that, together, offer a holistic view of the system .

The transition from an ER model to a relational data model involves mapping entities, relationships, and attributes to tables, constraints, and keys. Matching ER and relational concepts is crucial for accurately translating logical data representations into a physical database schema, ensuring operational integrity and consistency .

Attributes define the properties of an entity and contribute to identifying unique entities through primary key attributes. These attributes provide specific values for each entity instance, enabling differentiation and precise data management. For example, in a student entity, the 'rollno' attribute uniquely identifies each student .

An ER diagram for an airport database would define entities like Airplane, Owner, Employee, Hanger, and Service, each with relevant attributes (e.g., registration number, owner details). Relationships would depict which employees service which planes, while cardinalities reflect possible associations (e.g., one-to-many for airplane ownership).

Specialization and generalization enhance modeling capabilities by allowing the representation of hierarchical relationships among entities. Generalization consolidates common attributes into a generalized entity, while specialization divides an entity into subsets based on distinguishing attributes. This hierarchical organization enables precise modeling of complex datasets with shared characteristics .

The ER model facilitates communication between system designers and users by providing a simple, easy-to-use tool that represents the logical structure of information. It outlines entities, their attributes, and relationships, which makes it easier for both parties to visualize and discuss requirements and conceptual designs during the analysis phase .

You might also like