fb
$20 Bonus + 25% OFF
$20 Bonus + 25% OFF
Securing Higher Grades Costing Your Pocket? Book Your Assignment at The Lowest Price Now!
Add File

Error goes here

COIT20247 Database Design And Development

tag 0 Download2 Pages / 484 Words

Question:

Assessment task 

1 Normalization

  1. Map the ERD, from the sample solution, into a set of relations in at least Third Normal Form (3NF). You must ensurethat your relations meet 3NF. There is no need to show your 
  2. Select any two (2) of your relations from the previous step, 1a), and perform the following for each ofthose two relations:
    • List all the functional dependencies exist in the
    • Demonstrate that the relation meets Third Normal Form (3NF).

2 Relational database implementation

Note: Use the relations that you created in the above step 1-Normalization to create a database. The recommended tool for this work is Microsoft Access, version 2010 or later. One reason for recommending Microsoft Access is that your unit lecturer/tutor will be able to provide adequate support to you in the use of this tool. 

If you choose to use a different DBMS, then there is no guarantee that your lecturer/tutor will be able to provide enough support to you.  The remainder of this document assumes that you are using MS-Access.

In implementing the ER model provided, you must complete the following tasks:

  • Create all the relations in a Microsoft Access database. Consider each attribute in every tableand make appropriate choices regarding data types & sizes, indexes, required/not required and validation rules. Your choices should be appropriate for each attribute and should support data integrity. (Note: see the Data Integrity section below for specific data integrity requirements)
  • Create relationships as appropriate. Enforce referential integrity for all appropriaterelationships in the database. Apply cascade update and/or delete options wherever necessary.
  • Review the default index created by Access for each table. You should ensure that theindexes follow the guidelines given in the unit 
  • Populate the database with sample data of your own. You must include sufficient sample data to testyour queries and report.  Please note that the expected result of query questions may depend on the actual sample data 

Data integrity

You are required to implement integrity constraints within the database to enforce the following requirements:

  • Fees/Charges should be positive currency and expressed in 2 decimals only.
  • Date values should not be predated.
  • Name of patients/medical specialists should not be null.
  • State values should be limited to Australian States/Territories only.
  • Skills and experience values should not be null.
  • Discount amount should be positive and cannot exceed $50.

Information requests

Create queries to answer the following information requests. Note: Do not use the Access query builder (QBE) to create your queries – you should type the queries manually using SQL view/editor.

The marking process of the queries for information requests may also consider the effectiveness of your SQL statements that have been used in the queries. It is important that your SQL statement must reflect the correct business logic & SQL syntax.  (Use enough sample data so that the output is produced by each and every query).

  1. Display the details of medical specialist(s) who has/have never performed/completed any appointment.
  1. Display the details of appointment, name of the patient and medical specialist involved in the most expensive appointment.
  1. For each of the medical specialists, display his/her name and the number of appointments completed so far.
  1. For each of the appointmentcompleted, display the date, (start) time, name of the patient and total fees/charges of the appointment, discount amount and final amount payable. (Final amount payable = total appointment charges – discount amount )
  1. Which patients have consulted more than one specialist? Display the details of such patients only.
  1. Display the details of the patient(s) whose name contains the word “more”.  Show the results in ascending order of the name.

Report

Create a simple report object showing the details of skills and experience of the each of the medical specialists.  The details have to be presented in ascending order of the (first) name of the medical specialist.

Implementation Report

You must also provide a short implementation report describing your experiences with this assignment. You may discuss any of the following:

  1. Any two interesting things/procedures you learnt by doing this assignment.
  2. Any one requirement of the assignment that was relatively difficult / complex to solve but successfully completed by you.

Use the implementation report to demonstrate that you have thought carefully about the issues that arise when implementing a relational database.

Download Sample Now

Earn back the money you have spent on the downloaded sample by uploading a unique assignment/study material/research material you have. After we assess the authenticity of the uploaded content, you will get 100% money back in your wallet within 7 days.

Upload
Unique Document

Document
Under Evaluation

Get Money
into Your Wallet

Total 2 pages, 1 USD Per Page

Cite This Work

To export a reference to this article please select a referencing stye below:

