4: Database Modeling and Normalization
Imagine that you work for a consulting firm that offers information technology and database services. Part of its core services is to
optimize and offer streamline solutions for efficiency. In this scenario, your firm has been awarded a contract to implement a new
personnel system for a government agency. This government agency has requested an optimized data repository for its system which will enable the management staff to perform essential human resources (HR) duties along with the capability to produce ad hoc reporting features for various departments. They look forward to holding data that will allow them to perform HR core functions such as hiring, promotions,policy enforcement, benefits management, and training.
Using this scenario, write a three to four (3-4) page paper in which you:
Determine the steps in the development of an effective Entity Relationship Model (ERM) Diagram and determine the possible iterative
steps / factors that one must consider in this process with consideration of the HR core functions and responsibilities of the client.
• Analyze the risks that can occur if any of the developmental or iterative steps of creating an ERM Diagram are not performed.
• Select and rank at least five (5) entities that would be required for the development of the data repositories.
• Specify the components that would be required to hold time-variant data for policy enforcement and training management.
• Diagram a possible 1:M solution that will hold salary history data,job history, and training history for each employee through the use of
graphical tools in Microsoft Word or Visio, or an open source alternative such as Dia. Note: The graphically depicted solution is
not included in the required page length.
• Plan each step of the normalization process to ensure the 3NF level of normalization using the selected five (5) entities of the personnel
database solution. Document each step of the process and justify your assumptions in the process.
• Diagram at least five (5) possible entities that will be required to sustain a personnel solution. The diagram should include the
following:
1. Dependency diagrams
2. Multivalued dependencies
Note: The graphically depicted solution is not included in the required page length.
5.Tour Operator Agency Database
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.
A multinational tour operator agency has gained new business growth in the North American market through the use of social media. Its
operation has expanded by 50% within six months and the agency requires an enhanced data management strategy to sustain their
business operations. Their existing data repository for its reservation processing system is limited in business intelligence and
reporting functionalities. The tour operator seeks a database management specialist to assist them in leveraging their data sources
to enable them to forecast and project tour sales appropriately.
Imagine that you have been hired to fulfill their need of enhancing the data repository for their current reservation processing system.
Upon reviewing the system, you find that the data structure holds redundant data and that this structure lacks normalization. The
database has the following characteristics:
• A table that stores all the salespersons. The table holds their employee id, first name, last name and "Tours sold" field. The "Tours
sold" field is updated manually.
• A table that stores tour customer data and tours sold. The table holds customer name, address, city, state, zip code, tour(s) selected,
number of persons in tour, and total amount paid. The current structure will show the customer more than once, if the customer books
multiple tours.
• A tour table that is used as a tour rate sheet which holds the tours offered and the cost per person. Tour rates vary every three (3)
months depending on the tourist season.
• Write a three to four (3-4) page paper in which you propose an enhanced database management strategy. Your proposal should include the following:
• Design a data model that will conform to the following criteria:
1. Propose an efficient data structure that may hold the tour operator's data using a normalization process. Describe each step of
the process that will enable you to have a 2nd Normal Form data structure.
2. Create naming conventions for each entity and attributes.
3. Conclude your data model design with an Entity Relationship Model (ERM) that will visually represent the relationships between the
tables. You may make use of graphical tools in Microsoft Word or Visio, or an open source alternative such as Dia. Note: The
graphically depicted solution is not included in the required page length.
• Construct a query that can be used on a report for determining how many days the customer's invoice will require payment if total amount due is within 45 days. Provide a copy of your working code as part of the paper.
• Using the salesperson table described in the summary above,complete the following:
1. Construct a trigger that will increase the field that holds the total number of tours sold per salesperson by an increment of one (1).
2. Create a query that can produce results that show the quantity of customers each salesperson has sold tours to.
• Support the reasoning behind using stored procedures within the database as an optimization process for the database transactions.
6: SQL Concepts and Database Design
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:
• 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.
• 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.
• 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