Database questions


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 11 of 11

Thread: Database questions

Hybrid View

  1. #1
    Kofi Brown Guest

    Database questions


    I am a cont. ed student but I'm not presently enrolled. I was wondering if
    anyone could assist me in answering these questions from my "self-study"
    practice section. I'm stumped and with no professor, I don't have access
    to solutions for the problems I don't understand. Thanks.

    Computer Sciences Department frequent fliers have been complaining to Dane
    County Airport officials about the poor organization at the airport. As
    a result, the officials have decided that all information related to the
    airport should be organized using a DBMS, and you’ve been hired to design
    the database. Your first task is to organize the information about all the
    airplanes that are stationed and maintained at the airport. The relevant
    information is as follows:

    Every airplane has a registration number, and each airplane is of a specific
    model.

    The airport accommodates a number of airplane models, and each model is identified
    by a model number (e.g., DC-10) and has a capacity and a weight.

    A number of technicians work at the airport. You need to store the name,
    SSN, address, phone number, and salary of each technician.

    Each technician is an expert on one or more plane model(s), and his or her
    expertise may overlap with that of other technicians. This information about
    technicians must also be recorded.

    Traffic controllers must have an annual medical examination. For each traffic
    controller, you must store the date of the most recent exam.

    All airport employees (including technicians) belong to a union. You must
    store the union membership number of each employee. You can assume that
    the social security number uniquely identifies each employee.

    The airport has a number of tests that are used periodically to ensure that
    airplane are still airworthy. Each test has a Federal Aviation Administration
    (FAA) test number, a name, and a maximum possible score.

    The FAA requires the airport to keep track of each time that a given technician
    using a given test tests a given airplane. For each testing event, the information
    needed is the date, the number of hours the technician spent doing the test,
    and the score that the airplane received on the test.

    1. Draw an ER diagram for the airport database. Be sure to indicate the
    various attributes of each entity and relationship set; also specify the
    key and participation constraints for each relationship set. Specify any
    necessary overlap and covering constraints as well (in English).

    2. The FAA passes a regulation that a technician who is an expert on that
    model must conduct tests on a plane. How would you express this constraint
    in the ER diagram? If you cannot express it, explain briefly.


    A company database needs to store information about employees (identified
    by ssn, with salary and phone as attributes); departments (identified by
    dno, with dname and budget as attributes); and children of employees (with
    name and age as attributes). Employees work in departments; each department
    is managed by an employee; a child must be identified uniquely by name when
    the parent (who is an employee; assume that only one parent works for the
    company) is known. We are not interested in information about a child once
    the parent leaves the company.

    1. Draw an ER diagram that captures this information.
    2. Give the SQL to construct the database tables necessary for the ER diagram.


    A common topic is the issue with 1-many relationships: the “one” entity can
    be absorbed into the “many” entity, thereby reducing the number of tables.
    Give an example database of when you would do this and another example of
    when you wouldn’t, and explain why.


    Assume 40 blocks of data. Describe how the data and any redundant data might
    be spread out over a RAID 5 system with 10 disks. State how data and parity
    is mapped onto the disks (what data is on each disk), and what blocks each
    parity block is computed over, and what disk it is stored on.


    Draw a picture of what a hash database would look like after inserting the
    following data. Don’t do any splits. Assume we start with four buckets.
    Each page can hold four keys.

    40, 36, 17, 22, 25, 39, 84, 81, 68, 27, 95, 74, 24, 29,32, 55, 59, 96.

    What does the hash table look like after one split? After the split, where
    does 37 hash to?


    Construct a B+ tree for the data above. Assume that leaf nodes and index
    nodes both hold four items. Illustrate the 1st, 5th and last tree.

    Given the data above, describe and picture what the sorting process will
    look like. Assume that each data page holds just two items, that we have
    8 pages of memory available, and that we’ll use double-buffering when that’s
    useful.



    Imagine you’re setting up a database for FunTime videos franchise (one store).
    Your initial database is very simple: it has tables of customers, videos,
    and rentals. The customers have a card number, a name, and an address.
    Since the store will buy multiple copies of each movie, there will be one
    record per tape, because we have to keep track of who has what tapes, so
    that when one gets put in the drop box, the correct customers is credited
    with the return. Assume that a title is unique as a title and a director
    are unique. The rental relationship is many-to-many, since a customer can
    rent many videos, and multiple customers will rent a video. When a video
    is rented the “returned” date is null; it’s filled in when the video is returned.


    Create table customers (cardno number,
    name varchar2 (20) not null,
    address varchar2 (30),
    balance number (5, 2),
    primary key (cardno));

    Create table videos (vid number,
    title varchar2 (20) not null,
    director varchar2 (15),
    genre varchar2 (10),
    primary key (vid));

    Create table rentals (vid number,
    cardno number,
    rented date not null,
    returned date,
    returnedp number,
    primary key (cardno, vid),
    foreign key (cardno) references customers,
    foreign key (vid) references videos);

     The total and average balances among the customers.

     Names of all customers who currently have a video out

     The titles of videos that are completely out (all copies are rented).
    Note that a title should appear only once in the listing.

     The boss wants to know how many tapes we have rented, broken down
    by genre (how many dramas, comedies, westerns, etc.) so they can determine
    what genres are the best money producers.


    You’re designing a database for Delaney books. Explain how you would set
    up the database for efficient querying in multiple ways. What keys will
    you use? What indexes will you build? What alternatives for record storage
    will you use? How will you organize the database and indexes? Discuss (regarding
    probability and search costs) of various kinds of searches. Prepare to handle
    at lease 3-4 different kinds.




  2. #2
    Kent Guest

    Re: Database questions

    Asking more specific questions will result in more specific responses. Shoot
    the puck :-)

    --
    Kent
    rkcripps@softhome.net

    Kofi Brown <Kofi_K_Brown@yahoo.com> wrote in message
    news:3915cecd$1@news.devx.com...
    >
    > I am a cont. ed student but I'm not presently enrolled. I was wondering

    if
    > anyone could assist me in answering these questions from my "self-study"
    > practice section. I'm stumped and with no professor, I don't have access
    > to solutions for the problems I don't understand. Thanks.


    «snip»



  3. #3
    Kofi Brown Guest

    Re: Database questions


    Kent, the problem is that I have really no idea where to start on those problems.
    I'm learning this on my own so I'm quite lost.

    "Kent" <rkcripps@softhome.net> wrote:
    >Asking more specific questions will result in more specific responses. Shoot
    >the puck :-)
    >
    >--
    >Kent
    >rkcripps@softhome.net
    >
    >Kofi Brown <Kofi_K_Brown@yahoo.com> wrote in message
    >news:3915cecd$1@news.devx.com...
    >>
    >> I am a cont. ed student but I'm not presently enrolled. I was wondering

    >if
    >> anyone could assist me in answering these questions from my "self-study"
    >> practice section. I'm stumped and with no professor, I don't have access
    >> to solutions for the problems I don't understand. Thanks.

    >
    >«snip»
    >
    >



  4. #4
    Dave Keighan Guest

    Re: Database questions

    Hi Kofi

    > the problem is that I have really no idea where to start on those problems.

    The absolute best place to start is a good book store - I have about 400 lbs of
    VB books from VB-DOS to VB5. I'm also self taught. I've been using VB for 9
    years and have found that a lot of the people that use this (and a few other)
    news groups wrote those books. Buy a few lbs of what you're specifically
    interested in, learn and ask specific questions here.

    What you're asking for would take pages and pages to respond to. - ask them one
    at a time and you'll get answers. I have.

    >I'm learning this on my own so I'm quite lost.

    Me too, but only when I get off into some new feature of VB - it goes away -
    read, play & ask.

    Good Luck
    Dave



  5. #5
    Kofi Brown Guest

    Re: Database questions


    Thanks Dave. You'll see from my newest posts that I took your advice. I'm
    encouraged by your success with "self-study" and although I want to continue
    in school and I feel confident that it is possible not to be limited by being
    in school.

    "Dave Keighan" <dkeighan@home.com> wrote:
    >Hi Kofi
    >
    >> the problem is that I have really no idea where to start on those problems.

    >The absolute best place to start is a good book store - I have about 400

    lbs of
    >VB books from VB-DOS to VB5. I'm also self taught. I've been using VB for

    9
    >years and have found that a lot of the people that use this (and a few other)
    >news groups wrote those books. Buy a few lbs of what you're specifically
    >interested in, learn and ask specific questions here.
    >
    >What you're asking for would take pages and pages to respond to. - ask

    them one
    >at a time and you'll get answers. I have.
    >
    >>I'm learning this on my own so I'm quite lost.

    >Me too, but only when I get off into some new feature of VB - it goes away

    -
    >read, play & ask.
    >
    >Good Luck
    >Dave
    >
    >



  6. #6
    Dave Keighan Guest

    Re: Database questions

    Hi Kofi

    >You'll see from my newest posts that I took your advice.

    Can't say that statement is completly true - splitting the original message up
    wasn't really what I had in mind or what I suggested.

    Did you miss this part or my post?
    >The absolute best place to start is a good book store ...

    You read fast, you'll be a VBPro in no time.

    How about this one?
    >Buy a few lbs of what you're specifically interested in,
    >learn and ask specific questions here.

    The *learn* word was of specific import.

    One thing I forgot to mention - these people have a VERY good memories, don't
    *P* them off or you'll really need those books I mentioned or a different news
    group. The likely hood that they'll answer you're questions (and with an answer
    you can use) is usually directly proportional to their ability to get P'd off
    with you for asking them to do you're learning.

    Have a nice day - you're in for some really interesting comments!
    Dave :-o




  7. #7
    Kofi Brown Guest

    Re: Database questions


    Silly question: You and another responder refered to VB. That would stand
    for Visual Basic, correct? If not, what are you referring to, as in my Database
    Management book, I haven't come across anything referred to as VB. BTW,
    I will be visiting some book stores on your advice.

    Perhaps I did misunderstand or misinterpret your post. I thought you meant
    I should split the post up as it was confusing in its nature. Now I assume
    you mean, split them up, present my own solution (even it is incompleteness)
    and ask for direction. That does seem logical.

    My intent was never to piss anyone off. However, I received two responses
    insinuating that I was attempting to get someone else to do my "homework".
    Not so. I have attempted to contact professors who teach these courses
    (via email) and have been told that "their don't help people who aren't in
    school". After several other routes, I thought this type of forum would
    provide the understanding I sought. Unfortunately, only a few responses,
    yours included, have been positive and helpful. Hence, I'll refrain from
    posting anymore questions. If anyone can answer the previous posts or provide
    guidance, great. Thanks.

  8. #8
    Dave Keighan Guest

    Re: Database questions

    Kofi

    You did know you were in a Visual Basic news group right? vb.database.design -
    VisualBasic.Database.Design
    If you don't want the Visual Basic slant on things maybe you should try one of
    msnews.microsoft.com groups.

    Dave


    Brown" <kofi_k_brown@yahoo.com> wrote in message
    news:391742ef$1@news.devx.com...
    >
    > Silly question: You and another responder refered to VB. That would stand
    > for Visual Basic, correct? If not, what are you referring to, as in my

    Database
    > Management book, I haven't come across anything referred to as VB. BTW,
    > I will be visiting some book stores on your advice.
    >
    > Perhaps I did misunderstand or misinterpret your post. I thought you meant
    > I should split the post up as it was confusing in its nature. Now I assume
    > you mean, split them up, present my own solution (even it is incompleteness)
    > and ask for direction. That does seem logical.
    >
    > My intent was never to piss anyone off. However, I received two responses
    > insinuating that I was attempting to get someone else to do my "homework".
    > Not so. I have attempted to contact professors who teach these courses
    > (via email) and have been told that "their don't help people who aren't in
    > school". After several other routes, I thought this type of forum would
    > provide the understanding I sought. Unfortunately, only a few responses,
    > yours included, have been positive and helpful. Hence, I'll refrain from
    > posting anymore questions. If anyone can answer the previous posts or provide
    > guidance, great. Thanks.




  9. #9
    Dave Keighan Guest

    Re: Database questions

    Kofi

    > I will be visiting some book stores on your advice.

    That'll be a good place to start.

    Maybe you could post a new question asking for advice on what books to buy. Tell
    us what type of database you are trying to learn or what course you're studing -
    I'd help you here but I just don't know what you're trying to learn. Some one
    will point you in the right direction. Keep it short.

    > My intent was never to piss anyone off. However, I received two responses
    > insinuating that I was attempting to get someone else to do my "homework".

    Don't worry about, it the situation just needs some clearing up. One was an MVP
    though and they usually have to be oiled down once their pissed - but it'll be
    OK.

    >I thought this type of forum would provide the understanding I sought.

    I'd be suprised if you find a free tutor in a news-group but get into the right
    group and ask specific questions and I'm sure you'll get lots of help - just be
    specific in you're questions like "How do I set up a one to many relationship in
    xyz program?" Make sure the answer isn't in the help file or demos first.

    Good Luck
    Dave



  10. #10
    Kent Guest

    Re: Database questions

    After reading your posts and the related threads, here is what I have
    gleaned:

    1. You want to know more about databases, especially the correct way to
    design them;
    2. You have at least one book on the subject. Unfortunately, it is a little
    heady and difficult for you to understand right now. I take that this book
    is not VB specific, and that it is probably either an advanced text or a
    college text of some form; and
    3. You've inadvertently stumbled across a good case of miscommunication.

    The questions that you posed ARE advanced questions. I, like Dave Keighan,
    propose that you start by reading a good book; There are many good ones. The
    SAM's, WROX and Sybex books seem to get recommended quite often. I started
    with one of the SAM's Teach Yourself XXX in 21 Days books. For me, it
    worked. If in doubt, ask about a specific book or ask for a book
    recommendation on a specific subject. Also, do a search of déja
    (http://www.deja.com/usenet/) for previous posts.

    There is a lot to learn here. Good luck.
    --
    Kent
    rkcripps@softhome.net

    Kofi Brown <Kofi_K_Brown@yahoo.com> wrote in message
    news:3916b4b8$1@news.devx.com...
    >
    > Kent, the problem is that I have really no idea where to start on those

    problems.
    > I'm learning this on my own so I'm quite lost.
    >
    > "Kent" <rkcripps@softhome.net> wrote:
    > >Asking more specific questions will result in more specific responses.

    Shoot
    > >the puck :-)
    > >
    > >--
    > >Kent
    > >rkcripps@softhome.net
    > >
    > >Kofi Brown <Kofi_K_Brown@yahoo.com> wrote in message
    > >news:3915cecd$1@news.devx.com...
    > >>
    > >> I am a cont. ed student but I'm not presently enrolled. I was

    wondering
    > >if
    > >> anyone could assist me in answering these questions from my

    "self-study"
    > >> practice section. I'm stumped and with no professor, I don't have

    access
    > >> to solutions for the problems I don't understand. Thanks.

    > >
    > >«snip»
    > >
    > >

    >




  11. #11
    Dave Keighan Guest

    Re: Database questions

    Hi Kent

    Thanks for stepping in. If Kofi follows these suggestions he'll be on his way.

    Dave

    "Kent" <rkcripps@softhome.net> wrote in message news:39176467@news.devx.com...
    > After reading your posts and the related threads, here is what I have
    > gleaned:
    >
    > 1. You want to know more about databases, especially the correct way to
    > design them;
    > 2. You have at least one book on the subject. Unfortunately, it is a little
    > heady and difficult for you to understand right now. I take that this book
    > is not VB specific, and that it is probably either an advanced text or a
    > college text of some form; and
    > 3. You've inadvertently stumbled across a good case of miscommunication.
    >
    > The questions that you posed ARE advanced questions. I, like Dave Keighan,
    > propose that you start by reading a good book; There are many good ones. The
    > SAM's, WROX and Sybex books seem to get recommended quite often. I started
    > with one of the SAM's Teach Yourself XXX in 21 Days books. For me, it
    > worked. If in doubt, ask about a specific book or ask for a book
    > recommendation on a specific subject. Also, do a search of déja
    > (http://www.deja.com/usenet/) for previous posts.
    >
    > There is a lot to learn here. Good luck.
    > --
    > Kent
    > rkcripps@softhome.net
    >





Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center