Data Management System - Report Example

SQL Database Student’s Name Course Title: Date: Definition of the business case Johnson is a company that is registered in London that deals with mobile phones and other electronics. The company sells mobile phones from different companies. In this case inventory database is destined to be destined to assist in managing stock from different suppliers as well as manage ordering systems of the company. This will have details of suppliers, customers, stock and order. This will enable the company the data for their clients. The company is assumed to be a wholesale and phones are purchased in large quantities. The database that is designed will have tables which will control the data flow within the company. The tables will include clients’ tables and order the database will manage the stock to ensure there is adequate stock for continued business operations. Description of Database Design The database will have 20 tables and 18 indexes as well as 80 columns and 2000rows because of a number of customers, number of phone brands and number of suppliers. The database will be will include all phones manufactured by branded phones from major international companies which will be sold over-the-counter as well online. It will updated and the program will set up will automatically calculate how many are still in stock or issue order level alert. If no more stock is available for a certain phone, the program will automatically update the database to show an “out-of-stock” sign on the phone’s page. Updates on sales from the online store and existing will be reflected every day on the database. The database’s platform will be able to support up to 1000 brands. To ensure accuracy of the database it will be counterchecked before use. It will be grouped into logical groups that matching with the data used which will be updated regularly E/R diagram The E/R diagram will start by first normalizing the inventory for the company. Normalization involves the removal of redundant data and making sure only relevant information is stored without repetition in a table. The entities of the inventory will be supplier, order and stock entities. In normalizing these entities first normal form to fifty normal form will be considered however due to time constraints, inventory database for Johnson will be normalized into the third normal form First Normal Form- in this form the three entities will be considered and will have attributes like price, stock in store, stock code, stock description, re-order level, re-order quantity, phone number of the customer, supplier name, quantity ordered, address of the supplier, order date and total amount. In this case, the first normal table will involve the creation of normal tables of each entity and have them as primary keys. The first table will have stock details which will include the mobile code, mobile description, mobile quantity, price, re-order level, re-order quantity. In this case the mobile code will be a unique key as it will show the changes in the stock thus we will not need a secondary key. There will be also supplier’s details which will entail Supplier Name, Address, address, city of origin, telephone number which will take the form of alpha-numeric, numeric or character. The unique column will the supplier ID which will not be repeating as other columns thus a primary key for the table. The other entity is order details which will have order ID as a primary key while mobile description will be only column with a field type characters. The other columns will contain order date, quantity ordered, total amount of order and unit price. The last entity will have mobiles in stock which will contain mobile code column as the key table and other columns will contain mobile type quantity, date in, date out and re-order level. Second Normal Form- This is a second normal form which will enable us create a relationship between the tables using key. Foreign keys will be used together in order to connect the tables. The foreign key is important as it is used for cross- referencing or connecting the tables specifically dependent tables. Therefore among the tables, the dependent tables should identified which is the supplier’s details table which depends on others such as mobile in stock tables. The mobile in stock table will determine the suppliers to be contacted when the stock reaches re-order level. The primary key in the mobile stock table which will be connected with supplier details will be order details. This will lead to formation of a table as shown below After third normal form. Third Normal Form- The columns that are independent of the primary key will be removed to attain a third normal form. The columns that will be eliminated will include mobile description, total amount to normalize the table as shown below Entity – Relationship Diagram (E – R Diagram) E/R Diagram has three entities diagram which creates two types of relationship which one to one relationship and many to much relationship. The entities in this case are mobile in stock entity , order details entity and supplier entity. The mobile in stock entity creates many to many relationship with supplier entity due to the fact that one supplier can supply many items as well as many suppliers can supply one type of mobile. The mobile in stock entity also creates many to many relationship with order details because one order may contain many mobiles while one type of mobile can be ordered from many suppliers. One to one relationship emerges from order details entity and supplier entity since one order is given to one supplier which he supplies. The normalized tables for the relationship are as shown below. Query Transactions Test Data: Supplier_Details Supplier_Id mobile_Code Supplier_Name Address City Phone vane001 sumsung001 vane No. 50 westminister. london 55555 John002 nokiaSn001 John Westminiter London 6579 Dav401 sumsungSn001 David Liverpool. Liverpool 5589 Dav401 LG001 David Liverpool. Liverpool 4579 Lind402 NOKIAh001 Lindah No.6 John St. Newcastle 3258 Lind402 LG001 Lindah No.6 John St. Newcastle 9999 Geo403 SumsungSn001 Geofrey No.8 Liverpool. Liverpool 36978 Geo403 SumsungTh001 Geofrey No.8 Liverpool. Liverpool 1324 Glad404 NokiaSn001 Gladys No.7 Liverpool. Liverpool 12589 Glad404 NokiaTh001 Gladys No.7 Liverpool. Liverpool 5697 Test Data: Order_Details Order_Id mobile_Code Supplier_ID Quantity_Ordered Order_Date Ord001 sumsungTh001 vane001 185 6/3/2013 Ord002 sumsungSn001 John404 275 6/3/2013 Ord003 nokiaSn001 Lindah403 380 6/3/2013 Ord004 LG001 Geofrey401 200 6/3/2013 Ord004 NokiaTh001 Geofrey401 210 6/3/2013 Snapshots The name of the database is inventory control and there are three tables in this database, they are, mobile_in_stock table, supplier details, order_details as shown in the following screen. The mobile_in_stock table has mobile_code as the primary key and the supplier_details table has combination of mobile_code and supplier_code as primary keys. The order_details table has a combination of order_id, item_code and supplier_code as primary keys. All these tables are linked to each other foreign reference keys shown in the database normalization diagrams in the previous sections. -DDL Scripts: DML Script: In the file -Application: In the file attached Description of database design Logical Design:- The logical design will deal with providing textual descriptions followed by a high level functional design and Graphical User Interface Design. Furthermore, such a GUI design will include logical design for screens, forms, and reports. Physical Design- The physical design will show the design of screens and reports for specific pages. Furthermore it would also show how normalization of data for the stock inventory database will be done. Different approaches to database design. The different approaches to database design are: Top-down approach, and Bottom-up approach Top-down approach Logical data models adopt this database design methodology. Models following this methodology follow an abstract way of gathering information from subject matter experts (SME). Any system designed by using a logical data model may not implement all the aspects of the logical model. However, the top-down approach serves as the reference point for these systems. Bottom-up approach- This database design approach is used mostly in reengineering efforts. The models that are based on this methodology start with the existing data structure forms, fields and reports. This is an application specific and an incomplete design methodology. The models that are based on this methodology are known as physical data models. Setup of a data management system on a site Setting up a database at any given site can be done using different models of architectures. Therefore the architecture in question is a decision of the specific organization that is concerned.The database system and other components of the software encompass nodes, internet protocol addresses, and other components. The system should be connected, through a well organized network, to the response team in order to interface between the customers, and the action and response team. This is done by synchronizing the two, that is, the site and the standby location. The main objective of such software is to provide the feedback communication between a whole system in a site, no matter how large, and to provide alarms incase of any ‘site failure’ that would trigger a disaster. An example of such a system is “Oracle Collaboration Suite Database”, a database management system and “OracleAS Metadata Repository”, a complimenting database system that acts as a server for the site, or rather customers. The operation of such a system encompasses ‘switchover, failover, patching, deployment application and a disaster recovery manager (DR)’ (Helbing 2005, p.56). These systems, a development of the Oracle Association has been tested and proven to work. Read More