Assignment 2 specification

 

The assignment must be submitted electronically via the online assignment submission link on the study desk. Instructions will be provided on the course study desk.

If you are unable to complete the whole assignment, submit what you have done rather than nothing.

You must use the official USQ data modelling and normalization methodology. This methodology is based on Clive Finkelstein’s techniques (SR 2.1 and SR 2.2) and all the examples in the lectures, study book and the tutorials use this methodology.

If you do not use the USQ methodology, you will probably be awarded a mark of zero.

You might wish to use a word processor or a drawing tool. In either case, you must adapt the drawing to conform to the USQ methodology.

This assignment covers much of the same ground as the exam and will provide you with a strong indicator of your level of preparedness for the exam. Most of the questions are of examination standard.

 

 

 

 

 

 

SECTION A  (Data Modelling) (40 marks)           

 

A local pharmacy store in Wollongong wants to develop an online system to track their product inventory as well as prescription records of their products for regulatory requirements. As part of systems development, a data model is required to determine data entities and associated attributes and business rules. You are required to develop a data model which will allow the information and activities to be recorded.

 

The most important data entity for the system is the ITEM entity – this entity represents all the products in the pharmacy. Item code, shelf, location and quantity are the attributes for ITEM entity. Every time an item is ordered, some of the order details such as order date, order quantity and special instructions to the patient for each item in every order are stored. Patient information includes an id, patient name, date of birth, contact number and next of kin details. Each patient may have multiple orders of several items. While most orders are from patients this is not always the case. An order can be created without necessarily being associated with a patient, e.g. order from a casual customer buying items.

 

An item can be of two types: medical or non-medical. Understandably more than 95% of items in the pharmacy are medical items. For non-medical items, promotion period is an additional attribute to store since the pharmacy only sells non-medical items during promotions. Medical items are further classified into three types: doctor-prescription items, over-the-counter prescription items, and no-prescription items. Prescription records are required to be stored in the system. One doctor can prescribe multiple doctor-prescription items and vice versa. We store the prescription date and special instructions by the doctor prescribing a particular item. It is also important to store the doctor’s name, specialty and registration number from the Medical Board of Australia.

 

An over-the-counter prescription item must be given by one of the store’s pharmacists after consultation with the customer. Several over-the-counter items can be prescribed by different store pharmacists; therefore we also need to store the prescription date and special instructions by the pharmacist prescribing a particular item.  A pharmacist is one of two types of staff at the pharmacy store. The other type is general staff. For all staff, we store their name, date of birth. joined date and qualifications and level. For a pharmacist, we also need to store their registration number from the Pharmacy Board of Australia.

 

 

 

 

 

Prepare the following:

 

a)      An ER diagram for the system. Show all entities, relationships, cardinalities and optionalities. Also, include all intersection entities. You must use the Finkelstein methodology as per the study book and tutorials.                         (25 marks)

 

b)      A list of relations (equivalent to Finkelstein entity list). Produce complete relations for all entities and attributes. Show all primary and foreign keys. Include all attributes that are specifically mentioned and all key attributes. You may need to create primary and foreign keys that are not specifically mentioned. You must use the Finkelstein methodology as per the study book and tutorials.                                (10 marks)

 

c)      A SQL CREATE TABLE statement for the ITEM relation along with the creation of primary and foreign key constraints as required.                             (5 marks)

 

 

 

 

 

MARKING CRITERIA FOR SECTION A

 

1. Entities – no missing entities, appropriate names, no redundant entities, etc.

2. Cardinalities and optionalities all shown and correct.

3. Complete list of relations, showing all applicable attributes, primary keys and foreign keys.

4. Sophistication: well-presented solution; good layout; innovative approach; correct diagrams and notation; solution easy to read and understand; solution comprehensive

5. SQL CREATE TABLE uses an appropriate relation, is syntactically correct and meets the business rules.

6. USQ methodology used throughout.

 

 

SECTION B  (Normalisation) (20 marks) 

 

