isom mis3150 data and information management lecture 2 - data and process modeling arijit sengupta
TRANSCRIPT
ISOM
MIS3150 Data and Information Management
Lecture 2 - Data and Process Modeling
Arijit Sengupta
ISOM
Structure of this semester
Database Fundamentals
Relational Model
Normalization
ConceptualModeling Query
Languages
AdvancedSQL
0. Intro 1. Design
Newbie Users ProfessionalsDesigners
Java DB Applications –
JDBC
4. Applications
MIS3150
2. Querying
Developers
Transaction Management
DataMining
3. AdvancedTopics
ISOM
Today’s Buzzwords
• Data Modeling• Process Modeling• Data Flow Diagrams• Entity-Relationship Models• Cardinality and Participation Constraints• Weak Entities• Generalization Hierarchies
ISOM
So, where are we?
Analysis
Design
Implementation
Testing
Installation
ProposalRequirements
NormalizationModeling
Schema design
TablesIndexesQueries
Optimization
ISOM
Objectives of this lecture
• Describe the process inherent in a system• Present a system process in a concise
diagrammatic form• Describe the system data in terms of
conceptual objects and relationships between them
• Translate such conceptual descriptions into actual tables
ISOM
Benefits of Conceptual Design
• Projects without a strong conceptual design are more likely to fail
• Design is one of the most important aspects of project and business process quality management standards: ISO 9000 CMM
• Designs are typically network structured, not flat like databases
• Literature in Relational Model shows Benefits of Conceptual Design in user performance
ISOM
Database Modeling
• Process Models Overview of process components Inputs and outputs of different processes Data sources and destinations Mode of data flow between processes
• Data Models Model only the data, no process Different components of the data Relationships between primary data components
ISOM
Models, method, and media
• A modeldescribes business or organizationseparates operation from technology
• Good modeling requires good methodologiesencompass data, process, decisions richly expressive and provide for levels of analysissimple representation
• Modeling mediumboth formal and visual
ISOM
Data Flow medium
• Notation:Source: boxProcess (transform): box with rounded cornersFile (store): box open on rightDestination: boxFlow: arrow
• Structure: “Explosion” of processes (recursion on structure)
ISOM
Data Flow Diagrams
ISOM
DFD rules
• Start with a very basic overview of complete process, showing only the most important processes, sources, destinations, and files
• Recursively “explode” each of the processes (note: processes only!):preserve inputs and outputspreserve file accessesnew processes, files and sources/destinations can
be created, but cannot be used from previous levels if not directly used in the previous level
ISOM
Overview of Data Modeling
• Conceptual design: (ER Model is used at this stage.) What are the entities and relationships in the
enterprise? What information about these entities and
relationships should we store in the database? What are the integrity constraints or business rules
that hold? A database `schema’ in the ER Model can be
represented pictorially (ER diagrams). Can map an ER diagram into a relational schema.
ISOM
ER Model Basics
• Entity: Real-world object distinguishable from other objects. An entity is described (in DB) using a set of attributes.
• Entity Set: A collection of similar entities. E.g., all employees. All entities in an entity set have the same set of
attributes. (Until we consider ISA hierarchies, anyway!)
Each entity set has a key. Each attribute has a domain.
Employees
ssnname
dob
ISOM
Alternative Entity Representations
Employee------------------------
SSNNameDob
EmployeeSSN
NameDob
Employee
SSNNameDob
ISOM
ER Model Basics (Contd.)
• Relationship: Association among two or more entities. E.g., Attishoo works in Pharmacy department.
• Relationship Set: Collection of similar relationships. An n-ary relationship set R relates n entity sets E1 ... En; each
relationship in R involves entities e1 E1, ..., en En
• Same entity set could participate in different relationship sets, or in different “roles” in same set.
salary
dname
budgetdid
sincename
Works_In DepartmentsEmployees
ssn
Reports_To
salary
name
Employees
subor-dinate
super-visor
ssn
ISOM
Model this
An auto repair shop provides services to vehicles brought in by customers. A customer may own multiple vehicles that they bring in for service. Each service request is assigned to a technician. A service consists of different jobs that are assigned fees. A service may need parts as well. The customer is given an invoice with details on all the fees and parts costs.
1.What should be modeled?
2.Which items should be modeled as entities?
3.Which items should be modeled as relationships?
4.Which items should be modeled as attributes?
ISOM
A thumb rule to modeling
• Major nouns become entities
• Minor nouns become attributes
• Verbs connecting major nouns become relationships
ISOM
Major nouns in our passage?
ISOM
Minor nouns in our passage?
ISOM
Verbs in our passage?
ISOM
ER model for our exercise
ISOM
Business Rules
• A department must have one and only one manager
• A manager may manage multiple departments
• An employee works in only one department
• A department (of course) has many employees
ISOM
Participation Constraints
• Does every department have a manager? If so, this is a participation constraint: the participation of
Departments in Manages is said to be total (vs. partial).• Every did value in Departments table must appear in a
row of the Manages table (with a non-null ssn value!)
salaryname dname
budgetdid
sincename dname
budgetdid
since
Manages
since
ssn
Works_In
0,M 1,1
1,1 1,M
Employee Department
ISOM
Structural Constraints
• ParticipationDo all entity instances participate in at least one
relationship instance?
• CardinalityHow many relationship instances can an entity
instance participate in?
(min,max) (min,max)
Participation Cardinality
0 -- Partial 1 -- one
1 -- Total (Mandatory) M -- more than one
ISOM
Understanding P/C constraints
Employee Department
works_in1:1 1:M
John
Mary
Susan
Jack
Peter
Sally
Accounting
Sales
Development
manages1:10:M
ISOM
Many-Many relationships
Student Coursetakes0:M 0:M
John
Mary
Susan
Jack
Peter
Sally
MIS415
MIS215
MIS345
MIS490
ISOM
Alternative Approaches
• Arity approach
• Crow’s foot approach (as in book)
• Minmax approach
• For this class, use ONLY the Participation-Cardinality approach – this is what will be used in assignments and exams
ISOM
Back to our Auto Service Example
• What are the participation/cardinality constraints of the relationships?
• Owns -• Assigned to -• Consists of -• Needs part –• ?
ISOM
Weak Entities
• A weak entity can be identified uniquely only by considering the primary key of another (owner) entity. Owner entity set and weak entity set must participate in a one-to-
many relationship set (one owner, many weak entities). Weak entity set must have total participation in this identifying
relationship set.
salary
name
agepname
DependentsEmployees
ssn
Policy
cost
0:M 1:1
ISOM
Point to ponder
• Is there a weak entity in the auto service example?
ISOM
ISA (`is a’) Hierarchies
Contract_Emps
namessn
Employees
lot
hourly_wages
Hourly_Emps
contractid
hours_worked
As in C++, or other PLs, attributes are inherited.If we declare A ISA B, every A entity is also considered to be a B entity.
• Overlap constraints: Can Joe be an Hourly_Emps as well as a Contract_Emps entity? (Allowed/disallowed)
• Covering constraints: Does every Employees entity also have to be an Hourly_Emps or a Contract_Emps entity? (Yes/no)
• Reasons for using ISA: To add descriptive attributes specific to a subclass. To identify entitities that participate in a relationship.
ISOM
Stop and think
• Is there an IS-A hierarchy in the auto service example?
• What would it do to the design?
ISOM
Conceptual Design Using the ER Model
• Design choices: Should a concept be modeled as an entity or an attribute? Should a concept be modeled as an entity or a relationship? Identifying relationships: Binary or ternary? Aggregation?
• Constraints in the ER Model: A lot of data semantics can (and should) be captured. But some constraints cannot be captured in ER diagrams.
ISOM
Entity vs. Attribute
• Should address be an attribute of Employees or an entity (connected to Employees by a relationship)?
• Depends upon the use we want to make of address information, and the semantics of the data: If we have several addresses per employee,
address must be an entity (since attributes cannot be set-valued).
If the structure (city, street, etc.) is important, e.g., we want to retrieve employees in a given city, address must be modeled as an entity (since attribute values are atomic).
ISOM
Converting model to design
• Many-to-many relationshipsEach entity becomes a tableThe relationship becomes a tablePKs of entities becomes FKs in the relationship
Student( ) Course( ) Takes( )
takesStudent CourseStudentID
NameClass
Major
CoursenoCoursenameCredits
semester
0:M 0:M
ISOM
Model to design (contd.)
• 1-Many relationshipsEntities become tablesCopy PK of multi-participant to single
participantCopy attributes of relationship to single
participant (why?)
includesComputer PartComputerID
MakeModel
Year
PartnoTypeMake
installdate
1:M 0:1
ISOM
Model to design (contd.)
• 1-1 relationshipsEntities can be merged, orcopy PK of any entity to the other
• GeneralizationCopy PK of parent entity to child entity as FK,
as well as PK
• Weak entitiesCopy PK of controlling entity to weak entity
as FK as well as part of PK
ISOM
Lets convert our autoservice
ISOM
Summary of Conceptual Design
• Conceptual design follows requirements analysis, Yields a high-level description of data to be stored
• ER model popular for conceptual design Constructs are expressive, close to the way people think
about their applications.
• Basic constructs: entities, relationships, and attributes (of entities and relationships).
• Some additional constructs: weak entities, ISA hierarchies, and aggregation.
• Note: There are many variations on ER model.
ISOM
Summary of ER (Contd.)
• Several kinds of integrity constraints can be expressed in the ER model: key constraints, participation constraints, and overlap/covering constraints for ISA hierarchies. Some foreign key constraints are also implicit in the definition of a relationship set. Some constraints (notably, functional
dependencies) cannot be expressed in the ER model.
Constraints play an important role in determining the best database design for an enterprise.
ISOM
Summary of ER (Contd.)
• ER design is subjective. There are often many ways to model a given scenario! Analyzing alternatives can be tricky, especially for a large enterprise. Common choices include:
• Entity vs. attribute, entity vs. relationship, binary or n-ary relationship, whether or not to use ISA hierarchies
• Ensuring good database design: resulting relational schema should be analyzed and refined further. FD information and normalization techniques are especially useful.
ISOM
Class Exercise
Design an ER Model for a hospital system, with the following case description. Add other assumptions as needed.
The hospital database stores data about patients, their admission and discharge from hospital’s departments and their treatments. For each patient, we know the name, address, sex, social security number. For each department we know the department’s name, its location, the name of the doctor who heads it, the number of beds available, and the number of beds occupied. A doctor may work in several departments, but may only be the head in one department. Each patient goes through multiple treatments during hospitalization; for each treatment we store its name, duration and the possible reactions to it that the patient may have. A treatment may have one or more follow-up treatments.
Items to ponder:What other constraints can we apply on this model?