Lanchester Liners Hospitality Database - Essay Example

?Lanchester Liners Hospitality Database This report presents a detailed overview of 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. In addition, the report also offers a detailed discussion on multi-user access to a database including concurrency, locking, security and access control. 1. Create tables and insert all the data provided in Appendix A a) Table Creation From the given schema and the information provided for the schema, the columns for each table and their data types are summarized below: SHIP EVENT PORT SHIPPORT PORTEVENT shipNo (Integer) eventId (Varchar(10)) portId (Varchar(4)) shipNo (Integer) portId (Varchar(4)) shipName (Char(25)) eventName (Varchar(15)) portCountry (Char(20)) portId (Varchar(4)) eventId (Varchar(10)) tonnage (Integer) portName (Char(20)) buildDate (Date) speed (Varchar(5) The tables are created using the below SQL statements: CREATE Statements: CREATE TABLE SHIP ( shipNo integer NOT NULL , shipName Char(25) NOT NULL , tonnage Integer NOT NULL, buildDate Date NOT NULL, speed Varchar(5) NOT NULL); CREATE TABLE EVENT ( eventId Varchar(8) NOT NULL, eventName Varchar(15) NOT NULL ); CREATE TABLE PORT ( portId Varchar(4) NOT NULL , portCountry Varchar(20) NOT NULL, portName Varchar(20) NOT NULL); CREATE TABLE SHIPPORT ( shipNo integer NOT NULL, PortId Varchar(4) NOT NULL ); CREATE TABLE PORTEVENT ( portId Varchar(4) NOT NULL, eventId Varchar(8) NOT NULL); Defining Primary Key: ALTER TABLE SHIP ADD CONSTRAINT pk_ship PRIMARY KEY(shipNo); ALTER TABLE EVENT ADD CONSTRAINT pk_event PRIMARY KEY(eventId); ALTER TABLE PORT ADD CONSTRAINT pk_port PRIMARY KEY(portId); ALTER TABLE SHIPPORT ADD CONSTRAINT pk_shipport PRIMARY KEY(shipNo,portId); ALTER TABLE PORTEVENT ADD CONSTRAINT pk_portevent PRIMARY KEY(portId,eventId); b) Database Population Database is populated with the given information using the below SQL INSERT Statements: Populating SHIP table: INSERT ALL INTO SHIP(shipNo, shipName, Tonnage, buildDate, speed) Values(9104005,'Adonia',77000,'08/02/1994','21') INTO SHIP(shipNo, shipName, Tonnage, buildDate, speed) Values(19238, 'Amazing Grace',1585,'10/01/1975','Sails') INTO SHIP(shipNo, shipName, Tonnage, buildDate, speed) Values(1893233,'Amsterdam',61000,'05/09/1977','22.5') INTO SHIP(shipNo, shipName, Tonnage, buildDate, speed) Values(1029833,'Aurora',76000,'16/07/1980','24') INTO SHIP(shipNo, shipName, Tonnage, buildDate, speed) Values(1938821,'Black Prince',11209,'25/06/1976','24.5') INTO SHIP(shipNo, shipName, Tonnage, buildDate, speed) Values(2832911,'Black Watch',28668,'31/12/1981','21') INTO SHIP(shipNo, shipName, Tonnage, buildDate, speed) Values(9283321,'Dawn Princess',77000,'30/03/1984','21') INTO SHIP(shipNo, shipName, Tonnage, buildDate, speed) Values(3903211,'Galaxy',77713,'21/09/1979','21.5') INTO SHIP(shipNo, shipName, Tonnage, buildDate, speed) Values(1120322,'Genesis',220000,'11/11/1978','22.3') INTO SHIP(shipNo, shipName, Tonnage, buildDate, speed) Values(3829111,'Horizon',46811,'17/03/1975','21.4') INTO SHIP(shipNo, shipName, Tonnage, buildDate, speed) Values(3822211,'Jewel of the Seas',90090,'20/08/1982','25') INTO SHIP(shipNo, shipName, Tonnage, buildDate, speed) Values (4833928,'Norwegian Crown',34242,'01/06/1981','22') INTO SHIP(shipNo, shipName, Tonnage, buildDate, speed) Values(3928111,'Norwegian Dawn',92250,'09/02/1978','25') INTO SHIP(shipNo, shipName, Tonnage, buildDate, speed) Values(3920000,'Oceana',77000,'28/07/1982','21') INTO SHIP(shipNo, shipName, Tonnage, buildDate, speed) Values(3029992,'Polynesia',430,'09/05/1972','Sails') INTO SHIP(shipNo, shipName, Tonnage, buildDate, speed) Values(3400049,'Rhapsody',78491,'23/02/1975','22') INTO SHIP(shipNo, shipName, Tonnage, buildDate, speed) Values(2939222,'Sensation',70367,'31/12/1975','21') INTO SHIP(shipNo, shipName, Tonnage, buildDate, speed) Values(3992019,'Serenata',133500,'07/11/1974','20.5') SELECT * FROM DUAL; Populating PORT Table: INSERT ALL INTO PORT(portId,portCountry,portName) VALUES ('P1','Cayman Islands', 'George Town') INTO PORT(portId,portCountry,portName) VALUES ('P2','Barbados', 'Bridgetown') INTO PORT(portId,portCountry,portName) VALUES ('P3','Belize','Belize City') INTO PORT(portId,portCountry,portName) VALUES ('P4','Bermuda','Hamilton') INTO PORT(portId,portCountry,portName) VALUES ('P5','England', 'Southampton') INTO PORT(portId,portCountry,portName) VALUES ('P6','Scotland', 'Invergordon') INTO PORT(portId,portCountry,portName) VALUES ('P7','Puerto Rico','San Juan') INTO PORT(portId,portCountry,portName) VALUES ('P8', 'Venezuela','Isla Margarita') INTO PORT(portId,portCountry,portName) VALUES ('P9','Anguilla','Blowing Point') INTO PORT(portId,portCountry,portName) VALUES ('P10','St. Vincent', 'Kingstown') INTO PORT(portId,portCountry,portName) VALUES ('P11', 'New Zealand', 'Auckland') INTO PORT(portId,portCountry,portName) VALUES ('P12','Colombia', 'Cartegena') INTO PORT(portId,portCountry,portName) VALUES ('P13','St. Barts','Gustavia') INTO PORT(portId,portCountry,portName) VALUES ('P14','Antigua','St. John') INTO PORT(portId,portCountry,portName) VALUES ('P15','Russia St.', 'Petersburg') INTO PORT(portId,portCountry,portName) VALUES ('P16','Denmark', 'Copenhagen') INTO PORT(portId,portCountry,portName) VALUES ('P17','Costa Rica', 'Puerto Limon') INTO PORT(portId,portCountry,portName) VALUES ('P18','Martinique', 'Fort de France') INTO PORT(portId,portCountry,portName) VALUES ('P19','Italy','Messina') INTO PORT(portId,portCountry,portName) VALUES ('P20','Spain', 'Barcelona') INTO PORT(portId,portCountry,portName) VALUES ('P21','Spain','Ibiza') INTO PORT(portId,portCountry,portName) VALUES ('P22','Croatia', 'Dubrovnik') INTO PORT(portId,portCountry,portName) VALUES ('P23','Monaco','Monte Carlo') INTO PORT(portId,portCountry,portName) VALUES ('P24','Italy', 'Santa Margherita') INTO PORT(portId,portCountry,portName) VALUES ('P25','Italy', 'Civitavecchia') INTO PORT(portId,portCountry,portName) VALUES ('P26','Belgium', 'Zeebrugge') INTO PORT(portId,portCountry,portName) VALUES ('P27','Corfu','Kerkira') INTO PORT(portId,portCountry,portName) VALUES ('P28','Norway', 'Kristiansand') INTO PORT(portId,portCountry,portName) VALUES ('P29','Finland','Helsinki') INTO PORT(portId,portCountry,portName) VALUES ('P30','Sweden', 'Stockholm') SELECT * FROM DUAL; Populating Event Table: INSERT ALL INTO EVENT(eventId,eventName) VALUES ('Event1','BBQ') INTO EVENT(eventId,eventName) VALUES('Event2','Shooting') INTO EVENT(eventId,eventName) VALUES('Event3','Jet Ski') INTO EVENT(eventId,eventName) VALUES('Event4','Golf') INTO EVENT(eventId,eventName) VALUES('Event5','Whales') INTO EVENT(eventId,eventName) VALUES('Event6','Scuba') INTO EVENT(eventId,eventName) VALUES('Event7','Casino') INTO EVENT(eventId,eventName) VALUES('Event8','Helicopter') INTO EVENT(eventId,eventName) VALUES('Event9','Fishing') INTO EVENT(eventId,eventName) VALUES('Event10','Shuffleboard') INTO EVENT(eventId,eventName) VALUES('Event11','Ice Hotel') SELECT * FROM DUAL; Populating shipPort table: INSERT ALL INTO SHIPPORT(SHIPNO,PORTID) VALUES (9104005,'P1') INTO SHIPPORT(SHIPNO,PORTID) VALUES (9104005,'P2') INTO SHIPPORT(SHIPNO,PORTID) VALUES (19238,'P2') INTO SHIPPORT(SHIPNO,PORTID) VALUES (19238,'P3') INTO SHIPPORT(SHIPNO,PORTID) VALUES (19238,'P4') INTO SHIPPORT(SHIPNO,PORTID) VALUES (1893233,'P3') INTO SHIPPORT(SHIPNO,PORTID) VALUES (1893233,'P4') INTO SHIPPORT(SHIPNO,PORTID) VALUES (1029833,'P14') INTO SHIPPORT(SHIPNO,PORTID) VALUES (1029833,'P4') INTO SHIPPORT(SHIPNO,PORTID) VALUES (1938821,'P7') INTO SHIPPORT(SHIPNO,PORTID) VALUES (1938821,'P13') INTO SHIPPORT(SHIPNO,PORTID) VALUES (2832911,'P8') INTO SHIPPORT(SHIPNO,PORTID) VALUES (2832911,'P9') INTO SHIPPORT(SHIPNO,PORTID) VALUES (2832911,'P10') INTO SHIPPORT(SHIPNO,PORTID) VALUES (9283321,'P11') INTO SHIPPORT(SHIPNO,PORTID) VALUES (9283321,'P12') INTO SHIPPORT(SHIPNO,PORTID) VALUES (3903211,'P13') INTO SHIPPORT(SHIPNO,PORTID) VALUES (1120322,'P14') INTO SHIPPORT(SHIPNO,PORTID) VALUES (1120322,'P4') INTO SHIPPORT(SHIPNO,PORTID) VALUES (3829111,'P17') INTO SHIPPORT(SHIPNO,PORTID) VALUES (3829111,'P18') INTO SHIPPORT(SHIPNO,PORTID) VALUES (3822211,'P20') INTO SHIPPORT(SHIPNO,PORTID) VALUES (3822211,'P21') INTO SHIPPORT(SHIPNO,PORTID) VALUES (4833928,'P23') INTO SHIPPORT(SHIPNO,PORTID) VALUES (3928111,'P24') INTO SHIPPORT(SHIPNO,PORTID) VALUES (3928111,'P25') INTO SHIPPORT(SHIPNO,PORTID) VALUES (3928111,'P22') INTO SHIPPORT(SHIPNO,PORTID) VALUES (3920000,'P27') INTO SHIPPORT(SHIPNO,PORTID) VALUES (3029992,'P19') INTO SHIPPORT(SHIPNO,PORTID) VALUES (3400049,'P28') INTO SHIPPORT(SHIPNO,PORTID) VALUES (3400049,'P29') INTO SHIPPORT(SHIPNO,PORTID) VALUES (2939222,'P30') INTO SHIPPORT(SHIPNO,PORTID) VALUES (2939222,'P15') INTO SHIPPORT(SHIPNO,PORTID) VALUES (3992019,'P16') INTO SHIPPORT(SHIPNO,PORTID) VALUES (3992019,'P6') INTO SHIPPORT(SHIPNO,PORTID) VALUES (3992019,'P26') INTO SHIPPORT(SHIPNO,PORTID) VALUES (3992019,'P5') SELECT * FROM DUAL; Populating portEvent table: INSERT ALL INTO PORTEVENT(PORTID,EVENTID) VALUES('P1','Event1') INTO PORTEVENT(PORTID,EVENTID) VALUES('P2','Event2') INTO PORTEVENT(PORTID,EVENTID) VALUES('P2','Event9') INTO PORTEVENT(PORTID,EVENTID) VALUES('P3','Event2') INTO PORTEVENT(PORTID,EVENTID) VALUES('P4','Event3') INTO PORTEVENT(PORTID,EVENTID) VALUES('N/A','Event4') INTO PORTEVENT(PORTID,EVENTID) VALUES('P3','Event9') INTO PORTEVENT(PORTID,EVENTID) VALUES('P4','Event5') INTO PORTEVENT(PORTID,EVENTID) VALUES('P14','Event2') INTO PORTEVENT(PORTID,EVENTID) VALUES('P4','Event8') INTO PORTEVENT(PORTID,EVENTID) VALUES('P7','Event2') INTO PORTEVENT(PORTID,EVENTID) VALUES('P13','Event6') INTO PORTEVENT(PORTID,EVENTID) VALUES('P8','Event5') INTO PORTEVENT(PORTID,EVENTID) VALUES('P9','Event6') INTO PORTEVENT(PORTID,EVENTID) VALUES('P10','Event9') INTO PORTEVENT(PORTID,EVENTID) VALUES('P11','Event5') INTO PORTEVENT(PORTID,EVENTID) VALUES('P12','Event2') INTO PORTEVENT(PORTID,EVENTID) VALUES('P13','Event8') INTO PORTEVENT(PORTID,EVENTID) VALUES('P14','Event3') INTO PORTEVENT(PORTID,EVENTID) VALUES('N/A','Event10') INTO PORTEVENT(PORTID,EVENTID) VALUES('P17','Event9') INTO PORTEVENT(PORTID,EVENTID) VALUES('P18','Event8') INTO PORTEVENT(PORTID,EVENTID) VALUES('P20','Event7') INTO PORTEVENT(PORTID,EVENTID) VALUES('P21','Event6') INTO PORTEVENT(PORTID,EVENTID) VALUES('P23','Event7') INTO PORTEVENT(PORTID,EVENTID) VALUES('P24','Event1') INTO PORTEVENT(PORTID,EVENTID) VALUES('P25','Event1') INTO PORTEVENT(PORTID,EVENTID) VALUES('P22','Event2') INTO PORTEVENT(PORTID,EVENTID) VALUES('P27','Event1') INTO PORTEVENT(PORTID,EVENTID) VALUES('P19','Event1') INTO PORTEVENT(PORTID,EVENTID) VALUES('P28','Event8') INTO PORTEVENT(PORTID,EVENTID) VALUES('P29','Event11') INTO PORTEVENT(PORTID,EVENTID) VALUES('P30','Event11') INTO PORTEVENT(PORTID,EVENTID) VALUES('P15','Event9') INTO PORTEVENT(PORTID,EVENTID) VALUES('P16','Event3') INTO PORTEVENT(PORTID,EVENTID) VALUES('P6','Event9') INTO PORTEVENT(PORTID,EVENTID) VALUES('P26','Event8') INTO PORTEVENT(PORTID,EVENTID) VALUES('P5','Event7') SELECT * FROM DUAL; 2. SQL Commands a) List the ship names, tonnage, build date and speed of all ships in alphabetical order of ship name. SELECT SHIPNAME, TONNAGE, BUILDDATE, SPEED FROM SHIP ORDER BY SHIPNAME b) List the ship names and tonnage of all ships that were built in 1975. SELECT SHIPNAME, TONNAGE FROM SHIP WHERE TO_CHAR(BUILDDATE,'YYYY')='1975' c) List, in descending order of totals, the total number of ships by speed. SELECT COUNT(SHIPNO) TOTAL, SPEED FROM SHIP GROUP BY SPEED ORDER BY TOTAL DES d) List, in alphabetical order, both the names of the ports and the ships that dock at each port. 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 e) Using a nested subquery, list the total number of ships which dock at Barbados. SELECT COUNT(*) NO_OF_SHIPS FROM SHIPPORT WHERE SHIPPORT.PORTID IN (SELECT PORTID FROM PORT WHERE PORT.PORTCOUNTRY='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). 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 3. Presentation of results The reports for the above SQL queries are generated and are shown in below figures: Fig 1: Report for query (a) Fig 2: Report for query (b) Fig 3: Report for query (c) Fig 4: Report for query (d) Fig 5: Report for query (e) Fig 6: Report for query (f) 4. Multiuser access to database: The primary function of a database is not only to store and retrieve data but also to efficiently handle concurrent request for data from multiple users while maintaining the data security from unauthorised users. Designing a database to handle multiple concurrent requests and granting/restricting the data access based on user’s access privilege are one of the most challenging tasks in multi-user environment. These two features of the database are discussed in detail in the following sections: Multi user Concurrency: Concurrency is the ability of a database to ensure that any number or users can retrieve or update data as if they are the only owners accessing the piece of data. In other words, no two users should either try to overwrite each other’s data or read uncommitted data that is being changed by the other user. In order to achieve this, a database uses the concept of isolation using locks. A lock prevents transaction from interfering with each other. A locked data is not allowed to get updated by another user, thereby preventing one transaction from overwriting the changes made by other transaction. Locks can be issued in exclusive mode or shared mode. An exclusive lock prevents the data to be shared. This lock mode is recommended to modify data. A shared lock allows the data to be shared among users depending upon the type of operation. In general, each database offers a default level of locking, however for critical situation like booking systems where no two users should be allowed to book the same seat or an item, it is recommended for the application to issue an explicit lock by coding FOR UPDATE clause in the select statement while retrieving the data. Too frequent use of FOR UPDATE clause will slow down the performance. This is because, when user A issues a lock on a particular transaction, then user B has to wait as long as user A commits the changes in order to update the same piece of data. This type of interference caused by the conflicting locks is called contention. The more the contention, slower is the performance and lower is the overall throughput. If proper isolation is not enforced within the transactions, various data integrity problems may arise, four of which are discussed below: Lost Updates: This type of data integrity issue arises when two transactions update the same piece of data out of which the change made by one transaction overwrites the changes made by the other one. Dirty Reads: This occurs when databases allow users to read the uncommitted data that is being changed by another user. Uncommitted read is usually allowed to avoid contention issues discussed earlier. But in cases, when the user rolls back the changes made, then data read would be incorrect. Non-Repeatable Reads: This type of issue is experienced when a transaction A issues a query and reads a piece of data, before the end of this transaction another transaction B updates the same data, the if transaction A issues the same query again, it would result in a different set of data and the original set of data obtained earlier would be of no value. Phantom Reads: This is similar to Non-Repeatable read, but in this case the transaction B inserts a new row, then the result set produced by the same query at different point of time within the same transaction will not have the same number of rows at all times In order to overcome the above integrity issues, Oracle database offers 3 types of isolation levels: Read consistency, Serializable transaction and Read only. Read consistency is the default isolation level. It ensures that a query reads only the data that are committed before the execution of the query (not the transaction) and this removes the possibility of dirty reads. Serializable transactions ensures that a query in the transaction reads only the data that are committed at the time the transaction began and the changes made by the transaction itself (insert, update or delete). Read only transactions see only the data that are committed before the transaction began and does not allow any update, insert or delete statements. All locks acquired by the transaction remain in effect till the duration of the transaction. Oracle releases the locks when an explicit COMMIT or ROLLBACK is issued by the transaction. Security: Database Security deals with granting and revoking access to actions on databases and its objects depending upon the user privileges. A privilege is the permission to access an object in a specified manner. For example if a user A has or is granted a privilege to query a particular table, then user A can only perform SELECT action, all other actions like insert, update or delete will not be allowed. In Oracle, database access control is achieved through user schema and security domains. The database will have a list of user names and any user can connect to and access a database only through a valid user name. Also, each user name will be associated with a valid password to prevent unauthorized access. A user is allowed to connect only if he or she enters the valid user name and the corresponding password. And each user is associated to a security domain. A domain is a set of properties that determine the access privilege, tablespace quotas and the limits for system resources. This way, each user connecting to a database will be granted access to database objects based on the defined privileges for that user. References Greenwald, Rick., Stackowiak, Robert., Stern, Jonathan. (2007) Oracle Essentials. 4th ed. O'Reilly Media, Inc. Oracle Documentation (2005) Oracle Database Concepts [WWW] Oracle. Available from http://docs.oracle.com/cd/B19306_01/server.102/b14220/security.htm [Accessed on 29-01-2012]. Read More