Data Modeling Using the Entity-Relationship Model

1. Data ModelingUsingthe Entity-RelationshipModel

2. Using High-Level Conceptual Data Models for Database Design

3. Functional Requirements Data Requirements CONCEPTUAL DESIGN FUNCTIONAL ANALYSIS Conceptual Schema (in a high-level data model) High-level Transaction Specification LOGICAL DESIGN (DATA MODEL MAPPING) Logical (Conceptual) Schema (In the data model of a specific DBMS) APPLICATION PROGRAM DESIGN PHYSICAL DESIGN TRANSACTION IMPLEMENTATION Internal Schema Application Program Requirements Collection and Analysis Phases of Database Design The next step is to create a conceptual schema for the database, using a high level conceptual schema for the database. The conceptual schema is a description of the data requirements of the users and includes detailed description of the entity types, relationships and constraints, present by using high level data model. Because concepts do not include do not include implementation details, they are usually easier to understand and can be used to communicate with user. The last step is the physical design phase. During which the internal storage structures, indexes, access paths, and file organizations for the database files are specified. First step shown is requirements collection and analysis. This step, the database designers interview user to understand and document their requirements. The next step is actual implementation of the database, using a commercial DBMS. Most commercial DBMS use an implementation Data Model such as relation model or the object-relational database model.This step is transformed from the high-level data model into the implementation data model.

4. Entity Relational Data Model ER-Model describes data as • Entities • Attributes • Relationships

5. Entities and Attributes • Entity is thing in the real world with an independent existence. • Entity may be an object with a physical existence for example person, car house or employee • Entity may be an object with a conceptual existence for example a company, a job, a university course.

6. Attributes • Attribute is the particular properties that describe entity. • The attribute values that describe each entity become a major part of the data stored in the database. • For example • Employee entity may describe by • Name, Age, address, salary and job.

7. Attribute & Attribute Value Attribute Student Name Faculty Major ID 475020126-3 Kamonphon Science IT 465020100-0 PiyathiDa Science Comp SC. Attribute Value

8. Attribute Type • Atomic Attribute • Composite Attributes • Single-Valued • Multi Valued • Stored Attribute • Derived Attribute

9. Atomic Simple Atomic Attribute • Atomic Attribute is an attribute that is not divisible. • Called Atomic or Simple Attributes. • Example Student First Name Last Name Age

10. Composite Attribute Street Address ZipCode Tambol Province District Composite Attribute? Atomic Attribute Composite Attributes • Composite Attributes can be divided into smaller subparts, which represent more basic attributes with independent meanings. Address

11. Single-Valued VS Multi Valued Attributes • Single-Valued : attribute has a single value for a particular entity • Multi Value Attribute: has multiple values

12. Attribute Age Degree A : 24 Years B : 27 Years A : B.Sc. B : B.Sc, M.Sc., Dr. Eng. AttributeValue Example

13. Stored VS Derived Attributed • Stored Attribute • Derived Attribute: an attribute value can be determined from another attribute / related entity.

14. Attribute Stored Attribute Age Birth Day A : 24 Years B : 40 Years A : 13 July 1980 B : 23 Dec 1964 Derived Attribute AttributeValue Example

15. Key Attribute • An attribute that whose values are distinct for each individual entity in the entity set. • Unique • Not Null

16. Null Value Some case a particular entity may not have an applicable value for an attribute. Example Degree attribute applied only to a person with collage degree. For this situations, a special value called null is create.

17. Entity Type • Regular Entity Type • Entity that do have a key attribute • Weak Entity Type • Entity that do not have key of their own. • Depend on other entities • Other entities are called owner entity • Relationship between Owner and Weak entities is called “identifying relationship”

18. Example • Entity • Employee • Department • Project • Dependent

19. Entity Type, Entity Sets, Keys, Value Set • Entity type defines a collection (or set) of entity that have same attributes. • Each entity type in the database is described by its name and attributes. • Entity set is the collection of all entities of a particular entity type in a database at any point in time. • The entity set usually referred to using the same name as the entity type.

20. Key attribute of Entity type • Key or Uniqueness constraint an attribute whose values are distinct for each individual entity in entity set

21. Relationship • There are several relationship among the various entity types.

22. Relationship Type • Relationship type R among n entity types E1,E2,…,En defines a set of association or relationship set among entities from these entity types. • Mathematically, • the relationship set R is a set of relationship instances ri, where each ri associates n individual entities (e1,e2,…, en) and each entity ej in ri is a member of entity type Ej, 1 ≤ j ≤ n.

23. Relationship Degree • Degree of a relationship type is the number of participating entity types. • For example Degree of a Relationship among Teacher and Student entities is two. A relationship degree two and three are called binary and ternary, respectively.

24. Constraint on Relationship Types • Two main Type of relationship constraints 1.Cardinality ration 2. Participation

25. Cardinality Ratios • Cardinality ratio specifies the maximum number of relationship instances that an entity can participate • 1 to 1 Relationship (1:1) • 1 to many relationship (1:N) • Many to Many relationship (N:M)

26. 1:1 Relationship R1 R2 R3 R4 E1 E2 E3 E4 E5 E6 E7 D1 D2 D3 D4 Entity E2 Entity E1 Relationship

27. 1 to Many Relationship R1 R2 R3 R4 E1 E2 E3 E4 E5 E6 E7 D1 D2 D3 D4 Entity D Entity E Relationship

28. Many to Many Relationship R1 R2 R3 R4 E1 E2 E3 E4 E5 E6 E7 J1 J2 J3 J4 Entity J Entity E Relationship

29. Participation Constraints and Existence Dependencies • Participation Constraints specified whether the existence of an entity depends on its being related to another entity via the relationship type. • It specifies the minimum number of relationship instance that each entity can participate in, and is sometimes called the minimum cardinality constraint.

30. Participation type • Partial • Total participation (existence dependency)

31. Total participation R1 R2 R3 R4 E1 E2 E3 E4 E5 E6 E7 D1 D2 D3 D4 Entity D Entity E Relationship

32. E1 E2 E3 E4 E5 E6 E7 D1 D2 D3 D4 Partial participation R1 R2 R3 R4 Entity E Entity D Relationship

33. Entity Weak Entity Relationship Symbol

34. Weak Entity Attribute Key Attribute Composite Attribute Derived Attribute

35. E1 E2 R E1 E2 R 1 N partial Total Cardinality Ratio