#COIT20247 Data Modeling

[ultimateguitar]

assignment help australia

Buy assignment Sydney,Buy assignment Australia,online assignment help,my assignment help australia,assignment help australia,online assignment writer in sydney,assignment help sydney,java assignment help australia,python assignment help,java assignment help,hp assignment help,c/c++ assignment help,android assignment help,

database assignment help,Programming assignment help,web design assignment help,joomla assignment help,Wordpress assignment help,accounting assignment help,business accounting assignment help,management assignment help,marketing assignment help,human resources assignment help,law assignment help,online assignment help sydney,java coursework help,COIT20247 Data Modeling,COIT20247 Designing and Implementing,COIT20262 Advanced Network Security,COIT20250 E-Portfolio - Assignment 1,COIT20261-Network Routing and Switching,COIT20263 Information Security Management,COIT20263 Information Security Management,COIT20268 Assignment Help,COIT20246 ICT Services Management

[assignmentsolutions4me]

COIT20247 | Assessment Item 1 Draw an Entity Relationship | Database

Objectives

To develop a conceptual data model diagram

To perform logical design (optional)

Assignment Specification

In this assignment, you need to perform the following tasks for the given case study:

Draw an Entity Relationship (ER) diagram

Perform logical design (optional)

Create 3NF Relations (optional)

Case Study

CQ Insurance (CQI) offers insurance policies to insure Home-buildings and Home-Contents of Australian residents.  For each of their properties (Homes) of interest, the customers can apply for either a Home-buildings policy or a Home-contents policy or a combined Home-buildings-contents policy.  CQI allows a customer to have only one of the above mentioned policies for each of his/her properties.

When new customers approach for an insurance policy (hereafter called as policy), CQI collects their details so that they can communicate with them in future. Each policy requires full premium payment prior to the coverage period of one year.  At the time of preparing the policies, CQI collects the details of the property (Home) and/or items (Home contents) depending upon the type of the policy that the customer is interested in.

At the time when a customer lodges an insurance claim (hereafter called as claim) such as collapse of false ceiling, loss of home contents, etc., CQI ensures that the insurance policy has not lapsed prior to processing the claims and further verifies that the items claimed have been covered by the respective policy.  A claim should be related to only one policy but each claim is not limited to only one item that has been covered by the policy.  As part of the claim-processing for each of the claims, an assessor (appointed by CQI) prepares and submits an initial assessment and if required the assessor also prepares and submits a final assessment.  The assessor’s initial assessment may recommend either to further process or reject the related claim. The assessor’s final assessment will provide the total amount that can be settled relating to the claim and may contain a memo that describes the recommended settlement amount for each of the items that have been claimed.

Note:

The attributes of the possible entities have not been mentioned clearly.  Hence, you need to assume at least two and at most nine relevant attributes for each of the entities that you will identify for your ER diagram.

In addition to the recommendations in the assessor’s report, the following processes can also provide the reasons for rejection of the claim:

Verification to ascertain whether the claimed items have been covered by the respective policy or not

Ensuring that the policy is in force (has not lapsed) at the time of lodging the claim

Assignment Requirements

In this assignment, you need to create an Entity Relationship (ER) diagram relevant to the above case study and optionally perform logical design to produce appropriate 3NF Relations.

The attributes for various entities have not been purposely described in the case study so that the students are encouraged to further research and list the pertinent attributes in addition to the required identifier for each and every entity in their ERD.

Draw an appropriate ER diagram

Use the symbols as prescribed in your unit-textbook to draw the ER diagram (ERD) for the above case study.

Your ERD must

show all necessary entities, attributes and relationships

show identifiers/identifying attributes as necessary

show multi-valued attributes, if any

show participation and cardinality

show associative entities, if appropriate

show weak entities, if any

use the notation described in the set text

use consistent and appropriate naming for entities and attributes as specified in the unit text book

Some business rules or other aspects of the case study may not be clear to you when you read the case study. If this is the case, then you should either approach your lecturer or tutor for clarification, or you may simply make an assumption and then develop your ERD accordingly. For example, the case study might not mention all relevant participation information (also called minimum cardinalities). If so, you may make an assumption about what the minimum cardinalities might reasonably be, and then show them in your ERD accordingly. You should justify each assumption in terms of the business, for example: it is assumed that each customer must have at least one policy because it is assumed that the business does not record customer details until the customer opts for a policy with them. To get yourself started, ask yourself, 'If I were running this business, what things I need to keep a list of?'  Write those things down.  For each thing, what information would you need to record about it? How can it be identified? The answers to these questions will help you to develop your ERD.

Assumptions and Business rules

Provide assumptions and business rules relevant to your ERD.

Create Logical Design (Optional):

If you would like to perform this optional component of this assignment, you can map your Entity Relationship diagram (ERD) into relations and make sure that all the relations are in 3NF.  Provide all the relations in the following format:

