Design a database for a hotel reservation business (e. g. hotels.com) using the below data requirements:...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Design a database for a hotel reservation business (e. g. hotels.com) using the below data requirements: The hotels are identified by a unique hotel number and each hotel has a name and city where it is located. The hotels have rooms with room number and information related to rooms such as the type of room (single, double, etc.) and price need to be stored. Information of guests (customers) such as the guest name and address has to be maintained. Each guest is uniquely identified by a guest number. Hotel room bookings by guests will include information of the arrival and departure dates. (Guests may book the same room as part of different bookings and hence the unique identifier of a booking should include the arrival date instead of the room number.) 1) Design the conceptual schema (using ER Diagrams). 2) Design the relational database schema based on the conceptual schema. 3) Design an Oracle/MySQL database using SQL DDL based on the relational schema. (10) (15) (25) 4) Populate the database using SQL DML with at least five records in each table and with data appropriate for the below queries. 5) Write SQL queries for the following: (20) (30) a) List full details of all hotels in London. b) List the names and addresses of all guests in London, alphabetically ordered by B name. c) List all double or family rooms with a price below $ 40.00 per night, in ascending order of price. d) List the bookings for which no departure date has been specified. e) How many hotels are there? f) What is the average price of a room? g) What is the total revenue per night from all double rooms? h) How many different guests have made bookings for August? i) List all guests currently staying at the Grosvenor Hotel. j) List the number of rooms in each hotel. Design a database for a hotel reservation business (e. g. hotels.com) using the below data requirements: The hotels are identified by a unique hotel number and each hotel has a name and city where it is located. The hotels have rooms with room number and information related to rooms such as the type of room (single, double, etc.) and price need to be stored. Information of guests (customers) such as the guest name and address has to be maintained. Each guest is uniquely identified by a guest number. Hotel room bookings by guests will include information of the arrival and departure dates. (Guests may book the same room as part of different bookings and hence the unique identifier of a booking should include the arrival date instead of the room number.) 1) Design the conceptual schema (using ER Diagrams). 2) Design the relational database schema based on the conceptual schema. 3) Design an Oracle/MySQL database using SQL DDL based on the relational schema. (10) (15) (25) 4) Populate the database using SQL DML with at least five records in each table and with data appropriate for the below queries. 5) Write SQL queries for the following: (20) (30) a) List full details of all hotels in London. b) List the names and addresses of all guests in London, alphabetically ordered by B name. c) List all double or family rooms with a price below $ 40.00 per night, in ascending order of price. d) List the bookings for which no departure date has been specified. e) How many hotels are there? f) What is the average price of a room? g) What is the total revenue per night from all double rooms? h) How many different guests have made bookings for August? i) List all guests currently staying at the Grosvenor Hotel. j) List the number of rooms in each hotel.
Expert Answer:
Answer rating: 100% (QA)
The question at hand involves several steps to design implement and query a database for a hotel reservation system This is a complex task that usually requires extensive planning and handson database ... View the full answer
Related Book For
Data Modeling and Database Design
ISBN: 978-1285085258
2nd edition
Authors: Narayan S. Umanath, Richard W. Scammel
Posted Date:
Students also viewed these programming questions
-
Planning is one of the most important management functions in any business. A front office managers first step in planning should involve determine the departments goals. Planning also includes...
-
Hi there , I need help. for my work i need a relevant and well written introduction as well as conclusion to the following below. My work is complete i just need a introduction and conclusion please...
-
At Acme Corp., the firm's senior managers approach marketing implementation with the belief that marketing strategy should be developed at the top of the organization and then transmitted to lower...
-
Find the duration of a bond with settlement date May 27, 2016, and maturity date November 15, 2025. The coupon rate of the bond is 7%, and the bond pays coupons semiannually. The bond is selling at a...
-
The production department of Rordan Corporation has submitted the following forecast of units to be produced by quarter for the upcoming fiscal year: Each unit requires 0.55 direct labour-hours, and...
-
An often-ignored concept in breach of contract is the availability, if any, of the award of punitive damages. Often, cases incorporate both breach of contract and tort actions. The tort actions...
-
1. Develop a CPM/PERT network for Moore House Contractors and determine the probability that the contractors can complete a house within 45 days. Does it appear that the Moores might need to increase...
-
Consider two descriptive facts. First, the poorest countries in the world are disproportionately located close to the equator. Second, the civilizations that were richest in 1500 are among the...
-
In early 2016, Doc and Lyn McGee formed the McGee Cake Company. The company produced a full line of cakes, and its specialties included chess cake,* lemon pound cake, and double-iced, double...
-
Solve the below equations and find that are an inverse or not 1)- f(x)=5x-2 and g(x)=1/5x-2 2)- f(x)=1/7x-3 and g(x)=12+7x 3)- f(x)=2x+4 and g(x)= 1/2x-2
-
Assume the firm invests $150,000 today to get $23,000 at Year 1, $45,000 at Year 2, $55,000 at Year 3, $46,000 at Year 4, $40,000 at Year 5, and $16,500 at Year 6. Assuming the Interest (discount)...
-
The financing provided for start-up, often high-risk, private business enterprises is called: Question 3 options: Venture capital. Junk bonds. Flotation costs. Initial public offerings. Financial...
-
Choose the best paraphrase for the following passage: "Unlimited time off is a much more common perk among start-ups and other tech firms- but despite the name, unlimited leave can feel like anything...
-
Each box sells for a revenue of $50. They estimate four possible levels of demand for these fruit boxes for next month: there is a 10% chance the demand will be 20 boxes; a 30% chance the demand will...
-
Explain Shift micro-operation and explain arithmetic logic shift unit.
-
Question 3: A simple random sample of 800 individuals provided a sample proportion of p'=0.70. a. Construct a 90% confidence interval for the population proportion. Lower limit b. Construct a 95%...
-
Uniform electric field in Figure a uniform electric field is directed out of the page within a circular region of radius R = 3.00 cm. The magnitude of the electric field is given by E = (4.50 x 10-3...
-
Distinguish between a simple attribute, a single-valued attribute, a composite attribute, a multi-valued attribute, and a complex attribute. Develop an example similar to Figure 2.3 that illustrates...
-
What is a subclass and when is a subclass of use in data modeling?
-
Revise the ER diagram drawn in the previous exercise to include the following mandatory attributes: CLIENT- ID number, name, address (city, state, zip), phone number(s), birthdate; AGENT - agent...
-
Find TC, AFC, AVC, AC, and MC from the following table: 100 VC $ 0 Units FC $100 012345 40 100 60 100 70 100 85 100 130
-
Explain how each of the following will affect the average fixed cost, average variable cost, average total cost, and marginal cost curves faced by a steel manufacturer: a. New union agreement...
-
A firm has fixed costs of $100 and variable costs of the following: a. Show AFC, ATC, AVC, and MC in a table. b. Graph the AFC, ATC, AVC, and MC curves. c. Explain the relationship between the MC...
Study smarter with the SolutionInn App