Activity 1 - Writing the Business Case and Documenting the Business Reporting Requirements Deliverables
1. A 300-500 word Abstract (single spaced, 12 point font) that answers the following questions about your (hypothetical) business case:
a. The name of the business
b. What the nature of the business is
c. Scope and size of the business
d. Description of the business model (i.e. how does it make money? or does it?)
e. Description of the customers and products or services
f. Current state of the business where a database might improve the business (what is the business problem the company has?)
2. A detailed listing of the business reporting requirements (a minimum of 6) For example: a business reporting requirement might be stated as:
1) Must generate a report that specifies the names of customers who bought item number 10357 during the month of November. Methodology Brainstorm about an interesting business that is small enough to describe, and that would have a business need to improve its data capturing and reporting capability. You should clearly understand, and articulate, the business model (how it operates, functions, makes money, etc.), who its customers are, and the basic flow of information inside the company. This is a hypothetical company, so you can be creative. But a word of caution: don't let the scope get too large! You want something that is manageable for this assignment.
Activity 2 - Develop the E-R Model for the Business Deliverables
1. A textual representation of the "data set" for the company. This is a list of all the data values that the company needs (or would like to) keep track of in the business. For example: customers name, address, phone, email, etc. This should include all areas of operation described in your business case scenario. An example of this would be as follows: CUSTOMER (cust_fname, cust_lname, cust_address...etc).
2. Document the business rules for the company. Include all business rules that satisfy the multidirectional relationships between business entities.
3. The normalized Entity-Relationship model (ERD) for the business. Show all data modeling work, to include cardinality of relationships, key constraints, and relevant assumptions made in modeling the data. Methodology Using appropriate data modeling and data normalization techniques create an E-R model for the business described in your business case scenario. The ER model will be developed by going through SDLC 5 functions/steps described in deliverables 1 and 2. The model for deliverable 3 can be illustrated using PowerPoint, a simple E-R modeling tool, or any software program that has the needed modeling tools. Show all work in creating the model as you move from 1NF -> 2NF -> 3NF, as well as implementation of primary and foreign key constraints.
Activity 3 - Develop the Data Dictionary and Entity Participation Constraints Deliverables
1. The Data Dictionary for the logical (E-R) model. Methodology Using the E-R model created in Activity 2, create a Data Dictionary for the logical model of the relational database. Include in the Data Dictionary a minimum of the following:
a. Attribute name
b. Field name ]
c. Datatype
d. Field length
e. Constraints
f. Associated relation (table/entity)
g. Description of the attribute
Activity 4 - Write the SQL Data Definition Language script to physically implement the logical design Deliverables
1. One SQL script that will run against the DBMS to create the relational database tables. Methodology Using SQL DDL syntax, write the script that will build all of the relational tables, based on your logical (E-R) model. Include all appropriate Data Dictionary definition in the SQL script.
Activity 5 - Populate the relational tables with sample data Deliverables
1. One SQL Data Manipulation Language script that will populate the relational tables with a minimum of 10 sample records per table. Methodology Write one SQL DML script that will populate the tables with at least 10 records. Be sure to observe the cascading rules for primary and foreign key relationships.
Activity 6 - Write the SQL queries to meet your business reporting requirements 6 Deliverables
1. A minimum of 6 SQL queries that satisfy the business reporting requirements as specified in the deliverable for Activity 1. Methodology Review your business reporting requirement that you specified in Activity #1 and write a corresponding SQL query that will satisfy the conditions of each of the business reporting requirements.
Activity 7 - User Interface Specifications Deliverables
1. A document that describes/illustrates the User Interface Requirements for the relational database developed in Activities 1-6. Methodology The User Interface should allow for addition/deletion of records in some particular area. For example, if you define the interface for the Employee Relations department, then the interface specification would call for access to the tables that contain the employee data. Your interface specification would include the specific table and attributes, as well as any exclusion of attributes. For example, you may not want the salary information shown if the interface is for a secretary in the Employee Relations department. Your document will define the user interface that you will build.
Activity 8 - Present the final project Deliverables
1. PowerPoint slide presentation (should equate to 15 - 20 minutes if presented in person) Methodology Develop a PowerPoint slide presentation for your Database Life Cycle Project. Attach it to the assignment Dropbox titled for this assignment by the date specified by your facilitator.