Assignment 6: SQL Concepts And Database Design Due We 753204

Assignment 6: SQL Concepts and Database Design Due Week 7 and Worth 120 points

The assignment involves creating a data dictionary for a sales database, constructing specific SQL queries, and developing a plan for database design activities. Additionally, it requires adherence to formatting guidelines and inclusion of a cover page. The goal is to document a database schema that meets SOX compliance and to demonstrate an understanding of relational database design principles, data security, and strategic planning for database implementation.

Paper For Above instruction

The process of designing a relational database that complies with Sarbanes-Oxley (SOX) regulations necessitates comprehensive documentation and precise structuring of data. This paper addresses three key components: developing a detailed data dictionary for a sales database, constructing specific SQL queries to analyze invoice data, and outlining a step-by-step plan for implementing a robust database design. Each section is vital for ensuring data integrity, security, and functional efficiency in a business environment, particularly within a finance-focused organization.

Data Dictionary Development

The first step in database design is creating an exhaustive data dictionary that describes each attribute within the database schema. This documentation aids in establishing data standards and ensuring the clarity of data storage and retrieval processes.

  • Employee Table
  • EmpNumber: Employee's unique identification number. Data type: INTEGER. Format: Numeric. Range: 1–99999. Label: Primary Key.
  • EmpFirstName: Employee’s first name. Data type: VARCHAR2. Format: Text string, up to 50 characters.
  • EmpLastName: Employee’s last name. Data type: VARCHAR2. Format: Text string, up to 50 characters.
  • CommissionRate: Percentage rate of commission earned. Data type: DECIMAL(5,2). Format: Numeric with two decimal places (e.g., 12.50). Range: 0.00–100.00.
  • YrlySalary: Employee’s annual salary. Data type: NUMBER(10,2). Format: Numeric with two decimal places. Range: 0.00–99999999.99.
  • DepartmentID: Foreign key linking to the Department table. Data type: INTEGER. Range: 1–9999.
  • JobID: Foreign key linking to the Job table. Data type: VARCHAR2. Format: Text, up to 10 characters.
  • Invoice Table
  • InvNumber: Unique invoice number. Data type: VARCHAR2. Format: Text string, up to 20 characters. Primary Key.
  • InvDate: Date of invoice creation. Data type: DATE. Format: YYYY-MM-DD.
  • EmpNumber: Employee responsible for the invoice. Data type: INTEGER. Foreign key.
  • InvAmount: Total invoice amount. Data type: DECIMAL(12,2). Range: 0.00–999999999.99.
  • CustomerID: Customer associated with invoice. Data type: VARCHAR2. Format: Text, up to 10 characters. Foreign key.
  • InvoiceLine Table
  • InvLineNumber: Line item number within the invoice. Data type: INTEGER. Primary key with auto-increment.
  • InvNumber: Link to Invoice table. Data type: VARCHAR2. Format: Text, up to 20 characters. Foreign key.
  • ProductNumber: Reference to Product table. Data type: VARCHAR2. Up to 20 characters. Foreign key.
  • Quantity: Quantity of product ordered. Data type: INTEGER. Range: 1–10,000.
  • Product Table
  • ProductNumber: Unique product identifier. Data type: VARCHAR2. Up to 20 characters. Primary key.
  • ProductDescription: Description of the product. Data type: VARCHAR2. Up to 255 characters.
  • ProductCost: Cost of the product. Data type: DECIMAL(10,2). Range: 0.00–99999999.99.
  • Department Table
  • DepartmentID: Department ID. Data type: INTEGER. Primary key.
  • DepartmentDescription: Overview of department functions. Data type: VARCHAR2. Up to 100 characters.
  • Job Table
  • JobID: Job role identifier. Data type: VARCHAR2. Up to 10 characters. Primary key.
  • JobDescription: Description of the job role. Data type: VARCHAR2. Up to 100 characters.
  • Customer Table
  • CustomerID: Unique customer identifier. Data type: VARCHAR2. Up to 10 characters. Primary key.
  • CustomerName: Name of the customer. Data type: VARCHAR2. Up to 100 characters.
  • CustomerAddress: Customer address. Data type: VARCHAR2. Up to 255 characters.
  • CustomerPhone: Phone number. Data type: VARCHAR2. Up to 15 characters.

SQL Queries for Data Analysis and Business Insights