My Assignment Help. (2021). Database Design And Development. Retrieved from https://myassignmenthelp.com/free-samples/coit20247-database-design-and-development/relational-database.html.

My Assignment Help (2021) Database Design And Development [Online]. Available from: https://myassignmenthelp.com/free-samples/coit20247-database-design-and-development/relational-database.html
[Accessed 27 October 2021].

My Assignment Help. 'Database Design And Development' (My Assignment Help, 2021) <https://myassignmenthelp.com/free-samples/coit20247-database-design-and-development/relational-database.html> accessed 27 October 2021.

My Assignment Help. Database Design And Development [Internet]. My Assignment Help. 2021 [cited 27 October 2021]. Available from: https://myassignmenthelp.com/free-samples/coit20247-database-design-and-development/relational-database.html.


MyAssignmenthelp.com is the number one assignment help provider in the world. Apart from providing authentic accounting assignment help, we also offer free access to our samples section. Hence, you get to clarify your doubts in an instant and gain further insight into the subject matter. You get to see an ample number of assignments, and there is no restriction on the number of times you access it.

Latest Database Samples

ITC566 Database System

Download : 0 | Page : 1

Answer:

ERD 1 (Task 1)

ERD 2 (Task 2)

ERD 3 (Task 3)

ERD 4 (Task 4) 

(TechTarget 2016)

(Cinergix Pty Ltd. 2011) 

References 

TechTarget (2016), entity relationship diagram, Retrieved from http://searchcrm.techtarget.com/definition/entity-relationship-diagram. 

Cinergix Pty Ltd. (2011), Ultimate Guide to ER Diagrams, Retrieved from http://creately.com/blog/diagrams/er-diagrams-tutorial/ 

Read More arrow

ITC516 Data Mining And Visualisation For Business Intelligence

Download : 0 | Page : 1

Answer: CCAvg Education Mortgage Personal Loan Securities Account CD Account Online CreditCard 1.6 1 0 0 1 0 0 0 1.5 1 0 0 1 0 0 0 1 1 0 0 0 0 0 0 1 2 0 0 0 0 0 1 1.5 2 0 0 0 0 1 0 0.6 2 104 0 0 0 1 0 8.9 3 0 1 0 0 0 0 2.4 3 0 0 0 0 0 0 2.5 2 0 0 0 0 1 0 1.5 3 0 0 0 0 1 1 0.9 2 111 0 0 0 1 0 2 ...

Read More arrow

ITC544 Computer Organisation And Architecture

Download : 0 | Pages : 2

Answers: 1: a). Single precision IEEE 754 format- 0 01111110 10100000000000000000000 The first MSB is the sign, the middle portion is the exponent and the last portion is the mantissa. Decimal conversion- 8.125 * 10^-1  b) 5-bit word- 0 1010 Signed magnitude- +10 (Most significant bit-0 [positive] rest 4 bits are magnitude) One’s complement- 10101 (all the 0’s are replaced by 1 and vice-versa) Two’s complement- ...

Read More arrow

CSE5DWD Data Warehouse Concepts And Design

Download : 0 | Pages : 7

Answer: Data Warehouse Bus Matrix: Business processes Dimensions Date Payment method Customer Employee Flights Membership Points Diagnosis Tool Products Agents Commission Flight management X     X X     X X X     Awarding programs and services X &nb...

Read More arrow Tags: Australia Truganina Computer Science Warehouse Concepts and Design University of Sydney Engineering 

ISYS1055 Database Concepts

Download : 0 | Pages : 9

Answer: Trivial Functional Dependencies: Table Name Attributes Dependencies Author Email Name, Address, Telephone1, Telephone2, Telephone3 Publisher Name Address, URL, ABN WrittenBy Email, ISBN Title Book ISBN Title, Edition, Year, ListPrice, PublisherName StokedAt ISBN, Code StockQty ShoppingCart CartID Timest...

Read More arrow Tags: Australia Deer Park Accounting Accounting theory and accountability University of New South Wales Managemnt 
Next

5% Cashback

On APP - grab it while it lasts!

Download app now

*Offer eligible for first 3 orders ordered through app!

ribbon
callback request mobile
Have any Query?