A local businesswoman has decided to start her own Internet business, called Masterpieces Ltd, hiring paintings to private individuals and commercial companies. Because of your reputation as a database designer she has called upon your services to design and implement a database to support her new business. At the initial planning meeting, to discuss the design, the following user requirements were requested. The system must be able to manage the details of customers, paintings and those paintings currently on hire to customers. Customers are categorized as B (bronze), S (silver), G (gold) or P (platinum). These categories entitle a customer to a discount of 0%, 5%, 10% or 15% respectively. Customers often request paintings by a particular artist or theme (e.g. animal, landscape, seascape, naval, still‐life, etc). Over time a customer may hire the same painting more than once. Each painting is allocated a customer monthly rental price defined by the owner. The owner of the painting is then paid 10% of that customer rental price. Any paintings that are not hired within six months are returned to the owner. However, after three months, an owner may resubmit a returned painting. Each painting can only have one artist associated with it. Several reports are required from the system.                                                                                            (12 Marks) 

A) Create the table of customer, owner and artist.

 

B) Write a query for each customer, a report showing an overview of all the paintings they have hired or are currently hiring

 

C) Write a query for each artist, a report of all paintings submitted for hire.

 

D)  Write a query for each artist, a returns report for those paintings not hired over the past six months Remember to identify key attributes and any foreign key attributes.

 

E) Write a query to fetch the records of those customers that resides in Rawalpindi.

 

F) Write a query to fetch the records of artist that age between 23 and 70.

 

G) Write a query to fetch the records of customer that lies in gold category and discount offers 15%.

 

H) Write a query to display a list of paintings and artists that are not hired within six months.

 

I) Create a sequence on customer no.

 

J) Apply a unique key constraint on painting title.

 

K) Write a query to display unique records of rental report.

 

L) Write a query to count the total number of returned paintings dated between 2020 and 2021.

 

solve all parts and all steps completely and show complete solution

Transcribed Image Text:

Artist Report Artist No Artist Name: Country of Birth Painting No Painting Title Theme Artist Report Rental Price (monthly) Year of Birth Year of Death Owner No No Age: (if applicable) Owner Name Owner Tel Return to Owner Report Owner No: Painting No Return To Owner Report Painting Title Owner Name: Owner Address: Return Date Artist Report Artist No Artist Name: Country of Birth Painting No Painting Title Theme Artist Report Rental Price (monthly) Year of Birth Year of Death Owner No No Age: (if applicable) Owner Name Owner Tel Return to Owner Report Owner No: Painting No Return To Owner Report Painting Title Owner Name: Owner Address: Return Date

Answer rating: 100% (QA)

Before going deeper to face with problems company has and how Eeva software can solve them the following chapters describe the procedures in the field of software development and define some accountin... View the full answer