Produce a set of relations (equivalent to the Finkelstein entity list) in third normal form (3NF) from the following un-normalised relation for a flight reservation system. You must use the Finkelstein methodology as used in the study book and tutorials.

 

FLIGHT (departure airport code, departure airport name, departure time, departure date, arrival airport code, arrival airport name, arrival time, arrival date,  aircraft name, manufacture date, first class seats, business class seats, economy class seats, ((flight crew member, date joined, hours worked, crew member position, ((crew member role)) )), (( booking number, booking type, booking date, ((passenger surname, passenger first name, passenger dob, passenger contact number, seat number, special requests)), ticket price, booking fee)) )

 

Notes:

1. Name of an aircraft can uniquely identify any aircraft.
2. The number of seats in the economy, business and first class can be different for each aircraft.
3. Each flight has a service of several crew members. Each crew member has one position but can have multiple roles such as cabin service and supervisor that must be recorded.
4. The number of hours that each crew member worked in a particular flight regardless of their role is stored in the “hours worked” attribute.
5. A departure airport for one flight can be an arrival airport for another flight and vice versa. Hence, it is important to uniquely identify an airport.
6. A particular booking can be of two types: direct or through agent.
7. Ticket price can be different for every flight under different bookings.
8. Seat number and special requests by a passenger are associated with the corresponding flight and booking reference along with the passenger.

 

 

 

 

 

 

MARKING CRITERIA FOR SECTION B

 

1. Relations – no missing relations, appropriate names, no redundant relations.

2. All primary keys present and correctly notated.

3. All foreign keys present and correctly notated.

4. All attributes present.

5. All repeating groups resolved.

6. Derived attributes indicated in brackets.

7. All 2NF and transitive dependencies resolved.

8. All relations correctly notated using USQ methodology.

 

 

SECTION C (SQL) (40 marks)

For each question, four marks will be awarded for the query and one mark for the screen capture of the results obtained after executing the query. Please copy the screenshot and paste it in your assignment solution document for every question.

The following E-R diagram represents a Car Hire database.

I_CarGroup

I_Model

I_Car

I_Booking

I_Customer

 

 

 

 

 

 

 

 

 

 

 

 

In this question, you will use the CAR HIRE database. The CAR HIRE database including appropriate data will be made available on the USQ Oracle server. You may query any of these tables but may not make changes.

 

 

The table descriptions appear below, including the column names and data types.

 

I_CAR

Column Name	Type and Size	Constraints	Description
Registration	VARCHAR2(7)	NOT NULL	Registration number of the car. This is the Primary key.
Model_name	VARCHAR2(8)	FK	Model for the car. Foreign key into the Models table.
Car_group_name	VARCHAR2(2)	FK	Group code defining type of car and rental cost. Foreign key into the CarGroups table.
Date_bought	DATE		Date the car was purchased.
Cost	NUMBER(8,2)		The original cost of the car.
Miles_to_date	NUMBER(6)		The current mileage of the car as read at the end of the most recent rental.
Miles_last_service	NUMBER(6)		The mileage of the car when it was last serviced.
Status	CHAR(1)		The current status of the car. ‘A’ for available, ‘H’ for on hire, ‘S’ for being serviced, ‘X’ for in need of service or repair.

 

 

I_CARGROUP

 

Column Name	Type and Size	Constraints	Description
Car_group_name	VARCHAR2(2)	NOT NULL	The car group code. This will be one of the following values: ‘A1’, ‘A2’, ‘A3’, ‘A4’, ‘B1’, ‘B2’, ‘B3’, or ‘B4’. This column is the primary key for this table.
Rate_per_mile	NUMBER(3)		The charge per mile for cars in this group in cents.
Rate_per_day	NUMBER(5,2)		The rental charge per day for cars in this group in dollars and cents.

 

 

 

I_MODEL

 

Column Name	Type and Size	Constraints	Description
Model_name	VARCHAR2(8)	NOT NULL	The model name, an abbreviation of the full model name. This is the primary key for this table.
Car_group_name	VARCHAR2(2)	FK	The group to which this model of car belongs.
Description	VARCHAR2(30)		Full description of the model.
Maint_int	NUMBER(5)		Number of miles between services for this model.

 

 

