Project Deliverable 3: Database and Programming Design
Student’s Name
Institution
Date
Introduction
The main function of any business is to transform the data into information. The use of database warehouse and relational database has increased in recent times. It has gained recognition has the standard for the operation of most organizations. As stated by Laher, Surace, Grillmair, Ofek, and Levitan (2014) a quality database design makes the flow of business data to be seamless. However, the database schema is the beginning of the relation database and therefore, it is important for a business to have a quality database schema for it to have seamless flow of information in the company. The database schema defines the tables, relationship, views, fields, indexes and other elements of database. It is important for a company to create a quality schema for it realizes business, workflow and processes. Since the company is an internet based company the database should focus on designed towards the warehousing.
It is one of the viable strategies, which would give the company the competitive advantage in the market. It is therefore, evidence that database schema is a key component required for efficient database development and management. It is important to ensure that it is properly build to support an organization’s business goals and objectives. The report, therefore, presents a database schema, which presents the business idea of the company. It also illustrates the relationship entity and the rationale for establishing the kind of business schema. The table and other elements, which support the schema, relationship and the E-R Diagram, will also be provided as well to show the process of creating database for efficient and seamless flow of data. The company has variety of data and most of its data are online oriented and therefore, it is requires for an organization to have an efficient data, which can reflect the business nature of the company. It is therefore, important to look at the database Schema, relationship of the data and the tables and other fields, which form the database.
Database Schema
Enterprise data is comprehensive and can be used in-house and external system. Analysis of the company, established that there is a need to analyze the data across different organizational system by time, location and channel. The integration of data is needed here so that a company can be able to store and organize data in a single place. It would help for the business to cut time and the lengthy processes, which is involved in the creation and generation of business reports. Previously, it used to take businesses days and months to generate various reports. Time has changed and with advanced technology, reports and other important information are generate within seconds. The generation of reports involves series of steps that utilized the exact data from a source the sort the data and merges. Data warehousing therefore, serves the purpose of several platforms to make the data reliable and also facilitate easy data integration. According to Laher, Surace, Grillmair, Ofek, and Levitan (2014), the data which are stored in the data warehouse is completely integrated, no-variant, and also subject oriented. The data warehousing help the organization to get full view of the organization and its performance as well. However, by creating a database management system to be sued for data warehouse, the organization can be able to increase its level or operations and efficiency by using the data collected through internet enterprise resource planning. A lot of companies have integrated the EPR their major system with EPR to improve its performance in the market.
The efficient management of the organization requires understanding on how data flow in the organization. According to Chung and Paredes (2015), a successful deployment of ERP by using data warehousing is likely to help in reducing the production, shipping, and inventory expenses and therefore, it gives the company a competitive advantage in the market. The data warehouse allows the company to analyze some of its Key Performance Indicators (KIPS), which are used to measure the business track performance against the business objectives. KIP usually applied to measure a greater length of the organization performance and therefore, it informs the management whether the implementation of the system has deteriorated the performance or improved the business performance. And therefore, with efficient data management due to quality database, the performance of an organization is likely to improve. The essential element of quality of database is the database schema. Therefore, the existence, efficiency and better performance of a database depend on the nature of the database. The database schema is based on the nature of the business as well and therefore, the designing and implementation of database schema requires understanding of the nature of business being done by a company.
It is also important to note that large scale environmental problem is the difficult task to address. Business applies the use of database to address the management problem. According to Cox (2014), the data application in business impact performance hence most companies usually realize a huge profit. The type of data schema depends with the business structure and management level. And therefore, the data schema required for this business is based on the data flow in the organization. The database schema is based on the administration knowledge. It also illustrates how a real world entitles is structured into the system. The business is running a warehouse and therefore, the data schema should contain features, which would allow employees to enter data and process easily in the system.
Sales ID
Client’s Name
Phone No
SalesOrderDetails
Product Serial
Address
CarrierTrackingNumber
mber
Name
Product ID
Order Quantity
Number
Name
Diagram 1: Database Schema
Rational for the structure
The database schema in the diagram one (1), has four (4) sections, the Sales ID, Sales order Details Carrier tracking Number and Order Quantity. The nature of business being conducted by the organization is an online base and therefore, clients gives order online through its website, which is integrated with the ERP and therefore, it must have a way of tracking all its products to ensure that the products reaches the clients. Therefore, the table Carrier tracking ID is required since every order is issued with a tracking ID. Therefore, the kind of data, which will be entered into the system, includes sales details; cost of goods, selling price, data of submission and the clients also come from different regions. These must be captured by the system for easy processing and therefore, the data schema reflects what the database shall contain. The business would enter information related to sales, products, supplier or customers contacts, where they can from and data and month when the product was brought to the store (Laher, Surace, Grillmair, Ofek, & Levitan, 2014). This information would be captured best using the above data schema because of nature. The data nature contains four tables’ calendar, Fact table, customer and Bind Style. The Sales ID would contains information related to dates especially when the product was sold out and also when the product was product into the store. The information would be helpful in analysis sales and reporting the stocking as well. Since the database will capture when the products was brought into the store and the time it was sold. The information would be helpful in understating the sales report, stock report, and also purchase details of every client.
Entity relationship
It is important for the database to have graphic representation to indicate the functionality of the database. The data is required to have relationship for it to function well and therefore, it is important for the data to have the method to ensure that the system is protected. The entity relationship is the kind of relationship where graphical representation is used to illustrate the relationship between the tables or information. It describes the kind of information, which is stored (Rüegg, et al., 2018). The relationship is how the data is stored between tables, fields or entities. The data must have a proper link for it to work well and give positive feedback when processing queries or report. The business requires the relationship of one-to- many. This means that one table is linked to several other tables of the data. It means that the data entered at the warehouse of the company is associated with customer, and other data, which are closely related to the customer (Laher, Surace, Grillmair, Ofek, & Levitan, 2014). It is established this way to ensure that there is efficiently in data process across several departments. The image below shows an example of how entity relationship operates to ensure that it operates well across the company.
The entity relationship will be established by linking SALES ID, SALES ORDER QUANTITY, AND CARRIER TRACKING NUMNBER tables together. This will ensure that there is proficient communication within the data level and therefore, the processing would be easy and faster. It ensures that there is consistency and accuracy of the data, which is being processed. The diagram is therefore, just as example of how the entity relationship works.
Data Flow Diagram
The data flow indicates how the data flow in the organization. It begins from where the data starts and the end process, which could be report generation or queries and even storage of the data. It describes the process, which involve in the system for the transfer of data from the entry point to the exit (Chung & Paredes, 2015). The exit of the data processing could be either a report being generated or the data is stored for future use. It is described based on the logical data flow, which is required to established functionality of the system. The DFD is needed for processing, storage, manipulating, and the distribution of data to the system. It communicates with the system to ensure that the data flows from the beginning to the end. It is therefore, important to have a proper flow of data for the system to work well. And for this to be obtained the structure of the system must be well thought-out and clear. The data flow diagram is therefore, every essential for the operations of the database.
Database tables with appropriate field-naming conventions
The database table is an essential part of the database schema. The table provides the layout of the database and the way the data intends to interact or the relationship of the database with key elements of the system. A quality table for database must have a key and a better referential integrity for it to function well. According to Rüegg, et al. (2018) the key and referential integrity determines the performance of the database and therefore, it is required that the key and referential integrity should be properly defined so that the data can be understand and function as well. However, the table has SalesOrderID, SalesOrderDetails, OrderQuantity and ProductKey as its table. The primary key of the table is defined at the SalesOrderID and salesorderDetail columns. The primary key is located at the fact table in the first field of the table, which is the SalesOrderID. The SalesOrderDetails is used to identify every customer in the database and it is also describe the relationship between the tables to ensure that the database could function well. When customers come to the store, each customer is assigned a unique identification number when the product is processed to the warehouse. The customer’s products, sales, cost and the amount owned by the company could be established using he customer key and therefore, the primary key is the SalesOrderID and SalesOrderDetailsID.
The rationale for using the table is based on the demand of the company. The company is an online based company dealing with various products online. It has several sales daily and these sales are online based. The clients must be identified based on the sales items. The ID therefore, is issued to customers based on the sales. Therefore, each customer has a sales ID in the system. The sales ID are issued by the system when a client put an order. Therefore, the sales ID is the unique identification number, which is issued to all customers.
The Referential Integrity is the accuracy and consistency of the data. The referential integrity would be achieved by ensuring that there is a foreign key which is associated with one of the tables (Chung & Paredes, 2015). The foreign key can be established at the fact table to ensure that the four tables are connected. The parent table must have foreign key for success of complete processing of data when entered the system. Without foreign key the data can easily get lost and the incomplete information is brought back. From the database, the users would not be able to generate reports and queries cannot be sent as well. The referential key is therefore, important and it must be placed on the database to ensure that the system is having a proper relationship. Without it the database will not be able to communicate using tables and therefore, company will not be able to generate reports.
SalesOrderID
SalesOrderDetails
CarrierTrackingNumber
OrderQuantity
ProductKey
3421
1
421-351
3
78512
3422
2
421-352
2
78513
3423
3
421-353
3
78514
3424
4
421-354
1
78515
3425
5
421-355
5
78516
3426
6
421-356
6
78517
3427
7
421-357
7
78518
3428
8
421-358
8
78519
3429
9
421-359
15
78520
3430
10
421-360
10
78521
3431
11
421-361
11
78522
3432
12
421-362
13
78523
3433
13
421-363
13
78524
3434
14
421-364
14
78525
3435
15
421-365
15
78526
3436
16
421-366
114
78527
3437
17
421-367
17
78528
3438
18
421-368
14
78529
3439
19
421-369
24
78530
Diagram 4: Database tables with appropriate field-naming conventions
The security of the data base
Security policy is an important measure which is taken to prevent the access to the system. The best way to protect a system is derived a policy which can protect the system from unauthorized access. An organization can derive policies regarding login and accessibility of the system. The security policy can be including login password strength, active status and how clients can be able to log to the system and stay active after certain duration. The network administrator must come up with security policy to prevent people from login to the system. The policy would be able to prevent the system from external attack such as hackers and therefore, it is important to ensure that there is a proper security system based on the policy. For instance, the system can be set to require string password such word and letters, and at least eight. This is to make sure that nobody can easily guess the password and use to access a system.
Recommendation
For efficient operations of the company it is important to ensure that the database is designed based on the operations and the market demand of the company. It is therefore, recommended for a company to conduct research on the market and serious analysis of the company operations for efficient understanding of the operations and therefore, it would help in realignment of the company database to the demand of the customers and other stakeholders. Network security is an important factor for enterprises. It is therefore, important to ensure that the system is protected using policies, hardware and software to safeguard the integrity of the data. Security implementation should be done at the entry point and within the system to ensure that any network is protected from any unauthorized access. It is therefore recommended for enterprises to deploy some of the latest security system which require the use of bio for access to a building or a system. The firewall should e deployed as well at the entry and exists to make sure that the system is protected. In real world network security involve prevention detention and response. If the prevention techniques were perfect there would be no need to require detection and prevention. There are so many techniques, which are used to protect the system. Besides firewall, there are encryptions, passwords which are used to provide proper security to the system. Security of the network can be achieved only through proper installation of firewalls, which is at the entry and exit point of any system
Bibliography
BIBLIOGRAPHY Chung, K. S., & Paredes, C. W. (2015). Towards a Social Networks Model for Online Learning & Performance. International Forum of Educational Technology & Society , 2-35.
Laher, R. R., Surace, J., Grillmair, C. J., Ofek, E. O., & Levitan, D. (2014). IPAC Image Processing and Data Archiving for the Palomar Transient Factory. Publications of the Astronomical Society of the Pacific , 2-35.
Rüegg, J., Gries, C., Bond-Lamberty, B., Bowen, G. J., Felzer, B. S., McIntyre, N. E., et al. (2018). Completing the data life cycle: using information management in macrosystems ecology research. JOURNAL ARTICLE , 2-35.
