Role(The contact set can be defined in an object set)
- The entity set involved in the contact does not have to be named as roles by the different tags "manager" and "worker"; they specify how the employee entity is related to the contact set through works-.
- In the E-R diagram, the role marks the line connecting the diamond and the rectangle.
- Role tags are optional and used to clarify the semantics of a link.
Base Constraint
A directed straight line () is used to represent "1" between the contact set and the entity set, and a non-directed straight line (-) indicates "more ".
One-to-one connection
For example, one-to-one connection from customer to loan:
- By contacting borrower, a customer can be associated with a loan at most.
- By contacting borrower, a loan can be associated with up to one customer.
One-to-multiple connections (including 0)
Through one-to-multiple contact borrower from customer to loan, a loan can only be associated with one customer at most, but one customer can be associated with multiple (including 0) loans.
Multi-to-Multi-contact (including 0)
- One customer can be associated with multiple (including 0) Loans
- A loan can be associated with multiple customers (including 0)
How the entity set participates in the contact set
- Full participation (expressed in double lines): each entity in the entity set must participate in at least one connection in the contact set.
E. g. Loan participation in borrower is complete
Through borrower, each loan must be associated with at least one customer.
- Partial participants: Some entities may not be involved in any contacts in the contact set.
E. g. Customer participation in borrower is partial
Base limit representation (the base limit can also express the participation constraint)
(Min, max): indicates that an object of entity set a participates in the contact for at least min and Max times.
Min = 0, indicating that you can not participate. min> = 1 indicates that you must participate at least once.
(Customer) max = 1; (loan) max = 1 a one-to-one B
(Customer) max = *; (loan) max = 1 A to multiple B
Key
- The superkeys of an object set are the sets of one or more attributes whose values can uniquely identify an object.
- The candidate key of an entity set is a very small superkey.
- Customer-ID is the candidate key of customer
- Account-number is the candidate key for the account.
- Multiple candidate keys may exist. select one of them as the primary key.
E-R diagram with Ternary Link
Base constraint of ternary connections
Up to one arrow can appear for connections over three yuan
E. g. the arrow from works-on to job indicates that each employee takes up to one job at each branch.
If there are multiple arrows, there are two definitions.
- The ternary relationship between A, B, and C. R has an arrow pointing to B and C, which means
- Each entity a is associated with a unique entity B and entity C, or
- Each entity pair (A, B) is associated with a unique entity C, and each entity pair (a, c) associated with a unique B object the above two definitions are used in different systems
- To avoid confusion, we disable more than one arrow.
Weak entity set
- The entity set without a primary key is called a weak entity set.
- The existence of a weak object set depends on the existence of its identity object set.
- A weak entity set is associated with its ID entity set through a complete, one-to-many association set from the ID entity set to the weak entity set.
- The ID contact is represented by a double link.
- The identification attribute (or partial key) of a weak entity set refers to the set of attributes that distinguish all objects in a weak entity set.
- The primary key of the weak entity set is composed of the primary key of the strong entity set on which it depends plus its identification attribute.
For example, the family entity set depends on the staff entity set.
- The weak entity set is represented by a double rectangle.
- Use the dotted line below to identify weak entity sets.
- Payment-number-payment object set identification attribute
- Payment's primary key-(loan-number, payment-number)
- Note: The primary key of the strong entity set does not exist explicitly in the weak entity set, but implicitly works through the identification link.
- If the loan-number explicitly exists, the payment becomes a strong entity, and the relationship between the payment and the loan becomes redundant. Because the loan-number attribute shared by payment and loan defines an implicit link.
Special/deductive
- During the top-down design process, a subset of the entity set with special properties is determined.
- These subsets become lower-level entity sets, which have special attributes or participate in special connections.
- Represented by a triangle with an ISA tag (e. g. Customer "is a" person ).
- Property inheritance-the lower-level object set inherits all the attributes of the upper-level object set connected to it and the connections involved.
Generalization/induction
- Bottom-up design process-combines multiple entity sets that share the same features into a high-level entity set.
- Features and generalization are simple reciprocal, which are expressed in the same way in the E-R diagram.
Specialization and Generalization
An entity set can have multiple special entity sets based on different features.
E. g. in addition to Officer vs. Secretary vs. Teller, permanent-employee vs. Temporary-Employee
Each employee is
- Permanent-employee or a member of temporary-Employee
- It is also a member of Officer, secretary, or teller.
ISA contact is also called a superclass-subclass contact.
Design constraints on specialization/Generalization
Constraints on which entities can be members of a given lower-level entity set.
- -Defined by conditions; e. g. Customers over 65 years old are members of the senior-citizen entity set; senior-citizen ISA person.
- -User defined
Whether an object can belong to more than one lower-layer entity set in a single generalization.
- -Not Intersection
- An object can belong to only one lower-level entity set.
- Add disjoint next to the ISA triangle in the E-R Diagram
- -Overlap
- One entity can belong to multiple lower-level entity sets.
Completeness constraint-indicates whether the entity in the upper-level entity set must belong to at least one lower-level entity set.
- Completely: Yes
- Partial: No
Build the concept model e_r model:
- Identifies an object;
- Identity contact;
- Identifies the relevant attributes of the object and the contact;
- Determine the attribute domain;
- Determine the candidate key and primary key attributes;
- Special and generalized entities;
- Delete attributes that are incompatible with the relational model;
- Check whether the model supports user transactions.
E-R design decision
- Objects are represented by attributes or object sets.
- A real-world concept is best represented as an entity set or a contact set.
- Use a three-element connection or a one-to-two-element connection
- Use strong or weak entity sets
- Special/generalized use-helpful for Design
- Usage of clustering-the clustering entity set is considered as a single unit, so that you do not have to care about the details of its internal structure.
Bank Database example
- Banking System data requires banks to be composed of sub-branches. Each sub-branch has a unique name in a specific city. The bank can view the sub-branch assets.
- Bank customers are differentiated by their numbers. The bank stores the name, city, and street of each customer. Customers have their own accounts and can make loans. A customer may be associated with a specific employee who acts as a loan officer or personal bank consultant of the customer.
- A bank provides two types of accounts: a savings account and a check account. An account can correspond to multiple customers, and one customer can also have multiple accounts. Each account is assigned a unique account. The bank saves the balance of each account and the latest date on which the account owner accesses the account. In addition, each savings account has an interest rate, and the overdraft information of each check account is recorded. Banks provide loans to customers. A loan occurs in a specific bank. A loan can be shared by one or more people. Each loan is determined by a unique loan number. For each loan, the Bank maintains the loan quantity and loan repayment records. Although the loan repayment number cannot uniquely determine which payment is made for all bank loans, you can determine the payment record for a specified loan. Each payment has a date and amount.
- Bank staff are differentiated by employee numbers. The bank management department stores the name and phone number of each employee, the family name of the bank employee, and the employee number of the employee Manager. The bank also stores the employee's employment date.
View Integrated Concept Database Design Process
1. design the local e_r Mode
Determine the local structure range;
Define entities to generate local entities;
Define the contact to determine the relationship between local entities and their structural constraints;
In-depth analysis to determine sub-classes, superclasses, and other connections; 2 merging and designing the global e_r Mode
(1) determine the common entity types between local e_r models;
(2) Merge local e_r mode, merge the corresponding part, retain the special part, and delete the redundant part:
Merge by two;
Merge associated items first;
Starting from the public object type, and then adding an independent local structure;
3. eliminate conflicts
(1) attribute conflict: type, value range, unit, and other conflicts;
(2) Constraints/structure conflicts: Conflicts between attributes and entities, object keys, and links;
(3) Name Conflict: The name has the same meaning and the same name has the same meaning;
4. The optimization accurately and comprehensively reflects the user's functional requirements. The number of entity types should be as few as possible; the number of attributes should be as few as possible; and the relationship between entity types should not be redundant;
(1) Merge object types. For example, you can consider merging two objects in the contact;
(2) Remove redundant attributes. After being integrated into the global model, redundant attributes within the global scope may be generated. For example, some data can be released by other data;
(3) eliminate redundant connections.
