Access over 20 million homework & study documents
search

ER Diagram Exercises

Content type

User Generated

Subject

Mathematics

Type

Other

Rating

Showing Page:
1/9
kxv141230 Karthik Reddy Vesireddy
1. Draw an ER diagram for university library system. Specify key attributes of each entity type
and structural constraints on each relationship type (cardinality ratios and participation
constraints). State any assumptions you make.
Assumptions:
1. ‘Book_Author’ is dependent on the ‘Book’ entity; hence ‘Book_Authors’ is a weak entity.
‘Authored_By’ is the identifying relationship. The (book_id,Author_name) form the
primary key for the book_authors relation. Here, Book_authors has a total participation in
the
‘Authored_by’ relationship. An author may write any number of books, and a book can
have many authors, so the cardinality ratio for this is M: N (Many to many).
2. The ‘Publisher’ entity contains only the data of publishers, whose books are in the library.
Only one publisher can publish a book and one publisher can publish many books. So,
publisher has a total participation in the ‘published_by’ relationship with Book entity and
all the publisher data of all books are in the ‘publisher’ entity, so a total participation in the
‘published_by’ relationship. The cardinality ratio is M: 1 (Many to one).
3. The book entity in the relationship ‘Book_copies’ has total participation because; all the
books are present in all the branches put together. Each library_branch has many books.
So, the cardinality ratio is M: N (many to many).
4. In the Book_Loans relationship, all the books are not borrowed by the borrowers, some are
not. So, it is a partial participation. A book can be borrowed by many borrowers. A
borrower can borrow many books. So, cardinality ratio is M:N (Many to many).
5. In the ‘Book_Loans’ relationship, all the books are loaned through the existing library
branches only, so Library_branch has a total participation, and a ‘library_branch’ does not
lend all the books, so ‘book’ entity has a partial participation in this relationship.
6. Even if a student does not borrow any book, his information is present in the borrower
table, hence it is partial participation with the book_loans.
ER Diagram:

Sign up to view the full document!

lock_open Sign Up
Showing Page:
2/9
kxv141230 Karthik Reddy Vesireddy

Sign up to view the full document!

lock_open Sign Up
Showing Page:
3/9

Sign up to view the full document!

lock_open Sign Up
End of Preview - Want to read all 9 pages?
Access Now

Unformatted Attachment Preview

kxv141230 Karthik Reddy Vesireddy 1. Draw an ER diagram for university library system. Specify key attributes of each entity type and structural constraints on each relationship type (cardinality ratios and participation constraints). State any assumptions you make. Assumptions: 1. ‘Book_Author’ is dependent on the ‘Book’ entity; hence ‘Book_Authors’ is a weak entity. ‘Authored_By’ is the identifying relationship. The (book_id,Author_name) form the primary key for the book_authors relation. Here, Book_authors has a total participation in the ‘Authored_by’ relationship. An author may write any number of books, and a book can have many authors, so the cardinality ratio for this is M: N (Many to many). 2. The ‘Publisher’ entity contains only the data of publishers, whose books are in the library. Only one publisher can publish a book and one publisher can publish many books. So, publisher has a total participation in the ‘published_by’ relationship with Book entity and all the publisher data of all books are in the ‘publisher’ entity, so a total participation in the ‘published_by’ relationship. The cardinality ratio is M: 1 (Many to one). 3. The book entity in the relationship ‘Book_copies’ has total participation because; all the books are present in all the branches put together. Each library_branch has many books. So, the cardinality ratio is M: N (many to many). 4. In the Book_Loans relationship, all the books are not borrowed by th ...
Purchase document to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Anonymous
I use Studypool every time I need help studying, and it never disappoints.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4