C2028 Database Management System Term 3 2008 Assignment, part 1

Home > Education, IADIC, Informatics > C2028 Database Management System Term 3 2008 Assignment, part 1

C2028 Database Management System Term 3 2008 Assignment, part 1

March 6th, 2009

Hi All!

This is the assignment of C2028 Database Management System that I do in Term 3 2008 in my IADIC (International Advanced Diploma in Computing) course from Informatics Computer School Singapore.

This module talks about many factors of a Database system. And this assignment focuses on the Normalization process.

So, basically this assignment ask us to design a database for a library using ERD (Entity Relationship Diagram), then do the normalization process to the designed database, we go from first normal form until the third normal form.

Here is the assignment question:

You are to develop a Library database system.

(i)    Candidates must provide a description of current business activities and operations, a list of Business Rules.
(ii)    Design and derive the following from the above system:
•    Identify of objects – Entities, Relationship and Attributes
•    Identify and include Primary Keys.
•    Normalization the relationship (At least in 3NF).
•    Draw Final ER Diagram.

My Answer:

In this assignment, I will develop a database system for a library. The database developed here will be built in a way that makes it suitable to support the operation of a library. So we need to identify the environment for the database (Library’s business) and identify all the entities and relationships that is needed to support the library, by doing it that way we can make sure that the database that we develop for the library will be suitable and able to support the library’s operation and management. There are two main parts in this assignment. The first part that describe more on the library’s business, and the second part that explain more on the library database itself.

In the first part, we will discuss about the business activities, operations, and business rules of the library itself. The database that we build for the library must be suitable for the library’s business so it can support the library effectively, that is why we need to identify the particular library’s business activities, operations, and business rules.

The second part of this assignment will explain technical side of the library’s database. I will try to identify what are the entities are there in the library’s system, how the relationships between those entities, and what are attributes that belong to each entity. After identifying these things, then I will move on to identify the primary key field for each table that is present in the library database. Before commencing to draw the final Entity Relationship diagram, we will try to observe if there is any relationship that can be normalized, if so then we will do all the necessary relationship normalization. Finally, I will draw the final Entity Relationship diagram for this library database system.

The main business activity of the library is manages its collection of books, accept new membership registration, lending books to members, handling book reservation, and return of borrowed book.

The library needs to keep tracks of so many records. It has its list of books that need to be sorted in a way that will make it easy for someone to search for a book’s information. It also needs to do many data management for its member, books, and staffs. The library has many members, a vast collection of books, and many staffs with different positions such as librarian, security guard, cleaning staff, administration staffs, etc. For these reason, the library need a library database system that will support its daily operation.

In this section, I will list some of the business rules from the library that is important for the development of the library’s database system.

II.c.1. Member Rules

ü A person acquires library membership after he/she fills a form that tells his particular information such as name, address, e-mail, etc.

ü A member can borrow more than one book at a time by bringing the book to a librarian that is in duty who will process the borrowing process.

ü Each member can borrow book for maximum of 40 (forty) days.

ü Library member may do a book reservation by filling a form and then put the form in the reservation box.

II.c.2. Book Management Rules

ü Each library book is given a unique book identification number.

ü A member may not borrow book that status is a reference book.

ü The entire book in the library must be recorded for its title, author, publisher, number of pages, ISBN, etc.

ü Each book in the library is given a genre that will make it easier for people to have a brief image about what is the book’s theme.

II.c.3. Staff Rules

ü Each staff’s personal particular must be recorded.

ü Only staff with librarian position may facilitate a library member to borrow a book.

ü The salary of the staff is not defined by the position.

ü Some staff can be assigned as a supervisor for another library staff.

We will try to identify what are the objects that are needed in this library’s database system. These objects comprise of Entities, Relationship, and Attributes for each entity.

Entity is the main object where it can represent a person, a place, an event, or a specific thing that is needed by the library. For example, we may have library member or book as an entity in a library database. Entity is the main object that most likely will become a table in a relational database system. So, if we have an entity called member, most likely we will have a table called member also in our database.

One entity can have many attributes. Attributes is the characteristic or we can also say that attribute is the property of an entity. For example, if we have an entity called book we may have some attributes for this entity such as book name, description, ISBN (International Standard Book Number), etc.

Relationship shows how one entity relates to another entity in the database system. There are many kinds of relationship.

Beside the explanation that I give about Entity in the previous section, there are also few other views of what an entity is. CR Vanslyke (knol, 2008) suggests that entity is a thing where the database will store the data. Therefore, in our developed database, we should be able to store data into entity that we found here.

Some things we can make as entity are:

a. Things or items that is countable.

b. Events or transactions, for example book borrowing.

c. People that are involved in the system

