Lanchester Liners Hospitality Database - Report Example

Introduction This report presents a detailed overview on design and implementation of hospitality database for Lanchester Liners for holding information about their ships, the ports visited where hospitality is offered, and any corporate team-building events organised on-board. The report also discusses various issues related to multi-user access to a database including concurrency, locking, security and access control. Hence the report is classified into two tasks: Task A defines the implementation details of physical database. It also describes how the database is populated with the given data and reports are generated for given business requirements. Task B discusses the multi-user database access. Task A 1. CREATE and INSERT statements: a) CREATE Statements Ship: Port: Event: ShipPort: PortEvent: b) INSERT Statements Ship: Port: Event: ShipPort: PortEvent: 2. SQL Queries a) List the ship names, tonnage, build date and speed of all ships in alphabetical order of ship name. b) List the ship names and tonnage of all ships that were built in 1975. c) List, in descending order of totals, the total number of ships by speed. d) List, in alphabetical order, both the names of the ports and the ships that dock at each port. e) Using a nested sub-query, list the total number of ships which dock at Barbados. f) List, in alphabetical order, the names of the events which are only organised on-board ship (In other words, where port id, port country and port name are all N/A). 3. Reports: a) b) c) d) e) f) Task B Multi-User Access Multi-user Concurrency: The primary purpose of a database is to collect, store and retrieve information. However, the demand for concurrent access to the database increases with increase in amount of information flow within the database accessed from different applications and different users across the network. Concurrency, if not handled efficiently, will not only affect the underlying data integrity but also the performance of the application. Concurrency is one of the features of the database that enables each user to realise as if they are the only user accessing that particular piece of information. In other words, no users should either overwrite the changes made by other users or read the uncommitted changes that are made by other users. This concept of isolation is implemented in databases by using locks. A lock on a result-set prevents other users from modifying the underlying data thereby isolating one transaction from another. All locks remain in effect till the user issues an explicit COMMIT or ROLLBACK. Locks can be issued in either Exclusive mode or Shared mode. Exclusive locks in which the user holds exclusive control over the piece of data and do not allow the data to be shared by other users are generally used for modify operations like INSERT, DELETE and UPDATE while Shared locks in which the data is allowed to be shared among users are generally for read-only operations like SELECT. Though each database employs its own default level of locking for each operation, it is recommended for the applications to issue an explicit lock in the FOR UPDATE clause while retrieving data using SELECT statement. However, issuing too frequent locks will slow down the performance of the application because when one user issues an explicit lock on a particular piece of data, the other users has to wait till the first user commits the changes to modify the same piece of data. This scenario is often called contention and too frequent contentions will impact the overall application to a worst extent. Maintaining high level of data concurrency among multiple users without compromising the overall performance is a real challenge in the database design. On the other hand, if the necessary isolation is not implemented in the database then various data integrity issues may arise, four of which are discussed below: Lost Updates: When more than one user try to update the same piece of information, the changes or the updates made by one user will be partially or completely lost by the changes made by the other users. This scenario of data integrity issue is called ‘Lost updates’. Dirty Reads: This happens when users try to read uncommitted changes made by other users. For example, transaction A is modifying some piece of data and transaction B issues a query for the same data and reads few rows of uncommitted changes. Later if transaction A rolls back the changes, then the data read and processed by transaction B is not valid anymore and hence called ‘Dirty Read’. Non-Repeatable Read: This scenario is met when the same query executed at a different point of time within the same application flow gives different result sets. For example, when transaction A issues a query and reads a piece of data, before the end of this transaction another transaction B updates the same data, then if transaction A issues the same query again, it would result in a different set of data. Phantom Reads: This scenario is met when the same query executed at a different point of time within the same application flow gives unequal number of result sets. For example, when transaction A issues a query and reads a piece of data, before the end of this transaction another transaction B inserts a new row, then if transaction A issues the same query again, it would result in a new result set with different number of rows. Security: Each database has a list of user names and its corresponding password. Access to database is achieved only upon entering a valid user name and password. Also, each user in the list is associated to a security domain. A domain is a set of properties that determine the access privileges (the permission of users for actions on databases and its objects), table space quotas and the limits for system resources. This type of access control is achieved by coding appropriate GRANT/REVOKE statements when creating the USERS for the database. Appendix A Content of Oracle Spool file > SELECT * FROM SHIP ORDER BY SHIPNAME TONNAGE SHIPNAME BUILDDATE SPEED ---------------------- ------------------------------ ------------------------- ----- 77000 Adonia 08-02-94 21 1585 Amazing Grace 10-01-75 Sails 61000 Amsterdam 05-09-77 22.5 76000 Aurora 16-07-80 24 11209 Black Prince 25-06-76 24.5 28668 Black Watch 31-12-81 21 77000 Dawn Princess 30-03-84 21 77713 Galaxy 21-09-79 21.5 220000 Genesis 11-11-78 22.3 46811 Horizon 17-03-75 21.4 90090 Jewel of the Seas 20-08-82 25 34242 Norwegian Crown 01-06-81 22 92250 Norwegian Dawn 09-02-78 25 77000 Oceana 28-07-82 21 430 Polynesia 09-05-72 Sails 78491 Rhapsody 23-02-75 22 70367 Sensation 31-12-75 21 133500 Serenata 07-11-74 20.5 18 rows selected > SELECT SHIPNAME, TONNAGE FROM SHIP WHERE TO_CHAR(BUILDDATE,YYYY)=1975 SHIPNAME TONNAGE ------------------------------ ---------------------- Amazing Grace 1585 Horizon 46811 Rhapsody 78491 Sensation 70367 > SELECT COUNT(SHIPNO) NoOfShips, SPEED FROM SHIP GROUP BY SPEED ORDER BY NoOfShips DESC NOOFSHIPS SPEED ---------------------- ----- 5 21 2 22 2 25 2 Sails 1 21.4 1 24.5 1 22.3 1 24 1 22.5 1 21.5 1 20.5 11 rows selected > SELECT PORT.PORTNAME, SHIP.SHIPNAME FROM PORT,SHIP,SHIPPORT WHERE SHIPPORT.PORTID=PORT.PORTID AND SHIPPORT.SHIPNO=SHIP.SHIPNO ORDER BY PORT.PORTNAME,SHIP.SHIPNAME NOOFSHIPS SPEED ---------------------- ----- 5 21 2 22 2 25 2 Sails 1 21.4 1 24.5 1 22.3 1 24 1 22.5 1 21.5 1 20.5 11 rows selected > SELECT COUNT(*) NO_OF_SHIPS FROM SHIPPORT WHERE SHIPPORT.PORTID IN (SELECT PORTID FROM PORT WHERE PORT.PORTCOUNTRY=Barbados) NO_OF_SHIPS ---------------------- 2 > SELECT EVENTNAME FROM EVENT WHERE EVENTID IN (SELECT PORTEVENT.EVENTID FROM PORTEVENT,EVENT WHERE PORTEVENT.EVENTID=EVENT.EVENTID AND PORTEVENT.PORTID=N/A) ORDER BY EVENTNAME EVENTNAME -------------------- Golf Shuffleboard > spool off References Greenwald, Rick., Stackowiak, Robert., Stern, Jonathan. (2007) Oracle Essentials. 4th ed. OReilly Media, Inc.Oracle Documentation (2005). How to spool with Oracle [WWW] scribd. http://www.scribd.com/doc/4542950/How-to-Spool-in-Oracle. [Accessed on 25-02-2012]. Oracle Database Concepts [WWW] Oracle. Available from http://docs.oracle.com/cd/B19306_01/server.102/b14220/security.htm [Accessed on 24-02-2012]. Oracle Isolation Level Tips [WWW] Burleson Consulting. http://www.dba-oracle.com/t_oracle_isolation_level.htm. . [Accessed on 23-02-2012]. Using Oracle Locks to Manage Data Concurrency and Consistency [WWW] Database Journals. http://www.databasejournal.com/features/oracle/article.php/2223371/Using- Oracle-Locks-to-Manage-Data-Concurrency-and-Consistency.htm. [Accessed on 25-02-2012]. Read More