COMP508 DATABASE SYSTEM DESIGN

SEMESTER 1, 2025

Paired Assignment

Database Project                                                                                               50% of the final grade

Due: 5:00 pm, 13th June 2025

1. Introduction to Assignment Requirements

This assignment is designed to  provide students with practical exposure to the stages involved in database development, including the development of entity relationship modelling, database design and  implementation.  It  provides  an  opportunity  for  students  to  apply to  a  larger  case  study the database concepts discussed in class and practised in exercises.

A separate document on Canvas: Assignments – ‘Case Study for Database Project’, describes the case study in detail. Please read the case study carefully. Students may ask for additional clarification of the case study on the discussion board on Canvas.

This  is a  paired assignment.  Pairs  of students within the same  lab stream  should complete tasks together.  However, you  have the  option to work  independently with the approval of the Course leader.

The details of the three parts of the assignment and the tasks are specified in Section 2. Section 3 describes the submission details and dates. The marking scheme is in Section 4.

Expectations

The assignment includes 3 parts. Part A consists of Tasks 1 and 2. Part B is comprised of Tasks 3 and 4. Part C has task 5. Students are required to attempt all the tasks.

Tools & Templates

You must use Oracle SQL Developer and the specified CASE tool, Visual Paradigm CE version for this assignment.

Plagiarism

Plagiarism means borrowing from the work of another without indicating by referencing that the ideas expressed are not one’s own.

Unauthorised Collaboration

Unauthorised collaboration means joint effort between students or students and others in preparing material  submitted  for  assessment,  except  where  this   has   been   pre-approved  by  the  paper programme. Students are encouraged to discuss matters covered in classes, but the expression of ideas and arguments must be the student’s work.

ACADEMIC INTEGRITY GUIDELINES

AUT takes Academic Integrity very seriously and you are reminded that the following actions may be deemed to constitute a breach of the General Academic Regulations Part 7: Academic Discipline, Section 2 Dishonesty During Assessment or Course of Study

.    2.1.1 copies from, or inappropriately communicates with another person

.    2.1.3 plagiarises the work of another person without indicating that the work

is not the student’s own – using the full work or partial work of another person without giving due credit to the original creator of that work

.    2.1.4 collaborates with others in the preparation of material, except where this has been approved as an assessment requirement.

.    2.1.5 resubmits previously submitted work without prior approval of the assessment board

.    2.1.6 using any other unfair means.

ADDITIONAL INFORMATION

.    Your assessment  responses must be your own work. You may be required to orally defend your responses to assessment questions.

2. Assignment Parts and Tasks

Part A Entity Relationship Modelling and Logical Database Design (35 marks)

Task 1  Identify Entities & Develop Business Rules                                                         [10 marks]

Identify all entities and relationships in the case study and develop a set of business rules. You should follow the syntax given below. Two rules must describe each relationship, one in each direction.

Each ENTITY_ 1 May/Must Relationship_Verb_Phrase number ENTITY_2

Task 2  Construct a logical Entity Relationship Diagram (ERD)                                     [25 marks]

Based on the business rules developed in Task 1, construct a logical ERD for the case study using the Visual Paradigm tool.

.    Identify all attributes in each entity, including all primary and foreign keys (transform any composite and multivalued attributes according to the rules of the relational model).

.    Name all relationships (i.e. you must use verb phrases on both sides of each relationship)

.    Identify the Cardinality and Participation for each relationship.

.    Resolve all many-to-many (M:N) relationships.

.    Describe any assumptions you have made in a text note on the diagram.

Please note: You don’t need to identify data types in your ERD.

Part B Database Implementation (35 marks)

In this part, you should develop a database based on your logical ERD developed in Part A. You must populate the tables with realistic sample data.

Task 3  Create tables [25 marks]

Create the tables in Oracle. Write SQL scripts defining each table. The table definitions should include

.          All attributes with appropriate data types

.          All appropriate constraints, such as primary key, foreign keys, and check statements (CHECK constraints).

.          All constraints must be given names. Naming standards must be used.

Task 4  Populate data                                                                                                             [10 marks]

Insert a small sample of realistic test data (5 –10 rows minimum) into each table. All test data must be meaningful to demonstrate your understanding of the data.

After creating all your tables (Task 3) and inserting data (Task 4), run the SQL SELECT statement below:

SELECT table_name FROM user_tables;

The statement will retrieve a list of all tables in your Oracle account (i.e., all tables you created this semester). Include the results of the SELECT statement in your assignment.

You must also include the following for each table in your database:

.    SQL table creation script.

.    SQL insert script (used for inserting sample data)

.    Evidence of Sample data - you must run “SELECT * FROM table_name” (where table_name is the table's name) for each table in Task 3 and include results of SELECT statements for each table.

Part C Construct SQL Queries (30 marks)

Task 5  Construct SQL Queries [30 marks]

Identify five data retrieval requirements that would be used by the business described in the case study. Construct a SQL query to display the data for each requirement you have identified. So, you must construct five SQL queries in total. The queries must include a combination of the following SQL features:

.    Use of restriction (WHERE clause), Arithmetic expressions, Concatenation of columns

.    Use of comparison and logical operators

.    Multiple tables join (e.g., Equijoin, Outer Join, and Self-join)

.    Group functions (e.g., COUNT, SUM, AVG,  MAX,  MIN) and clauses (e.g., ORDER BY, GROUP BY, HAVING)

Note:

1. Your queries can combine the above features, which can be spread across the five queries, i.e., not all features need to be present in a single query. The design of each query should demonstrate your knowledge and application of SQL content covered in the course and your understanding of the correct usage of the various features and clauses of SQL. A simple select

* from table_name will fetch you a mark of zero in this task.

2. There should be no more than one query that involves a single table.

3. An example of a query is given on the next page.