Chapter 3.pptxoop presentation goods one

best one

1. Chapter 3: Database Modeling O utline The high-level conceptual model Entity types and Sets, Attributes and Keys Relationships , Associations, Roles and Structural Constraints E/R Diagram naming conventions, and Design issues Mapping ER-models to relational tables 1

2. H igh level conceptual modeling The first step shown is requirements collection and analysis. During this step, the database designers interview prospective database users to understand and document their data requirements. The result of this step is a concisely written set of users’ requirements . These requirements should be specified in as detailed and complete a form as possible. In parallel with specifying the data requirements, it is useful to specify the known functional requirements of the application. These consist of the user-defined operations (or transactions) that will be applied to the database, including both retrievals and updates. In software design, it is common to use data flow diagrams , sequence diagrams, scenarios, and other 2

3. techniques to specify functional requirements . Once the requirements have been collected and analyzed, the next step is to create a conceptual schema for the database, using a high-level conceptual data model . This step is called conceptual design . The conceptual schema is a concise description of the data requirements of the users and includes detailed descriptions of the entity types , relationships, and constraints; these are expressed using the concepts pro-vided by the high-level data model . During or after the conceptual schema design, the basic data model operations can be used to specify the high-level user queries and operations identified during functional analysis. This also serves to confirm that the conceptual schema meets all the identified functional requirements . The next step in database design is the actual implementation of the database, using a commercial DBMS. 3

4. Most current commercial DBMSs use an implementation data model such as the relational or the object-relational database model so the conceptual schema is transformed from the high-level data model into the implementation data model . This step is called logical design or data model mapping; its result is a database schema in the implementation data model of the DBMS . Data model mapping is often automated or semi automated within the database design tools. The last step is the physical design phase , during which the internal storage structures , file organizations, indexes, access paths, and physical design parameters for the database files are specified. In parallel with these activities, application programs are designed and implemented as database transactions corresponding to the high-level transaction specifications. 4

5. 5

6. Entity Set or Entity Type-Collection of entities all having the same properties. Attributes Each entity is described by a set of attributes/properties. Types of Attributes Simple Attributes having atomic or indivisible values. example: Dep't – a string Composite Attributes : having several components in the value. example: Qualification with components (Degree Name, Year, University Name) Derived Attributes: Attribute value is dependent on some other attribute. Single-valued : having only one value rather than a set of values. ƒ for instance, PlaceOfBirth – single string value. Multi-valued : having a set of values rather than a single value. ƒ for instance, CoursesEnrolled attribute for student 6 WSU, Prepared by Feven T. chapter 3

7. Domains of Attributes Each attribute takes values from a set called its domain For instance, studentAge – {17,18, …, 55} Home Address character strings of length 35 Domain of composite attributes cross product of domains of component attributes Domain of multi-valued attributes – set of subsets of values from the basic domain Key – an attribute or a collection of attributes whose value(s) uniquely identify an entity in the entity set. 7 WSU, Prepared by Feven T. chapter 3

8. Key – an attribute or a collection of attributes whose value(s) uniquely identify an entity in the entity set. A key for an entity set may have more than one attribute . Super Key is any combination of fields within a table that uniquely identifies each record within that table. Candidate Key is a subset of a super key. A candidate key is a single field or the least combination of fields that uniquely identifies each record in the table. The least combination of fields distinguishes a candidate key from a super key. Primary Key is a candidate key that is most appropriate to be the main reference key for the table. As its name suggests, it is the primary key of reference for the table and is used throughout the database to help establish relationships with other tables . Foreign Key is generally a primary key from one table that appears as a field in another where the first table has a relationship to the second. In other words, if we had a table A with a primary key X that linked to a table B where X was a field in B, then X would be a foreign key in B. 8 WSU, Prepared by Feven T. chapter 3

9. WSU, Prepared by Feven T. chapter 3 9 Figure 3.2 The CAR entity type with two key attributes, Registration and Vehicle_id . (a) ER diagram notation.

10. Relationship An entity set may have more than one key. Keys can be determined only from the meaning of the attributes in the entity type. Relationships When two or more entities are associated with each other, we have an instance of a Relationship. Degree of a relationship Degree : the number of participating entities. Degree 2: binary Degree 3: ternary Degree n: n- ary Binary relationships are very common and widely used. 10 WSU, Prepared by Feven T. chapter 3

