Reference no: EM132660007

ISYS3412 Practical Database Concepts - RMIT University

Assessment: Database Design Project

Overview
The objective of this assignment is to reinforce what you have learnt in the lectures and tute/lab sessions throughout the course. Specifically, it covers the advanced concepts in relational database design, using SQL for querying a relational database and building a simple application that connects to a database backend running a simple relational schema.

Assessment Criteria
This assessment will measure your ability to:
• Use normalisation process to evaluate the schema and make sure that all of the relations are at least 3NF.
• Create tables on SQLite Studio and populate them with data available from the sources outlined above.
• Write SQL statements required for CRUD (create, read, update and delete) operations on the database you built;
• Appreciate a good database design;
• Apply good database design guidelines, such as normalisation, to build a well-designed database schema;
• Write efficient SQL statements for retrieving data for specific user requirements;

Learning Outcome 1: explain the main concepts for data modelling and characteristics of database systems;
Learning Outcome 2: develop a sound database design using conceptual modelling mechanisms such as entity-relationship diagrams;
Learning Outcome 3: develop a database based on a sound database design;
Learning Outcome 4: Apply SQL as a programming language to define database schemas, update database contents and to extract data from databases for specific users' information needs.

Part A Relational Database Design
After analyzing the requirements of the University Enrollment Management System outlined in Assignment 1, you have come up with the following ER UML model:

The clients have indicated that the design needs to manage details about lab assistants that are also involved in courses, but that there are no other types of students. This model was incorrectly mapped into the following set of relations:
Staff(staffID, name) Qualification(name, year) Holds(staffID*, name*, year*) Tutor(staffID*, name)
Lecturer(staffID*, name, year, semester) Tutors(year*, semester*, staffID*) CourseOffering(year, semester)
Course(courseID, name, creditPoints, fee, prerequisuites*) Offers(courseID*, year*, semester*)
Student(studentID, name, DOB, gender) Enrolment(grade, status) Local(StudentID*, TFN, hecAmount) International(StudentID, visa)

1. Identify any missing relations and their attributes, denoting their primary and foreign keys.
2. For the complete set of relations (including the newly added relations), write down all functional dependencies. If there are no functional dependencies among attributes, you must state so. Do not write down trivial functional dependencies, such as staffID ? staffID.
3. Write down the highest normal form that each of these relations is 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. Identify any redundant relations (explaining why they are redundant).
4. If they are not in 3NF, decompose them into 3NF relations.
Write down the full database schema at the end of this step, eliminating decomposed relations and replacing them with newly created relations.
5. Where possible, combine the relations resulting from Part 3.
6. Write down the full database schema at the end of this step, eliminating combined relations and replacing them with newly created relations. Remove any relations that you have identified as redundant or unnecessary.
7. Write down the final relational database schema.

Part B SQL

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.

borrow(transactionID, personID*, borrowdate, duedate, returndate)
author(authorID, firstname, middlename, lastname)
book_copy(bookID, bookdescID*)
book(bookdescID, title, subtitle, edition, voltitle, volnumber,
language, place, year, isbn, dewey, subjectID*)
borrow_copy(transactionID*, bookID*)
person(personID, firstname, middlename, lastname, address, city,
postcode, phonenumber, emailaddress, studentno, idcardno)
publisher(publisherID, publisherfullname)
written_by(bookdescID*, authorID*, role)
published_by(bookdescID*, publisherID*, role)
subject(subjectID, subjecttype)

Write SQL queries for the following tasks:

1. Display the firstname and lastname of authors that have written books (not translated) where one of the publishers (not editors) is 'Prentice-hall' (include books that are co-published with other publishers).
a. Write your query using an EXISTS sub query.
b. Write your query using a JOIN

2. List the firstname and lastname of every author who has ever translated a book. Each authors' role in writing of the book is described in "role" attribute in the written_by table.

3. Display the title of books that have no copies in the library.
a. Write the query using a IN/NOT IN
b. Write the query again using an OUTER JOIN
c. Write the query again using SET operators

4. Books sometimes have more than one publisher. Display the names of publishers that have worked together on the same book in any capacity (as publisher or editor) identifying which publisher they worked with. Order the results in alphabetical order of the first publisher's name, then the second publisher's name. There must be no rows that duplicate the same information. eg the fact that Prentice-hall and Microsoft Press published together should not appear more than once (in any combination).

5. List the firstname and lastname of authors that have written (not edited) books with "engineering" occurring anywhere in the title.

6. List the title of books that have only ever been borrowed by students. ie they have the ‘student' classification in the person table.

7. The library wants to make auto recommendations based on members past borrowing history. As a test case, display the list of books that are on the same subjects as books previously borrowed by member "John Johnston".

8. Display the firstname and lastname of the library member(s) that has/have borrowed the most number of books. If there is more than one member, show them all. Your query should show the number of books along with the member's name.

Provide detailed answers to the following question.
9. The current design only allows each book to have one subject which is not very realistic. 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.

10. The current design has a lot of null values when modelling relationships and has no use of inheritance to model commonality.
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.

Part C Relational Database Design
Consider the following schema for an auction database site:
Auction(buyerUsername, buyerAddress, bidNo, bidAmount, bidDate, bidTime, itemID, itemStartDate, itemEndDate, itemCategoryTitle, itemSubCategoryTitle, sellerUsername, sellerAddress, sellerBusinessNo, sellerBusinessAddress)

1. Identify the functional dependencies of the Schema.

2. Decompose the relation into 3NF. Where possible combine relations, but avoid introducing NULL values wherever possible. Identify the final relational database schema identifying primary keys and foreign keys. Avoid introducing unnecessary attributes and keys.

3. Create an ER UML diagram representing your decomposed design.

4. The organization wants the successful buyer (who wins the Auction) to be able to leave feedback (a rating and a comment) on the item that they purchased. Show how this can be supported by updating your ER UML diagram. Identify any limitations of your design.

5. Produce a set of SQL statements to create your tables in SQLite and populate with the above data. (do not include the changes outlined in step 4).

6. Write a query that will join your tables together to reproduce the information in the above table (in the same order).

7. Write a query to identify the username of the winner of each auction item and the amount of the winning bid (any auction item that is not bid on should have 'NONE' listed as the winner's username and 0 of the amount of the winning bid). Your query result must be based on the date and time that the query is executed.

8. New bids must be greater than the current highest bid, and bidno must be ONE more than the last successful bid, and the bid date cannot be after the items end date. Use trigger(s) to enforce these rules.

Attachment:- Practical Database Concepts.rar