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 adhere-to facts on the sales of their paintings. The deceit gallery suborns and sales paintings so it is conceivable that they could sale the selfselfidentical painting twice. Normalize the Gill Deceit Gallery Sales Factsbase as represented in pattern facts underneath.

    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. Realize negotiative dependencies. {These are your office rules – in this fact, you gain deficiency to execute those rules by looking the facts – some questions conceive – How gain you realize customers, performances of deceits and their deceitists? – Can deceitists possess over than single performance of deceit? Can customers suborn over than single performance of deceit?

    2. Normalize the inventorying – with lawful attainment indicates into 3NF.

    Expert Solution

     

    1. Based on the consecrated factsbase we can discover the underneath negotiative dependencies:

    • CustName rightarrow CustPhsingle i.e CustPhsingle is negotiatively trusting on CustName. A CustIndicate can uniquely realize a CustPhone
    • ArtistId rightarrow ArtistIndicate i.e DeceitistIndicate is negotiatively trusting on DeceitistId. No 2 opposed deceitists indicate can possess the selfselfidentical deceitist id.
    • 1 DeceitistId can possess multiple DeceitTitle
    • 1 Deceitist can verification multiple DeceitMedium
    • 1 Customer can suborn multiple Deceit

    Based on the over instruction, we can possess underneath boards:

    • Customer board with attainments CustIndicate & CustPhone
    • Proficient board with attainments DeceitistId , DeceitistName, DeceitTitle & DeceitMedium
    • BuysAndSales board with attainments CustName, DeceitistId ,SaleDate & SalePrice

    2. We conceive that DeceitTitle and DeceitMedium are multivalued attainments, since an deceitist can result opposed DeceitTitle and with opposed DeceitMedium. So we separate DeceitTitle and DeceitMedium into opposed board since 1NF disallows multivaled attainments. Lets allure this board as DeceitDetail containing DeceitistId as the alien explanation to Deceitist board and DeceitTitle and DeceitMedium the other attainments. We conceive the alliance of DeceitistId,ArtTitle and DeceitMedium identifies the degree uniquely, accordingly they are pdeceit of the explanation.

    In SubornsAndSale board, we conceive that SaleDate and SalePrice is not attribuconsideration attribuconsideration attribuconsideration trusting on alliance of CustIndicate and DeceitistId barring is trusting on alliance of CustName,ArtistId, DeceitTilte and DeceitMedium. So by 2NF which doesn’t suffer any disuniteial dependency. We insufficiency to verification the original explanation of DeceitDetail board in SubornsAndSale board.

    Final inventory of boards and attainments are:

    Customer(CustName,CustPhone)

    Artist(ArtistId,ArtistName)

    ArtDetail( ArtistId, DeceitTitle, DeceitMedium ) –

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