ISYS1055/1057 Database Concepts
project 1
Semester 2, 2018
1 Introduction
This is an individual project, 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 project 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 project, 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. (https://www.rmit.edu.au/students/student-essentials/rights-and-responsibilities/academic-integrity) 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 project 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 project. 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.
1.2.4 Penalties for late submissions
Late submissions of projects 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 projects 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 project 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 project, 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 project. 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
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 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 (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-self- care/australian-health-system-how-it-works has a simple explanation.
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.
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.
3 Part B: Relational Database Model (30 Marks)
This question has been adopted from Fundamentals of Database Systems, Elmasri and Navathe. (Question 5.11).
image.png
A relational database schema and an instance of this schema are given below.
image.png
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).
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.
UPDATE employee
SET SSN=’666884444’ WHERE SSN = ‘123456789’;
UPDATE employee
SET SSN=’123456789’ WHERE SSN = ‘666884444’;
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.
UPDATE employee
SET salary = salary*110/100 WHERE SSN = ‘888665555;
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.
UPDATE employee
SET super_SSN = '666884444' WHERE SSN = ‘123456789';
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.
INSERT INTO project (pname, plocation) VALUES (‘Agent X’, ‘Washington’);
Write down all the integrity constraints violated by the above operation. If the operation does not violate any constraints, indicate as 'no violations'.
