Reference no: EM131047073

Team assignment-

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.

b. Draw a level 0 Data Flow Diagram (DFD) for the scenario mentioned above.

c. Draw an Entity Relationship Diagram (ERD) for the video rental store mentioned above. Each relationship must be clearly defined.

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 given in the tutorial in the week beginning 5th October (nominate 2 members to present).

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.

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

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.

c. Create and PRINT a query that lists department, book title, author, supervisor, publisher and contact. Save this query as BooksByDepartment.

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.

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.

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

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 onyour student disk in an Images folder. PRINT a screenshot of Book Form showing the first book withits image.

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.

D. Enforce and PRINT referential integrity between the Book table, Department table and Publisher table.

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.

F. Establish Children as the default value for the Department field of the Book Table and PRINT the table.

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.

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:

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:

2. Create and PRINT a select query that lists the author and book title for all books written by Seuss. Save this query as KellyCaseA before you print it.

3. Create and PRINT a select query that lists the author and book title for all books purchased and supervised by Julie Smith, sorted in ascending order by book title. Save this query as KellyCaseB before you print it.

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.

5. Edit the query you just created. Add fields for author and for publisher and delete the List Price. Change the criteria from 'starting with Life to 'containing the word Pig'. Save this query as KellyCaseD before you print it.

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.

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.

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.

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.

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.

11. Create and run and PRINT a delete query that deletes all products with a quantity of Zero. Save this query as KellyCaseJ.

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.

ii. Add two new columns to the sheet called Unit-Cost and Cost. Unit cost is a computed field (list price divided by 1 plus 50%). Cost is another computed field (unit cost x quantity). Calculate these columns for any book in Children department.

iii. Display a subset of records that only list books written by Collins.

iv. Implement a search method to find List Price for a given ISBN. PRINT your result for the book 'Pandora'.