there are labs 6,7,8,9,10 and 01 assignment which i want first
Guide to Project for Supervisors ITECH 2004 DATA MODELLING Lab 10 Topic Database Administration and Security Questions 1. Define dirty data and identify some of its sources. 2. What is data quality, and why is it important? 3. Suppose that you are a DBA. What data dimensions would you describe to top-level managers to obtain their support for endorsing the data administration function? 4. What special considerations must you take into account when introducing a DBMS into an organization. 5. Describe the DBA's responsibilities. 6. Why and how are new technological advances in computers and databases changing the DBA's role? 7. Describe and characterize the skills desired for a DBA. 8. Protecting data security, privacy, and integrity are important database functions. What activities are required in the DBA's managerial role of enforcing these functions? 9. Assume that your company assigned you the responsibility of selecting the corporate DBMS. Develop a checklist for the technical and other aspects involved in the selection process. 10. Define the concept of a data dictionary and discuss the different types of data dictionaries. If you managed an organization’s entire data set, what characteristics would you want for the data dictionary? 11. Using SQL statements, give some examples of how you would use the data dictionary to monitor database security. 12. Identify and explain some of the critical success factors in the development and implementation of a good data administration strategy. 13. How have cloud-based data services affected the DBA’s role.? CRICOS Provider No. 00103D itech 2004_10_lab.docx Page 1 of 4 CRICOS Provider No. 00103D itech 2004_10_lab solutions.docx Page 2 of 4 Guide to Project for Supervisors ITECH 2004 DATA MODELLING Lab 6 Topic Introduction to SQL Questions 1. In a SELECT query, what is the difference between a WHERE clause and a HAVING clause? 2. What is the difference between the COUNT aggregate function and the SUM aggregate function? 3. What is a CROSS JOIN? Give an example of its syntax? 4. What is a recursive join? 5. Explain the difference between an ORDER BY clause and a GROUP BY clause. Exercises 6. The ConstructCo database stores data for a consulting company that tracks all charges to projects. The charges are based on the hours each employee works on each project. The structure and contents of the ConstructCo database are shown in Figure E7.1. Note that the ASSIGNMENT table in Figure E7.1 stores the JOB_CHG_HOUR values as an attribute (ASSIGN_CHG_HR) to maintain historical accuracy of the data. The JOB_CHG_HOUR values are likely to change over time. In fact, a JOB_CHG_HOUR change will be reflected in the ASSIGNMENT table. Naturally, the employee primary job assignment might also change, so the ASSIGN_JOB is also stored. Because those attributes are required to maintain the historical accuracy of the data, they are not redundant. Given the structure and contents of the ConstructCo database shown in Figure E7.1, use SQL commands to answer the questions below: Figure E7.1 ConstructCo Database a. Download the following file from moodle: Week 6: Lab Files / 06_ConstructCo_MySQL.txt b. Import the file 06_ConstructCo_MySQL.txt into XAMPP. c. Write the SQL code required to list the employee number, last name, first name, and middle initial of all employees whose last names start with Smith. In other words, the rows for both Smith and Smithfield should be included in the listing. Sort the results by employee number. Assume case sensitivity. d. Using the EMPLOYEE, JOB, and PROJECT tables in the ConstructCo database, write the SQL code that will join the EMPLOYEE and PROJECT tables using EMP_NUM as the common attribute. Display the attributes shown in the results presented in Figure E7.2, sorted by project value. Figure E7.2 Query Results Exercise 6d. e. Write the SQL code that will produce the same information that was shown in Exercise 6d, but sorted by the employee’s last name. f. Write the SQL code that will list only the distinct project numbers in the ASSIGNMENT table, sorted by project number. g. Write the SQL code to validate the ASSIGN_CHARGE values in the ASSIGNMENT table. Your query should retrieve the assignment number, employee number, project number, the stored assignment charge (ASSIGN_CHARGE), and the calculated assignment charge (calculated by multiplying ASSIGN_CHG_HR by ASSIGN_HOURS). Sort the results by the assignment number. h. Using the data in the ASSIGNMENT table, write the SQL code that will yield the total number of hours worked for each employee and the total charges stemming from those hours worked, sorted by employee number. The results of running that query are shown in Figure E7.3. Figure E7.3 Query Results Exercise 6h. Extension Exercises (Optional) 7. The structure and contents of the SaleCo database are shown in Figure EE7.1. Use this database to answer the exercises below. Figure EE7.1 SaleCo Database a. Download the following file from moodle: Week 6: Lab Files / 06_SaleCo_MySQL.txt b. Import the file 06_SaleCo_MySQL.txt into XAMPP. c. Write a query to count the number of invoices. d. Write a query to count the number of customers with a balance of more than $500. e. Generate a listing of all purchases made by the customers. Sort the results by customer code, invoice number, and product description. f. Generate a list of customer purchases, including the subtotals for each of the invoice line numbers. The subtotal is a derived attribute calculated by multiplying LINE_UNITS by LINE_PRICE. Sort the output by customer code, invoice number, and product description. Use the following column aliases; Units Brought, Unit Price and Subtotal. g. Write a query to display the customer code, balance, and total purchases for each customer. Total purchase is calculated by summing the line subtotals (as calculated in Extension Exercise 7f) for each customer. Sort the results by customer code, and use the following aliases; Total Purchases h. Modify the query in Extension Exercise 7g to include the number of individual product purchases made by each customer. (In other words, if the customer’s invoice is based on three products, one per LINE_NUMBER, you count three product purchases. Note that in the original invoice data, customer 10011 generated three invoices, which contained a total of six lines, each representing a product purchase.) Your output values must match those shown in Figure EE7.2, sorted by customer code. Figure EE7.2 SaleCo Database CRICOS Provider No. 00103D itech 2004_02_lab solutions.docx Page 1 of 4 CRICOS Provider No. 00103D itech 2004_06_lab solutions.docx Page 2 of 4 Guide to Project for Supervisors ITECH 2004 DATA MODELLING Lab 7 Topic Introduction to SQL continued … Questions 1. Explain the difference between a regular subquery and a correlated subquery. 2. What does it mean to say that SQL operators are set-oriented? 3. The relational set operators UNION, INTERSECT, and MINUS work properly only if the relations are union-compatible. What does union-compatible mean, and how would you check for this condition? 4. What SQL Server function should you use to calculate the number of days between the current date and January 25, 1999? 5. What two things must a SQL programmer understand before beginning to craft a SELECT query? Exercises 6. The LargeCo database (see Figure E6.1) stores data for a company that sells paint products. The company tracks the sale of products to customers. The database keeps data on customers (LGCUSTOMER), sales (LGINVOICE), products (LGPRODUCT), which products are on which invoices (LGLINE), employees (LGEMPLOYEE), the salary history of each employee (LGSALARY_HISTORY), departments (LGDEPARTMENT), product brands (LGBRAND), vendors (LGVENDOR), and which vendors supply each product (LGSUPPLIES). Some of the tables contain only a few rows of data, while other tables are quite large; for example, there are only eight departments, but more than 3,300 invoices containing over 11,000 invoice lines. Given the structure of the LargeCo database shown in Figure E6.1, use SQL commands to answer the questions below: a. Download the following file from moodle: Week 7: Lab Files / 07_LargeCo_MySQL.txt b. Import the file 07_LargeCo_MySQL.txt into XAMPP. c. Write a query to display the first name, last name, and email address of employees hired from January 1, 2005, to December 31, 2014. Sort the output by last name and then by first name. d. Write a query to display the first name, last name, phone number, title, and department number of employees who work in department 300 or have the title “CLERK I.” Sort the output by last name and then by first name e. Write a query to display the first name, last name, phone number, title, and department number of employees who work in department 300 or have the title “CLERK I.” Sort the output by last name and then by first name f. Write a query to display the employee number, last name, first name, salary “from” date, salary end date, and salary amount for employees 83731, 83745, and 84039. Sort the output by employee number and salary “from” date. g. Write a query to display the first name, last name, street, city, state, and zip code of any customer who purchased a Foresters Best brand top coat between July 15, 2015, and July 31, 2015. If a customer purchased more than one such product, display the customer’s information only once in the output. Sort the output by state, last name, and then first name. h. Write a query to display the brand ID, brand name, and average price of products of each brand. Sort the output by brand name. Results are shown with the average price rounded to two decimal places. i. Write a query to display the customer code, first name, last name, and sum of all invoice totals for customers with cumulative invoice totals greater than $1,500. Sort the output by the sum of invoice totals in descending order. j. Write a query to display the vendor ID, vendor name, brand name, and number of products of each brand supplied by each vendor. Sort the output by vendor name and then by brand name. k. Write a query to display the brand ID, brand name, brand type, and average price of products for the brand that has the largest average product price Figure E6.1 Large Co ERD Extension Exercises (Optional) 7. Given the structure of the LargeCo database shown in Figure E6.1, above, use SQL commands to answer the questions below: a. Write a query to display the manager name, department name, department phone number, employee name, customer name, invoice date, and invoice