Question

A loan company wants to design a database to track student loans. Each student attending school is eligible for a loan. A student may have more than one loan. A student may be registered, possibly at different times, in more than one school. Each loan should belong to only one bank. Each bank can approve as many loans as it desires. For each loan, the loan company will track: the student’s SSN, name, address, amount of loan, date of the loan, interest rate ( which may be different for each loan as determined by the bank), duration of the loan, monthly payment, remaining balance, school ID, school name and address, number of years the student has been at the school, bank name, bank branch, and bank ID. Draw an ER diagram. State any assumptions you make in the diagram. Note: You must use one of the automated database design tools such as: ER STUDIO ER Assistant Vision Enterprise or draw.io website.

Answer 1

1).ANSWER:

GIVEN BELOW:

ERD for Given Database

No of Entities:

1. Student entity : Attributes are { SSN , name , address,NoOFYearInSchool}
2. Loan entity: Attributes are { LoanAmount ,LoanDate , InterestRate , Duration , MonthlyPayment , RemainingBalance ,SSN, BankID}
3. School entity : Attributes are { SchoolID , SchoolName , SchoolAddress , SSN}
4. Bank entity : Attributes are { BankID , BankName , BankBranch}
Relationships :

There are have relationship between Student entity and Loan entity
There are Registered relationship between Student entity and School entity
There are BelongsTo relationship between Loan entity and Bank entity
Multiplicity:

There are 1:M multiplicity between Student entity and Loan entity
There are 1:M multiplicity between Student entity and School entity
There are M:1 multiplicity between Loan entity and Bank entity
Pk and FK in ERD

1. SSN : PK in Student entity and Fk in Loan entity and School entity
2. SchoolID : Pk in School entity
3. BankID : Pk in Bank entity and FK in Loan entity
Diagram :