If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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.

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.

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

Re: Database questions

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.

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.

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

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

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.

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.

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.

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.

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
>