Antennas online shop

Antennas$4$AllSeasons $($A$4$AS$)$ Ltd$.$ is an online reseller of multi$-$purpose and multi$-$standard outdoor antennas. Company business managers decided two weeks ago to redesign their e$-$commerce Web portal because, starting from January $1$st$,2022,$ the company will be able to sell indoor antennas as well. Therefore, the expected e$-$shop will be required to allow users selecting between outdoor antennas $($under the definition of $$base station antennas$$ or BSAs$)$ and indoor antennas $($under the definition of $$in$-$building antennas$$ or IBAs$).$ The BSA product category must be kept organised into three subcategories, as it was originally: omnidirectional BSA, sectorial BSA, small$-$cell BSA. The new IBA category, instead, is not expected to have any further subcategory.

Independently from the product category, each antenna must be characterised in terms of general features and electrical specifications. General features are: type $($i$.$e$.,$ omnidirectional, sectorial$,$ small$-$cell$),$ band $($i$.$e$.,$ single, multiple$),$ brand, model name, colour, number of RF connectors, length $($in mm$),$ width $($in mm$),$ depth $($in mm$),$ weight $($in kg$),$ and cost $($in GBP$).$ Electrical specifications are: frequency band $($in MHz$),$ input power $($in Watts$),$ horizontal beamwidth $($in degrees$),$ vertical beamwidth $($in degrees$),$ return loss $($in dB$),$ gain $($in dBi$)$ and compliance with the ISO $9001$:$2015$ standard. In case of multiple$-$band antennas, keep in mind that all the electrical specifications change depending on the band. To avoid data modellers panicking during the website refactoring, the company has provided a sample datasheet, whose $$specifications$$ section should be consulted in case of necessity.

Online sales are available only once customers are registered. Online sales are managed in terms of orders, which are traced from the moment they are placed by the customers to the moment they left the company warehouse in Bristol for shipping to the customer. The interactions with freight couriers are not considered in this scenario. A$4$AS Ltd$.$ operates according to a B$2$B model only.

By examining the scenario described above:

$1.$ Design the conceptual data model $($EER diagram$)$ of $($at least a part of$)$ the information system

$2.$ OLTP Section

a$.$ Map the corresponding relational model. b$.$ In SQL:

i$.$ List all the available sectorial base station antennas having a gain G $>6\mathrm{.}5$ dBi.

ii$.$ Provide an alphabetically ordered list of the customers who placed an order for

small cell BSAs in September $2021,$ possibly grouped by country.

$3.$ OLAP Section

a$.$ Design the DFM of a fact $($with two measures, at least$)$ that you deem significant for the

described scenario.

b$.$ Design the corresponding Snowflake$/$Star schema and detail formulas of the proposed

measures if needed.

c$.$ Sketch a possible mock dashboard according to the proposed DFM $($limit the dashboard

to the Model component of the MAD approach$).$

$4.$ Theory. Answer the following questions:

a$.$ Describe the different types of measures available in DFM modelling and provide at least an example for each of them.

b$.$ Explain in how many ways the cardinality ratio can be expressed in ER$/$EER modelling and provide an example for each of them by referring a single binary relationship.