MN405 Data and Information Management Assignment

MN405 Data and Information Management Assignment

MN405 Data and Information Management Assignment

Task 1: Project and Document Requirement

Library database design requirements

1.In the library, all the details of the user required.

2.Record all the information of department or sections of the library.

3.In the database, keep all the record of order books and heritage items.

4.Keeps the record of staff member details are ( staff Id staff name, his salary and his contact details)

5.Details of all the books.

Business rules and the user views

1.At a time user can order only one book.

2.The user can order book without pay anything.

3.One section of library only managed by the staff members

4.The user who orders or buys the book should be a member of the library.

5.The staff member of the library can be warden of the heritage items

6.The section of the library encloses a number of books.

E-R Diagram

The below image shows the entity relationship diagram of the library:

MN405 Data and Information Management AssignmentFigure1E-R Diagram

Relationship

Relationship of the different entity involved with the library.

Table1Relationship Table

S.no

Entity of the library

Relationship

1

Book details

Borrower

One to many

1:0

2

Borrower

User

One to many

1:0

3

Book details

Book of department

One to many

1:0

4

Book department

Library staff

One to many

1:0

5

Library staff

Heritage items

One to many

1:0

Relationship Diagram :

MN405 Data and Information Management AssignmentFigure2Relationship diagram

Normalization Process

Normalization is the process to organize the attributes (column) and the tables of the database to minimize the data redundancy and by this improve the data reliability [1].

There is various steps to perform the normalization but it has 3 important steps which have to follow to achieve the normalization process:

Un-normalized form:

Book details: Book_ID, Department_ID, Book_Title, Author, Edition, Year_publication, price, Borrower_ID, Borrower_Book, Issue_Date, Return_date, Fine

Heritage Item: Item_ID, CareTaker_ID, Item_Name, Place_found, Historic_value,Staff_ID, Staff_Name, Designation, Staff_salary, Joining_date, Staff_phone

Now apply the normalization

Step 1: 1NF

In the 1NF there is no repeated value allowed in the column section, the column must have to contain a unique value.[2] After applying the 1NF the result is shown below:

Book_Details:

Book_ID, Department_ID, Book_Title, Author, Edition, Year_publication, price,

Borrower_details:

Borrower_ID, Borrower_Book, Issue_Date, Return_date, Fine

Step 2: 2NF

It is the second step of this process, in this, the column must depend on the primary key. It is followed by the 1NF [3]. After applying the 2NF the result is shown below:

Heritage_Table:

Item_ID, CareTaker_ID, Item_Name, Place_found, Historic_value

Staff_Details:

Staff_ID, Staff_Name, Designation, Staff_salary, Joining_date, Staff_phone

Step 3: 3NF

It is the 3rd and the final step of this process. In this, all the attributes are reduced into different tabular form. No one has repeated values [4]. After applying the 3NF the result is shown below:

Book_Details:

Book_ID, Department_ID, Book_Title, Author, Edition, Year_publication, price,

Borrower_details:

Borrower_ID, Borrower_Book, Issue_Date, Return_date, Fine

Heritage_Table:

Item_ID, CareTaker_ID, Item_Name, Place_found, Historic_value

Staff_Details:

Staff_ID, Staff_Name, Designation, Staff_salary, Joining_date, Staff_phone

Book_Department:

Department_ID, Department_Nmae, Department_Head

User:

User_ID, User_Name, User_Address, User_Email, User_Phone, User_Age

Functional dependencies in library database

This diagram shows the dependency of the attributes. In the diagram, the attributes show with the elliptical shape and dependency shown by the arrow. The red arrow shows the primary key of one table act as a forgone key to another table.

MN405 Data and Information Management Assignment

Figure3Function Dependency Diagram

Document of used reports, queries and forms

Forms of Library:

Book department form:

MN405 Data and Information Management AssignmentFigure4Book department form

Book details form:

MN405 Data and Information Management AssignmentFigure5Book details form

Borrower form:

MN405 Data and Information Management AssignmentFigure6Borrower form

Heritage item form:

MN405 Data and Information Management AssignmentFigure7Heritage item form

Library staff form:

MN405 Data and Information Management AssignmentFigure8Library staff form

User form:

MN405 Data and Information Management AssignmentFigure9User form

Quarries:

Book issue details

MN405 Data and Information Management AssignmentFigure10Book issue details

Book details stock

MN405 Data and Information Management Assignment

Figure11Book details stock

Heritage details with staff

MN405 Data and Information Management Assignment

Figure12Heritage details with staff

Minimum fine pay

MN405 Data and Information Management Assignment

Figure13Minimum fine pay

Staff details with books department

MN405 Data and Information Management Assignment

Figure14Staff details with books department

Report:

Book issue detail report

MN405 Data and Information Management AssignmentFigure15Book issue detail report

Books details stock report

MN405 Data and Information Management AssignmentFigure16Books details stock report

Heritage details with staff report

