Assignment 6: SQL Concepts And Database Design

Assignment 6: SQL Concepts and Database Design

Your instructor will provide you with login credentials to a Strayer University maintained Oracle server. Imagine that you work for a finance industry-based organization. Your organization is looking to submit its database design documentation to an evaluation team in order to meet Sarbanes-Oxley (SOX) compliance. You have been assigned to assist in preparing the documentation of the organization’s sales database. You are asked to propose a data dictionary that can be used to document the following:

  • Employee (EmpNumber, EmpFirstName, EmpLastName, CommissionRate, YrlySalary, DepartmentID, JobID)
  • Invoice (InvNumber, InvDate, EmpNumber, InvAmount, CustomerID)
  • InvoiceLine (InvLineNumber, InvNumber, ProductNumber, Quantity)
  • Product (ProductNumber, ProductDescription, ProductCost)
  • Department (DepartmentID, DepartmentDescription)
  • Job (JobID, JobDescription)
  • Customer (CustomerID, CustomerName, CustomerAddress, CustomerPhone)

Write a three to four (3-4) page paper in which you:

  1. Create a data dictionary that includes the following:
    • A description of the content for each field
    • The data type of each field
    • The format the data will be stored as in the field
    • The range of value for the field
    • A label, as required, if the attribute is a primary key or foreign key
  2. Imagine that you are asked to identify the number of days that exist between the first invoice and last invoice for each month and complete the following:
    • Construct a query that will show the number of days that exist between the first invoice and last invoice, for each month, for each employee, using the DATEDIFF function. Be sure to provide the SQL script that will carry out this function.
    • Construct a query to show the expected payment date if invoices are due within 30 days of transaction.
    • Construct a query that will show distinct area codes of the customers.
  3. Create a plan of the necessary activities that would be required to implement a valid database design process including:
    • Steps in the conceptual design stage
    • Steps in the DBMS selection stage
    • Steps in the logical design stage
    • Steps in the physical design stage
    • Task details of each activity within each stage

Your assignment must follow these formatting requirements:

  • Be typed, double spaced, using Times New Roman font (size 12), with one-inch margins on all sides; citations and references must follow APA or school-specific format.
  • Include a cover page containing the title of the assignment, the student’s name, the professor’s name, the course title, and the date. The cover page and the reference page are not included in the required assignment page length.

Paper For Above instruction

The development of a comprehensive data dictionary is a fundamental step in establishing a robust database system that aligns with organizational needs and compliance standards such as Sarbanes-Oxley (SOX). This paper presents a detailed data dictionary for a sales database used by a financial organization, provides SQL queries to analyze invoice data, and outlines the activities required for implementing a valid database design process.

Data Dictionary

