Assignment 6 SQLConcepts and Database DesignThe Strayer Or
Assignment 6: SQLConcepts and Database DesignThe Strayer Oracle Server may be used totest and compile the SQL Queries developed for this assignment. Your instructorwill provide you with login credentials to a Strayer University maintainedOracle server.Imagine that you work for a financeindustry-based organization. Your organization is looking to submit its databasedesign documentation to an evaluation team in order to meet Sarbanes-Oxley (SOX)compliance. You have been assigned to assist in preparing the documentation ofthe organization’s sales database. You are asked to propose a data dictionarythat 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 inwhich you:Create a data dictionary that includes thefollowing:A description of the content for eachfieldThe data type of each fieldThe format the data will be stored as in thefieldThe range of value for the fieldA label, as required, if the attribute is aprimary key or foreign keyNote: An example is shownin Table 7.3 in chapter 7 of the textbook.Imagine that you are asked to identify thenumber of days that exist between the first invoice and last invoice for eachmonth and complete the following:Construct a query that will show the numberof 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 SQLscript that will carry out this function.Construct a query to show the expectedpayment date if invoices are due within 30 days of transaction. Construct a query that will show distinctarea codes of the customers.Create a plan of the necessary activitiesthat would be required to implement a valid database design process by includingthe following:Steps in the conceptual designstageSteps in DBMS selection stageSteps in logical design stageSteps in physical design stageTask details of each activity within eachstageYour assignment must follow these formattingrequirements:Be typed, double spaced, using Times NewRoman font (size 12), with one-inch margins on all sides; citations andreferences must follow APA or school-specific format. Check with your professorfor any additional instructions.Include a cover page containing the title ofthe assignment, the student’s name, the professor’s name, the course title, andthe date. The cover page and the reference page are not included in the requiredassignment page length.The specific course learning outcomesassociated with this assignment are:Design a relational database so that it isat least in 3NF.Prepare database design documents using thedata definition, data manipulation, and data control language components of theSQL language.Explain the key principles of data securityand identify data security risk and violations in data management systemdesign.Use technology and information resources toresearch issues in the strategic implications and management of databasesystems.Write clearly and concisely about topicsrelated to the strategic planning for database systems using proper writingmechanics and technical style conventions.