Database Structure

PART 1

Below is a database structure and contents of a Sale Company. Your task is divided into following components

? Briefly give description of the database structure of the company in not more than 500 words

Database structure shows that the product of the company, the clients, suppliers. At the same time, it also provides some about the relationship between them.

Firstly, show some details of the clients, providing the high-quality service for customers. This table provide code, the user name, customer first, client, client prefix and customer balance. This information can help the company's sales company management system and easy access to the consumer. The code from the customer and sales company know which areas of the best product sales.

So they will make a proper marketing plan. Customers can help companies understand relevant code of the relationship between customers and invoice the table.

Secondly, database connections with the invoice. This table shows the code, invoice number, customer the invoice date. Sales company from this list can get the information they want. The invoice date to display when shopping, they can sell most of the product.

The invoice line on invoice number, invoice number, product code, invoices, unit price. Sales company will know exactly how many customers to purchase products and customers to buy the product. From this list, sales company can know what they should be more attention. This product code elements can help companies find the relationship of the invoice line table and product table.

Database is about products, including some information, such as product code, product description, P_INDATE, P_ONHAND, P_MIN product price and discount, vendor product code. This table provide detailed product. It can tell what kind of company products sell the best, what kind of price applies to customers. In this product suppliers table, the table of the code of the contact.

Finally, the seller in sales company database structure system, presents the seller, the seller code name, suppliers, contact telephone number code, suppliers, V_STAT and V_ORDER. The information provided in this form for suppliers of sales company. Company sales personnel may know the product sales staff and most of the best.

Database structure sales company provides a sales company.

It contains a number of relevant company and its clients.

? Analyze the table structure on the basis of data inconsistency and data anomalies. Write a brief of your analysis in not more than 300 words.

From the customer table the 10014 and 10017 customers ID have null. From the product table the 23114-AA and RVC23DRT codes have null, this table shows nearly 20 different kinds product about the company. However, there are only 10 products can be contacted to the customers. We can not find any point about the other product expect the 10 products. It means that the half information about the product table is useless. It is one of the inconsistencies of the company's database structure. From the line table there is no ID, so we hardly to find out the information. Form the vendor table, Suppliers, vendors, contact telephone number code prefix, suppliers, V_STAT and V_ORDER. For the company, they can know, providing services, customer. But this information displayed in contact makes us confuse. Because some customer's name is the same, some names of customers, we can even find a customer list. We can't has a clear understanding of the information we need from the table .

PART 2

Using the table structure in the database, create a Crow Foot ER model for the company's database.

PART3

(SQL Query)The databasestructure of theSaleCompany1. Generatea listing if all purchases made by the customers, using the output shown below.

2. Using the output attribute shown in the above problem, generate a listing of customer purchases, including the subtotals for each invoice line numbers.

3. Now modify the query in above problem to create the following output.

4. Now modify your SQL query to match the output below.

1. Generate a listing of all purchases made by the customers, using the output shown below.

SELECT

i.cus_code as cus_code,

i.inv_number as inv_number,

i.inv_date as inv_date,