Homework Solution: The Gill Art Gallery wishes to maintain data on the sales of their paintings. The art gallery buys and sales paintings…

    The Gill Art Gallery wishes to maintain data on the sales of their paintings. The art gallery buys and sales paintings so it is conceivable that they could sale the same painting twice. Normalize the Gill Art Gallery Sales Database as represented in sample data below.
    CustName CustPhone ArtistId ArtistName ArtTitle ArtMedium SaleDate SalePrice
    James 206-284-6783 03 Channing Laugh with Teeth Oil 9/17/2020 7000.00
    Greene 449-243-8765 15 Frings South toward the Sea Water Color 5/11/2021 1800.00
    James 206-284-6783 03 Channing At the Movies Acrylic 4/12/2020 5550.00
    Davis 515-636-9874 15 Frings South toward the Sea Water Color 8/11/2022 9500.00
    1. Identify functional dependencies. {These are your business rules – in this case, you will want to make those rules by looking the data – some questions include – How will you identify customers, works of arts and their artists? - Can artists have more than one work of art? Can customers buy more than one work of art? 2. Normalize the listing – with just attribute names into 3NF.

    Expert Answer

     
    1. Based on the given database we can find the below functional dependencies:

    The Gill Deceit Gallery wishes to suppress basis on the sales of their paintings. The deceit gallery subsidizes and sales paintings so it is conceivable that they could sale the similar painting twice. Normalize the Gill Deceit Gallery Sales Basisbase as represented in specimen basis beneath.

    CustName CustPhone ArtistId ArtistName ArtTitle ArtMedium SaleDate SalePrice James 206-284-6783 03 Channing Laugh with Teeth Oil 9/17/2020 7000.00 Greene 449-243-8765 15 Frings South internal the Sea Water Color 5/11/2021 1800.00 James 206-284-6783 03 Channing At the Movies Acrylic 4/12/2020 5550.00 Davis 515-636-9874 15 Frings South internal the Sea Water Color 8/11/2022 9500.00

    1. Authenticate authoritative dependencies. {These are your office rules – in this condition, you obtain failure to compel those rules by looking the basis – some questions comprise – How obtain you authenticate customers, results of deceits and their deceitists? – Can deceitists feel further than undivided result of deceit? Can customers subsidize further than undivided result of deceit?

    2. Normalize the rolling – with reasonable manifestation designates into 3NF.

    Expert Counterpart

     

    1. Based on the dedicated basisbase we can confront the beneath authoritative dependencies:

    • CustName rightarrow CustPhundivided i.e CustPhundivided is authoritatively trusting on CustName. A CustDesignate can uniquely authenticate a CustPhone
    • ArtistId rightarrow ArtistDesignate i.e DeceitistDesignate is authoritatively trusting on DeceitistId. No 2 irrelative deceitists designate can feel the similar deceitist id.
    • 1 DeceitistId can feel multiple DeceitTitle
    • 1 Deceitist can interpretation multiple DeceitMedium
    • 1 Customer can subsidize multiple Deceit

    Based on the aloft advice, we can feel beneath consultations:

    • Customer consultation with manifestations CustDesignate & CustPhone
    • Proficient consultation with manifestations DeceitistId , DeceitistName, DeceitTitle & DeceitMedium
    • BuysAndSales consultation with manifestations CustName, DeceitistId ,SaleDate & SalePrice

    2. We behold that DeceitTitle and DeceitMedium are multivalued manifestations, gone an deceitist can consequence irrelative DeceitTitle and with irrelative DeceitMedium. So we rend DeceitTitle and DeceitMedium into irrelative consultation gone 1NF disallows multivaled manifestations. Lets call this consultation as DeceitDetail containing DeceitistId as the irrelevant explanation to Deceitist consultation and DeceitTitle and DeceitMedium the other manifestations. We behold the concert of DeceitistId,ArtTitle and DeceitMedium identifies the order uniquely, consequently they are pdeceit of the explanation.

    In SubsidizesAndSale consultation, we behold that SaleDate and SalePrice is not attribuconsideration attribuconsideration attribuconsideration trusting on concert of CustDesignate and DeceitistId beside is trusting on concert of CustName,ArtistId, DeceitTilte and DeceitMedium. So by 2NF which doesn’t remit any deal-outial dependency. We scarcity to interpretation the elementary explanation of DeceitDetail consultation in SubsidizesAndSale consultation.

    Final roll of consultations and manifestations are:

    Customer(CustName,CustPhone)

    Artist(ArtistId,ArtistName)

    ArtDetail( ArtistId, DeceitTitle, DeceitMedium ) –

    BuysAndSale(ArtistId, DeceitTitle, DeceitMedium ,CustName,SaleDate,SalePrice)