I_CUSTOMER

 

Column Name	Type and Size	Constraints	Description
Cust_no	NUMBER(5)	NOT NULL	The customer account number. This is the primary key for this table.
Cust_name	VARCHAR2(20)	NOT NULL	The name of the customer.
Address	VARCHAR2(20)		Street address of the customer.
Town	VARCHAR2(20)		Town the customer lives in.
County	VARCHAR2(20)		County the customer lives in. Default is Australia
Post_code	VARCHAR2(10)		Postcode for the town.
Contact	VARCHAR2(20)		Name of person to contact.
Pay_method	CHAR(1)		Code to indicate the usual payment method for this customer. ‘A’ indicates an account, ‘C’ indicates cash or credit card, NULL indicates unknown.

 

 

I_BOOKING

 

Column Name	Type and Size	Constraints	Description
Booking_no	NUMBER(5)	NOT NULL	A serial number used to uniquely identify the booking. This is the primary key for this table.
Cust_no	NUMBER(5)	FK	Customer number of the customer making the booking.
Date_reserved	DATE		Date on which the booking was made.
Reserved_by	VARCHAR2(12)		Name of the person who took the reservation.
Date_rent_start	DATE		Date on which the rental commences.
Rental_period	NUMBER(3)		Length of rental period in days.
Registration	VARCHAR2(7)	FK	Registration of the car actually rented.
Model_name	VARCHAR2(8)		Model of the car rented.
Miles_out	NUMBER(6)		Miles on the odometer at the start of the rental.
Miles_in	NUMBER(6)		Miles on the odometer at the end of the rental.
Amount_due	NUMBER(6,2)		Cost of the rental. Calculated when the car is returned.
Paid	CHAR(1)		Flag to indicate if this rental has been paid for. ‘Y’ if it has been paid and ‘N’ if not.

 

 

 

 

Write SQL queries to solve the following specifications. Provide the queries and the output.

1.      Display full details of all car bookings where (a) the odometer reading of cars is greater than 30,000 miles at the start of their rental and the reservation was done by a staff called REDMOND, OR (b) the car has not been returned and the car model name ends with ‘TR’. Order the output by the date on which the booking was made, showing the most recent date first.

 

2.      Using a set operator, find out all names of the customers that have not had any bookings.

 

3.      Display the booking number, rental rate per day, rental period and the current status of the car for all car bookings. Filter your results to show only the records where the car is on hire and the rental rate per day is greater than $35.

 

4.      Display the car registration number and the average miles travelled in all booking by each car where the average miles travelled is less than 1,000 miles and registration number begins with H.

 

5.      Display the total rental income to date from the least expensive car available for rental. Format the output as a currency value (i.e. formatted as $9,999.99).

 

6.      Display the car registration, rental cost, mileage of the car when it was last serviced and current mileage of the car for all completed bookings where the car is rented more than 30 months after it was bought.

 

7.      Display booking number, date on which the booking was made and the cost of the rental for all the bookings made in the year 2012 and the car is already returned. Also include the name and full description of the corresponding model of the car used in the rental.

 

8.     

Nursingpapertutors.com places a premium on meeting deadlines. The company understands the significance of timely paper submissions for nursing students. To uphold their commitment to punctuality, Nursingpapertutors.com employs a well-organized workflow and efficient team coordination. This ensures that students receive their papers promptly, affording them ample time for review and making any necessary adjustments. Providing exceptional customer support is central to Nursingpapertutors.com’s mission. The company recognizes that nursing students may have queries or require assistance at any time of the day. As such, Nursingpapertutors.com offers 24/7 customer support to promptly address any concerns, clarify instructions, and offer updates on the paper’s progress. Their responsive support team ensures a seamless and positive experience for every student.

 

 

Do you need a similar assignment done for you from scratch? We have qualified writers to help you. We assure you an A+ quality paper that is free from plagiarism. Order now for an Amazing Discount!
Use Discount Code "Newclient" for a 15% Discount!

