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 Adroitness Gallery wishes to sustain facts on the sales of their paintings. The adroitness gallery subsidizes and sales paintings so it is conceivable that they could sale the corresponding painting twice. Normalize the Gill Adroitness Gallery Sales Factsbase as represented in scantling facts adown.

    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 inside 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 inside the Sea Water Color 8/11/2022 9500.00

    1. Confirm professional dependencies. {These are your matter rules – in this predicament, you get absence to mould those rules by looking the facts – some questions enclose – How get you confirm customers, exertions of adroitnesss and their adroitnessists? – Can adroitnessists own past than single exertion of adroitness? Can customers subsidize past than single exertion of adroitness?

    2. Normalize the inventorying – with righteous sign designates into 3NF.

    Expert Solution

     

    1. Based on the attached factsbase we can confront the adown professional dependencies:

    • CustName rightarrow CustPhsingle i.e CustPhsingle is professionally contingent on CustName. A CustDesignate can uniquely confirm a CustPhone
    • ArtistId rightarrow ArtistDesignate i.e AdroitnessistDesignate is professionally contingent on AdroitnessistId. No 2 incongruous adroitnessists designate can own the corresponding adroitnessist id.
    • 1 AdroitnessistId can own multiple AdroitnessTitle
    • 1 Adroitnessist can reason multiple AdroitnessMedium
    • 1 Customer can subsidize multiple Adroitness

    Based on the overhead instruction, we can own adown considerations:

    • Customer consideration with signs CustDesignate & CustPhone
    • Master consideration with signs AdroitnessistId , AdroitnessistName, AdroitnessTitle & AdroitnessMedium
    • BuysAndSales consideration with signs CustName, AdroitnessistId ,SaleDate & SalePrice

    2. We look that AdroitnessTitle and AdroitnessMedium are multivalued signs, past an adroitnessist can effect incongruous AdroitnessTitle and with incongruous AdroitnessMedium. So we rive AdroitnessTitle and AdroitnessMedium into incongruous consideration past 1NF disallows multivaled signs. Lets ole this consideration as AdroitnessDetail containing AdroitnessistId as the exotic clew to Adroitnessist consideration and AdroitnessTitle and AdroitnessMedium the other signs. We look the alliance of AdroitnessistId,ArtTitle and AdroitnessMedium identifies the grade uniquely, accordingly they are padroitness of the clew.

    In SubsidizesAndSale consideration, we look that SaleDate and SalePrice is not attribuconsultation attribuconsultation attribuconsultation contingent on alliance of CustDesignate and AdroitnessistId excepting is contingent on alliance of CustName,ArtistId, AdroitnessTilte and AdroitnessMedium. So by 2NF which doesn’t grant any divorceial dependency. We need to reason the pristine clew of AdroitnessDetail consideration in SubsidizesAndSale consideration.

    Final inventory of considerations and signs are:

    Customer(CustName,CustPhone)

    Artist(ArtistId,ArtistName)

    ArtDetail( ArtistId, AdroitnessTitle, AdroitnessMedium ) –

    BuysAndSale(ArtistId, AdroitnessTitle, AdroitnessMedium ,CustName,SaleDate,SalePrice)