代写SQL 数据库、代写数据库SQL 查询语句、代写SQL程序

日期：2018-08-25 03:38

代写SQL 数据库作业、代写数据库SQL 查询语句作业、代写SQL程序

ISYS1055/1057 Database Concepts

Assignment 1

Semester 2, 2018

1 Introduction

This is an individual assignment, to be submitted electronically using the Canvas facility. A

submission link will be enabled on Canvas closer to the submission date. It is due 23:59 Friday

24th August 2018, and contributes 20% towards the aggregate of 100 marks.

The objective of this assignment is to reinforce what you have learned in the lectures and tute/ lab

sessions. Specifically, it covers the basic concepts in the relational database model, and using

entity-relationship model for database design.

1.1 Plagiarism

All code or other material that is not original must be fully credited. That is, any material that is

copied or derived from another source must be clearly identified as such and the original author

must be identified. Sometimes students assist each other with an assignment, but end up working

together too closely, so that the students' separate solutions have significant parts in common;

unless the solutions were developed independently, they are regarded as plagiarised.

Plagiarism is a very serious offence. Any submissions determined to be a result of plagiarism will

be deemed as an academic misconduct and harsh penalties apply. It is also an offence for

students to allow their work to be plagiarised by another student. You should familiarize yourself

with the university website for Academic Integrity Policy, Procedures and Guidelines.

All work is to be done individually and plagiarism of any form will be dealt with according to the

RMIT plagiarism policy.

1.2 What to Submit, When, and How

1.2.1 When

This assignment is due at 23:59 Friday 24th August 2018.

1.2.2 What

You should submit one PDF document with all answers together. You may use LucidChart to work

on Part A of your assignment. You may use Word or any other word processor to compile your

submission. At the end, convert it into PDF format. Do not submit Word files. if that option is not

available on your system there are free pdf converters online you can utilise. e.g.

http://convertonlinefree.com/

1.2.3 How

You are required to submit your solution electronically using the Canvas facility. A submission link

will be enabled on Canvas closer to the submission date.

Computer Science and Information Technology

Document: Assignment 1 V1.0.docx

Author: Santha Sumanasekara

Save Date: 02/08/2018

Page 2 of 7

1.2.4 Penalties for late submissions

Late submissions of assignments will be penalised as follows. For 1 to 5 days late, a penalty of

10% (i.e. 10% out of total marks, not 10% out of your marks) per day. For assignments more than

5 days late, 100% penalty applies. If you submit between 00:00 25 August and 23:59 25 August,

your penalty is 10%. If you submit between 00:00 26 August and 23:59 26 August, 20% penalty,

and so on.

1.2.5 Special Consideration

If unexpected circumstances affect your ability to complete the assignment you can apply for

special consideration. If you seek a short extension, you can directly contact the lecturer. For

longer extensions, you must follow instructions provided at

http://www1.rmit.edu.au/students/specialconsideration

1.3 Preparation Tasks

For Part A of this assignment, you must be familiar with LucidChart or a similar diagramming

application. You are required to complete the Week 4 Tute/ Lab session prior to attempt this

assignment. Additional resources on using LucidChart are available under Resources section of

the course Canvas shell.

2 Part A: Entity-Relationship Modelling (70 Marks)

2.1 Designing an Entity-Relationship Model

You have just been employed as a database designer in a well-established software development

firm. Your first job is to design and implement a database system for a small medical centre in one

of the Melbourne suburbs.

The following information have been gathered after analysing the current practices of the medical

centre.

• The system records information about the doctors work at the medical centre. Doctors

have unique registration numbers. In addition to the registration number for each

doctor, the system records the name, date of birth, gender, qualifications, and

specialisations. Doctors can have multiple qualifications and multiple specialisations.

• The system keeps a record for each patient. When a new patient comes to the medical

centre, the patient is given a registration form to be filled. The information gathered in

this form will be stored on the system. When these information is entered to the system,

a serial number is assigned to each patient. The registration form captures following

information: name, address, date of birth, gender, telephone numbers (multiple

numbers are allowed), Medicare number (if any), private health cover number (if any),

