Reference no: EM131186841
Database Management Systems
Assignment - Overview
You are to create an ER diagram, and a relational schema for a small business. You need to provide a discussion of normalisation including the normal form that each entity is in, a list of relationships with all table names, primary and foreign keys, and attributes indicating their type and purpose.
Learning Outcomes Assessed
The following course learning outcomes are assessed by completing this assessment:
-Design a relational database for a provided scenario utilising tools and techniques including ER diagrams, relation models and normalisation
-Describe relational algebra and its relationship to Structured Query Language (SQL);
-Design and implement a relational database using a database management system;
Project Specification-
You have been commissioned to create a database for Flying High travel services (FHTS). FHTS is a new and specialised travel agency who cater for Australian companies with high travel needs. Their market niche is in low cost business travel packages by airlines. They keep their prices low by dealing directly with the airlines, who provide combined cheap flights and accommodation.
When a FHTS customer wishes to book business flights with accommodation, it is always done by the respective employees of that Australian company, who book online. This model is used to reduce administration costs.
When a booking is made, a FHTS staff member sources flights by airlines that contain the dates and destinations specified by the customers. Accommodation will always be included automatically in the final destination, this is provided by the airline. It may not always be the same accommodation, but is always of high quality to satisfy the customer's comfort.
The following business rules and information gathered about the current business activities will allow you to derive entities. Your submission is not expected to have many to many relationships left unresolved. You may add entities or attributes as you see fit. Assumptions can be made to include further entities and their relationships, but referential integrity and normalisation processes must be adhered to. Reasons should be given for any relation that is not maintained in 3NF.
- A company (a business client of FHTS) is referenced by a unique company number, it has a company name, phone, fax, and email contact.
- A customer of FHTS is an employee of a company who is requiring the business travel services of FHTS. Each customer can only belong to one company. A customer will have a unique customer ID, first name, last name, mobile phone number, address, post code and email address. These details are needed in case travel arrangements change and they need to be notified.
- A customer may also be a VIP of FHTS. The level of the VIP status is dependent on the number of years and the amount of money that the customer has spent with FHTS. There are three levels of VIP status, these include Gold, Silver and Bronze. A customer VIP status will give a discount to the customer booking.
- A customer can also be a frequent flyer. As a frequent flyer, a customer may receive a discount on their flights. All airlines that FHTS deal with, recognise their customer's frequent flyer status. The amount of a frequent flyer discount is only dependent on the amount of frequent flyer points they have accumulated.
- Also a frequent flyer will accumulate more points based on the number flying kilometres they accumulate. The number of kilometres travelled by a flight needs to be recorded.
- A customer can make many travel bookings with FHTS, but only one customer is allowed to be on a booking. FHTS records the date of a travel booking.
- There are many staff working at FHTS who take many bookings from the customers. However, only one staff member is allocated to each booking.
- A number of staff members are supervisors who supervise one or more other staff members. However, each staff member is supervised by only one supervisor.
- A staff member needs a unique ID, first name, last name, phone number, date of birth, start date, tax file number, home address, email address and a Supervisor ID.
- A booking also needs an attribute to state if there has been supervisor intervention for a respective booking."Yes" or "No" is all that is required.
- A booking may contain one or more flights, by one or more airlines. Each flight has only one location departure and destination.
- A flight details include a unique ID, flight number, departure location ID and location name, departure date and time, destination location ID and location name, destination date and time.
- Each flight has cabin class fares. Cabin class fares are directly associated with the cabin classes. The cabin classes are first, business and economy. A customer may have different class fares for each booking i.e. for different flights, but only one class fare for each flight.
- A cabin class fare is set by each Airline. As FHTS deals with the Airlines directly, all class fares remain fixed across all Airlines. However the overall flight fare is determined by the cabin class fare and an additional flight fee charge. FHTS stores the flight fees.
- No flights will have stop overs, all flights will have final end locations (destinations). A location will have one or many flights that can be either departure or destination flights.
- A location may have one or many accommodation sites. However, a customer will only be allocated one accommodation site at their travel location destination.
- A location will need at least, a unique ID, name and the number of days for the stay.
- Information regarding an accommodation site is kept including, unique ID, name, address details, and email address, date of arrival and date of departure.
Upgrading a from paper mode to digital mode
: You are upgrading a from paper mode to digital mode where customer is stored digitally. Present solution for a waste management to save its data using a program rather storing it on paper.
|
How many information bits are conveyed by each encoder
: Suppose that the following sequence of symbols must be transmitted: (-2, +2), (0, 0), (+2, -2), (0, 0), (-2, +2), (0, 0), . . . . Draw the corresponding waveform in each pair of wires (use the clock period, T, as your time unit).
|
Calculate and explain the resulting symbol rate
: Suppose that the following sequence of symbols is allowed and must be transmitted: (-2, -1, +1, +2), (0, +1, -1, 0), (-2, -1, +1, +2), (0, +1, -1, 0), . . . . Draw the corresponding waveform in each pair of wires using the clock period, T, of part..
|
Draw the corresponding 33-bit waveform
: Assume that the character string "Hi!", encoded using the ASCII code, must be transmitted using an asynchronous transmission protocol similar to that seen in Figure 7.2, which includes start, stop, and parity bits. Draw the corresponding 33-bit wa..
|
Create an er diagram
: ITECH 1006 - Database Management Systems. You are to create an ER diagram, and a relational schema for a small business. You need to provide a discussion of normalisation including the normal form that each entity is in, a list of relationships wit..
|
Briefly describe semiconductor doping
: Briefly compare Ge, Si, and GaAs. Which can be used for ery-low-noise amplifiers? And for light-emitting devices? Why is Si preferred whenever possible?
|
What advice should you give widgets r us
: The market price is $3 per unit, the marginal cost of the 250th unit is $2.75, average total cost is $3.50 per unit, and average variable cost is $2.50 per unit. What advice should you give Widgets R Us?
|
Give the specifications of laser printer
: 1. Explain USB Port (Universal Serial Bus). 2. Give the specifications of Laser printer. 3. Give the specifications of dot matrix printer. 4. Give the specifications of inkjet printer.
|
What is the force on particle y
: Particle X and Particle Y interact with each other.- Particle X has twice the charge of particleB.- Compared to the force on ParticleX, what is the force on particle Y.
|