Established in 1914, ABC-books is well-known in Accra, Kumasi and Tamale. The shop has operated manually since its inception, however, since 2000 ABC -books has witnessed a consistent decline in sales across all its branches. This has compelled management of ABC-book shop to go online with their activities. The following requirements were gathered after interacting with management, employees and customers of ABC -books: 1. ABC-book has 3 branches. 2. Each branch of ABC has a unique branch number, branch name, address and phone number. 3. Each branch stocks a quantity of books from publishers. 4. Each publisher has a name, address, phone number and web address. 5. Each publisher publishes books from authors. 6. Each book has year, edition, price, title, ISBN and DIO. 7. Each book is written by an author. 8. Each author has a name, address, title, specialty, date of birth and nationality. 9. The online bookshop should have a distinct customer accounts. 10. Each customer account should have a distinct user identity which should be linked to individual customers in the system 11. The system should capture each customer's name, national identity, gender, date of birth, email, address and phone number 12. Each customer account should be linked to a unique cart with a cart number 13. A cart takes a number of books and a cart can have a number of selected items 14. Selected items should have unique a quotation number, number of selected items, payment status. 15. Selected items is linked to payment. 16. Payment should have unique receipt number, amount paid, outstanding balance. 17. Payment is linked to customer accounts. 18. Selected items should be moved to Transit items after payment. 19. Transit items should have unique tracking code, current location, date, time, destination delivery confirmation. 20. Transit items should be moved to delivered items after delivery is confirmed. 21. Delivered items should linked to a particular customer 22. Delivered items have delivery date, time, signature, location, status, remarks. Question 1: Using entity relationship diagrams, construct a conceptual model for the proposed ABC online bookshop. Use assumptions where necessary. (20 Marks) a) Identify all entities. (4 Marks) b) Identify attributes of entities. (4 Marks) c) Identify Relationships. (4 Marks) d) Indicate cardinality. (4 Marks) e) Indicatication entity participation. (4 Marks) 2 Part II: Database implementation Question 2. Using MySQL workbench implement the conceptual model of ABC online bookshop and submit the working SQL script as a text file through the LMS. Indicate the following information on your SQL script:(20 Marks) a) Schema b) Activiation c) Tables d) Comments (The comment should include student index number as developer of the system, date and time of implementation and assumption use in the design.) Part II: Database Application Question 3. The attached text file XYX_LTD is an executable SQL script containing workers informations at XYZ company ltd. Execute the script on my SQL workbench and use it to answer the following questions: (20 Marks) a) Write an SQL command to replace any of the workers in the database with yourself. Use your student ID and names to replace the work ID and names. Both the SQL code and the execution output are required. Provide the execution output in picture format in your answer sheet. (4 Marks) b) Write an SQL query to obtain the names of workers who earn the highest salary. (I Marks) c) Write an SQL query to print "FIRST_NAME" from Worker table in upper case and the "LAST_NAME in lower case. (1 Marks) d) Create an SQL stored procedure to obtain a list of departments and total amount cach department spends on salary payments. (4 Marks) c) Write an SQL query to fetch the last five records from a table. (2 Marks) ) Create an SQL stored procedure that prints a list of all departments indicating the number of people in each department. (3 Marks) 8) Write an SQL query to obtain the details of all Workers who are also Managers. (2 Marks) h) Write an SQL query to print details of the Workers whose LAST_NAME begins with A. (1 Mark) ) Write an SQL query to print details of the Workers who joined XYZ company in June 2014. (I Mark) 1) Write an SQL query to combine the FIRST_NAME and LAST_NAME of workers in a single column called COMPLETE_NAME and their salaries in another column. (I Mark)