NB: We do not resell papers. Upon ordering, we do an original paper exclusively for you.

1) Write a paper (750 words) that addresses the Review Questions 2 and 3 in chapter 20 of the textbook. Legal Aspects of

Health Care Administration

Eleventh Edition

George D. Pozgar, MBA, CHE

Include a rationale for your answer

 

2. Using the hospital as a setting, give two examples of what would violate the NLRA.

 

3. How do patients’ rights come into play during a strike?

 

THE RUBRIC

 2) Prepare this assignment according to the APA guidelines.

 

3) This assignment uses a grading rubric

 

 

Top of Form Chapter 20 Review Questions 2-3     1 Unsatisfactory 0.00% 2 Less Than Satisfactory 74.00% 3 Satisfactory 79.00% 4 Good 87.00% 5 Excellent 100.00% 70.0 %Content   40.0 %Read chapter 20, and answer Review Questions 2 and 3, with rationale for the answers. Does not demonstrate understanding of the legal concepts in chapter 20, including the implications. Does not demonstrate critical thinking and analysis of the material and does not develop effective answers to the questions, with rationale. Demonstrates only minimal understanding of the legal issues and concepts in chapter 20. Demonstrates only minimal abilities for critical thinking and analysis, and develops weak answers to the questions, with minimal rationale Demonstrates knowledge of the legal issues on chapter 20, but has some slight misunderstanding of the implications. Provides a basic idea of critical thinking and analysis for the questions, answers, and rationale. Does not include examples or descriptions. Demonstrates acceptable knowledge of the legal issues in chapter 20 (in your own words). Develops an acceptable response to the questions and rationale for them. Utilizes some examples. Demonstrates thorough knowledge of the legal issues in chapter 20. Clearly answers the questions and develops a very strong rationale. Introduces appropriate examples.   30.0 %Integrates information from outside resources into the body of paper. Does not use references, examples, or explanations. Provides some supporting examples, but minimal explanations and no published references. Supports main points with examples and explanations, but fails to include published references to support claims and ideas. Supports main points with references, explanations, and examples. Analysis and description is direct, competent, and appropriate for the criteria. Supports main points with references, examples, and full explanations about how they apply. Thoughtfully, analyzes, evaluates and describes major points of the criteria.   20.0 %Organization and Effectiveness   7.0 %Assignment Development and Purpose Paper lacks any discernible overall purpose or organizing claim. Thesis and/or main claim are insufficiently developed and/or vague; purpose is not clear. Thesis and/or main claim are apparent and appropriate to purpose. Thesis and/or main claim are clear and forecast the development of the paper. It is descriptive and reflective of the arguments and appropriate to the purpose. Thesis and/or main claim are comprehensive. The essence of the paper is contained within the thesis. Thesis statement makes the purpose of the paper clear.   8.0 %Argument Logic and Construction Statement of purpose is not justified by the conclusion. The conclusion does not support the claim made. Argument is incoherent and uses noncredible sources. Sufficient justification of claims is lacking. Argument lacks consistent unity. There are obvious flaws in the logic. Some sources have questionable credibility. Argument is orderly, but may have a few inconsistencies. The argument presents minimal justification of claims. Argument logically, but not thoroughly, supports the purpose. Sources used are credible. Introduction and conclusion bracket the thesis. Argument shows logical progression. Techniques of argumentation are evident. There is a smooth progression of claims from introduction to conclusion. Most sources are authoritative. Clear and convincing argument presents a persuasive claim in a distinctive and compelling manner. All sources are authoritative.   5.0 %Mechanics of Writing (includes spelling, punctuation, grammar, language use) Surface errors are pervasive enough that they impede communication of meaning. Inappropriate word choice and/or sentence construction are used. Frequent and repetitive mechanical errors distract the reader. Inconsistencies in language choice (register), sentence structure, and/or word choice are present. Some mechanical errors or typos are present, but are not overly distracting to the reader. Correct sentence structure and audience-appropriate language are used. Prose is largely free of mechanical errors, although a few may be present. A variety of sentence structures and effective figures of speech are used. Writer is clearly in command of standard, written, academic English.   10.0 %Format   5.0 %Paper Format (Use of appropriate style for the major and assignment) Template is not used appropriately, or documentation format is rarely followed correctly. Appropriate template is used, but some elements are missing or mistaken. A lack of control with formatting is apparent. Appropriate template is used. Formatting is correct, although some minor errors may be present. Appropriate template is fully used. There are virtually no errors in formatting style. All format elements are correct.   5.0 %Research Citations (In-text citations for paraphrasing and direct quotes, and reference page listing and formatting, as appropriate to assignment and style) No reference page is included. No citations are used. Reference page is present. Citations are inconsistently used. Reference page is included and lists sources used in the paper. Sources are appropriately documented, although some errors may be present Reference page is present and fully inclusive of all cited sources. Documentation is appropriate and citation style is usually correct. In-text citations and a reference page are complete and correct. The documentation of cited sources is free of error.   100 %Total Weightage         Bottom of Form

