10.2 Example of an E-R Database Schema

An E-R diagram for a library database schema is shown in Figure 10.3 which illustrates the use of various E-R semantics and notation described in this lecture and in Lecture 9.

The E-R model contains the following entities:

A Member entity: that is a regular entity. It contains attributes:

		Member_ID, a key attribute which uniquely identify Member instances.
		Name, a composite attribute which contains attributes First, Middle and Last.
		Phone_no, a multi_valued attribute which holds more than one phone number value for the member.

Department entity: that is a regular entity. It contains attributes:

		Dept_ID, a key attribute.
		Dept_name.
		Dept_building.

10.2 Example of an E-R Database Schema

A Book entity: that is a regular entity. It contains attributes:

		Book_ID, a key attribute.
		Title
		Author
		Publisher

A Penalty entity: that is a weak entity.

		It maintains charges required from a library member due to late returns of books.
		Deleting a Member instance would cause all Penalty instances associated with it to be deleted.
		The Penalty entity contains the following attributes:

			Amount
			Date
			Total, a derived attribute which is calculated based on the Amount attribute values.

10.2 Example of an E-R Database Schema

The relationships which are part of the E-R model are:

Belongs_To: a relationship type that associates the Member entity with the Department entity.

		The Member entity participation in the relationship is total, i.e., each instance of Member must participate in the Belong_To relationship.
		In other words, each member must be associated with a department.
		The cardinality ratio in this relationship is 1:N. I.e, a member can only be associated with one department. However, a department can has many members associated with it.

Borrows: a relationship type that associates the Member entity with the Book entity.

		Note that the relationship includes the attributes Return_date.
		The cardinality ratio for this relationship is N:M.

Has: an identifying relationship that associates weak entity Penalty with its identifying owner entity Member.

		The cardinality ratio for this relationship is 1:N.
		A member can have many penalties but each penalty is associated with one member only.

10.2 Example of an E-R Database Schema