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
- ABC Markets sell products to customers. The relational diagram shown in Figure P10.6 represents the main entities for ABC's database. Note the following important characteristics: A customer may make many purchases, each one represented by an invoice. The CUS_BALANCE is updated with each credit purchase or payment and represents the amount the customer owes. The CUS_BALANCE is increased (+) with every credit purchase and decreased (–) with every customer payment. The date of last purchase is updated with each new purchase made by the customer. The date of last payment is updated with each new payment made by the customer. An invoice represents a product purchase by a customer. An INVOICE can have many invoice LINEs, one for each product purchased. The INV_TOTAL represents the total cost of the invoice, including taxes. The INV_TERMS can be "30," "60," or "90" (representing the number of days of credit) or "CASH," "CHECK," or "CC." The invoice status can be "OPEN," "PAID," or "CANCEL."…arrow_forwardSQL Helparrow_forwardThe following tables describe the content of a relational database: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_forwardNeed with explanationarrow_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_forward
- Explainarrow_forwardQUESTION 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 Brief: Some of the most difficult decisions that you face as a database developer are what tables to create and what columns to place in each table, as well as how to relate the tables that you create. Normalization is the process of applying a series of rules to ensure that your database achieves optimal structure. Normal forms are a progression of these rules. Each successive normal form achieves a better database design than the previous form did. Based on your understanding for the various normal form respond to the following problems and provide the optimal answer/solution. Problem#1: 1st Normal Form (1NF) Consider the Students table, with the primary key underlined, and the following data: Students: Alpha 100111 Email doe@usna.edu Name Courses GradePoints NN204, SI204, IT221 SM223, EE301 SI204 John Doe 2,3,3 Matt Smith 092244 113221 smith@usna.edu black@usna.edu 4,4 Melinda 3 Black NN204, SI204, IT221 090112 Tom Johnson Johnson@usna.ed 4,2,3 u a) Is the Students table…arrow_forward
- The following four tables form part of a library database: • Book (isbn, title, author, genre, price) • Copy (copyId, isbn) • Loan (userId, copyId, loanDate, dueDate) • User (userId, name, address, email) Where: • Book contains details of books and isbn is the primary key• Copy contains details of individual copies of books and copyId is the primary key• Loan contains details of loans and (userId, copyId, loanDate) forms the primary key • User contains details of library users and userId is the primary key 3.1 Identify the primary in the above schema. 3.2 Identify the foreign keys in the above schema. 3.3 Draw an Entity relationship model (ER-model) for the above schema, including: • all attributes• correct notation for primary and foreign keys • relationships• multiplicities 3.4 Draw sample tables for the above relations, ensuring relational integrity rules are met. 3.5 Explain the Entity Integrity constraint with reference to the Book relation.arrow_forwardYou are asked to design a database for an University. This particular university has multipledepartments. Each department in that university offers one or more courses to the students. All thestudents are part of a certain department and they take courses in that department. A department hasa name, unique number and chairman. The courses have a name, a course number such as ‘311’ , acredit point value, and the year it commenced. A course cannot be identified uniquely with thecourse number, but the course number is unique for each department. Students have given names,surname, unique Student ID, Date of Birth and the year they enrolled. When a student takes a coursethe year and the semester he or she took that particular course is recorded. When he finishes thecourse , the grade and total marks are recorded. A student can take multiple courses but they are alsoallowed to drop a semester in that university by taking no courses. Construct an ER diagram usingthe requirements given here.…arrow_forwardSubject: Database Write the SQL statement for the following tasks. 1. Create a database for a library name MY_LIBRARY 2. Based on the given details, write the statements to create the following tables (include constraints):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