Assignment Detail:- ICT105 Data Modelling and Database Systems - Sydney International School of Technology & Commerce
Assessment - Designing and Implementing a Relational Database
OverviewThe 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 databaseLearning 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-
RequirementsQuestion 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 pageTable of contents1-0 IntroductionSQL Statements ReportQuestion 1Question 2Question 3Question 4---2-12 Question 153-0 Conclusion References
Attachment:- Data Modelling and Database Systems-rar
Most Recent Questions