database Design Project

Contents Database Design Project Overview---------------------------------------------------------------------------- 2 Week 3 Company Overview and Product----------------------------------------------------------------- 2 Company Name------------------------------------------------------------------------------------------------ 2 Company Overview/Description---------------------------------------------------------------------------- 2 Product----------------------------------------------------------------------------------------------------------- 2 Product Attributes---------------------------------------------------------------------------------------------- 3 Week 4 Customer Sales Invoice----------------------------------------------------------------------------- 3 Customer Sales Invoice--------------------------------------------------------------------------------------- 3 Week 5 Entity Relationship Diagrams---------------------------------------------------------------------- 3 ER Diagram including M:M Relationships---------------------------------------------------------------- 3 ER Diagram including M:M Relationships Resolved--------------------------------------------------- 3 Week 6 Database Build---------------------------------------------------------------------------------------- 4 Relational (Physical) Model---------------------------------------------------------------------------------- 4 SQL File--------------------------------------------------------------------------------------------------------- 4 DDL Create Statements------------------------------------------------------------------------------------- 4 Week 7 Business Rules and Constraints------------------------------------------------------------------- 4 Identify Business Rules---------------------------------------------------------------------------------------- 4 DDL Database Constraint Statements--------------------------------------------------------------------- 5 Database Constraint Testing--------------------------------------------------------------------------------- 5 Week 8 Study Week------------------------------------------------------------------------------------------- 5 Week 9 Final Database Design Project Submission----------------------------------------------------- 5 Database Design Project Overview

• Each group is assigned a product by the instructor.
• A database is to be designed and implemented based on the Customer Sales Invoice in Week 4 and other information.
• The project must consist of your own work completed according to schedule
• The requirements below are an outline of the minimum requirements
• All weekly dropbox due dates are Friday 8:00 p.m. on the week specified
• The weekly dropbox submissions keep you on track to complete the project on time.
• Weekly submissions are graded on a submitted/not submitted basis
• The mark for the final database design project is evaluated independently from the weekly submissions. In other words, getting 100% on the weekly dropbox submissions does not guarantee any mark on the final project mark.
• Final database design project includes:
  • Database design technical document (Word/PDF file)
  • Database SQL file (.sql)

Database Requirements

• Minimum of 12 tables (including at least 4 intersection tables)
• Minimum of 30 rows in the PRODUCTS/PARTS tables
• Minimum of 20 rows in the other primary tables
• Minimum of 10 rows in the associative tables
• At least 2 forms to enter data in the main tables (in more than one table at a time)
• At least 2 reports to retrieve data from the main tables (from more than one table per report)
• A user manual, if you believe it is necessary. Assume users do not read help or user manuals. As much as possible, the interface should be self-evident and require no additional information to learn how to use.

Week 3 Company Overview and Product

2. Create a Word document (.docx) called DB1_C00000, where C00000 is your student number. This document is a database proposal developed for the Vice President of IT and contains the components for the database design.
4. Format the document as follows:
   2. Front cover page including your name, student number, project name, term, and professor name
   4. Styles for titles, headings, and so on
   6. A header that contains the project title
   8. A footer that contains your name, student number, and page number
   10. Table of contents generated from the heading styles
7. Include the headings identified by (H) and content from each milestone

Company Name

• Must be a unique name that is not an existing company name

Company Overview/Description

• Include an overview/description of the company that is unique and not from an existing website

Product

• Identity your product

Product Attributes

• List at least 8-10 attributes (characteristics), such as model, brand, type, serial number, description, size, color, height, width, price, and so on. Example attributes can be found on many websites.
• Identify the primary unique identifier (UID). This UID can be called product id, product code, or something similar. You decide.

Week 4 Customer Sales Invoice Customer Sales Invoice

• Modify the sample Customer Sales Invoice to reflect your company and the company\'s product. The invoice must be based on the assigned product only.
• In addition to product id, quantity, unit price, and extended price, include three attributes that identify the product purchased. Use the three attributes that best describes the product purchased. It is not necessary to include all product attributes on the invoice.
• Include at least five products on the invoice.

Week 5 Entity Relationship Diagrams ER Diagram including M:M Relationships

• Create an ER diagram based on the customer sales invoice and the product attribute list. Include many-to-many relationships:
  • Entities
  • Attributes (mandatory or optional)
  • Unique Identifiers
  • Relationship cardinality
  • Optionality
  • Identifying and non-identifying relationships
  • Foreign Keys
  • Named Relationships
  • ERD language
• Additional requirements:
  • The company operates warehouses in different regions of the country
  • Each warehouse stores products and each product may be stored at each warehouse. Not all products are stored at each warehouse. In addition, a warehouse could store a product, but be out of stock of that product. The company wants to maintain the inventory of each product at each warehouse.

ER Diagram including M:M Relationships Resolved

• Create a second ER diagram with many-to-many relationships resolved

Week 6 Database Build Relational (Physical) Model

• Create a relational (physical) model in table format of the proposed database:
  • Tables
  • Key Types (PK, FK, UK)
  • Column Optionality
  • Column Names
  • Data Types
  • Length/Decimal Positions

SQL File

• Create a file called DB2_C00000.SQL, where C00000 is your student number. This file will contain all SQL code for the database build
• All non-code must be commented so the entire code will run as a script
• At the top of the file, include your name, student number, project name, term, and professor name
• Include headings and comments throughout the document that clearly identify the SQL code and each step in the process
• Upon completion, this SQL file will contain (1) DDL Create Statements; (2) , DDL Database Constraint Statements; and (3) Database Constraint Testing

DDL Create Statements

2. Construct CREATE statements for each database table
4. Create INSERT statements that populate each table with test data. Include:

• 5 customers
• 3 associates
• 10 products
• 10 orders with at least two products on each order

Week 7 Business Rules and Constraints Identify Business Rules

• Create a table (Word or Excel) in the DB1 Word document that lists each database table, column, and constraint
• Your database MUST include the following constraints:
  • NOT NULL
  • DEFAULT
  • Primary key
    • Primary key of the CUSTOMERS table is an IDENTITY auto generated column
    • Primary key of the ORDERS table is a SEQUENCE auto generated column
  • Unique key
  • Foreign key
  • Check constraint

DDL Database Constraint Statements

• Use the ALTER TABLE statement to create the constraints identified in the business rules
• The NOT NULL constraints may be defined at the column level

Database Constraint Testing

2. Include at least the following constraint testing:
   2. NOT NULL (1)
   4. DEFAULT (1)
   6. Primary keys (2)
   8. Unique keys (1)
   10. Foreign keys (4)
   12. CHECK constraints (at least three, but test all CHECK constraints)
5. Test each constraint and provide unit-testing documentation:
   2. Provide a test that validates valid data is accepted
   4. Provide a test that validates invalid data is refused. For constraints such as ranges, test the upper and lower ranges. In other words, verify that all conditions work correctly.

Week 8 Study Week Week 9 Final Database Design Project Submission

• The final database design project includes two files:
  • Database Design Technical Document (Word/PDF file)
  • SQL File (.sql file)
• Suggested marking scheme. Your professor will provide the making scheme used.

• Technical Document: One mark for each task. Either one (100%) or zero
• Design and Build components are is marked out of two as follows:
  • Two marks if component completed 100% according to the specifications
  • One mark if component is included but one item is incomplete or incorrect
  • Zero if component not included or more than one item is incomplete or incorrect