Produce a set of relations

Assignment Help Database Management System
Reference no: EM13687755

Part -1:

Prepare the following:

a) An ER diagram for the system. Show all entities, relationships, cardinalities and optionalities. Also, include all intersection entities. You must use the Finkelstein methodology as per the study book and tutorials.

b) A list of relations (equivalent to Finkelstein entity list). Produce complete relations for all entities and attributes. Show all primary and foreign keys. Include all attributes that are specifically mentioned and all key attributes. You may need to create primary and foreign keys that are not specifically mentioned. You must use the Finkelstein methodology as per the study book and tutorials.

c) A SQL CREATE TABLE statement for the ITEM relation along with the creation of primary and foreign key constraints as required.

Part -2:

Produce a set of relations (equivalent to the Finkelstein entity list) in third normal form (3NF) from the following un-normalised relation for a flight reservation system. You must use the Finkelstein methodology as used in the study book and tutorials.

FLIGHT (departure airport code, departure airport name, departure time, departure date, arrival airport code, arrival airport name, arrival time, arrival date,  aircraft name, manufacture date, first class seats, business class seats, economy class seats, ((flight crew member, date joined, hours worked, crew member position, ((crew member role)) )), (( booking number, booking type, booking date, ((passenger surname, passenger first name, passenger dob, passenger contact number, seat number, special requests)), ticket price, booking fee))

Notes:

  1. Name of an aircraft can uniquely identify any aircraft.
  2. The number of seats in the economy, business and first class can be different for each aircraft.
  3. Each flight has a service of several crew members. Each crew member has one position but can have multiple roles such as cabin service and supervisor that must be recorded.
  4. The number of hours that each crew member worked in a particular flight regardless of their role is stored in the "hours worked" attribute.
  5. A departure airport for one flight can be an arrival airport for another flight and vice versa. Hence, it is important to uniquely identify an airport.
  6. A particular booking can be of two types: direct or through agent.
  7. Ticket price can be different for every flight under different bookings.
  8. Seat number and special requests by a passenger are associated with the corresponding flight and booking reference along with the passenger.

Part -3:

For each question, four marks will be awarded for the query and one mark for the screen capture of the results obtained after executing the query. Please copy the screenshot and paste it in your assignment solution document for every question.

The following E-R diagram represents a Car Hire database.

1383_ER diagram for the system.png

In this question, you will use the CAR HIRE database. The CAR HIRE database including appropriate data will be made available on the USQ Oracle server. You may query any of these tables but may not make changes.

Write SQL queries to solve the following specifications. Provide the queries and the output.

1. Display full details of all car bookings where (a) the odometer reading of cars is greater than 30,000 miles at the start of their rental and the reservation was done by a staff called REDMOND, OR (b) the car has not been returned and the car model name ends with 'TR'. Order the output by the date on which the booking was made, showing the most recent date first.

2. Using a set operator, find out all names of the customers that have not had any bookings.

3. Display the booking number, rental rate per day, rental period and the current status of the car for all car bookings. Filter your results to show only the records where the car is on hire and the rental rate per day is greater than $35.

4. Display the car registration number and the average miles travelled in all booking by each car where the average miles travelled is less than 1,000 miles and registration number begins with H.

5. Display the total rental income to date from the least expensive car available for rental. Format the output as a currency value (i.e. formatted as $9,999.99).

6. Display the car registration, rental cost, mileage of the car when it was last serviced and current mileage of the car for all completed bookings where the car is rented more than 30 months after it was bought.

7. Display booking number, date on which the booking was made and the cost of the rental for all the bookings made in the year 2012 and the car is already returned. Also include the name and full description of the corresponding model of the car used in the rental.

8. Display the customer number and the average rental income from that customer from all bookings where the average rental income is greater than the highest rental income in all bookings reserved by a staff named REDMOND. Exclude all bookings where the rental period is less than 10 days.

Reference no: EM13687755

Questions Cloud

Find how many lines per centimeter does the grating have : The light shining on a diffraction grating has a wavelength of 495 nm (in vacuum). How many lines per centimeter does the grating have
Find the force on the particle : A particle with charge -1.00 C initially moves at v = 1.00i +7.00j m/s. If it encounters a magnetic field B = 60.0 k T, find the force on the particle
Give three advantages of sampling over a census : Give three advantages of sampling over a census
What is the rate with which the current is changing : A 2.0-cm-diameter solenoid is wrapped with 1000 turns per meter. 0.60cm from the axis, What is the rate dI/dt with which the current through the solenoid is changing
Produce a set of relations : An ER diagram for the system. Show all entities, relationships, cardinalities and optionalities. Also, include all intersection entities. You must use the Finkelstein methodology as per the study book and tutorials.
How fast is it moving vertically when it hits the ground : If a car , moving at 15 m/s , drives over a 30m cliff. How fast is it moving vertically when it hits the ground
What is the terminal velocity of the bar : Two resistance less rails rest 22cm apart on a 5.0?ramp. They are joined at the bottom by a 0.75 ohm resistor. What is the terminal (steady) velocity of the bar as it slides frictionless down the rails
How fast will the car be moving at the end : Consider a car weighing 1500 kg containing three people weighing a total of 240kg. How fast will the car be moving at the end of 50 m
What is the angle between the transmission axes : Unpolarized light is passes through two polarizers in succession. The output intensity is 12.5% of the input intensity. What is the angle between the transmission axes of the 2 polarizer's

Reviews

Write a Review

 

Database Management System Questions & Answers

  Advantages and disadvantages of joins and nested queries

One side effect of normalization is that you often need more than one table to get meaningful results. For example, you may have a table that includes a list of parts and the ID number for the vendor of each part. T

  Vehiclerentaloz data warehousevehiclerentaloz is a large

vehiclerentaloz data warehousevehiclerentaloz is a large chain of vehicle rental company over 500 stores distributed

  What are the responsibilities of a dba

What are the responsibilities of a DBA? If we assume that the DBA is never interested in running his or her own queries, does the DBA still need to understand query optimization? Why?

  Pros and cons in agile and waterfall project management

Evaluate the pros and cons in agile and Waterfall project management in terms of planning and the execution of projects and collaboration among project team members.

  Create class diagram

Draw a UML class diagram that models the aspects of job agencies that are described below.

  Find average number of books borrowed per member

Find the average number of books borrowed per member. Take into account that if a member does not borrow any books, then that member does not appear in the borrowed relation at all.

  What are some reasons for studying file systems

What are the advantages of having the DBMS between the end user's applications and the database?

  Make a report that identifies the most expensive bicycles

Prepare a report that identifies the five most expensive bicycles. The report should list the bicycles in descending order from most expensive to lsit expensive, the quantity on hand for each, and the mark up percentage for each.

  Discuss optimization techniques specific to data warehousing

Demonstrate the basic mechanisms for accessing relational databases from various types of application development environments.

  Is it possible for r to be in bcnf if so under what conditon

Consider the relation schema R(A,B,C), which has the FD B → C. If A is a can-didate key for R, is it possible for R to be in BCNF? If so, under what conditions? If not, explain why not.

  Create a class diagram of the intial code

The base application consists of three classes - Doctor, Patient and Hospital. A patient can be a private or public patient and is handled differently in either case. Patients are assigned to Doctors according to availability and preference.

  Evaluate product costs for external financial reports

The total amount of overhead cost allocated to Product X would be closest to - Andris Corporation uses activity-based costing to determine product costs for external financial reports

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd