Reference no: EM132512400

HS2021 Database Design and Use Assignment - Holmes Institute, Australia

Assessment Task - Tutorial Questions

Purpose - This assignment is designed to assess your level of knowledge of the key topics covered in this unit.

Unit Learning Outcomes Assessed -

1. Design and implement a relational database by considering business needs and context of the systems.

2. Retrieve and present information from cross functional areas information using SQL tools and techniques.

3. Discuss implementation approaches and associated risks associated with the implementation of a Database Management System.

4. Contribute and influence business decisions by consolidating information from databases.

5. Understand the ICT profession and the expectations of ICT professionals in Database Management Systems roles.

Description: Your task is to answer a selection of tutorial questions for weeks 1 to 5 inclusive and submit these answers in a single document. The questions to be answered are;

Question 1 - Create the Entity-Relationship Diagram and the Relational Schema for the following scenario:

BestFreelancer prides itself on having the most efficient platform through which is possible to find freelancers for any type of work. Freelancers can freely register on the platform and provide information about all the projects they have completed. To do this, BestFreelancer relies on a company-wide information system. Freelancers are the heart of the BestFreelancer information system. Freelancers are characterized by their profile code (unique), first name, last name, and email. Freelancers can list, within their profile, as many projects as they want. Projects are characterized by their project code (unique), start date, end date, project title, and project description.

Question 2 - Create the DDL statement to create tables described in the following Entity-Relationship Diagram.

Question 3 - Given the following tables, Create the DML statement to retrieve the age of the users who purchased an album worth at least $50 or a different type of item (which is not an album) worth at least $25.

Question 4 - Create the DDL statement to create tables described in the following Entity-Relationship Diagram.

Question 5 - Given the following table, Create the DML statement that for each user provides information about the total amount of money the user spent of each given type of item and the number of items bought.