a)
Transaction:
A transaction is a logical unit of work.
- A sequence of process that must be completely completed or aborted.
- Intermediate states are not acceptable in the process of transaction.
- To make a transaction use a keyword “BEGIN TRANSACTION;”, insert SQL queries and commit the transaction.
Syntax for “TRANSACTION”:
BEGIN {TRANSACTION|TRAN};
SQL queries;
COMMIT;
“insert” query:
The INSERT command in SQL is employed to add records to an existing table. INSERT will increase the number of rows in a table.
Syntax for INSERT command:
INSERT INTO table_name VALUES (value1, value2…);
Example:
INSERT INTO employee VALUES(101, ‘Smith’,10);
“update” query:
The UPDATE command is employed to update information present in a table.
It is always used with SET keyword either in bulk or individually. UPDATE will not increase the number of rows in a table.
Syntax for UPDATE command:
UPDATE table_name SET column_name1 = value WHERE some_condition;
Example:
UPDATE employee SET name= ‘Presil’ age= 24 WHERE id=115;
b)
Explanation of Solution
Program plan:
The following SQL query is to make a customer payment for a customer “10010” of “$100” in cash using the payment ID “3428”.
- Begin the transaction.
- Insert the appropriate values into “payments” table using “insert” query.
- Update the customer details to reduce the customer balance by “100” for the customer “10010” using “update” query...
Trending nowThis is a popular solution!
Chapter 10 Solutions
Database Systems: Design, Implementation, & Management
- Write the following queries in SQL on the relational database schema for COMPANY database given in page 4. Find the total number of dependents of each employee who has dependents.arrow_forwardSQL Helparrow_forwardConsider the following table in a relational database. www.w Last Name Smith Rank Room Shift Manager Morning Afternoon 234 Jones Smith Custodian 33 Custodian 33 Evening Morning Doe Clerical 222 According to the data shown in the table, which of the following could be candidate keys of the table? a) {Last Name} b) {Room} c) {Shift} d) {Rank, Room} e) {Room, Shift}arrow_forward
- A company that provides a movie-streaming subscription service uses a relational database to store information on movies to answer customer questions. Each entry in the database contains the following items: Movie ID (the primary key), movie title, year made, movie type, MPAA rating, starring actor #1, starring actor #2, starring actor #3, and director. Movie types are action, comedy, family, drama, horror, science fiction, and western. MPAA ratings are G, PG, PG-13, R, NC-17, and NR (not rated). Using a graphics program, develop an entity-relationship diagram for a database application for this database.arrow_forwardThe relational schema shown below is part of a hospital database. The primary keys are highlighted in bold. Patient (patientNo, patName, patAddr, DOB)Ward (wardNo, wardName, wardType, noOfBeds)Contains (patientNo, wardNo, admissionDate)Drug (drugNo, drugName, costPerUnit)Prescribed (patientNo, drugNo, unitsPerDay, startDate, finishDate) Formulate the following SQL statements: a) Find the names of all the patients being prescribed ‘Morphine’.b) What is the total cost of Morphine supplied to a patient called ‘John Smith’?c) For each ward that admitted more than 10 patients today, list the ward number, ward type and number of beds in each ward.arrow_forwardTHIS IS A PROBLEM IN MYSQL. PLEASE ANSWER CORRECTLY. OUR MY SQL IS RUN USING COMMAND LINEarrow_forward
- Database Schema The schema for the Ch07_FACT database is shown below and should be used to answer the next several problems. Click this image to view it in its own tab. FIGURE P7.56 THE CH07_FACT ERD CHECKOUT PATRON PK Check Num PK Pat ID FK1 Book_Num FK2 Pat_ID Check_Out_Date Check_Due_Date Check_In_Date >0-----H- Pat_FName Pat LName Pat_Type BOOK AUTHOR PK Book_Num PK Au ID Book_Title Book_Year Book_Cost Book_Subject FK1 Pat_ID Au_FName Au_LName Au_BirthYear WRITES PK,FK1 Book Num PK,FK2 Au ID The CIS Department at Tiny College maintains the Free Access to Current Technology (FACT) library of e-books. FACT is a collection of current technology e-books for use by faculty and students. Agreements with the publishers allow patrons to electronically check out a book,arrow_forwardWrite the following queries in SQL on the relational database schema for COMPANY database given in page 4. Find the last name of the supervisors who do not have dependents.arrow_forwardWrite the following queries in SQL on the relational database schema for COMPANY database given in page 4. Find the total working hours of each manager.arrow_forward
- QUESTION 1 SQL: Consider the following database schema for flight reservation database. A flight can be booked by many passengers, and a passenger can book different flights. For each booking, the date of the flight and price are recorded. The database schema is given below Flight (FID:int, Airline:string(unique), DepartingAirport:string, ArrivalAirport:string, Rating: int (1,2,3,4,5) ) Booking (FlightID: int, PassengerCPR: int, Date: date, Time: time, Price: double) Passenger (CPR:int, FirstName: String, LastName: String, Address:String) Write SQL Code to: Create the Flight Table.List the Departing Airport and Arrival Airport for all flights with rating 4 or 5.arrow_forwardAssignment III (5%): Design of the conceptual model (ERD) from the given relational database. (Note: students has to give appropriate labels on relationships and type of relationships between the entities) Use the same Assignment- I schema to perform the PL/SQL programs Q1. The management of the organization has decided to increase the salaries of all employees to 15% for this, write a PL/SQL block to accept the employee number and to update the salary of that employee. Display the output based on the existing records of the employees. (2 marks) Q2. having the employee age above 40 years. (2 marks) Create a PL/SQL a cursor program that will increase the employee salary to 25% who are Q3. where place belongs to Ibri, dunk or Yanul (hint: Project (projectID. projectName, place, start Year, end Year, #DepartmentID) (2 marks) Write a PL/SQL procedure to change the name of the project to "AL Dahariyah Projects"arrow_forwardWrite the following queries in SQL on the relational database schema for COMPANY database given in page 4. Find the name of employees who have salary less than the average salary of employees who are in department 4.arrow_forward
- Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781305627482Author:Carlos Coronel, Steven MorrisPublisher:Cengage LearningDatabase Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781285196145Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos CoronelPublisher:Cengage LearningA Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology Ptr