Ch. 7 P. 280

The sales budget is usually the first and most crucial of the component budgets in a master budget because all other budgets usually rely on it for planning purposes. 

Required:

Assume that your company’s sales staff provides information on expected sales and selling prices for items making up the sales budget.  Prepare a two-page memorandum to your supervisor outlining concerns with the sales staff’s input in the sales budget when its compensation is at least partly tied to these budgets.  More generally, explain the importance of assessing any potential bias in information provided to the budget process.

 

 

Written Project Requirements

 

Library Assistance:

Review the Library materials in the Student Resources tab and  this brief tutorial.  http://www.screencast.com/users/KULibraryVideos/folders/Default/media/3f6404cf-48b8-4c49-9065-6e5c74c8650b

Memo format:

Proper memo format can be found online at the Perdue OWL–http://owl.english.purdue.edu/owl/resource/590/02/

Instructions:

You will be using Chapter 7 Communication in Practice p 280 as the basis for this written project.  You will be required to review the situation in the text and provide solutions to the situation based on research. This will be done in the form of a memo to your supervisor explaining your answer that is backed up by research.  You must use at least 2 outside sources (professional journals found in the library) for your research.

Grading Rubric: 

Category	Grade	Comments
Student Name:	Scale of 5-0 where 5=Requirements met 0=Requirements not met
Format / Citations -20%
o Format: Proper Memo format used
o Memo uses, 1” margins,  12 pt font in Times New Roman style  and pages are numbered
*Remember if sources are not noted it is considered plagiarism
o Citations:  Sources used in the paper are cited in APA format (author name, date, page number if needed for a direct quote).
o Sources are listed on a separate references page in APA format (alphabetical order by author, if there is one, with all needed information).
Content / Quality -80%
o Content: There is a clear concise opening segment including  the purpose of the memo, the context  and the specific task
o There is a clear context section  this is the event, circumstance, or background of the problem you are solving
o There is a clear task statement where you should describe what you are doing to explain the problem
o There is a clear summary statement that provides a brief statement of the key recommendations you have reached based on your research.
o There is a clear discussion segment that includes all the details and research that support your ideas
o There is a clear closing segment summarizing your key points for the reader.
o There is a smooth transition between paragraphs.
o The information presented including exhibits is accurate and terms are used correctly.
o Concepts covered in class are incorporated into the paper.
o No more than 20% of the paper was taken from outside sources.
Memo is not less than 2 and not more than 3 pages of text (not including exhibits and reference page).
o Quality: 2 outsides sources (not the textbook) from professional journals found in the library were used.
o Used proper grammar, spelling, and punctuation; did not use slang or other forms of casual language.
o Submitted paper on or before the date due.
o Extra credit if the report from the OWL is submitted and corrections are made.
Total

Requires Use of Microsoft Excel

 

Must be Proficient at Trginometry

 

See Attached File

• Retired workers and disabled workers both receive Social Security benefits. What information would we need to test the claim that the difference in monthly benefits between the two groups is greater than $30 at the 0.05 level of significance? Write out the hypotheses and explain the testing procedure.

two paragraphs