CSCI235 Database System Assignment 1, 2026 | University of Wollongong Australia

CSCI235 Assignment 1 (10% of total marks)

Scope

The tasks of this assignment cover functional dependency and indexing. The assignment covers the topics discussed in lecture 1, 2, and 3.

Assessment criteria

Marks will be awarded for:

• Correct,
• Comprehensive, and
• Appropriate

application of the materials covered in this subject.

Assignment Specification:

Task 1 (3.0 marks) Analysis of relational schemas and normalization

A book is described by a catalog number, a book title, author’s first name, author’s last name, a publisher name, the year the book was published, and a price of the book. An author may write many books and each book may be written by one or more authors. The information is stored in a relational table book as follow:

BOOK (CatalogNum, BookTitle, AuthorFName, AuthorLName, PublisherName, YearOfPublication, Price)

The following dependencies exist in the relational table BOOK:

• 𝐶𝑎𝑡𝑎𝑙𝑜𝑔𝑁𝑢𝑚 → 𝐵𝑜𝑜𝑘𝑇𝑖𝑡𝑙𝑒, 𝑃𝑢𝑏𝑙𝑖𝑠ℎ𝑒𝑟𝑁𝑎𝑚𝑒, 𝑌𝑒𝑎𝑟𝑂𝑓𝑃𝑢𝑏𝑙𝑖𝑐𝑎𝑡𝑖𝑜𝑛
• 𝐵𝑜𝑜𝑘𝑇𝑖𝑡𝑙𝑒, 𝑃𝑢𝑏𝑙𝑖𝑠ℎ𝑒𝑟𝑁𝑎𝑚𝑒, 𝑌𝑒𝑎𝑟𝑂𝑓𝑃𝑢𝑏𝑙𝑖𝑐𝑎𝑡𝑖𝑜𝑛 → 𝑃𝑟𝑖𝑐𝑒
• 𝐴𝑢𝑡ℎ𝑜𝑟𝐹𝑖𝑟𝑠𝑡𝑁𝑎𝑚𝑒, 𝐴𝑢𝑡ℎ𝑜𝑟𝐿𝑎𝑠𝑡𝑁𝑎𝑚𝑒, 𝐵𝑜𝑜𝑘𝑇𝑖𝑡𝑙𝑒 → 𝐶𝑎𝑡𝑎𝑙𝑜𝑔𝑁𝑢𝑚

i. Find all the minimal super key of the relational table BOOK. List the derivations of all minimal keys.

ii. Identify the highest normal form of the relational table BOOK. List the justifications for each highest normal form found.

iii. Decompose the relational table BOOK into minimal number of relational tables in BCNF. List all relational tables obtained from the decompositions.

Deliverables

A file solution1.pdf with the outcomes of the steps (i), (ii), and (iii) listed above. Note, that “educated guesses” of the solutions score no marks. You must provide the complete justifications of your answers.

Submission of a file with a different name and/or different extension and/or different type scores no marks!

Task 2 (4.0 marks) Indexing

Using the relational table LINEITEM of the sample database TPCHR, for each one of the queries listed below:

i. Find all the discount (l_discount) of all the items that are shipped (l_shipdate) most recently. Hint. Most recently mean the latest shipment date.

ii. Find the total number of items shipped by air (l_shipmode) in 1998 (l_shipdate).

iii. Find the order number (l_orderkey) and item number (l_linenumber) that have the highest discount (l_discount).

iv. Find the total number of item per line status (l_linestatus). List the line status and the total items per line status.

v. Find the order key (l_orderkey), line item number (l_linenumber), line status (l_linestatus), shipment date (l_shipdate) and shipment mode (l_shipmode) of all orders with the order number (l_orderkey) 1795718, 1799046, and 1794626.

a) Construct an SQL statement that produces the required output specified in the statement. (1.5 marks)

b) Find the best possible indexing of a relational table LINEITEM. The best possible indexing means that a database system will compute a query with an index proposed by you using the smallest number of read block operations. Note that you can create only one index per query, and there is no need to find indexes that speed up the processing of more than one query. Use the explain plan and show plan statements to justify your solutions. (2.5 marks) 

Deliverables

A file solution3.pdf with CREATE INDEX statements that improve the performance of the queries listed (i, ii, iii, iv, and v above) and the execution plan generated.

Please remember that you must consider each one of the queries as an individual case! Please remember that all relational tables are large enough to make full table scans more time consuming that accessing the tables through an index! It means that any solution in which an index is not used for query processing is incorrect.

Task 3 (3.0 marks) Indexing

Using the relational table ORDERS of the sample database TPCHR and the following index created, construct an appropriate SQL statement and satisfies the following requirement:

create index A1Task3Idx on orders(o_orderdate, o_clerk, o_totalprice);

Find SELECT statements that will use the index in the following ways:

a. Execution of the first SELECT statement must traverse the index vertically and it must not access a relational table ORDERS. (0.6 mark)

b. Execution of the second SELECT statement must first traverse the index vertically followed by horizontal scan at the leaf level. The retrieval operation must not access a relational table ORDERS. (0.6 mark)

c. Execution of the third SELECT statement must traverse the leaf level of the index horizontally and it must not access the relational table ORDERS. (0.6 mark)

d. Execution of the fourth SELECT statement must traverse the index vertically and it must access a relational table ORDERS.  (0.6 mark)

e. Execution of the fifth SELECT statement must first traverse the index vertically followed by horizontal scan at the leaf level. The retrieval operation must access a relational table ORDERS. (0.6 mark)

Deliverables

A file solution3.pdf with the SELECT statements for each one of the five cases described above.

Submissions

Submit the files solution1.pdf, solution2.pdf, and solutions3.pdf through Moodle in the following way:

1) Zip all the files (Solution1.pdf, solution2.pdf, and solution3.pdf into one zipped folder.)

2) Access Moodle at http://moodle.uowplatform.edu.au/

• To login use a Login link located in the right upper corner the Web page or in the middle of the bottom of the Web page
• When successfully logged in, select a site CSCI235 (SP421) Database Systems
• Scroll down to a section Submissions of Assignments 6) Click at Submit your Assignment 1 here link.
• Click at a button Add Submission
• Move the zipped file created in Step 1 above into an area provided in Moodle. You can drag and drop files here to add them. You can also use a link Add…
• Click at a button Save changes,
• Click at check box to confirm authorship of a submission,
• When you are satisfied, remember to click at a button Submit assignment.

A policy regarding late submissions is included in the subject outline. Only one submission per student is accepted.

Assignment 1 is an individual assignment and it is expected that all its tasks will be solved individually without any cooperation with the other students. Plagiarism is treated seriously. Students involved will likely receive zero. If you have any doubts, questions, etc. please consult your lecturer or tutor during lab classes or over e-mail.

Hire a Professional Essay & Assignment Writer for completing your Academic Assessments

Flexible Rates Compatible With Everyone’s Budget

Order Now