Topic 10 : Supporting Transactions

50 %
50 %
Information about Topic 10 : Supporting Transactions
Education

Published on February 10, 2014

Author: PradipKharbuja

Source: slideshare.net

Topic 10 Supporting Transactions Er. Pradip Kharbuja

Draw an ERD for a Boat Rental System  The requirements are the following: 1. You should be able to record that a boat is rented to a customer for a set period. 2. Any damage to the boat is recorded against the particular rental. 3. A boat should have a name. 4. All boats are of the same type (yacht). 5. Damage is classified as being hull, interior or other.

Draw an ERD for a Boat Rental System  The requirements are the following: 1. You should be able to record that a boat is rented to a customer for a set period. 2. Any damage to the boat is recorded against the particular rental. 3. A boat should have a name. 4. All boats are of the same type (yacht). 5. Damage is classified as being hull, interior or other.

ERD for a Boat Rental System BOAT 1 0...N RENTAL 1 0...N DAMAGE 0...N 1 CUSTOMER

Data Dictionary  Produce a data dictionary specifying the base relations (tables), attributes and domain. Domain DamageType varchar length 30 Base Relation Boats( BoatID number NOT NULL, BoatName varchar 30 NOT NULL Primary Key (BoatID) );

Data Dictionary Base Relation Customers( CustomerID number NOT NULL, CustomerName varchar 30 NOT NULL, CustomerAddress varchar 60 NOT NULL, Primary Key (Customer ID) );

Data Dictionary Base Relation Rentals( BoatID number NOT NULL, CustomerID number NOT NULL, RentalStartDate date NOT NULL, RentalEndDate date NOT NULL, Primary Key (BoatID, CustomerID, RentalStartDate), Foreign Key (BoatID) REFERENCES Boat (BoatID), Foreign Key (CustomerID) REFERENCES Customer(CustomerID) );

Data Dictionary Base Relation Damage( BoatID number NOT NULL, CustomerID number NOT NULL, RentalStartDate date NOT NULL, DamageType DamageType );

What is a Business Rule?  A rule of doing something that applies to a particular business is a Business Rule.  Business rule is enforced with a check constraint.

Transactions  A transaction is an operation carried out on the database.  Transactions can generally be identified as retrievals, inserts, updates and deletes. This is remembered by the acronym CRUD (Create, Retrieve, Update and Delete).  Transactions can be made up of one or more operations.

Identify Transactions What do they do? What tables do they affect? How often do they run? What attributes do they affect? How many rows do they affect?

Transactions of Appointment System Transaction 1 – Add a new patient Transaction 2 – Delete a patient Transaction 3 – Record a appointment Transaction 4 – Show a detail list of patient and the appointments they have had with the doctors Transaction 5 – Show a list of patients Transaction 6 – Update a patient record to change their address  The tables required for this system are Patient, Appointment and Doctor.

CRUD Matrix of Appointment System (Blank) Transaction T1 T2 T3 T4 T5 T6 Relation Patient Appointment Doctor

CRUD Matrix of Appointment System Transaction T1 T2 T3 T4 T5 T6 Relation Patient Appointment Doctor C R R C D R R U

Transactions in the Boat Hire System a. Enter the details of all the boats. Update any details for boats. Delete boats. b. Enter the details for customers. Update any details for customers. c. Enter the details for hiring of boats. d. Enter the details for any damage to boats. e. List the details of all the boats. f. List the details of all the customers; their hire and for which boats. g. List the details for damage, to which boats, during which hire periods and for which customers. h. Provide a summary of the hires for a particular period.

Blank CRUD Matrix Transaction A B C D E F G H Relation Boat Customer Hire Damage

Completed CRUD Matrix Transaction A B C D E F G H Relation Boat Customer Hire Damage CUD CU C C R R R R R R R R R

Literary agent  Fill in the CRUD matrix below to show the following transactions. Transaction 1. Add a new Author. Transaction 2. Create a new agent and set up an appointment for her. Transaction 3. Delete an author and all the appointments they have had. Transaction 4. Show a list of Agents details and the Appointments they have had and with which Authors. Transaction 5. Update an Agent’s address Transaction 6. Delete an Appointment.

