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.

19. Transit items should be moved to delivered items

after delivery is confirmed.

20. Delivered items should linked to a particular

customer

21. 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)

c) Indicatication entity participation. (4 Marks)

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

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. (1 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 each department spends on salary payments. (4Marks)

) Write an SQL query to fetch the last five records from a table. (2 Marks)

f) Create an SQL stored procedure that prints a list of all departments indicating the number of people in each department. (3 Marks)

g) Write an SQL query to obtain the details of all

Workers who are also Managers. (2 Marks)

h) Write an SOL query to print details of the Workers

whose LAST_NAME begins with "A'. (1 Mark)

i) Write an SQL query to print details of the Workers

who joined XYZ company in June 2014. (1 Mark)

j) 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. (1 Mark)