Database design and use

Assessment Task – Tutorial Questions

Unit Code: HS2021

Unit Name: Database Design and Use

Assignment: Tutorial Questions 1

Due: 11:30pm 15th May 2020

Weighting: 25%

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.

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

Description: Each week students were provided with three tutorial questions of varying degrees of difficulty. These tutorial questions are available in the Tutorial Folder for each week on Blackboard. The Interactive Tutorials are designed to assist students with the process, skills and knowledge to answer the provided tutorial questions. 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;

Week 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.

(10 marks)

Week 2

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

Employee

Role

Role ID

Security Level

Salary Level

Employee ID

First Name

Last Name

(10 marks)

Week 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

Users
Username	Age	Credit
Johndoe31	25	10000
Jacky12345	41	5000
Willy2000	65	2000
Wozniak	35	1000

Purchases
PurchaseID	Item	Price	Username
12345	Song	1	Johndoe31
43234	Video	50	Jacky12345
13414	Video	40	Willy2000
77475	Album	30	Wozniak
56546	Video	50	Johndoe31

(10 marks)

Week 4

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

Player

Club

Club Name

Est Year

Player Name

Role

Plays for

Started with

(10 marks)

Week 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

Purchases
PurchaseID	Item	Price	User
12345	Song	1	Johndoe31
43234	Video	50	Jacky12345
13414	Video	40	Willy2000
77475	Album	30	Wozniak
56546	Video	50	Johndoe31

(10 marks)

Submission Directions:

The assignment has to be submitted via Blackboard. Each student will be permitted one submission to Blackboard only. Each student needs to ensure that the document submitted is the correct one.

Academic Integrity

Academic honesty is highly valued at Holmes Institute. Students must always submit work that represents their original words or ideas. If any words or ideas used in a class posting or assignment submission do not represent the student’s original words or ideas, the student must cite all relevant sources and make clear the extent to which such sources were used. Written assignments that include material similar to course reading materials or other sources should include a citation including source, author, and page number.

In addition, written assignments that are similar or identical to those of another student in the class is also a violation of the Holmes Institute’s Academic Conduct and Integrity Policy. The consequence for a violation of this policy can incur a range of penalties varying from a 50% penalty through to suspension of enrolment. The penalty would be dependent on the extent of academic misconduct and the student’s history of academic misconduct issues.

All assessments will be automatically submitted to SelfAssign to assess their originality.

Further Information:

For further information and additional learning resources, students should refer to their Discussion Board for the unit.

4