Wine Database System
SQL Relational Database

Introduction

JK Wines takes customer service seriously. We feel it is our duty to deliver the highest quality wines while providing the highest customer satisfaction. Our current customer feedback has unveiled a plethora of problems in our in operations. The wine is great, but the customer service is not. In order to turn the company into a respected business we need to offer more to the customer then great wine and good prices. The first step in re-organizing the company is to develop a database that will not only raise employee motivation and efficiency, but will provide a better marketplace for customers to take advantage of. If our customers are happy we are happy, and if tasks can be completed more efficiently and easier, the happier everyone will be.

When customers have a concern or question about an order, nobody wants to dig through stacks and stacks of paper to find the right invoice. When employees want to find out the email address of a customer, the task is not easy. These simple examples are just a drop in the bucket compared to the amount of problems a database will solve. It is a no-brainer that an e-commerce company such as JK Wines needs to implement a custom database that will fulfill the needs of customers, suppliers and employees.

Problem Scope

Our database addresses the problem of maintaining data integrity. It will provide easy storage of data in one centralized location. It will minimize the space required, and lower storage and hardware costs. It will facilitate data integrity, in that there will only be one set of data, instead of duplicate data spread over multiple databases. Customers will not have to worry about ordering something that is out of stock, because the database will constantly update our website with current inventory. It will provide a customers and administrators a better way to find the information they need.

Customer satisfaction will be the main goal of our database. They will have the ability to sign up for a membership to one of the wine clubs or for a basic purchasing account. After signing up they will have the capability to purchase wine from our vast collection of fine Californian wines. Being able to find, sort, and organize data efficiently will also satisfy the administrators of the database. Kimi and Jeff will be the only two with access to manipulate the database and have access to confidential information. There will be quite a bit of data in the database, so efficient organization is crucial.

Our database will hopefully be a large collection of data containing customers, orders, products, and suppliers. The customer entity will store shipping addresses, contact info, membership info, and records of customer comments and questions. The order entity will contain all of the order records. The products entity will store records of all the wine we currently have in stock including their prices, varietal, vintage, appellation, alcohol content, date we put the wine up for sale, customer ratings, and suggestions for food to enjoy with each wine. Top selling wine will also be included in the products entity, which will be a derived attribute of orders. Our supplier entity will contain all of our suppliers contact info, purchase prices, purchase date, shipping costs and miscellaneous info on suppliers.

Constraints on the data in the database will be strictly enforced and up to management to decide. Some constraints may include important laws such as maximum sales of alcohol to each customer. Legal drinking age will be a constraint. The constraints are not limited to what is mentioned above, but will be at the discretion of management to add or change.

The customer will sign up for an account and hopefully purchase some wine. Before finishing the purchase the database will check to make sure there is current stock of what they are ordering. If there is, the inventory will automatically be updated to show the new stock levels. Another transaction will occur when we enter data about new suppliers and every time we purchase something from a supplier. When we have new inventory we will have to update our database to provide info on the new products. We hope to set up automatic alerts to when a wine has been in stock too long, and must be put on sale.
There will be much sought after information at our finger tips once the database is implemented. As administrators, we would like to have reports of sales for each day, new customer reports, inventory that is out of stock, and new customer comments and questions. We would also like to know which age group, location and types of wine our frequent customers are purchasing for marketing campaigns. Some of the most successful companies on the Internet aren’t just lucky, they have strategic marketing campaigns that they employ to target their markets efficiently with high returns on their investments. The queries that customers would like to be able to run would be searches on our website to choose wine they would like to purchase. These would include searches for vintage, price, varietal, what is on sale, recommendations, and best sellers. In almost every part of the transaction online, the customer will be accessing our database.

This project will allow JK Wines us to save money by saving time and being more efficient. If we do it right the first time we won’t have to spend time and money developing a database later, or fixing problems that occur in the future. Therefore the competitive advantage lies mostly in terms of employee and customer satisfaction. With a great database our customers will notice the difference and we will have a higher likely of repeat customers. And employees will be more satisfied, therefore be more motivated to help the company succeed. Also If we decided to not only selling our products on line, but also selling them in the stores, we will already have a functioning database we can use. We will be able to use reliable and useful data to help our company start smoothly. Our competition will spend more time and money keeping track of data while we are gaining market share.

ER Diagram

The entities of the database are customers, orders, products, shipment and suppliers. Order line is the only associative entity. An entity is a person, place, object, event or concept in the user environment about which the organization wishes to maintain data. The customers and suppliers entities are the two databases of people involved in any given transaction. The orders and shipment entities are events that occur along the process of making a sale. The products entity is a table that will keep track of the wine inventory. The order line is an associative entity because it relates orders and products but also has its own attributes.

The attributes of this diagram are circled. Each attribute represents a property or characteristic of an entity. The underlined attributes are the identifying attributes that will be used to uniquely identify each entity. For example, each customer will be given a customer I.D. that will never change, and will be used to uniquely identify that customer in our database. The other attributes of the customer entity are name, address, date of birth, email address and password. Each of these attributes will allow us to keep track of our customers, and enables us to identify them. We will be able to store more information about our customers in general. The date of birth attribute is important to obtain because of drinking age regulations. The password entity will be chosen by the customer in order for them to log into their online account. This will give them access to customer only web pages and also speed up their checkout process when purchasing a product.

The order entity database will contain a unique order I.D. and the order date. The order line entity will only have one attribute which is quantity. When a customer makes a purchase the quantity is kept in the order line table and the date and order id will be part of the orders table. The information about the products inventory will be stored in the product database.

The attributes associated with the product entity are price, alcohol content, appellation, vintage, description, varietal, and name. The customer rating attribute is a multi-valued attribute that will contain all the customer ratings for each wine. The identifying attribute will be the bar code of each wine that will uniquely identify the wines from each other. Each wine has an appellation attribute which specifies where the grapes are grown. The vintage attribute specifies the year that the grapes were grown. The varietal attribute is the type of grape(s) used to make the wine. And the alcohol percentage is simply the alcohol content of each wine.

The next entity is shipment. This entity contains the shipment I.D. and shipment date. These pertain to shipments of products from suppliers to our warehouse. The shipment I.D. is the unique identifier for this entity. The next entity is suppliers. The supplier entity is the last entity and it has four attributes. They are supplier I.D., which is the unique identifier, name, address, and email address.

The supplier has a relationship with shipment. The relationship between these two entities indicates the transactions between them. The shipment cardinality is mandatory one, and the suppliers entity has a cardinality of optional many. In other words, each shipment can be sent by only one supplier, and suppliers can send as many shipments as needed. Another relationship in our ER diagram is between shipment and products. This relationship is named include, therefore we can rephrase this relationship in English as follows; a shipment includes at least one product, and any number of products can be included in a shipment. Finally, the last relationship is between the orders and customers entities, and is called place. A customer can place any number of orders, but each order can only be from one customer.

Next > 2 3

Topics

Tech Business Computer Engineering Electrical Engineering Selected Topics

Wine Database System SQL Relational Database

Wine Database System
SQL Relational Database