The Mad Art Gallery wishes to maintain data on their inventory and their custome
ID: 3606644 • Letter: T
Question
The Mad Art Gallery wishes to maintain data on their inventory and their customers, artists and paintings. They may have several paintings by each artist in the gallery at one time. Paintings may be bought and sold several times. In other words, the gallery may sell a painting, then buy it back at a later date and sell it to another customer. Use the following sample data to design a relational database schema in 3NF. Show all the steps. (Note: artist and paintings are real - data from https://www.saatchiart.com Gallery Customer History Form Phone (206) 284-6783 Jackson, Elizabeth 123 -6th Avenue Austin, TX 78702 Purchases Made Artist 03 - Luca Brandi 15 - Rebecca Bradley 03 Luca Brandi 15- Robert Gendron 15 - Rebecca Bradley Title The red field Curious The red field Le bosquet Curious Purchase Date 09/17/2016 05/11/2014 10/24/2017 10/24/2017 08/21/2017 Sales Price 5,000.00 800.00 6,750.00 1,000.00 1,350.00 Gallery Customer Inventory Form: Artist Inventory (sample) Title Dimensions (inches) 19.7 x 23.56 x 0.6 36 x 36 x 1.8 9.8x 13.8 x 1.2 Artist Country Asking Price Elena Ivanova Marlise Witschi Flavio Man UK Canada Argentina Pink sunrise Seeing light everywhere La danza de San Vito $725 $1,960 $660Explanation / Answer
Answer is as follows:
The conditions for the tables to be in 3NF is that there is no transitive dependency and if x -> y than x should be super/ candidate key OR y should be prime attribute. Prime attribute is the attribute that should be present in Candidate Key.
The given data tables are divide in 3NF as follows:
Candidate Keys for each table:
Customer(Cust_Name,Phone_Num)
Artist(Artist_Name,Title)
Purchase(Painting_title,Artist)
Foriegn Key:
Customer(Artist refresnces to purchase)
Tables in 3NF:
Customer(Cust_Name,Phone_Num,Artist*, Address); //we can define full table with candidate key
Purchase(Painting_title,Artist,Purchase_Date,Sales_Price) // it defines the full table with candidate key of purchase table.
Artist(Artist_Name,Title,Dimensions,Country,Ask_Price)
By making these three tables you will get all the tables and database in 3NF.
I am enable to post the tables due to technical problems so provide to you within sometime.
If there is any query please ask in comments...
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.