Fill This Form To Receive Instant Help

Help in Homework
trustpilot ratings
google ratings


Homework answers / question archive / The database used for this question is based upon the following relational schema that represents a veryPrimary Key simplified medical billing database (, Foreign Key): Patient (PatientID, FamilyName, GivenName, Address, Suburb, State, PostCode) Doctor (ProviderNo, Name) Item (ItemNo, Description, Fee) Account (AccountNo, ProviderNo, PatientID, Date) AccountLine (AccountNo, ItemNo) The Patient relation contains data about patients treated at a typical medical surgery

The database used for this question is based upon the following relational schema that represents a veryPrimary Key simplified medical billing database (, Foreign Key): Patient (PatientID, FamilyName, GivenName, Address, Suburb, State, PostCode) Doctor (ProviderNo, Name) Item (ItemNo, Description, Fee) Account (AccountNo, ProviderNo, PatientID, Date) AccountLine (AccountNo, ItemNo) The Patient relation contains data about patients treated at a typical medical surgery

Computer Science

The database used for this question is based upon the following relational schema that represents a veryPrimary Key simplified medical billing database (, Foreign Key): Patient (PatientID, FamilyName, GivenName, Address, Suburb, State, PostCode)

Doctor (ProviderNo, Name)

Item (ItemNo, Description, Fee)

Account (AccountNo, ProviderNo, PatientID, Date)

AccountLine (AccountNo, ItemNo)

The Patient relation contains data about patients treated at a typical medical surgery. The details of the Doctors are contained in the Doctor relation. The ProviderNo attribute is a unique code allocated to each registered medical practitioner in Australia. The Item relation contains the details of treatment items, including the ItemNo which is a unique code allocated to each treatment item.

When a patient visits the Doctor, an Account is created. A Patient can only be treated by one Doctor on a particular visit. An Account can have several lines, each of which list the item number of the treatment provided. A Patient can have more than one account for a day.

Please ensure that you include the result table as well as your SQL; you can copy and paste this from either your ssh client or SQL Developer. Each query is worth 2 marks. These tables exist in arion and are owned by the user dtoohey.

  1. Family name and suburb of patients who live in the State named 'WA'
  2. Name and suburb of patients who live in the State named 'WA' and have been treated by Dr Brian or Dr Barbara
  3. Name and suburb of patients treated by Dr Brian but not Dr Barbara
  4. Name and suburb of patients who have been treated by both Dr Brian and Dr Barbara
  5. Item Description and the treatment date of all treatments for any patient named Jessie Stange (i.e., Given name is Jessie, family name is Stange)
  6. Name of patients who have been treated by Dr Brian and who have had an Extended Consultation
  7. Total number of patients in each of the different States of Australia
  8. Total number of treatments for each item number
  9. Total amount of fees charged to any Patient named Jessie Stange
  10. Name of any patient who, over the lifetime of the database, received all of the treatment items

 

 

You have been given the following specifications of a simple database for keeping track of lectures and those who presented the lecture at a national Biological Sciences conference (note that primary keys are shown underlined, foreign keys in bold).

You should run your SQL to demonstrate that it works correctly, and paste in the statements used plus the output from Oracle.  

PRESENTER (PresenterNo, PresenterName, Biography, InstitutionName)

LECTURE (LectureNo, LectureName, Description, Theme, Capacity, DateAndTime, PresenterNo)

Based on the table specifications provided, answer the following questions. Each question is worth 3 marks.

a.      Give the SQL to create the PRESENTER table. Choose appropriate data types.  None of the attributes should be allowed to be null. Include the primary key constraint.

b.     Give the SQL to create the LECTURE table. Use appropriate data types, and include the primary key and foreign key constraints. Referential integrity should be set such that if a presenter is deleted from the database, any lectures that he or she is running will also be deleted.

c.      Give the SQL to add your own record to the PRESENTER table. Include your name, 'Murdoch University' as your institution, and make up a short biography.

d.     Give the SQL to add the attribute VenueName to LECTURE.  There are three possible venues: Building A, Building B and Building C, which should be enforced by the SQL definition.

e.      Give the SQL to record the fact that all lectures have had their capacity increased by 10.

Purchase A New Answer

Custom new solution created by our subject matter experts

GET A QUOTE