COMP2400/6240 – Relational Databases

    Need Solution - Download from here

    This assignment will be marked out of 10. It will count for 10% of the nal grade. Below you will nd 3

    questions to reach this score. Marks are assigned for the process of nding a solution, not only for the result.

    Hence, include all essential ideas and steps that are necessary to derive a solution.


    • This assignment should be done by a group of two students, or individually if you prefer. COMP2400

    students can only be paired with other COMP2400 students. COMP6240 students can only be paired

    with other COMP6240 students. You can choose a dierent group partner (as for Assignment 2) if you


    • You must submit the following le on Wattle before the due date:

    { \CODE-u1234567-u7654321.pdf” (replace CODE with COMP2400 or COMP6240, and replace

    u1234567 and u7654321 with your UID and your group partner’s UID, respectively). Make sure

    you only upload the PDF le, not a Word or text le.

    • For each group, you must submit a single copy of your assignment on Wattle under either your UID

    or your partner’s UID.

    • Late submission is not granted under any circumstance. You will be marked on whatever you have

    submitted at the time of the deadline. Please take careful note of deadlines and adhere to them. Of course,

    if you nd yourself in a situation beyond your control that you believe signicantly eects an assessment,

    you should follow the ANU’s special consideration process (


    • Plagiarism will attract academic penalties in accordance with the ANU guidelines. A student in this

    course is expected to be able to explain and defend any submitted assessment item. The course convener

    can conduct or initiate an additional interview about any submitted assessment item for any student. If

    there is a signicant discrepancy between the two forms of assessment, it will be automatically treated as

    a case of suspected academic misconduct.

    Question 1 4 Marks

    Consider the following database schema for a university library.

    BOOK = fBookID, Titleg

    PK: fBookIDg

    AUTHOR = fBookID, AuthorID, Nameg

    PK: fBookID, AuthorIDg

    FK: [BookID] BOOK[BookID]

    STUDENT = fStudentID, Name, Address, Phoneg

    PK: fStudentIDg

    LOAN = fBookID, StudentID, StartDate, DueDateg

    PK: fBookID, StudentIDg

    FK: [BookID] BOOK[BookID], [StudentID] STUDENT[StudentID]


    Answer the following question using relational algebra expressions. You are encouraged to use relational algebra

    expressions to represent intermediate results if needed.

    1.1 Find all students who have borrowed at least one book. List their StudentIDs and names. (0.5 Mark)

    1.2 Find all the students who have borrowed any book(s) written by George Martin. List their StudentIDs and

    phone numbers. (0.5 Mark)

    1.3 Find all the authors whose book(s) has/have never been borrowed by any student. List their AuthorIDs

    and names. (0.5 Mark)

    1.4 Find all the students who have only borrowed the book \the Fellowship of the Ring”. List their StudentIDs.

    (0.5 Mark)

    1.5 Find all pairs of authors who have co-authored at least one book (i.e., both of them are authors of the same

    book). List all pairs of their AuthorIDs and the title of the book that they co-authored. (1 Mark)

    1.6 Find all students who have borrowed at most 1 book. List their StudentIDs and names. (1 Mark)

    Question 2 6 Marks

    Consider the following database of ight booking system.

    CUSTOMER = fCustomerID, CustomerName, Phoneg

    PK: fCustomerIDg

    EMPLOYEE = fEmployeeID, EmployeeNameg

    PK: fEmployeeIDg

    FLIGHT = fFlightNo, Date, Origin, Destinationg

    PK: fFlightNo, Dateg

    BOOKING = fCustomerID, EmployeeID, FlightNo, Date, Priceg

    PK: fCustomerID, EmployeeID, FlightNo, Dateg

    FK: [CustomerID] CUSTOMER[CustomerID], [EmployeeID] EMPLOYEE[EmployeeID], [FlightNo, Date]

    FLIGHT[FlightNo, Date]


    CustomerID CustomerName Phone

    101 Peter 811-649-322

    202 John 822-531-989

    303 Miranda 833-674-282


    FlightNo Date Origin Destination

    A01 30-09-2017 Canberra Sydney

    A02 30-09-2017 Sydney Perth

    A03 29-09-2017 Canberra Melbourne

    A04 29-09-2017 Melbourne Sydney


    EmployeeID EmployeeName

    11 Sylvain

    22 Jens


    CustomerID EmployeeID FlightNo Date Price

    101 11 A04 29-09-2017 500

    101 22 A01 30-09-2017 200

    202 22 A03 29-09-2017 400

    303 22 A01 30-09-2017 200


    2.1 Evaluate the relational algebra expressions over the given sample relations, and show each of your answer

    as a table that includes the attribute names and tuples. For example, such a table can be shown as follows:

    CustomerID EmployeeID FlightNo Date Price

    101 11 A04 29-09-2017 500

    101 22 A01 30-09-2017 200

    202 22 A03 29-09-2017 400

    303 22 A01 30-09-2017 200

    (a) BOOKING ./ CUSTOMER ./ FLIGHT (0.5 Mark)

    (b) BOOKING ./(BOOKING:FlightNo=FLIGHT:F lightNo)_(BOOKING:Date=FLIGHT:Date) FLIGHT (0.5


    (c) (BOOKING ./ (CustomerID0;EmployeeID0;F lightNo0;Date;P rice0)(BOOKING)) ./ FLIGHT (0.5 Mark)


    (0.5 Mark)

    2.2 Optimize the following two relational algebra queries (Your marks will depend on how well you present the

    key ideas of query optimization in your answer). In addition to this, draw the query trees that correspond to

    queries before and after your optimisation.

    (a) BOOKING:CustomerID;Origin;P rice(BOOKING:FlightNo=FLIGHT:F lightNo


    (b) R1:CustomerID(R1:CustomerID=R2:CustomerID((R1:Date6=R2:Date)^(R1:Origin=0Canberra0)^(R2:Origin=0Canberra0)


    (2 Mark)



    By |2017-10-07T14:57:51+00:00October 7th, 2017|Categories: database|0 Comments

    Leave A Comment