Test 2 Sample

1
Test 2 Sample

• note additional questions may also feature in
  the text

2

• Given a database (see next slide), how do you
  add/change rows in one or more tables

3
There is a new faculty member named Dr. Martha
Henderson. She is a new Assistant Professor in
the Accounting department and her office is 535
Alter Hall. Her phone number is 214-204-5432. Her
email address is mhenderson_at_temple.edu.
4

• INSERT INTO facultystaff (FacStaffID,FirstName,Las
  tName, ShortTitle, OfficeNumber, BuildingCode,
  Phone, Email, FacultyCode, StaffCode) VALUES
  (109, Martha, Henderson,Dr.,535,3,214-204-5
  432, mhenderson_at_temple.edu,1,0)
• Insert into facultystaff_department (DeptFacKey,
  FacStaffID, DepartmentID, Primary Appointment)
  VALUES (5, 109, 3, 1)

5

• Given a star schema (see next slide)
• What is the fact in this schema?
• Which one is the fact table?

6
Smoking Smoking_code Smoking_YN
Guests Guest_number Guest_firstname Guest_lastname
Guest_address Guest_city Guest_zipcode Guest_emai
l
Hotels Hotel_id Country_code Hotel_name Hotel_addr
ess Hotel_city Hotel_zipcode Average_star_rating
Reservation Reservation_id Hotel_id Guest_number T
ime_id Smoking_code (Average) room_rate Number_day
s
Time TimeID Month Day Year
7

• Choose fields to draw a Pivot Table to answer the
  following questions (see next slide)
• Which products sold most in January in zip code
  19122
• Which product did 20 year old customers buy most
  in Northeast Region?

8
(No Transcript)
9
BMW Dealerships

• Dimension 1 LocationThere are locations
  Philadelphia, Atlanta, San Francisco, New York
• Dimension 2 ProductThere are three models of
  cars X5, 5 series, 7 series
• Dimension 3 Time There are three time periods
  October 2011, November 2011, December 2011
• Fact Quantity and Revenue

10
Represent this information in the data
cube? What does the shaded area represent?