Oracle Database Analysis and Design

Need help on questions 4-7, please

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 companys 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 stocks 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 funds 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 funds 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.

1. Extract Entity Types and Identify Relationship Matrix

Based on the given Investment Portfolio Scenario in Section 3.1, four external entity types have been extracted. They are identified as: Client, Stock, Mutual Fund and Family Fund

2. Generated Business rulesThe three pairs of relationships are further defined as the database business rules as follows:

One client may invest in one or more than one stock.

A stock may be bought by one or more than one client.

One client may invest in one or more than one mutual fund.

A mutual fund may be bought by one or more than one client.

One fund family must have one or more than one mutual fund.

One fund must belong to only one fund family.

3. Draw an ER diagram with

1) entity types, 2) relationship types, 3) keys, 4) cardinality and 5) PKs and FKs

Database Logical Design

4. Map the ER diagram to a relational database schema indicating the relation name, primary key and foreign key. Add appropriate additional attribute by yourself.

Table Name

Table Name

Table Name

Table Name

Table Name

Table Name

5. 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.

6. Do function analysis for each of your tables

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

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

Table Name 1NF 2NF 3NF