Lab # 04©Copyright 1999-2012The Penn State College of Information Sciences and TechnologyWorld Campus IST210-SQL Query on a Single TableDate Assigned and Points: See Course ScheduleAssignment:Using the VLABS environment, log into SQL SERVER and use the tables IST210-WC schema with the prefix of UC, and perform the following queries:1.Use the NULL keyword to display the s_id and c_sec_id for records that have no value for the field grade from the table uc_enrollment.2. Display the s_id, s_last, s_first, s_phone for students whose faculty advisor is faculty number 1.3. Display course_id, sec_num, and maximum enrollment for courses where the maximum enrollment is in the 35 to 100 range from the uc_course_section table sorted in descending order by maximum rollment value.4. Display faculty id, first name and last name when the faculty last na me contains letter 'w' (upper or lower case) anywhere in the last name.5. Display the number (tally) of faculty members who have the rank of associate professor?6. Display the minimum, maximum and average available seats in a section for courses in term_id 5.

 

Assignment 2

 

Using the case study and NIST SP 800-53, Identify and prioritize IT Security controls that should be implemented. Discuss any applicable US Government regulations/standards that apply to this organization (The organization is from Project 1)

Step 1: Review the selected case study and describe at least10 issues related to security, interoperability, and operations.

Step 2: Prioritize and articulate the selected requirements based on immediate need, security posture, complexity, resource availability, and cost.

Step 3: Identify at least 4 applicable government regulations/ standards that govern how the requirements must be met, implemented, or measured. Provide rationale for why these are applicable.

Step 4: Using NIST Special Publication 800-53 select at least 4 security controls that relate to these issues and describe how these controls enhance the security posture or facilitates the secure implementation of these requirements.

 

Assignment 3

This assignment provides a hands-on application of the concepts related to creating and populating a data warehouse. It involves a series of activities from thinking from a user/management perspective to determine the most important questions in a business situation, through design of a data warehouse, writing SQL to extract data from existing operational databases and transforming it into the data warehouse format, to the SQL that uses the data warehouse to answer the questions proposed.

 

 

Situation Description

 

You work for a large corporation that has just purchased 2 hotel and resort corporations each consisting of over 100 hotels. Each Corporation operates a custom database. You are provided the data dictionary and ER diagrams for the two operational databases. Management would like you to design a data warehouse that allows them to achieve the most competitive advantage possible.

 

(Note: The databases you will evaluate come from student groups in another class responding to the Hokie Resort problem. This problem also is provided for your review.)

 

 

 

To achieve this you decide to do the following:

 

Review the Hokie Resort situation description and the ER diagrams for the two operational databases.

Write the three most important questions that management must answer to achieve a competitive advantage in the hotel market.  Submit the questions and a brief (1-2 pages) explanation/justification of why these are the most important questions. (10 points)

Design a Data Warehouse Star or Snowflake schema that is sufficient for addressing these questions.  Submit an ER Model and the Oracle DDL* that implements your data warehouse schema (Note: you do not have to implement the schema in a DBMS). (15 points)

Analyze the ER diagram and data dictionary from both of the operational databases to determine if the two operational hotel databases have the data needed for your data warehouse. For each DB, create a mapping that shows the tables and attributes from that DB that are used to create rows in the data warehouse tables. For each data warehouse table, describe how the operational data is aggregated to create a row in the table. Submit your mapping and aggregation summary. (Note: If an operational database does not contain the data needed for your data warehouse design, then propose revisions to the existing tables in the DB or define additional tables to be populated in the DB so that it will contain the data needed for your data warehouse). (10 points)

Write the SQL necessary to extract and process the data from the two databases so that it will be suitable for your data warehouse. This requires writing 2 SQL procedures (Note: you do not have to make the procedures work).  Be sure that each procedure extracts fact and dimension table data (Hint: Don't forget the time dimension). Pay attention to the correct grouping and aggregation necessary to transform the operational data into the form needed for your data warehouse. An example of the procedure file is provided from a previous semester using the Hokie Hospital problem. Your task is to make a similar procedure that will extract the data from the operational databases into your data warehouse design. Submit you both procedures. (20 points)

Explain for each question how users will be able to easily determine the answer to the three most important questions (from step 1) using data from the data warehouse. Be sure to use language that the users of the system will understand. (10 points)

Write the SQL that will be needed to answer the three questions using your data warehouse. This requires writing 3 SQL procedures. (10 points)

ComputerScienceExpert

(11)

Status NEW Posted 22 May 2017 06:05 AM My Price 9.00

-----------

Attachments

file 1495435843-Solutions file 2.docx preview (51 words )

H-----------ell-----------o S-----------ir/-----------Mad-----------am ----------- Th-----------ank----------- yo-----------u f-----------or -----------you-----------r i-----------nte-----------res-----------t a-----------nd -----------buy-----------ing----------- my----------- po-----------ste-----------d s-----------olu-----------tio-----------n. -----------Ple-----------ase----------- pi-----------ng -----------me -----------on -----------cha-----------t I----------- am----------- on-----------lin-----------e o-----------r i-----------nbo-----------x m-----------e a----------- me-----------ssa-----------ge -----------I w-----------ill----------- be----------- qu-----------ick-----------ly -----------onl-----------ine----------- an-----------d g-----------ive----------- yo-----------u e-----------xac-----------t f-----------ile----------- an-----------d t-----------he -----------sam-----------e f-----------ile----------- is----------- al-----------so -----------sen-----------t t-----------o y-----------our----------- em-----------ail----------- th-----------at -----------is -----------reg-----------ist-----------ere-----------d o-----------n -----------THI-----------S W-----------EBS-----------ITE-----------. ----------- Th-----------ank----------- yo-----------u -----------

Not Rated(0)

• Buy Answer