data modeling yong choi school of business csub. part # 2 2 study objectives understand concepts of...
TRANSCRIPT
![Page 1: Data Modeling Yong Choi School of Business CSUB. Part # 2 2 Study Objectives Understand concepts of data modeling and its purpose Learn how relationships](https://reader036.vdocuments.us/reader036/viewer/2022062805/5697bfc91a28abf838ca91bf/html5/thumbnails/1.jpg)
Data Modeling
Yong ChoiSchool of Business
CSUB
![Page 2: Data Modeling Yong Choi School of Business CSUB. Part # 2 2 Study Objectives Understand concepts of data modeling and its purpose Learn how relationships](https://reader036.vdocuments.us/reader036/viewer/2022062805/5697bfc91a28abf838ca91bf/html5/thumbnails/2.jpg)
Part # 2
2
Study Objectives Understand concepts of data modeling and
its purpose Learn how relationships between entities are
defined and refined, and how such relationships are incorporated into the database design process
Learn how ERD components affect database design and implementation
Learn how to interpret the modeling symbols
![Page 3: Data Modeling Yong Choi School of Business CSUB. Part # 2 2 Study Objectives Understand concepts of data modeling and its purpose Learn how relationships](https://reader036.vdocuments.us/reader036/viewer/2022062805/5697bfc91a28abf838ca91bf/html5/thumbnails/3.jpg)
Part # 2
3
Why Data Modeling?Data Model by CASE tool = Actual
Database Represent “reality” of the actual database Blue print: documentation Effective Communication Tool User involvement Identify the business rules to be stored in
the database Independence from a particular DBMS Example of data model by CASE tool on
the website
![Page 4: Data Modeling Yong Choi School of Business CSUB. Part # 2 2 Study Objectives Understand concepts of data modeling and its purpose Learn how relationships](https://reader036.vdocuments.us/reader036/viewer/2022062805/5697bfc91a28abf838ca91bf/html5/thumbnails/4.jpg)
Part # 2
4
Conceptual data modelingConceptual data modeling
The conceptual data modeling revolves around discovering and analyzing organizational and users data requirements.
What data is important What data should be maintained
The major activity of this phase is identifying entities, attributes, and their relationships to construct model using the Entity Relationship Diagram methodology.
![Page 5: Data Modeling Yong Choi School of Business CSUB. Part # 2 2 Study Objectives Understand concepts of data modeling and its purpose Learn how relationships](https://reader036.vdocuments.us/reader036/viewer/2022062805/5697bfc91a28abf838ca91bf/html5/thumbnails/5.jpg)
Part # 2
5
Entity Relationship diagram (ERD) Entity Relationship diagram (ERD)
Data modeling methodology Developed by Peter Chen (1976).
See his original ERD article on the class website
ERD is commonly used to: Translate different views of data among managers,
users, and programmers to fit into a common framework.
Define data processing and constraint requirements to help us meet the different views.
Help implement the database.
![Page 6: Data Modeling Yong Choi School of Business CSUB. Part # 2 2 Study Objectives Understand concepts of data modeling and its purpose Learn how relationships](https://reader036.vdocuments.us/reader036/viewer/2022062805/5697bfc91a28abf838ca91bf/html5/thumbnails/6.jpg)
Part # 2
6
Basic ERD Elements
Entity : a collection of people, places, objects, events, concepts of interest (a table)
Entity instance – a member of the Entity : a person, a place, an object … (a row in a table)
Attribute - property or characteristic of interest of an entity (a field in a table)
Relationship – association between entities (corresponds to primary key-foreign key equivalencies in related tables)
![Page 7: Data Modeling Yong Choi School of Business CSUB. Part # 2 2 Study Objectives Understand concepts of data modeling and its purpose Learn how relationships](https://reader036.vdocuments.us/reader036/viewer/2022062805/5697bfc91a28abf838ca91bf/html5/thumbnails/7.jpg)
Part # 2ERD using Chen’ Notation (first - original)
![Page 8: Data Modeling Yong Choi School of Business CSUB. Part # 2 2 Study Objectives Understand concepts of data modeling and its purpose Learn how relationships](https://reader036.vdocuments.us/reader036/viewer/2022062805/5697bfc91a28abf838ca91bf/html5/thumbnails/8.jpg)
Part # 2
8
Chen’s NotationChen’s Notation
Entities rectangle containing the entity’s name.
Attributes oval containing the attribute’s name.
Relationships diamond containing the relationship’s
name.
![Page 9: Data Modeling Yong Choi School of Business CSUB. Part # 2 2 Study Objectives Understand concepts of data modeling and its purpose Learn how relationships](https://reader036.vdocuments.us/reader036/viewer/2022062805/5697bfc91a28abf838ca91bf/html5/thumbnails/9.jpg)
Part # 2
9
Steps for creating an ERD
1. Identify entities2. Identify attributes 3. Identify relationships
![Page 10: Data Modeling Yong Choi School of Business CSUB. Part # 2 2 Study Objectives Understand concepts of data modeling and its purpose Learn how relationships](https://reader036.vdocuments.us/reader036/viewer/2022062805/5697bfc91a28abf838ca91bf/html5/thumbnails/10.jpg)
Part # 2
10
Entity
“A fundamental THING of relevance to the enterprise about which data may be kept”
What should be an Entity: both tangible & intangible
An object that will have many instances in the database An object that will be composed of multiple attributes An object that we are trying to model
What should NOT be an Entity: A user of the database system An output of the database system (e.g. a report)
![Page 11: Data Modeling Yong Choi School of Business CSUB. Part # 2 2 Study Objectives Understand concepts of data modeling and its purpose Learn how relationships](https://reader036.vdocuments.us/reader036/viewer/2022062805/5697bfc91a28abf838ca91bf/html5/thumbnails/11.jpg)
Part # 2
11
ERD using IE Notation (most popular)
![Page 12: Data Modeling Yong Choi School of Business CSUB. Part # 2 2 Study Objectives Understand concepts of data modeling and its purpose Learn how relationships](https://reader036.vdocuments.us/reader036/viewer/2022062805/5697bfc91a28abf838ca91bf/html5/thumbnails/12.jpg)
Part # 2
12
Entity InstanceEntity instance: a single occurrence of an entity.
6 instances
Student ID
Last Name
First Name
2144 Arnold Betty
3122 Taylor John
3843 Simmons Lisa
9844 Macy Bill
2837 Leath Heather
2293 Wrench Tim
Entity: student
instance
![Page 13: Data Modeling Yong Choi School of Business CSUB. Part # 2 2 Study Objectives Understand concepts of data modeling and its purpose Learn how relationships](https://reader036.vdocuments.us/reader036/viewer/2022062805/5697bfc91a28abf838ca91bf/html5/thumbnails/13.jpg)
Part # 2
13
“describe property or characteristic of an entity ” Entity: Employee Attributes:
Employee-Name Address (composite) Phone Extension Date-Of-Hire Job-Skill-Code Salary
AttributesAttributes
![Page 14: Data Modeling Yong Choi School of Business CSUB. Part # 2 2 Study Objectives Understand concepts of data modeling and its purpose Learn how relationships](https://reader036.vdocuments.us/reader036/viewer/2022062805/5697bfc91a28abf838ca91bf/html5/thumbnails/14.jpg)
Part # 2
14
Classes of attributes
Simple attribute Composite attribute Derived attributes Single-valued attribute Multi-valued attribute
![Page 15: Data Modeling Yong Choi School of Business CSUB. Part # 2 2 Study Objectives Understand concepts of data modeling and its purpose Learn how relationships](https://reader036.vdocuments.us/reader036/viewer/2022062805/5697bfc91a28abf838ca91bf/html5/thumbnails/15.jpg)
Part # 2
15
A simple attribute cannot be subdivided. Examples: Age, Gender, and Marital status
A composite attribute can be further subdivided to yield additional attributes. Examples:
ADDRESS -- Street, City, State, Zip PHONE NUMBER -- Area code, Exchange
number
Simple/Composite attributeSimple/Composite attribute
![Page 16: Data Modeling Yong Choi School of Business CSUB. Part # 2 2 Study Objectives Understand concepts of data modeling and its purpose Learn how relationships](https://reader036.vdocuments.us/reader036/viewer/2022062805/5697bfc91a28abf838ca91bf/html5/thumbnails/16.jpg)
Part # 2
16
is not physically stored within the database instead, it is derived by using an algorithm.
Example: AGE can be derived from the date of birth and the current date.
MS Access: int(Date() – Emp_Dob)/365)
Derived attributeDerived attribute
![Page 17: Data Modeling Yong Choi School of Business CSUB. Part # 2 2 Study Objectives Understand concepts of data modeling and its purpose Learn how relationships](https://reader036.vdocuments.us/reader036/viewer/2022062805/5697bfc91a28abf838ca91bf/html5/thumbnails/17.jpg)
Part # 2
17
“attributes that uniquely identify entity instances” Uniquely identify every instance of the entity One or more of the entity’s attributes
Composite identifiers are identifiers that consist of two or more attributes
Identifiers are represented by underlying the name of the attribute(s)Employee (employee_ID), student (student_ID)
(unique) Identifier(unique) Identifier
![Page 18: Data Modeling Yong Choi School of Business CSUB. Part # 2 2 Study Objectives Understand concepts of data modeling and its purpose Learn how relationships](https://reader036.vdocuments.us/reader036/viewer/2022062805/5697bfc91a28abf838ca91bf/html5/thumbnails/18.jpg)
Part # 2
Type of Relationships
One – to – One (1:1) Each instance in the relationship will have exactly one
related member on the other side One – to – Many (1:M)
A instance on one side of the relationship can have many related members on the other side, but a member on the other side will have a maximum of one related instance
Many – to – Many (M:N) Instances on both sides of the relationship can have many
related instances on the other side
![Page 19: Data Modeling Yong Choi School of Business CSUB. Part # 2 2 Study Objectives Understand concepts of data modeling and its purpose Learn how relationships](https://reader036.vdocuments.us/reader036/viewer/2022062805/5697bfc91a28abf838ca91bf/html5/thumbnails/19.jpg)
Part # 2
1:1 relationship in Set notation
DEPARTMT EMPLOYEE
![Page 20: Data Modeling Yong Choi School of Business CSUB. Part # 2 2 Study Objectives Understand concepts of data modeling and its purpose Learn how relationships](https://reader036.vdocuments.us/reader036/viewer/2022062805/5697bfc91a28abf838ca91bf/html5/thumbnails/20.jpg)
Part # 2
1:M relationship in Set notation
DEPARTMT EMPLOYEE
![Page 21: Data Modeling Yong Choi School of Business CSUB. Part # 2 2 Study Objectives Understand concepts of data modeling and its purpose Learn how relationships](https://reader036.vdocuments.us/reader036/viewer/2022062805/5697bfc91a28abf838ca91bf/html5/thumbnails/21.jpg)
Part # 2
M:N relationship in Set notation
WAREHOUSE PRODUCT
![Page 22: Data Modeling Yong Choi School of Business CSUB. Part # 2 2 Study Objectives Understand concepts of data modeling and its purpose Learn how relationships](https://reader036.vdocuments.us/reader036/viewer/2022062805/5697bfc91a28abf838ca91bf/html5/thumbnails/22.jpg)
Part # 2
M:N relationship
Each student takes many classes, and a class must be taken by many students.
** Many-to-many relationships cannot be used in the data model because they cannot be represented by the relational model (see the next slide for the reason) **
STUDENTCLASSTAKE
IS_TAKEN_BY
![Page 23: Data Modeling Yong Choi School of Business CSUB. Part # 2 2 Study Objectives Understand concepts of data modeling and its purpose Learn how relationships](https://reader036.vdocuments.us/reader036/viewer/2022062805/5697bfc91a28abf838ca91bf/html5/thumbnails/23.jpg)
Part # 2
Example of M:N
Many-to-many relationships is a second sign of complex data.
When x relates to many y's and y relates to many x's, it is a many-to-many relationship.
In our example schema, a color swatch can relate to many types of sweaters and a type of sweater can have many color swatches.
![Page 24: Data Modeling Yong Choi School of Business CSUB. Part # 2 2 Study Objectives Understand concepts of data modeling and its purpose Learn how relationships](https://reader036.vdocuments.us/reader036/viewer/2022062805/5697bfc91a28abf838ca91bf/html5/thumbnails/24.jpg)
Part # 2Example M:N Relationship
3 to 330 to 30
300 to 3003000 to 3000
30,000 to 30,000300, 000 to 300, 000
Table to represent Entity
![Page 25: Data Modeling Yong Choi School of Business CSUB. Part # 2 2 Study Objectives Understand concepts of data modeling and its purpose Learn how relationships](https://reader036.vdocuments.us/reader036/viewer/2022062805/5697bfc91a28abf838ca91bf/html5/thumbnails/25.jpg)
Part # 2Converting M:N Relationship to Two 1:M Relationships
Bridge Entity
![Page 26: Data Modeling Yong Choi School of Business CSUB. Part # 2 2 Study Objectives Understand concepts of data modeling and its purpose Learn how relationships](https://reader036.vdocuments.us/reader036/viewer/2022062805/5697bfc91a28abf838ca91bf/html5/thumbnails/26.jpg)
Part # 2
Bridge Entity
MUST have a composite (unique) identifier STU_NUM (from STUDENT entity) and CLASS_CODE
(from CLASS entity)