Assignment attached as file.
DBC_Assessment_1 School of Science — ISYS3412 Practical Database Concepts Assessment 2: Database Design Project Assessment Type: PDF Word limit: N/A (see instructions) Due date: 11:59PM (AEST), Sunday 18 October 40% of your overall grade 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; Page 1 of 10 Course Learning Outcomes This assessment is relevant to the following Course Learning Outcomes: CLO 2: explain the main concepts for data modelling and characteristics of database systems; CLO 3: develop a sound database design using conceptual modelling mechanisms such as entity-relationship diagrams; CLO 4: develop a database based on a sound database design; CLO 5: Apply SQL as a programming language to define database schemas, update database contents and to extract data from databases for specific users’ information needs. Page 2 of 10 Part A Relational Database Design (30 points) 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. Important: No marks are awarded to the final schema in Part 7 if you do not show the workings of decompositions (in Part 3) and combining relations (in Part 5). Indicate the primary key (underlined) and foreign key(s) (with an asterisk*) in each relation. Part B SQL (30 points) 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) 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 ER diagram) representing the data is given below. A copy of the pre-built Library database in SQLite format (Library.db) is available on Canvas under Modules > Sample Databases and Tools. Note that your queries must be able to handle any variations in capitalization that could be introduced in the future (even if the current data is consistent). Submit a text file containing the queries called part_b.txt. Your queries must be properly formatted - capitalization for syntactic keywords, new lines, indentation. e.g. SELECT COUNT(bookdescid) FROM book WHERE subjectid IN (SELECT subjectid FROM subject WHERE subjecttype = 'DataBases'); 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 translated) 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". (List only the full titles of books – including the subtitle). 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