MN405 Data and Information Management Assignment

Figure17Heritage details with staff report

Maximum fine pay details report

MN405 Data and Information Management AssignmentFigure18Maximum fine pay details report

Staff details with book department report

MN405 Data and Information Management AssignmentFigure19Staff details with book department report

Task 2: Implementation of Melbourne library using Ms- Access

Report

Book issue detail report

MN405 Data and Information Management AssignmentFigure20Book issue detail report

Books details stock report

MN405 Data and Information Management AssignmentFigure21Books details stock report

Heritage details with staff report

MN405 Data and Information Management AssignmentFigure22Heritage details with staff report

Maximum fine pay details report

MN405 Data and Information Management AssignmentFigure23Maximum fine pay details report

Staff details with book department report

MN405 Data and Information Management AssignmentFigure24Staff details with book department report

Queries

Book issue details

MN405 Data and Information Management Assignment

Figure25Book issue details

SQL view:

MN405 Data and Information Management Assignment

Figure26SQL view

Book details stock

MN405 Data and Information Management Assignment

Figure27Book details stock

SQL view:

MN405 Data and Information Management AssignmentFigure28SQL view

Heritage details with staff

MN405 Data and Information Management Assignment

Figure29Heritage details with staff

SQL view:

MN405 Data and Information Management Assignment

Figure30SQL view

Minimum fine pay

MN405 Data and Information Management Assignment

Figure31Minimum fine pay

SQL view:

MN405 Data and Information Management Assignment

Figure32SQL view

Staff details with books department

MN405 Data and Information Management Assignment

Figure33Staff details with books department

SQL view:

MN405 Data and Information Management Assignment

Figure34SQL view

Forms for Melbourne library

Book department form:

MN405 Data and Information Management Assignment

Figure35Book department form

Book details form:

MN405 Data and Information Management Assignment

Figure36Book details form

Borrower form:

MN405 Data and Information Management Assignment

Figure37Borrower form

Heritage item form:

MN405 Data and Information Management Assignment

Figure38Heritage item form

Library staff form:

MN405 Data and Information Management Assignment

Figure39  Library staff form

User form:

MN405 Data and Information Management Assignment

Figure40User form

Task 3: SQL commands to generate the queries

SQL query1: Book details stock:

SELECT [books issue detail].Book_ID, [books issue detail].Book_Title, [books issue detail].price, Books_detail.Year_Published

FROM [books issue detail] INNER JOIN Books_detail ON [books issue detail].[Book_ID] = Books_detail.[Book_ID];

SQL query2: book issue details

SELECT Books_detail.Book_ID, Books_detail.Book_Title, Books_detail.Author, Books_detail.price, Borrower.Borrower_ID, users.user_Name

FROM users INNER JOIN (Books_detail INNER JOIN Borrower ON Books_detail.Book_ID = Borrower.Borrowed_book) ON users.user_ID = Borrower.Borrower_ID;

SQL query3: heritage details with staff

SELECT Heritage_item.Item_Name, Heritage_item.Historic_Value, lib_Staff.Staff_Name

FROM lib_Staff INNER JOIN Heritage_item ON lib_Staff.Staff_ID = Heritage_item.CareTaker_ID;

SQL query4: maximum fine pay

SELECT Max(users.user_Name) AS MaxOfuser_Name, Max(users.user_Address) AS MaxOfuser_Address, Max(users.user_Email) AS MaxOfuser_Email, Max(users.user_phone) AS MaxOfuser_phone, Max(Borrower.fine) AS MaxOffine

FROM users INNER JOIN Borrower ON users.user_ID = Borrower.Borrower_ID;

SQL query5: staff details with books department 

SELECT books_Department.Department_Name, lib_Staff.Staff_Name, lib_Staff.Designation

FROM lib_Staff INNER JOIN books_Department ON lib_Staff.Staff_ID = books_Department.Department_Head;

References

[1]  M. Rouse, "What is database normalization? - Definition from WhatIs.com", SearchSQLServer. [Online]. Available: http://searchsqlserver.techtarget.com/definition/normalization. [Accessed: 20- Sep- 2017]

[2]  C. Singh, "Normalization in DBMS: 1NF, 2NF, 3NF and BCNF in Database", beginnersbook.com, 2015. [Online]. Available: https://beginnersbook.com/2015/05/normalization-in-dbms/. [Accessed: 20- Sep- 2017]

[3]  W. server, "What is Normalization in SQL? - SQLServerCentral", Sqlservercentral.com, 2010. [Online]. Available: http://www.sqlservercentral.com/blogs/abhijit_desai/2010/09/07/noramlization/. [Accessed: 20- Sep- 2017]

[4]  Alyeksandr, "Database design  & Normalization (1NF, 2NF, 3NF)", Slideshare.net, 2012. [Online]. Available: https://www.slideshare.net/jagaarj/database-design-normalization. [Accessed: 20- Sep- 2017]