Attribute Description Data Type Data Format Range of Values Key Label
EmpNumber Unique identifier for each employee INTEGER Numeric, e.g., 0001 1 to 99999 Primary Key
EmpFirstName Employee's first name VARCHAR2(50) Text, e.g., 'John' Up to 50 characters
EmpLastName Employee's last name VARCHAR2(50) Text, e.g., 'Doe' Up to 50 characters
CommissionRate Employee's commission rate as a percentage NUMBER(5,2) Decimal, e.g., 10.50 0.00 to 100.00
YrlySalary Annual salary of the employee NUMBER(10,2) Currency format, e.g., 60000.00 0.00 to 1,000,000.00
DepartmentID Identifier for the department INTEGER Numeric, e.g., 101 1 to 999 Foreign Key
JobID Identifier for the job position INTEGER Numeric, e.g., 5 1 to 999 Foreign Key
InvNumber Invoice number, unique for each invoice VARCHAR2(20) Alphanumeric, e.g., 'INV1001' Up to 20 characters Primary Key
InvDate Date of the invoice DATE Date format, 'YYYY-MM-DD' Valid dates within transaction period
EmpNumber Employee responsible for the invoice INTEGER Numeric, matches Employee.EmpNumber Must exist in Employee table Foreign Key
InvAmount Total amount of the invoice NUMBER(12,2) Currency, e.g., 1500.75 0.00 to 9999999999.99
CustomerID Unique customer identifier VARCHAR2(20) Alphanumeric, e.g., 'CUST1001' Up to 20 characters Foreign Key
InvLineNumber Line number within an invoice INTEGER Numeric, e.g., 1, 2, 3 Positive integers
InvNumber Associated invoice number VARCHAR2(20) Matches InvNumber in Invoice Same as invoice number Foreign Key
ProductNumber Unique product identifier VARCHAR2(20) Alphanumeric, e.g., 'PRD001' Up to 20 characters Primary Key
Quantity Quantity of product ordered INTEGER Numeric, e.g., 10 1 to 99999
ProductDescription Description of the product VARCHAR2(100) Text, e.g., 'Laptop Computer' Up to 100 characters
ProductCost Cost per unit of product NUMBER(10,2) Currency, e.g., 499.99 0.00 to 1,000,000.00
DepartmentID Identifier for department INTEGER Numeric, e.g., 10 Valid department IDs Primary Key
DepartmentDescription Description of department VARCHAR2(100) Text, e.g., 'Sales' Up to 100 characters
JobID Identifier for job position INTEGER Numeric, e.g., 3 Valid Job IDs Primary Key
JobDescription Description of job VARCHAR2(100) Text, e.g., 'Sales Manager' Up to 100 characters
CustomerID Customer unique identifier VARCHAR2(20) Alphanumeric, e.g., 'CUST1002' Up to 20 characters Primary Key
CustomerName Name of the customer VARCHAR2(50) Text, e.g., 'XYZ Corporation' Up to 50 characters
CustomerAddress Customer's address VARCHAR2(100) Address text Up to 100 characters
CustomerPhone Customer's phone number VARCHAR2(15) Phone number, e.g., '(555) 123-4567' Up to 15 characters

SQL Queries

1. Days Between First and Last Invoice per Employee per Month

SELECT


e.EmpNumber,


TO_CHAR(i.InvDate, 'YYYY-MM') AS InvoiceMonth,


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


FROM


Invoice i


JOIN


Employee e ON i.EmpNumber = e.EmpNumber


GROUP BY


e.EmpNumber,


TO_CHAR(i.InvDate, 'YYYY-MM');

This query calculates the number of days between the first and last invoice dates for each employee within each month, utilizing the subtraction of dates in Oracle SQL, which yields the difference in days.

2. Expected Payment Date

SELECT


InvNumber,


InvDate,


InvDate + 30 AS ExpectedPaymentDate


FROM


Invoice;

Assuming invoices are due within 30 days, the expected payment date is computed by adding 30 days to the invoice date.

3. Distinct Customer Area Codes

SELECT DISTINCT


SUBSTR(CustomerPhone, 2, 3) AS AreaCode


FROM


Customer;

This query extracts the area codes from customer phone numbers, assuming a standard format like '(555) 123-4567' with the area code starting at position 2.

Activities for Implementing a Valid Database Design Process

Conceptual Design Stage

  1. Identify the main entities such as Employee, Invoice, Product, Customer, Department, and Job.
  2. Define the relationships between entities, such as one-to-many between Customer and Invoice, or between Employee and Invoice.
  3. Create an Entity-Relationship (ER) diagram to visualize and validate the data model.

DBMS Selection Stage

  1. Assess organizational requirements regarding scalability, security, and compliance.
  2. Evaluate various DBMS options like Oracle, SQL Server, or MySQL based on features, licensing, support, and cost.
  3. Choose a DBMS that best fits the organization's needs, considering integration capabilities with existing systems.

Logical Design Stage

  1. Convert the ER diagram into relational schemas, defining tables, primary keys, and foreign keys.
  2. Normalize tables to eliminate redundancy and ensure data integrity.
  3. Define constraints such as data types, ranges, and nullability.

Physical Design Stage

  1. Determine storage parameters such as tablespaces, indexing strategies, and partitioning for performance optimization.
  2. Implement security measures, including user roles and access controls.
  3. Design backup and recovery procedures.

Each activity within these stages involves detailed planning, stakeholder collaboration, validation, and iterative refinement to ensure the database aligns with organizational objectives and compliance requirements.

References

  • Codd, E. F. (1970). A relational model of data for large shared data banks. Communications of the ACM, 13(6), 377-387.

Related Assignments