Hotel(hID, hName, city) primary key is hID Guest(gID, gName, gAddress) primary key is gID Room(hID, rNumber, price) primary key is (hID, rNumber). hID is a foreign key to Hotel relation Book(gID, hID, rNumber, StartDate, EndDate) primary key is all attributes! (hID, rNumber) is a foreign key to Room relation gID is a foreign key to Guest relation. Write in relational algebra the following queries: Select guests who stayed at least 1 day in New York City hotels between September 8, 2001, and September 11, 2001. Select guests who have never booked a room in the same hotels that John Doe has.

Hotel(hID, hName, city) primary key is hID Guest(gID, gName, gAddress) primary key is gID Room(hID, rNumber, price) primary key is (hID, rNumber). hID is a foreign key to Hotel relation Book(gID, hID, rNumber, StartDate, EndDate) primary key is all attributes! (hID, rNumber) is a foreign key to Room relation gID is a foreign key to Guest relation. Write in relational algebra the following queries: Select guests who stayed at least 1 day in New York City hotels between September 8, 2001, and September 11, 2001. Select guests who have never booked a room in the same hotels that John Doe has.

Oracle 12c: SQL

3rd Edition

ISBN:9781305251038

Author:Joan Casteel

Publisher:Joan Casteel

Chapter1: Overview Of Database Concepts

Section: Chapter Questions

Problem 5HOA: Access path A database table is composed of records and fields hold data. Data is stored in records....

See similar textbooks

Similar questions