Customer (CustomerId, customerName, Street, Suburb, State, PostCode, Email)

Invoice (InvoiceNumber, invoiceDate, Total, CustomerId)

foreign key (CustomerId) references Customer(CustomerId)

How and what to submit:

You must make an electronic submission for this assignment using the appropriate assignment submission link in the unit web site.

You should submit a Microsoft Word document containing:

A copy of your ERD.

A list of business rules and assumptions including their justifications.

3NF Relations – optional

Your document should contain appropriate identifying information (your student number, name, unit number, etc.) and should contain appropriate headings for each section.

Important points

You must work individually on this

The model solution to this assignment will be released approximately TWO (2) weeks after the due date.

Assignments that are submitted after the release of the model solution will receive zero marks.

You must state in your assignment (at the end under the heading Acknowledgements is a good place) all materials and sources you used. This may be as simple as 'This assignment was completed using only the COIT20247 unit materials' to mention that you have used only the items provided by the unit COIT20247.

Before submitting your assignment, review the marking criteria that are available in the assignment specification. Ensure that all the criteria are addressed in your submission.

After submitting your assignment, please verify that your submission was successful, e. download your submitted files to ensure that they are correct.

Penalty related to late submission and plagiarism is handled as per the university policies.

Order Now

[grossjessica08]

COIT20247 Database Design and Development

Assessment item 1— Entity Relationship Diagram (ERD) Objectives

This assessment item is to develop a conceptual data model based on the following case study. It relates to the unit learning outcome numbers 1 and 3 in the unit profile.

Assessment task

In this assignment, you are going to perform the entity-relationship data modelling based on the following application scenario.

The application scenario: High Frequency Investments (HFI) Australia High Frequency Investments (HFI) is a leading investment management company in Australia with the headquarter office in Brisbane, Queensland. It was founded by a group of professional funding managers and security analysts. Specialising in investing Australian stock market, it provides investment & consultant services to private clients and institutional investors. With the new developed computational intelligence – based trading technique, its business has been booming in recent years. HFI needs a relational database to record information related to its analysts, managers, clients served, analysts' recommendations for stocks, portfolios managed, trade details, as well as the share registry.

The employees of HFI mainly consist of security analysts and portfolio managers. The duty of the analysts is to regularly review various stocks listed at Australian Security Exchange (ASX) and make recommendations based on their extensive research, including a comprehensive fundamental analysis and a thorough technical analysis. Each analyst is recorded with his/her ID, full name, phone number and expertise area. The recommendation could be ‘buy’, ’long term buy’, ’sell’, and ’hold’, with a brief explanation. Of course the revenue and profits of a listed company and its share price could be varied at any time under different internal and external environment so the recommendation should be date-sensitive.

This means that a ‘sell’ recommendation for a particular stock at a date could be upgraded as ‘buy’ recommendation at other dates with the new information input. The stock can be described with the share code, company name, section, and listed year. In ASX, a 3-letter code can uniquely identify an ordinary stock. As an example, the stock share of the Rio Tinto has the code RIO. It belongs to the materials section, with the dividend yield 4.5% approximately and the listed year was 1962. You can search the websites on the information for representative listed companies in Australia (such as BHP, Wesfarmers, Telstra and Commonwealth bank etc.). In addition, each stock is associated to a share registry which provides stock transfer service. In Australia, there are only limited institutions (like Computershare, Linked Market Service and Boardroom etc.) providing such a service. The name of share registry and its home webpage URL is required to record in the developed database system.

The information on portfolio managers must also be recorded in the database. This includes his/her ID, name, phone, address, and a brief description of their work experience. Each manager may manage multiple portfolios. Each portfolio has its own ID. However a portfolio should be only associated with one client. In addition the portfolio should contain the data like available cash. Although HFI provides service for all aspects of the financial asset management, the database to be designed in this case study only concerns its core business – stock trading of ordinary shares.

When a portfolio manager decides to trade a stock, he/she will trade the stock for a particular portfolio. In other words, the managers trade the stocks on behalf of clients rather than for themselves. The database will track the trade details such as trade type (buy or sell), quantities traded, price per share and transaction date. The clients normally can be categorized into three types: individual, trustee and corporate investor. Their IDs, names, addresses, and contact phones should be recorded. In addition, the tax file numbers (TFN) of an individual client is required to provide. For a trustee client, a corresponding trust account is required to nominate; while for a corporate investor, an Australian Business Number (ABN) is essential.

You are required to develop an ER model to represent the information requirements for the application scenario (HFI) as described on last page.

Your ER model must:

show all necessary entities, attributes and relationships

show unique identifiers

show multi-valued attributes (also called repeating attributes), if any

show participation and cardinality

show associative entities, if appropriate

