​​ Page 1 of 8 ACC5FAS Team assignment (Five members); Due date: 19 May (Total Marks: 200) – You must submit a Statement of Authorship for each group The final document (hard-copy) is due on 19 May. Presentation is due during the tutorial in the week beginning 16 May. Submission Instructions (Part A): Submit a soft copy of a word document (i.e. the context diagram, DFD and ERD) and the presentation slides. You also need to email your presentation slides to your tutor by the 16th May; otherwise the uploading time will be part of the 10 minute presentation and youwill get less time to present. PART A: SUCCESS VIDEO STORE (SVS) Success Video Store (SVS) runs a series of fairly standard video stores. Before a video can be put on the shelf, it must be catalogued and entered into the video database. Every customer must have a valid SVS customer card to rent a video. Customers rent videos for 3 days at a time. Every time a customer rents a video, the system must ensure that he or she does not have any overdue videos. If so, the overdue videos must be returned and an overdue fee must be paid before the customer can rent more videos. Likewise, if the customer has returned overdue videos but has not paid the overdue fee, the fee must be paid before new videos can be rented. Every morning, the store manager prints a report that lists overdue videos; if a video is 2 or more days overdue, the manager calls the customer to remind him or her to return the video. a. Draw a context diagram for the scenario mentioned above. (10 marks) b. Draw a level 0 Data Flow Diagram (DFD) for the scenario mentioned above. (20 marks) c. Draw an Entity Relationship Diagram (ERD) for the video rental store mentioned above. Each relationship must be clearly defined. (30 marks) For example: 1 M Order Each customer may make more than one order. However, each order can only be made by a specific customer (please note that this example is not linked with the scenario given above). d. 10 minutes (maximum) of team presentation on the proposed diagram (part a, b and c) to be givenin the tutorial in the week beginning 5th October (nominate 2 members to present). (40marks) Page 2 of 8 Submission Instructions (Part B): This assignment must be submitted in both soft and hard copy. To submit your soft copy (electronic submission), you are required to submit MS Word, Access and Excel files online. Your file names should include all student IDs in the group, separated by underlines. You also need to provide your names and IDs in MS Word file in the first page. To submit the hard copy (printed version), you MUST submit your answers to every question underneath the related question in a printed MS word document. To do this, you are required to take a screen‐shot of the answer you provide to every question and then transfer that screen‐shot to MS Word file, underneath the related question for both Access and Excel questions. For the excel questions, apart from the screenshot, you are required to provide the formula that you use and/or the instructions you followed to answer the excel question underneath the related excel question. Please provide the name and the student ID number of all the group members on the front page of the printed version submission. How to Create and Insert a Screen Shot into Word By pressing PRINT SCREEN button on your keyboard, you capture an image of your entire screen, also known as a "screen‐shot" or "screen‐capture", and it is copied to the Clipboard in your computer's memory. You can then paste the image into a word document, or other file. Copy the entire image on the screen 1. Open everything you want copy and position it the way you want 2. Press PRINT SCREEN 3. The text you see on your keyboard might be PrtSc, PrtScn, or PrntScrn The other text on the Print Screen key is usually SysRq 4. Paste (CTRL+V) the image into a Microsoft Office program or other application Copy only the image of the active window The active window is the window that you are currently working in. Only one window can be active at atime. 1. Click the window that you want to copy 2. Press ALT+PRINT SCREEN 3. The text you see on your keyboard might be PrtSc, PrtScn, or PrntScrn The other text on the Print Screen key is usually SysRq 4. Paste (CTRL+V) the image into a Microsoft Office program or other application Once you get the image or screen shot into your Microsoft Word or other application such as PowerPoint program, then you are able to edit the image by using the Format tab on the Picture Tools Contextual tab.From this tab, you can adjust the brightness or contrast; change the style; position the image within your document; and change the size or crop it. You can also use Microsoft Paint (Start, All Programs, Accessories, Paint) to crop the image, or save it as aJPEG file. Use Paint to crop your capture image and save as JPEG 1. Capture the screen Page 3 of 8 2. Click on the Start button, slide up to All Programs, then slide up to Accessories, and finally slide over and down to Paint 3. Press Ctrl + V to paste in the picture and then press Esc key. 4. If you want just a portion of the image you can use the Select tool to grab just a portion of the screen. 5. Press Ctrl + X to cut what you have selected 6. From the File menu choose New 7. Click No when asked to Save 8. Press Ctrl + V to paste in the new picture 9. You can continue to edit this size 10. When ready, select and copy the image and then paste it into Word (You can also go to the File menu and choose Save As) PART B: KELLY'S BOUTIQUE Kelly's Boutique sells books as well as woman's shoes. Kelly is eager to incorporate computers in her business. She uses a database to keep a record of her book inventory. She has a partial list of books which can be a table in her database. The list includes each book's ISBN, department code, related supervisor and supervisor phone number, book title, publisher and publisher contact number, author and list price. 1) Create a new database file using the following information and name the file StudentName_Access. a. Use the information below to create a book table, department table and publisher table in Access, and then PRINT each table. (3 Marks) Book Table ISBN DeptID BookTitle Author PubNum ListPrice 439272602 2 Alvie Eats Soup Collins 9 $ 15.95 733330827 2 Fearless Thompson 2 $ 14.99 1926428749 1 Island Home Winton 3 $ 39.99 1460750322 1 Life in Balance Hay 2 $ 39.99 439901006 2 Medusa Jones Collins 9 $16.99 43944421 2 My Chair James 9 $ 16.95 9780099271086 1 Pandora Rice 6 $ 19.99 9780552565431 2 Pig and Small Latimer 6 $ 19.99 Department Table DeptID Dept Supervisor Phone 1 Adult Julie Smith 515‐5467 2 Children Nicola Clarke 515‐9823 Page 4 of 8 Publisher Table PubNum Publisher Contact Phone 1 Mass Market Paperback Smith 515‐9745 2 Harper Collins Potter 515‐7481 3 Penguin Frued 515‐8974 4 Simon & Schuster Gonzales 515‐9874 5 Viking Press Hu 515‐1654 6 Random House Ouimet 515‐9144 7 Scholastic Press Salazar 515‐9888 8 Touchstone Books Chi 515‐1112 9 Arthur A. Levine Books Robinson 515‐5118 b. Establish the appropriate relationship between each table, PRINT the relationship report. (3 Marks) c. Create and PRINT a query that lists department, book title, author, supervisor, publisher and contact. Save this query as BooksByDepartment. (6 Marks) d. Create a form that shows all fields from the book table in a columnar format. Save it as Book Form and PRINT the first record. (7 marks) e. Create and PRINT a report that contains the book title, ISBN, publisher, and phone number. The report should have no grouping, be sorted by book title in ascending order, and be formatted in a tabular portrait layout and all field information should be visible. Save this report as Book Report. (7 Marks) Save the database as KellyData01. =26 marks Page 5 of 8 Kelly would like to make some adjustment to the tables by adding and deleting some records, adding some OLE fields and pictures, and changing the structure of the database. Make a copy of previous database and save it as KellyData02. 2) Make the following changes to Kelly's database, KellyData02: A. Add the new records to the following tables Publisher Table PubNum Publisher Contact Phone 10 Warner Yee 515‐7894 Book Table ISBN DeptID BookTitle PubNum Author ListPrice 9781439153666 1 Kitchen House 4 Grissom $ 16.00 0590203207 2 Frida 10 Winter $ 1 6 .95 9780684801056 1 Time and Again 4 Finney $ 15.99 (2 Marks) B. Add a picture field for OLE objects to the book table. Then add pictures to the Book table for Frida, and Pig and Small and Island Home. Picture files are saved by book name and are located on your student disk in an Images folder. PRINT a screenshot of Book Form showing the first book withits image. (4 Marks) C. Establish a phone number input mask for the Phone field of the Publisher and Department tables. PRINT a screenshot of the input mask in Publisher table. (2 Marks) D. Enforce and PRINT referential integrity between the Book table, Department table andPublisher table. (4 Marks) E. Create and PRINT a validation rule for the List Price field of the Book table, making the field a required one and making sure the price is no less than $ 1 but no more than $ 100. (3 Marks) F. Establish Children as the default value for the Department field of the Book Table and PRINT the table. (2 Marks) Save the database KellyData02. =17 marks Kelly would now like to create, run and print some select, parameter, and action queries. . Make a copy of previous database and save it as KellyData03. Page 6 of 8 3) Make the following changes to Kelly's database, KellyData03: 1. Add Quantity field (Data Type=Number) to the Book Table and then enter values as follows: (1 Mark) BookTitle Quantity Alvie Eats Soup 18 Fearless 4 Frida 8 Island Home 5 Kitchen House 6 Life in Balance 4 Medusa Jones 3 My Chair 3 Pandora 15 Pig and Small 10 Time and Again 8 Add Markup field (Data Type=Number, Field Size=Decimal, Format=Percent, Scale=2, Decimal Places=0) to the Department table and then enter values as follows: (5Marks) 2. Create and PRINT a select query that lists the author and book title for all books written bySeuss. Save this query as KellyCaseA before you print it. (2 Marks) 3. Create and PRINT a select query that lists the author and book title for all books purchasedand supervised by Julie Smith, sorted in ascending order by book title. Save this query as KellyCaseB before you print it. (3 Marks) 4. Create and PRINT a select query that lists the book title and List Price for all book title startwith Life. Save this query as KellyCaseC before you print it. (1 Mark) 5. Edit the query you just created. Add fields for author and for publisher and delete the ListPrice. Change the criteria from 'starting with Life to 'containing the word Pig'. Save this queryas KellyCaseD before you print it. (2 Marks) DeptID Dept Markup 1 Adult 100% 2 Children 50% Page 7 of 8 6. Create and PRINT a select query that lists the book title and List Price, quantity and retail value(a computed field equal to list price x quantity), stored in descending order by retail value. Be sure to format the field as Currency. Save this query as KellyCaseE before you print it. (2 Marks) 7. Create and PRINT a select query that lists the book title and List Price, markup, unit cost,quantity and cost for books in children department. Unit cost is a computed field (list price divided by 1 plus markup). Cost is another computed field (unit cost x quantity). Be sure to sort the query alphabetically by book title. Save this query as KellyCaseF before you print it. (3 marks) 8. Modify the query created in last part so that it includes the DeptId field. Save the query as KellyCaseG1. And then use this query as the query that sums the cost of inventory by department. Save this query as KellyCaseG before you print it. (3 Marks) 9. Create and run an action query that increases all books' List price by 5 percent. Be sure to backup your file first! Save this query as KellyCaseH. Create another query that lists the book title and List Price for all books, sorted alphabetically by book title. Save this query as KellyCaseH1 and then PRINT it. (2 Marks) 10.Create a parameter query that lists books from a particular publisher. The query should ask "Enter Publisher's Name:" and then lists the publisher, book title, and quantity for that publisher. Save this query as KellyCaseI. Run and print the query after entering "Harper Collins" as the publisher. (4 Marks) 11.Create and run and PRINT a delete query that deletes all products with a quantity of Zero. Save this query as KellyCaseJ. (2 Marks) Save the database KellyDatabase03. = 30 marks It's time for Kelly to analyse her data in Excel sheets. Save your database and then export Book Table to MS Excel as an Excel Workbook. Remember to export data with formatting and layout. Save your file as BookSheet. Open the file and try to make the following changes using Pivot Table, IF statement, Filters and VLookup: i. In a new sheet, present a summary of book titles and quantity of each title for any publisher (PubNum) chosen by user. (10 Marks) ii. Add two new columns to the sheet called Unit‐Cost and Cost. Unit cost is a computed field (listprice divided by 1 plus 50%). Cost is another computed field (unit cost x quantity). Calculate these columns for any book in Children department. (8 Marks) iii. Display a subset of records that only list books written by Collins. (4 Marks) Page 8 of 8 iv. Implement a search method to find List Price for a given ISBN. PRINT your result for the book 'Pandora'. ( 5 Marks)