Relational Database System Design - Admission/Application Essay Example

Relational Database System Design 1. Executive Summary ECT is a project for an organization that deals with the stocking and selling of clothe products. It intends development of a system integrated with a relational database system design and a front-end interface for managing the movement of stock sales of clothes. ECT requires a critical consideration of the relationships that exist between the database tables. The products are classified in different fields as follows, Product ID, Product Name, Style, Color, Size and Cost of each clothe. The structure of the database is as shown below. Product ID Product Name Style Color Size Cost The current system has been facing some challenges with the structure and the design of its database and the relationship of the data items. The immediate noticeable challenge with the database is the use of important database elements, such as primary keys, foreign keys and the relationships. The tables apparently require normalization. If database is not normalized, there is a high risk of the data being inconsistent and lacking integrity. The proposed information management system for the ECT project should have the ability to integrate flawlessly into the ECT Relational Database. The ECT Information Management System infrastructure receives the required data through structural query request, using special criteria for data search. The ECT project seeks better ways of rearranging its data elements to avoid replication of information and improve the performance of the information System. 2. Introduction The strategic plan for ECT is to use the data to serve the customers by supplying the required products, as the customers require them. The descriptions of the products for example, have to be consistent with the data descriptions. The company places its clients from the onset to concentrate all the important aspects of its business around the interests of its customers. In this strategy, the sales and all the chances of learning for each client is taken care of. This project provides fine details about the company that uses the ECT strategy and the challenges, which, the organization faces. The project recommends strategic opportunities for assisting the organizational structure to provide visionary and intelligent database management. The system development process and its feasibility evaluation are performed in our proposal. The conclusion that can be drawn from this project will lead to the final structure and design of the database. The relational database will thus be relevant and applicable in this ECT project and will improve the competitive position of the company. 3. Requirement Analysis The ECT project is about database design and normalization. It considers the following elements and keywords for its database design. Legend IDs Data Record   Information View   Null Value Primary Key   Table Foreign Key   Field Linking Table   Multipart Field One-To-One   Calculated Field One-To-Many   Multivalued Field Many-to-Many ECT uses the keywords to create and to manage the data elements and the data about its products and relevant transactions. ECT has the vision of improving the support for its varied client size. The database layout shows a variety of products and services, which the organization adopting the system will be able to operate in. This includes both corporate and individual clients. At present, the database structure shows a large size of business product base, with large and very scattered fields. This conglomeration of dispersed field interferes with the sales power. Data Base Structure The database structure is as shown below, with the data structure and sample dummy data: Product data table Product ID Product Name Style Color Size Cost 1000 shirt plain red S $10.00 1001 shirt plain red M $10.00 1002 shirt plain red L $10.00 1003 shirt plain red XL $10.00 1004 shirt plain red XXL $10.00 Orders Record Order Records Transaction # Customer ID Product ID Quantity Order Subtotal 98-1000 001-0001 1000 2 $20.00 98-1000 001-0001 1049 1 $10.00 98-1000 001-0001 1112 1 $15.00 Transaction Records Transaction Records Transaction # product subtotal Shipping Cost Total Cost Purchase Date Shipping Address Shipping ID 98-1000 $45.00 $5.30 $50.30 11/12/2013 3132 Nonya Buisness Avenue 101 Shipping Method Data Table Shipping Methods Shipping ID Methods 100 FedEx 101 UPS   Customers Records Customers Customer ID First Name Last Name CC Type Phone Number Billing Address City, State Zip Code 001-0001 Robert Russell Master 8123689512 3135 Packson Avenue Terre Haute, Indiana 47806                                 Relationships Between the products and the Orders, there is a one – to - much relationship. This is because order can include many products, while one product can only belong to one order. Between the Products and Customers, there exists a many to one relationship because one customer can order for more than one product, while one product can only belong to one customer. Between the Product and the Transaction, there is a many – to - one relationship, because one transaction can involve many products while one product can only be in one transaction. Stages of Implementation Phase 1: Identification Stage Identification of data structures and design needs Identification of the relationship need Identification of the level and the order of normalization Identification of the primary key and the foreign keys Phase 2: Relationship In this phase, we create the relationships between the tables and datasets To avoid data redundancy as it appears in the product table, we raise the level of normalization to the highest possible order (4th order). Phase 3: Design phase At this stage, we design and construct the system to meet the following standards and needs: To be able to receive the data input To perform any necessary computations To display output of data To be able to accommodate changes and improvement To provide access to customer and the ECT product information needs and to maintain the relationships between the data items To generate and provide reports consistent with the ECT transactions 4. Proposed Features and functionalities ECT system will be made of dynamic applications with a high speed because the transaction size grows and slows down the data access speed (Tai-hoon and Hojjat 56). The speed should be optimized because the ECT system is real-time. The relationships should be able to prevent data replication and inconsistency. The system should be able to provide the sale transaction for each product, and the history of every client. It should prevent loses of transactions and duplication of data. The primary keys and foreign keys exist for that very purpose to control serialization in each data table. The system should allow for data updates in all the tables so that if any staff makes mistakes, then he or she can update the data. 5. System Description ECT is an integrated information system for managing the storage and movement of products. It also keeps records of the customers and product transactions. It should allow multiple user access and multiple data operations. In other words, it should be able to allow more than one user to access the data and operate at the same time. It has records about shipping and orders. It enables every individual user to access the data through access control. It maintains the records about clients using an integrated document system. Works Cited Tai-hoon, Kim., and Hojjat, Adeli. Advances in Computer Science and Information Technology: AST/UCMA/ISA/ACN 2010 Conferences, Miyazaki, Japan June 23-25, 2010. Joint Proceedings, New York: Springer, 2010. Read More