数据库 SQL 代写 ISYS1055/1057 Database Concepts Assignment 2

School of Science/ Computer Science and IT

ISYS1055/1057 Database Concepts Assignment 2

Semester 2, 2018

1 Introduction

This is an individual assignment, to be submitted electronically using the Blackboard facility. A submission link will be enabled on blackboard closer to the submission date. It is due 23:59 Sunday 07 October 2018, and contributes 15% towards the aggregate of 100 marks.

The objective of this assignment is to reinforce what you have learned in the lectures and tute/ lab sessions. Specifically, it covers the advanced concepts in the relational database model, using SQL for querying a relational database and analyse different database models for different applications.

1.1 Plagiarism

All code or other material that is not original must be fully credited. That is, any material that is copied or derived from another source must be clearly identified as such and the original author must be identified. Sometimes students assist each other with an assignment, but end up working together too closely, so that the students’ separate solutions have significant parts in common; unless the solutions were developed independently, they are regarded as plagiarised.

Plagiarism is a very serious offence. Any submissions determined to be a result of plagiarism will be deemed as an academic misconduct and harsh penalties apply. It is also an offence for students to allow their work to be plagiarised by another student. You should familiarize yourself with the university website for Academic Integrity Policy, Procedures and Guidelines. (https://www.rmit.edu.au/students/student-essentials/rights-and-responsibilities/academic-integrity) All work is to be done individually and plagiarism of any form will be dealt with according to the RMIT plagiarism policy.

1.2 WhattoSubmit,When,andHow 1.2.1 When

This assignment is due at 23:59 Sunday 07 October 2018.

1.2.2 What

You should submit one PDF document with all answers together. You may use SQLiteStudio to work on your assignment. You must not submit result sets from SQL queries, only the SQL queries are to be submitted. You may use Word or any other word processor to compile your submission, by collating everything into one document. At the end, convert it into PDF format. Do not submit Word files. if that option is not available on your system there are free pdf converters online you can utilise. e.g. http://convertonlinefree.com/

Document: Assignment 2 V1.0.docx Author: Santha Sumanasekara Save Date: 16/09/2018 Computer Science and Information Technology Page 1 of 7

1.2.3 How

You are required to submit your solution electronically using the Canvas facility. A submission link will be enabled on Canvas closer to the submission date.

1.2.4 Penalties for late submissions

Late submissions of assignments will be penalised as follows. For 1 to 5 days late, a penalty of 10% (i.e. 10% out of total marks, not 10% out of your marks) per day. For assignments more than 5 days late, 100% penalty applies. If you submit between 00:00 08 Oct and 23:59 08 Oct, your penalty is 10%. If you submit between 00:00 09 Oct and 23:59 09 Oct, 20% penalty, and so on.

1.2.5 Special Consideration

If unexpected circumstances affect your ability to complete the assignment you can apply for special consideration. If you seek a short extension, you can directly contact the lecturer. For longer extensions, you must follow instructions provided at http://www1.rmit.edu.au/students/specialconsideration

1.3 PreparationTasks

Part B of the assignment is based on Library database. A copy of this sample database in SQLite format (Library.db) can be downloaded from the Assignment 2 folder on Canvas. Download this file and add it to your SQLite Studio setup. Check it for all tables before you attempt SQL questions.

Computer Science and Information Technology

Document: Assignment 2 V1.0.docx Author: Santha Sumanasekara Save Date: 16/09/2018 Page 2 of 7

2 Part A: Relational Database Design (30 Marks)

Consider the following E-R diagram, which models an online bookstore. As a part of the Assignment 1 activities, we have mapped entities and relationships in this E-R model into a relational database schema.

5. Write down the final relational database schema.

Important: No marks are awarded to the final schema in Part 5 if you do not show the workings of decompositions (in Part 3) and combining relations (in Part 4). Indicate the primary key (underlined) and foreign key(s) (with an asterisk*) in each relation.

3 Part B: SQL (40 Marks)

LibraryDB is a database system that keeps track of information concerning the books and their circulation in an imaginary library.

Disclaimer: The data that populates the database are artificially constructed and by no means correspond to actual real-world data.

The schema for the LibraryDB database is given below.

OwnedBy

As a result of incorrect application of 7-step mapping process, it was mapped into following relations.

Author (Email, Name, Address, Telephone1, Telephone2, Telephone3) Publisher (Name, Address, URL, ABN)
WrittenBy (Email*, ISBN*, Title*)
Book (ISBN, Title, Edition, Year, ListPrice, PublisherName*) Warehouse (Code, Address)

StockedAt (ISBN*, Code*, StockQty)
ShoppingCart (CartID, TimeStamp, ISBN, BuyPrice, Qty) Customer (Email, Name, Address, CartID*)

1. For each of these relations, write down all non-trivial functional dependencies. If there are no functional dependencies among attributes, you must state so.
2. Write down the highest normal form each of these relations are in. For each of these relations, state the reasons why it doesn’t meet the next normal form requirements. This is not required if the relation is in 3NF.

The primary keys are underlined. The foreign keys are denoted by asterisks (*).

Description of the schema

• person — keeps track of the people who borrow books from the library. The attributes contain personal and contact information.

• author — keeps track of personal information about authors.
• publisher — keeps track of the publisher information. To make simple, most of the

attributes have been truncated in the sample database.
• subject — this relation keeps information about the subjects on which the library collection

have books (such as Mathematics, Database, etc)
• book — contains information about the books that are available in the library. Every book

can have one or more physical copies in the collection. Each book can have one or more

authors and it is published by one or more publishers.
• book_copy — keeps track of the physical copies of the books in the library collection.
• borrow — keeps track of the check-ins and check-outs of the books. Every transaction is

done by one person, however may involve with one or more book copies. If there is no

return date, it means the book has been checked out but not returned.
• written_by — associates books with authors. A book may be associated with several

authors and an author may be associated with several books. There is also an attribute

‘role’ that specifies the role of the author for the book (author/ editor/ translator/ etc).
• published_by — associates publishers with books. There is an attribute ‘role’ here too. • borrow_copy — associates physical copies of books with a transaction. Members are

allowed to borrow several books in a single transaction.

A conceptual data model (shown as an entity-relationship diagram) which represents these data is given below.

3. If they are not in 3NF, decompose them into 3NF relations.
4. Where possible, combine the relations resulting from Part 3.

Computer Science and Information Technology

Document: Assignment 2 V1.0.docx Author: Santha Sumanasekara Save Date: 16/09/2018 Page 3 of 7

Computer Science and Information Technology

Document: Assignment 2 V1.0.docx Author: Santha Sumanasekara Save Date: 16/09/2018 Page 4 of 7

If you wish to do this part of the assignment from home, you can install SQLite Studio. The instructions for installing, configuring and using SQLite Studio is provided in the Week 3 Labsheet. Also included is the pre-built Library database in SQLite format (Library.db) at the same location.

Write SQL queries for the following tasks.

1. Display the first name and last name of the authors who wrote books on the subject “DataBases”.
   1. Write your query using a sub query.
   2. Write your query using JOINs.
2. Who translated the book “American Electrician’s Handbook”? Display the first name, middle

   names, and last name of the translator. Each authors’ role in the writing of the book is

   described in “role” attribute in written_by table.

3. Display the titles of books that haven’t been borrowed.
4. A borrower wants to borrow the book titled “PRINCIPLES AND PRACTICE OF DATABASE

   SYSTEMS”, but all of its copies are already borrowed by others. Write two queries to display other recommended titles using the following methods.

   1. Using partial matching of the book title — note that the borrower is interested in a “DATABASE” book.
   2. By searching of other books written by the same author (i.e. the author of “PRINCIPLES AND PRACTICE OF DATABASE SYSTEMS”

      Document: Assignment 2 V1.0.docx Author: Santha Sumanasekara Save Date: 16/09/2018

Computer Science and Information Technology Page 5 of 7

10.

11.

12.

According to this database schema, it is assumed that all booked borrowed in one transaction are to be returned together. However, this is an unreasonable assumption and quite contrary to the common practice across all forms of libraries. You can return books (borrowed together) separately.

Provide the required changes to the database schema to accommodate this requirement. Your answer should include a portion of the ER model where the changes are applied and the updated relational database schema. If required, you may add new relations and attributes and also new foreign keys.

Libraries allow customers to place holds onto books that are not currently available. When a customer places a hold for a book, it is added into a holds queue. When the book becomes available, it can be borrowed by the customer who places the hold first (i.e. the customer at the front of the queue). This database schema cannot keep information on holding of books. Extend this schema to accommodate this requirement.

Your answer should include a portion of the ER model where the changes are applied and the updated relational database schema. If required, you may add new relations and attributes and also new foreign keys.

The library allows customers to extend their loans before the due date.

1. Can this database schema handle loan extensions? If so, how are they handled? If

   not, what changes required to the database schema?

2. If there is an additional condition that a customer can extend their loan only two

   times, can this database schema handle loan extensions? If so, how are they

   handled? If not, what changes required to the database schema?

Your answers should include a portion of the ER model where the changes are applied and the updated relational database schema. If required, you may add new relations and attributes and also new foreign keys.

5. Display the list of publishers who have published books on the subject “DataBases”. Your query should display publisher’s full name, along with “DataBases” book titles they published.
6. Display the titles of books that never borrowed.
   1. Write your query using OUTER JOINs.
   2. Write the query again without using OUTER JOINs.
7. Display full names of publishers with whom the author Alfred Aho published his book(s). Your query must use EXISTS clause.
8. Display the first name and last name of authors who wrote more than 3 books. Along with each name, display the number of books as well.
9. Display the title of the book which has most physical copies. If there are more than one book with the largest number of copies, show them all. Your query should show the number of copies along with the title.

Provide detailed answers to the following questions.

Computer Science and Information Technology

Document: Assignment 2 V1.0.docx Author: Santha Sumanasekara Save Date: 16/09/2018 Page 6 of 7

4 Part C: Research Question (30 Marks)

Let’s assume that a decision was made to rebuild the public transport timetable used to power ptv.vic.gov.au website and its mobile apps, mainly due to recent increase in popularity of the PTV app on iPhone and Android mobile phones. You are assigned the task of producing an analysis of backend database server options that can cater the volume of data it is currently dealing with as well as projected growth over the next 5 years.

The current website (ptv.vic.gov.au) and smartphone apps cater for millions of users and on average one million timetabling queries are processed per day. It is expected a steady growth of 10% increase annually.

You have two choices: (1) traditional relational database systems (such as Oracle and SQL Server); or, (2) no-SQL database systems (such as MongoDB). Write a report identifying the advantages and disadvantages of the both approaches and a conclusion making your recommendations. Your report may include case studies for both paradigms and draw conclusions based on their findings.

Approximate report length should be around 1000 – 1500 words. You must be careful about quoting texts extracted from other sources. You can paraphrase them with proper referencing. Before you start your report, please refer RMIT Library Referencing Guide, available at: http://www1.rmit.edu.au/browse;ID=8rwjnkcmfoeez

Document: Assignment 2 V1.0.docx Author: Santha Sumanasekara Save Date: 16/09/2018 Computer Science and Information Technology Page 7 of 7