Assignment question... PLEASE HELP!

[Guest]

Hi, I have an assignment, and I don't know where to start with it. My
confusion centres around how many tables I should create, and which ones to
create relationships between. It's an annoyingly written question, any
help/advice for a novice would be diamond. Here is the question.....

A database stores information about flights, customers (who book flights),
and employees (or crew members). Carry out Entity/Relationship modelling for
such a database application where:
- Your data model should separate general flight information (flight number,
route) from instance related information (date/time of flight, crew,
passengers booked).
- Customers are described by: ID, name, and a contact phone number.
- Employees are described by: ID, name, and job title (e.g., pilot,
air-hostess etc.).

Continuing from the E/R model phase – or conceptual modelling - you must
implement a database capable of storing records for the above application.
The airline has just started trading, and as yet, does not have a
record-keeping system. Your task is to design and build a suitable database
for the billing-system.

The system should have 4 main components, these are: products, customers,
invoices and payments.
- The flights details component should have the ability to store date, time,
flight-number, number of passengers and number of crew for each flight.
- The customer component must be able to store a unique customer number,
customer name, phone-number, and credit-card details. Customers may have a
number of distinct credit-cards stored in the system. It is a business rule
of the airline, however that the multiple credit-card details for the same
customer, must all be distinct from each other.
- The Flight summary component must detail a reference to the actual flight
(i.e. where date, time, number of passengers etc. are stored), but must also
store the route. (Route information can just simply be: the destination
City/Airport name.)
- The Crew-Member component must detail the job-title, name, salary and
employee id of all crew-members employed by the airline.

 

[tina]

actually, the instructor gave very specific information about the entities
involved in the process, and the business requirements. from the sound of
it, your instructor expects you to have acquired some basic understanding of
relational data modeling/normalization, and has geared the assignment to
test your knowledge. so there's not much we can tell you that would be
helpful - yet still allow you to demonstrate *your* skill level in
relational design and implementation.

if you need further study of database design (most people struggle with the
concepts at first), see
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101
for numerous links to help you. also, i often recommend Database Design for
Mere Mortals by Michael Hernandez. good luck with your assignment.

hth

 

[Guest]

Hi.

My
confusion centres around how many tables I should create

You need a table for each "entity" that the database is modeling, in order
to store the attributes (fields or columns) of each instance of that entity.
For example, a table named tblCustomers stores a record for each "instance"
(customer), including the customer's first name, last name, phone number, et
cetera. Most importantly, the table uses a field or combination of fields to
uniquely identify each record in that table. Remember primary keys? That
"unique customer number" mentioned in the assignment's scenario is just such
a field in the table storing customer data.

I don't know where to start with it.

Get a pencil and paper and start drawing your model as a diagram of entities
(tables) and the information (attributes) that each entity contains. Start
with modeling the simplest entities that don't depend on any other entities,
and then work your way towards the entities that model the relationship
between two or more entities (tables) already drawn in the diagram.
tblCustomers is a good one to start with, because its table structure is
spelled out for you:

Table: tblCustomers
customer number
customer name (break this into multiple fields, because you know that
there is more than one item of info to query on)
phone number
credit card details (break this into multiple fields, because you know
that there is more than one item of info to query on)

Decide which of these fields or combination of fields in this table is the
primary key. Pay attention to this primary key, because when you get to the
entities that model relationships between entities, this primary key value
will be placed in the relationship entity as an attribute known as a foreign
key -- in order to relate the record in the relationship entity with its
record in the table that it depends upon (the foreign, or parent, table).

My
confusion centres around . . . , and which ones to
create relationships between.

Relationships model one-to-one, one-to-many, or many-to-many connections
between entities. Try to put entities together in a sentence and see whether
the verb used helps establish a connection, or relationship, between the two
entities in a one-for-one, one-to-many, or many-to-many type of situation.
For example, a publisher publishes one or many books. Each book is written
by one or many authors, and each author can write one or many books. In this
example, the publishers entity has a one-to-many relationship with the books
entity. And the books entity has a many-to-many relationship with the
authors entity.

Good luck.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.