Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2012
    Posts
    5

    Help with building a databse

    I have to build a database for a class (first time I'm doing it) and was wondering if anyone would mind looking at it to see if I did it right?

    The instructions read:

    In the museum, each work of art is described by an item code, title, and type. The museum also notes the country of origin if it is known. The museum keeps the following information about artists: name, date of birth, and dateof death (which is null for still living artists). Only data about artists for works currently owned by the museum are kept in the database. *Is the last sentence relevant, and does it affect how I constructed mine?*

    It is critical to keep track of the work of art. At any point in time, a
    work of art can be on display at the museum, held in storage, be away from the museum aspart of a traveling show, or be on loan to another gallery. If the work of art is on display at the museum, then it is also described by its location within the museum. Every traveling show is described by the city in which the show is takes place, and the start and end dates of the show. Many of the museum works may be part of a given show, and only active shows with at least one museum work of art need be represented in the database. *ditto for last sentence*

    For the loans: At present, the museum keeps the name, location (city), and telephone number of every gallery. The museum wants to retain a complete history of loaning a work of art to other galleries, and each time a work is loaned the museum wants to know the date the work is loaned and the date it was returned. The director added that occasionally the same art work can be loaned two or more times to the same gallery.


    Here is what I have

    http://i40.tinypic.com/34ik1mu.jpg

    I'm mainly interested if the relationships I have and my primary/foreign keys are ok, and if I should use a category shape to connect the artistinfo entity, since I'm not sure as this is my first time and the instructor provided little info on how to do it. I did this with visio but I guess the concept is the same and the only difference from access is the shape of the relationship lines?

    Thanks to anyone who takes the time to look it over.

  2. #2
    Join Date
    Mar 2012
    Posts
    5
    Oh, and fwiw this has to be an ERD. And the relationship I put from location to the other three is zero or one instead of exactly one as it can only be in one place, is that right? And whether I used foreign keys and primary keys correctly.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    nh
    dont think you've "got it" yet

    a musuem exists
    it has various items in collections in different galleries / locations
    depednign on how you model the musuem sapce you will have locations as a seaprate entity

    an object (pieceofart) is lcoated in a specific position.

    a potential issue is how you identify location
    fersintance is it located in say the 'industry gallery'
    or is it located in the industry gallery Area 5 section C row 15?

    I suspect youd' be better off defining musuems
    then defining galleries / storage spaces
    then defining objects
    the associating an object with a location

    the degree of detail and or validation required wil tell you how to store the location(s) and how to decide where an object physically is
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Mar 2012
    Posts
    5
    So I should do something like adding a new entity and linking it to Museum so that way it would account for the section within the museum for example?

    Would you say the rest is ok though? Like the type of relationships I've used and my foreign/primary keys with taking into account the info requested from the description. Since this is just supposed to be basic stuff and it's what will be graded primarily.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    no, Im saying I think your current model is flawed, its not how I'd expect it to be. that doesn't mean you current model is right or wrong, it means I as an individual don't' think it stacks with my perception of a museum. what Im sugegsting is you need to revisit your design and work through it to make certain its logical (ie you are storing a piece of information in the correct place.
    if it helps think of the entites as x belongs y, or x has many of y

    you should evolve your own design so you understand it, and also understand the process. virtually everyone has their own approach or ideas as to how to meet the requirement
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    May 2008
    Posts
    277
    Your first effort is pretty nice.

    One stylistic note: don't name your tables "info" (or "record" or "table") unless that's what it's actually describing. So rename your ArtistInfo table so it's just Artist.

    In general, your foreign keys appear to be reversed. If an artist creates many pieces of art, which table should the foreign key be in (art or artist)?

    Quote Originally Posted by madgasser View Post
    Only data about artists for works currently owned by the museum are kept in the database.
    This is giving you information about the cardinality of the relationship between artist and art. If an artist is in the database, how many pieces of his/her art must also be in the database? Same thing with the traveling shows.

    At any point in time, a work of art can be on display at the museum, held in storage, be away from the museum aspart of a traveling show, or be on loan to another gallery.
    The key things to note about this requirement is that a) the location of a piece of art can change over time, b) the locations are all mutually exclusive, so that at any time, a piece of art will have exactly one status, c) it appears you are only interested in recording the current location. I think your current model is close, but look at the relationship between your art and location tables, and ask yourself "How many locations can a piece of art currently be in? How many pieces of art are described by a piece of art's (current) location?"

    In addition to a piece of art's current location, you must also keep a history of loans to other galleries. Your loan table is a good start, but if you loaned 5 paintings to the Metropolitan Museum of Art, how would you record this? It may help you to visualize this if you write out what some sample data in your table would look like.

    Hopefully these are helpful suggestions.

  7. #7
    Join Date
    Mar 2012
    Posts
    5
    I've changed it up a bit now, hopefully accounting for those instances you mention.

    Here is what I have:

    http://i40.tinypic.com/s47zac.jpg

    Since the work of art can have more than one artist I've split up the m2m relationship.

    Another question I have is whether the relationship type is supposed to be identifying or not. I assume it is for my model between artwork, table1 and artist. Are any of the others supposed to be identifying (like the one between loan and gallery, since the gallery can't exist without the loan? That should be identifying no?)

  8. #8
    Join Date
    May 2008
    Posts
    277
    I think you're pretty much there. However, does loan_date and return_date describe a loan or gallery? Make sure you have attributes associated with the proper entity.

    Another question I have is whether the relationship type is supposed to be identifying or not. I assume it is for my model between artwork, table1 and artist. Are any of the others supposed to be identifying (like the one between loan and gallery, since the gallery can't exist without the loan? That should be identifying no?)
    Are you talking about weak and strong entities? Basically, you need to pay attention to the cardinality, or perhaps more precisely, the optionality of the relationship between entities. Some of these are just common sense: a loan identifies a period of time a work of art is lent to another gallery. Can you have a loan without either a work of art or a gallery? Others may be established by your requirements: while there may be many traveling art shows that don't involve the museum, we're only interested in recording those that are active and involve pieces from the museum's art collection. On the other hand, a location inside the museum will exist regardless of whether or not artwork is currently on display there. Take another look at your relationships and see if you need to make any changes.

  9. #9
    Join Date
    Mar 2012
    Posts
    5
    I see, so then if we are only interested in shows having our exhibit the location within the museum should be the only one with an optional one.

    If I put identifying for the relationship to the show entity though, it creates a ShowID foreign key in my table 1 and a ShowID foreign key in my loan. Is that right if it's like this?

    ImageShack® - Online Photo and Video Hosting

  10. #10
    Join Date
    May 2008
    Posts
    277
    The real world dragged me away for a while.

    Quote Originally Posted by madgasser View Post
    if we are only interested in shows having our exhibit
    Don't take my word for it, look at the requirements you posted.

    Another question I have is whether the relationship type is supposed to be identifying or not.
    I may have confused you with my previous answer. An identifying relationship establishes the relationship between a strong entity and a weak entity. A strong entity is an entity which exists on its own, a weak entity is an entity whose existence depends on another entity. The Wikipedia articles uses the example of an order item depending on an order. Another example is that of a city: we can not identify "Springfield" the city without also specifying its state, "Massachussetts". A weak entity will include the primary key of the associated strong entity in its own key, for example "Springfield, Massachussetts". Although keep in mind that if we expand our view beyond the USA, then state also becomes a weak entity. Although "Massachusetts" alone might be enough information to identify it as a specific state in a specific country, a state (or province, prefecture, etc.) cannot exist without a country. Also consider "Georgia", and how you would identify the American state versus the country. This means the key for state become "Massachussetts, USA", and consequently the key for city becomes "Springfield, Massachussetts, USA".

    Knowing this is certainly helpful in establishing your relationships. Can a museum room exist without art on display in it? Can a gallery exist without some of our art being loaned to it? Can a show exist without including some of our art? Keep in mind that this is a different question than do we care about galleries that don't have some of our art loaned to it, or do we care about shows that don't include some of our art. Although this second question will influence whether or not a relationship is optional, only the first question, "can it exist?" will determine whether or not a relationship is an identifying one.

    However, what I was getting at is it may not be strictly necessary to know this to figure out your relationships -- though you will most likely need to know this for a test question. Take a look at your relationship between art and show, and say or write out the relationship you've specified: "A piece of our art must be included in exactly one show. A show may include many (but also none) of our pieces of art." Ask yourself if that makes any sense, and also bounce it against your requirements to see if it's correct.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •