DBMS allows users to share the data and query, access, and update the stored data.

INSTRUCTIONS TO CANDIDATES

ANSWER ALL QUESTIONS

The DBMS allows users to share the data and query, access, and update the stored data. These are some of the COSC3380 data models:

Customers are a person or an organization that buys or may potentially buy products from COSC3380.

An order is the purchase of one or more products by a customer.

A product is an item that COSC3380 makes and sells.

An order line is details about each product sold on a particular customer order (such as quantity and price).

 

Each customer can place any number of orders. Conversely, each order is placed by exactly one customer. Each order contains any number of order lines. Conversely, each order line is contained in exactly one order.

Each product has any number of order lines. Conversely, each order line is for exactly one product.

 

From a study of the business processes at COSC3380, the following data needs to be stored in the database:

 

• COSC3380 sells a number of different furniture products. These products are grouped into several product lines. The identifier for a product is Product ID, whereas the identifier for a product line is Product Line ID. COSC3380 identified the following additional attributes for product: Product Nam, Product Finish, Product Standard Price, and photo. Another attribute for product line is Product Line Name. A product line may group any number of products but must group at least one product. Each product must belong to exactly one product line.

 

• Customers submit orders for products. The identifier for an order is Order ID, and another attribute is Order Date. A customer may submit any number of orders but need not submit any orders. Each order is submitted by exactly one customer. The identifier for a customer is Customer ID. Other attributes include Customer Name, Customer Address, Customer City, Customer State, Customer Postal Code, Customer Email, Customer Username, and Customer Password (to store the login information).

 

• A given customer order must request at least one product and only one product per order line item. Any product sold by COSC3380 may not appear on any order line item or may appear on one or more order line items. An attribute associated with each order line item is Ordered Quantity.

 

• COSC3380 has established sales territories for its customers. Each customer may do business in any number of these sales territories or may not do business in any territory. A sales territory has one to many customers. The identifier for a sales territory is Territory ID and an attribute is Territory Name.

 

• COSC3380 has several Sales Persons. The identifier for a Sales Person is Sales Person ID. Other attributes include Sales Person Name, Sales Person Telephone, Sales Person Email, Sales Person Username, and Sales Person Password (to store the login information).

A Sales Person serves exactly one sales territory. Each sales territory is served by one or more Sales Persons.

COSC3380 Product Manager Support:

This is a list of attributes a Product Manager wants access to:

Customer         Customer Identifier

                                    Customer Name

                                    Customer Zip Code 

Product           Product Identifier

Product Name

Product Finish

Product Price

Product Line Name

 

Product Line   Product Line Name

Order               Order Number

Order Placement Date

Customer Identifier

Ordered Product (Order Line)            Order Number            

Product Identifier

Order Quantity

These are the reports that a Product Manager will need so far:

 

• Product Line Sales Comparison Report (a list of each of the products in the COSC3380 product line showing each product’s total sales to date.

• Total Value for Products Report (what is the total value of orders placed for each furniture product).

COSC3380 Sales Support

These are the reports that Sales People at COSC3380 will need so far:

• Data for Customer Report (for each customer in his territory produce the list of products and their prices).

COSC3380 Inventory Manager Support

These are the reports that Inventory Manager at COSC3380 will need so far:

• Customer by States Shipment Report (Count the number of customers with addresses in each state to which we ship).

The Inventory Manager needs to know (the action of being informed) when an inventory item’s standard price is updated in the Product table (the event). After creating a new table, PriceUpdates, write a trigger StandardPriceUpdate that enters each product name when it is updated, the date that the change was made, the old price and the new standard price that was entered.

Attachments:

Related Questions

. Introgramming & Unix Fall 2018, CRN 44882, Oakland University Homework Assignment 6 - Using Arrays and Functions in C

DescriptionIn this final assignment, the students will demonstrate their ability to apply two ma

. The standard path finding involves finding the (shortest) path from an origin to a destination, typically on a map. This is an

Path finding involves finding a path from A to B. Typically we want the path to have certain properties,such as being the shortest or to avoid going t

. Develop a program to emulate a purchase transaction at a retail store. This program will have two classes, a LineItem class and a Transaction class. The LineItem class will represent an individual

Develop a program to emulate a purchase transaction at a retail store. Thisprogram will have two classes, a LineItem class and a Transaction class. Th

. SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 1 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of

. Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of