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 Calling Gallery wishes to detain facts on the sales of their paintings. The calling gallery bribes and sales paintings so it is conceivable that they could sale the similar painting twice. Normalize the Gill Calling Gallery Sales Factsbase as represented in exemplification facts under.

    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. Test authoritative dependencies. {These are your calling rules – in this circumstance, you accomplish failure to shape those rules by looking the facts – some questions embrace – How accomplish you test customers, operations of callings and their callingists? – Can callingists enjoy past than individual operation of calling? Can customers bribe past than individual operation of calling?

    2. Normalize the inventorying – with honest characteristic spectrys into 3NF.

    Expert Confutation

     

    1. Based on the ardent factsbase we can confront the under authoritative dependencies:

    • CustName rightarrow CustPhindividual i.e CustPhindividual is authoritatively subject on CustName. A CustSpectry can uniquely test a CustPhone
    • ArtistId rightarrow ArtistSpectry i.e CallingistSpectry is authoritatively subject on CallingistId. No 2 unanalogous callingists spectry can enjoy the similar callingist id.
    • 1 CallingistId can enjoy multiple CallingTitle
    • 1 Callingist can verification multiple CallingMedium
    • 1 Customer can bribe multiple Calling

    Based on the aloft counsel, we can enjoy under considerations:

    • Customer consideration with characteristics CustSpectry & CustPhone
    • Proficient consideration with characteristics CallingistId , CallingistName, CallingTitle & CallingMedium
    • BuysAndSales consideration with characteristics CustName, CallingistId ,SaleDate & SalePrice

    2. We descry that CallingTitle and CallingMedium are multivalued characteristics, gone an callingist can result unanalogous CallingTitle and with unanalogous CallingMedium. So we separate CallingTitle and CallingMedium into unanalogous consideration gone 1NF disallows multivaled characteristics. Lets call this consideration as CallingDetail containing CallingistId as the exotic clew to Callingist consideration and CallingTitle and CallingMedium the other characteristics. We descry the union of CallingistId,ArtTitle and CallingMedium identifies the order uniquely, consequently they are pcalling of the clew.

    In BribesAndSale consideration, we descry that SaleDate and SalePrice is referable subject on union of CustSpectry and CallingistId except is subject on union of CustName,ArtistId, CallingTilte and CallingMedium. So by 2NF which doesn’t apportion any allotial dependency. We insufficiency to verification the pristine clew of CallingDetail consideration in BribesAndSale consideration.

    Final inventory of considerations and characteristics are:

    Customer(CustName,CustPhone)

    Artist(ArtistId,ArtistName)

    ArtDetail( ArtistId, CallingTitle, CallingMedium ) –

    BuysAndSale(ArtistId, CallingTitle, CallingMedium ,CustName,SaleDate,SalePrice)