Develop a model to store questionnaires

Assignment Help Database Management System
Reference no: EM13652322

BTC AU is the second largest Bitcoin exchange company in Australia. While it focused on keeping up with its core business in a new and emerging environment BTC AU paid scant attention to managing the rest of its rapidly expanding business operations. Integration of its business processes with IT became one of the priorities of BTC AU.

The current approach to its backroom data handling management has been ad hoc and piecemeal. There was no overall map, plan, or model guided approach to the evolution of its systems. Records of past business performance have been hard to elicit from the traditional file processing systems leaving the company with an inability to gain insights to drive business planning forward.

The CIO of the company Sam Nakamoto wants to look at a database approach to integrating the various applications currently running within the organization with the hope of ultimately using the data to support decision making and developing new insights for management in
order to gain competitive advantage.

As a business consultant to BTC AU, you are asked to develop a short brief to management, in the form of an essay (around 1000 words), highlighting the following:

1. The existing and potential problems with the current data processing approach.

2. The need for a database approach to satisfy their organizational and decision making demands of the company.

3. A suggested database developmental approach

4. The primary activities associated with database design process.

For each question, marks will be awarded for the SQL and for the correct output.

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

Write SQL queries to solve the following specifications. Include the query AND THE OUTPUT. A screen dump of the output is acceptable. Show as many rows as you can.

A screen dump is usually done via the ALT + PRNT SCRN command sequence. While the output helps to understand your solution, you should not be analysing the output of the query in detail. As long as you are confident that your query corresponds to the question completely, your output may not be significant. You can have an output that says NO ROWS FOUND and it could be a perfectly valid output as long as your query fulfils the requirement.

1. Display structure of the i_model table. Display all the information stored in the i_model table belonging to car group ‘A4'. (3 marks)

2. Display the car registration, current mileage (miles_to_date column) and purchase date (date_bought column) for all cars. Order by current mileage in descending order.

3. Display all the cars registration, miles to date and status for all the cars that have not had a service but are available for hire. Order the list in ascending order of miles to date.

4. Display the booking number, date rent started, period of rental and the expected end date of each rental (i.e. date_rent_start + rental_period) and sort the output by the expected end date in descending order. Label the expected end date of each rental as:


5. Display full details for all the bookings where the distance travelled during that booking (i.e. miles_in - miles_out) is less than 1000 miles.

6. Display rental period, booking number, name of the person who took the reservation, the date that the rental period starts and where Paid is set to ‘N' for all the bookings where the car has not been returned. List the result by the rental period for the booking in descending order.

7. Display full details for any car that: (a) costs more than $100,000.00 or the name of the car model starts with the letter ‘L' and (b) belongs to group ‘A4‘ and has a registration whose second digit is 9.

8. Display full details for all car groups belonging to ‘A1', ‘A2', ‘B1' or ‘B2' AND the rate per mile is either 110 or 120. Find a way of optimising your code so that the complete SQL statement has only ONE logical operator (AND, OR, NOT).

9. Display full details for all cars that have not been serviced before and have a current mileage of greater than 900.

10. Display full details for any car that: (a) costs less than $50,000.00 or the name of the car model starts with the letter ‘D' and (b) belongs to group ‘B2‘ and is currently available. (You should have a single query that completely fulfils all of the above conditions).

11. Display the name, town and usual payment method of all customers. Order by payment method ascending within town descending. Your query must NOT use the WHERE clause.

12. Display the booking number, registration and the name of the customer who made the booking. You may need to use traditional join between i_booking and i_customer tables to display all the required columns.

Question 1

A questionnaire is a research instrument consisting of a series of questions. We need to develop a model to store questionnaires and its underlying questions. A questionnaire records questionnaire name, primary contact name, start date and expected end date. A questionnaire may have many questions. Each question has a question number, question text and a category description for the question.

Question 2

A mobile phone model consists of a number of variants (different styles, colours and features) which are sold as separate units. We need to develop a data model to record different mobile phone models and their variants. A mobile phone model records brand name, model name, model number (unique) and start year. A mobile phone model may have several variants. Each variant has a variant code, style, colour, feature, launch year and discontinued year.

Question 3

A Bitcoin wallet manages your Bitcoins. A wallet does not store Bitcoins but holds the private keys of the owner of the wallet that allow him/her to access his/her bitcoin addresses and to sign transactions to allow the owner to spend his/her funds.

Question 4

