ISYS1055/1057 Database Concepts assignment 代写
100%原创包过,高质量代写&免费提供Turnitin报告--24小时客服QQ&微信：273427
ISYS1055/1057 Database Concepts assignment 代写
Computer Science and Information Technology
Document: Assignment 1
Author: Santha Sumanasekara
Save Date: 22/03/2017
Page 1 of 9
School of Science/ Computer Science and IT
ISYS1055/1057 Database Concepts
Assignment 1
Semester 1, 2017
1 Introduction
This is an individual assignment, to be submitted electronically using the Blackboard facility. A
submission link will be enabled on blackboard closer to the submission date. It is due 23:59 Friday
21st April 2017, 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, using entity-
relationship model for database design, and using SQL for querying a relational database.
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.
(http://www.rmit.edu.au/academicintegrity) 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 on Friday 21st April 2017.
1.2.2 What
You should submit one PDF document with all answers together. You can use Oracle SQL
Developer and LucidChart to work on 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 Blackboard facility. A submission
link will be enabled on blackboard closer to the submission date.
Computer Science and Information Technology
Document: Assignment 1.docx
Author: Santha Sumanasekara
Save Date: 22/03/2017
Page 2 of 9
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 22 nd April and 23:59 22 nd April,
your penalty is 10%. If you submit between 00:00 23 rd April and 23:59 23 rd April, 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
Part A of this assignment, you must be familiar with LucidChart 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 Databases and Tools section of the course
Blackboard shell.
2 Part A: Entity-Relationship Modelling (50 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
consultations. For each referral, following information are recorded: name of the
procedure, short description, referred practitioner, and whether this procedure essential
Computer Science and Information Technology
Document: Assignment 1.docx
Author: Santha Sumanasekara
Save Date: 22/03/2017
Page 3 of 9
ISYS1055/1057 Database Concepts assignment 代写
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 Medicare 1 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 bulk-
bill 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 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-self-
care/australian-health-system-how-it-works has a simple explanation.
Computer Science and Information Technology
Document: Assignment 1.docx
Author: Santha Sumanasekara
Save Date: 22/03/2017
Page 4 of 9
2.2 Mapping an ER Model to a Relational Database Schema
Consider the following E-R diagram, which models an online bookstore. Map this E-R 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.
Book
ISBN {PK}
Title
Edition
year
ListPrice
Publisher
Name {PK}
Address
URL
ABN
Author
EMail {PK}
Name
Address
Telephone {1..N}
ShoppingCart
CartID {PK}
TimeStamp
Customer
Email
Name
Address
Warehouse
Code {PK}
Address
0..N
1..N
PublishedBy
1..N
0..N
WrittenBy
0..N
0..N
AddedTo
0..N 1..1
OwnedBy
0..N 0..N StockedAt
StockQty
Qty
BuyPrice
Computer Science and Information Technology
Document: Assignment 1.docx
Author: Santha Sumanasekara
Save Date: 22/03/2017
Page 5 of 9
3 Part B: SQL (30 Marks)
LibraryDB is a database system that keeps track of information concerning the books and their
circulation in an imaginary library.
Disclaimer: The data that populates the database are artificially constructed and by no means
correspond to actual real-world data.
The schema for the LibraryDB database is given below.
The primary keys are underlined. The foreign keys are denoted by asterisks (*).
Description of the schema
• person -- keeps track of the people who borrow books from the library. The attributes
contain personal and contact information.
• author -- keeps track of personal information about authors.
• publisher -- keeps track of the publisher information. To make simple, most of the attributes
have been truncated in the sample database.
• subject -- this relation keeps information about the subjects on which the library collection
have books (such as Mathematics, Database, etc)
• book -- contains information about the books that are available in the library. Every book
can have one or more physical copies in the collection. Each book can have one or more
authors and it is published by one or more publishers.
• book_copy -- keeps track of the physical copies of the books in the library collection.
• borrow -- keeps track of the check-ins and check-outs of the books. Every transaction is
done by one person, however may involve with one or more book copies. If there is no
return date, it means the book has been checked out but not returned.
• written_by -- associates books with authors. A book may be associated with several
authors and an author may be associated with several books. There is also an attribute
'role' that specifies the role of the author for the book (author/ editor/ translator/ etc).
• published_by -- associates publishers with books. There is an attribute 'role' here too.
• borrow_copy -- associates physical copies of books with a transaction. Members are
allowed to borrow several books in a single transaction.
A conceptual data model (shown as an entity-relationship diagram) which represents these data is
given below.
borrow(transactionID, personID*, borrowdate, duedate, returndate)
author(authorID, firstname, middlename, lastname)
book_copy(bookID, bookdescID*)
book(bookdescID, title, subtitle, edition, voltitle, volnumber,
language, place, year, isbn, dewey, subjectID*)
borrow_copy(transactionID*, bookID*)
person(personID, firstname, middlename, lastname, address, city,
postcode, phonenumber, emailaddress, studentno, idcardno)
publisher(publisherID, publisherfullname)
written_by(bookdescID*, authorID*, role)
published_by(bookdescID*, publisherID*, role)
subject(subjectID, subjecttype)
Computer Science and Information Technology
Document: Assignment 1.docx
Author: Santha Sumanasekara
Save Date: 22/03/2017
Page 6 of 9
You can access a sample database instance of this library database system using Oracle SQL
Developer (either on myDesktop or lab machines) or sqlplus on core teaching servers (titan, jupiter
or saturn.) Please follow the instructions provided on the Blackboard to access the sample
database.
Username: rolibrary
Password: library
If you wish to do this part of the assignment from home, you can install SQLite (with SQLite
Studio). The instructions for installing, configuring and using SQLite Studio is provided in the
Databases and Tools section of the Blackboard. Also included is the pre-built Library database in
SQLite format (Library.db) at the same location.
Write SQL queries for the following tasks.
1. Display the titles of all books on the subject "DataBases". Your result set should be sorted
on the alphabetical order of the titles.
2. Display
a. the number of books on the subject "DataBases".
b. the number of book copies on the subject "DataBases".
3. Display the firstname and lastname of the authors who wrote books on the subject
"DataBases".
a. Write your query without using NATURAL JOINs.
b. Write your query using NATURAL JOINs.
4. Who translated the book "American Electrician's Handbook"? Display the firstname,
middlenames, and lastname of the translator.
Author
authorID {PK}
firstname
middlename
lastname
Subject
subjectID {PK}
subjecttype
Publisher
publisherID {PK}
publisherfullname
Book
bookdescID {PK}
title
subtitle
edition
voltitle
volnumber
language
place
year
isbn
dewey
BookCopy
bookID {PK}
Borrow
transactionID {PK}
borrowdate
duedate
returndate
Person
personID {PK}
firstname
middlename
lastname
address
city
postcode
phonenumber
emailaddress
studentno
idcardno
1..N
0..N
1..1 0..N
1..N
0..N
1..1
0..N
1..N
0..N
0..N
1..1
Role
Role
Computer Science and Information Technology
Document: Assignment 1.docx
Author: Santha Sumanasekara
Save Date: 22/03/2017
Page 7 of 9
5. Display the firstname and lastname of the people who returned books late.
6. Display the firstname and lastname of the people who returned books more than 7 days
late.
7. Display the titles of books that haven't been borrowed.
8. A borrower wants to borrow the book titled "PRINCIPLES AND PRACTICE OF DATABASE
SYSTEMS", but all of its copies are already borrowed by others. Write two queries to
display other recommended titles using the following methods.
a. Using partial matching of the book title -- note that the borrower is interested in a
"DATABASE" book.
b. By searching of other books written by the same author (i.e. the author of
"PRINCIPLES AND PRACTICE OF DATABASE SYSTEMS"
9. Display the list of publishers who have published books on the subject "DataBases". Your
query should display publisher's full name, along with "DataBases" book titles they
published.
10. List the full names of publishers who have not published books on “Databases".
Computer Science and Information Technology
Document: Assignment 1.docx
Author: Santha Sumanasekara
Save Date: 22/03/2017
Page 8 of 9
4 Part C: Relational Database Model (20 Marks)
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.
Computer Science and Information Technology
Document: Assignment 1.docx
Author: Santha Sumanasekara
Save Date: 22/03/2017
Page 9 of 9
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).
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’);
ISYS1055/1057 Database Concepts assignment 代写
