Designing A Point Of Sale Relational Database Computer Science Essay

Today in the food-service industry, a point of sale (POS) system is the most widely used technology. It allows retailers to operate every facet of their business. Generally speaking, POS systems are electronic systems which allow businesses to retain and analyze a wide variety of inventory and transaction data on a continuous basis.

Much like a computer, the POS system contains two basic parts: the touch screen and the software that runs the system. It allows cashiers to key in customer’s orders during the process. Moreover, according to Chen and Lee (2010), “The point of sale (POS) system provides the information analysis ability and can be used to analyze consumers’ purchasing behavior as well as forecast needs.”

As it shows above, a POS system is quite necessary for retail business, especially Panda restaurant who is intending to franchise more nationwide stores. So installing a POS system and designing a proper database is at the top of its agenda.

The modern database (a hierarchical model) appeared in the mid-1960s. According to Rob and Coronel (2009), “A data model is a relatively simple representation, usually graphical, of more complex real-world data structures” (p. 31). Also in the mid 1960s, another type of database model, network model, appeared. Network structure was developed to represent the more complex data relationships that hierarchical structure cannot model.

Both hierarchical and network models are called flat-file database to distinguish from relational database. In June, 1970, E.F. Codd proposed the relational model. It was an announcement of the arrival of the relational database. Compared to former database approaches, the relational database model hid complexities from end-users. Moreover, relational database model had flexible and standard query language (SQL).

In 1976, Peter Chen presented the Entity-Relationship (ER) model. ER models are represented in an entity relationship diagram (ERD), which could be used to depict enterprise data modeling. Enterprise Data Modeling is “a common consistent view and understanding of data elements and their relationships across the enterprise” (“Enterprise”, Para. 2). This type of data modeling provides access to information scattered throughout an enterprise under the control of different divisions or departments with different databases and data models.

In the mid-1980s, Object-oriented (OO) and extended-relational data models (ERDM) showed up. Although the OO and ERDM focus on different fields, their concepts and procedures are merging to facilitate another Internet burgeoning.

2. Technical description of a point of sale relational database.

Fundamentals of relational databases

Tables, primary keys and foreign keys

Tables in the relational model are used to represent “things” in the real world. They constitute rows and columns. Each table should represent only one thing. These things (or entities) can be real-world objects or events. For instance, a real-world object in X restaurant might be a customer, an inventory item, or a supplier. Examples of events could be telephone-orders, purchases from vendors or credit card transactions.

The relational model requires that each row in a table be unique. If duplicate rows are allowed in a table, then there is no way to uniquely address a given row via programming. This will create all kinds of ambiguities and problems that should be avoided. To guarantee uniqueness for a table, a primary key is designated. As Rob and Coronel (2009) defined, a primary key is “an identifier composed of one or more attributes that uniquely identifies a row” (p.671). Each table can have only one primary key, even though it can be composed of two or more attributes.

Although primary keys are important to individual tables, they would be of little use if databases consist of only independent and unrelated tables. But at the time the database administrator (DBA) starts to create relationships that join together multiple tables, primary keys become essential. Another important term-foreign key-emerges along with this process. A foreign key in a table used to reference a primary key in another table.

3. Comparisons between flat-file database and relational database.

The flat-file database is ideal for small amounts of data that needs to be human readable or edited by hand. Essentially it is a set of strings in one or more files that can be parsed to get the information they store. The flat-file database is great for storing simple lists and data values. But it can get complicated when a DBA try to replicate more complex data structures. That is not impossible to store complex data in a flat-file database. The fact is that doing so can be more costly in time and processing power compared to a relational database. The methods used for storing the more complex data types are also likely to render the file unreadable and un-editable to anyone looking after the database.

The typical flat-file database is split up using a common delimiter. If the data is simple enough, this could be a comma, but more complex strings are usually split up using tabs, new lines, or a combination of characters not likely to be found in the record itself.

One of the main problems with using flat files for even a semi-active database is the fact that it is very prone to corruption. There is no inherent locking mechanism that detects when a file is being used or modified, and so this has to be done on the script level. Even if care is taken to lock and unlock the file on each access, a busy script can cause a “race condition” and it is possible for a file to be wiped clean by two or more processes that are fighting for the lock; the timing of file locks will become more and more important as a site gets busy.

The relational databases like Oracle, MySQL, Microsoft SQL Server, and so on have a much more logical structures to store data. Tables can be used to represent real world objects. For example, a table called VENDOR could have the columns vendor name, vendor address, and vendor telephone number. These columns describe the details of each vendor and each row in the table represents a new vendor.

The “relation” comes from the fact that the tables can be linked to each other. For example certain products in an inventory table could be cross-referenced with the product table to provide more information about it. These kinds of relations can be quite complex in nature and would be hard to replicate in the standard flat-file format.

4. Recommendations.

Retail businesses, X restaurant included, require flexible and customizable reporting capabilities of their database systems to analyze sales and determine which products (entries) are profitable and which are not. A well designed point of sale relational database could provide perfect solutions for those requirements. One major advantage of the relational model is that there should be no duplication of any data. No duplication helps to maintain database integrity; on the other hand, it saves much more time to process. Also, a relational database is flexible to retrieve, sort, and edit data through different applications. For example, X restaurant can access the database and produce a variety of sales reports by date, item, server and so on.

Based on all the discussion above, a point of sale relational database system is the right solution for X restaurant to manage efficiently and even generate profit.

Order Now