b.    Define the relational schema: [6 marks]

Convert your ER model to a relational schema by doing suitable relational mapping. You may do this iteratively, starting first with basic tables, attributes, and relationships and then refining it to convert more complex relationships in the design.

All your tables will be in at least the first normal form, but if you have done the design and the relational mapping correctly, your tables will be in the third normal form.

You must consider the constraints, such as primary and foreign keys. You may also add any other constraints deemed required. You are expected to improve the preliminary work by adding suitable referential integrity constraints.

c.    Data description: [4 marks]

Then, decide on suitable data types and attribute-level constraints (such as NOT NULL) for attributes. Show this information in tabular format. For each attribute, you must define at least the attribute name, selected data type, description of the attribute and any constraints on attribute values. Any important business rules you assume also must be defined here.

Part 2.   Implementing the database you have designed and load it with sample data.

[15 marks]

a.    Implementation: [11 marks]

Looking at the relational schema and the data description resulting from part 1 above, implement your database with suitable tables and constraints, including proper referential integrity constraints. First, create a sample database with  _, then implement all tables there.

b.    Loading sample data: [4 marks]

Insert sample values into the implemented database and demonstrate that the integrity constraints are met when entering data.

NOTE: Some web links to obtain sample data relating to the given scenario will be mentioned on the assignment page of Blackboard. You may use these sources or any other suitable source to obtain sample data. You have to enter a reasonable amount of data into the database so that meaningful results for the queries you will design in the next sections can be obtained.

Part 3.   Designing and implementing queries: [16 marks]

When your database is up and running, it is time to retrieve data to answer some reasonable queries. First, think and derive some meaningful and useful questions (around 6 -10 questions) regarding the data in your database, which can be converted to SQL queries to get answers. Make sure you think about using a single table or several tables, obtaining data based on conditions, string manipulation, etc. (There are many other aspects you can think about.)

Then, for each of your questions, design and implement an appropriate SQL query to produce the required answer in an effective manner.

You should demonstrate that you can,

Level 1: [8 marks]

Use basic SQL SELECT statements with the use of numeric data, date-time functions, string comparison and manipulation, and other related basic methods with suitable WHERE clauses.

Level 2: [8 marks]

Use joins and sub-queries with GROUP BY, ORDER BY, aggregate functions and related clauses.

Part 4.     Increase the database functionality with advanced concepts: [16 marks]

Design and implement database programming concepts such as stored procedures, triggers, views, and indexes to improve the capabilities of your database. You are expected to design and implement at least two categories of the above-advanced features, including stored procedures, and show at least two uses. For example, you can implement two useful stored procedures and two useful triggers. You are expected to demonstrate your ability to meaningfully use different concepts such as parameters, user-defined variables, loops/ if-else, different forms of triggers, complex views, etc.

Part 5.     Connect to the database using a suitable programming language and show sample

query results: [10 marks]

Demonstrate your ability to connect to a MySQL database via Python3 and use it in a Python3 environment/ Java environment.

First, you are expected to connect your database to Python3 or Java in a proper way and  call your already defined queries (part 3 above) via the Python programming constructs to show that you can do some useful data retrieval activities. Then, you are expected to demonstrate your ability to perform other operations, including inserting/ updating/ deleting data via the programming environment. (Alternatively, you may use Java connectivity to answer this sub-section.)

For parts 2 to 5 above, SQL statements are to be written. Overall use of proper SQL (comments, readability of code, styling, good practices, proper use of scripts, etc.) is also expected [10 Marks]

You may note that the total mark, as per the above-detailed description, is 85. The remaining marks will be allocated for the user guide, introduction, and reflection of your work.

Note: You can reuse the SQL queries and approaches from the lectures and practicals, but as the   scenario is different, you cannot use them directly. Remember to cite and cite your sources, if any. If you submit work that you have already submitted for a previous assessment (in this unit or any other), you have to state this specifically.

4. Documentation

You need to document what you have done in each stage of the assessment so that another person can get a clear idea about what you have done. You are expected to produce two short documents in this assessment.

1.    User guide to implement and use your database [5 Marks]

