SQL Concepts and Database Design

May 24th, 2014
DotaCN
Category:
Programming
Price: $30 USD

Question description

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:
    1. A description of the content for each field
    2. The data type of each field
    3. The format the data will be stored as in the field
    4. The range of value for the field
    5. A label, as required, if the attribute is a primary key or foreign key

Note: An example is shown in Table 7.3 in chapter 7 of the textbook.

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:

  1. 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.
  2. Construct a query to show the expected payment date if invoices are due within 30 days of transaction. 
  3. Construct a query that will show distinct area codes of the customers

3. 

  1. Create a plan of the necessary activities that would be required to implement a valid database design process by including the following:
    1. Steps in the conceptual design stage
    2. Steps in DBMS selection stage
    3. Steps in logical design stage
    4. Steps in physical design stage
    5. 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. Check with your professor for any additional instructions.
  • 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.

note attached:table 7.3.jpg 

Tutor Answer

(Top Tutor) Daniel C.
(997)
School: University of Maryland
PREMIUM TUTOR

Studypool has helped 1,244,100 students

8 Reviews


Summary
Quality
Communication
On Time
Value
kevin12622
Dec 4th, 2016
" Goes above and beyond expectations ! "
kiln82
Nov 25th, 2016
" awesome work thanks "
ashleyisgod
Nov 21st, 2016
" Top quality work from this guy! I'll be back! "
likeplum4
Nov 12th, 2016
" Excellent work as usual "
Molly_Moon
Oct 29th, 2016
" AMAZING as always! "
kpcutie
Oct 20th, 2016
" Excellent job "
Hemapathy
Oct 7th, 2016
" all I can say is wow very fast work, great work thanks "
pmallory
Sep 28th, 2016
" Totally impressed with results!! :-) "
Ask your homework questions. Receive quality answers!

Type your question here (or upload an image)

1823 tutors are online

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors