A University database is described by the following relational schema. Student (StudentId, Name, MatrNr, Faculty) Lecturer (LecturerID, Name, Institute, Salary) Faculty (FacultyID, PAYENo, Name, PersonType, Department, Faculty, Institute, TeachingStatus, Salary, CarLotNo, ComputerNo) Write one SQL script to populate the faculty database with the three lecturers below given the following critical information for each lecturer. 1- John Doe is new and so does not have a computer. 2- Jack Sprat does not drive a car. 3- Win Ling is a visiting professor

A University database is described by the following relational schema. Student (StudentId, Name, MatrNr, Faculty) Lecturer (LecturerID, Name, Institute, Salary) Faculty (FacultyID, PAYENo, Name, PersonType, Department, Faculty, Institute, TeachingStatus, Salary, CarLotNo, ComputerNo) Write one SQL script to populate the faculty database with the three lecturers below given the following critical information for each lecturer. 1- John Doe is new and so does not have a computer. 2- Jack Sprat does not drive a car. 3- Win Ling is a visiting professor

A Guide to SQL

9th Edition

ISBN:9781111527273

Author:Philip J. Pratt

Publisher:Philip J. Pratt

Chapter2: Database Design Fundamentals

Section: Chapter Questions

Problem 11RQ

See similar textbooks

Similar questions

