The Skynet Museum Consortium (SMC), headquartered in Toronto, Canada, helps coordinate cooperation among 50 large government-sponsored...

 

 

Transcribed Image Text:

The Skynet Museum Consortium (SMC), headquartered in Toronto, Canada, helps coordinate cooperation among 50 large government-sponsored museums across North America, South America and Europe. Initially funded through an agreement between two Quebec museums, the alliance now includes contributing members in Canada, USA, England, France, Germany, Mexico, Peru and Argentina. Rhonda Cordel, current CEO of SMC,, is championing a database project that will track inter-museum loans of all types of works, including paintings, photographs, sculptures, textiles, pottery, furniture, tools, amusements, military objects and precious objects. "We need to track many things for this new database," says Rhonda. "Among these are: the actual pieces of art (we call them. "works"), the artists that created them (if known), and the member-museums that own or display them. Even though some works are already identified to world-wide agencies, many are not, so we generate our own unique number to identify all registered works. We identify our member-museums in the same way. Each museum, on average, owns 1100 works that it registers with us. This small extract below shows some of our data about some of the works and, for each work, its associated owner-museum..." Work Description Year Creato Museum Name Work type code Owner Museum# # created 1109 1110 1111 1112 1113 Mona Lita Louis XIV desk 1114 Thinker Poker Dogs Flying carpet Pharoh necklace Item Sequence# 1989 1700 1 2 1850 1975 3 1400 4 -980 r Work # Artist# 106 1299 4819 5370 770 301 157 295 2319 pntg furn scul pntg txtl 27 16 01 16 21 16 prec Chicago Museum of Science and Industry "As shown above, for each work, we use a code to specify its type (eg. pntg phot scul, txt, pott, furn', 'tool', amus', 'milt' or 'prec) - I have identified the full descriptions for these codes in an earlier paragraph. For each type, we track whether or not it requires special "Shockshield" packaging for shipping. Also, for each work, the artist_id number of the artist (if known) is recorded. This number refers to our master-list of 2400 different artists, where we also record the artist's last and given names, their public name, their date and country-code of birth, and if applicable, their date of death. All country-codes are selected from our master-list of 233 possible countries, where we track each country's name, population estimate and year the population estimate was made. In addition to the above small extract, for each museum, we track its street address, municipality, postal code and floor area in square meters." Smithsonian Chicago Museum of Science and Industry Bytown Museum Loanee Name: Royal Ontario Museum Returned_date: Royal Ontario Museum Chicago Museum of Science and Industry Vancouver Art Gallery 2020-Jul-31 2020-Jul-31 2020-Jun-30 2020-Mar-27 2020-Jul-30 "For each work, the consortium might track up to several appraisals. Not every work needs an appraisal, but the best works (perhaps the top 4000) would have an average of 3 appraisals each. Each appraisal pertains to only one work, and is identified by a unique id number generated by us. Each appraisal includes an appraisal_date, the appraised value of the work, and possibly a few words of explanation. Of course, each appraisal also includes the id number of the associated appraiser, selected from our master-list of 500 appraisers world-wide. For each appraiser, we also track their name, municipality and country-code of operation." "The sample loan-report below shows one loan consisting of 5 loan-items (each loan-item including a reference to one work#). The average member-museum negotiates just over four loans per month, and the average loan specifies 15 loan-items. To identify each loan's loan-items, a small sequence number is included (although a loan-item is not uniquely identified without also including the loan number). Even though loans are free among member museums, a gratuity, to be given by the "loanee" museum, is usually negotiated for each item, to help offset shipping costs. Also, a separate premium is charged for each loan-item to pay for "all-perils" insurance. Currently, we only keep the most recent 6 years worth of loan data. Loan #: Loaner Name: 1494 Loaner Museum #: 3 Loanee Museum #: 1 Loaner Signature Date: 2020-Jan-10 Loanee Signature Date: 2020-Jan-11 Country code Gratuity Amount: USA USA 300 700 CAN USA 55 95 1000 CAN USA Curator name Eff Date: 2020-Mar-05 R.Peters D. Wong Insurance Premium: 200 300 J. Fevrier D. Wong 20 20 350 Loan TOTAL: A. Cushenko D. Wong Exp Date: 2020-Jul-31 Cale'd Subtotal 500 1000 75 115 1350 $3040.00 Requirements: Using https://draw.io/ and the attached AsgnB_SMC_Starting Point_Fall_2023.drawio file as your starting point, create an Entity Relationship Diagram (ie, data model) of the Skynet Museum Consortium business, as shown in the HC case above. Completely and correctly specify all Entities, Relationships and Attributes as described in the lecture and lab materials on Entity Relationship Diagramming. Include all relevant facts from the case into the model, including primary keys, volume estimates, data-types for all fields, descriptive labels on relationships, and italicization of foreign key fields. Ensure that your relationship lines do NOT cross or overlay, as this makes them hard to read. There is no need to create or assume any new business entities or attributes other than those required for the case above. Your final model should have exactly NINE entities and, if printed (not required), should fit on "page". NOTE: In the "starting point" diagram, the number of lines in each entity indicates the total number of expected attributes (including all keys: PK, FK). Do NOT re-organize this diagram. All work is to be done individually. Do not copy, in whole or in part, the work of others, including paper printouts, electronic files or computer programs. Do not use the work of others as a starting point and then modify it. All work submitted under your name must be yours and yours alone. ?????? (?) PK ?????? ????????? ????????? ????????? ????????? (?) PK ?????? FK_?????? ????????? ????????? ????????? ????????? ????????? ??????? ??????? ??????? ??????? PK ?????? FK_?????? ????????? ????????? ??????? ??????? ??????? ??????? ??????? ??????? ??????? ????????? (?) ??????? ??????? ??????? ??????? ????????? (?) PK ?????? FK ?????? ????????? ????????? ????????? ????????? ????????? ????????? Work (?) PK ?????? FK_?????? FK_?????? FK_?????? ????????? ????????? ??????? ??????? ??????? ??????? ??????? ??????? ??????? ??????? PK ?????? FK_?????? FK_?????? ????????? ????????? ????????? ????? ??????? ?????? ??????? ??????? ??????? ????????? (?) ??????? ??????? ??????? ??????? ??????? ??????? ????????? (?) PK ?????? FK_?????? FK_?????? ????????? ????????? ????????? ????????? ????????? (?) ??????? PK FK ??? PK ?????? FK ?????? ????????? ????????? ????????? ??????? ??????? ??????? ??????? ??????? ??????? ??????? PK ?????? ????????? (?) ????????? ????????? ??????? ??????? ??????? ??????? ??????? ??????? ??????? ??????? The Skynet Museum Consortium (SMC), headquartered in Toronto, Canada, helps coordinate cooperation among 50 large government-sponsored museums across North America, South America and Europe. Initially funded through an agreement between two Quebec museums, the alliance now includes contributing members in Canada, USA, England, France, Germany, Mexico, Peru and Argentina. Rhonda Cordel, current CEO of SMC,, is championing a database project that will track inter-museum loans of all types of works, including paintings, photographs, sculptures, textiles, pottery, furniture, tools, amusements, military objects and precious objects. "We need to track many things for this new database," says Rhonda. "Among these are: the actual pieces of art (we call them. "works"), the artists that created them (if known), and the member-museums that own or display them. Even though some works are already identified to world-wide agencies, many are not, so we generate our own unique number to identify all registered works. We identify our member-museums in the same way. Each museum, on average, owns 1100 works that it registers with us. This small extract below shows some of our data about some of the works and, for each work, its associated owner-museum..." Work Description Year Creato Museum Name Work type code Owner Museum# # created 1109 1110 1111 1112 1113 Mona Lita Louis XIV desk 1114 Thinker Poker Dogs Flying carpet Pharoh necklace Item Sequence# 1989 1700 1 2 1850 1975 3 1400 4 -980 r Work # Artist# 106 1299 4819 5370 770 301 157 295 2319 pntg furn scul pntg txtl 27 16 01 16 21 16 prec Chicago Museum of Science and Industry "As shown above, for each work, we use a code to specify its type (eg. pntg phot scul, txt, pott, furn', 'tool', amus', 'milt' or 'prec) - I have identified the full descriptions for these codes in an earlier paragraph. For each type, we track whether or not it requires special "Shockshield" packaging for shipping. Also, for each work, the artist_id number of the artist (if known) is recorded. This number refers to our master-list of 2400 different artists, where we also record the artist's last and given names, their public name, their date and country-code of birth, and if applicable, their date of death. All country-codes are selected from our master-list of 233 possible countries, where we track each country's name, population estimate and year the population estimate was made. In addition to the above small extract, for each museum, we track its street address, municipality, postal code and floor area in square meters." Smithsonian Chicago Museum of Science and Industry Bytown Museum Loanee Name: Royal Ontario Museum Returned_date: Royal Ontario Museum Chicago Museum of Science and Industry Vancouver Art Gallery 2020-Jul-31 2020-Jul-31 2020-Jun-30 2020-Mar-27 2020-Jul-30 "For each work, the consortium might track up to several appraisals. Not every work needs an appraisal, but the best works (perhaps the top 4000) would have an average of 3 appraisals each. Each appraisal pertains to only one work, and is identified by a unique id number generated by us. Each appraisal includes an appraisal_date, the appraised value of the work, and possibly a few words of explanation. Of course, each appraisal also includes the id number of the associated appraiser, selected from our master-list of 500 appraisers world-wide. For each appraiser, we also track their name, municipality and country-code of operation." "The sample loan-report below shows one loan consisting of 5 loan-items (each loan-item including a reference to one work#). The average member-museum negotiates just over four loans per month, and the average loan specifies 15 loan-items. To identify each loan's loan-items, a small sequence number is included (although a loan-item is not uniquely identified without also including the loan number). Even though loans are free among member museums, a gratuity, to be given by the "loanee" museum, is usually negotiated for each item, to help offset shipping costs. Also, a separate premium is charged for each loan-item to pay for "all-perils" insurance. Currently, we only keep the most recent 6 years worth of loan data. Loan #: Loaner Name: 1494 Loaner Museum #: 3 Loanee Museum #: 1 Loaner Signature Date: 2020-Jan-10 Loanee Signature Date: 2020-Jan-11 Country code Gratuity Amount: USA USA 300 700 CAN USA 55 95 1000 CAN USA Curator name Eff Date: 2020-Mar-05 R.Peters D. Wong Insurance Premium: 200 300 J. Fevrier D. Wong 20 20 350 Loan TOTAL: A. Cushenko D. Wong Exp Date: 2020-Jul-31 Cale'd Subtotal 500 1000 75 115 1350 $3040.00 Requirements: Using https://draw.io/ and the attached AsgnB_SMC_Starting Point_Fall_2023.drawio file as your starting point, create an Entity Relationship Diagram (ie, data model) of the Skynet Museum Consortium business, as shown in the HC case above. Completely and correctly specify all Entities, Relationships and Attributes as described in the lecture and lab materials on Entity Relationship Diagramming. Include all relevant facts from the case into the model, including primary keys, volume estimates, data-types for all fields, descriptive labels on relationships, and italicization of foreign key fields. Ensure that your relationship lines do NOT cross or overlay, as this makes them hard to read. There is no need to create or assume any new business entities or attributes other than those required for the case above. Your final model should have exactly NINE entities and, if printed (not required), should fit on "page". NOTE: In the "starting point" diagram, the number of lines in each entity indicates the total number of expected attributes (including all keys: PK, FK). Do NOT re-organize this diagram. All work is to be done individually. Do not copy, in whole or in part, the work of others, including paper printouts, electronic files or computer programs. Do not use the work of others as a starting point and then modify it. All work submitted under your name must be yours and yours alone. ?????? (?) PK ?????? ????????? ????????? ????????? ????????? (?) PK ?????? FK_?????? ????????? ????????? ????????? ????????? ????????? ??????? ??????? ??????? ??????? PK ?????? FK_?????? ????????? ????????? ??????? ??????? ??????? ??????? ??????? ??????? ??????? ????????? (?) ??????? ??????? ??????? ??????? ????????? (?) PK ?????? FK ?????? ????????? ????????? ????????? ????????? ????????? ????????? Work (?) PK ?????? FK_?????? FK_?????? FK_?????? ????????? ????????? ??????? ??????? ??????? ??????? ??????? ??????? ??????? ??????? PK ?????? FK_?????? FK_?????? ????????? ????????? ????????? ????? ??????? ?????? ??????? ??????? ??????? ????????? (?) ??????? ??????? ??????? ??????? ??????? ??????? ????????? (?) PK ?????? FK_?????? FK_?????? ????????? ????????? ????????? ????????? ????????? (?) ??????? PK FK ??? PK ?????? FK ?????? ????????? ????????? ????????? ??????? ??????? ??????? ??????? ??????? ??????? ??????? PK ?????? ????????? (?) ????????? ????????? ??????? ??????? ??????? ??????? ??????? ??????? ??????? ???????