ICT50515 Diploma of Database Design and Development

YOU MUST DEMONSTRATE SATISFACTORY COMPETENCE IN EACH QUESTION OF THE ASSIGNMENT.

1. This is an open book assessment. You may use online help, manuals and/or handouts to complete the tasks in this paper.
2. Complete and sign the Assessment Cover Sheet – Out of Class. This is to be submitted with your assessment.
3. Follow WH&S standards for safe use of computer equipment and the workstation all at times.
4. Your response to this assessment task should be submitted as a report with appropriate response to the given task, in a Word document as well as an SQL query file. The Word document should have the Cover page, Table of contents, Introduction, responses to the Task, References and a Write-up.

ASSESSMENT CRITERIA

To obtain a satisfactory assessment outcome, students must show evidence of the ability to:

• write a simple, structured query language (SQL) statement to retrieve and sort data
• write an SQL statement that selectively retrieves data
• write SQL statements that:
  • use functions and operators
  • use aggregation and filtering
  • retrieve data from multiple tables
  • write and execute SQL sub-queries
  • create and manipulate tables to meet specific requirements
  • create and use views that satisfy information requirements
  • create and use stored procedures.

AND

• identify client data management and security requirements
• produce a prototype data base
• populate and perform tests
• discuss results with client for approval
• implement the data base with the client.

Task

You are to design and develop a simple database system for a training institute. You can make valid assumptions about the entities found in such a domain. At a bare minimum, you have to have at least three entities, which are the most important ones. Essentially, you are to do the following:

• Draw (Word or Visio) an ERD showing Entity names and their properties.
• Identify and show the relationships between these entities.
• Create a database with appropriate name and create relevant tables with Primary and Foreign keys.
• Write SQL queries to perform the following operations:
  • Insertion of one row in a given table.
  • Insertion of multiple rows in a given table.
  • Update of an existing record.
  • Deletion of an existing record.
  • Reading data from one table.
  • Reading data from two tables.
  • Reading data from three tables.