Consider the relational database below, where the primary keys are underlined.employee (person-name, street, city)works (person-name, company-name, salary)company (company-name, city)manages (person-name, manager-name)Give a query for each of the following:a Give all managers in database a 10 percent raise.b Give managers in the database a 10 percent raise, unless the salary would be greaterthan $100,000. In such cases, give only 3 percent raise.c Delete all tuples in the works relation for employees of Small Bank Corporation
Consider a database that consists of the following relations.SUPpLIER(Sno. Sname)PART(Pno, Pname)PROJECT(Jno, Jname)SUPPLY(Sno, Pno, Jno)The database records information about suppliers, parts, and projectsandincludes a ternary relationship between suppliers, parts, and projects.Thisrelationship is a many-many-many relationships. Specify and execute thefollowingqueries using the relational algebraRetrieve the part numbers that are supplied to exactly two projects.b. Retrieve the names of suppliers who supply more than two parts toproject J11c. Retrieve the part numbers that are supplied by every supplier.d. Retrieve the project names that are supplied by supplier S1' only.e. Retrieve the names of suppliers who supply at least two different partseach to at least two different projects.
Consider the relational database below, where the primary keys are underlined.employee (person-name, street, city)works (person-name, company-name, salary)company (company-name, city)manages (person-name, manager-name)Give a query for each of the following:a Find the company that has the smallest payroll.b Modify the database so that Jones now lives in Newtown.c Give all employees of the First Bank Corporation a 10 percent raise.d Give all managers in database a 10 percent raise.e Give managers in the database a 10 percent raise, unless the salary would be greaterthan $100,000. In such cases, give only 3 percent raise.f Delete all tuples in the works relation for employees of Small Bank Corporation.
The following is a relational schema for a database to document sharksightings for a research project. Primary keys are underlined. ForeignKeys are identified by FK, and their name indicates the attribute that theyreference.________________________________________________________________Sharks(shark_id:Integer, name:String, species_name:String,tagged_by_mission_id:Integer (FK))Sightings(shark_id:Integer (FK), sighting_num:Integer,sight_time:Timestamp, lat:Real, lng:Real, ocean:String, mission_id:Integer(FK))Missions(mission_id:Integer, start_date:Date, end_date:Date,vessel_id:Integer (FK))ResearchVessels(vessel_id:Integer, name:String)________________________________________________________________Sharks identified by a unique id, have a name which is not necessarilyunique, and a scientific species name.All Sharks are tagged and are sighted by Research Vessels, which areresearch ships that go on research Missions throughout the year. EachMission has a start and end date. Shark…
Consider a relational database for an online hotel booking company. The database consisting ofthe 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 hotelfor 8 days starting on Jan 5, 2016. The attribute "gAddress" records the home address of theguests.Write the following queries in relational algebra (1) Return thename 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 thename of guests who have booked a…
Consider the following relations in the relational schema for a database that keeps track of business trips of Sales Representatives in a sales office: salesperson(SSN, lName, startYear, deptNo) trip(tripID, SSN, fromCity, toCity, departureDate, returnDate) expense(tripID, accountNumber, amount) Part 1 Answer the Following 3 Questions. Places Your Answers in a Word Processor identify the primary key for each table. Identify the foreign keys in each table. Given the above foreign keys, list the order in which the tables could be successfully created. Write your Answers using a Word Processor Review the Relational Schema and other necessary material in this assignment to create the metadata document for the above relational schema. Write your Metadata using a Spreadsheet or a Word Processor. Submit the Final Version as a PDF Open up a text editor such as Visual Studio Code. Write the SQL SELECT Queries to respond to the following Information Requests Find…
Consider the given bank database. Compose SQL Queries for each of below scenarios. branch(branch_name,branch_city,assets) customer(customer_name,customer_address,customer_city) loan(loan_number,branch_name,amount) borrower(customer_name,loan_number) account(account_number,branch_number,balance) depositer(customer_name,account_number) e. Find the names of all depositors who have an account with a value greater than $6,000 at the “Uptown” branch. f. Show Functional Dependencies, Primary Keys and Foreign Keys in the relations.
Consider the following shorthand representation of a relational database used by a book publisher to answer the following questions.BRANCH (BRANCH_NUM, COUNTRY, NUM_EMPLOYEES) WRITER (WRITER_NUM, LAST_NAME, FIRST_NAME, BOOK_NUM) BOOK (BOOK_NUM, TITLE, TYPE, PRICE, WRITER_NUM) INVENTORY (BOOK_NUM, BRANCH_NUM, ON_HAND)2.3 List the number of employees for each branch in South Africa. 2.4 List the book code and title of each book of type “Romance”. 2.5 For each type of book list the type and the average price. 2.6 Find the title for each book written by “Bob Builder”. 2.7 List the name of all books in South Africa written by “Hans Anderson“ 2.8 Count the number of books (AS NumOfBooks) written by each author
We have the following database schema: Computer (vendor, model, machinetype) Model (num, speed, ram, hd, price) Vendor (name, address, phone) Where vendor indicates the manufacturer of a computer, and machine type takes values such as “desktop”, “laptop” and “server”. Underline indicates the primary key. Following inclusion dependencies hold: vendor ⊆ name, and model ⊆ num. Express following queries in relational algebra: Find the phone numbers of all the vendors who make desktops with speed = 2.
Consider the following relational database that represents books, publishers, and books published by publishers. 1) Does this database schema allow for a book to have more than one publisher? Explain why or why not using an example. 2) Does this database schema allow for a book to be currently out of print (not currently being published)? Explain why or why not using an example. 3) List three super keys of the books relation. 4) List all candidate keys of the books relation. 5) List all of the candidate keys of the publishes relation. 6) List all of the superkeys of the publishes relation. 7) Are there any attributes in considered logically non-atomic? Briefly explain why or why not. Assuming tuple (4,83,200,1990) into the publishes table failed. Why did this happen? What database principle was violated? 9) Does the SQL statement ( select title, author from books;) evaluate to a relation? Briefly explain. 10) one web site. the database that could be we have the tables above, inserting…
Consider the relational database below, where the primary keys are underlined.employee (person-name, street, city)works (person-name, company-name, salary)company (company-name, city)manages (person-name, manager-name)Give a query for each of the following:a Find all employees who live in the same city as the company for which they work islocated.b Find all employees who live in the same city and on the same street as theirmanagers.c Find all employees in the database who do not work for the First Bank Corporation. d Find all employees who earn more than the average salary of all employees of theircompany.e Find the company that has the most employees.f Find the company that has the smallest payroll.g Modify the database so that Jones now lives in Newtown.h Give all employees of the First Bank Corporation a 10 percent raise.i Give all managers in database a 10 percent raise.j Give managers in the database a 10 percent raise, unless the salary would be greaterthan $100,000. In such…
The following question are based on the database whose relational schemas are emp(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) and DEPT(DEPTNO, DNAME, LOC). (a) Update employee SMITH’s job and salary to match that of employee WARD.(b) List the employees joined in Jan with salary ranging from 1500 to 4000. (c) Find out the names and salaries of employees whose salaries greater than salaries of their managers.(d) Find out the manager’s name and number of employees who is having maximum number of employees working under him.

SEE MORE QUESTIONS

Recommended textbooks for you

A Guide to SQL

Computer Science

ISBN:

9781111527273

Author:

Philip J. Pratt

Publisher:

Course Technology Ptr

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781285196145

Author:

Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Publisher:

Cengage Learning

A Guide to SQL

Computer Science

ISBN:

9781111527273

Author:

Philip J. Pratt

Publisher:

Course Technology Ptr

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781285196145

Author:

Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Publisher:

Cengage Learning

SEE MORE TEXTBOOKS