Case Study: Design and Create a Book Publisher Database
Smart Publishing Inc. (SPI) is a publishing company that specialises in foreign language textbooks. Recently, this company purchased a small, private publisher of Arabic, Spanish, Chinese, Maori and Japanese language textbooks. These languages are increasing in popularity with primary, intermediate, college and university students in New Zealand.
Smart Publishing pays a base salary to its book representatives and also additional bonus based on exceeding sales goals. Customers place orders with the publisher through their bookstores. Schools can return unused books if they cancel classes due to low enrolment. At the end of each accounting period, these returns are subtracted from the current amount due.
Problem Definition
For the past few years, maintaining records on the sales of textbooks from the newly acquired publisher separately from the other foreign language textbooks in order to track profitability and market potential have been very challenging. SPI currently keeps data on its Customers,Representatives and Orders in three tables and processes information about its operationsmanually. SPI would like to keep its data current and accurate and also analyse it for trends and produce a variety of useful reports.
Task
In order for SPI to monitor its business better and provide timely and accurate reports, you have decided to help (as a database expert) to design, create and use a database to meet all the specific requirements set out by this company. You will need to design a Book Publisher Database from scratch, create the database,create the relationships between the three tables, enter the given data into the appropriate tables and then query the database in order to provide required information. Use the concepts and techniques you have learnt in database design guidelines in your design process. Use the data shown in Tables 1 to 3 below to design your database.
PART A: Creating and Using a Book Publisher Database
1. Create a new database using the Blank Database template. Save the database as your Surname_SmartPublisher. For example, if your surname is Smith, your filename would beSmith_SmartPublisher. Keep saving your file regularly.
2. Create and define a new database table called CUSTOMER using the following field definitions
3. The City field should be chosen from a lookup list of allowable items: AKL, WTK, MNK and NSH.
4. Enter the following as description for the City field: "Customer's City such as AKL = Auckland,
WTK=Waitakere, MNK=Manukau and NSH=North Shore" and then enter appropriatedescriptionfor other field names in the CUSTOMER Table.
5. Create the second table named REP using appropriate field names, field properties and descriptions. Ensure to use the correct data types for all the field names in this table.
6. Specify the following legal rules (Validation Rules) and Validation Texts for the specified fields in the REP table and save changes:
a. Specify the legal values PRMor PREM or NORM for the RepType field. Include an appropriate Validation Text for this field.
b. Specify that the BaseSalary field must be between $30,000.00 and $50,000.00 inclusive. Include an appropriate Validation Text for this field.
7. Create the third table named ORDER and then apply the following properties to each field name.
8. The Category field should be chosen from a lookup list of allowable items: Arabic, Spanish,Chinese, Maori and Japanese.
9. Enter appropriate descriptions for all the field names in the ORDER Table. Save the Table again.
Part B: Relating and Updating Tables
10. Create a one-to-many relationship between the three tables using appropriate fields to establish the following links:
a. one-to-manyrelationship between CUSTOMER and ORDER tables.
b. one-to-manyrelationship between the REP and ORDER tables.
11. Enable the features that will: Enforce Referential Integrity and Cascade Delete RelatedRecords for all relationships created in step 10 above. Save and exit the relationshipEnter the records as shown in Table 1 (page 2) into the CUSTOMER Table using the datasheet view. Sort the records in the CUSTOMER table into ascending order by City.
12. Enter the records as shown in Table 2 (page 3) into the REP table.
13. Enter the records as shown in Table 3 (page 3) into the ORDER table. Sort the records in this table in descending order by DateOrdered. Save and close all opened tables.
14. Resize all columns in the three tables to best fit the data.
Part C: Querying a Database
The management of SPI would like you to use the database to retrieve some vital information (queries) for decision making and the smooth running of their business. Create the following queries as requested.
16. Use the CUSTOMER table to create a query that includes the customer number, customer name, amount paid, current due and book rep number for all customers whose name starts with letter F and whose book rep number is 62. Save the query as qryCustDetails(NameAndNumber).
17. Create a query from the REP table that includes all the field names in the query design grid for all representatives whose StartDate is after 01/01/2015 or earn a base salary greater than or equalto $45000. Save the query asqryRepDetails(DateOrSalary).
18. Create a query from the ORDER table that includes the customer number, book number, book category, price per unit and quantity ordered for customer DSU10. Hide the customer number field name in the query result (dynaset). Save the query as qryBookOrdered(DSU10).
19. Create a query using the REP and CUSTOMER tables. For each book rep, list the book rep number, last name, and first name. Also, list the customer number and customer name for each of the book rep's customers. Sort the results in ascending order by book rep number. For customers with the same book rep number, further sort the results in descending order by customer name. Save the query as qryCustRepDetails.
20. Using the ORDER table, create a query to calculate the total quantity of books ordered by customers of each rep. Include the book rep number and quantity fields in the query design grid. Save the query as qryBookOrdered(TotalQuantity).
21. Using the CUSTOMER Table, count the number of customers located at different cities:
Auckland, Waitakere, Manukauand North Shore. Include theCityandCustIDfields in thequery design grid. Save the query as qryCountofCust(By City).
22. Use the REP Table to create a query that lists all Rep Types. Each rep type should appear only once [Hints: omit duplicates by setting each rep type as a unique value]. Save the query as qryListofRepType.
23. Create a query from the CUSTOMER table that would display all customers with Returnsbetween $2,000and$5,000inclusive.
Select all the fields in the Customer table at ONCE and place them into the field area in the first column (field) of the query design grid.
a. Select the Returns field, and then place this field into the second field area of the query grid. Hide the Returns field in the query result.
b. Specify the required criteria to obtain the required information for this query.
c. Run and save the query as qryReturns(Btw $2,000 and $5,000).
24. Customers can return unused books if they cancel classes due to low enrolment. Create a query that will calculate the total amount, sales price and sales price inclusive GST for each customer. Using the CUSTOMER table, list the customer number, customer name, amount paid, current due, returns, total amount, sales price and sales price inclusive GST (rate of 15%) for each customer. Hints: add the calculated fields (TotalAmount, SalesPrice and SalesPriceIncGST) after the Returns field in the query design grid as follows:
1. TotalAmount= AmountPaid + CurrentDue
2. SalesPrice= TotalAmount - Returns
3. SalesPriceIncGST= SalesPrice + (SalesPrice * GSTRate)
Format the SalesPrice and the SalesPriceIncGST fields in the query as currency to two decimalplaces. Run and then save the query asqryTotalSalesPrice.
25. Create a new query from the qryTotalSalesPrice query in task 24 above to summarise and display the minimum, maximum and average sales price inclusive GST by each City. Ensure to include the City, Minimum Sales Price Inc GST, Maximum Sales Price Inc GST and the Average Sales Price Inc GST in your query result. Format the Minimum, Maximum and the Average Sales Price Inc GST fields in the query results as currency to two decimal places.
Run and save the query as qrySalesDetails(ByCity).