The organization requires specific SQL queries to analyze invoice data:

  1. Days Between First and Last Invoices per Month per Employee:
  2. The goal is to compute the number of days between the earliest and latest invoice dates for each employee within each month. This can be achieved using the DATEDIFF function, which calculates the difference between two dates. Here is the SQL query:

  3. SELECT

    EXTRACT(YEAR FROM InvDate) AS Year,

    EXTRACT(MONTH FROM InvDate) AS Month,

    EmpNumber,

    DATEDIFF(day, MIN(InvDate), MAX(InvDate)) AS DaysBetween

    FROM

    Invoice

    GROUP BY

    EXTRACT(YEAR FROM InvDate),

    EXTRACT(MONTH FROM InvDate),

    EmpNumber;

  4. Note: The exact syntax for the DATEDIFF function may differ based on the database system. For Oracle, the following expression uses the subtraction of dates:

  5. SELECT

    EXTRACT(YEAR FROM InvDate) AS Year,

    EXTRACT(MONTH FROM InvDate) AS Month,

    EmpNumber,

    (MAX(InvDate) - MIN(InvDate)) AS DaysBetween

    FROM

    Invoice

    GROUP BY

    EXTRACT(YEAR FROM InvDate),

    EXTRACT(MONTH FROM InvDate),

    EmpNumber;

  6. Expected Payment Date (Invoiced Amount Due within 30 Days):
  7. Assuming invoices are due 30 days after their invoice date, the expected payment date is calculated by adding 30 days to each InvDate:

  8. SELECT

    InvNumber,

    InvDate,

    ADD_MONTHS(InvDate, 0) + 30 AS ExpectedPaymentDate

    FROM

    Invoice;

  9. Distinct Area Codes of Customers:
  10. If customer addresses contain area codes, typically as part of phone numbers or postal codes, extracting unique area codes involves string functions. For example, if customer phone numbers are formatted as (XXX) XXX-XXXX:

  11. SELECT DISTINCT

    SUBSTR(CustomerPhone, 2, 3) AS AreaCode

    FROM

    Customer;

Database Design Implementation Plan

Implementing a valid database design involves systematic activities at each phase:

Conceptual Design Stage

  • Identify entities, such as Employee, Invoice, Product, Customer, Department, and Job.
  • Define relationships between entities, including foreign key associations.
  • Create an Entity-Relationship (ER) diagram to visualize and validate the data structure.

DBMS Selection Stage

  • Evaluate organizational needs, scalability, security features, and compatibility with current infrastructure.
  • Select an appropriate DBMS, such as Oracle, SQL Server, or PostgreSQL, based on technical requirements and compliance needs.

Logical Design Stage

  • Translate ER diagrams into relational schemas, defining tables, columns, and primary/foreign keys.
  • Normalize schemas to at least 3NF to minimize redundancy and ensure data integrity.
  • Define constraints, default values, and data validation rules.

Physical Design Stage

  • Determine indexing strategies to optimize query performance.
  • Plan storage parameters, such as tablespaces and partitioning if necessary.
  • Implement security measures, including user roles, privileges, and encryption.

Activities within each stage are iterative, often requiring revisiting earlier steps to optimize performance, security, and compliance.

Conclusion

Designing a compliant and efficient sales database entails a meticulous process of documentation, analysis, and planning. Developing a comprehensive data dictionary ensures clarity and consistency across data elements. Constructing precise SQL queries provides valuable insights for decision-making and operational efficiency. Implementing a structured database design process guarantees that the system is reliable, scalable, and secure, aligning with strategic business objectives and regulatory standards such as SOX. The integration of these components fosters robust data management practices crucial for the organization’s success and compliance.

References

  • Elmasri, R., & Navathe, S. B. (2015). Database Systems: The Complete Book (6th ed.). Pearson.
  • Kroenke, D. M., & Auer, D. J. (2012). Database Concepts (6th ed.). Pearson.
  • Hoffer, J. A., Venkataraman, R., & Topi, H. (2016). Modern Database Management (12th ed.). Pearson.
  • Coronel, C., & Morris, S. (2015). Database Systems: Design, Implementation, & Management (11th ed.). Cengage Learning.
  • Silberschatz, A., Korth, H. F., & Sudarshan, S. (2020). Database System Concepts (7th ed.). McGraw-Hill Education.
  • TechRepublic. (2022). Best practices for database security. TechRepublic.
  • Oracle Corporation. (2023). Oracle Database Security Guide.
  • ISO/IEC 27001:2013. Information security management standards.

Related Assignments