Conceptual Database Design: Entities, Relationships, and Cardinality Constraints, Lecture notes of Database Management Systems (DBMS)

Download Conceptual Database Design: Entities, Relationships, and Cardinality Constraints and more Lecture notes Database Management Systems (DBMS) in PDF only on Docsity!

COMP

Week 2: Conceptual Database Design

Semester 1, 2019

Dr Azadeh Ghari Neiat School of Computer Science

Announcements

› Assignment 1 will be released soon

• Once you have formed a group, please send the details of your group (including name and student ID of each member) to the Teaching Assistant, Heming Ni , at email heni 7690 @uni.sydney.edu.au and CC me at [email protected]

› Consultation session:

• Time : Wednesdays 11 - 12 (Week 2 - 6 )
• Location : Room 453 , Level 4 , The School of Computer Science (J 12 )

›First set of homework questions (Quiz) has been released

• due in 1 week (next Friday 20 : 00 pm)
• includes questions from last week + this week
• pay attention that you can only submit ONCE!

Outline

› Introduction to Conceptual Database Design

› Entity Relationship Model

• Notation and usage
• Entity and Relationship types, attributes
• Key, participation and cardinality constraints
• Weak entities, IsA hierarchies, aggregation

Conceptual Database Design › What is conceptual database design? › What is the purpose of conceptual database design?

• Agree on the structure of database before deciding on a particular implementation

Conceptual Database Design (Cont’d)

• Conceptual Database Design : A technique for understanding and capturing business information requirements graphically
  • depicts how we can describe the data associated with a real world in the context of a business problem, in terms of types of objects and their relationships.
• Convert conceptual database design to DDL (Logical Design) › Conceptual Database Design does not imply how data is implemented, created, modified, used, or deleted.
• Works as communication vehicle between technical people and non-technical people
• Facilitates planning, operation & maintenance of various data resources › Conceptual database design is model & database independent

: Specification of a database schema

Conceptual Data Models

› Entity-Relationship Model (ERM)

› Object-oriented Data Models

• Unified Modelling Language (UML)
• OMG, Booch, …

› Etc.

• Object Role Modelling (ORM)
• Semantic Object Model (SOM)
• Semantic Data Models (SDM)

Underlying the structure of a database is the data model: a collection of

conceptual tools for describing data, data relationships, data semantics and

consistency constraints.

Entity-Relationship Model (Cont’d)

› A data modelling approach that depicts the associations among different

categories of data within a business or information system.

• What are the entities and relationships in the enterprise?
• What information about these entities and relationships should we store in the database?
• What are the integrity constraints or business rules that hold?

› A database ‘schema’ in the ER Model is represented pictorially ( ER

diagrams ).

• We can convert an ER diagram into a logical (e.g., relational) schema.

› It is about what data needs to be stored

• It does not imply how data is created, modified, used, or deleted.

Entities & Entity Type › Entity is an individual object in the real word, e.g., a person, place, object, event, or concept about which you want to gather and store data.

• It must be distinguishable from other entities
• Example: John Doe, unit COMP9120, account 4711 › Entity Type (also: entity set ) is a collection of similar entities that share common properties or characteristics.
• Example: students, courses, accounts
• Rectangle represents entity type
• Entity sets do not need to be disjoint. › Attribute describes one aspect of an entity type
• Descriptive properties possessed by all members of an entity type
• Example: students have IDs , names and addresses
• It is depicted by an ellipses

A 1

entity type An

Graphical Representation in E-R Diagram  Entity Types represented by a rectangle  Attributes depicted by ellipses Keys are underlined Double ellipses for multi-valued attributes entity type isbn Book title authors

Symbols:^ Examples:

A 1

entity type An Remarks: Book.authors is a multi-valued attribute ; Employee.name is a composite attribute. Employee.Age is a derived attribute first last tfn Employee name DOB Age multi-valued attribute composite derived

Relationships › Relationship : relates two or more entities

• Example: John is enrolled in INFO › Relationship Type ( Relationship Set ): is a set of similar relationships
• Formally: a relation among n  2 entities, each from entity sets: {( e 1 , e 2 , … en ) | e 1  E 1 , e 2  E 2 , …, en  En }
• Example: Student (entity type) related to UnitOfStudy (entity type) by EnrolledIn (relationship type).
• Diamond represents relationship type › Distinction between relationship and relation :
• relationship (E-R Model) – describes relationship between entities
• relation (relational model) - set of tuples
• Both entity sets and relationship sets (E-R model) may be represented as relations (in the relational model) Student EnrolledIn^ UoS

Relationship Attributes and Roles › Relationship-Attribute: Relationships can also have descriptive attribute.

• E.g., John enrols in COMP9120 in the first semester 2019
• John and COMP9120 are related
• 2019sem1 describes the relationship - value of the Semester attribute of the EnrolledIn relationship set › Relationship-Role: Each participating entity can be named with an explicit role
• E.g. John is value of Student role, COMP9120 value of Subject role
• Useful for relationship that relate elements of the same entity type
• Example: Supervises( Employee:Manager, Employee ) › Relationship Type Schema :
• Relationship name
• Role names (or names of participating entity sets) – this is optional
• Relationship attributes (+domains)

Graphical Representation of Relationships in E-R Diagrams AcademicStaff teaches Subject Entity-Type 1 Relship-Type Entity-Type (^2) A 1 An

... Symbol : Example RoleName RoleName Year Lecturer  Diamonds represent relationship types  Lines link attributes to entity types and entity types to relationship types.  Roles are edges labeled with role names

› Q: What does this say?

› A: A person can only buy a specific product once (on one date)

Decision: Relationship vs. Entity? Product Purchased name (^) category price Person name date Modeling something as a relationship makes it unique; what if not appropriate?

Decision: Relationship vs. Entity? › Q: What about this way? › A: Now we can have multiple purchases per product, person pair! Product name (^) category price Person name date Purchase PID# quantity ProductOf BuyerOf We can always use a new entity instead of a relationship. For example, to permit multiple instances of each entity combination!