Q$1)$ If you want to study relationship between profit and sales of Macy$$s$,$ In tableau which of the following chart would be most useful

A$.$ Bar

B$.$ Heat Map

C$.$ Scatter plot

D$.$ Geographical map

Q $2)$ Relationship between dimension tables and fact table is $($typically$)$

A $1$:$1$

B$.$ m:$1$

C$.1$:m

D$.$ none of the above

Q $3)$ ABC company is interested in finding their deadbeat customers by sales in a zip code $($a categorical variable$).$ What would be most appropriate way of doing it

A$.$ Create a bar cart with zip code and number of records in each zip code

B$.$ Find the correlation between zip code and number of records

C$.$ Create a graphical chart with total sales by zip code

D$.$ Can not be done

Q$4)$ In star schema with four dimensions $($state$,$ time, client, city$)$ PK of FACT table will be $.$

A$)$ combination of Pks of state and time

B$)$ combination of Pks of state, time, client and city

C$)$ there is no PK in Fact table

D$)$ None of the above

Q$5)$ Which statement about siloization is not true

A$.$ Siloization creates local data repositories in the silos

B$.$ Siloization may not give optimal outcome

C$.$ Siloization provides integrated data access across the company

D$.$ In siloization every department may have their own goals

Q$6)$ given two users:

user$1$ has stored names as first, last and user $2$ has stored name as last, first. You want to standardize names as first, last. This process of standardization is called

A$)$ Data extraction

B$)$ Data Transformation

C$)$ Data Loading

D$)$ Data reduction

Q$7)$ What would be the FK $($Foreign key$)$ of following relations?

PUBLISHER $($Pub$\_$ID$,$ Pub$\_$Name, pub$\_$address$)$

AUTHOR$\_$BBOK $($auth$\_$ID$,$ auth$\_$name, book$\_$ID$,$ pub$\_$Name$)$

A$)$ pub$\_$ID

B$)$ book$\_$ID

C$)$ Pub$\_$name

D$)$ none of the above

$$

A restaurant sales file contains data about all customer transactions for $2$ years

SALE $($state$,$ month, autotype, department, sales$)$

There are three dimensions

State

Time in years

Autotypes

There are

$4$ states

$10$ autotypes

Original sales table contains $500$ records

Following questions $8-9$ pertain to above SALE table

Q $8)$ what is the maximum fact table size?

A$)40$

B$)80$

C$)120$

D$)500$

Q$9$ What is the maximum size of state table.

A$)6$

B$)4$

C$)120$

D$)$ none of the above

Q$10)$ A digital dashboard can be used by a CEO

A$.$ To study sales trends

B$.$ To drill down

C$.$ To drill up

D$.$ All of the above

$$

Q$2.$Given the data for four countries and three businesses for months of January and February

January

Australia Canada France Germany

AMZN $2040600$

BAL $40500525$

TSLA $2500450$

February

Australia Canada France Germany

AMZN $0122050$

BAL $102200$

TSLA $402001510$

Cube is given on the following page, answer a$,$b$,$c$,$d$,$e parts

$(10$ points$)$

Give the total sales for the following cube parts

A$\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_$

B$\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_$

C$\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_$

D$\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_$

E$\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_$

Q # $3$

Given the following table,

$(5$ points$)$

Probject$\_$NUM Proj$\_$Name EMP$\_$NUM EMP$\_$NAME JOB$\_$CODE EMP$\_$PHONE

$1$ Lampost $101$ John Point EE $310-435-2131$

$1$ LAMPPOST $102$ Red nike CT $334-56-78906$

$1$ lamppost $103$ Johnny bench CT $410-214-6543$

$2$ Coastal code $110$ Mary Pilata BE

$2$ Coastal codes $102$ John Point EE $310-435-2131$

$3$ satellite $105$ Lee, doug $310-435-2131$

$3$ Satellite$123103$ Johhny Bench $410-214-6543$

$5$ Mountain$\_$ring $11041-214-6543$

$5$ Mountain$-$Ring Mary Pilata EE

Assumptions: some employees may not have phones

All phones have xxx$-$xxx$-$xxxx format

Identify at least $5$ data errors and discuss how they can be corrected

$$

Q $4$ Given the following heat map $($tree map$)$ from Tableau for population of a country by date in a country. If some blocks$/$data are not showing, ignore them in answering your questions.

a$.$ What does the intensity of color represent?

$(2$ points$)$

b$.$ Which country had the maximum population in $2016?$

$(1$ point$)$

$$

Q $5.(10$ points$)$

Given following table:

ENROLLMENT$($ department, school, quarter, total number of credits$)$

Mr$.$ Smith wants to keep track of total credits for a given quarter for a given school and department

Identify

$$ Dimension tables and its attributes, PK$,$ FK if any

$$ fact table and its attributes, PK$,$ FK if any

$$ Identify relationships between dimensions and fact table

$$

Q $6$ same as what we did in class.

$(5$ points$)$

How will gender and month DIMENSION tables change after aggregation? Just show attributes and their values

CUSTOMER $($gender$)$ DIMENSION

Cust key gender

Calendar $($month$)$ Dimensions

Calendar key month

BONUS $(3$ points$)$

Develop the fact table

Calendar Key Pro Key Store Key Cust Key $ Sold Units Sold