CSC316 slp2 (see attachment)

1. Identify all relevant entities and relationships.

2. Use ER diagrams to present them.

When you have finished, place them into a Word document.

SLP Assignment Expectations

· Improve existing database design using entity-relationship modeling methods.

· Communicate effectively with your audience.

The following database will be used by the old bookstore which deals with CDs and books to transact its operations electronically. The system will capture the data from the day-to-day operations and then the data stored in the database. The designed database will consist of two tables namely; the sales transaction table and the inventory status table. The primary table for the tables is the Product_ID. The primary key will be the one linking the tables.

The Current Inventory Status

The database user may get the CDs as well as books’ current inventory status by entering the date in the products table. The inventory status table shall have the following fields: product name, product id, type of item, unit price, cost of CD or book, and date. The information or data is obtained from the Point of Sale inputs. Any new CD or book entered together with the date will be stored in their respective columns. In order to view the books and CDs current inventory, the SQL syntax is : SELCT cont (*), FROM Products Table, Group by Product ID

The table structure will be as indicated below.

The Sales Transaction List

The sales transaction list table will have all the information concerning sales. The data is obtained from the Point of Sale. In order to generate the Sales Transaction List, the user will input the date range for the required tables.

The SQL syntax swill be: SELECT COUNT (sale.isbn) AS “Number of books sold”, CONCAT (author.fname,’ ‘, author.Iname) AS “Author”, FROM sale, JOIN book ON sale.ISBN=book.isbn, RIGHT JOIN author ON, book.author_id=author.author_id, GROIUP BY author.Name, author.Iname.

The table structure will be

Monthly Profit Report

The monthly profit report can be generated from the sales table by selecting the dates where you want to see the profit report. The SQL syntax is

SELECT SUM (sales_table.cost_as_percentage_of_price) AS Profit FROM sales _table WHERE sales_transaction_date BETWEEN ‘ ’ AND ‘ ’

The Inventory status query by product

In order for the inventory status query by report to be displayed, the user must input either the CD or book name in the products table. The product_num column will be used to join the products table with the order table so that the report can be shown.

The Sales transaction that each employee has processed

Every employee’s sales transaction shall be displaying the number of times the worker has attended a customer who bought the CDs or books from the bookstore. The employee transaction SQL syntax is

SELECT COINT (sale.employee_id) AS “Number of books purchased”

Employee (employee.fname, ‘ ’, employee.Iname) AS “Customer Name”

FROM employee table

JOIN employee ON employee.employee_id=sale.employee-id

Any other output you think appropriate

The CDs and books with the most orders from the customers

REFERENCES

· Coronel, C., & Morris, S. (2017). Database systems: Design, implementation, and management

· DASFAA, Bao, Z., Trajcevski, G., Chang, L., Hua, W., BDMS, BDQM, ... DMMOOC. (2017). Database systems for advanced applications: DASFAA 2017 International Workshops: BDMS, BDQM, SeCoP, and DMMOOC, Suzhou, China, March 27-30, 2017, Proceedings

· Nguyen, N. T., Tojo, S., Nguyen, L. M., & Trawiński, B. (2017). Intelligent Information and Database Systems: 9th Asian Conference, ACIIDS 2017, Kanazawa, Japan, April 3–5, 2017, Proceedings, Part II.

7