IDB Assignment Question - APD1F-APU1F-2106

CT042-3-1-IDB Group Assignment Page 1 of 4
Module Learning Outcomes:

 Apply redundancy control in designing a database.
 Demonstrate a database solution using an appropriate tool based on a case study.
Case Study:
UPA University Library Management System
UPA University has approximately 20,000 library members comprising of students and staff
from various departments, 100,000 titles, and 200,000 volumes (or an average of 2 copies per
book). About 15 percent of the volumes are out on loan at any one time.
The librarians ensure that the books that members want to borrow are available when the
members want to borrow them. Also, the librarians must know how many copies of each book
are in the library or out on loan at any given time. A catalogue of books is available online that
lists books by author, title, and subject area. For each title in the library, a book description is
kept in the catalogue that ranges from one sentence to several pages. The librarians on duty want
to be able to access this description when members request information about a book.
Books are categorized as either open-stack, yellow-tagged, red-tagged or green-tagged books.
The loan periods for these books vary based on their categories. The library does not lend some
books, such as reference books, journals, student projects and maps. The librarians must be able
to differentiate between books that can be lent and those that cannot be lent. Student and staff
members are allowed to borrow at any one time, a maximum total of 5 and 10 books
respectively. Lecturers are allowed to check out green-tagged books for one-month intervals.
Members usually get a reminder email 1 day before a book is due for return. About 5 percent of
the members have to be sent reminders to return a book. Most overdue books are returned within
a month of the due date. Members are required to pay fines for overdue books. The fines are
charged based on the number of overdue days and varies for each category of books.
Approximately 5 percent of the overdue books are either kept or never returned. In addition,
members are also allowed to reserve books that are out on loan. The library staff will notify
those members when the books become available for borrowing.
The most active members of the library are defined as those who borrow at least ten times during
the year. About 20 percent of the members are totally inactive in that they are members but never
borrow any books.
Some books may have the same title; therefore, the title cannot be used as a means of
identification. Every book is identified by its International Standard Book Number (ISBN), a
unique international code assigned to all books. Two books with the same title can have different
ISBNs if they are in different languages or have different bindings (hard cover or soft cover).
Editions of the same book have different ISBNs. However, since the library usually keeps
multiple copies of the same book, each copy of the book must be uniquely identified despite
having the same ISBN.
The proposed database system must be designed to keep track of the members, the books, the
catalogue, and the borrowing activity.
Coursework Details:
Level 1 Asia Pacific University of Information & Technology 112021

1. In this assignment, you are required to design, implement, and document a database system
for the UPA University Library Management System.
2. Create the following queries using Data Manipulation Language (DML) – student must be
able to explain the queries.
Student 1
1. For each member who has borrowed more than 2 books, list the member names and
the total number of books currently on loan to them. List the results in alphabetical
order of member names.
2. List the total number of book titles available in each category (i.e. open-stack,
yellow-tagged, etc.). List the results in descending order of total book titles.
3. List the book titles and publisher names of all books written by the authors named
‘Thomas Connolly’ or ‘Ramez Elmasri’.
Student 2
1. List the details of all current book reservations. The list should include member IDs
and names, book IDs and titles as well as the status of each book (i.e. it is
‘available’ or ‘loaned out’) reserved.
2. For each department, list the department names, the total number of books borrowed
by its students and the total number of books borrowed by its staff.
3. List all details of all members who have borrowed any books written by the author
named ‘C.J. Date’.
Student 3
1. List the titles of all ‘Computing’ books published by ‘Prentice Hall’ (i.e. publisher)
in the year 2019.
2. List in ascending order of department names, then in alphabetical order of member
names, the details of all ‘inactive’ members.
3. For each member with overdue books, list their IDs, names, the book IDs and titles
of overdue books as well as the fines incurred for each of those books.
Student 4
1. List the IDs and names of all members who have borrowed at least 5 books.

2. List in alphabetical order, the names of all publishers of books from either the
‘red-tagged’ or ‘yellow-tagged’ categories.
3. List the member IDs, member names, book title and the publisher names for all
members who had borrowed any books containing the word ‘Databases’ in its
title.
3. Deliverables - Minimum requirement of your documentation:
Part Component
1 a) Database and Database Management System
 Disadvantages of file-based system
 Advantages of Database and DBMS, functions of DBMS
 Relate your discussion to the case study
1 b) Business Rules & Normalization
 Generate a list of business rules
 Provide an example of UNF according to case study and perform
normalization up to 3NF clearly showing all the steps with
explanation
1 c) Entity Relationship Diagram
 Design the database using Chen’s or crow’s foot notation
 Draw the ERD with any suitable tools such as Visio
 All entities, attributes, relationship and constraints should be shown
2 d) Database Schema
 Finalized ERD using Chen’s or crow’s foot notation
 Implement the database design into the DBMS
 Generate the database diagram from the DBMS
2 e) SQL-Data Definition Language (DDL)
 Create all tables with suitable data types
 Insert 5-10 rows of data into each table
 Screen shot all tables with its data
 Screen shot all query statements
2 f) SQL-Data Manipulation Language (DML)
 Write SQL statements to answer question (2) above
 Screen shot all query statements together with its executed result
General Requirements:
In this assignment you are required to:

 Work a group of 3-4 members.
 Design and implement a solution to a business problem.
 Implement the solution using any Enterprise DBMS.

 Document the solution as set out in the assignment requirements.

 Submit the document online according to the date and time given below.
 Each group member is required to participate in all tasks / discussions together.
 Presentation schedules will be published at a later date through MS TEAMS.
Note: It is acceptable for discrete activities of this assignment to be undertaken by individual

group members. However, it is essential that all group members understand the presentation
in its entirety. At the end of the demonstration your group will be asked a series of questions to
explore your understanding and analysis of the given problem. Responses to these questions
such as “I don’t know because I didn't work on that part of the assignment” are not
acceptable and will result in a penalty for either the entire group or specific individual(s).
Part Assessment Criteria: Marks Online

Allocatio Submission Date
n
1 Group Component (40%) Week 8
a) Database and Database Management System 8% 23 January 2022
b) Business Rules & Normalization 12% 11.59pm
c) Entity Relationship Diagram 20%
2 Group Component (18%)

d) Database Schema 18% Week 12
Individual Component (42%) 20 February 2022
e) SQL-Data Definition Language (DDL) 12% 11.59pm
f) SQL-Data Manipulation Language (DML) 30%

IDB Assignment Question - APD1F-APU1F-2106

Uploaded by

Copyright:

Available Formats

IDB Assignment Question - APD1F-APU1F-2106

Uploaded by

Document Information

Original Description:

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

IDB Assignment Question - APD1F-APU1F-2106

Uploaded by

Copyright:

Available Formats

CT042-3-1-IDB Group Assignment Page 1 of 4

Module Learning Outcomes:

Level 1 Asia Pacific University of Information & Technology 112021

Level 1 Asia Pacific University of Information & Technology 112021

3. Deliverables - Minimum requirement of your documentation:

In this assignment you are required to:

Level 1 Asia Pacific University of Information & Technology 112021

 Document the solution as set out in the assignment requirements.

Note: It is acceptable for discrete activities of this assignment to be undertaken by individual

Part Assessment Criteria: Marks Online

2 Group Component (18%)

Level 1 Asia Pacific University of Information & Technology 112021

You might also like