1 / 149

Database Modeling and Design

This database modeling and design course covers the concepts of entity sets, relationship sets, and design issues. It also includes mapping constraints, keys, E-R diagram, extended E-R features, introduction to UML, and object modeling in UML. Based on the text book and presentation on UML by Niraj Trivedi.

ramosm
Download Presentation

Database Modeling and Design

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database Modeling and Design N. L. Sarda nls@iitb.ac.in (based on the text book and presentation on UML by Niraj Trivedi)

  2. Modeling and Database Design • Entity Sets • Relationship Sets • Design Issues • Mapping Constraints • Keys • E-R Diagram • Extended E-R Features • Introduction to UML • Object Modeling in UML • From ER to Relations

  3. Why We Model • We build models of complex systems because we cannot comprehend any such system in its entirety • Need to develop a common understanding of the problem and the solution • Cannot afford a trial-and-error approach • to communicate the desired structure and behavior of our systems

  4. How We Model • The choice of which model we use has a profound influence on how a problem is attacked and how a solution is shaped • No single model is sufficient; every complex system is best approached through a set of independent models • The best models are connected to reality

  5. DATA MODEL • represents operational data about real world events, entities, activities, etc. • model may be at various levels depending of requirements : • logical or physical • external, conceptual, internal

  6. Data Model…… • a good model • is easy to understand • has a few concepts • permits top-down specifications • model offers concepts, constructs and operations • must capture meaning of data (data semantics) which help us in interpreting the data

  7. Data Model…… • semantics captured through data types, inter-relationships and data integrity constraints • permitted values • uniqueness • existence dependence • restrictions on some operations such as insertions, deletions

  8. ER MODEL…. • a few concepts • simple and easy-to-use • permits top-down approach for controlling details • useful as a tool for communication between designer and user during requirements analysis and conceptual design

  9. ENTITY • an object that exists • distinguishable from other objects • could be concrete or abstract • Examples : this course on DBIS, Ganesh as a student, etc

  10. ENTITY SET • a set of similar entities • need not be disjoint with other entity sets • e.g., supplier and customer may have common entities • example : set of all books in a library • entity set also called entity type or entity class • an entity is an occurrence or an instance of some entity type

  11. ENTITY SET…… • we often use the words ‘entity’ to mean ‘entity-set’ • entity sets are named using singular common nouns : Book Student Course

  12. ATTRIBUTE • an entity has a set of attributes • attribute defines property of an entity • entity Book has Price attribute • it is given a name • attribute has value for each entity • value may change over time • same set of attributes are defined for entities in an entity set

  13. ATTRIBUTE…. • Example : entity set BOOK has the following attributes TITLE ISBN ACC-NO AUTHOR PUBLISHER YEAR PRICE • a particular book has value for each of the above attributes

  14. ATTRIBUTE…. • an attribute may be multi-valued, i.e., it has more than one value for a given entity; e.g., a book may have many authors • an attribute which uniquely identifies entities of a set is called candidate key attribute of that entity set • composite attribute : date, address, etc

  15. Composite Attributes

  16. PRIMARY KEYS • to distinguish occurrences of entities • distinction made using values of some attribute(s) • set of one/more attributes which, taken collectively, uniquely identify an entity in an entity set is called its candidate key • Roll-number for a student • Acc-no for a book

  17. EXAMPLE : A COLLEGE (some entities and their attributes) • STUDENT : rollno, name, hostel-no., date-of-birth • COURSE : courseno, name, credits • TEACHER : empno, name, rank, room-no, tel- phone • DEPT : name, tel-phone Ex : identify primary keys of above entities.

  18. EXAMPLE : A COLLEGE… • perception of reality and focus of design could have indicated more entities • HOSTEL SEMESTER • Or, teacher could only be an attribute EXERCISE : identify entities in a hospital and give a few instances of each

  19. RELATIONSHIP • represents association among entities • e.g., a particular book is a text for particular course • the book ‘Database Systems’ by S. Sudarshan is text for course identified by code ‘CS644’ • the student GANESH has enrolled for course CS644

  20. RELATIONSHIP SET • set of relationships of same type • words ‘relationship’ and ‘relationship set’ often used interchangeably • between certain entity sets • binary relationship : between two entity sets • ternary relationship : among three entity sets

  21. RELATIONSHIP SET…. • binary relationship set STUDY between STUDENT and COURSE • relationship STUDY could be ternary among STUDENT, COURSE and TEACHER • What is the difference ? • a relationship may have attributes • attribute GRADE and SEMESTER for STUDY

  22. RELATIONSHIP SET…. • relationships named using verbs or nouns Study Enroll Order

  23. DEPICTING A RELATIONSHIP • entity sets as a collection • entity instances by small circles • relationship instances by small rectangle with connections to involved entities

  24. (Ram, Cobol) (Sita, DBMS)

  25. PRIMARY KEY FOR REPATIONSHIPS • made of primary keys of all participating entities e.g., primary key of STUDY is (rollno, courseno)

  26. RELATIONSHIP CARDINALITY • is a constraint on a relationship • it characterizes relationships further • given by indicating : how many entities of an entity set participate in a relationship • especially useful for binary relationships • Express the number of entities to which another entity can be associated via a relationship set.

  27. RELATIONSHIP CARDINALITY… • a relationship (set) R between entity (sets) A and B may be one of the following • one-to-one : one entity in A is associated with at most one entity in B • one-to-many : one entity in A may be associated with zero/more number of entities in B. However, one entity in B can be associated with at most one entity from A. • many-to-one : reverse of above definition (like a mathematical function) • many-to-many : one entity in A may be associated with any number of entities in B, and vice-versa.

  28. RELATIONSHIP CARDINALITY… • EXAMPLES : • relationship TEACHES from TEACHER to COURSE is one-to-many (TAUGHT-BY from COURSE to TEACHER is many-to-one) • relationship STUDY between STUDENT and COURSE is many-to-many

  29. E-R Diagrams • Rectangles represent entity sets. • Diamonds represent relationship sets. • Lines link attributes to entity sets and entity sets to relationship sets. • Ellipses/circles represent attributes • Double ellipses represent multivalued attributes. • Dashed ellipses denote derived attributes. • Underline indicates primary key attributes (will study later)

  30. E-R Diagram With Composite, Multivalued, and Derived Attributes

  31. Relationship Sets with Attributes

  32. Roles • Entity sets of a relationship need not be distinct • The labels “manager” and “worker” are called roles; they specify how employee entities interact via the works-for relationship set.

  33. Roles … • Roles are indicated in E-R diagrams by labeling the lines that connect diamonds to rectangles. • Role labels are optional, and are used to clarify semantics of the relationship

  34. Cardinality Constraints • We express cardinality constraints by drawing either a directed line (), signifying “one,” or an undirected line (—), signifying “many,” between the relationship set and the entity set. • E.g.: One-to-one relationship:

  35. One-To-Many Relationship • In one-to-many relationship a loan is associated with at most one customer via borrower, a customer is associated with several (including 0) loans via borrower

  36. Many-To-Many Relationship • A customer is associated with several (possibly 0) loans via borrower • A loan is associated with several (possibly 0) customers via borrower

  37. Participation of an Entity Set in a Relationship Set • Totalparticipation (indicated by double line): every entity in the entity set participates in at least one relationship in the relationship set • E.g. participation of loan in borrower is total • every loan must have a customer associated to it via borrower

  38. Participation … • Partial participation: some entities may not participate in any relationship in the relationship set • E.g. participation of customer in borrower is partial

  39. Alternative Notation for Cardinality Limits • Cardinality limits can also express participation constraints

  40. Describe the real-world mapped above in words. • Can you represents this : a supplier may supply same part many times • . Relationship ‘supplies’ could also be ternary • (by involving warehouse)

  41. TERNARY RELATIONSHIPS • be sure that your model reflects real-world correctly • ternary (or, of higher order) relationships are harder to understand • ternary relationship is not same as two binary relationships

  42. E-R Diagram with a Ternary Relationship

  43. Cardinality Constraints on Ternary Relationship • We allow at most one arrow out of a ternary (or greater degree) relationship to indicate a cardinality constraint • E.g. an arrow from works-on to job indicates each employee works on at most one job at any branch.

  44. Ternary … • If there is more than one arrow, there are two ways of defining the meaning. • E.g a ternary relationship R between A, B and C with arrows to B and C could mean • 1. each A entity is associated with a unique entity from B and C or • 2. each pair of entities from (A, B) is associated with a unique C entity, and each pair (A, C) is associated with a unique B • To avoid confusion we outlaw more than one arrow

  45. Converting Non-Binary Relationships to Binary Form • In general, any non-binary relationship can be represented using binary relationships by creating an artificial entity set. • Replace R between entity sets A, B and Cby an entity set E, and three relationship sets as shown • Create a special identifying attribute for E • Add any attributes of R to E

  46. Converting Non-Binary Relationships (Cont.) • Also need to translate constraints • Translating all constraints may not be possible • There may be instances in the translated schema thatcannot correspond to any instance of R • Exercise: add constraints to the relationships RA, RB and RC to ensure that a newly created entity corresponds to exactly one entity in each of entity sets A, B and C • We can avoid creating an identifying attribute by making E a weak entity set (described shortly) identified by the three relationship sets

More Related