Reference no: EM132642555

COMP2350 Database Systems - Macquarie University

Database Design and Manipulation

The background knowledge for the assignments is given in the textbook(s), lectures, any other components of the unit, in the prerequisite units ISYS114 or COMP1350, and in the readings provided on ilearn. However, some parts of the assignments may not be answered without prior independent research and/or searching for other sources of information.

This assignment concerns database Database Design and Manipulation. It will be marked out of 100 and will contribute 10% towards your final grade. It consists of normalising some given relation schemas to an appropriate level, implementing those resultant relations employing MySQL, and carrying out some simple data manipulation using those relations.

Problem Context

The context of this Assignment is the same as for Assignment 1, namely the Sydney Library
(SL). This has been reproduced as is in the Appendix for your convenience.

We pretend that your firm had an agreement with SL to design and implement the Database, and the job fell on you. You in turn assigned that task to an intern you are mentoring. The intern has gone through the specification and designed some relational schemas for this purpose. You had a quick look at them, and one of them particularly stood out since it had quite a number of attributes that you suspected should best belong to different relations. The suspect relation schema in question is:

Abnormal_Rel( BookID, BookTitle, Author, Publisher, PubYear, LibBranchID, BranchSuburb, BookStatus, BookPrice, MemberID, MemberName, MemberAddress, MemberExpDate, BookIssueID, DateBorrowed, DateReturned, ReturnDueDate )
Upon discussion with the intern you gathered that the BookID identifies a specific physical volume of book that is housed at a definite branch of SL identified by LibBranchID. A book can be either available or on loan, indicated by the BookStatus value. Whenever a book is issued (loaned) to a member, a new ID is assigned to the transaction related to that book (BookIssueID). For instance, if a member issues borrows five books in one go, five different BookIssueIDs will be generated for those five books.
You took upon yourself to explain to the intern the issue at hand, and how the issue should be addressed. Complete the following tasks in that context.

Task Specifications
You will submit two files on iLearn:
1. Assignment2.pdf. You will first put all your answers to the tasks below (including copying and pasting any SQL code) into a file Assignment2.doc (carefully following the instructions provided), then generate a .pdf file called Assignment2.pdf.
2. Assignment2Code.sql. This is a text file. One way to produce it is first open a new text file called Assignment2Code.txt employing a text editor such as Notepad, WordPad or TextEdit. You will then copy and paste the required SQL codes into it as text. When completed, save it, exit and replace the suffix .txt by the suffix .sql. (There are more efficient ways doing it - this suggestion is provided in case some student does not already know how to do it.)

Task 1
Explain in a short paragraph what non-trivial Functional Dependencies (FDs) are. Identify the non-trivial FDs on the relation Abnormal_Rel. Then identify the Candidate key(s) of Abnormal_Rel.

Task 2
Explain the three forms of Anomalies in three short paragraphs. Determine for each anomaly if the relation Abnormal_Rel is susceptible to that anomaly. Support your determination with a small example.

Task 3
Determine the highest normal form that the relation Abnormal_Rel is in. Then:
1. Normalize/decompose it until you get relations that are in 3NF. Use appropriate illustration to aid the understanding of your work.
2. Check if the resultant relations are in BCNF. If not, decompose them as necessary until you get all of them in BCNF.

Task 4
Now you have a set of relation(s) in BCNF.
1. Create an appropriate table for each such relation (in BCNF), keeping the key constraints in mind. Copy and paste into your .doc document the SQL code you used for this purpose. Also paste into your Assignment2Code text file the same.
2. Insert five rows of (made-up) data into each table. Make sure that the data you enter in these tables should be sufficient to return at least one row for each query in Task
5. For instance, SL should hold at least one copy of the book Anna Karenina by Leo Tolstoy. Copy and paste into your .doc document the SQL code you used for this purpose. Also paste into your Assignment2Code text file the same.
3. Display the content of each table using a SECECT * query. Copy and paste into your
.doc document the result that was displayed.

Task 5
Write SQL queries to answer the following four questions (Q1-Q4). With respect to each,
• Copy and paste into your .doc document the SQL query you used for this purpose. Also paste into your Assignment2Code text file the same.
• Display the result of running this query. Copy and paste into your .doc document the result that was displayed.

[Query 1] List the name of members who have ever borrowed the book with the title
Anna Karenina.

[Query 2] List each member (ID and name) who has books currently overdue against them, together with the ID and title of those books.

[Query 3] List the five most popular book(s) owned by SL. (Interpret this task in a sensible way. If SL has 3 copies of Anna Karenina, we are talking about popularity of the book Anna Karenina, not popularity of its individual copies. Different students are still likely to interpret it differently. Clarify how you "measure" popularity.)

[Query 4] How many books were lent out from SL since Covid-19 related lockdown started (assume it to be March 01, 2020), but have not been returned yet? (In this case, a book means a volume - if two copies of Anna Karenina satisfy this criterion, they should be counted as two books.)

Note: Need only Task 4 and 5.