IST 302 Database Project

Database Project – Instructions

This semester’s project involves the design of a database system for a Sales Transaction application.  In these instructions we refer to the data that would result from execution of a business query (BQ) as a Business View (BV). Note that a BV could be a virtual view or a materialized view.

Each Project Team will be required to do the following:

1. Business Understanding 1 (Be prepared to discuss this on 11/15/2017)
2. Do some background reading on Sales Transactions applications

A sale is an exchange of a product (or service) between two or more parties in exchange of money or some other compensation.  It takes place almost every day in human-life.  Whenever there is a sale, there are at least two parties involved; a seller who is typically an owner of the product or who has legal possession and a buyer who is seeking the product.

Transaction Detail—this table typically contains one row per item in a transaction basket. The transaction detail table is often very wide, containing dozens of attributes about each item in a transaction. Captured fields typically include the following:

• Pricing information such as retail price, discounted price and markdown amounts;
• Manually keyed price changes at the point of sale;
• UPC codes, which can tie to vendors;
• Package sizes and weights;
• Timestamps for each item; and
• Tax amounts (sometimes) by item.

2. Identify & describe typical types of users (at least 3) who would need transactional information based on Sales Transactions;
3. Data Understanding (Be prepared to discuss this on 11/15/2017)
4. Familiarize yourself with each of the operational database tables of the LIY26 schema by exploring the data in the tables (see Appendix 1):
   • Determine if the datatype that is used in the columns definition is consistent with the actual data. Provide justification
   • Identify columns which appear to contain useless data. Provide justification.
   • Analyze each low cardinality field to determine the set of values; for each field.
   • Identify attributes that appear to be potential identifiers of embedded entities. Provide justification
   • Identify functional dependencies. Provide justification
   • Identify tables that appear to represent the same entity. Provide justification

4. Conceptual Data Modeling (Be prepared to discuss this on 11/15/2017)
5. Given the results of your Data Understanding activities, create an appropriate ERD for your Sales Transactions database system. Provide justification
6. Business Understanding 2 (Be prepared to discuss this on 11/29/2017)
7. For each user, identify & describe at least one transactional information needs (i.e. Business Query).
8. Data from each table must be included in at least one Business Query. See Appendix 3.
9. Some of your transactional questions should involve the following derived attributes:
   • Discount = List_Price – Actual_Price, where Actual_Price = Amount_Sold/Quantity_Sold
   • Discount_Rate = Discount*100/List_Price
   • Days_To_Ship = Shipment_Date – Sale_Date
   • Days_To_Pay = Payment_Date – Sale_Date

8. Security Requirements (Be prepared to discuss this on 11/29/2017):
9. Based on the query profile that you developed, place an “X” in each cell of Table E1 for which the relevant BUSINESS VIEW should be available to the given USER

Table E1: INITIAL ACCESS PERMISSION INTERACTION MATRIX

	BUSINESS VIEWs
USERs	BV01	BV02	BV03.			BV…
U01
U02
U03
U…

 

9. Place an “X” in each cell of Table E2 for which the relevant BUSINESS VIEW should not be available to the given USER

 

Table E2: ACCESS PREVENTION INTERACTION MATRIX

	BUSINESS VIEWs
USERs	BV01	BV02	BV03..			BV…
U01
U02
U03
U..

 

1. DESIGN RELATIONAL DATABASE (Be prepared to discuss this on 11/29/2017):
2. Define tables of relational database system that corresponds to your ERD. Each relation should be in 3NF. Provide justification.
3. INTEGRITY CONSTRAINTS (Be prepared to discuss this on 11/29/2017):

You will need to specify & test integrity constraints of your relational database system

11. Explore the values of low cardinality columns in order to get an idea as to the acceptable values for the column
12. Define domain constraints for each low cardinality column.
13. For each base table:
    • define primary key constraint;
    • Where appropriate, define referential integrity constraints
    • Where appropriate, define relevant inter-columns.

1. CREATE ORACLE TABLES & LOAD DATA into ORACLE:

For each table of your relational database system:

14. Estimate the storage requirements
15. Determine the values of the PCTFREE, PCTUSED, INITRANS, PCTINCREASE, etc. parameters
16. Specify the SQL DDL statement to Create the table. Your CREATE statement should include appropriate Integrity Constraint Specifications & appropriate values for the STORAGE clause, PCTFREE, PCTUSED, INITRANS, etc. parameters.
17. Execute the SQL DDL statement to Create the table.
18. Use the data in the corresponding tables of the LIY26 schema to populate the corresponding permanent table permanent tables of your relational database system.
19. Record any integrity constraint violation.
20. DETERMINE ACCESS STRUCTURES FOR DATABASE SYSTEM:
21. For each Business Query in your Query Profile, use the EXPLAIN PLAN facility to generate various execution plans, and identify its ‘best’ execution plan.
22. Based on the information obtained from these ‘best’ execution plans, determine the Indexes that should be included in your database and those which should be excluded. Provide justification for each choice.
23. Determine which of the Materialized VIEWS should be included in your database.
24. Specify & Test Security Scripts:
25. Write SQL Scripts to implement the security requirements.
26. Do Positive & Negative Tests on the SQL Security Scripts.
27. Team Contribution
28. Each student should write a paragraph about their team members contributions (e.g., what percentage of contribution a team member has and in what area). The team contribution report is sent to instructor separately as an individual assignment on Canvas.