Q$1$: Given the following scenario:

Construct a database design in UML for a stock market tracking database. Data

types are not needed.

$$ The database tracks companies, where a company has a name and is

identified by its ticker symbol.

$$ Each company participates in a particular industry. An industry has an

identifying name and also a description.

$$ Stock market analysts cover stocks and produce recommendations.

Analysts are distinguished from each other by the name of their firm and

their name. Analysts also have an accuracy rating that measures the

accuracy of their recommendations.

$$ Each recommendation is from a particular analyst on a certain company. A

recommendation includes a predicted price in the next $12$ months and a

rating for the stock $($buy$,$ hold, sell$).$

$$ The database includes a daily summary for each company with a closing

price, volume, low price, and high price. Daily summaries are identified by

the company it is associated with and the summary date.

$$ News events are tracked for companies. A news event has an identifying

eventId and has additional details on the event text and the release date. A

news event may be associated with multiple companies, and a company

may have many news events.

$$ A government analyst makes sure that the company complies with the free

market policies. A policy analyst has to be appointed for each company.

Each company has to have at least one policy analyst and at most three of

them.

$1.$ Build an ER$/$UML diagram $(4$ Marks$).$ This can be done using Astah.

$2.$ The design has one opportunity for using subclasses $($i$.$e$.$ IS$-$A constraints$).$

Identify the entities involved and provide the appropriate constraint $($i$.$e$.$

OPTIONAL$/$MANDATORY and AND$/$OR$)(1$ Mark$).$

$3.$ Convert the diagram into the relational model. SQL DDL is NOT required.

Write out the relation names with attributes. Identify by underlining

primary keys and indicate all foreign keys. Remember to clearly indicate the

FK direction $(5$ Marks$)$