A local businesswoman has decided to start her own Internet business, called Masterpieces Ltd, hiring paintings to private individuals and commercial companies. 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 (eg animal, landscape, seascape, naval, still‐life, etc). A customer may hire the same painting more than once. Each painting is allocated a monthly rental price. Each painting can only have one artist associated with it. 1. Draw an ER Diagram which includes all details mentioned in the scenario. You can add your own assumptions and features. 2. Create a schema from the ER Diagram drawn above. 3. Normalize the schema into 3NF.
Assignment
A local businesswoman has decided to start her own Internet business, called Masterpieces Ltd, hiring paintings to private individuals and commercial companies.
She has called upon your services to design and implement a
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 (eg animal, landscape, seascape, naval, still‐life, etc). A customer may hire the same painting more than once. Each painting is allocated a monthly rental price. Each painting can only have one artist associated with it.
1. Draw an ER Diagram which includes all details mentioned in the scenario. You can add your own assumptions and features.
2. Create a schema from the ER Diagram drawn above.
3. Normalize the schema into 3NF.
4. Using MySQL, create a database and the tables for the finalized schema. Add a database name as you prefer.
5. Remember to identify key attributes and any foreign key attributes.
6. Insert 2 rows for all tables.
7. Write SQL statements for the following:
a. Show details of all customers in Silver category.
b. Show details of all paintings that are currently on hire to customers.
c. What is the total rental charge for all paintings which are currently on hire?
Step by step
Solved in 4 steps with 1 images
