Reference no: EM133148220

ICT105 Data Modelling and Database Systems - Sydney International School of Technology & Commerce

Assessment - Designing and Implementing a Relational Database

Overview
The purpose of this assignment is to develop the student's skills in designing and implementing a relational database for a given case study.

Learning Outcome 1: Interpret entity relationship diagrams to implement database
Learning Outcome 2: Describe relational algebra and its relationship to Structured query language (SQL)
Learning Outcome 3: Use of query language tools and techniques to obtain data and information from database.

Assessment Details

In this Assignment you are implementing and writing the several SQL queries on a relational Database.

Requirements
Question 1. Write the SQL query to create the table structures for the entities shown in Figure1 ER-Diagram. The structure should contain the attributes specified in Figure1. Use data types that are appropriate for the data that will need to be stored in each attribute. Enforce primary key and foreign key constraints as indicated by the diagram.

Question 2. The following tables provide some example data that will be inserted into the database. Write the INSERT commands necessary to place the attached data in the tables that were created in Question 1. Alternatively provide the commands in your final report.

For the questions 3-15, use the tables created in question1 and the data inserted in question2.

Question 3. Write a SQL query in to find the Hire date, Employee title and Department number of employee ‘' TOM HOBSON".

Question 4. Write the SQL query to find the Product details that contain the word "coating" in product description.

Question 5. Write a SQL query to display departments in the LGDEPARTMENT table sorted by Department name.

Question 6. Write a SQL query to display the first name, last name and email address of Employees hired from January 1, 2005 to December 31,2006. Sort the output by last name and then by first name.

Question 7. Write a SQL query to display the first name, last name, phone, tittle, and department number of employees who work in department 280 or have the title "ASSOSIATE". Sort the output by last name.

Question 8. Write a query to display a brand name and the number of products of that brand that are in database. Sort the output by brand name.

Question 9. Write a SQL query to find all products with a price greater than or equal to the average product price.

Question 10. Write a SQL query to display the number of products in each category that have a water base, sorted by category.

Question 11. Write a query to display the employee number, last name and first name, and sum of invoice totals for all employees who completed an invoice. Sort the output by employee last name and then by first name.

Question 12. Write a SQL query to display the largest average product prize of any brand.

Question 13. Write a SQL command to change the department number to 300 for the employee whose employee number is 83537.

Question 14. Write a SQL command to alter the employee table to include EMP_HRS attribute. The attribute should not accept NULL values, and it should have default value 10.

Question 15. Write a SQL command to modify the brand 23 to indicate the brand type Premium.

Prepare a business style report which contains the following: Each SQL query and associated output and Full APA referencing of any resources used. The structure of the report given below:

Title page
Table of contents
1.0 Introduction
SQL Statements Report
Question 1
Question 2
Question 3
Question 4
...
2.12 Question 15
3.0 Conclusion References

Attachment:- Data Modelling and Database Systems.rar