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 entityrelationship 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 22nd April and 23:59 22nd April, your penalty is 10%. If you submit between 00:00 23rd April and 23:59 23rd 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 essentialComputer Science and Information Technology Document: Assignment 1.docx Author: Santha Sumanasekara Save Date: 22/03/2017 Page 3 of 9 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 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.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 1..N PublishedBy 0..N WrittenBy 0..N 0..N AddedTo 0..N 1..1 OwnedBy 0..N StockedAt 0..N StockQty Qty BuyPriceComputer 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 RoleComputer 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’);