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
- Draw the ERD with appropriate mapping cardinalities for mail order database in which employees, each identified by unique employee number, first name last name and ZIP code. Employee are categorized as manager, clerk ad delivery staff. Each customer of the company is identified by a unique employee number, first and last name and ZIP code. Each part sold by the company is identified by unique part number, part name, price and quantity stock. Each order placed by a customer is taken by a clerk and is given a unique order number. Each order contains specified quantities of one or more parts. Each order has a date of receipt as well as an expected ship date. The actual ship date is also recorded. The delivery boy places the order of specified customers. There is a provision of replacing one or many fault parts to the customer but before that it must be verified by the manager.arrow_forwardFigure Q3.13 The Ch03_VENDINGCO database tables Table Name: BOOTH BOOTH _PRODUCT BOOTH _PRICE Chips 1.5 Cola 1.25 Energy Drink 2 Table Name: MACHINE MACHINE _PRODUCT MACHINE _PRICE Chips 1.25 Chocolate Bar 1 Energy Drink 2 Create the table that results from applying a UNION relational operator to thetables shown in Figure Q3.13.arrow_forwardQuestion#2: Given the following Theatre Database form: No.of seats:500 Owner: J.Martin Year Founded: 1890 Owner’s Phone: 2347756 Play Name Play Type No. of performances Play Author name Play Author nationality MacBeth Tragedy 35 W.shakesper English Ulysses Drama 12 J.Joyce Irish Represent the above as an unnormalized relational schema then apply the rules of normalization to normalize it to 3NF.arrow_forward
- Suppose that you are a manufacturer of product ABC, which is composed of parts A, B, and C. Each time a new product ABC is created, it must be added to the product inventory, using the PROD_QOH in a table named PRODUCT. Also, each time the product is created, the parts inventory, using PART_QOH in a table named PART, must be reduced by one each of parts A, B, and C. The sample database contents are shown in Table P10.1. Given the preceding information, answer Questions a through e. How many database requests can you identify for an inventory update for both PRODUCT and PART? Using SQL, write each database request you identified in Step a. Write the complete transaction(s). Write the transaction log, using Table 10.1 as your template. Using the transaction log you created in Step d, trace its use in database recovery.arrow_forwardThe dependency diagram in Figure Q6.8 indicates that a patient can receive many prescriptions for one or more medicines over time. Based on the dependency diagram, create a database whose tables are in at least 2NF, showing the dependency diagram for each table.arrow_forwardRelational Algebra Specify the following queries on the COMPANY relational database schema shown in Figure 3.5, using the relational operators discussed in this chapter. Also, show the result of each query. - List the managers who are controlling more than 2 projects. - List the names of employees all of whose dependents were born before 1990. - Retrieve the average salary of employees who work totally no less than 30 hours on projects.arrow_forward
- Consider the unnormalized data presented in the STUDENT table below:a) Identify the data redundancies and give reasons why they create problems in schemas. STUNUM STUNAME TUTNUM TUTNAME COURSENUM COURSEDESC GRADE 00118536 John Brown RC985 Rose Carter CS1001 CS1002 CS1003 Comp Research English Database design B A A 00118537 Marie Smith TB526 Tom Black CS9001 CS9002 CS1002 Calculus Javascript English A B C 00118538 Mary Cole PF587 Peter Finch CS9002 CS1003 CS1001 Javascript Database design Comp Research C A A 00118539 Ian Dunkley SH231 Sarah Hardy CS9005 CS9006 CS1002 Information Systems Web Design English A B B 00118540 Bob Marley JH850 Jacqui Hart CS9002 CS1002 CS1003 JavaScript English Database Deign A B C b) Which dependencies represent 1NF violations? Identify and eliminate 1NF violations. Present your table(s) after transformation into 1NF.c) Identify and remove any 2NF violations. Show the resulting schemas and tablesd) Identify and…arrow_forwardConsider the following relational database schema that contains information about employees and projects to which they are assigned. Emp (eid, lastname, gender, DOB) Assign (eid, pid, hours) Proj (pid, pname, budget, manager_eid) Answer the following questions. (a) Find two superkeys in the Proj table — both of these two superkeys should NOT be same as the primary key. You should use appropriate set notation if the superkey contains multiple attributes. (b) Suppose the Emp and Proj tables have been created appropriately. Define (i.e., create) the Assign table in SQL (Just create only one table.) You should follow all the requirements discussed in the class. (c) Suppose in the Proj table a project has changed pid from P06 to P07. From the perspective of integrity constraints, how should this modification be handled?arrow_forwardWhen mapping entities to one another for a relational database, there are several different approaches to take. Describe your thought process when trying to determine the mapping relationship (I.e. 1:M, M:N, 1:1) of two entities.arrow_forward
- 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…arrow_forwardConsider 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.arrow_forwardA 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 professorarrow_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 Learning