Reference no: EM131174457
SQL Concepts and Database Design
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 by including the following:
• Steps in the conceptual design stage
• Steps in DBMS selection stage
• Steps in logical design stage
• Steps in 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. 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.
Product of monopolistically competitive? firm
: What is the trademinus−off that consumers face when buying the product of a monopolistically competitive? firm? If a monopolistically competitive firm has excess capacity.
|
How much does the power required to drive the pump change
: For a given size of centrifugal pump casing, if the diameter of the impeller is reduced by 25 percent, how much does the power required to drive the pump change?
|
Develop and share your position on an ethical issue
: develop, and share your position on an ethical issue.- develop your position based on sound reasoning and critical thinking.
|
How does your lesson serve as motivation for learners
: How does your lesson serve as motivation for learners? How does the lesson model a non-threatening environment providing differentiated learning opportunities without isolating students?
|
Construct a query to show the expected payment date
: 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.
|
Calculate how much money is needed to buy fuel
: A 34% efficient nuclear power plant outputs 1000MW per year. Calculate how much money is needed to buy fuel for the lifetime of the plant, 30 years. The fuel contains 7% of uranium, and 0.003g is lost when 235g of pure uranium is consumed. The plant ..
|
What is growth rate of money supply-what is inflation rate
: Assume that wages and prices are sticky and that we start at a long-run equilibrium. Assume that at this initial point, the growth rate of the money supply is 4%, the growth rate of the velocity of money is 3% and that the real economic growth rate i..
|
Determine npshr npsha pump efficiency and pressure
: In addition to identifying a pump for the job, also determine NPSHR, NPSHA, pump efficiency, pressure at the pump outlet, and total head on the pump as reported on the FLO-Sheet® in PUMP-FLO®.
|
Explain function of bill of materials in production process
: Explain the function of a bill of materials in the production process. Please ensure that your original response is at least 250 words and is supported with references.
|