Database Schema for a Company's Business and Processes - Case Study Example

Database Schema for a Company’s Business and Processes Requirement Analysis The determination of the database requirement is by conducting an interview with both the producers and the users of the data. The information gain the finds use in the speculation of a formal requirement specification. Thus, the specifics are the natural data relationships required in place and the database implementation platform. The requirements for the company’s businesses and processes are: The types of business processes in place The types of customers involved in the enterprise process The types of orders available in the business process The products description The sales person witnessing the transaction The date of each transaction Quantitative transaction carried out every day Logical design The logical design is a global schema with diagrams of the conceptual data model showing all the data relationships. Entity relationship is the technique towards its development. The ultimate is to transform the data model constructs into normalized tables or relations. The case of the company’s business and processes will have to entail: Conceptual data modeling The designer should be able to have a concept of the various data items to be an inclusion in the company’s database. In the case of the company’s business and processes, a variety of factors is under consideration. An inclusion would be the customer/ client, order type, product, and sales person. A relation is of the data items by the use of a conceptual data model diagram. The model consists of rectangular shapes to represent the database tables in use. The use of circles is a representation of the various table attributes, and the diamond finds use in drawing relationship links between the tables. View integration The view integration depicts the role of each data item present in the database. It is a simulation of the functions of each data item towards the completion of a company’s business transaction. It simulates each and every role in module parts. The rectangles, circles, and diamonds all find similarly applicable use as in the case of conceptual data modeling. Transformation of the data into SQL tables Anything rectangular is a table as the statements above suggest. Let the database name be myCompany, the various data fields in the relevant tables will have the following skeletons: Customer Number Name Telephone Products Product_id Name Buying_price Selling_price Salesperson Sales_name address phoneNo Order Order_number sale_name Number The identifiers Customers, Products, Salesperson, and Order are all part of the SQL tables with their relevant field names. For the creation of the customer table: CREATE TABLE customer ( Number VARCHAR (10) NOT NULL PRIMARY KEY, Name VARCHAR (50) NOT NULL, Telephone VARCHAR (50) NOT NULL ) For the creation of the Products table: CREATE TABLE Products ( Product_id VARCHAR (50) UNIQUE PRIMARY KEY NOT NULL, Name VARCHAR (50) NOT NULL, Buying_price DOUBLE NOT NULL, Selling_price DOUBLE NOT NULL ) For the creation of the Salesperson table: CREATE TABLE Supplier ( Sales_name VARCHAR (50) PRIMARY KEY NOT NULL, address VARCHAR (50) NOT NULL, PhoneNo INT ) For the creation of the order table: CREATE TABLE Order ( Order_number INT PRIMARY KEY NOT NULL, Sales_name VARCHAR (50) FOREIGN KEY, Number INT ) The primary key creates a unique identifier relationship between the different fields of the table. The primary key is compulsory in each table to represent a unique field. The Number, Product_id, Sales_name, and Order_number are all primary keys. The foreign key draws a relationship that will only work if the value of a relation R1 will match that of relation R2 with a foreign key. In the present case, we consider the field Sales_name in the in the table Salesperson and the field Sales_name in the table Order. The unique key also finds use in identifying a distinct key that should not be a match to any other field. By the use of the three keys, referential integrity is an achievement. Normalization When it comes to normalization, it is a technique of data organization in a database. The purposes of normalization are for the elimination of useless data and providing surety for logical data storage. The application of 3NF is where the tables non-prime attributes are a dependency on the primary key. When it comes to 3NF, the goal is to reduce the amount of duplicated data and to achieve data integrity. If we normalize the table products, CREATE TABLE Products ( Product_id VARCHAR (50) PRIMARY KEY NOT NULL, Name UNIQUE VARCHAR (50) NOT NULL, Buying_price DOUBLE NOT NULL, Selling_price DOUBLE NOT NULL ) We set the Name field as unique leaving the Product_id field as the primary key. Data integrity is an achievement as the uniqueness of the product will only be an identity by name and not by the Product_id functioning as both the primary and unique key. The company’s business and process needs a supplier and a project to be in motion at all times. The supplier also facilitates the name attribute together with the project attribute with relations being an attraction towards the supply, stocks, and needs. The part field is a representation of the products table with the order, supplier and customer falling under the project field. Data Flow Diagram The illustration of the data flow pictorial above depicts the flow of events from the root of the business process to its termination. The provider passes through the purchasing department as the consumer passes through the sales department. They both collide at the department of production management and control to the finish of the data production. All of them pass through various attributes like purchasing order, sales schedule, and purchase requisition to the production of useful data as a business process product. Some sample queries would look like the following: SELECT * FROM Supplier ORDER BY PhoneNO; SELECT * FROM Order WHERE Sales_name = “white_rice101”; The achievement of data integrity is often by the use of constraints or integrity rules. The specification of the rules is normally at the level of the database schema. The declaration of such constraints is towards the correct definition of the relational database. It is such that a null value cannot participate as an attribute of the primary key. When it comes to relations; say R2, with an inclusion of a foreign key that is a match for the primary key of R1 relation, it is then fair to say that some value of the foreign key should have some equality to the primary key. Another constraint would be the semantic integrity constraint that draws reference to meaning and correctness of data. For an attribute not to be null, a specification of the null constraints is a requirement. Finally, the unique constraint is a specification of a difference in attribute presentation. The definition of identifiers for a relation is by use of keys for the relational data. The use of the keys is for the constraints and rules of the database data. The essential of the constraints maintains data correctness and efficiency. The responsibility of the database management system is for the maintenance and verification of the database data and its consistency. Gantt chart Task/duration (weeks) 1 2 3 4 5 6 7 8 9 Task 1: feasibility study Task 2: Resource collection Task 3: Design Task 4: Implementation Task 5: Testing and maintenance Task 6: completing and report writing References Ramakrishnan R. and Gehrke J. (2003). Database Management Systems; McGraw-Hill, Boston, MA, Third ed. Stonebraker M. (1986). Inclusion of new types in relational database systems, ICDE, pp. 262–269. Silberschatz A. Korth H. F. and Sudarshan S. (2001). Database System Concepts; McGraw-Hill, Boston, MA, Fourth ed. Shah M. A., Madden S., Franklin M. J., and Hellerstein J. M. (2001). Java support for data-intensive systems: Experiences are building the telegraph data flow system. ACM SIGMOD Record, vol. 30, pp. 103–114. Read More