Assignment question... PLEASE HELP!

ExpandCollapse

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

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.

ExpandCollapse

Guest

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.

"RuiCosta" <RuiCosta@discussions.microsoft.com> wrote in message
news:5BBC6F0B-B0F8-4825-93C8-BCD55FB24ACA@microsoft.com...
> 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.
>
>

ExpandCollapse

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.

"RuiCosta" wrote:
> 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.
>
>

Share This Page

can you please help me feel positive ? I would be grateful to you. I am 25 yr old male. I don't have any job. I am a fresher in my field - IT. I did my graduation in 2010. I enrolled for masters in 2011 . But I could never complete it as by then I had realized I was gay but couldn't tell my...

About Us

Our community has been around for many years and pride ourselves on offering unbiased, critical discussion among people of all different backgrounds. We are working every day to make sure our community is one of the best.

Like us on Facebook

Support SPN

The management works very hard to make sure the community is running the best software, best designs, and all the other bells and whistles. We'd really appreciate your support!