Database System Concepts
7th Edition
ISBN: 9780078022159
Author: Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher: McGraw-Hill Education
expand_more
expand_more
format_list_bulleted
Related questions
Question
Consider a relational database for an online hotel booking company. The database consisting of
the following tables (where the primary keys are underlined):
Hotel (hId, hName, hAddress, hCity)
Guest(gId, gName, gAddress, gCity)
Room(hid, roomNo, type, price)
Booking(gId, hId, roomNo, fromDate, year, noOfDays)
Where hId and gId are identifiers for the hotels and the guests,
and the Booking relation indicated that a guest booked a hotel room for a specified number of days
(noOfDays) starting from fromDate of a given year. For instance, a tuple < g12345, h5555, 220,
Jan05, 2016, 15> in Booking indicates that the guest g12345 booked room 220 of the h5555 hotel
for 8 days starting on Jan 5, 2016. The attribute "gAddress" records the home address of the
guests.
Write the following queries in relational algebra
(1) Return the
name of guests who have booked a hotel room in NYC in January 2016 (i.e.,
fromDate between 01/01 and 01/31 and year=2016).
(2) Return the
name of guests who have booked a hotel room of type “suite” in NYC for longer than
30 days.
the following tables (where the primary keys are underlined):
Hotel (hId, hName, hAddress, hCity)
Guest(gId, gName, gAddress, gCity)
Room(hid, roomNo, type, price)
Booking(gId, hId, roomNo, fromDate, year, noOfDays)
Where hId and gId are identifiers for the hotels and the guests,
and the Booking relation indicated that a guest booked a hotel room for a specified number of days
(noOfDays) starting from fromDate of a given year. For instance, a tuple < g12345, h5555, 220,
Jan05, 2016, 15> in Booking indicates that the guest g12345 booked room 220 of the h5555 hotel
for 8 days starting on Jan 5, 2016. The attribute "gAddress" records the home address of the
guests.
Write the following queries in relational algebra
(1) Return the
name of guests who have booked a hotel room in NYC in January 2016 (i.e.,
fromDate between 01/01 and 01/31 and year=2016).
(2) Return the
name of guests who have booked a hotel room of type “suite” in NYC for longer than
30 days.
(3) Return the
name of guests who never booked a hotel room for longer than 3 days.
(4) Return the ids of the hotels located in NYC which were not booked at all in the year 2015.
(5) Return the ids of the guests who have booked at least one room of type “penthouse suite” in
every hotel located in NYC
(4) Return the ids of the hotels located in NYC which were not booked at all in the year 2015.
(5) Return the ids of the guests who have booked at least one room of type “penthouse suite” in
every hotel located in NYC
Expert Solution
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
This is a popular solution
Trending nowThis is a popular solution!
Step by stepSolved in 3 steps with 3 images
Knowledge Booster
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.Similar questions
- 1A. Consider the bank database schema as below. loan (loan_number, branch_name, amount) account (account_number, branch_name, balance) depositor (customer_name, account_number). Give a relational algebra expression for following queries: a. Find all loan numbers with a loan value greater than $25,000. b. Find the names of all depositors who have an account with a value greater than $10,000 at the "Manipal" branch. 1B. Identify and list at least 3 Tables with 5 attributes (Including primary key), which might be used to create Database for a Social-Networking system like Facebook.arrow_forwardConsider the following database scheme Employee (SSN, fname, lname, salary, deptno) Department (deptno, dname, total_salary) Where total salary is a derived attribute that represents the total salary of employees working in a specific department. Write a trigger to compute the total_salary in the event of inserting one or more new employee record(s) in the employee table.arrow_forwardConsider the following database instance. Table name: Students Primary key: sid Sid sname 7 Ricky 2 Ellen 6 MaryLou 4 Ellen Table name: Courses Primary key: cid cid cname 1 ICS 2 Finance Table name: Register Primary key: sid,cid Foreign key: sid references Students(sid) Foreign key: cid references Courses(cid) sid cid 7 2 2 2 7 1 4 1 5.1) For each of the following statements, show whether the statement is correctly executed or not (assume that the statements are executed in order, which means that if a statement is correctly executed, its effect is reflected in the following statement). If you say the statement is not executed, explain why. INSERT INTO Students VALUES (3, ‘Ellen’); INSERT INTO Students VALUES (6, ‘Ellen’); INSERT INTO Register VALUES (1, 2); INSERT INTO Courses VALUES (5, ‘Systems’); INSERT INTO Register VALUES (6, 5); INSERT INTO Register…arrow_forward
- Consider the following database instance. Table name: Students Primary key: sid Sid sname 7 Ricky 2 Ellen 6 MaryLou 4 Ellen Table name: Courses Primary key: cid cid cname 1 ICS 2 Finance Table name: Register Primary key: sid,cid Foreign key: sid references Students(sid) Foreign key: cid references Courses(cid) sid cid 7 2 2 2 7 1 4 1 5.1) For each of the following statements, show whether the statement is correctly executed or not (assume that the statements are executed in order, which means that if a statement is correctly executed, its effect is reflected in the following statement). If you say the statement is not executed, explain why. INSERT INTO Students VALUES (3, ‘Ellen’); INSERT INTO Students VALUES (6, ‘Ellen’); INSERT INTO Register VALUES (1, 2); INSERT INTO Courses VALUES (5, ‘Systems’); INSERT INTO Register VALUES (6, 5); INSERT INTO Register…arrow_forwardConsider the given schema for Library Database. Convert the chema into SQL.arrow_forwardThe database used for this question is a very simple one with the following schema: (Primary keys are bold, foreign keys are underlined) CUSTOMER (CustID, FirstName, LastName, City, Phone, Email) INVOICE (InvoiceNumber, CustID, Date) INVOICE_ITEM(InvoiceNumber, ItemNumber, Quantity) ITEM (ItemNumber, ItemName, UnitPrice) When a customer makes a purchase, an invoice is created. The invoice may be for many items. For example, in a single purchase, a customer might buy 10 Back Scratchers, 4 Hair Removers and a Dog Lead. Provide relational algebra (NOT SQL) queries to find the following information. Each question is worth 2 marks. NOTE: You can use the symbolss, P, etc., or the words’ PROJECT’, ‘RESTRICT’etc.as you prefer. You do not need to try to make efficient queries – just correct ones. Where you use a join, always show the join condition. List the first and last names of Customers who come from the City named Perth. List the first and last names of customers who had…arrow_forward
- Consider the relational schema for a library system as given below: Member (mem_no, name, dob) Books (isbn, title, authors, publisher) Borrowed (mem_no, isbn, date) Write the following queries in relational algebra. i. List the members who have not borrowed any books from the library. ii. Name all the books borrowed by a member named 'Ram'. ii. List the book that has been borrowed the most.arrow_forwardDatabase Systems: Design and Applicationarrow_forwardQUESTION 7 Consider the following database schema for University Library database. A student can borrow many books and a given book can be borrowed by any student if it is available in the library. For each borrowing, the borrowing and return dates are registered in the database. Student (SID: long, CPR:long (unique), Name:string, tel:number, major:String, gender:Character {"F' or M'}) Book (ISBN:long, Title:string (unique), Author:string) Borrowedltems(stSID:long, booklSBN:long, serialNo.int (unique), BorrowingDate:date, RetumDate:date) Write SQL statements to: 1. List ISBN, Title, Author of all books that include the word 'Database' in their titles in a descending order of ISBN. 2. List SID, Name, and Major of all female students doing major in 'CS' and their name starts with 'S'.arrow_forward
- The database used for this question is a very simple one with the following schema: (Primary keys are bold, foreign keys are underlined) CUSTOMER (CustID, FirstName, LastName, City, Phone, Email) INVOICE (InvoiceNumber, CustID, Date) INVOICE_ITEM(InvoiceNumber, ItemNumber, Quantity) ITEM (ItemNumber, ItemName, UnitPrice) When a customer makes a purchase, an invoice is created. The invoice may be for many items. For example, in a single purchase, a customer might buy 10 Back Scratchers, 4 Hair Removers and a Dog Lead. Provide relational algebra (NOT SQL) queries to find the following information. NOTE: You can use the symbols s, P, etc., or the words’ PROJECT’, ‘RESTRICT’ etc. as you prefer. You do not need to try to make efficient queries – just correct ones. Where you use a join, always show the join condition. List the dates on which Homer Griffin made purchases. List the first and last names of customers who have bought “Back Scratcher” or “Hair Remover” List the first…arrow_forward,,,,,,,arrow_forwardConsider the following schema of hotel reservations database: Hotel (Hotel No, Name, City) Room (Room No, Hotel No, Type, Price) Booking (Hotel No, Guest No, Date From, Date_To, Room No) Guest (Guest_No, Name, City) Write RA queries for the following: a) Get the details (names and price) of all hotels in California. b) Get the average price of a room grouped by city. c) Find the hotel name and city of the hotel with the highest priced room. d) Get the hotel names and room numbers of any hotel rooms that have not been booked.arrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
- Database System ConceptsComputer ScienceISBN:9780078022159Author:Abraham Silberschatz Professor, Henry F. Korth, S. SudarshanPublisher:McGraw-Hill Education
Starting Out with Python (4th Edition)Computer ScienceISBN:9780134444321Author:Tony GaddisPublisher:PEARSON
Digital Fundamentals (11th Edition)Computer ScienceISBN:9780132737968Author:Thomas L. FloydPublisher:PEARSON 
C How to Program (8th Edition)Computer ScienceISBN:9780133976892Author:Paul J. Deitel, Harvey DeitelPublisher:PEARSON
Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781337627900Author:Carlos Coronel, Steven MorrisPublisher:Cengage Learning
Programmable Logic ControllersComputer ScienceISBN:9780073373843Author:Frank D. PetruzellaPublisher:McGraw-Hill Education
Database System Concepts
Computer Science
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:McGraw-Hill Education
Starting Out with Python (4th Edition)
Computer Science
ISBN:9780134444321
Author:Tony Gaddis
Publisher:PEARSON
Digital Fundamentals (11th Edition)
Computer Science
ISBN:9780132737968
Author:Thomas L. Floyd
Publisher:PEARSON
C How to Program (8th Edition)
Computer Science
ISBN:9780133976892
Author:Paul J. Deitel, Harvey Deitel
Publisher:PEARSON
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781337627900
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Programmable Logic Controllers
Computer Science
ISBN:9780073373843
Author:Frank D. Petruzella
Publisher:McGraw-Hill Education