An event coordinator may be working on a number of events over time. An event can have multiple event coordinators working on it but will only have a single event manager. For an event coordinator, we store a unique coordinator identification, his/her office number and area of specialty. We also need to store the date the event coordinator commenced work on the event and the name of the event, the event manager's name and other relevant event details.

Verified Expert

Reference no: EM13652322

Previous Q& A

  What is the maximum kinetic energy of the body

A body is suspended vertically from an ideal spring of spring constant 3.4 N/m. The spring is initially in its relaxed position. The body is then released and oscillates about its equilibrium position. The motion is described by the following equa..

  Explain the ph of a solution that results from mixing

What is the pH of a solution that results from mixing together equal volumes of a 0.110 M solution of acetic acid and a 0.0550 M solution of sodium hydroxide

  Resistor is connected across the capacitor plates

A 1.03 10-3 µF capacitor has an initial charge of 0.100 C. When a resistor is connected across the capacitor plates, there is an initial current through the resistor of 1.08 A. What is the current 1.06 s later?

  Evaluate the value of ka for butanoic acid

The odor of spoiled butter is due in part to butanoic acid, which results from the chemical breakdown of butter fat. A 0.100 M solution of butanoic acid is 1.23% ionized. Calculate the value of Ka for butanoic acid.

  How many air molecules were released

A tank of compressed air of volume 1.0 m3 is pressurized to 40.0 atm at T = 273 K. A valve is opened and air is released until the pressure in the tank is 33.0 atm. How many air molecules were released?

  Find the speed of the proton

At a certain location, the horizontal component of the earth's magnetic field is 2.2 x 10-5 T, due north. A proton moves eastward with just the right speed, so the magnetic force on it balances its weight. Find the speed of the proton.

  Define net equation and mechanism of the synthesis

Net equations and mechanisms for Tollen's test and Iodoform test with 3,3-dimethyl-2-butanone.Also net equation and mechanism of the synthesis of 2,4-Dinitrophenylhydrazine(derivative) with 3,3-dimethyl-2-butanone.

  A proton moves through a magnetic field

A proton moves through a magnetic field at 29.7% of the speed of light At a location where the field has a magnitude of 0.00639 T and the proton's velocity makes an angle of 119 degree with the field. what is the magnitude of the magnetic force actin..

  Describe what is the enthalpy of the reaction

What is the enthalpy of this reaction. Assume that no heat is lost to the surroundings or to the coffee cup itself and that the specific heat of the solution is the same as that of pure water.

  Frequency of light wave is same when the light travels

The frequency of a light wave is the same when the light travels in two different media. Assuming that the refraction indices of the two media are 1.628 and 1.862, respectively, find the ratio of the wavelength of the light in the first medium to tha..


Write a Review


Similar Q& A

  What are the main activities that the business undertakes

What are the main activities that the business undertakes and where in the business are the crucial decisions made?

  Recognize level within a database system

Recognize level within a database system (user, programmer of application software, and designer of the DBMS software) at which each of the following concerns or activities occur.

  Create a struct data structure for each file contained

Write a password protected console-based application, which enables you to create user accounts and specify the name of the file or directory to be shared with other users.

  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.

  Research paper on the turing and von neumann models

Write a two page research paper on the Turing and von Neumann models.

  List the price of the item called back scratcher

List the first and last names of any customers who have bought all of the items. (This does not need to be as part of a single purchase).

  Activity diagram for information gathering component

The prototype program should be running without errors throughout the two Phases: Information Gathering and Information Presenting.

  Discussed and implemented the mvc design pattern

Find another design pattern which could be used for web based development and write a synopsis on it, pointing out whether it would be applicable for use within your project or not. Comment as applicable on design patterns that other class members..

  Write names-e-mail addresses for customers by foreign key

Illustrate all data in each of the four tables. Don't show foreign key columns. Write down the names and e-mail addresses for all customers who have had stove repair which cost more than $50.

  Interface with some other dbms

Develop a RDBMS (Relational Database Management System) in Python. The RDBMS should actually be written in Python, not just interface with some other DBMS. This system should track students: Personal Data, Schedules and Grades. The system should cont..

  Create a gis database of information about vietnam

You are asked to create a GIS database of information about Vietnam at scale of 1:100,000. Database will include the following: Which of these features are best represented by raster model? Explain why?

  If those were for me wed keep them all in two to three

if those were for me wed keep them all in two to three minutes longer he says. but thats not my call-its the

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