Now, we will look at what are the entities that we going to have in this library database system. We will only have what is important in the library database system to be the entity. Entities that we have here are:

Member
Staff
Book

Member, Staff, and Book are three entities that are needed by this library database system.

The library will need to record its member that is why we have member as one of the entity. Additionally, the library can have many staffs including librarian, security officer, and so on, therefore, it is also important to have a record of the library staffs inside the staff entity. Finally, book is the main asset to a library, so definitely we will need to store data about all the library’s books.

Here I will try to identify what are the attributes for the three entities (Member, Staff, and Book) that we have identified in the previous section.

Attributes is the characteristic of our three entities. Mike Chapple (About.com, 2008) suggest that in database environment attributes, also known as field or column, is a single data item that is associated to each database entity.

Now, lets look at what are the attributes for each of our three entities:

1. Member

2. Staff

3. Book

The attributes that we will assign to each entity will be based on what data that we need to know from an entity. Additionally, all the attributes that I assigned to each entity here is considered as condition before any normalization process is done.

Attributes for each entity:

1. Member

a. member_id

b. name

c. gender

d. date_of_birth

e. address

f. phone_no

g. e-mail

h. borrow_id – (multivalued attribute)

i. book_borrowed – (multivalued attribute)

j. borrow_date – (multivalued attribute)

k. librarian_serve – (multivalued attribute)

l. book_duedate – (multivalued attribute)

m. return date – (multivalued attribute)

n. reservation_id – (multivalued attribute)

o. book_reserved – (multivalued attribute)

p. reserve_date – (multivalued attribute)

2. Staff

a. staff_id

b. name

c. gender

d. date_of_birth

e. address

f. phone_no

g. e-mail

h. position

i. salary

j. Supervisor

3. Book

a. book_id

b. ISBN

c. Title

d. Author

e. Publisher

f. Publication_year

g. Edition

h. Pages

i. Genre

j. Genre_description

k. Reference_book

That is all the attributes for each of our entity (Member, Staff, and Book). Of course this attributes refer to the condition before these entity attributes comes to the normalization process. We will do the normalization process in section III.c.

Relationship is a natural relation that is exist between entities in Entity Relationship Diagram, for example Member borrow Book (webfuse.cqu.edu.au, 2006).

There are some kinds of relationship degree: unary, binary, ternary, and n-ary relationship. Unary relationship is a relationship that involves only a single entity, or we can say that it is a relationship between an entity to itself. Binary relationship is a relationship that involves two different entities. Ternary relationship exists when the relationship involves three different entities. Finally, when we have a relationship with more than three entities involved, we call this kind of relationship as n-ary relationship (CR Vanslyke, 2008).

Now lets look at what relation that we have for our three entities that are Member, Staff, and Book. The relation that we will see here is the relation between entities before any normalization process is carried out.

Relations that we have between our entities here are:

1. Borrow

2. Reserve

3. Supervises

That are the three relations that we have for our entities in this library database system. In the next section we will discuss more about each of these three relations.

1. Borrow

Borrow is a ternary relationship involving the three entities Member, Book, and Staff. I will give a simple ERD (Entity Relationship Diagram) to show the borrow relationship.

Note: – M means multivalued attribute

The above ERD shows how the relations “borrow” relate all the three entities Member, Staff, and Book together. In a glance, this relation means that the library’s member can borrow books from the library facilitated by the library’s staff (librarian).

Now lets take a look at the signs at the relationship line there in the ERD. We can see that there are some signs like single line, circle, and crowfoot (/|\), these symbols represents the cardinality of the relation. The symbol closer to the entity is called as the maximum cardinality while the symbol next to it is called as the minimum cardinality. Maximum cardinality is the maximum number of entity instances that can be related to an entity. Minimum cardinality means the minimum number of instances that can be related to an entity. The circle symbol means zero or null, the line symbol means one, and the crowfoot symbol means many.

Using the cardinality symbol’s meaning we can get more information for “borrow” relation that is in the above ERD.

First, let us look at the Book entity. There is a circle symbol as the minimum cardinality and a crowfoot symbol as the maximum cardinality. It means that each of the library member can borrow minimum no books (not borrow anything) and also allowed to borrow more than one books.

Second, let us look at the Staff entity. There is a line symbol for both the minimum and maximum cardinality. This means that for each book borrowing events there must be minimum and maximum one staff (librarian) that facilitate the member to borrow the book.

Finally, let us look at the Member entity. We can see that there is a circle symbol as the minimum cardinality and a line symbol as the maximum cardinality. This means that each book in the library can be borrowed by no one or can be borrowed by maximum one member at a time.

2. Reserve

Reserve is a binary relationship between Member entity and Book entity.

Below is the Entity Relationship Diagram for the “reserve” relation.

Note: – M means multivalued attribute

