Course Project
icanusesomehelp
course_project_specifications.pdf
MSCD 600 Database Architecture
Course Project Specifications
2
Table of Contents
Introduction .................................................................................................................................... 3
Preparation for Weeks 3 & 4 Discussions ....................................................................................... 3
Week 3 Discussion – ERD and Business Rules Peer Review ......................................................... 3
Week 4 Discussion – Normalization Diagram Peer Review ........................................................... 3
Activity 1 - Writing the Business Case and Documenting the Business Reporting Requirements 4
Deliverables ................................................................................................................................. 4
Methodology................................................................................................................................ 4
Activity 2 - Develop the E-R Model for the Business...................................................................... 4
Deliverables ................................................................................................................................. 4
Methodology................................................................................................................................ 4
Activity 3 - Develop the Data Dictionary and Entity Participation Constraints ............................. 5
Deliverables ................................................................................................................................. 5
Methodology................................................................................................................................ 5
Activity 4 - Write the SQL Data Definition Language script to physically implement the logical
design .............................................................................................................................................. 5
Deliverables ................................................................................................................................. 5
Methodology................................................................................................................................ 5
Activity 5 - Populate the relational tables with sample data .......................................................... 5
Deliverables ................................................................................................................................. 5
Methodology................................................................................................................................ 5
Activity 6 - Write the SQL queries to meet your business reporting requirements ....................... 5
Deliverables ................................................................................................................................. 6
Methodology................................................................................................................................ 6
Activity 7 - User Interface Specifications ........................................................................................ 6
Deliverables ................................................................................................................................. 6
Methodology................................................................................................................................ 6
Activity 8 - Present the final project ............................................................................................... 6
Deliverables ................................................................................................................................. 6
Methodology................................................................................................................................ 6
3
Introduction
You will demonstrate the culmination of all that you are learning in this course through a course project.
While working independently, you will have the opportunity to apply what you are learning and your
knowledge of database design. For your course project you may select either a hypothetical business, or
your own life or work experiences to use as the foundational model for your real-world database design.
For your course project, you are required to develop an extensive database design document that includes
five components, 1) business requirements, 2) conceptual schema diagrams, 3) logical schema definitions,
4) a data dictionary, and 5) sample queries that will allow you to answer a defined set of business
requirements. The following activities will guide you through the development of your course project.
As a graduate student, you will be expected to manage your time throughout this course and complete
your course project by the due date in Week 7. Check with your facilitator for any specific due date
requirement.
Complete the following activities. If you have questions, please check with your facilitator.
Preparation for Weeks 3 & 4 Discussions
Be prepared to discuss (see details below):
Week 3 – your course project ERD and Business Rule
Week 4 – your course project Normalization Diagram
Week 3 Discussion – ERD and Business Rules Peer Review
For your course project, we will discuss and peer review your ERD and Business Rules. Your business
model can be derived from a hypothetical your life or work experiences and it will become the
foundational model for your course project. Your ERD should include no more than 5 entities for
simplicity. This can be in word format at this point and does not need to be in diagram format unless
that’s easier for you (note that for the final project it will need to be in diagram format)…and have fun
with this! In addition, please support other students in their ER model development by gently pointing
out possible inconsistencies so that everyone can learn from this process.
Please include the following in your post:
Business Rules
ER Model
Sample Data (2-3 rows for each entity)
Note: Database design business rules would always be defined first and the ER model would be based on
these rules. Begin by writing the rules first though you may find that this is an iterative process.
Please provide your peer review with constructive feedback to at least two other learners this week to
enable for a cohesive learning experience for everyone.
Week 4 Discussion – Normalization Diagram Peer Review
For this week’s discussion, please take the database design that you created in week 3 and post the
database normalization diagrams (data dependency diagrams) that would go along with your model.
Keep in mind, that when you are creating an ER model, you are almost always already in 3NF. This is
just a sign that you created your initial model well. Therefore, do not try and un-normalize your tables to
try and show 1NF and 2NF just for the sake of showing each step.
This exercise is designed to take what you developed in week 3 and allow you to create data dependency
diagrams by working through the normal form steps and rules. As you go through this process, you may
4
find additional normal form discrepancies that you may have missed in the design process. Please define
all dependencies in your data dependency diagrams.
Please provide your peer review with constructive feedback to at least two other learners this week to
enable for a cohesive learning experience for everyone.
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.
