1 ISYS1055/1057 Database Concepts S2/2016 Assignment 2 This is an individual assignment. Plagiarism in oral, written or visual presentations is the presentation of the work, idea or creation of another person, without appropriate referencing, as though it's one's own. Plagiarism is not acceptable and may result in charges of academic misconduct which carries a range of penalties. It is also a disciplinary offence for students to allow their work to be plagiarised by another student. For details, please check the course guide. Due time Final submission is due at 10:00pm 14 October 2016 Friday in Week 12. Submit one PDF file to the "on-time submission" folder in the Blackboard submission system. • Make sure you receive an automatic confirmation email after submission and keep the email as your submission receipt. • Do not leave your submission to the last minute as you may experience difficulty uploading your file. • You can re-submit multiple times before the due time – any re-submission will override previous submissions. Do not worry about the system warning message like " Originality reports for resubmissions can take up to 24 hours to generate". Late submissions of assignments after the due time (even if with approved extensions) need to be submitted to the "late submission" folder and will be penalised as follows. For every (up to) 24 hours late, a penalty of 10% (i.e. 10% out of total marks, not 10% out of your marks) applies. For assignments more than 5*24 hours late, 100% penalty applies. Assessment 100 marks in total, which is 20% of the overall assessment for ISYS1057 and ISYS1055. The assessment components and weights are: Assignment 1 Assignment 2 Exam 20% 30% 50% There is not a hurdle for any assessment component.2 Question 1. The Relational model (31 marks). Consider the ER diagram as shown in Figure. 1. In the ER diagram, keys for entity sets express FDs among attributes. In addition there are also following FDs: suppPhoneNo à suppAddr orderNo à managerName 3.1. Map the ER diagram to a relational database schema following the ER to a relational database schema mapping rules. 3.2. For each relation in the resultant relational database schema, denote primary key (underline) and any foreign keys (*) in the relation. 3.3. For each relation in the resultant relational database schema, indicate if the relation is in BCNF and explain your answer using FDs. Decompose non-BCNF relations into relations in BCNF or 3NF following the decomposition algorithm. Figure 1 ER diagram for orders and suppliers for production3 Question 2. ER model (29 marks). You are asked to construct an ER diagram for designing the Video Master video shop database. Description is given below. • Each customer has a unique customer number. Other attributes about customers, including name, address, and contact telephone number, should also be kept in the database. • Each video is identified by a unique number like VID0001, and it is also described by title, classification (e.g. drama, comedy, or documentary), year of production and producer. • A video usually has several copies. Different copies of a video are distinguished by copy numbers. For example, we may have copy No.1 of the video VID0001, copy No. 2 of the video VID0001, and copy No.1 of the video VID0002. • Each staff member is identified by a staff number, and also has a name and an address. • Each producer has a unique code, an address and a contact name. Each video has only one producer. • Transactions of renting and returning videos, whose details include staff, customers, and video copies involved, should be recorded. Transaction dates, as well as fees for renting transactions are also recorded. According to the given description, construct an ER diagram for the database, and make assumptions where necessary. You must represent entities, relationships and their attributes, and all applicable constraints in your ER diagram. Explain any concepts in the description that can not be expressed in the ER diagram. Your ER diagram must use notations in the lecture notes and should not be hand drawn. ER diagrams using other notations will receive zero mark for this question. You should use the ER diagramming tool Dia, which can be downloaded from the course Blackboard ("Database and Tools à ER Diagram Tools"). Some common errors in ER diagrams: • If the scenario is set in say, a club, you do not need an entity for the club. The arrangement you depict in the ER diagram is the club. • Entities do not connect to other entities with straight lines. Entities have relationships to/with other entities so there has to be a relationship between connected entities. • Every entity must have a primary key. • The names of all relationships and entities must be unique. • There are no foreign keys in ER diagrams. They come about as part of the mapping process into a relational schema.4 Question 3. SQL (30 marks). You are expected to use Oracle SQL*Developer to write and/or run SQL queries to answer questions. You need to run the script "academics.sql" in your Oracle account to create the tables in the Academics database. In particular, the Author table from Assignment 1 may be in your Oracle account. You need to SUCCESSUFLLY remove this table first to create the Author table in the Academics database. In addition to the lecture notes, you need to study by yourself the SQL*Plus tutorial on the course Blackboard ("Using Oracle à SQL*Plus tutorial") for Oracle syntax and useful functions. Schema for the Academics database is as follows: DEPARTMENT(deptnum, descrip, instname, deptname, state, postcode) ACADEMIC(acnum, deptnum*, famname, givename, initials, title) PAPER(panum, title) AUTHOR(panum*, acnum*) FIELD(fieldnum, id, title) INTEREST(fieldnum*, acnum*, descrip) Some notes on the Academics database: • An academic department belongs to one institution (instname) and often has many academics. An academic only works for one department. • Research papers (PAPER) are often authored by several academics, and of course an academic often write several papers (AUTHOR). • A research field (FIELD) often attracts many academics and an academic can have interests in several research fields (INTEREST). Primary keys are underlined and foreign keys are marked with *. The SQL script for defining and populating the database academics.sql can be downloaded from the course Blackboard ("Databases and Tools à Databases"). Write ONE SQL query for each question below except question 3.6). Each component of an SQL statement must be on a separate line. For example, SELECT * FROM Department WHERE State='VIC'; Do not include the result of the query or the script used to create the tables. Your query should not produce duplicates in output but use DISTINCT only if necessary. Each question below is worth 5 marks. 3.1. Are there any academics who have not written any papers? List the acnum, givename and famname of these academics. Your query must contain a subquery. 3.2. Find the academics that have more than 1 research interest. List the acnum, famname and givename of these academics, sorted first by famname and then by givename.5 3.3. Are there any research fields where no academics have any interest in? Print the total number of such research fields. Your query must use a SET operator. 3.4. Find the research fields that have the largest number of interested academics. Output the fieldnum and number of interested academics. Your query can NOT have the MAX operator. 3.5. Are there any academics who have written less than 20 (including none) papers? List the acnum, title, famname and givename of these academics and the number of papers they have written. 3.6. The SQL query below is intended to find the departments with the largest number of academics and print their deptnum, deptname and instname, but it has syntax errors. Identify the syntax errors in the query and give the correct SQL query. select deptnum, deptname, instname, count(acnum) from department, academic, where academic.deptnum =department.deptnum group by deptnum having max(count(acnum)); Question 4. Research question (10 marks). Excellence in Research for Australia http://www.arc.gov.au/excellence-research-australia is a comprehensive quality evaluation framework of research produced in Australian universities. Assume that the research paper index (RPI) for each department is defined as the total number of papers by academics in the department, normalised by the total number of academics in the department. In calculating the RPI for academic departments, each paper carries one point and is divided to departments according to the number of contributing authors. For example, consider a paper by three authors where two authors are from Department A and one author from Department B, the paper contributes 2/3 point to the RPI of department A and 1/3 point to that of department B. As another example, a paper by two authors from department A contributes one point to the RPI of Department A. Based on the Academics database, write SQL queries to output deptnum and RPI for each academic department, ranked in decreasing order of RPIs. You can write several queries and define views to keep intermediate query results. Explain in English what each query or view is doing. You are expected to run and test your queries in SQL Developer.