$($Total $20$ points$)$ Figure $1$ is a part of Chattanooga Furniture Company's ERD.

Figure $1.$Spotify ERD

$(1)(2$ points$)$ We would like to create MySQL$\text{'}$s codes for Five $(5)$ entities and relationships as the ERD presents. Please specify the order to create this entire ERD.

Step $1$: first, make $($

Step $2$: and then, ma$,$

rable

Step $3$: and then, make

Table

Step $4$: and then, make

${?}^1$ Table

Step $5$: and then, make Table Table$(2)(3$ points$)$ Create SQL codes to create these five entities and relationships. $($Hint: You should consider the order of entity creations as you answered $1.(1))$

$($Hint: You can find the type information of attributes in the figure $1.)(3)(3$ points$)$ Now, consider we want to track new information $-$ Product Line. Therefore, you need to create a new entity called ProductLine.

In the ProductLine entity, we will keep four attributes PLID as PK$,$ PLName, PLAddress, and PLZipcode. $($PK should be char$(5),$ PLName should be varchar$(20),$ PLAddress should be varchar$(30),$ and PLZipcode should be char$(5).)$ We want to track the product line for each product. Therefore, we want to link the ProductLine entity with Product entity.

Using ALTER and Create, please complete to create ProductLine entity, modify required entities, and link the two entities $-$ ProductLine and Product.$(4)(1$ points$)$ List all the information in raw material. When you list the information, you must sort the result by material standard cost in descending order.

$(5)(1$ points$)$ List all the information on the Product entity with its product line name and zipcode of product line, but not product line id and product line address. You want to retrieve such information only when he standard price of product should be greater than $1,000$ dollars.

$(6)(2$ points$)$ List vendor name, material name, supply unit price, and standard cost of the material when the supply unit price is between $500$ dollars and $3500$ dollars.$(7)(2$ points$)$ List raw material name, product id$,$ and product description when the material name has "copper" or "platinum."

$($HINT: Some of the materials have name like Black Copper and Blue Platinum. You want to retrieve all such raw materials.$)$

$7$

$(8)(2$ points$)$ Consider the groups of Product Lines where each product line manufacture multiple products. You want to see aggregate information such as average product standard price and average quantity to make the product with product line's name by product$-$line. Finally, we want to retrieve such information only when the product line is in $374$XX area$(9)(2$ points$)$ Consider the groups of Products where each product require to have multiple raw materials. You want to see aggregate information such as average quantity uses, and average material standard costs required per material to create each product. We want to retrieve such information with product id and product description.

$(10)(2$ points$)$ Finally you want to retrieve information such as material name, material standard costs, and average supply unit price for the materials by each of the raw materials. You want to retrieve such information only when part of the vendor's name includes "see" or $"$uk$"($MUST USE A SUBQUERY$).(0\mathrm{.}5$ points$)$ A data mart is a$($n$)$:

A$)$ enterprise$-$wide data warehouse.

B$)$ smaller system built upon file processing technology.

C$)$ data warehouse that is limited in scope.

D$)$ generic on$-$line shopping site.

E$)$ None of above.

$(0\mathrm{.}5$ points$)$ A star schema contains both fact and $q,$ tables.

A$)$ narrative

B$)$ cross functional

C$)$ dimension

D$)$ starter

E$)$ None of above.

$(0\mathrm{.}5$ points$)$ The level of detail in a fact table determined by the intersection of all components of the primary key, including all foreign keys and any other primary key elements, is called the:

A$)$ span.

B$)$ grain.

C$)$ selection.

D$)$ aggregation.

E$)$ None of above.$(0\mathrm{.}5$ points$)$ A class of database technology used to store textual and other unstructured data is called:

A$)$ mySQL$.$

B$)$ NoSQL.

C$)$ KnowSQL.

D$)$ PHP$.$

E$)$ None of above.

$9$

$(0\mathrm{.}5$ points$)$ Organizations adopt data mart architectures because it is easier to have separate, small data warehouses than to get all organizational parties to agree to one view of the organization in a central data warehouse.

$($True$)$ or $($False$)(0\mathrm{.}5$ points$)$ ETL is short for Extract, Transform, Load.

$($True$)$ or $($False$)$

$(0\mathrm{.}5$ points$)$ When multiple systems in an organization are synchronized, the need for data warehousing increases.

$($True$)$ or $($False$)$