database 3 conceptual modeling and er
DESCRIPTION
TRANSCRIPT
![Page 1: Database 3 Conceptual Modeling And Er](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b5cefe4a7959cc268b45d9/html5/thumbnails/1.jpg)
February 23 , 2002 Dr. A. K. Ramani 11
DESIGN METHODS
In previous section– General steps in DB development process , ISA ,EDM
– Database Architectures
AimDesign of conceptual Data Model
Objectives
• Understand – Conceptual Data Modeling
– Concepts of E-R Modeling
– Model Example & Limitations
RECAPRECAP
![Page 2: Database 3 Conceptual Modeling And Er](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b5cefe4a7959cc268b45d9/html5/thumbnails/2.jpg)
February 23 , 2002 Dr. A. K. Ramani 22
DESIGN METHODS
Purpose:– create detailed specification of internal documents and tasks from
the EDMInput:
– EDM, usage statistics, and other information gathered during the analysis
Output:– ER-Diagram, Data Representation, Constraints, Task
DecompositionsTechniques:
– data modeling – top-down decomposition of tasks until their specification is
sufficiently detailed to allow a programmer to implement them– task decomposition may result in tasks replacing the original task or
in subtasks controlled by the original taskTools: ER-Model
APPROACHAPPROACH
![Page 3: Database 3 Conceptual Modeling And Er](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b5cefe4a7959cc268b45d9/html5/thumbnails/3.jpg)
February 23 , 2002 Dr. A. K. Ramani 33
DESIGN METHODS
To build a
What is 1st Step ?What are the choices,Why ?• User Requirements
- User is not sure - Precise specification is difficult
• Communication Problems- Analyst & user communication- User not willing to take responsibility
Technical - Skills of modeler - Design Methodology – Non
Standard
CONCEPTUAL MODELCONCEPTUAL MODEL
![Page 4: Database 3 Conceptual Modeling And Er](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b5cefe4a7959cc268b45d9/html5/thumbnails/4.jpg)
February 23 , 2002 Dr. A. K. Ramani 44
DESIGN METHODS
Feasibility study
Requirement Analysis & Specifications
Data Modeling Process Modeling
Implementation Prototyping Testing
DESIGN APPROACHDESIGN APPROACH
![Page 5: Database 3 Conceptual Modeling And Er](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b5cefe4a7959cc268b45d9/html5/thumbnails/5.jpg)
February 23 , 2002 Dr. A. K. Ramani 55
DESIGN METHODS
Requirements Collection & Analysis
Conceptual Design (G)
Logical Design (Blue Print)
Physical Design
Application Design
Methods Req. Collection
Conceptual Model
Data Base System
DBMS
Tools , OS
Requirements Specs
Conceptual Database Model
Logical Models Performance Tests
Final Schema
Application PGMS
DESIGN PHASESDESIGN PHASES
![Page 6: Database 3 Conceptual Modeling And Er](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b5cefe4a7959cc268b45d9/html5/thumbnails/6.jpg)
February 23 , 2002 Dr. A. K. Ramani 66
DESIGN METHODS
Conceptual Design
Logical Design
Physical Design
Application Design
Requirement Collection & Analysis
C,Windows,Power Builder
ORACLE
Relational DBs
ER & ERR Model
Questionnaire & Interview
DESIGN METHODSDESIGN METHODS
![Page 7: Database 3 Conceptual Modeling And Er](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b5cefe4a7959cc268b45d9/html5/thumbnails/7.jpg)
February 23 , 2002 Dr. A. K. Ramani 77
DESIGN METHODS
• Models can be useful when we want to examine or manage part of the real world
• The costs of using a model are often considerably lower than the costs of using or experimenting with the real world itself
• Examples:– airplane simulator– nuclear power plant simulator– flood warning system– model of US economy– model of a heat reservoir– map
WHY TO USE MODELWHY TO USE MODEL
![Page 8: Database 3 Conceptual Modeling And Er](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b5cefe4a7959cc268b45d9/html5/thumbnails/8.jpg)
February 23 , 2002 Dr. A. K. Ramani 88
DESIGN METHODS
A Map Is a Model of Reality
MODEL OF REALITYMODEL OF REALITY
![Page 9: Database 3 Conceptual Modeling And Er](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b5cefe4a7959cc268b45d9/html5/thumbnails/9.jpg)
February 23 , 2002 Dr. A. K. Ramani 99
DESIGN METHODS
• A model is a means of communication• Users of a model must have a certain amount of
knowledge in common• A model on emphasized selected aspects• A model is described in some language• A model can be erroneous• A message to map makers: “Highways are not
painted red, rivers don’t have county lines running down the middle, and you can’t see contour lines on a mountain” [Kent 78]
MODEL TO RAISE QUESTIONMODEL TO RAISE QUESTION
![Page 10: Database 3 Conceptual Modeling And Er](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b5cefe4a7959cc268b45d9/html5/thumbnails/10.jpg)
February 23 , 2002 Dr. A. K. Ramani 1010
DESIGN METHODS
– A process that construct an abstract model which represents the entities,relationships and activities of an enterprise of real world
– Putting a Real World Object on to Paper
Purpose of model is to sharpen the question
MYCOM.COM
US $ 10 b
MCA/MMS
IIPS
CONCEPTUAL MODELINGCONCEPTUAL MODELING
![Page 11: Database 3 Conceptual Modeling And Er](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b5cefe4a7959cc268b45d9/html5/thumbnails/11.jpg)
February 23 , 2002 Dr. A. K. Ramani 1111
DESIGN METHODS
Why Conceptual Modeling ?• Obtain better understanding of business• Enable the end-user communication• Discover design errors at early stage• Build a Solid Foundation• Ensure the quality• A DBMS independent DB designWhat to Model ?• Static information
– Data - Entities– Associations - Relationship among entities
• Dynamic Information– Process – operations/transactions– Integrity constraints – Business Rules / Regulations
CONCEPTUAL MODELINGCONCEPTUAL MODELING
![Page 12: Database 3 Conceptual Modeling And Er](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b5cefe4a7959cc268b45d9/html5/thumbnails/12.jpg)
February 23 , 2002 Dr. A. K. Ramani 1212
DESIGN METHODS
• Process - Oriented Approach– Focus on activities, process & operations
Data Flow Diagram
• Data – Oriented Approach– Focus on data & their relationship– Characteristics of Data captured– Data more complex than process– Rich data source is the GOAL– Data is more stable than process– Data Orientation - Longer life
• Object – Oriented ApproachCombine data and process
CONCEPTUAL MODELING APPROACHESCONCEPTUAL MODELING APPROACHES
![Page 13: Database 3 Conceptual Modeling And Er](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b5cefe4a7959cc268b45d9/html5/thumbnails/13.jpg)
February 23 , 2002 Dr. A. K. Ramani 1313
DESIGN METHODS
• Entity – relationship model (ER) introduced in 1976 by Peter Chen
• Extended ER model (EER) expanded the original ER with new concepts
Lecturer Course
SSN
NameTeach
ER MODELINGER MODELING
Entity• An Entity is a conceptual object
• Physically or conceptually exists
• Usually a noun in requirement specification
E.g. Lecturer , Course , Movie , Sales-order
![Page 14: Database 3 Conceptual Modeling And Er](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b5cefe4a7959cc268b45d9/html5/thumbnails/14.jpg)
February 23 , 2002 Dr. A. K. Ramani 1414
DESIGN METHODS
• Collection of Entities have same properties• An entity instance is a single occurrence of entity
type• Described once in metadata• A noun in requirement specifications• A true data entity have many instances,each with
distinguishing feature• A strong entity – exists independently like student ,
Course , Car• A weak entity – existence depends upon other entity
(identifying owner)• Dependent
ENTITY TYPESENTITY TYPES
![Page 15: Database 3 Conceptual Modeling And Er](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b5cefe4a7959cc268b45d9/html5/thumbnails/15.jpg)
February 23 , 2002 Dr. A. K. Ramani 1515
DESIGN METHODS
• Property , description of entities and entity types• Attribute type ( domain)
– Define all possible attribute values
• Attribute value , associated with individual entities• A noun or an objective in requirement specifications
IM-99-02Student
ID
DoB
Singh
4-9-77
Entities – Attributes
•Entities have ‘independent’ meaning e.g. Car , Student
•Attributes have no independent meaning ? ID
ATTRIBUTEATTRIBUTE
![Page 16: Database 3 Conceptual Modeling And Er](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b5cefe4a7959cc268b45d9/html5/thumbnails/16.jpg)
February 23 , 2002 Dr. A. K. Ramani 1616
DESIGN METHODS
• Uniquely identify individual instances of an entity
type– A key refers to one or a group of attributes as a whole– A key attribute refers to a component of a composite
key– Definition of a key changes with Data Semantics
• An entity type may have few keys – Primary key – One of candidate key– Secondary key – Other keys
• The primary key attribute (s) underlined• Choose key – will not change• Choose key – Not Null• Avoid INTELLIGENT KEYS
KEY ATTRIBUTEKEY ATTRIBUTE
![Page 17: Database 3 Conceptual Modeling And Er](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b5cefe4a7959cc268b45d9/html5/thumbnails/17.jpg)
February 23 , 2002 Dr. A. K. Ramani 1717
DESIGN METHODS
• Simple attribute– Can’t be broken into smaller values– Contains only atomic values
• Composite attribute– Has component attribute
• Single valued attribute– One only per attribute
• Multi – Valued attribute– Contains multiple values
ATTRIBUTE CLASSIFICATIONATTRIBUTE CLASSIFICATION
![Page 18: Database 3 Conceptual Modeling And Er](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b5cefe4a7959cc268b45d9/html5/thumbnails/18.jpg)
February 23 , 2002 Dr. A. K. Ramani 1818
DESIGN METHODS
Student
Roll No
Skill
Dob
F.Name
M.Name
L.Name
Name
Degree
EXAMPLEEXAMPLE
![Page 19: Database 3 Conceptual Modeling And Er](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b5cefe4a7959cc268b45d9/html5/thumbnails/19.jpg)
February 23 , 2002 Dr. A. K. Ramani 1919
DESIGN METHODS
• It is an association among instances of one or more
entities involved.– Label as– Verb in requirement specifications,in present tense &
descriptive– Example
– Model associations, not actions and process
Student Course
Faculty
Teach
take
advise
RELATIONSHIPRELATIONSHIP
![Page 20: Database 3 Conceptual Modeling And Er](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b5cefe4a7959cc268b45d9/html5/thumbnails/20.jpg)
February 23 , 2002 Dr. A. K. Ramani 2020
DESIGN METHODS
• How is an entity linked to relationship ? [Participation]
• How many relationship instances is an entity permitted to be linked to ? [cardinality]
• Relationship instance is an association between entity instances, where each instance includes exactly one entity from each participating entity type
Student Advise Faculty
Akr
Trupti
Kris
Ram
Mohan
Singh
JUSTIFICATIONJUSTIFICATION
![Page 21: Database 3 Conceptual Modeling And Er](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b5cefe4a7959cc268b45d9/html5/thumbnails/21.jpg)
February 23 , 2002 Dr. A. K. Ramani 2121
DESIGN METHODS
E1E2R
min,max min,max
Participation
0 – Partial
1 - Total
Cardinality
1 - - One
M - - More than One
O - - One
Advise Faculty
X
Z
Y
A
B
CD
Student
CARDINALITY AND PARTICIPATIONCARDINALITY AND PARTICIPATION
![Page 22: Database 3 Conceptual Modeling And Er](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b5cefe4a7959cc268b45d9/html5/thumbnails/22.jpg)
February 23 , 2002 Dr. A. K. Ramani 2222
DESIGN METHODS
Unary Relationship
Binary Relationship
Ternary/N-ary Relationship
RELATIONSHIP DEGREESRELATIONSHIP DEGREES
![Page 23: Database 3 Conceptual Modeling And Er](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b5cefe4a7959cc268b45d9/html5/thumbnails/23.jpg)
February 23 , 2002 Dr. A. K. Ramani 2323
DESIGN METHODS
Person Married
(0,1)
(0,1)
Employee Manager
(0,m)
(0,1)
Unary RelationshipRELATIONSHIP DEGREERELATIONSHIP DEGREE
![Page 24: Database 3 Conceptual Modeling And Er](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b5cefe4a7959cc268b45d9/html5/thumbnails/24.jpg)
February 23 , 2002 Dr. A. K. Ramani 2424
DESIGN METHODS
One –to-one binary RelationshipA BCD
X
Y
Z
A
One –to-Many binary Relationship
BCD
X
Y Z
E1 E2(1, 1) (0, m)
BINARY RELATIONSHIPBINARY RELATIONSHIP
![Page 25: Database 3 Conceptual Modeling And Er](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b5cefe4a7959cc268b45d9/html5/thumbnails/25.jpg)
February 23 , 2002 Dr. A. K. Ramani 2525
DESIGN METHODS
Many to Many binary Relationship
E1 E2R1(0, m) (1, m)
X
Y
Z
A
B
C
D
BINARY RELATIONSHIPBINARY RELATIONSHIP
Justification
![Page 26: Database 3 Conceptual Modeling And Er](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b5cefe4a7959cc268b45d9/html5/thumbnails/26.jpg)
February 23 , 2002 Dr. A. K. Ramani 2626
DESIGN METHODSCustomer
SELL
SalesmanCar
TERNARY RELATIONSHIPTERNARY RELATIONSHIP
CAR REP(0,1) (0, m)
Sell
Customer
(0,M)
![Page 27: Database 3 Conceptual Modeling And Er](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b5cefe4a7959cc268b45d9/html5/thumbnails/27.jpg)
February 23 , 2002 Dr. A. K. Ramani 2727
DESIGN METHODS
Unary
n-aryto
RELATIONSHIP DEGREERELATIONSHIP DEGREE
![Page 28: Database 3 Conceptual Modeling And Er](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b5cefe4a7959cc268b45d9/html5/thumbnails/28.jpg)
February 23 , 2002 Dr. A. K. Ramani 2828
DESIGN METHODS
Relationship attributes• Attributes describing relationship, like when ,where, what
FacultyStudent Advise
MemoTime
Date
• A relationship instance must include all participants
• Need to be careful when converted to binary relationship
RELATIONSHIP ATTRIBUTESRELATIONSHIP ATTRIBUTES
![Page 29: Database 3 Conceptual Modeling And Er](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b5cefe4a7959cc268b45d9/html5/thumbnails/29.jpg)
February 23 , 2002 Dr. A. K. Ramani 2929
DESIGN METHODS
Attributes on a relationship might suggest to convert it to entity , termed (---) as associative entity.
CourseEMP Completes
Date
m m
CERT
Date Number
ASSOCIATIVE ENTITYASSOCIATIVE ENTITY
![Page 30: Database 3 Conceptual Modeling And Er](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b5cefe4a7959cc268b45d9/html5/thumbnails/30.jpg)
February 23 , 2002 Dr. A. K. Ramani 3030
DESIGN METHODS
• Conditions– Result entity independent meaning
– Result entity participates in one or more other relationships
Other Notations
Mandatory One
Mandatory Many
Optional One
Optional Many
CONDITIONS AND NOTATIONSCONDITIONS AND NOTATIONS
![Page 31: Database 3 Conceptual Modeling And Er](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b5cefe4a7959cc268b45d9/html5/thumbnails/31.jpg)
February 23 , 2002 Dr. A. K. Ramani 3131
DESIGN METHODS
BASIC E-R NOTATIONBASIC E-R NOTATION
![Page 32: Database 3 Conceptual Modeling And Er](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b5cefe4a7959cc268b45d9/html5/thumbnails/32.jpg)
February 23 , 2002 Dr. A. K. Ramani 3232
DESIGN METHODS
SAMPLE ER DIAGRAMSAMPLE ER DIAGRAM
![Page 33: Database 3 Conceptual Modeling And Er](https://reader033.vdocuments.us/reader033/viewer/2022061218/54b5cefe4a7959cc268b45d9/html5/thumbnails/33.jpg)
February 23 , 2002 Dr. A. K. Ramani 3333
DESIGN METHODS
• Conceptual Modeling – important skill• Conceptual Schema important design
document – independent of DBMS• Entities , types• Relationships Unary – n-ary• Associations
– Cardinality (Connectivity)– Participation (Degree)
SUMMARYSUMMARY