Question

Oracle Database Analysis and Design

This project is to develop and implement an Oracle database. See the scenario of the Investment database.

This simplified Investment Portfolio case study maintains information regarding clients that invest in stocks and mutual funds. Each client has a unique taxpayer identification number. A client may invest in stocks or mutual funds.

A stock is uniquely identified by its ticker, which is symbol on the stock exchange. The name of issue for a stock is the name of the company. A stock has a rating, which is an assessment of the stock. The principal business of a stock denotes the primary business from which the company obtains its revenue. There are several prices associated with a stock. Besides the current price of a stock, the high and low price ranges are recorded for the calendar year. The return value for a stock, which is the price appreciation, is recorded for the prior year and five-year period.

A mutual fund is also uniquely identified by a ticker symbol and has a fund name. The principal objective of the fund is also recorded (e.g. ‘G’ for growth, ‘I’ for income, and ‘S’ for stability). Similar to stocks, mutual funds record a current offering price and a high and low price range. A percent yield from investment income is also maintained. A mutual fund may be associated with at most one fund family.

A fund family has a unique identification number and the company’s name and address. A fund family, as its name implies, can have many associated mutual funds, but must have at least one mutual fund.

Based on the high level introduction to the Investment Portfolio enterprise, assume the following:

The characteristics of a client include a unique taxpayer identification number, a name, and an address. A client can invest in many stocks in many mutual funds.

The characteristics of a stock include the stock’s unique ticker on the stock exchange and additional descriptive properties that include the name of issue, a rating, the principal business, a high and low price range, the current price, and return values for the prior year and 5-year period.

The characteristics of a mutual fund include the mutual fund’s unique ticker and additional descriptive properties that include the name of the fund, its principal objective, the high and low price range, the current offering price, and the fund’s yield. A mutual fund may be associated with the most one fund family.

The characteristics of a fund family include a unique identification, a name, and a address. A fund family may have mutual funds and must be associated with at least one mutual fund.

                     

Establish join paths for the above relational database using the referential integrity by drawing arrow lines between the above tables. Indicate all the foreign keys (FK).

F.K.    ->   P.K.

Do function analysis for each of your tables

       Attribute A -> Attribute B (Determinant attribute(s)   Determines Dependent Attribute(s))

Show all the normalized tables and indicate the normalization form for each of your tables.

Table Name	1NF	2NF	3NF

Answer 1

Solution: