IS 4420 Database Fundamentals Chapter 3 Modeling Data

IS 4420 Database Fundamentals Chapter 3: Modeling Data in the Organization Leon Chen

Systems Development Life Cycle Project Identification and Selection Project Initiation and Planning Database Development Process Enterprise modeling Conceptual data modeling Analysis Logical Design Physical Design Implementation Maintenance Logical database design Physical database design and definition Database implementation Database maintenance 2

Overview n n What is data modeling Importance of data modeling Business rules Entity-relationship model: entities, relationships, and attributes 3

Conceptual Data Modeling Business Rules -Data names -Data definitions Conceptual Data Model (Entity-Relationship Diagram) 4

Data Modeling is the Most Important n n n Input for follow-up processes Data rather than (piuttosto che) processes are the most complex Data tend to be more stable than business processes (I dati tendono ad essere più stabili dei processi) 5

Business Rules n Statements that define or constrain some aspect of the business. For example: Friday is business casual dress day Ø Students can only register a course if prerequisite is successfully completed Ø n Automated through DBMS software 6

Entity-Relationship Model n n STUDENT A logical representation of the data for an organization or for a business area 3 constructs: Ø Name Ø Registers Ø Ø Entity - person, place, object, event, concept (often corresponds to a row in a table) Attribute - property or characteristic of an entity (often corresponds to a field in a table) Relationship – link between entities (corresponds to primary key-foreign key equivalencies in related tables) Type vs. instance • • n Entity type: Instructor Entity instance: Leon Chen Entity-Relationship Diagram Ø Ø A graphical representation of entity-relationship model Also called E-R diagram or just ERD 7

Sample E-R Diagram (Figure 3 -1) Attribute Supplier_ID Supplier_Name Supplier_Address 8

Entity n n n Person: EMPLOYEE, STUDENT Place: WAREHOUSE, COUNTRY Object: BUILDING, MACHINE Event: SALE, REGISTRATION Concept: ACCOUNT, COURSE EMPLOYEE DEPENDENT Strong entity Weak entity CERTIFICATE Associative entity 9

What Should an Entity Be? n SHOULD BE: 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 Ø n SHOULD NOT BE: A user of the database system Ø An output of the database system (e. g. a report) Ø 10

Attribute n n Attribute - property or characteristic of an entity type Classifications of attributes: Required vs. Optional Attributes Ø Simple vs. Composite Attribute Ø Single-Valued vs. Multivalued Attribute Ø Stored vs. Derived Attributes Ø Identifier Attributes - keys Ø 11

Required vs. Optional Attributes n Example: entity – ONLINE_ACCOUNT Ø Required attributes (Not NULL) • • • Ø Account_ID Password Owner_Name Optional attributes • • Phone_Number Password_Hint 12

Figure 3 -7 – A composite attribute An attribute broken into component parts 13

Figure 3 -8 – Entity with a multivalued attribute (Skill) and derived attribute (Years_Employed) Derived from date employed and current date Multivalued: an employee can have more than one skill (speaks Chinese, plays tennis) 14

Figure 3 -19 – An attribute that is both multivalued and composite This is an example of time-stamping. More examples? 15

Identifiers (Keys) n n n Identifier (Key) - An attribute (or combination of attributes) that uniquely identifies individual instances of an entity type Candidate Key – an attribute that could be a key. Examples (for STUDENT, PERSON)? Simple Key versus Composite Key 16

Figure 3 -9 a Simple key attribute The key is underlined 17

Figure 3 -9 b Composite key attribute Flight_Number Date 18

Guidelines for Identifiers n n n Will not change in value Will not be null Substitute new, simple keys for long, composite keys Ø ? Game_Number 19

Relationship n n Relationship Type vs. Relationship Instance Degree (grado) of a relationship Cardinality of a relationship (Associative Entity – combination of relationship and entity) 20

Figure 3 -11 a attribute of the relationship The relationship type is modeled as the diamond and lines between entity types 21

Relationship Instance is between specific entity instances 22

Degree of a relationship is the number of entity types that participate in it One entity related to another of the same entity type Entities of two different types related to each other Entities of three different types related to each other 23

Cardinality of a Relationship n One-to-One Ø n One-to-Many Ø n Each entity in the relationship will have exactly one related entity An entity on one side of the relationship can have many related entities, but an entity on the other side will have a maximum of one related entity Many-to-Many Ø Entities on both sides of the relationship can have many related entities on the other side 24

25

26

Note: a relationship can have attributes of its own 27

Cardinality Constraints n n Cardinality Constraints - the number of instances of one entity that can or must be associated with each instance of another entity Minimum Cardinality. Examples? Ø Ø n If zero, then optional If one or more, then mandatory Maximum Cardinality. Examples? Ø The maximum number 28

Cardinalities Mandatory one Mandatory many 11 Optional one Optional many 18 29

Figure 3 -16 a Basic relationship Mandatory minimum cardinalities – Figure 3 -17 a 30

Figure 3 -17 c Optional cardinalities with unary degree, one-to-one relationship 31

Maximum Cardinality Constraint max cardinality constraint 32

Entities can be related to one another in more than one way 33

Attributes or Relationship? Attributes can be transformed to relationships 34

Attributes or Relationship? Attributes should be transformed to relationships 35

OPZIONALE(Associative Entities) entity n It’s an n AND it’s a n – it has attributes When should a relationship – it links entities together relationship with attributes be an associative entity? Ø Ø Ø All relationships for the associative entity should be many The associative entity could have meaning independent of the other entities The associative entity should have at least one or more attributes other than the identifier The associative entity may participate in other relationships other than the entities of the associated relationship Ternary relationships should be converted to associative entities 36

Figure 3 -11 b – An associative entity (CERTIFICATE) 37

Relationship symbols Attribute symbols Entity symbols A special entity that is also a relationship Relationship degrees specify number of entity types involved(COINVOLTO) Relationship cardinalities specify how many of each entity type is allowed 38

Sample E-R Diagram (Figure 3 -1) Attribute Supplier_ID Supplier_Name Supplier_Address 39