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 7 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 Include at least the following constraint testing NOT NULL (1) DEFAULT (1) Primary keys (2) Unique keys (1) Foreign keys (4) CHECK constraints (at least three, but test all CHECK constraints) Test each constraint and provide unit testing documentation Provide a test that validates valid data is accepted 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

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on May 15, 2024

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

Week 7 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

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