Great Deal! Get Instant $10 FREE in Account on First Order + 10% Cashback on Every Order Order Now

SQL Concepts and Database Design **Focus on SQL, however Oracle and Access can be utilized** Imagine that you work for a finance industry-based organization. Your organization is looking to submit its...

1 answer below »
SQL Concepts and Database Design 
**Focus on SQL, however Oracle and Access can be utilized**
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 Sa
anes-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 (3) page paper in which you:
1. Create a data dictionary that includes the following:
a. A description of the content for each field
. The data type of each field
c. The format the data will be stored as in the field
d. The range of value for the field
e. A label, as required, if the attribute is a primary key or foreign key
Note: An example of a data dictionary is shown below:
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:
a. 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 ca
y out this function.
. Construct a query to show the expected payment date if invoices are due within 30 days of transaction.  
c. 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 by including the following:
3. Steps in the conceptual design stage
3. Steps in DBMS selection stage
3. Steps in logical design stage
3. Steps in physical design stage
3. Task details of each activity within each stage
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 format.
· Include a cover page containing the title and the date. The cover page and the reference page are not included in the required page length.
Answered Same Day Nov 28, 2021

Solution

Deepti answered on Dec 02 2021
148 Votes
database design
1. The database of Sales and Organization requires the following Data Dictionary. It provides the definition of the attributes in each table (Customer, Job, Employee, Invoice, InvoiceLine, Department), their data type, format, range and label.
DATA DICTIONARY:
    Attribute
    Description
    Data Format
    Data Type
    Range
    NOTNULL/LABEL
    EmpNumbe
    Autogenerated, Unique number for employee
    
    Int
    10
    Primary Key in Employee table. Foreign Key in Invoice table
    EmpFirstName
    Employee’s First Name
    Text
    Varcha
    50
    
    EmpLastName
    Employee’s Last Name
    Text
    Varcha
    50
    
    CommissionRate
    Fixed Rate of commission for the employee
    
    Int
    10
    
    YrlySalary
    Employee’s Annual Salary
    
    Int
    10
    NOT NULL
    CustomerID
    Unique number identifying each customer
    
    Int
    10
    Primary Key
    CustomerName
    Customer’s Name
    
    Varcha
    50
    NOT NULL
    CustomerAddress
    Customer’s Address
    
    Varcha
    50
    NOT NULL
    City
    City of custome
    
    Varcha
    50
    
    State
    State of Custome
    
    Varcha
    20
    
    Zipcode
    Adress zipcode
    
    Varcha
    20
    
    CustomerPhone
    Customer’s Phone numbe
    
    Varcha
    10
    NOT NULL
    DepartmentID
    Autogenerated, Unique number for each department
    
    Int
    10
    Primary Key in Department table. Foreign Key in Employee table
    DepartmentDescription
    Department Name and description
    Text
    Varcha
    100
    
    JobID
    Unique number for each jo
    
    Int
    10
    Primary Key in Job table.
Foreign Key in Employee table
    JobDescription
    Description of job to be done
    Text
    Varcha
    100
    
    InvDate
    Date on which the invoice is issued
    YYYY-MM-DD
    Date
    8
    NOT NULL
    InvAmount
    Total amount of the invoice
    
    Int
    10
    NOT NULL
    InvLineNumbe
    Autogenerated, Unique number for Invoice Line
    
    Int
    10
    NOT NULL
    InvNumbe
    Autogenerated, Unique number for each invoice
    
    Big Int
    10
    Primary Key in Invoice table....
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here