Sql concepts and database design

Assignment Help Database Management System

Reference no: EM13818753

The Strayer Oracle Server may be used to test and compile the SQL Queries developed for this assignment. 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:

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. 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

Reference no: EM13818753

Questions Cloud

What are the advantages and disadvantages of cloud computing : What are the advantages and disadvantages of cloud computing. What can the technology bring to your organization

Management techniques for project teams : Research conflict management techniques for project teams and post your opinion on how to respond to ONE of the following conflict situations.

Discuss google''s organizational culture : Discuss Google's organizational culture, and determine whether or not you would prefer to work in this type of environment. Indicate whether or not Google's company culture would help you to perform your tasks with a greater degree of creativity a..

Presentation with your map and competitor explanations : Complete a perceptual map that includes a minimum of 10 competitors from that same industry. Explain why you placed each competitor on its particular spot on the map. Create a 10- to 15-slide Microsoft PowerPoint presentation with your map and compet..

Sql concepts and database design : SQL Concepts and Database Design

How might walmart design an incentive program : How might Walmart design an incentive program? Are we looking at the corporate side of Walmart or the retail side of Walmart?

Identify all pure strategy nash equilibria : Construct a 3 x 3 game in which there is only one Nash equilibrium in pure strategies and the vector of payoffs in this equilibrium is "worse" than some other vector of payoffs in the game.

Write essay about icts-for every action there is a reaction : Write essay about ICTs-For Every Action there is a Reaction. Through your careful reading of primary and secondary sources, you will offer a thoughtful and logical explanation for the event, or ICT(s).

Question regarding the risk-free treasury bill : What is the required return using the CAPM if the stock's beta is 1.2, and the individual, who expects the market to rise by 13.2%, can earn 6.4% invested in a risk-free Treasury bill?

User Account

All Pages