I have uploaded
CIS2002_Assignment1_S3_2016 CIS 2002 – Assignment 1 CIS 2002 – Assignment 1 CIS 2002 – Assignment 1 Semester 1 2018 SEMESTER 1, 2018 Assignment 1 specification Description Marks out of Wtg(%) Due date ASSIGNMENT 1 100.00 10% 27 April 2018 19 April IMPORTANT INFORMATION You must submit the assignment electronically by the due date via the link on the study desk. Instructions will be provided on the course study desk. You must use the official USQ data modelling and normalization methodology. This methodology is based on Clive Finkelstein’s techniques (SR 2.1 and SR 2.2) and all the examples in the lectures, study book and the tutorials use this methodology. If you do not use the USQ methodology, you will probably be awarded a mark of zero. It is perfectly acceptable if you submit neat hand-drawn ERD’s. Alternatively, you might wish to use Word. If you use a CASE or drawing tool, you must adapt the drawing to conform to the USQ methodology. SECTION A (APPLIED DATABASE THEORY) (20 marks) You are required to write an opinion article (also known as an "op-ed" piece or article) for a technology magazine on the following topic: Warehousing and Business Intelligence. To enable better business decision, organizations look at the transactional data collected over the years for trends and patterns. In order to be able to organize and better mine the available data organizations design warehousing solutions and organize the information to enable Business Intelligence. Your task is to research and describe the process and benefits of data warehousing, why relation type databases are a natural fit for this design and how the warehousing enables Business Intelligence systems. Your opinion piece (op-ed) should be no longer than 500 words. There is no requirement for formal in-text referencing in this question. However, it is recommended to put a list of references at the end of the article showing the published materials that you researched while writing this article. See marking criteria section at end of the document for breakup of marks. SECTION B (SQL) (40 marks 4 marks per SQL question) For each question, three marks will be awarded for the SQL and one mark for the correct output. The following E-R diagram represents the JustLee Books database. The script for the table creation is located on the Moodle website for the CIS2002 course under the assignment specifications. Please ensure that you re-run the script to reset the tables to their default state before starting your assignment. In this question, you will use the JustLee books database. The JustLee database including appropriate data will be made available on the USQ Oracle server. You must use this data. Full description of the JustLee database is found in Appendix A in the textbook on page 511. You may also get most of the details by querying the data dictionary on the oracle server. Write SQL queries to solve the following specifications. Include the query AND THE OUTPUT. A screen dump of the output is acceptable. Show as many rows as you can. A screen dump is usually done via the ALT + PRNT SCRN command sequence or use the ‘Snipping Tool’ under windows to capture parts of the screen. While the output helps to understand your solution, you should not be analysing the output of the query in detail. As long as you are confident that your query corresponds to the question completely, your output may not be significant. You can have an output that says NO ROWS FOUND and it could be a perfectly valid output as long as your query fulfils the requirement. Explain why the query may not contain any output in this instance. Questions 1. Display the full name of all the customers in a single column (by concatenating first name followed by a comma then a space then last name), rename the filed ‘Customers’, region and referred for all customers who live in any western region (that is in any region in the western sector ‘W’, ‘SW’, ‘NW’) and who have been referred by another customer. 2. List the order number, order date and shipping date for all the orders that have no shipping cost associated with the order but have been shipped. 3. Display the ISBN, title, and discount and calculate 5% of retail cost (rename the field “5% Discount”) for books in the BOOKS table where the book has a discount of more than 5% of the retail price of the book. Order the result by retail price in descending order. 4. Our bookstore wishes to identify books which earn a profit (retail – cost) of more than 75% on the cost of the book to calculate new discounts for those books. Using the BOOKS table write a query to display book title, cost of the book, retail price of the book, calculate the current profit on the book (rename the filed ‘Current Profit”), calculate the profit at 75% on the cost of the book (rename the field “Profit at 75% of Cost”), and calculate the discount for the book (rename the field “New Discount”). Order the resulting set by retail descending order and the cost ascending. 5. Display the ISBN, title and category of books in the BOOKS table where the category starts with the characters ‘COM’ or the book title has the characters ‘THE’ anywhere in the tile and the book does not have a discount value recorded. . 6. Display the book ISBN, title, retail price and authors first and last name for all authors whose last name is White and the books don’t belong to the category computer. 7. Display the ISBN, quantity, price paid for each book, and a created field ‘Total Order Price’ (i.e. Quantity * Paideach) for all orders in the ORDERITEMS table whose quantity is greater than 1. Order the list by ‘Total Order Price’ descending. 8. Display the publisher name, book title and retail price less any available discount for that book for all books that have a discount. Name the new field ‘Discounted Price’. Order by resulting records by publisher name ascending and discounted price of the book in descending order. 9. List ISBN, title, retail, category and published date of all the books that have the (category of ‘COMPUTER’ or where the category contains the string ‘LIFE anywhere in the category description) and where (price of the books is more than 20 dollars for all the books where published in 2005). 10. Display the customer number, customer last name and city, customer zip code (rename the field ‘Customer ZIP’) and referrer zip code (rename the field ‘Referrer ZIP’) for all the customers who have been referred by a referrer with the same last name as the customer. SECTION C (Data Modelling) (40 marks) In this section you will construct data models per the given specifications. For each of the following questions you will need to provide and ERD and a list of relations (entity list): 1. An ER diagram: Show all entities, relationships, cardinalities and optionalities. Also, include all intersection entities but do not create entities that are not specifically covered by the specification. You must use the Finkelstein methodology as per the study book and tutorials. 2. A list of relations (entity list). Produce complete relations for all entities and attributes. Show all primary and foreign keys. Include all attributes that are specifically mentioned and all key attributes. You may need to create primary and foreign keys that are not specifically mentioned but do not create any other additional attributes. Question 1 (5) A questionnaire is a research instrument consisting of a series of questions. We need to develop a model to store questionnaires and its underlying questions. A questionnaire records questionnaire name, primary contact name, start date and expected end date. A questionnaire may have many questions. Each question has a question number, question text and a category description for the question. Question 2 (5) A mobile phone model consists of a number of variants (different styles, colours and features) which are sold as separate units. We need to develop a data model to record different mobile phone models and their variants. A mobile phone model records brand name, model name, model number (unique) and start year. A mobile phone model may have several variants. Each variant has a variant code, style, colour, feature, launch year and discontinued year. Question 3 (10) A truck driver may be assigned to many teams over time. A team comprises of a minimum of one driver but could have up to four. For driver, we store the name, license number and birth date. For team, we store the team identifier and base location. We also need to store the date a driver is assigned to a team, the date a driver leaves the team and the total kilometres the driver has driven within the team. Question 4 (20) An architectural firm has a number of architects working for it. For architects we store information such as office location, the phone number, email address and preferred type of architectural work (renovations, modern design, public buildings and so on). An architect may have more than one style of buildings they like to design on. The firm’s clients hire the architects to work on a building projects. For the client we record the client name and email address. Each building project may have a number of architects who work on the same project over a period of time. We need to record the project start date and the amount of time each architect spent on the project. MARKING CRITERIA SECTION A (20 marks) 1. Clearly describe the benefits of data warehousing and relation to business intelligence (5) 2. Discussion shows understanding the warehousing and structuring data for biasness intelligence (10) 3. Presented in easy to read and easy to understand manner. Solution is grammatically and structurally sound and presentation is neat and professional. (4) 4. 4. Shown research source of information for the essay (1) SECTION B (40 marks, 10 questions 4 marks each) 1. Three marks awarded for each correct SQL statement and one mark for the correct output. 2. Alternative approaches to the model answer will often be accepted unless they do not follow the rules / requirements set out in the specification, are poorly optimised or are poorly constructed (SQL) or utilise features of SQL not