Mr.Steve, a local businessman and real estate owner, has decided to expand business into paintings. He decides to register it as an Internet business, called Masterpieces Limited. It aims to lease the paintings to private individuals and commercial companies owned by masterpieces, as well as different painters or artists. He has been looking for consulting and designing companies. As a database designer, you need to help him for his services to design and implement a database to flourish his new business. In the initial phase, the following user requirements were discussed. 1. 1. The system must be able to manage the details of staff, customers, paintings and paintings available on hire. 2. 2. Customers are categorized as Regular (R), Loyal(L), privileged(P) and VIP(V) and charged accordingly. If customers ask for a discount, they are entitled to a discount of 15%, 10 %, 5% and 0%. 3. 3. As masterpieces allow you to buy or lease paintings, Customers can request painting by artist or theme like (landscape, persons or animals). A customer can lease the same painting more than once and even lease their own painting. Each painting has a certain cost as a monthly rental price offered by the owner (painter or artist). The owner is then paid 20% off the allocated rental price. 4. 4. Any paintings that are not leased within four months are returned to the owner. However, after return, an owner may resubmit a returned painting after one month. Each painting can only have one artist associated with it. Use SQL to solve the following questions. Information query (10 Marks) 1. List all customers according to category 2. List paintings and their artist with monthly rental price and paid price. 3. Show total staff in Masterpieces Limited sorted by higher salary. 4. Show paintings leased before and currently by any one customer. 5. List all paintings that have been returned to the owner.

Transaction query (10 Marks) 1. List the number of paintings available for rent according to category.

Page | 6

2. List the details of paintings that have not been leased within three months. 3. List the details of customers who have leased the painting more than four times. 4. List top 5 paintings based on total collected rental amount. 5. Show the name of the painter and their paintings sold value (in total) for the current month.