Securing Higher Grades Costing Your Pocket? Book Your Assignment at The Lowest Price Now!
loader
Add File

Error goes here

Files Missing!

Please upload all relevant files for quick & complete assistance.

Guaranteed Higher Grade!

COIT20247 Database Design And Development

tag 0 Download 3 Pages / 528 Words tag 01-02-2021

Question:

The sample solution of assignment 1 will be released on the unit website approximately two (2) weeks after the assignment 1 due date.  You must use that sample solution of assignment 1 only for the following tasks:

Assessment task

1 Normalization

a)Map the ERD, from the sample solution, into a set of relations in at least Third Normal Form (3NF). You must ensure that your relations meet 3NF. There is no need to show your working.

b)Select any two (2) of your relations from the previous step, 1a), and perform the following for each of those two relations:

  • List all the functional dependencies exist in the relation.
  • Demonstrate that the relation meets Third Normal Form (3NF).

i.The primary key is StudentID which identifies a student.  There will be one student name per Student ID because only one name is recorded for each student. Even though a student may have multiple addresses (e.g. a home address and a work address), the case study states that there is a need to record the home address only and hence there will be only one address for each student. There will be only one date of birth for each student.  Thus, there are no repeating groups. Hence, this relation is in 1NF.

ii.The primary key is StudentID.  This is the only candidate key since Name is not guaranteed to be unique (two different students may have the same name).  Address is also not guaranteed to be unique. This means that Student ID functionally determines every other attribute in the table.  There is a single valued simple candidate key (StudentId) and therefore no partial dependencies are possible.  Hence, the relation is in 2NF.

iii.Name cannot be used to functionally determine any other attribute in the table since two different students may have the same name; likewise for address and date of birth. Therefore there are no transitive dependencies in the table.  Therefore it meets the requirements of first, second and third normal form.

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.

2.Display the details of appointment, name of the patient and medical specialist involved in the most expensive appointment.

3.For each of the medical specialists, display his/her name and the number of appointments completed so far.

4.For each of the appointment completed, 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 )

5.Which patients have consulted more than one specialist? Display the details of such patients only.

6.Display the details of the patient(s) whose name contains the word “more”.  Show the results in ascending order of the name.

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 3 pages

Cite This Work

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

My Assignment Help (2021) Database Design And Development [Online]. Available from: https://myassignmenthelp.com/free-samples/coit20247-database-design-and-development/drawing-the-entity-relationship-diagram.html
[Accessed 26 November 2022].

My Assignment Help. 'Database Design And Development' (My Assignment Help, 2021) <https://myassignmenthelp.com/free-samples/coit20247-database-design-and-development/drawing-the-entity-relationship-diagram.html> accessed 26 November 2022.

My Assignment Help. Database Design And Development [Internet]. My Assignment Help. 2021 [cited 26 November 2022]. Available from: https://myassignmenthelp.com/free-samples/coit20247-database-design-and-development/drawing-the-entity-relationship-diagram.html.


Stuck on Any Question

Our best expert will help you with the answer of your question with best explanation.

question
We will use e-mail only for:

arrow Communication regarding your orders

arrow To send you invoices, and other billing info

arrow To provide you with information of offers and other benefits

loader
250 words
Error goes here

Error goes here

Files Missing!

Please upload all relevant files for quick & complete assistance.

We Can Help!

Get top notch assistance from our best tutors !
Excel in your academics & career in one easy click!

icon

Other Samples

Content Removal Request

If you are the original writer of this content and no longer wish to have your work published on Myassignmenthelp.com then please raise the content removal request.

icon

5% Cashback

On APP - grab it while it lasts!

Download app now (or) Scan the QR code

*Offer eligible for first 3 orders ordered through app!

screener
ribbon
callback request mobile
Have any Query?