COIT20247 | Database Design and Development | Database

Assessment task

1. Normalization

A. You have to map the ERD diagram, into a set of relations in at least 3^rd normal form. You must make sure that your relation meets third normal form.

B. You have to select any two of your relations from the previous step and perform the following for each of those two relations:

a. List all the functional dependencies exist in the relation
b. Demonstrate that the relation meets third normal form

2. Relation database implementation

You have to create in the above 1 point normalization to make a. The recommended tool for this assessment is Microsoft Access.

In the ER model provided, you should complete the following task:

1. Create all the relations in Microsoft Accesses database. You have to consider each attribute in every table and prepare suitable choices regarding data types, size, indexes, required and validation rules. Your choice should be suitable for every attribute and should support data integrity.

2. You have to create relationships as suitable. Referential integrity for all suitable relationship in the database.

3. Review the default index created by Access for each table

4. Populate the database with sample data of your own. You must include sufficient sample data to test your queries and report.

Data integrity

You are needed to implement integrity constraints within the database to enforce the following needs:

1. Price Per Share should be positive currency and expressed in 2 decimals only.

2.  The tax file number in the individual investors must be a 9-digit number.

3.  The recommendation date and trade date can’t future-dated.

4.  The value of recommendation type from analysts should be limited to ‘buy’, ‘sell’, ‘hold’, ‘long-term buys’, ‘light positions’ and ‘heavy positions’.

5.  Stock code must contain 3 letters only

6.  The quantities in a single trade are expected not exceeding 1,000,000.

                                                                Order Now

Information requests

Create queries to answer the following questions:

1. Display the details of the stocks which have not been recommended. (2 marks)

2. How many recommendations have been made from each analyst? List the analyst name, the number of recommendation and order the output by the analyst the last name. (2 marks)

3. Which bank stocks were traded more than twice? List the stock code, the company name as well as the number of trade. (2 marks)

4. Show the details of stocks purchased during last six months. Be sure that upcoming trades don’t appear in your output but current day’s trades are included. You should show the stock code, quantities of purchase, price per share and trade date. Order the output by the date of the trade.(2 marks)

5. Show a list of all recommendations made before 1 July 2017 on the stock’s company name containing the word ‘energy’. The list includes analyst name, recommendation type, reason and date. (2 marks)

6. Display the details of the total amount of Bank share that each portfolio owns and the corresponding portfolio manager name. (2 marks)

Report

You have to make a simple report object viewing the details of all stocks that corporate investors accepted in their portfolios. These details contain investor name, stock code, quantities of each stock as well as the date purchased.

Implementation report

1. Implementation report discusses appropriate issues

2. Sub-total before any possible deductions Late penalty

Marking criteria (35 weight total)

1. Relational database implementation (12 marks)

a. Set of relations correctly mapped from ERD(3 marks)

b. Functional dependencies correctly listed for two of the relations (2 marks)

c. Normalization correctly listed for two of the relations (2 marks)

d. Relationships set up correctly, referential integrity enforced, appropriate use of cascade update/delete (2 marks)

e. Data types & sizes selected appropriately (2 marks)

f. indexes – Indexes are appropriate (1 marks)

2. Data integrity (3 marks)

a. Data integrity correctly implemented (3 marks)

3. Queries (12 marks)

4. Report (4 marks)

a. Underlying query/queries has/have been created and are correct (2.5 marks)

b. Report layout and heading are appropriate (1.5 marks)

5. Implementation report (4 marks)

Challenges students may face

Students face off various problems in completing this assessment such as understanding the complex problems of making references, lack of writing skills, data integrity and making report etc. Students can take help and guidance from our technical experts and get better grades in their assessments.