and private health cover company (if any).

• When a patient attends a consultation with a doctor, following information are recorded:

consultation number, date, time, description, symptoms, and special remarks.

Symptoms is a multi-valued attribute.

• A number of prescriptions can be recommended after each consultation. For each

prescription, following information are recorded: name of the medicine, dosage, number

of repeats, and special instructions. None of these attributes are unique. However, for a

given consultation a medicine is prescribed only once.

• The doctors sometimes make referrals for other procedures, such as X-ray, MRI, and

physiotherapy. The system should be able to record such referrals made at

Computer Science and Information Technology

Document: Assignment 1 V1.0.docx

Author: Santha Sumanasekara

Save Date: 02/08/2018

Page 3 of 7

consultations. For each referral, following information are recorded: name of the

procedure, short description, referred practitioner, and whether this procedure essential

or optional. Like prescriptions, referral for a given procedure is only made once in a

consultation

• The system should be able to keep accounting information. In particular, the system

should be able to store payments made for the consultations. There are three

possibilities:

1. bulk-billing:

Some Medicare1 patients (such as disabled and seniors) are bulk-billed. This means

that the patient is not required to pay anything up-front. The medical centre will bulkbill

the Medicare (the government agency that deals with healthcare services in

Australia) the costs associated with the consultation. For such transactions, the

medical centre will record the payment number, timestamp, amount and the

Medicare reference number

2. full up-front payments (cash or card):

The patients with no Medicare benefits are required to pay full cost of the

consultation up-front. For such payments, the medical centre will record the

payment number, timestamp, the amount paid, and the method of payment.

3. settling part of the invoice using a Medicare benefits and the balance in cash or

card:

Some Medicare patients are required to pay a top-up payment (gap fee). That

means, part of the consultation fee is covered by Medicare benefits and the rest is

paid up-front by the patient. For such transactions, two payment records are created

and stored. (The both are linked to one consultation). First record will be similar to

(1) above. The second is similar to (2) above.

Based on the information you gathered, model the activities in your client's business and present

your model as an Entity-Relationship (ER) diagram. Carefully state any assumptions that you

make. In your ER diagram, you must properly denote all applicable concepts, including weak or

strong entities, keys, composite or multi-valued attributes; relationships and their cardinality and

participation constraints.

If you cannot represent any of these information in the ER model, clearly explain what limitations in

the ER model restrict you from representing your model.

You must use UML notation used in the lectures and tute/ lab sessions. You may use any

diagramming tool to draw your diagram, however, use of LucidChart is highly recommended.

Your diagram must be drawn to a high standard with minimal clutter.

Note that you are not required to map the ER model to relational model.

A special note: This is an open-ended question with many different models can be derived. Your

model is assessed based on how accurately it represents business rules described above.

1 If you are not familiar with how Medicare works, http://www.mydr.com.au/first-aid-selfcare/australian-health-system-how-it-works

has a simple explanation.

Computer Science and Information Technology

Document: Assignment 1 V1.0.docx

Author: Santha Sumanasekara

Save Date: 02/08/2018

Page 4 of 7

2.2 Designing and refining an Entity-Relationship Model

A small bus company wants a simple database to track some aspects of its operation.

They describe key elements of their requirements as follows.

• The company has busses that can be identified by their registration number and it is

important to record the brand and model, year made, the number of seats, the engine

capacity and the last service date.

• Bus routes are known by their Route Number. Route numbers are three-digit numbers,

starting with 100. All routes have a start terminus and an end terminus and the distance of

the route.

• The drivers details are name, their start date with the company, their licence number and its

expiry date.

• Each bus driver is allocated a bus and only that driver will drive that bus. However, at any

given time, some drivers will be on leave and some busses will be in for servicing.

• A bus is allocated to just one route at a time. However, at any given time, there are number

of busses plying on the same route.

• Several mechanics work for the company. Their name and unique certificate number are

stored in the database. A bus can be worked on by any of the mechanics and all mechanics

are qualified to service all buses.

Based on the information you gathered, model the activities in your client's business and present

