QUE: 1

You are on a project team to create a software system for creating and analyzing reports for Education Data Collected across multiple schools within a particular state. The reports will contain Ethnicity of the students, Income levels of parents and average class size. The data is found across multiple systems: School Enrollment Database (ENROLL) and School Grading Database (GRADER). The data contains spans for more than 30 years of student data on each system. The data need to be updated Semi-Annually.

(A) In less than 15 points, explain why creating a Data Warehouse is a viable solution.

(B) Identify the data source systems for the above requirements

(C) Identify the typical activities you would perform for Data Collection, Data Storage and Data Reporting (refer to Chapter 7 Technical Architecture) or the Week-3 Presentations

(D) What different salient features of the Data Warehouse, you would need to incorporate in your Data Warehouse design

QUE: 2

Based on the data on the below 3 tables, write SQL queries as below:

Table 1: HumanResources.Employee

Table 1: HumanResources.Employee

Table 2: HumanResources.Department

Table 3: HumanResources.EmployeeDepartmentHistory

(A) Write SQL query to display BusinessEntityId, LoginID, DepartmentId, StartDate

Hint: Inner join Table 1 and Table 3

(B) Write SQL query to display BusinessEntityId, login id, DepartmentId, Department Name, Department GroupName, StartDate

Hint: Inner join Table 1, Table 3 and then Table 2

QUE: 3

True and False. If False, write a brief note on the reason:

(A) For Data Warehouses, data is usually extracted from one or more data source systems.

(B)Transactional database applications typical goals is to get the data out of the system for Analytical purposes.

(C) A number of users of a Data Warehouse are typically lesser than the number of users for a transactional database application.

(D) Data in a transaction system is read-only and is not updated or deleted while data in the Data Warehouse is read, deleted and updated often.

(E) Data Warehouse is also a database with different structure and lesser number of tables compared to a transactional database.

OrganizationLevel 295947284 245797967 509847174 112457891 695256908 JobTitle Chief Executive Officer Vice President of Engineering Engineering Manager Senior Tool Designer Design Engineer Design Engineer Research and Development Manager Research and Development Engineer BirthDate 1963-03-02 S 1965-09-01 S 1988-12-13 M 1989-01-23 S 1946-10-29 M 1953-04-11 M 1981-03-27 1980-07-06 0x58 odventure workiloOx5ADA 134969118 adventure-works dylan x5AE1 adventure-worksldiane1 Ox5AE158 M S 811994146