SQL project

Instructions for Final Project:

For your final project, you will compile seven activities into a professional document showcasing theseactivities of the Database Life Cycle. This project provides you with the opportunity to show your learning in database design and architecture principles and practices. You start by writing a business case with accompanying business reporting requirements followed by development of an E-R Model for the Business. Next, you develop the Data Dictionary and Entity Participation Constraints, then write the SQL Data Definition Language script to physically implement the logical design and populate the relational tables with sample data. Finally, you will write the SQL queries to meet your business reporting requirements and user interface specifications. All of these activities will culminate in two deliverables: a document showing each activities outcome and a PowerPoint presentation prepared with speaker notes as if you were presenting it to the class.

All written deliverables must adhere to the  University College format and style requirements (Links to an external site.)Links to an external site. .

Activity 1 - Writing the Business Case and Documenting Business Reporting Requirements

Activity Deliverables  A 300-500 word abstract that answers the following questions about your (hypothetical) business case:

The name of the business  What the nature of the business is Scope and size of the business  Description of the business model (i.e. how does it make money? or does it?)  Description of the customers and products or services  Current state of the business where a database might improve the business (what is the business problem the company has?)

A detailed listing of the business reporting requirements (a minimum of 6)

For example: a business reporting requirement might be stated as: "Must generate a report that specifies the names of customers who bought item number 10357 during the month of November."

Methodology

1. 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.

2. Clearly articulate your understanding of 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 and Business Rules for the Business

Activity Deliverables

1. Document the business rules for the company. Include all business rules that satisfy the multidirectional relationships between business entities.

2. Develop functional dependency diagrams for all entities. Note that during the design process you may have created an initial design that is already in 3NF or 2NF.  Therefore, only show relevant normalized forms for your design models and don’t show lower normal forms unless you identified partial or transitive dependencies during the normalization process.

3. The normalized Entity-Relationship model (ERD) for the business. An ER model should be developed that coincides with the business rules provided and all primary key and foreign key values should be correctly placed.

4. Show all data modeling work, to include cardinality of relationships, key constraints, and relevant assumptions made in modeling the data.

Methodology

1. Using appropriate data modeling and data normalization techniques, create an E-R model for the business described in your business case scenario.

2. The ER model will be developed by going through SDLC functions/steps described in Activities 1 and 2. The model for Activity 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 the data normalization process including all normal forms that were identified in the design process, as well as implementation of primary and foreign key constraints.

Activity 3 - Develop the Data Dictionary for your database

Activity Deliverables

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:

· Attribute name

· Datatype

· Field length

· Constraints

· Associated relation (table/entity)

· Description of the attribute

Activity 4 - Write the SQL Data Definition Language script to physically implement the logical design

Activity Deliverables 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

Activity Deliverables

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

Activity Deliverables

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

Activity Deliverables

A document that describes/illustrates the User Interface Requirements for the relational database developed in Activities 1-6.

Methodology

1. 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.

2. Your interface specification would include the specific table and attributes, as well as any exclusions of attributes.

3. 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.

4. This activity is meant to demonstrate how the user interface (how the end user views the data) is different from the logical/physical data model (as the data model should be transparent to the user).

5. So, for example the user interface may require the user to enter data into multiple fields that look related to a CUSTOMER, but that in reality actually relate to many possible different tables/views on the back end. This can be done by just describing your end user interface or by creating a mock interface (you can even just use the drawing tools in Word or PowerPoint, nothing fancy needed).

6. In addition, you don't have to give the specifications for every single user interface, as you will most likely have several. Just enough so that I can see that you understand the fact that what a user sees does not directly relate to how the data is stored.

Activity 8 – “Present” the final project via a PowerPoint presentation, recorded using Zoom and upload a Word document containing activities 1-7 to Canvas

Activity Deliverables

1. PowerPoint slide presentation

2. Presentation recording (should be 15 – 20 minutes.)

3. Word document containing activities 1-7.

Methodology

1. Develop a PowerPoint slide presentation for your Database Life Cycle Project. Record the presentation via Zoom. Please follow the Zoom instructions below. Each class member will review two other’s presentations and provide feedback in Week 10.

2. Combine all 7 DBLC activities into one final project Word document.

3. Upload the PowerPoint presentation, the presentation recording url, and final project document to Canvas.