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.

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.
>
>
>
>

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.
>>
>>
>>
>>
>

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.
> >>
> >>
> >>
> >>
> >
>

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.
> >>
> >>
> >>
> >>
> >
>