Home>Computer Science homework help

CSYS3602
2020/2021- Semester 1
Advanced Database Management
THE COUNCIL OF COMMUNITY COLLEGES OF JAMAICA
BACHELOR’S DEGREE COURSEWORK
SEMESTER I – OCTOBER 2020
PROGRAMME: MANAGEMENT INFORMATION SYSTEMS
COURSE NAME: ADVANCED DATABASE MANAGEMENT
CODE: CSYS3602
YEAR GROUP: FOUR
UNIT: ONE/ SIX/ NINE
DUE DATE: TBD
ASSESSMENT TYPE: COURSEWORK (30%)
INSTRUCTION: Answer all questions
CSYS3602
2020/2021- Semester 1
Advanced Database Management
Coral Shores Community College is a small college with two campuses: Shoreline Campus and
Shorecove Campus. Coral Shore Community College’s Library has approximately 3,000
members, 10,000 titles, and 30, 500 volumes. About 10% of the volumes are out on loan at any
one time. The librarians ensure that the books that members wish to borrow are available when
they want them. The librarians must also know the number of copies of each book that are in the
library or out on loan at any given time. A catalogue of books is available online which 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 reference librarians wish to be
able to access this description when members request information about a book. The library staff
includes one (1) Head Librarian, two (2) Assistant Librarians, two (2) Reference Librarians,
three (3) Check-Out Staff, and four (4) Library Assistants.
Books may be checked out for seven (7) working days. Members are only allowed to have three
(3) books out at a time. Members usually return books within three to four weeks. Most members
know that they have a three (3) working days grace period before a notice is sent to them, so they
try to return books before the grace period ends. About 5% of members have to be sent
reminders to return books. Most overdue books are returned within thirty (30) working days of
the due date. Approximately 5% of the overdue books are either kept or never returned.
Members who borrow books at least ten times during the year are classified as the most active
members of the library. The top 1% of membership does 15% of the borrowing, and the top 10%
of the membership does 40% of the borrowing. About 20% of the members are totally inactive in
that they are members who never borrow.
To become a member of the library, applicants fill out a form including the following:
• Student ID# / Staff ID#
• Campus
• Mailing Address
• Phone Number(s)
The librarians issue a numbered, machine- readable card with the member’s photo on it. This
card is good for four (4) years. A month before a card expires, a notice is sent to a member for
CSYS3602
2020/2021- Semester 1
Advanced Database Management
renewal via mail. Lecturers at the college are considered automatic members. When a new
academic member joins the college, his or her information is pulled from the employee records.
Library cards are mailed to lecturer’s address while students collect theirs form the library when
they are ready. Processing usually takes one (1) week. Lecturers can check out books for threemonth intervals and have a two-week grace period. Renewal notices to lecturers are sent to their
mailing address. The library does not lend some books, such as reference books, rare books, and
maps. The librarians must differentiate between books that can be lent and those that cannot. In
addition to this, the librarians have a list of books they are interested in acquiring but cannot
obtain, such as rare or out-of-print books and books that were lost or destroyed and have not
been replaced. The librarians must have a system that keeps track of books that cannot be lent as
well as books that they are interested in acquiring. 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 (hardcover or softcover). Editions of the same book have different ISBNs.
You have been asked to design a proposed database system which will be a web-based version of
what the college has now. Each user must be required to log in or register online to use this
database.
Your database must keep track of the members, sending out membership renewal reminders
via email, the books, the catalogue, and the borrowing activity. Members must be able to
reserve books online. Your database should have records for no less than five (5) members
and at least one member should have a renewal reminder sent to them via e-mail.
CSYS3602
2020/2021- Semester 1
Advanced Database Management
Requirements:
1. Based on the above-mentioned information, draw an EER model to show the conceptual
design of the relational database. Please indicate any assumptions that you have made.
2. Normalize at least three (3) tables to at least 3NF and up to 5NF where possible.
3. Using XML and SQL, design an appropriate web-based version of the company’s processes.
4. Produce a report of no more than 20 pages. This report should contain the following:
a. Technical Requirements (what will be required by the company to use this web-based
version)
b. The EER model diagram and related schemas derived from the EER model.
c. SQL codes for:
i. At least two (2) stored procedures
ii. At least two (2) Functions
iii. At least two (2) Triggers
iv. At least two (2) Cursors
5. The table structures of at least five (5) tables in SQL.
6. Screenshots of:
a. The registration page
b. A successful registration confirmation page
c. The log in screen
d. The reservation page
e. A successful reservation confirmation page
7. XML codes for:
i. XML schemas