use the notation described in the set text

use consistent and appropriate naming for entities and attributes throughout

Some business rules or other aspects of the case study may not be clear to you when you read the case study. If this is the case, then you should either approach your lecturer or tutor for clarification, or you may simply make an assumption and then develop your ER model accordingly. For example, the case study might not mention all relevant participation information (also called minimum cardinalities). If so, you may make an assumption about what the minimum cardinalities might reasonably be, and then show them in your ER model accordingly. You should justify each assumption in terms of the business, for example: it is assumed that each customer must have at least one order because it is assumed that the business does not record customer details until the customer places an order.

To get yourself started, ask yourself, 'If I were running this business, what things I would need to keep a list of?' Write those things down. For each thing, what information would you need to record about it?

Entity and attribute definitions

You are required to write brief definitions for the entities and attributes in your model. For each entity and attribute in your model, write a sentence to describe the meaning of that entity or attribute. You should refer to the sections Naming and Defining Entity Types and Naming and Defining Attributes in the textbook. For example:

STUDENT: A Student is a person who has enrolled in one or more programs at the university. This includes Start Uni Now high school students but does not include prospective students.

Student No: Uniquely identifies each student.

Student Name: The full name of the student.

Date of Birth: The student’s date of birth.

PROGRAM: A Program is a program of study available at the university.

Program code: Uniquely identifies each program

Program name: The full name of the program.

What you should submit

You should submit a Microsoft Word document containing:

A copy of your entity-relationship model(s).

A list of business rules that are apparent from the case study.

A list of any assumptions that you made (include any clarifications from your tutor) with justifications.

Your entity and attribute definitions.

A set of 3NF relations that are from the mapping of ER model. (This item is optional. See a note below.)

Your document should contain appropriate identifying information (your student number, name, course number, etc.) and should be appropriately formatted. For example, your document should contain a very brief introduction stating the purpose of the document, as well as headings for each section.

Note: Due to some campus having weekend lectures and tutorials and the topic of 3NF in Week 5, the assessment on the transforming the ER data model to 3NF relations is optional in Assignment 1. You can include it in your word document if you can complete but no marks will be allocated to it. In Assignment 2, you must include the mapping of relations into 3 NF.

Important points

You must work individually on this assignment.

The model solution to this assignment will be released approximately TWO (2) weeks after the due date.

Assignments that are submitted after the release of the model solution will receive zero.

You must state in your assignment (at the end under the heading Acknowledgements is a good place) all materials and sources you used. This may be as simple as 'This assignment was completed using only the COIT20247 course materials' if that was all you used.

Extension requests must be submitted online (for AIC students, contact your local campus administration staff) before the assignment due date. A penalty of 5% of the total mark per calendar day will apply for late submissions without an extension, or zero if submitted after the release of the model solution.

Before submitting your assignment, review the marking criteria as shown on the marking guide. Ensure that all the criteria are addressed in your submission.

After submitting your assignment, please verify that your submission was successful, i.e. download your submitted files to ensure they are correct and uncorrupted.

Order Now

[assignmentsolutions4me]

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 3rd 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 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.

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.

Order Now

[assignmentsolutions4me]

COIT20247 Database Design and Development

Assessment item 1— Entity Relationship Diagram (ERD)

Objectives

This assessment item is to develop a conceptual data model based on the following case study. It relates to the unit learning outcome numbers 1 and 3 in the unit profile.

Assessment task

In this assignment, you are going to perform the entity-relationship data modelling based on the following application scenario.

The application scenario: High Frequency Investments (HFI) Australia High Frequency Investments (HFI) is a leading investment management company in Australia with the headquarter office in Brisbane, Queensland. It was founded by a group of professional funding managers and security analysts. Specialising in investing Australian stock market, it provides investment & consultant services to private clients and institutional investors. With the new developed computational intelligence – based trading technique, its business has been booming in recent years. HFI needs a relational database to record information related to its analysts, managers, clients served, analysts' recommendations for stocks, portfolios managed, trade details, as well as the share registry.

The employees of HFI mainly consist of security analysts and portfolio managers. The duty of the analysts is to regularly review various stocks listed at Australian Security Exchange (ASX) and make recommendations based on their extensive research, including a comprehensive fundamental analysis and a thorough technical analysis. Each analyst is recorded with his/her ID, full name, phone number and expertise area. The recommendation could be ‘buy’, ’long term buy’, ’sell’, and ’hold’, with a brief explanation. Of course the revenue and profits of a listed company and its share price could be varied at any time under different internal and external environment so the recommendation should be date-sensitive.