In this document, you are expected to describe clearly how the database you have designed/implemented can be created in a MySQL server and then use it to run the queries you have developed. You must clearly indicate how any MySQL scripts you have produced to create the database, create tables, etc., are to be executed, with relevant information about  your MySQL version information, operating system, etc. You must provide the commands, and may also include screenshots. (This is not your report.)

By following what you have written in the guide, another person should be able to implement and use your database. Use suitable headings and organize your document. Include a suitable sub-section/cover page indicating the assessment name, report title, your name, your Curtin student ID, and lab group. The user guide would be 2-5 pages.

2.    Report on your database [35 Marks, including the design section’s 18 marks] Your report should include the following sections:

a.    Cover page [1 mark]

Include the assessment name, your name, Curtin student ID, and practical class (date/time/ lab number)

b.    Introduction [1 mark]

Short overview of your work, including the selected scenario and activities you have done.

c.    Design of the database [18 marks, part 1 of the assessment task]

i.    Explain why you have selected the entities, relationships, data types, etc.

ii.    ER diagram, Relational schema, data description and any other material you have produced in the design stage, i.e., part 1 of the assignment

iii.   Any assumption you made during the design of the database.

d.    Implementation of the database and adding sample data   [3 marks out of 15 marks for part 2 of the assessment task]

Briefly describe how you have implemented the database with evidence of implementation.

Briefly describe your sample data, data sources, and how you have inserted data into your database with evidence.

e.    Use of the database

[7 marks out of 42 marks of part 3-part 5 assessment task]

i.    Design and implement queries.

Briefly describe what you wish to know by using the query, why the results are important, and evidence of their use (query implementation and sample outputs).

ii.    Design and implementation of advanced features

Briefly describe the advanced features you have implemented, their use and evidence of your implementations/ outputs.

iii.    Database connectivity and Python implementation

Briefly describe the database connectivity implementation and evidence of your implementation.

f.     Discussion [5 marks]

Reflect on your own work, including a summary of what you have achieved, challenges you have faced, limitations and ways to improve your work with other features you have not considered, and any other information you wish to present.

Your report would be around 10-20 pages.

5. Your submission

Your submission will be done in three steps.

Submission step 1:

You must submit a signed and dated assignment cover sheet (Declaration of Originality sheet) in PDF format to the “Assignment submission Step 1: cover sheet” link provided in the assignment folder. A blank assessment coversheet (Declaration of Originality) is available under the Assessments page of Blackboard. You can sign a hard copy and scan it or fill in a soft copy and digitally sign it.

Submission step 2:

Your documentation,

1.    Your user guide (refer to section 4.1)

2.   Your report on your database (refer to section 4.2),

should be submitted to the Turnitin links (User Guide Submission: Step 2(a) and Report Submission: Step 2(b)) provided in the assignment folder, respectively. Your documents submitted to Turnitin links and other links should be in PDF format.

Your report name must follow the following format:

“< Your First Name as per Blackboard>__DbSReport2024”

Submission step 3:

You should submit a single zip file of all the work produced in this assessment to the “Assignment submission: step 3” link provided in the assignment folder. First, create a folder with the name

“< Your First Name as per Blackboard>__DbS2024”

Then, place all your work inside this folder. Example: SuttonLiam_12134567_DbS2025

The folder should contain:

1.   Your SQL/database programming/ data files: You must submit all your .sql files or any  other file resulting in parts 2-5 of the detailed description section. Name your files in an appropriate manner and they must be referred to in your user guide correctly.

2.   Your user guide (refer to section 4.1) that has already been submitted to the Turnitin link.

3.   Your report on your database (refer to section 4.2) has already been submitted to the Turnitin link.

You can organize your files in sub-folders so that it is well organized and easy to use.

Zip this folder and submit to the “Assignment submission: step 3” provided in the assignment page. Make sure that your zip file contains what is required. Anything not included in your submission may not be marked, even if you attempt to provide them later. You are responsible for ensuring your submission is complete and correct.

6. Demonstration

After submitting your assignment, a short demonstration (15-20 minutes) will be held, and the schedule will be announced later.