MIS311 Assignment A: Data modeling with PowerPoint
Legacy Alliance (LA)
The Legacy Alliance, headquartered in Ottawa, Canada, helps coordinate cooperation among 30 major 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, the United States, England, France, Germany, Mexico, Peru and Argentina. Portia Peraboe, head of Member Services, is championing a database project that will track inter-museum loans of all types of artifacts, including paintings, photographs, sculptures, textiles, pottery, furniture, tools, military objects and precious objects.
“We need to track many things for this new database,” says Portia. “Foremost among these are the works of art (called artifacts), the artisans that created them (if known), and the member-museums that own or display them. Even though some artifacts are already identified to world-wide agencies, many are not, so we generate our own unique number to identify all registered artifacts. We identify our member-museums in the same way. Each museum, on average, owns 500 artifacts that it registers with us. This small extract below shows some of our data about some of the artifacts and, for each artifact, its associated owner-museum…”
Artifact # / Description / Year created / CreatorArtisan# / Artifact type code / Owner Museum# / Museum Name / Country code / Conservator name
1109 / Mona Lita / 1989 / 106 / pntg / 27 / Smithsonian / USA / R.Peters
1110 / Louis XIV desk / 1700 / 1299 / furn / 16 / Chicago Museum of Science and Industry / USA / D. Wong
1111 / Thinker / 1850 / 770 / scul / 01 / Royal Ontario Museum / CAN / J. Fevrier
1112 / Poker Dogs / 1975 / 301 / pntg / 16 / Chicago Museum of Science and Industry / USA / D. Wong
1113 / Flying carpet / 1400 / txtl / 21 / Vancouver Art Gallery / CAN / A. Cushenko
1114 / Pharoh necklace / -980 / prec / 16 / Chicago Museum of Science and Industry / USA / D. Wong
“As shown above, for each artifact, we use a code to specify its type (‘pntg’, ‘phot’, ‘scul’, ‘txtl’, ‘pott’, ‘furn’, ‘tool’, ‘milt’ or ‘prec’) – I identified the full descriptions of these codes in my earlier statements. Also, for each artifact, the artisan_id number of the artisan (if known) is recorded. This number refers to our master-list of 1500 different artisans, where we also record the artisan’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 230 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.”
“For each artifact, the alliance might track up to several appraisals. Not every artifact needs an appraisal, but the premier artifacts (perhaps the top 2000) would have an average of 3 appraisals each. Each appraisal pertains to only one artifact, and is identified by a unique id number generated by us. Each appraisal includes an appraisal_date, the appraised value of the artifact, 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 300 appraisers world-wide. For each appraiser, we also track their name, municipality and country-code of operation.”
“The average member-museum negotiates just over two loans per month. The average loan specifies 10 artifacts. Even though the loan of artifacts is free to member museums, an honorarium, to be paid 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 4 years worth of loan data. This sample loan-form shows one loan consisting of 5 loan-items (artifacts).
Loan #:1494 / Loaner Museum #: 3 / Loaner Name:
Bytown Museum / Loaner Signature Date:
2011-Jan-10 / Eff Date:
2011-Mar-05 / Exp Date: 2011-Jul-31
Loanee Museum #: 1 / Loanee Name: Royal Ontario Museum / Loanee Signature Date:
2011-Jan-11
Item Sequence# / Artifact # /
Returned_date:
/ Honorarium Amount: / Insurance Premium: / Calc’d Subtotal1 / 4819 / 2011-Jul-31 / 300 / 200 / 500
2 / 5370 / 2011-Jul-31 / 700 / 300 / 1000
3 / 157 / 2011-Jun-30 / 55 / 20 / 75
4 / 295 / 2011-Mar-27 / 95 / 20 / 115
5 / 2319 / 2011-Jul-30 / 1000 / 350 / 1350
/
Loan TOTAL:
/ $3040.00Requirements:
Using the PowerPoint document called AsgnA_Legacy_Model_Starting_point.pptx, create an Entity Relationship Diagram (ie, data model) of the Legacy Alliance business, as shown in the LA case above. Starting from the provided “starting point” diagram, 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 ONE 8.5” x 11” page. NOTE: In the “starting point” diagram, the number of lines with ‘x’s indicates the total number of expected attributes (including all keys). 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.
Recommended approach:
A) Produce an interim model, by reviewing the information provided in the LA case above, concentrating on entities, primary keys, volumes, relationships and foreign keys. Include all relevant facts. Use good, consistent naming conventions. Label all entities, keys and relationships properly. You do not have to deliver this interim model.
B) Produce a final model by adding attributes to your interim model, taken from the LA case above. Because this work is BOTTOM-UP, there is a risk that attributes will be added to the “wrong” entities. Use normalization rules to ensure attributes are added to the “best” entities, including the creation of new entities if necessary. Use good naming conventions.
Marking Scheme:
Assignment A: DATA MODEL
-Correct Entities (Well named, identified, volume estimates)
-Correct Relationships (Connected to correct entities, cardinalities (1-M? M-1?) correct, well described, NO crossing of lines)
-Correct Attributes (Each in the correct entity, well named, correct data type, primary keys underlined and foreign keys italicized)
-Submitted file created using PowerPoint, named correctly, delivered electronically through Blackboard, on time.
Cheating and Plagiarism:
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.
The University of Alberta is committed to the highest standards of academic integrity and honesty. Students are expected to be familiar with these standards regarding academic honesty and to uphold the policies of the University in this respect. Students are particularly urged to familiarize themselves with the provisions of the Code of Student Behavior (online at www.ualberta.ca/secretariat/appeals.htm) and avoid any behavior that could potentially result in suspicions of cheating, plagiarism, misrepresentation of facts and/or participation in an offence. Academic dishonesty is a serious offence and can result in suspension or expulsion from the University.
Submission Instructions:
Look to the Blackboard Assignment A (ERD) object for detailed submission instructions.