16/03/2016, 12:28 AM file:///Users/santha/teaching/ISYS1057/2016/Assignments/Assignment%201/asg1-specifications.html Page 1 of 5 CPT140/ISYS2095 - Assignment 1 - Due 15th April 2016 Assignment Requirements 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 15th April 2016, and contributes 20% towards the aggregate of 100 marks. 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 given zero marks for that assignment. In the event that a hurdle requirement is not met, this will result in the failure of the course. You should familiarize yourself with the contents of the university website for Academic Integrity. (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. Task Description Part A: Entity-Relationship Modelling (40 Marks) 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 (mutiple numbers are allowed), medicare number and individual reference number (IRN) (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, dosage, number of repeats, and special instructions. Prescription name is not unique. However, for a given consultation a medication is prescribed only once. The doctors sometimes use other treatments (other than prescribing medicines). The system should be able to record such treatments done at consultations. The system should be able to keep accounting information. In particular, the system should be able to store payments made for the consultations. To make the matter more complicated, the medical centre offers different payment options, such as bulk-billing, full up-front payments (cash/EFT), and settling part of the invoice using a Medicare cheque and the balance in cash/EFT. In other words, the consultation fee can be paid in a number of payments. Some patients have a Medicare card and some patients are just dependents of a Medicare card holder (as such they do not have their own Medicare number). If they are dependents, they are assigned individual reference number (IRN) in the card holder's Medicare card. In addition, there are some patients who do not have Medicare benefits. The medical centre keeps medical histories of each patient. the system should be able to display the medical history of a patient when they attend a consultation again. Based on the information you gathered, model the activities in your client's business and present your model as an EntityRelationship diagram. Carefully state any assumptions that you make. In your ER diagram, you must properly denote all 16/03/2016, 12:28 AM file:///Users/santha/teaching/ISYS1057/2016/Assignments/Assignment%201/asg1-specifications.html Page 2 of 5 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 restricts you from representing your model. You must use the ER modeling notation used in the textbook. You can use any tool to generate your ER model. Note that you are not required to transform 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. 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 realworld data. The schema for the LibraryDB database 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) 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. You can access a sample database instance of this library database system using Oracle SQL Developer (either on myDesktop or installed on your home computer) or sqlplus on core teaching servers (titan, jupiter or saturn.) Please follow the instructions provided on the Resources Cupboard to access the sample database. 16/03/2016, 12:28 AM file:///Users/santha/teaching/ISYS1057/2016/Assignments/Assignment%201/asg1-specifications.html Page 3 of 5 SQL Queries 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 1. the number of books on the subject "DataBases". 2. 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". 1. Write your query without using NATURAL JOINs. 2. Write your query using NATURAL JOINs. 4. Who translated the book "American Electrician's Handbook"? Display the firstname, middlenames, and lastname of the translator. 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. 1. Using partial matching for the inclusion of the word "DATABASE" in the book title. 2. 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 book titles they published. Please note that, these publishers may have published books in other subjects, too. However, your query should only display book titles in DataBases subject. 10. List the full names of publishers who have not published books on the subject ``DataBases". Part C: Relational Database Model (30 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. Suppose that each of the update operations given below is applied directly to the database instance shown in the diagram. (Note that we apply all the updates to the original database instance -- i.e. ignore the effects of previous updates when you consider the current update). 16/03/2016, 12:28 AM file:///Users/santha/teaching/ISYS1057/2016/Assignments/Assignment%201/asg1-specifications.html Page 4 of 5 List all integrity constraints violated by each operation. If the operation does not violate any constraints, indicate as 'no violations'. For each and every violation, explain why such violation occured and actions you can take to make sure that the operation does not violate any integrity constraints. 1. Insert <'Robert', 'F', 'Scott', '987654321', '21-JUN-42', '2365 Newcastle Rd, Bellaire, TX', M, 58000, '888665555', 1 > into EMPLOYEE. 2. Insert <'ProductA', 3, 'Bellaire', 2> into PROJECT. 3. Insert <'Production', 4, '943775543', '01-OCT-88' > into DEPARTMENT. 16/03/2016, 12:28 AM file:///Users/santha/teaching/ISYS1057/2016/Assignments/Assignment%201/asg1-specifications.html Page 5 of 5 4. Insert <'123454321', null, '40.0'> into WORKS_ON. 5. Insert <'453345453', 'John', M, '12-DEC-60', 'SPOUSE'> into DEPENDENT. 6. Delete the DEPENDENT tuples with dependent_name= 'Joy'. 7. Delete the EMPLOYEE tuple with SSN= '888665555'. 8. Modify the SUPER_SSN of the EMPLOYEE tuple with SSN='123456789' to '666884444'. What to Submit, When, and How When This assignment is due at 23:59 on Friday 15th April 2016. What You should submit one PDF document with all answers together. You can use Microsoft Word or another word processing application to work on your assignment. At the end, convert it into PDF format. Do not submit Word file. if that option is not available on your system there are free pdf converters online you can utilise. e.g. http://convertonlinefree.com/ 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. 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 16th April and 23:59 16th April, your penalty is 10%. If you submit between 00:00 17th April and 23:59 17th April, 20% penalty, and so on. © 2015 RMIT University Author : Santha Sumanasekara Last modified : 10/03/2016