The above ERD shows how Member entity and Book entity is related with the “reserve” relationship. This relation is needed to record the books reserved by the customer. Let us look at the cardinality of this relationship.

First, the cardinality symbols for the Book entity. There is a circle symbol as the minimum cardinality and a crowfoot symbol as the maximum cardinality. It tells that a library member can reserve no book or a member can return one or more than one book.

Second, the cardinality symbols for the Member entity. There is a circle symbol as the minimum cardinality and a crowfoot symbol as the maximum cardinality. It tells us that there can be no library member reserve a book (in case that no one reserve any book from the library) and also one library book can be reserved by many library members at a time.

3. Supervises

Supervise is a unary relationship that relates Staff entity with itself. This relation happen as a consequence that there is many job positions for the library staff’s and there are some library staffs that supervises the job of another library staff.

Below is the Entity Relationship Diagram for the “supervises” relation.

The above ERD shows how the unary relationship “supervises” relates the staff entity to itself. This relation will show who supervises who in the library’s staff. Now let us look at this relation with its cardinality.

First, let us look at the two cardinality symbols below the staff entity in the above ERD. We can see that there is a circle symbol for the minimum cardinality and a crowfoot symbol for the maximum cardinality. From these symbol we can get information that a library staff may supervises a minimum of no other library staff, or he/she can supervises many other library staffs.

Second, let us look at the cardinality symbols at the right side of the Staff entity. We can see that there is a circle symbol for the minimum cardinality and a line symbol for the maximum cardinality. The information that we can derive from these two symbols is that a library staff can have no supervisor (in case that he/she have the highest staff position in the library) or a maximum of one supervisor for each library staff.

In conclusion, we have three kinds of relationship for our three entities (Member, Staff, Book). These relationships are “borrow” which is a ternary relationship involving all the three entities, “reserve” which is a binary relationship relating the member and the book entity, and also “supervises” which is a binary relationship relating the staff entity to itself. These relation is for the library database system before any normalization process is done. We will do the normalization process in section III.c.

After we identify the Entities, Attributes, and Relationships among the entities in the previous section, I will try to identify the primary keys for each of the entity that we have here in our library database system.

Primary key is an attribute or combination of it that will uniquely define each of the entity’s instances. There should be no two instances with a same primary key attribute. Using a primary key, we can guarantee that there will be no duplicate instance for each of our entity. Therefore, each of the entity that we define in the previous section must have a primary key.

When selecting an attribute to be a primary key, we need to select an attribute that have no chance of having duplicate value. Below are some tables that show the entities that we have in this library database system together with its attribute and primary key attribute (bold and underscored attribute). These primary keys are before any relationship normalization is done.

Member
Primary Key	member_id
	name
	gender
	date_of_birth
	address
	phone_no
	e-mail
multivalued	borrow_id
multivalued	book_borrowed
multivalued	borrow_date
multivalued	librarian_serve
multivalued	book_duedate
multivalued	return date
multivalued	reserve_id
multivalued	book_reserved
multivalued	reserve_date

Above is the table showing Member entity and its attributes. We can see that the member_id attribute is defined as the primary key. The reason why I choose member_id as the primary key for this entity is because we can assign a unique member id for each of the library’s member, so it will guarantee that we would not have any duplicate value in this attribute. Why other attribute does not qualify to be the primary key for this entity? It is because other attributes have a high chance to have more than one value same value.

Staff
Primary Key	staff_id
	name
	gender
	date_of_birth
	address
	phone_no
	e-mail
	position
	salary
	Supervisor

Above is the table showing Staff entity and its attributes. We can see that the staff_id attribute is defined as the primary key. The reason why I choose staff_id as the primary key for this entity is because we can assign a unique staff id for each of the library’s staff, so it will guarantee that we would not have any duplicate value in this attribute.

Book
Primary Key	book_id
Primary Key	ISBN
	Title
	Author
	Publisher
	Publication_year
	Edition
	Pages
	Genre
	Genre_description
	Reference_book

Above is the table showing Book entity and its attributes. We can see that the book_id and ISBN attribute is defined as the primary key. The reason why I choose book_id and ISBN as the primary key for this entity is because we can assign a unique book id for each of the library’s book and also different book have a different ISBN, so it will guarantee that we would not have any duplicate value in these two attributes.

In conclusion, in this section we have identified a primary key attribute for each of the entity that we have. We have member_id attribute as the primary key for the Member entity, we have staff_id attribute as the primary key for Staff entity, and book_id as the primary key for the Book entity. These primary key is before any relationship normalization, after some relationship normalization there is a chance that we will have more primary keys. We will do the normalization process in section III.c.

