Consider the Insurance database given below, where the primary keys are underlined. Assume each relation is filled with 10 tuples with all attributes having suitable domain-specific values and no null entries. Construct the following SQL queries for this relational database. person (driverid, name, address) car (license, model, year) accident (reportno, Adate, location) owns (driverid, license) participated (reportno, license, driverid, damage_amt) i. Find the number of accidents in which the cars belonging to “Karuna" were involved. ii. Update the damage amount for the car with the license number "KA15XL" in the accident with report number 52010 to 53000. Find out the location from which five or more accident cases are reported between iii. the time duration of 2016 to 2020.

Consider the Insurance database given below, where the primary keys are underlined. Assume each relation is filled with 10 tuples with all attributes having suitable domain-specific values and no null entries. Construct the following SQL queries for this relational database. person (driverid, name, address) car (license, model, year) accident (reportno, Adate, location) owns (driverid, license) participated (reportno, license, driverid, damage_amt) i. Find the number of accidents in which the cars belonging to “Karuna" were involved. ii. Update the damage amount for the car with the license number "KA15XL" in the accident with report number 52010 to 53000. Find out the location from which five or more accident cases are reported between iii. the time duration of 2016 to 2020.

Database Systems: Design, Implementation, & Management

11th Edition

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:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Chapter10: Transaction Management And Concurrency Control

Section: Chapter Questions

Problem 1P

See similar textbooks

Similar questions

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 Database schema on the relations Courses (Number, Faculty, Course Title, Tutor) Student (Number, Surname, First Name, Faculty) Tutors (Number, Surname, First Name) Exam (Student, Course, Grade, Date) Study Plan (Student, Course, Year) Formulate in relational algebra, in domain calculus, in tuple calculus and in Data log, the queries that produce: The student who have gained an ‘A’ in at least one exam, showing for each of them, the first name, sur name and the date of the first of such occasions; For every course in the engineering faculty, the student who passes the exam during last session; The students who pass all the exams required by their respective study plan For every course in the literature faculty, the student who pass the exam with the highest grade; The student whose study plans require them to attend lectures only in their own faculties; First name and sur name of the students who have taken an exam with a tutor having the same surname as the…
QUESTION 15 Consider the database schema below for hotels in countries. A country have many hotels, and the same hotel chain (name) are available in different countries. Country (ID: integer, Name: String (unique)) Hotel (CountryID: Integer, HotelName: String, ManagerID: Integer) A hotel can have unknown manager? No, violates key constraint. Yes. No, violates domain constraints. No, violates entity Integrity constraint No, violates referential integrity constraint.
Consider the following relational schema for a library database: Book (Title, Author, Catalog_no, Publisher, Year, Price, bookCoverType, contractDate) Collection (Title, Author, Catalog_no) Assume {Author, Title} is the key for both relations Additional functional dependencies are Title,Author --> Catalog_no Catalog_no --> Publisher, Year, bookCoverType Publisher, bookCoverType --> Price Author --> contractDate a) Explain what normal form the relation is in. b) Apply normalization until the 3rd State reasons behind each normalization
So SQL does not suppor functional depency constraints. However, if the database system supports constraints on materialized views and materialized views are maintained immediately, is it possible to enforce the functional dependency constraints in SQL? If it is possible, for a relation r(A,B,C), how can the constraints on materialized views be used to enforce a functional dependency, like B->A?
Let us consider the following relational database. Students(stuID, lastName, firstName, major, gpa) Faculty(facID, name, deptName, rank) Classes(classNhmber, facID, schedule, room) Enrolls(stuID, classNumber, grade) The primary keys are underlined. The referential integrity constraints are as follows: The column facID of relation Classes that references table Faculty, The column of stuID of relation Enrolls that references table Students, and The column classNumber of relation Enrolls that references table Classes. The following SQL statement is intended to define the table Students. However, this statement does not work correctly. CREAT TABLE Students( lastName VARCHAR(20), firstName VARCHAR(20), major VARCHAR(40) gpa DECIMAL(3,2), PRIMARY KEY(stuID)); However, this statement does not work correctly. Briefly explain why and list the corrected SQL statement.
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.
Let us consider the following relational database. Students(stuID, lastName, firstName, major, gpa) Faculty(facID, name, deptName, rank) Classes(classNumber, facID, schedule, room) Enrolls(stuID, classNumber, grade) The column facID of relation Classes that references the table Faculty, The column StuID of relation Enrolls that references table Students, and The column classNunber of relation Enrolls that references table Classes. The following SQL statement is intended to add a record to the table Students. Assume that the entered value for each column is appropriate. In other words, there is no domain constraint violation. INSERT INTO Students(stuID, lastName, firstName, major) VALUES('950555111', 'Anna' 'Smith', 'BSIT'); However, this statement does not work correctly. Briefly explain why and list the correct SQL statement.
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
QUESTION 4 Consider the database schema below: Company (CompnayID: integer, CompnayName: String (unique), address) Department (DepartmentID: Integer, DepartmentName: String, CompanyID: Integer) Employee (ID:integer; name:String, DepartmentID:intger (not null)) Is it valid in the department relation, that two different departments to have the same value of CompanyID? No, violates domain constraints. No, violates key constraint. Yes. No, violates referential integrity constraint. No, violates entity Integrity constraint.
QUESTION 17 Consider the database schema below for cities in countries. A country have many cities, and the same city name can be found in different countries. Country (ID: integer, Name: String (unique)) City (serialNo: integer (unique, not null), CountryID: Integer, CityName: String, Population: integer) The same country name can be repeated with two different IDs in Country relation? No, violates key constraint. No, violates entity Integrity constraint No, violates domain constraints. No, violates referential integrity constraint. Yes.
QUESTION 14 Consider the database schema below for cities in countries. A country have many cities, and the same city name can be found in different countries. Country (ID: integer, Name: String (unique), SN: serialNumber (int)(unique)) City (serialNo: integer (unique, not null), CountryID: Integer, CityName: String, Population: integer) The same country (ID , name) can be added twice in Country relation with different SN? No, violates entity Integrity constraint No, violates referential integrity constraint. No, violates key constraint. Yes. No, violates domain constraints.

SEE MORE QUESTIONS

Recommended textbooks for you

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