CIS2002 SEMESTER 1, 2014
Assignment 2 specification
Need Help Writing an Essay?
Tell us about your assignment and we will find the best writer for your paper.
Get Help Now!Description
Marks out of
Mode
Wtg(%)
Due date
ASSIGNMENT 2
100.00
External
25.00
30 May 2014
ASSIGNMENT 2
100.00
On Campus
18.00*
30 May 2014
*On-campus students take practical tests and the weightings for assignments are different due to this.
IMPORTANT INFORMATION
You must submit the assignment electronically by the due date via the EASE 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.
There is an automatic extension until 6 June. The extra time is only if you need it. You should aim to submit at the due date. There is no need to apply for this extension. Once the model answers are released no further assignments can be accepted.
You must use the official USQ data modelling and normalization methodology. This methodology is based on Clive Finkelsteins 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.
It is perfectly acceptable if you submit neat hand-drawn ERDs. Alternatively, you might wish to use Word. If you use a CASE or drawing tool, you must adapt the drawing to conform to the USQ methodology.
This assignment may require a significant amount of work but you should treat it as an exercise in examination preparation as well as an assignment. 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 at examination standard although the data modelling exercise is somewhat more substantial than what would be expected in an examination setting.
SECTION A (Data Modelling) (40 marks)
Law Associates is a large legal practice based in Sydney. You have been asked to design a data model for the practice based upon the following specification:
The practice employs nearly sixty lawyers who work in a wide variety of specialty areas. A speciality id and description is stored for each speciality. Each lawyer employed by the practice is classified as a partner, an associate, a junior or an intern. The practice stores the following information for all lawyers: Name, address, telephone, email, base salary and the one area of law in which that lawyer specialises. For partners, the practice also stores information about the percentage of the partnership held by the lawyer and the area of speciality that lawyer leads. Each area of speciality has one partner who acts as a leader (or resident expert and consultant) in that area. For associates, the practice also stores details about the percentage of cases the associate has won.
Juniors and interns undertake prescribed training courses and a record is kept. All training courses are registered and the name of the course, the duration (in days), the start date, the end date and details about the training organisation. Although a course is only offered by one training organisation, these organisations typically offer many courses. We store the name, address, telephone, email and Law Society accreditation number for all training organisations. When a lawyer completes a course, the grade received on that course is recorded.
We store details about the qualifications of all lawyers. Each lawyer may have many qualifications and a list of qualifications is stored, including the name of the qualification, its level (undergraduate or postgraduate) and the name of the university offering the qualification.
Each lawyer may take on many cases. A case is assigned to at least one lawyer but may be assigned to as many as four. If more than one lawyer is assigned to the case, one of the lawyers must be designated as a leader. Only seniors may be leaders of cases involving multiple lawyers. A case must have one or more clients but a client may initiate many cases over time. For case, we store a unique identification number, the estimated duration of the case, the client number/s of the clients involved, and the estimated start date. For client, we store an identification number, name and address. When any lawyer is assigned to a case, we store the date assigned and the date the lawyer leaves the case. A lawyer may become involved in a case on multiple occasions.
As cases can take years to resolve during the cases proceedings the layer in charge of the case may be unavailable as they be working on other cases. During this period the work associated with the case is assigned to the junior lawyer with mentoring from the layer originally assigned to the case. Upon the return of the principal lawyer to the case an evaluation is done of the assisting lawyer to record their performance for future evaluation. In the instance where a junior is assigned to a case where more than one lawyer is involved the other layers provide an evaluation of the junior lawyer as well. During the length of the case a number of junior layers could be assigned to assist with the case.
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. (15 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. (15 Marks)
NOTE: We use PART MARKING for the relations and will mark four relations chosen at random.
c) A single SQL statement or multiple statements that create/s the table for the relation training courses for jurors and interns. All key and attribute constraints should be included and data types suitable to each attribute should be chosen.
(10 Marks)
SECTION B (Normalisation) (30 marks)
Produce a set of relations (equivalent to the Finkelstein entity list) in third normal form (3NF) from the following un-normalised relation. Show your working and entitles for 1NF, 2NF and 3NF. You must use the Finkelstein methodology as used in the study book and tutorials.
PATIENT(patient id, name, address, age, gender, admitting doctor id, admitting doctor name, admitting doctor grade, date of admission, date of release, number of days in hospital, ((ward id, ward name, (( bed number, date arrived, date departed )) )), reason for admission)
Notes:
A new patient id is assigned for each admission to hospital
The admission to hospital must be authorised by only one admitting doctor.
During the hospital stay, the patient may be moved from ward to ward and from bed to bed within a ward.
Bed numbers are not unique within the hospital but are unique within the ward.
SECTION C (SQL) (20 marks)
MARKING CRITERIA
1. Four marks awarded for each correct SQL statement.
2. Alternative approaches to the model answer could be accepted unless they do not follow the rules / requirements set out in the specification, are poorly optimised or are poorly constructed (SQL).
3. Part marks may be awarded if an answer only has a small problem or an alternative solution is presented that works but is not ideally optimised / constructed.
4. There are 5 questions for the total of 20 marks.
For each question, five marks will be awarded for the SQL.
The following E-R diagram represents a Car Hire database.
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 only the queries output is not required.
1. Display the total number of customers that do not have a county recorded in for their address information.
2. Display each customer number and the total number of bookings that customer has made. Order the result by largest number of bookings at the top of the list.
Get Professional Assignment Help Cheaply
Are you busy and do not have time to handle your assignment? Are you scared that your paper will not make the grade? Do you have responsibilities that may hinder you from turning in your assignment on time? Are you tired and can barely handle your assignment? Are your grades inconsistent?
Whichever your reason is, it is valid! You can get professional academic help from our service at affordable rates. We have a team of professional academic writers who can handle all your assignments.
Why Choose Our Academic Writing Service?
- Plagiarism free papers
- Timely delivery
- Any deadline
- Skilled, Experienced Native English Writers
- Subject-relevant academic writer
- Adherence to paper instructions
- Ability to tackle bulk assignments
- Reasonable prices
- 24/7 Customer Support
- Get superb grades consistently
Online Academic Help With Different Subjects
Literature
Students barely have time to read. We got you! Have your literature essay or book review written without having the hassle of reading the book. You can get your literature paper custom-written for you by our literature specialists.
Finance
Do you struggle with finance? No need to torture yourself if finance is not your cup of tea. You can order your finance paper from our academic writing service and get 100% original work from competent finance experts.
Computer science
Computer science is a tough subject. Fortunately, our computer science experts are up to the match. No need to stress and have sleepless nights. Our academic writers will tackle all your computer science assignments and deliver them on time. Let us handle all your python, java, ruby, JavaScript, php , C+ assignments!
Psychology
While psychology may be an interesting subject, you may lack sufficient time to handle your assignments. Don’t despair; by using our academic writing service, you can be assured of perfect grades. Moreover, your grades will be consistent.
Engineering
Engineering is quite a demanding subject. Students face a lot of pressure and barely have enough time to do what they love to do. Our academic writing service got you covered! Our engineering specialists follow the paper instructions and ensure timely delivery of the paper.
Nursing
In the nursing course, you may have difficulties with literature reviews, annotated bibliographies, critical essays, and other assignments. Our nursing assignment writers will offer you professional nursing paper help at low prices.
Sociology
Truth be told, sociology papers can be quite exhausting. Our academic writing service relieves you of fatigue, pressure, and stress. You can relax and have peace of mind as our academic writers handle your sociology assignment.
Business
We take pride in having some of the best business writers in the industry. Our business writers have a lot of experience in the field. They are reliable, and you can be assured of a high-grade paper. They are able to handle business papers of any subject, length, deadline, and difficulty!
Statistics
We boast of having some of the most experienced statistics experts in the industry. Our statistics experts have diverse skills, expertise, and knowledge to handle any kind of assignment. They have access to all kinds of software to get your assignment done.
Law
Writing a law essay may prove to be an insurmountable obstacle, especially when you need to know the peculiarities of the legislative framework. Take advantage of our top-notch law specialists and get superb grades and 100% satisfaction.
What discipline/subjects do you deal in?
We have highlighted some of the most popular subjects we handle above. Those are just a tip of the iceberg. We deal in all academic disciplines since our writers are as diverse. They have been drawn from across all disciplines, and orders are assigned to those writers believed to be the best in the field. In a nutshell, there is no task we cannot handle; all you need to do is place your order with us. As long as your instructions are clear, just trust we shall deliver irrespective of the discipline.
Are your writers competent enough to handle my paper?
Our essay writers are graduates with bachelor’s, masters, Ph.D., and doctorate degrees in various subjects. The minimum requirement to be an essay writer with our essay writing service is to have a college degree. All our academic writers have a minimum of two years of academic writing. We have a stringent recruitment process to ensure that we get only the most competent essay writers in the industry. We also ensure that the writers are handsomely compensated for their value. The majority of our writers are native English speakers. As such, the fluency of language and grammar is impeccable.
What if I don’t like the paper?
There is a very low likelihood that you won’t like the paper.
Reasons being:
- When assigning your order, we match the paper’s discipline with the writer’s field/specialization. Since all our writers are graduates, we match the paper’s subject with the field the writer studied. For instance, if it’s a nursing paper, only a nursing graduate and writer will handle it. Furthermore, all our writers have academic writing experience and top-notch research skills.
- We have a quality assurance that reviews the paper before it gets to you. As such, we ensure that you get a paper that meets the required standard and will most definitely make the grade.
In the event that you don’t like your paper:
- The writer will revise the paper up to your pleasing. You have unlimited revisions. You simply need to highlight what specifically you don’t like about the paper, and the writer will make the amendments. The paper will be revised until you are satisfied. Revisions are free of charge
- We will have a different writer write the paper from scratch.
- Last resort, if the above does not work, we will refund your money.
Will the professor find out I didn’t write the paper myself?
Not at all. All papers are written from scratch. There is no way your tutor or instructor will realize that you did not write the paper yourself. In fact, we recommend using our assignment help services for consistent results.
What if the paper is plagiarized?
We check all papers for plagiarism before we submit them. We use powerful plagiarism checking software such as SafeAssign, LopesWrite, and Turnitin. We also upload the plagiarism report so that you can review it. We understand that plagiarism is academic suicide. We would not take the risk of submitting plagiarized work and jeopardize your academic journey. Furthermore, we do not sell or use prewritten papers, and each paper is written from scratch.
When will I get my paper?
You determine when you get the paper by setting the deadline when placing the order. All papers are delivered within the deadline. We are well aware that we operate in a time-sensitive industry. As such, we have laid out strategies to ensure that the client receives the paper on time and they never miss the deadline. We understand that papers that are submitted late have some points deducted. We do not want you to miss any points due to late submission. We work on beating deadlines by huge margins in order to ensure that you have ample time to review the paper before you submit it.
Will anyone find out that I used your services?
We have a privacy and confidentiality policy that guides our work. We NEVER share any customer information with third parties. Noone will ever know that you used our assignment help services. It’s only between you and us. We are bound by our policies to protect the customer’s identity and information. All your information, such as your names, phone number, email, order information, and so on, are protected. We have robust security systems that ensure that your data is protected. Hacking our systems is close to impossible, and it has never happened.
How our Assignment Help Service Works
1. Place an order
You fill all the paper instructions in the order form. Make sure you include all the helpful materials so that our academic writers can deliver the perfect paper. It will also help to eliminate unnecessary revisions.
2. Pay for the order
Proceed to pay for the paper so that it can be assigned to one of our expert academic writers. The paper subject is matched with the writer’s area of specialization.
3. Track the progress
You communicate with the writer and know about the progress of the paper. The client can ask the writer for drafts of the paper. The client can upload extra material and include additional instructions from the lecturer. Receive a paper.
4. Download the paper
The paper is sent to your email and uploaded to your personal account. You also get a plagiarism report attached to your paper.
PLACE THIS ORDER OR A SIMILAR ORDER WITH US TODAY AND GET A PERFECT SCORE!!!
The post Database Design And Implementation appeared first on Nursing Area.
Ask for Instant Assignment Writing Help. No Plagiarism Guarantee!
Online assignment writing service website that provide students with original and unique academic essays, research proposals, research papers, term papers, movie reviews, Book reviews, scholarship essays, personal statements, projects, presentations, dissertation, theses, admission essays, annotated bibliographies, reports, application papers, among others.
Stuck on a homework question? Our verified assignment writers can answer all questions, from basic math to advanced rocket science!
