VIDEO
HIGHLIGHT
Log InSign up
Exercises based on ER Model Concepts (Part 1)
SummaryTranscriptChat

Exercises based on ER Model Concepts (Part 1)

ER Model Exercises Part 1

In this video, the speaker focuses on exercises based on ER model concepts. The first exercise problem is to consider an ER diagram of a bank database and answer questions related to it.

Identifying Strong and Weak Entity Types

  • Non-weak or strong entity types are represented by a rectangle in the ER diagram. Bank account, loan, and customer entity types are non-weak entities in the given diagram.

Identifying Weak Entity Type and Constraints

  • A weak entity type is represented by a double rectangle in the ER diagram. In the given diagram, bank branch is a weak entity type with partial key "branch number" and identifying relationship "branches".
  • The constraint of the partial key "branch number" is that it needs to be combined with the key attribute of its owner entity type (bank) to uniquely identify the weak entity type (bank branch).
  • The constraints of the identifying relationship "branches" are that the weak entity set must have total participation in this relationship set, and that the relationship between bank and bank branch must be one-to-many.

Relationship Types and Min-Max Constraints

  • There are five relationship types in this diagram: branches, accounts, loans, AC (account-customer), LC (loan-customer).
  • For each relationship type:
  • Branches: Bank has one-to-many relationships with bank branches; each bank branch belongs to only one bank.
  • Accounts: Bank branch has zero-to-many relationships with accounts; each account belongs to only one bank branch.
  • Loans: Bank branch has zero-to-many relationships with loans; each loan belongs to only one bank branch.
  • AC (account-customer): Account has one-to-many relationships with customers; each customer can have multiple accounts.
  • LC (loan-customer): Loan has one-to-many relationships with customers; each customer can have multiple loans.

Relationship Types

This section explains the different relationship types in ER diagrams.

Entity Types and Participation

  • There are two types of participation: total and partial.
  • The entity type "customer" has a partial participation with a maximum number of accounts.
  • The relationship type "loans" has a total participation with one loan belonging to only one bank branch.
  • The entity type "bank branch" has a partial participation with any number of loans taken.

User Requirements

  • Five entity types were identified in the ER diagram: bank, bank branch, account, loan, and customer.
  • Each bank must have a unique code name and address.
  • Each bank branch may or may not have loans or accounts.
  • Each account is related to exactly one bank branch and at least one customer.
  • Each loan is related to exactly one bank branch and at least one customer.
  • Each customer must have a unique social security number (SSN), name, address, phone number, and may or may not have accounts or loans.

Min Max Constraints

This section explains how to specify min max constraints in an ER diagram based on user requirements.

Customer Account Loans Bank Branch Relationships

  • Every customer must have at least one account with a minimum-maximum constraint of 1,n.
  • A customer can take up to two loans at once.
  • A bank branch cannot have more than 1000 loans.

Conclusion

  • The ER diagram can be used to specify the min max constraints based on user requirements.

Entity Type Constraints

In this section, the speaker discusses the constraints for a specific entity type and clarifies that they will not change based on the given information in this question.

Constraints for Entity Type

  • The constraints for this entity type have already been discussed earlier and will remain unchanged.
  • The only constraints that will change are those related to the customer and bank branch entities.

Loan Relationship Constraints

This section covers the constraints related to the loan relationship between bank branches and loans.

Loan Relationship Constraints

  • Every bank branch may or may not have taken a loan, so there is partial participation.
  • One bank branch cannot have more than 1000 loans, so the maximum value is 1000.
Video description

DBMS: Exercises based on ER Model Concepts Topics discussed: A solved problem based on ER Model Concepts: Consider the ER diagram shown in the figure for part of a BANK database. Each bank can have multiple branches, and each branch can have multiple accounts and loans. 1. List the strong (nonweak) entity types in the ER diagram. 2. Is there a weak entity type? If so, give its name, partial key, and identifying relationship. 3. What constraints do the partial key and the identifying relationship of the weak entity type specify in this diagram? 4. List the names of all relationship types, and specify the (min, max) constraint on each participation of an entity type in a relationship type. 5. List concisely the user requirements that led to this ER schema design. 6. Suppose that every customer must have at least one account but is restricted to at most two loans at a time and that a bank branch cannot have more than 1,000 loans. How does this show up on the (min, max) constraints? Follow Neso Academy on Instagram: @nesoacademy(https://bit.ly/2XP63OE) Contribute: http://www.nesoacademy.org/donate Memberships: https://bit.ly/2U7YSPI Books: http://www.nesoacademy.org/recommended-books Website ► http://www.nesoacademy.org/ Forum ► http://forum.nesoacademy.org/ Facebook ► https://goo.gl/Nt0PmB Twitter ► https://twitter.com/nesoacademy Music: Axol x Alex Skrindo - You [NCS Release] #DBMSByNeso #DBMS #ERDiagram