person (driver_id, name, address) car (license, model, year) accident (report_number, date, location) owns (driver_id, license) participated (report number, license, driver id, damage amount) Figure 3.18 Insurance database for Exercises 3.4 and 3.14. Consider the insurance database of Figure 3.18, where the primary keys are underlined. Construct the following SQL queries for this relational database. a. Find the total number of people who owned cars that were involved in accidents in 2009. b. Add a new accident to the database; assume any values for required attributes. c. Deletethe Mazda belonging to "John Smith".

person (driver_id, name, address) car (license, model, year) accident (report_number, date, location) owns (driver_id, license) participated (report number, license, driver id, damage amount) Figure 3.18 Insurance database for Exercises 3.4 and 3.14. Consider the insurance database of Figure 3.18, where the primary keys are underlined. Construct the following SQL queries for this relational database. a. Find the total number of people who owned cars that were involved in accidents in 2009. b. Add a new accident to the database; assume any values for required attributes. c. Deletethe Mazda belonging to "John Smith".

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Similar questions

Refer to the Company Database. List the ssn, last name, birth date, and department name of all employees who were born in the 1980s and are younger than their supervisor. i. Show the SQL for the statements ii. Express the SQL statements as a sequence of relational algebra statements (to be executed sequentially). You must use all the operators: selection projection, join. iii. Illustrate a relational algebra query tree for the execution of the statements. You must use all the operators: selection, projection, join.
Q3) consider you have the Library database with the following relations, where the primary keys are underlined: Book (bookID, name, auther, No.-of-pages, section-name, category) Section(section-name, section-admin, no-of-books, library-code) Library(library-name, address, no-of-sections, manager-name, library-code, country)
Problem: design a er diagram for a database to keep track of building inspections required by a department of Damaged Buildings (DB). DB building inspections are requested by builders every month. Each building inspection is either passed or not. Inspections have a type code (3 characters, e.g. PLU, FRM, ELE, etc.) and possibly sequencing requirements. Some inspections cannot be performed before other inspections, e.g. final plumbing inspection cannot be performed until the framing inspection is passed. Each inspection has a numeric score, with 75 or more out of 100 being sufficient for a pass status. Each inspection data contains the date of inspection, inspector identification, inspection score, and textual information about the inspection. The textual information can be updated later, but the score can never be changed. FODB maintains a pool of inspectors. Each inspector has a unique 5 digit employee ID, name, and date they were hired. They can conduct any type of inspection but…
Here is the database design for the personnel data: employee(id, name, address) employeeMap(id, confidentialId) empSalary(confidentialId, salary) The role 'Clerk' would be granted SELECT, UPDATE, DELETE to employee table and granted SELECT to empSalary table. While the clerk can see salary information for any statistical analysis (e.g. average salary computation), he has no idea which employee the salary is for. The role 'Administrator' is granted rights on all three tables; using the confidentialId in the employeeMap table, she has access to salary data for individual employees too. A SQL query that the administrator can use to access all employee fields is: select e.id, e.name, e.address, es.salary from employee e inner join employeeMap m on e.id = m.id inner join empSalary s on m.confidentialId = s.confidentialId; Can you draw a database schema?
CSC 472 Introduction to Database Systems Project Consider the contents of output tuples generated by a join operator. The advantage of "late materialization" approach is that future operators in the query plan never need to go back to the base tables to get more data. Group of answer choices True False
We have a relational database that contains the table ‘Staff’. The table ‘Staff’ consist of columns ‘Name’, ‘Department’ and ‘Date of Joining’. What query would you use to extract the Names and Department of all staff in the table? a. SELECT * FROM Staff b. a) SELECT Name, Department FROM Staff c. SELECT Name, Department IN Staff d. SELECT * IN Staff
subject : database system Member (MemberID, Name, ContactNo, Status, Sex)Borrow (BorrowNo, MemberID, CatalogNo, CheckOut, Return, Fine)Book (CatalogNo, Title, Author, Publisher, Year, Category) Write the appropriate relational algebra to produce the following queries. List member ID for members that borrowed all the ‘database’ books (category).
I need the answer as soon as possible
Consider a database with the following three tables: RACES[RaceName, Year, Organizer, Country, Federation, Type, Distance] RaceName and Year form the primary key. Type is from { Run, Triathlon, Bike, MountainBike }. Distance is given in meters. RESULTS[RaceName, Year, Place, AthleteName] (RaceName, Year, Place) form the primary key. (RaceName, Year) form a foreign key referencing RACES. RECORDS[RaceName, Year, Record, Time] (RaceName, Year, Record) form the primary key. The foreign key is the same as for RESULTS. The values of Record could be, e.g., World Record, National Record, Fastest Time. Time is in seconds. Write queries in SQL that produce the answers to the following questions: (a) Find the names of all athletes who exclusively participate in runs that are longer than 40 kilometers. (b) Find organizers who organized a mountain-bike and a bike race, where the mountain-bike race was longer than the bike race. (c) Find the names of all athletes who achieved a world record in…
Relation algebra (Database)