In this section, we will perform some normalization process for our three entities which are Member, Staff, and Book. In normalization, we will break down the complex entity and its relationship into smaller parts that is easier to understand and closer to the entities in the real world.

Tony Marston (tonymarston.co.uk, 2004) suggest that normalization is concerned with things like:

ü Identify the relationships between entity’s attribute

ü Combine some attributes to make a new relation

ü Combine relations to form a database

While the benefits of normalization are:

ü Eliminate redundancy

ü Make the model closer to the entity, process, and relationship in real world

ü Make a flexible data structure

There are three normalized form that we want to achieve, those are 1^st Normal Form (1NF), 2^nd Normal Form (2NF), and 3^rd Normal Form (3NF). The following sections will discuss each transformation for our library database system to its 1NF, 2NF, and 3NF.

Normal Form (1NF)

Before we do any normalization, we consider our library database system is in un-normalized form (UNF). To make our library database system to be in the first normal form (1NF), what we will need to do is to remove any repeating groups (multivalued attributes) and define all the necessary key attributes after removal of the repeating groups.

Before we do any normalization process to make our library database system to be in the first normal form (1NF), I would like to give an Entity Relationship Diagram (ERD) for the current un-normalized form:

Note: - M means multivalued attribute and - PK means Primary Key

III.c.1.i. Member Entity

As we can see in the ERD of the un-normalized library database system, there are many attributes in Member entity that is a multivalued attribute. They are borrow_id, book_borrowed, borrow_date, librarian_serve, book_duedate, return date, reserve_id, book_reserved, and reserve_date.

These multivalued attributes is intended to store data about library member’s book borrowing and book reserving activity. What is the book borrowing id (borrow_id), what book is borrowed by a member (book_borrowed), when he/she borrow it (borrow_date), who is the librarian who process the borrowing process (librarian_serve), when is the due date of the book (book_duedate), and when he/she actually return the book (return_date). For the book reservation activity, we have reserve_id that show the reservation id, book_reserved that store data about the reserved book, and reserve_date that shows when is the reservation is made. In order to eliminate these repeating groups, I will introduce the relation “borrow” and the relation “reserve” as a new table/entity.

In the new “borrow” entity, we will rename book_borrowed attribute to book_id and then we also rename librarian_serve attribute to staff_id. This is because the book that is borrowed by a library member can be identified by the book_id attribute as in the Book entity, and the librarian who serve the borrowing process can be identified by the staff_id taken from the Staff entity. The other attribute that we will put in the new “borrow” entity are member_id, borrow_date, book_duedate, and return_date. The primary key of this entity will be the borrow_id attribute.

Member
Primary Key	member_id
	name
	gender
	date_of_birth
	address
	phone_no
	e-mail
multivalued	reservation_id
multivalued	book_reserved
multivalued	reserve_date

Borrow
Primary Key	borrow_id
	member_id
	staff_id
	book_id
	borrow_date
	book_duedate
	return_date

For the new “reserve” entity, we will rename the book_reserved attribute to book_id taken from Book entity. The primary key will be the reservation_id and we also include the reserve_date attribute as one of the attribute in this entity.

Member
Primary Key	member_id
	name
	gender
	date_of_birth
	address
	phone_no
	e-mail

Reserve
Primary Key	reservation_id
	book_id
	reserve_date

Therefore, now we have removed all the repeating groups in the Member entity.

III.c.1.ii. Book Entity

In the Book entity, there is no attribute with multiple value, that means that there is no repeating group in this entity, therefore no modification need to be made in this entity to make it in the 1NF.

Book
Primary Key	book_id
Primary Key	ISBN
	Title
	Author
	Publisher
	Publication_year
	Edition
	Pages
	Genre
	Genre_description
	Reference_book

III.c.1.iii. Staff Entity

In the Staff entity, there is no attribute with multiple value, that means that there is no repeating group in this entity, therefore no modification need to be made in this entity to make it in the 1NF.

Staff
Primary Key	staff_id
	name
	gender
	date_of_birth
	address
	phone_no
	e-mail
	position
	salary
	Supervisor

The Entity Relationship Diagram (ERD) for the first normal form (1NF) is as follow:

1^st Normal Form ERD. Note: - PK means primary key.

Normal Form (2NF)

The second step of our normalization process is to make our library database system to its second Normal Form (2NF).

We identify that a relation is in the second Normal Form (2NF) if the relation satisfy two conditions. First, the relation must be in the 1NF (First Normal Form). Second, there are no partial dependencies, or we can say that the entire attribute in the entity is functionally dependent on the primary key only.

Since we have made our library database system’s relation to its first normal form (1NF) in the previous section, now we just need to identify the functional dependency from each of the entity that we have, and try to find if there is any partial dependency.

III.c.2.i. Member Entity

The primary key in Member entity is member_id