Order Line Table By The Store Code example essay topic

Kudler Fine Foods Database Analysis POS 410 SQL for BusinessOverviewKudler Fines Foods is a rapidly growing small business located in Southern California. Founded in 1998, Kudler specializes in providing quality gourmet foods to a wide range of customers. When Kudler opened its first store back in 1998, Microsoft Access was chosen as the database platform used to track inventory, orders and customers. While this initially proved to be an adequate solution, it has since outgrown its usefulness. Now with three stores and a fourth due to open soon, a new database management system must be put into place. One of the most detrimental problems with the Microsoft Access database is that it is limited to a file size of only two gigabytes.

While this may initially seem like a large files size, it can rapidly grow and eventually reach this maximum size in a relatively short amount of time. This one drawback alone is reason enough to pursue a better solution. Another aspect of Microsoft Access that does not scale well is that it will not accommodate multiple users simultaneously. The database needs to be updated continuously by multiple users at each store. Running one instance of Microsoft SQL Server 2000 in a centralized location could easily handle the database needs of all of the store locations.

Connections to the database could be made via Virtual Private Network (VPN) connections over the Internet. Updates to the database can be further streamlined by developing and utilizing a web based interface. The best solution for Kudler Fine Foods would be to implement an enterprise level database solution. Microsoft SQL Server 2000 would be a great fit for the database needs of Kudler Fine Foods. Microsoft SQL Server 2000 will easily meet the current needs of Kudler Fine Foods and also meet their needs of the future as they continue their growth.

Existing Database Overview Currently the Kudler database contains nine tables. Each table is designed to support and facilitate the filling of orders placed by customers in the stores. Based upon the description of the tables that was posted on Kudler's intranet and our analysis, the tables may be described in the following manner: The Customer table contains demographic data for each of Kudler's customers. The data in this table is used to access the name and address of customers for order processing and for special mailings for anniversaries and / or birth dates, etc. Each record in the customer table contains a customer id field that is used to link the customer to the orders that they place. The Inventory table contains the components that make up an Item.

It is used for managing inventory and determining the availability of ingredients that go into prepared items, such as bakery products, etc. Each record in the inventory table is linked to the Item table by the item id and to the Supplier's table by the supplier id. The Item table stores information pertaining to products that may appear on an order. It describes goods that can be purchased from a Store.

The Item table is linked to the Inventory table by way of the item id and to the Order Line table by the item id field. The Order table is used to record information pertinent to each order placed with a given store. Each order will have one or more entries in the Order Line table associated with it. The Order table is linked to the Order Line table by the store code and the order id. It also links to the Customer table via the customer id field.

The Order Line table contains details on items appearing on an order. One or more order lines are associated with each order and each order line references an item being purchased with its description, quantity, price, etc. The Order Line table is linked with the Order table by the order id and the store code. It also links to the Item table by the item id. The Store table retains data for each store location, such as name, address, phone numbers, emergency contacts, etc. The Store table links with the Order table by the store code.

The Supplier table contains data concerning wholesalers, vendors and other suppliers from whom inventory is procured. These goods are used to prepare saleable items or are simply resold in their acquired form. The Supplier table is linked with the Inventory table by way of the supplier id. The purpose of the Tax table is to provide tax rates applicable to orders placed at a given store. The Tax table is linked with the Order table by way of the store code field.

The Tender table provides a means to record the methods of payment used for an order. For example, an order may be paid for partially by cash and partially by credit card. The Tender table is linked with the Order table by the store code and the order id. Recommendations for Improvements and / or Enhancements Based on the descriptions of the tables, it is recommended that the Kudler database undergo the following enhancements. The Tax table should only be linked to the Store table. The tax jurisdiction is based on the stores location and not on the order.

The store code should be removed from the Order Line table, since the table will function in the same manner it currently does without the store code in it. Information regarding the store could be linked back to the store by joining the Order Line table with the Order table and then using the store code in the Order table to match it with the Store table. The Tender table should also have the store code field removed from it for the same reason given for the Order Line table. There should be an additional table added to track the sales personnel. Currently there is a field called sales person id in the Order table, but there is no table to link this information with.

An Overview of the SQL Business Application The business application would be an online order processing system that would facilitate the orders from each of Kudler's locations, as well as an online ordering system for internet users to place orders without being in a store. Key features The key feature would be the integration of the stores ordering system and the online system. Each of these systems would store their data in the same database, which would allow for the tracking of both the customers orders by the store and orders placed online. This would facilitate the tracking of how an order changes based on where it is placed to determine if there is a difference between customers online order habits and their orders in a store. General description of input and output data The order would come into the system keyed on the location of the order. If it was an online order, it would have that information recorded in it.

It would then be moved to the transaction system, where the tender would be confirmed and the order parsed into the Order Line table. The order would then update the inventory system by parsing the item information into the particular inventory and recalculating the amount of inventory left based upon this order. Typical and special user needs The special needs of the system include the storage of historical data to aid in the tracking of customer orders throughout time. How SQL defines the database structure SQL commands are divided in two sub languages known as Data Definition Language (DDL) and Data Manipulation Language (DML). DDL is what's used to make and wipe out databases and the objects that were created with it.

DDL is also used to define the database structure. DML is used to put in, recover, and adjust the data contained within the database. DDL commands allow CREATE, USE, ALTER and DROP on the database management system (DBMS). Using CREATE allows a user to administer a lot of autonomous databases. The USE command is a simple command that tells the database that you want to start making some commands that will affect a different table. ALTER allows the user to make changes to the configuration of a table without deleting and making it again from scratch.

Finally, DROP is a command that removes a whole database from the system. DML uses commands such as INSERT, SELECT, UPDATE, and DELETE. INSERT is used to add data to present tables in the database. A more commonly used command is the SELECT command. The user will ask for specific data they are looking for in the database. The UPDATE command will change the data contained in a table.

It can be done one at a time or in a group. The DELETE command is a very good function and may work too well at times. If this command is to be used, it should specify what data needs to be deleted. If only a column in a table needs to be deleted, it should be specified as in the following example: DELETE FROM Employee WHERE employee id = 0001 Security Features and Benefits Microsoft SQL Server 2000 would also offer added security benefits. Its security model is comprised of five different components: SQL Server login, Database user, Guest user, Permissions, and Roles. This would allow Kudler Fine Foods to greatly customize the security settings that they would want in place for their newly updated database.

SQL Server login is compatible with Windows Authentication, which could allow an user to share the username and password with Windows NT and bypass an additional login for the database. Even password changes done in Windows NT would automatically change and update the SQL Server password. If separate logins are desired, they will also be possible with the Mixed Authentication offered through Microsoft SQL Server 2000. The security settings will be able to be further customized by the use of permissions and roles. A permission allows a user to do something within the database, whether it is accessing and manipulating data or creating new objects within the database. These settings can be individualized, or given to a group of users by roles.

Grouping the users into roles, either server or database roles, allows a way to assign permissions to multiple users at one time. For example, a salesperson would not have the same permissions as a manager probably would. Conclusion The changes and updates that we have outline throughout this paper would greatly improve and enhance Kudler's operations. This would be a much more effective, efficient database that would not only serve them well today, but allow for growth for tomorrow.

Bibliography

Developer. com - Retrieved on June 3, 2005, on the World Wide Web: web 721441 1.