11. Diagrammatic Notation for Relationships ƒ Relationship – diamond shaped box ƒ Rectangle of each participating entity is connected by a line to this diamond. Name of the relationship is written in the box. Binary Relationships and Cardinality Ratio E1 E2R • The number of entities from E2 that an entity from E1 can possibly be associated thru R (and vice-versa) determines the cardinality ratio of R. 11 WSU, Prepared by Feven T. chapter 3

12. Cardinality Ratios • One-to-one: An E1 entity may be associated with at most one E2 entity and similarly an E2 entity may be associated with at most one E1 entity. • One-to-many: An E1 entity may be associated with many E2 entities whereas an E2 entity may be associated with at most one E1 entity. • Many-to-one: … ( similar to above) • Many-to-many: Many E1 entities may be associated with a single E2 entity and a single E1 entity may be associated with many E2 entities. example 12 WSU, Prepared by Feven T. chapter 3

13. WSU, Prepared by Feven T. chapter 3 13

14. WSU, Prepared by Feven T. chapter 3 14

15. WSU, Prepared by Feven T. chapter 3 15

16. Participation Constraints • An entity set may participate in a relation either totally or partially. • Total participation: Every entity in the set is involved in some association (or tuple) of the relationship. • Partial participation: Not all entities in the set are involved in association (or tuples) of the relationship. Weak Entity Sets Weak Entity Set: An entity set whose members owe their existence to some entity in a strong entity set. ƒ entities are not of independent existence . Each weak entity is associated with some entity of the owner entity set through a special relationship. ƒ Weak entity set may not have a key attribute . Example 16 WSU, Prepared by Feven T. chapter 3

17. WSU, Prepared by Feven T. chapter 3 17

18. WSU, Prepared by Feven T. chapter 3 18

19. Recursive Relationships and Role Names Recursive relationship: An entity set relating to itself gives rise to a recursive relationship E.g ., the relationship prereq Of is an example of a recursive relationship on the entity Course Role Names – used to specify the exact role in which the entity participates in the relationships Essential in case of recursive relationships, Can be optionally specified in non-recursive cases 19 WSU, Prepared by Feven T. chapter 3

20. Structural Constraints • Cardinality Ratio and Participation Constraints are together called Structural Constraints. • They are called constraints as the data must satisfy them to be consistent with the requirements. • Min-Max notation: pair of numbers ( m,n ) placed on the line connecting an entity to the relationship. • m: the minimum number of times a particular entity must appear in the relationship tuples at any point of time • 0 – partial participation • ≥1 – total participation • n: similarly, the maximum number of times a particular entity can appear in the relationship tuples at any point of time 20 WSU, Prepared by Feven T. chapter 3

21. E/R Diagram naming conventions, and Design issues E/R diagram is a diagram that shows the relationship between entities. Naming conventions :We choose to use singular names for entity types, rather than plural ones, because the entity type name applies to each individual entity belonging to that entity type. In our ER diagrams, we will use the convention that entity type and relationship type names are uppercase letters, attribute names have their initial letter capitalized, and role names are lowercase letters. As a general practice, given a narrative description of the database requirements, the nouns appearing in the narrative tend to give rise to entity type names, and the verbs tend to indicate names of relationship types. Attribute names generally arise from additional nouns that describe the nouns corresponding to entity types. Another naming consideration involves choosing binary relationship names to make the ER diagram of the schema readable from left to right and from top to bottom 21 WSU, Prepared by Feven T. chapter 3

22. WSU, Prepared by Feven T. chapter 3 22

23. ER-D WSU, Prepared by Feven T. chapter 3 23

24. E-R Diagram for a University Enterprise 24 WSU, Prepared by Feven T. chapter 3

25. Design Issues Use of entity sets vs. attributes Use of phone as an entity allows extra information about phone numbers (plus multiple phone numbers) 25 WSU, Prepared by Feven T. chapter 3

26. Design Issues Use of entity sets vs. relationship sets Possible guideline is to designate a relationship set to describe an action that occurs between entities 26 WSU, Prepared by Feven T. chapter 3

27. Mapping ER-models to relational tables Representing Strong Entity Sets Entity set E with attributes a an translates to table E with attributes a1,….., an Entity of type E $ row in table E Primary key of entity set ! primary key of table Example : Student Student major Student name Student num Student num Student name major 27 WSU, Prepared by Feven T. chapter 3

28. THANKYOU! 28 Prepared by Feven T. Chap 4