Database questions.


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: Database questions.

Hybrid View

  1. #1
    Kofi Brown Guest

    Database questions.


    I am a cont. ed student but I'm not enrolled at present. Because I'm doing
    "self-study", I don't have access to solutions and I was wondering if anyone
    could solve any of the following problems from my "test your understanding"
    practice section. Any enlightenment will be appreciated. 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
    C. E. Buttles Guest

    Re: Database questions.

    I would think the appropriate approach would to be to supply your answers
    and then ask for comments. Most of us don't have a lot of time to do your
    homework for you.


    "Kofi Brown" <Kofi_K_Brown@yahoo.com> wrote in message
    news:3915ce4f$1@news.devx.com...
    >
    > I am a cont. ed student but I'm not enrolled at present. Because I'm

    doing
    > "self-study", I don't have access to solutions and I was wondering if

    anyone
    > could solve any of the following problems from my "test your

    understanding"
    > practice section. Any enlightenment will be appreciated. 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.
    >
    >
    >
    >



  3. #3
    Kofi Brown Guest

    Re: Database questions.


    Perhaps, you misread by previous post. I'm not asking anyone, especially
    anyone who doesn't have time, to do my "homework". Of 35 questions, these
    are the few that stumped me. If no one answers, then I'll just have to postpone
    my study until I can afford to re-enroll at which time, a professor could
    provide solutions. All I'm asking is that if there is an expert out there
    who immediately has some suggestions for any, not all, of the problems, they
    share it with me. Any enlightenment will be appreciated. You're the second
    person to respond negatively and I don't understand. Perhaps I should have
    broken the questions up into separate posts but other than that I have no
    idea why you took such a hostile approach.


    "C. E. Buttles" <cebuttle@sprintsvc.net> wrote:
    >I would think the appropriate approach would to be to supply your answers
    >and then ask for comments. Most of us don't have a lot of time to do your
    >homework for you.
    >
    >
    >"Kofi Brown" <Kofi_K_Brown@yahoo.com> wrote in message
    >news:3915ce4f$1@news.devx.com...
    >>
    >> I am a cont. ed student but I'm not enrolled at present. Because I'm

    >doing
    >> "self-study", I don't have access to solutions and I was wondering if

    >anyone
    >> could solve any of the following problems from my "test your

    >understanding"
    >> practice section. Any enlightenment will be appreciated. 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.
    >>
    >>
    >>
    >>

    >



  4. #4
    Michael Levy Guest

    Re: Database questions.

    I think that the issue is that most of the people that frequent this forum
    (at least I can speak for myself) do so on our own time. We don't have alot
    of time to invest in one specific question, especially the way that it was
    worded. You would probably have better luck to take a shot at the answer and
    then post it and ask for options. I'm sure that you'll get ALOT <s>. Or if
    there's something specific that you're not sure of, such as "how to model a
    many-to-many relationship or even what one is", it will be much easier to
    answer in the time constraints.

    -Mike
    --
    Michael Levy MCSD, MCDBA, MCT
    Consultant
    GA Sullivan
    michaell@gasullivan.com

    "Kofi Brown" <kofi_k_brown@yahoo.com> wrote in message
    news:39171bdb$1@news.devx.com...
    >
    > Perhaps, you misread by previous post. I'm not asking anyone, especially
    > anyone who doesn't have time, to do my "homework". Of 35 questions, these
    > are the few that stumped me. If no one answers, then I'll just have to

    postpone
    > my study until I can afford to re-enroll at which time, a professor could
    > provide solutions. All I'm asking is that if there is an expert out there
    > who immediately has some suggestions for any, not all, of the problems,

    they
    > share it with me. Any enlightenment will be appreciated. You're the

    second
    > person to respond negatively and I don't understand. Perhaps I should

    have
    > broken the questions up into separate posts but other than that I have no
    > idea why you took such a hostile approach.
    >
    >
    > "C. E. Buttles" <cebuttle@sprintsvc.net> wrote:
    > >I would think the appropriate approach would to be to supply your answers
    > >and then ask for comments. Most of us don't have a lot of time to do

    your
    > >homework for you.
    > >
    > >
    > >"Kofi Brown" <Kofi_K_Brown@yahoo.com> wrote in message
    > >news:3915ce4f$1@news.devx.com...
    > >>
    > >> I am a cont. ed student but I'm not enrolled at present. Because I'm

    > >doing
    > >> "self-study", I don't have access to solutions and I was wondering if

    > >anyone
    > >> could solve any of the following problems from my "test your

    > >understanding"
    > >> practice section. Any enlightenment will be appreciated. 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.
    > >>
    > >>
    > >>
    > >>

    > >

    >




  5. #5
    C. E. Buttles Guest

    Re: Database questions.

    On reflection, yes, I was negative, but not necessarily "hostile". It is
    just that your problem here and all the others are too involved for anyone
    to take the time to answer, especially since you don't indicate you have
    tried for a solution yourself. This one, in particular, is of major project
    dimension for a classroom. It is too much to ask of the people who frequent
    the newsgroup(s).

    I put in over 9 hours a day at client sites, go home to over 300 e-mails
    that require personal attention plus take care of whatever else needs doing
    at home or at work. The others are probably in similar situations, whereas
    you seem to have the leisure to pursue this.

    If you are getting this from a text book, try to find the teacher's edition
    so you can save yourself the trouble of reasoning it out on you own.

    Alternatively, buy some of the excellent books on the market that deal with
    SQL Server, search Web sites such as msdn.microsoft.com, swynk.com and
    others for the topics that are involved. This approach would give you
    knowledge as opposed to just answers as the first one gives.

    If you get hung up, come back here and ask a specific question that requires
    a short answer.


    "Kofi Brown" <kofi_k_brown@yahoo.com> wrote in message
    news:39171bdb$1@news.devx.com...
    >
    > Perhaps, you misread by previous post. I'm not asking anyone, especially
    > anyone who doesn't have time, to do my "homework". Of 35 questions, these
    > are the few that stumped me. If no one answers, then I'll just have to

    postpone
    > my study until I can afford to re-enroll at which time, a professor could
    > provide solutions. All I'm asking is that if there is an expert out there
    > who immediately has some suggestions for any, not all, of the problems,

    they
    > share it with me. Any enlightenment will be appreciated. You're the

    second
    > person to respond negatively and I don't understand. Perhaps I should

    have
    > broken the questions up into separate posts but other than that I have no
    > idea why you took such a hostile approach.
    >
    >
    > "C. E. Buttles" <cebuttle@sprintsvc.net> wrote:
    > >I would think the appropriate approach would to be to supply your answers
    > >and then ask for comments. Most of us don't have a lot of time to do

    your
    > >homework for you.
    > >
    > >
    > >"Kofi Brown" <Kofi_K_Brown@yahoo.com> wrote in message
    > >news:3915ce4f$1@news.devx.com...
    > >>
    > >> I am a cont. ed student but I'm not enrolled at present. Because I'm

    > >doing
    > >> "self-study", I don't have access to solutions and I was wondering if

    > >anyone
    > >> could solve any of the following problems from my "test your

    > >understanding"
    > >> practice section. Any enlightenment will be appreciated. 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.
    > >>
    > >>
    > >>
    > >>

    > >

    >



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