deliverable 1

IS 3063 Term Project Deliverable 1 Template

Group Number:

Your Name:

Your email:

Note that in the first deliverable, each student should independently come up with a business problem/ERD with section 1, 3, 4 outlined below completed and exchange this individual proposal with other group members. The group will jointly choose one to develop as a group (you may also combine different portions of multiple ERDs to form a new ERD for the group). Your individual ERD cannot be the same as any example covered in the book, i.e. your ERD may contain some entities covered in the book examples but more than 50% of your entities or relationships should be different from any examples covered in the book/assignments.

Group proposal section (every student should include this section):

1. Description of the business context and related data management problem(s)

Perfect Furniture is furniture company that manufactures and sells different kinds of furniture to customers nationwide. The company is currently using different isolated database management systems to store and process data, which causes a lot of inconsistencies and discrepancies across its different business units. It also makes it very difficult for the management to monitor the inventory status and strategically prioritize its production capabilities.

For the Term Project, we would like to create a database application to help Perfect furniture integrate its fragmented data management systems and provide more effective data management… (this is only an example, please elaborate based on your actual problem/solution)

2. The entities and the attributes (with description, constraint and data types)

Entity: Orderline (Associative Entity between Order and Product) :

Description: records each line in an order placed by a customer
Attribute	Description	Constraints	Data Type
OrderID	6-digit Numeric ID of each order	PK, FK1	CHAR(12)
ProductID	8-digit Numeric ID of the product that appears on each line of the order	PK, FK2	CHAR(8)
OrderedQuantity	Number of units purchased	Unique, Not Null	INTEGER
SalesPrice	The final sales price of the product	Not Null, >0	DECIMAL(4,2)
Backordered	Whether this product is backordered	Default value: No	BOOLEAN
DateShipped	Date this product is shipped to customer		DATE
LineMemo	Special notation for each order line		VARCHAR2(40)

Please use one table for each entity.

3. Business rules that define all the relationships among entities, the constraints, and the attribute domains (if any). Note that each relationship (i.e. each line in your ERD) requires 2 business rules (bi-directional), and each business rules should specify both the minimum and maximum cardinalities. The following is just an incomplete list for your reference.

Relationships:

a. Customer – Order: 1:M, a customer can place either no order or multiple orders, and order can be placed by one and only one customer.

b. Order – Order line: 1:M, each order may consist of multiple line (one line for each product ordered), and each order must belong to one and only one order.

c. Product - Orderline: 1:M, each product may appear in multiple lines in the Orderline table, each order line may contain one and only one product.

Constraint : a product cannot appear in in multiple lines of the same order.

….

4. ERD (can be modified in the next deliverable).

The ERD below is just an example. Your ERD must have all primary/foreign keys, relationships and constraints correctly specified. The proposed ERD should at least 6 entities with at least one of each of the following relationships: 1:1, 1:M, M:N (broken in 1:M), unary relationship , and supertype/subtypes with different overlapping/specialization constraints specified.

Below is another example for an ERD that includes the supertype/subtype example for your reference. You should have all entities in one ERD.

5. A summary of the functionality of the application (i.e. what the users can do with this application, future extension/integration with other systems etc.).

This new DBMS will allow users to keep track of the number of units available for each product and update the inventory when a sale occurs, it will also allow users to search the database to derive useful business information. For example, a user can search the DBMS to find out which product generates the most sales, which state has the most number of customers, what are the preferences of customers (i.e. the type of furniture ordered) from different states, etc. In the future, we can also connect this DBMS with a shipping tracking system to provide customers more timely update of the shipping status of the products they ordered…..

Continue on next page.

Individual proposal section (every student should include this section except those whose ERD was chosen by the group – indicate so if that’s the case ):

1. Description of the business context and related data management problem(s).

2. Business rules that define all the relationships among entities, the constraints, and the attribute domains (if any). Note that each relationship (i.e. each line in your ERD) requires 2 business rules (bi-directional), and each business rules should specify both the minimum and maximum cardinalities.

3. ERD.