your model as an Entity-Relationship (ER) diagram. Carefully state any assumptions that you

make. In your ER diagram, you must properly denote all applicable concepts, including weak or

strong entities, keys, composite or multi-valued attributes; relationships and their cardinality and

participation constraints.

After presenting your ER model to the management, they pointed out that your model lacks the

ability to capture all requirements of the operation.

In particular, they pointed out the following shortcomings:

1. For auditing purposes, it is important to keep track of allocation of drivers to busses. For

each shift, it is required to record date, start and end times, the bus number and the driver

allocated, and any remarks made by the driver at the end of the shift.

2. For auditing purposes, it is required to keep track of allocation of busses to routes. For each

allocation, it is required to record route number, bus registration number, date, start and

end times, and any additional remarks made after the bus returning to the depot.

3. It is not sufficient to only record the last service date. It is required to keep track of all

services done on all busses since they entering the service. For each service done, it is

required to keep following information: bus registration number, service date, service type

(major or minor), odometer reading, and any additional remarks made at the service. It is

also required to identify the mechanic responsible for each service.

Draw a modified ER diagram to accommodate these additional requirements.

Noe: Your answer to this question should include TWO complete ER diagrams.

Computer Science and Information Technology

Document: Assignment 1 V1.0.docx

Author: Santha Sumanasekara

Save Date: 02/08/2018

Page 5 of 7

2.3 Mapping an ER Model to a Relational Database Schema

Consider the following ER diagram, which models an online bookstore. Map this ER diagram into a

relational database schema. Show every step of the mapping. No marks are awarded to the final

schema if you do not show the partially-built schema at the end of each step. Indicate the primary

key (underlined) and foreign key (with an asterisk) in each relation.

This question has been adopted from Fundamentals of Database Systems, Elmasri and Navathe.

(Question 5.11).

A relational database schema and an instance of this schema are given below.

Most of the attribute names are self-explanatory. Super_SSN refers to corresponding employee's

supervisor's SSN (Social Security Number). This example is based on US system, assume it is

similar to Australian Tax File Number.

Arrows indicate foreign keys and the corresponding attributes in parent relation. In the case of

Super_SSN, the parent relation is the Employee relation itself (self-referencing).

Computer Science and Information Technology

Document: Assignment 1 V1.0.docx

Author: Santha Sumanasekara

Save Date: 02/08/2018

Page 7 of 7

1. It was found that the data entry operator had incorrectly entered data for John B. Smith and

Ramesh K. Narayan. Their SSN were swapped. The error was discovered, and the data

entry operator was instructed to fix it. (S)he attempted to swap SSN value in each tuple

using the following SQL statement.

But, it was not successful. The DBMS returned an error message and two tuples were not

updated.

a) Explain why they didn’t work.

b) Give another way to correct the error.

c) Write down the correct SQL UPDATE statements to carry out your proposed solution.

2. James E. Borg gets a pay rise, which increased his salary by 10%. The data entry operator

has executed the following SQL statement.

Write down all the integrity constraints violated by the above operation. If the operation

does not violate any constraints, indicate as 'no violations'.

3. Due to a workplace dispute, John B. Smith has been moved to a new supervisor. To effect

this change, the data entry operator has executed the following SQL statement.

Write down all the integrity constraints violated by the above operation. If the operation

does not violate any constraints, indicate as 'no violations'.

4. A new project has been established, but neither a project number nor a department is

assigned yet. A new record has been entered into the Project relation, as follows.

Write down all the integrity constraints violated by the above operation. If the operation

does not violate any constraints, indicate as 'no violations'.

UPDATE employee

SET SSN=’666884444’

WHERE SSN = ‘123456789’;

UPDATE employee

SET SSN=’123456789’

WHERE SSN = ‘666884444’;

UPDATE employee

SET salary = salary*110/100

WHERE SSN = ‘888665555;

UPDATE employee

SET super_SSN = '666884444'

WHERE SSN = ‘123456789';

INSERT INTO project (pname, plocation)

VALUES (‘Agent X’, ‘Washington’);