Access path A database table is composed of records and fields hold data. Data is stored in records. A field is a part of a record and contains a single piece of data for the subject of the record. The access path for finding the list of books is AUTHOR: AuthorID, Lname BOOKAUTHOR: AuthorID, ISBN BOOKS: Pubdate, ISBN
Create ERD or EERD of local school library with these requirements, and write relation schema (underline primary key and connect foreign key with arrow) (use Chen Notation) 1. Book : - there may be some title with many copy/books, so ISBN isn't unique. Librarian however create ID for each book to identified them. - book can have many author, each other name should be separable to navigate/searching when needed 2. Borrow : - only those with Membership can borrow - each borrow session you need to stored 'borrow date' and 'return date' so you can calculate 'penalty fee' if it's late 3. Membership - there are Expire date for each member - there are 4 types of membership : contributor, student, university staff, normal citizen. - Personal info include : CitizenID, (if they are student/university staff you need to store student ID and staffID too), Name, Address, Faculty, Telephone Number - citizen member can only borrow 3 book at maximum, studen 5, and staff 6, and contributor 5.…
Design the database in an MS Excel spreadsheet as a single relation called Movie. It should contain an ID, Title, Year Released, Genre (e.g., sci-fi, comedy, thriller, etc.), Rating (e.g., G, PG, R, etc.), Format (e.g., VHS, DVD, MP3, etc.), a free-form Comments field, plus the main cast and crew members. Add the following entry plus at least two of your own: Title: Star Trek Year: 2009 Genre: Sci-Fi Rating: PG Format: DVD Director: J. J. Abrams Starring: Chris Pine (Capt. Kirk), Zachary Quinto (Mr. Spock) Zoe Saldana (Uhura), Karl Urban (Bones McCoy) What normal form is this design? Why? What should be done to correct the design?
Design the database as a single relation called Movie. It should contain an ID, Title, Year Released, Genre (e.g., sci-fi, comedy, thriller, etc.), Rating (e.g., G, PG, R, etc.), Format (e.g., VHS, DVD, MP3, etc.), a free-form Comments field, plus the main cast and crew members. Add the following entry plus at least two of your own: Title: Star Trek Year: 2009 Genre: Sci-Fi Rating: PG Format: DVD Director: J. J. Abrams Starring: Chris Pine (Capt. Kirk), Zachary Quinto (Mr. Spock) Zoe Saldana (Uhura), Karl Urban (Bones McCoy) Draw your corrected design as a logical ERD showing attributes and multiplicities (suggest you use IE Notation in Oracle Data Modeler). No need to include physical data types. Modify the sample data from step 1 in a new page to match this design
Design the database as a single relation called Movie. It should contain an ID, Title, Year Released, Genre (e.g., sci-fi, comedy, thriller, etc.), Rating (e.g., G, PG, R, etc.), Format (e.g., VHS, DVD, MP3, etc.), a free-form Comments field, plus the main cast and crew members. Add the following entry plus at least two of your own: Title: Star Trek Year: 2009 Genre: Sci-Fi Rating: PG Format: DVD Director: J. J. Abrams Starring: Chris Pine (Capt. Kirk), Zachary Quinto (Mr. Spock) Zoe Saldana (Uhura), Karl Urban (Bones McCoy)
What distinguishes a primary key from a candidate key or a superkey?
1.5) An attribute can be atomic, composite, or multivaluedTrue False 1.6) Constraints should be used to force valid data entry / modification to the databaseTrue False 1.7) A primary key can contain only single row with null value since duplicate values are not allowedTrue False 1.8) Comparison with null can be meaningfully done with equals and not equals operatorTrue False 
Assume a user creates a new relation r1 with a foreign key that links to another relation r2. What degree of r2 access does the user need? Why can't something be done without prior authorization?
Write Mysql code The database has three tables for tracking horse-riding lessons: Horse with columns: ID - primary key RegisteredName Breed Height BirthDate Student with columns: ID - primary key FirstName LastName Street City State Zip Phone EmailAddress LessonSchedule with columns: HorseID - partial primary key, foreign key references Horse(ID) StudentID - foreign key references Student(ID) LessonDateTime - partial primary key Write a SELECT statement to create a lesson schedule with the lesson date/time, horse ID, and the student's first and last names. Order the results in ascending order by lesson date/time, then by horse ID. Unassigned lesson times (student ID is NULL) should not appear in the schedule. Hint: Perform a join on the Student and LessonSchedule tables, matching the student IDs.
Write Two CREATE USER Statements for the Publisher -Author - Books - Marketing: Database -- drop the database Publisher -Author - Books - MarketingDB if it already exists DROP DATABASE IF EXISTS PublisherAuthorBooksMarketingDB; -- create a database with name Publisher -Author - Books - MarketingDB CREATE DATABASE IF NOT EXISTS PublisherAuthorBooksMarketingDB; -- ensure that the database is used USE PublisherAuthorBooksMarketingDB; -- create the table for Publisher DROP TABLE IF EXISTS Publisher; CREATE TABLE IF NOT EXISTS Publisher ( pid VARCHAR(25), pubName VARCHAR(50), email VARCHAR(50), address VARCHAR(50), phone VARCHAR(15), CONSTRAINT Publisher_PK PRIMARY KEY(pid) ); -- create the table for Author DROP TABLE IF EXISTS Author; CREATE TABLE Author( socSecNum VARCHAR(15), authName VARCHAR (50), phone VARCHAR (15), address VARCHAR (50), email VARCHAR (50), CONSTRAINT Author_PK PRIMARY KEY (socSecNum) ); -- create the table for Marketer…
3.9 LAB - Select lesson schedule with inner join The database has three tables for tracking horse-riding lessons: Horse with columns: ID - primary key RegisteredName Breed Height BirthDate Student with columns: ID - primary key FirstName LastName Street City State Zip Phone EmailAddress LessonSchedule with columns: HorseID - partial primary key, foreign key references Horse(ID) StudentID - foreign key references Student(ID) LessonDateTime - partial primary key Write a SELECT statement to create a lesson schedule with the lesson date/time, horse ID, and the student's first and last names. Order the results in ascending order by lesson date/time, then by horse ID. Unassigned lesson times (student ID is NULL) should not appear in the schedule. Hint: Perform a join on the Student and LessonSchedule tables, matching the student IDs.
Suppose a user creates a new relation r1 with a foreign key referencing another relation r2. What authorization privilege does the user need on r2? Why should this not simply be allowed without any such authorization?