Data Modeling: Understanding ER Model, Components, and Relationships with UPS Use Case, Schemes and Mind Maps of Database Programming

Download Data Modeling: Understanding ER Model, Components, and Relationships with UPS Use Case and more Schemes and Mind Maps Database Programming in PDF only on Docsity!

DATA MODELING

The ER Model

• (^) ER model stands for an Entity-Relationship model. It is a high-level data model. This model is used to define the data elements and relationship for a specified system.
• (^) It develops a conceptual design for the database. It also develops a very simple and easy to design view of data.
• (^) In ER modeling, the database structure is portrayed as a diagram called an entity-relationship diagram.
• (^) For example, Suppose we design a school database. In this database, the student will be an entity with attributes like address, name, id, age, etc. The address can be another entity with attributes like city, street name, pin code, etc and there will be a relationship between them.

Component of ER Diagram

Entity:

• An entity may be any object, class,

person or place. In the ER diagram, an

entity can be represented as rectangles.

• (^) Consider an organization as an

example- manager, product, employee,

department etc. can be taken as an

entity.

Weak Entity:

• An entity that depends on another entity called a weak entity. The weak entity doesn't contain any key attribute of its own. The weak entity is represented by a double rectangle.

Component of ER Diagram

Attribute:

• (^) The attribute is used to describe the property of an entity. Eclipse is used to represent an attribute.
• (^) For example, id, age, contact number, name, etc. can be attributes of a student.

Component of ER Diagram

c. Multivalued Attribute: An attribute can have more than one value. These attributes are known as a multivalued attribute. The double oval is used to represent multivalued attribute. For example, a student can have more than one phone number.

d. Derived Attribute:

An attribute that can be derived from

other attribute is known as a derived

attribute. It can be represented by a

dashed ellipse.

For example, A person's age changes

over time and can be derived from

another attribute like Date of birth.

Component of ER Diagram

Relationship:

• (^) A relationship is used to describe the relation between entities. Diamond or rhombus is used to

represent the relationship.

• (^) Types of relationship are as follows:

1. One-to-One Relationship

2. One-to-many relationship

3. Many-to-one relationship

4. Many-to-many relationship

USE CASE

SCENARIO:

• (^) UPS prides itself on having up-to-date information on the processing and current location of

each shipped item. To do this, UPS relies on a company-wide information system. Shipped

items are the heart of the UPS product tracking information system. Shipped items can be

characterized by item number (unique), weight, dimensions, insurance amount,

destination, and final delivery date. Shipped items are received into the UPS system at a

single retail center. Retail centers are characterized by their type, unique ID, and address.

Shipped items make their way to their destination via one or more standard UPS

transportation events (i.e., flights, truck deliveries). These transportation events are

characterized by a unique schedule Number, a type (e.g. flight, truck), and a delivery Route.

• (^) Please create an Entity Relationship diagram that captures this information about the UPS

system. Be certain to indicate identifiers and cardinality constraints.

USE CASE

DBMS Specialization

• Specialization is a top-

down approach, and it is

opposite to Generalization.

In specialization, one

higher level entity can be

broken down into two

lower level entities.

• (^) Specialization is used to

identify the subset of an

entity set that shares some

distinguishing

characteristics.

DBMS Aggregation

• (^) In aggregation , the relation between two entities is treated as a single entity.
• (^) In aggregation, relationship with its corresponding entities is aggregated into a higher level entity.

Reduction of ER diagram to Table

• (^) Composite attribute represented by components. In the given ER diagram, student address is a composite attribute. It contains CITY, PIN, DOOR#, STREET, and STATE. In the STUDENT table, these attributes can merge as an individual column.
• (^) Derived attributes are not considered in the table. In the STUDENT table, Age is the derived attribute. It can be calculated at any point of time by calculating the difference between current date and Date of Birth.

Reduction of ER diagram to Table

Relationship of higher degree

• (^) The degree of relationship can be defined as the number of occurrences in one entity that is associated with the number of occurrences in another entity.
• (^) There is the three degree of relationship:
• (^) One-to-one (1:1)
• (^) One-to-many (1:M)
• (^) Many-to-many (M:N)

Relationship of higher degree One-to-one

• (^) In a one-to-one relationship, one occurrence of an entity relates to only one occurrence in another entity.
• A one-to-one relationship rarely exists in practice.
• (^) For example: if an employee is allocated a company car then that car can only be driven by that employee.
• Therefore, employee and company car have a one-to-one relationship.