This means that a ‘sell’ recommendation for a particular stock at a date could be upgraded as ‘buy’ recommendation at other dates with the new information input. The stock can be described with the share code, company name, section, and listed year. In ASX, a 3-letter code can uniquely identify an ordinary stock. As an example, the stock share of the Rio Tinto has the code RIO. It belongs to the materials section, with the dividend yield 4.5% approximately and the listed year was 1962. You can search the websites on the information for representative listed companies in Australia (such as BHP, Wesfarmers, Telstra and Commonwealth bank etc.). In addition, each stock is associated to a share registry which provides stock transfer service. In Australia, there are only limited institutions (like Computershare, Linked Market Service and Boardroom etc.) providing such a service. The name of share registry and its home webpage URL is required to record in the developed database system.

The information on portfolio managers must also be recorded in the database. This includes his/her ID, name, phone, address, and a brief description of their work experience. Each manager may manage multiple portfolios. Each portfolio has its own ID. However a portfolio should be only associated with one client. In addition the portfolio should contain the data like available cash. Although HFI provides service for all aspects of the financial asset management, the database to be designed in this case study only concerns its core business – stock trading of ordinary shares.

When a portfolio manager decides to trade a stock, he/she will trade the stock for a particular portfolio. In other words, the managers trade the stocks on behalf of clients rather than for themselves. The database will track the trade details such as trade type (buy or sell), quantities traded, price per share and transaction date. The clients normally can be categorized into three types: individual, trustee and corporate investor. Their IDs, names, addresses, and contact phones should be recorded. In addition, the tax file numbers (TFN) of an individual client is required to provide. For a trustee client, a corresponding trust account is required to nominate; while for a corporate investor, an Australian Business Number (ABN) is essential.

You are required to develop an ER model to represent the information requirements for the application scenario (HFI) as described on last page.

Your ER model must:

show all necessary entities, attributes and relationships

show unique identifiers

show multi-valued attributes (also called repeating attributes), if any

show participation and cardinality

show associative entities, if appropriate

use the notation described in the set text

use consistent and appropriate naming for entities and attributes throughout

Some business rules or other aspects of the case study may not be clear to you when you read the case study. If this is the case, then you should either approach your lecturer or tutor for clarification, or you may simply make an assumption and then develop your ER model accordingly. For example, the case study might not mention all relevant participation information (also called minimum cardinalities). If so, you may make an assumption about what the minimum cardinalities might reasonably be, and then show them in your ER model accordingly. You should justify each assumption in terms of the business, for example: it is assumed that each customer must have at least one order because it is assumed that the business does not record customer details until the customer places an order.

To get yourself started, ask yourself, 'If I were running this business, what things I would need to keep a list of?' Write those things down. For each thing, what information would you need to record about it?

Entity and attribute definitions

You are required to write brief definitions for the entities and attributes in your model. For each entity and attribute in your model, write a sentence to describe the meaning of that entity or attribute. You should refer to the sections Naming and Defining Entity Types and Naming and Defining Attributes in the textbook. For example:

STUDENT: A Student is a person who has enrolled in one or more programs at the university. This includes Start Uni Now high school students but does not include prospective students.

Student No: Uniquely identifies each student.

Student Name: The full name of the student.

Date of Birth: The student’s date of birth.

PROGRAM: A Program is a program of study available at the university.

Program code: Uniquely identifies each program

Program name: The full name of the program.

What you should submit a Microsoft Word document containing:

A copy of your entity-relationship model(s).

A list of business rules that are apparent from the case study.

A list of any assumptions that you made (include any clarifications from your tutor) with justifications.

Your entity and attribute definitions.

A set of 3NF relations that are from the mapping of ER model. (This item is optional. See a note below.)

Your document should contain appropriate identifying information (your student number, name, course number, etc.) and should be appropriately formatted. For example, your document should contain a very brief introduction stating the purpose of the document, as well as headings for each section.

Note: Due to some campus having weekend lectures and tutorials and the topic of 3NF in Week 5, the assessment on the transforming the ER data model to 3NF relations is optional in Assignment 1. You can include it in your word document if you can complete but no marks will be allocated to it. In Assignment 2, you must include the mapping of relations into 3 NF.

Important points

You must work individually on this assignment.

The model solution to this assignment will be released approximately TWO (2) weeks after the due date.

Assignments that are submitted after the release of the model solution will receive zero.

You must state in your assignment (at the end under the heading Acknowledgements is a good place) all materials and sources you used. This may be as simple as 'This assignment was completed using only the COIT20247 course materials' if that was all you used.

Extension requests must be submitted online (for AIC students, contact your local campus administration staff) before the assignment due date. A penalty of 5% of the total mark per calendar day will apply for late submissions without an extension, or zero if submitted after the release of the model solution.

Before submitting your assignment, review the marking criteria as shown on the marking guide. Ensure that all the criteria are addressed in your submission.

After submitting your assignment, please verify that your submission was successful, i.e. download your submitted files to ensure they are correct and uncorrupted.

Order Now