Database Design of Torrington Freight Rail - Essay Example

Table of Contents Chapter 3 Introduction 3 Chapter 2 4 Constraints 4 Assumptions 6 Diagram 7 Decisions in the Making Diagram 8 Chapter 3 11 Relational model database 11 Converting the class diagram into a relational model 12 Chapter 4 13 Converting the relational model into the physical model 13 Implementation 14 SQL Queries 23 User Views / Queries 23 Integrity & Constraints 33 Chapter 5 36 Conclusion 36 References 38 Chapter 1 Introduction Torrington Freight Rail (TFR) is a rail freight logistics company that operates (collecting and delivering products) in most parts of the UK. TFR manually maintains data related to its trains, rolling stock, drivers, products, etc. which becomes cumbersome and tiresome to manage. TFR requires a data model to replace its current manual database system. This aim of this report is to give a detailed description of the design for a relational database specifically proposed to fulfil the TFR data needs. The relational database system will enable TFR to manage all its train schedules and make efficient use of its rolling stock. The report gives a detailed technical description of the systematic formation of the system’s data model. The objectives of the report intend to encompass the important details related to the four major phases involved in building the proposed database design: 1. Formation of a structural model (i.e. Class diagram) based upon the TFR system’s basic entities and their associations, the involved system constraints and assumptions. 2. Highlight the database design considerations for converting the proposed class diagram to its corresponding relational model. 3. Highlight the design consideration for converting the proposed relational model to its corresponding physical model intended for the Oracle. 4. Implementation of the database design in Oracle and testing of the design through scenario specific SQL queries. The correctness of the result would prove the correctness of the proposed database design. The report ends with a critical evaluation of the proposed model and a review of the overall relational database design exercise. Chapter 2 Constraints The major constraint in designing a class diagram of a scheduling management database is in relation to a large number of variants brought about by the seasonal change of train journey, planned and unplanned maintenance of trains and rearrangement of weekdays and the holiday. Though the calendar stating of each train, the entry number description for a particular day, time and station makes the whole process cumbersome. The approach requires not less than 2 million records to accommodate the train scheduling the proposed relational database. In addition, to hold the schedule for a long time raises the number of records the key issue is the operation of updating changes in the schedule. Another key constraint is the issues relating to objects that have overlapping lifespan. This is very profound when this object overlaps. The situation is peculiar since the object maintains its state actuality even after changing (IBM 2003). It only takes place in that given a short time during the replacement. This will result in a situation where we have multiple real time tuple which describes the different versions that the same object has. This idea does not merge the previous RDBMS concept, therefore it is inherent to develop special data queries which will solve this situation. There are two data constraints visible in the TFR case study: 1. The total length of a train has to remain within the limit of 400 meters 2. The gross weight (tare + payload) of all the wagons making a train must be less than the maximum towing weight of the train’s locomotive Assumptions The four assumptions that are made during the design process of the relational database, these are: The time schedule and underlying condition will remain the same as long as the company’s mode of operation is still the same. The number of freight managed by the company will neither increase nor decrease in the next six months. This will enable the implementation process to be carried on smoothly. Both functional and non functional requirements of the database system are maintained in accordance with the requirement specification document. The current location of the rolling stock is irrelevant as they are assumed to be present at the place they are required. Class Diagram The class diagram for the TFR system is shown below: Decisions in the Making of Class Diagram To form a structural model for the system, all critical entities of the TFR for which data is stored and that are part of the daily business operations were identified. The basic entities for the TFR case study include: Products to collect and deliver Trains that carry products Rolling stock (locomotive and wagons) for carrying the products Product consignment information Routes for the trains to follow Drivers that drive the train and rolling stock The train schedule The stages that can be part of the route The stations that can be part of a stage The information (attributes and identification) stored for each entity is according to the specifications in the TFR case study and is as follows: Product ID [number(10)] – to identify a product to collect/deliver Description [string(35)] – to describe Weight [decimal (10)] – to store the weight of the product Quantity [string(10)] - to store quantity of the product to collect/deliver Train ID [number (10)] – to identify a train Rolling Stock ID [string(20)]– to identify a rolling stock Owned_No [number (10)] – to store the total number of stocks owned Length [decimal (10)] – to store the length of a stock Wagon_Type [string(20)] – to store the type of wagon stock Wagon_Description [string(100)] – to store a description of wagon stock Wagon_Tare_Weight [string (20)] – to store the tare weight of the wagon stock Wagon_Max_Payload [string (20)] – to store the maximum payload for the wagon stock Locomotive_Class [string(20)] – to store class info for a locomotive stock Locomotive_Max_Towing_Weight [decimal (10)] – to store maximum towing weight supported for a stock Consignment ID [number (10)] – to identify a consignment From_station [string (20)] – to store the source station To_Station [string (20)] – to store the intended destination Satus [string(35)] – to store the status of a consignment (unallocated, available or unavailable) Route ID [number (10)] – to identify a route Distance string (10)] – to specify the distance of a route Driver ID [number (10)] – to identify a driver Name [string(35)] – to store the name of a driver Address [string(35)] – to store the address of a driver Phone [string(35)] – to store the phone number of a driver Email [string(35)] – to store the email of the driver Stage ID [number (10)] – to identify a station Station_Start [string (20)] – to store the starting station Station_End [string (20)] – to store the ending station Distance [string (20)] – to store the distance between the two stations Station ID [number (10)] - to identify a station Name [string(20)] – to store the name of a station The associations (mandatory/optional and cardinality) found between the entities are described as follows: A consignment carries one or more product (mandatory – one-to-many) A product may be carried in many consignments (optional – one-to-many) A stock may be either wagon or locomotive (specialization). Through the use of specialization in the rolling stock class, the design of the system represents the real world more realistically. The common attributes form the rolling stock class, while the locomotive and the wagon subclass have distinct attributes. A train contains a locomotive and many wagons (aggregation) A driver can drive many types of locomotives (mandatory – one-to-many) A locomotive type can be driven by many drivers (mandatory – one-to-many) A consignment may be assigned many trains (mandatory – one-to-many) A train may be assigned to many consignments (optional –one-to-many) A consignment has one or more schedules (mandatory – one-to-many) A schedule belongs to only one consignment (mandatory – one-to-one) A schedule has a route (mandatory – one-to-one) A route belongs to many schedules (mandatory one-to-many) A route has many stages (mandatory – one-to-many) Chapter 3 Relational model database Converting the class diagram into a relational model The major design decisions that are considered in the conversion of the class diagram into a relational model are: Representation of classes. The idea resembles as no much difference with the classes in the conceptual class model. The decision involves addition of the word table to all the classes. The table representing all the schemas is created. The attributes of all tables created must be maintained. Choosing of primary keys- the primary keys of every table are chosen. The choice depends on the nature and previous state of a table. The attributes that already exist in a table can be used as the primary key (Helping & Bloesch 2009). The creation of the primary key can be automated to ensure that the database critical functions are kept. Representation of associations- the foreign keys that represent associated is placed within every table in accordance with the association being represented. These include one-to-many, many-to-one and many-to-many. Classification Hierarchies representation- the Classification Hierarchies like the association between different entities are represented using various methods such use of parent class primary key in the child class. Enforcement of referential integrity- this is basically ensuring that the referential integrity is consistent. The foreign keys appearing on the table should appear in the primary key value of all the related tables Chapter 4 Converting the relational model into the physical model The objective while defining the physical model is to make the data model efficient in terms of processing speed and storage space. Based upon this objective, the two major decisions that governed the conversion of the relational model to its corresponding physical model are given below: 1. Specifying Data Type: Since the model is to be implemented as an Oracle database, the data types chosen for the TFR database are the primitive Oracle data types. To represent the string based attributes, the varchar2 data type is used. This data type is resource efficient as Oracle allocates space according to the data store within e.g. the string ‘class09’ and ‘class1’ will occupy 7 and 6 bytes respectively. Similarly for the numeric and decimal data, the Oracle’s primitive data type, number is used. Similar to varchar2, the storage size is dependent on the data stored within the variable. A number(10) and number(5) does not occupy equal space. 2. Specifying Attributes: In the relational model, there is a derived attribute ‘distance’ in the route relation (table). This attribute is a summed value of all the individual distances of the stages making the route. Since Oracle provides a storage efficient number data type, the ‘distance’ attribute was kept as part of the physical model as the value is accessed too often and the computation power is saved. 3. Specifying Table Structure: In Oracle, as the NULL values are also allocated space according to the defined data type, tables containing mostly NULL values are defined as separate tables to save storage space. Since none of the attributes are NULL in the relational model, no separate tables were created. Implementation CREATE TABLE ROLLING_STOCK( SERIAL_ID varchar2(20) PRIMARY KEY NOT NULL, OWNED_NO NUMBER(10) NOT NULL , LENGTH DECIMAL(10)NOT NULL) CREATE TABLE WAGON( SERIAL_ID varchar2(20) referencesROLLING_STOCK(SERIAL_ID), WAGON_TYPE VARCHAR2 (20) PRIMARY KEY NOT NULL , DESCRIPTION VARCHAR(100) NOT NULL , TARE_WEIGHT varchar2(20) NOT NULL, MAX_PAYLOAD varchar2 (20) NOT NULL ) CREATE TABLE LOCOMOTIVE_TYPE( SERIAL_ID varchar2(20) references ROLLING_STOCK(SERIAL_ID), LOCOMOTIVE_CLASS varchar2(20) NOT NULL, MAX_TOWING_WEIGHT DECIMAL(10) NOT NULL, TRAIN_ID NUMBER(10) references TRAIN(TRAIN_ID)) CREATE TABLE TRAIN( TRAIN_ID NUMBER(10) PRIMARY KEY NOT NULL) CREATE TABLE FREIGHT_WAGON( TRAIN_ID NUMBER(10) references TRAIN(TRAIN_ID), SERIAL_ID varchar2 (20) references ROLLING_STOCK(SERIAL_ID)) CREATE TABLE LOCOMOTIVE( TRAIN_ID NUMBER(10) references TRAIN(TRAIN_ID), SERIAL_ID varchar2(20) references ROLLING_STOCK(SERIAL_ID)) CREATE TABLE DRIVER( DRIVER_ID NUMBER(10) PRIMARY KEY NOT NULL , DRIVER_NAME varchar2(20) , PHONE VARCHAR2(15) , ADDRESS VARCHAR2(30), EMAIL varchar2(60)) CREATE TABLE DRIVER_LOCOMOTIVE( DRIVER_ID NUMBER(10) references DRIVER(DRIVER_ID) , SERIAL_ID varchar2(20) references ROLLING_STOCK(SERIAL_ID)) CREATE TABLE CONSIGNMENT( CONSIGNMENT_ID number(10) PRIMARY KEY NOT NULL, FROM_STATION varchar2(20) NOT NULL, TO_STATION varchar2(20) NOT NULL, STATUS varchar2(20) NOT NULL) CREATE TABLE TRAIN_ALLOCATION( TRAIN_ID NUMBER(10) references TRAIN(TRAIN_ID), CONSIGNMENT_ID NUMBER(10) references CONSIGNMENT(CONSIGNMENT_ID), DRIVER1_ID NUMBER(10) references DRIVER(DRIVER_ID), DRIVER2_ID NUMBER(10) references DRIVER(DRIVER_ID)) CREATE TABLE PRODUCT( PRODUCT_ID number(10) PRIMARY KEY NOT NULL, DESCRIPTION varchar2(35) NOT NULL, WEIGHT varchar2(20) NOT NULL , QUANTITY varchar2(10)) CREATE TABLE CONSIGNMENT_PRODUCT( PRODUCT_ID number(10) references PRODUCT(PRODUCT_ID), CONSIGNMENT_ID number(10) references CONSIGNMENT(CONSIGNMENT_ID)) CREATE TABLE ROUTE( ROUTE_ID NUMBER(10) PRIMARY KEY NOT NULL, DISTANCE varchar2(10) NOT NULL) CREATE TABLE TRAIN_SCHEDULE( SCHEDULE_ID NUMBER(10) PRIMARY KEY NOT NULL, DEPARTURE_DATE varchar2(20) NOT NULL, DEPARTURE_TIME varchar2(20) NOT NULL, ARRIVAL_DATE varchar2(20) NOT NULL, ARRIVAL_TIME varchar2(20) NOT NULL, CONSIGNMENT_ID NUMBER(10) references CONSIGNMENT(CONSIGNMENT_ID), ROUTE_ID number(10) references ROUTE(ROUTE_ID)) CREATE TABLE STATION( STATION_ID number(10) PRIMARY KEY NOT NULL, STATION_NAME varchar2(20)) CREATE TABLE STAGE( STAGE_ID number(10) PRIMARY KEY NOT NULL , STATION_START VARCHAR2(20) NOT NULL, STATION_END varchar2(20) NOT NULL, DISTANCE varchar2(20) NOT NULL ) CREATE TABLE STAGE_ROUTE( STAGE_ID number(10) references STAGE(STAGE_ID) , ROUTE_ID number(10) references ROUTE(ROUTE_ID)) SQL Queries User Views / Queries Query 1: Show drivers who can drive locomotives of class 09 SELECT driver_name FROM driver, driver_locomotive WHERE driver_locomotive.serial_id LIKE 09% AND driver.driver_id = driver_locomotive.driver_id Query 2: Find the total number owned by ’Tank Wagon’ wagon SELECT sum(owned_no) FROM wagon, rolling_stock WHERE rolling_stock.serial_id=wagon.serial_id AND wagon.wagon_type= Tank wagon Query 3.1: Show all wagons that can support product of volume = 1000 tonnes SELECT serial_id FROM wagon WHERE TARE_WEIGHT*max_payload > 1000 Query 3.2: Show all locomotives that have more than 1500 tonnes WEIGHT SELECT serial_id FROM locomotive_type WHERE MAX_TOWING_WEIGHT> 1500 Query 4.1: Find the gross freight weight of train with id=3 SELECT sum(tare_weight + max_payload) AS grossWeight FROM freight_wagon, wagon WHERE freight_wagon.train_id=3 AND wagon.serial_id= freight_wagon.serial_id Query 4.2: Find the gross freight weight of train with id=3 (without the “AS grossWeight”) SELECT sum(tare_weight + max_payload) FROM freight_wagon, wagon WHERE freight_wagon.train_id=3 AND wagon.serial_id= freight_wagon.serial_id Query 5: Find the locomotives of class08 available for allocation. CREATE VIEW alloc_Trains AS SELECT TRAIN.TRAIN_ID FROM TRAIN, TRAIN_ALLOCATION, CONSIGNMENT WHERE TRAIN_ALLOCATION.TRAIN_ID=TRAIN.TRAIN_ID AND TRAIN_ALLOCATION.CONSIGNMENT_ID=CONSIGNMENT.CONSIGNMENT_ID AND CONSIGNMENT.STATUS=AVAILABLE; SELECT locomotive_type.serial_id FROM locomotive_type,LOCOMOTIVE,consignment,train_allocation WHERE CONSIGNMENT.STATUS=Available AND TRAIN_ALLOCATION.CONSIGNMENT_ID=CONSIGNMENT.CONSIGNMENT_ID AND locomotive_type.LOCOMOTIVE_CLASS=Class 08 AND LOCOMOTIVE.TRAIN_ID=LOCOMOTIVE_TYPE.TRAIN_ID AND LOCOMOTIVE_TYPE.train_id NOT IN (SELECT * FROM alloc_Trains) Query 6: Show schedule of trains allocated to fulfil a consignment with is 10001 SELECT train_id, from_station, to_station, departure_date, departure_time FROM consignment, train_schedule, train_allocation WHERE consignment.consignment_id=10001 AND train_allocation.consignment_id=consignment.consignment_id Query 7: Find the total length of train with id = 2 SELECT sum(length_train) FROM ( SELECT sum(rolling_stock.length) AS length_train FROM rolling_stock, freight_wagon WHERE freight_wagon.train_id=2 AND freight_wagon.serial_id=rolling_stock.serial_id UNION ALL SELECT sum(rolling_stock.length) AS length_train FROM rolling_stock, locomotive WHERE locomotive.train_id=2 AND locomotive.serial_id=rolling_stock.serial_id) Integrity & Constraints CREATE TABLE Consignment( Consignment_ID Number(10)PRIMARY KEY NOT NULL, FROM_STATION Number(20) NOT NULL, TO_STATION Number(20) NOT NULL, STATUS varchar2(20) NOT NULL CHECK (STATUS IN (unallocated,available,unavailable)), CONSTRAINT fk_conSta_sf FOREIGN KEY (FROM_STATION) REFERENCES Station (station_ID), CONSTRAINT fk_conSta_st FOREIGN KEY (TO_STATION) REFERENCES STATION (STATION_ID)); Query 1: Delete locomotives of class09 (referential constraint) DELETE FROM wagon WHERE wagon_type=’ Covered wagon’ Query 2: Leaving empty fields not allowed  (domain constraint) CREATE TABLE DRIVER ( DRIVER_ID NUMBER(10) NOT NULL, DRIVER_Name varchar2(20) NOT NULL, PHONE varchar2 (11) NOT NULL, EMAIL varchar2 (50) NOT NULL, PRIMARY KEY (driver_ID) ); Query 3: Insert wagon of serialID 91100 as a rolling stock when it already exists (entity integrity) INSERT INTO wagon(serial_id, wagon_type, description, max_payload) VALUES (‘91100’,’Open Wagon’, a high listed, 69); Chapter 5 Conclusion The report successfully presents the relational database model proposed for the TFR system. The database design supports the typical data requirements of the company. All data related to the stock, consignment, trains, drivers, routes and stations can be maintained in the database in a way that the useful information can easily be extracted through the data model. The data system allows retrieval of products and their volumes, the trains and the associated drivers The arrival and departure date and time of trains, the source destination station and the route taken for fulfilling a consignment can also be easily found out. All suitable wagons and locomotives can be listed according to the volume of goods in a consignment. All available trains can be listed for allocating to consignments. Proposing the relational database design for the TFR case study served as a practical data modeling exercise of a real world scenario. The project served as an assessment of the basic database design skills. The project required knowledge and understanding of the database design concepts to be able to perform the following activities: Forming structural Model (class diagram): Identify system processes, the identification of entities, their attributes and their relationships with one another. Application of enhanced entity relationship concepts, generalization and aggregation (Connolly and Begg 2009), mandatory and optionally constraint and the cardinalities. Forming relational Model based on the proposed class diagram. This required the formation of entities based on relationships, the formation of primary and foreign keys. Forming a physical model based on the proposed relational model which requires the specification of data types and removal of redundant information tables. Implementing the physical model in Oracle and testing it through SQL queries designed to extract information useful to the LFRs routine business operations. To summarize the project offered a first hand learning experience in designing a database model for a real world scenario. References Connolly, T.M. and Begg, C.E. 2009. Database Systems: A Practical Approach to Design, Implementation and Management. 5th Edition. Addison Weasley. Fowler, M., 2003, UML distilled: A brief guide to the standard object modeling language, 3rd edition, Addison-Wesley Halpin, T., Bloesch, A., 2009, Data modeling in UML and ORM: a comparison, in Journal of database management, vol. 10(4); http://www.orm.net IBM, 2003, Entity relationship modeling with UML, http://www3.software.ibm.com/ibmdl/pub/software/rational/web/whitepapers/2003/ermodeling.pdf Read More