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'.