Performance  The term ‘Performance’ is generally used by database professionals to refer to the way in which a query behaves when run against a database.  Increasingly, databases contain large amounts of data...  The rate at which a query can return an answer can be slowed when it has to sort though large numbers of records.  Performance becomes an issue...

Indexes  An index is a structure in a database that helps queries run more quickly.  An index is a data structure that stores the values for a specific column in a table that makes easier to find a record.  Improves performance  Index can also be unique which will prevent a duplicate value from being added to that column.

Roles in a System  Not every user is the same.  Users will need to access different parts of the system and access it in different ways.

Boat Hire System - Roles  Manager – should be able to access all parts of the system, because their role means that they might have to add and delete any data and be able to see anything.  Admin Assistant – just carries out routine tasks, such as adding any new customers and recording damage to boats. Table/User Boat Customer Rental Damage Manager CRUD CRUD CRUD CRUD Admin Assistant R CRU CRU CRU

SQL Facilities to Manage Roles  Grant – gives a particular role or user in the database system access to an object (such as a table).  Revoke – removes access to an object (such as a table) from a particular role or user in the database system.

Grant  GRANT CREATE ON Boat TO Admin; This command will give the role of Admin the right to create data on the table Boat.  GRANT ALL ON Boat TO Manager; This command will give the role of Manager the right to carry out any operation on the table Boat.

Revoke  REVOKE ALL ON Boat FROM Admin; – this command will take away any access rights from the role of Admin on the table Boat.  REVOKE DELETE ON Boat FROM Manager; – this command will take away the right to delete data from the Boat table by the Manager.

De-Normalisation  Normalising our data model means we will have the minimum amount of redundancy.  If we are running a query that joins tables, this will be slower than running a query against a single table or view. This can have an effect on performance.  Denormalisation can be done by including an attribute in a table that should not be there according to the rules of normalisation.

Improving Performance with the Use of Views Query Table 1 Table 2 Table 3 View of selected rows or columns of these tables

View  A view is a virtual table.  The use of view as a way to improve performance.  Views can be used to combine tables, so that instead of joining tables in a query, the query will just access the view and thus be quicker.

ANY QUESTIONS?

References  http://stackoverflow.com/questions/7605707/clustered-vs-nonclustered

Add a comment

Related presentations

Related pages

Topic 10 : Supporting Transactions - Education

Topic 10 : Supporting Transactions; Download. of 27
Read more

Supporting IBM CICS LU 6.2 Transactions

Supporting IBM CICS LU 6.2 Transactions. Letzte Aktualisierung: April 2008. Betrifft: Windows Server 2008
Read more

Supporting XA Transactions - technet.microsoft.com

It includes an overview of how distributed transactions work, ... Windows 10; Windows Server-Zertifizierung ... Supporting XA Transactions.
Read more

Patent US20040128244 - Charging device, charging method ...

A fee-charge processing device which can communicate with a transaction supporting device used to support completion of transactions that involve goods ...
Read more

Patent US9146944 - Systems and methods for supporting ...

Systems and methods are provided for supporting transaction recovery based on a strict ordering of two-phase commit calls. At least one resource manager in ...
Read more

Supporting Promotable Transactions and Phase 0 – Florin ...

If you are providing support for promotable transactions for your resource manager using PSPE then you need to remember to “support” Phase 0.
Read more

Topic 10: Stimulating Simulations - Documents

Topic 10: Stimulating Simulations. by bgalloway. on Jul 15, 2015. Report Category: Documents. Download: 0 Comment: 0. 45. views. Comments. Description.
Read more

Topic 10 - School - Documents - docslide.us

Topic 10 - Thermal Physics Topic 10 - Thermal Physics PV = nRT Ideal gas: A hypothetical gas -obeys the equation of state of an ideal gas exactly -has no ...
Read more

Topic 10 - Documents

Link. Topic 10. by hanie-sayang
Read more