Project #17224 - Advance Databases SQL

 

All the projects must be in one template, Project one will show you how to format everything, don’t worry about the name, and course number, and date, I will go over everything, and make minor modification’s once everything is complete, also project #3 information is in the attackment as well, I thank you for everything, and this must be completed by Next Sunday November 24, 2013.

 

Project # 1

The current database that your company is using keeps track of all of the customers and suppliers as well as products offered for sale, but the current environment has all of this data spread out across multiple different databases and it is difficult to see a complete picture of the environment. You have been tasked to consolidate the environment into a single database environment with the end goal of adding a data warehouse for the company.

Your manager is excited with the project description, is anxious to have a new database built for the company, and more excited that at the end of the project he will also have a data warehouse to help make strategic decisions. To start, your manager would like to understand the benefits of following a formal design methodology, especially with respect to database design. Describe how and why the company can benefit from spending time planning at the beginning of the project, instead of just jumping in and developing the applications to meet the perceived needs. To ensure that you give the best response, you choose to describe the 3-level ANSI-SPARC architecture and want to make sure you discuss how the use of this will promote data independence to save time in the long run.

You also want to take this opportunity to answer any potential questions about personnel needs with this new database environment. Describe the roles of a data administrator (DA) and a database administrator (DBA); describe the job functions of each and how the tasks they each perform differ. Would you recommend that your company has one person to perform both tasks, or should it hire two people?

Your paper should contain the following information:

  • Describe the 3-level architecture.
  • Describe data independence.
  • Talk about the differences between a DA and a DBA.
  • Discuss the pros and cons of having a separate DA and DBA or the need for 1 person doing it all.

You also should take this opportunity to create the template for your entire project, and create a Word document that you will add to for each remaining assignment. The document should follow this format:

  • Advanced Database Systems Project document shell
  • Use MS Word
  • Title Page
    • Course number and name
    • Project name
    • Student name
    • Date
  • Table of Contents
    • Use autogenerated TOC
    • Separate page
    • Maximum of 3 levels deep
    • Update fields of TOC so it is up-to-date before submitting project
  • Section headings (create each heading on a new page with TBD as the content, except for the sections listed under "New content" in each week's assignment)
    • Project Outline
    • The Database Models, Languages, and Architecture
    • Database System Development Life Cycle
    • Database Management Systems
    • Advanced SQL
    • Web and Data Warehousing and Mining in the Business World

Each week, you will add to this document and submit it for grading. As a preview, each section will contain the following:

  • The Database Models, Languages, and Architecture (Week 1: IP)
    • A description of the 3-level ANSI architecture model
    • A description of data independence
    • The difference in responsibility between:
      • Data administrator
      • Database administrator
  • Database System Development Life Cycle (Week 2: IP)
    • A completed enhanced entity-relationship diagram
    • A description about the relationship setup and multiplicity
  • Database Management Systems (Week 3: IP)
    • The normalization of a given logical data model to Boyce-Codd Normal Form
    • A logical data model for the Enhanced ERD from IP2
  • Advanced SQL (Week 4: IP)
    • A physical data model for your Enhanced ERD in a DBMS of your choice, including:
      • The DDL to create the tables
      • The DDL to create the primary and foreign keys
      • DML to manage data for the tables
      • 3 SELECT statements (one will be a JOIN)
  • Web and Data Warehousing and Mining in the Business World (Week 5: IP)
    • The design and DDL to create a star schema for a data warehouse for the database previously designed
    • A description of the ETL process

Add the discussion about the 3-layer ANSI architecture, data independence, and the different administrators to the section titled "The Database Models, Languages, and Architecture."

Name the document CS352__IP1.doc.

Submit the document for grading.

 

Project #2

After you have analyzed the existing material used by the company for their day-to-day duties, the current Access database, and the additional requirements that the current system does not meet, the following requirements entity/attributes have been compiled:

  • Customer information is tracked using ‘standard’ attributes.
  • A customer may purchase products or services; if they purchase products or services, the following is tracked:
    • Product purchased
    • Date of purchase
    • Total of purchase
  • A supplier may sell products or services; if they sold products or services, the following is tracked:
    • Product sold
    • Date of sale
    • Total of sale
    • Is item purchased available for resale
  • Your company wants to keep the number of tables storing address information to a bare minimum (read this as 1).
  • Customers can be both a "supplier" and "someone that purchased services," and it is not required that they be either.
  • Employee information is tracked using standard attributes.
  • An employee will either be considered customer interfacing or internal support.
  • If the employee is customer facing, the following information is tracked:
    • Customer for interaction (note that a customer will only interface with a single employee)
    • Product specialty
    • Hours of training
    • Commission rate
  • If the employee is internal support, then the following is tracked:
    • Salary
    • Support area
  • An employee can be either customer facing or internal support, but not both.
  • Your company wants to keep the number of tables storing generic employee information to a bare minimum (read this as 1).
  • Finally, the company wants to track products or services offered. This should be a single table with typical attributes that describe inventory.
  • Every customer that either makes a purchase or sells goods to the company must be associated with an employee.
  • Every transaction that a customer makes with the company is stored/tracked, A customer may buy or sell many products, and a product is sold to more than one customer.

Create an enhanced ERD to meet these requirements. Ensure that entities are properly defined and appropriate attributes are listed for each entity. Also, ensure that all entities are properly related.

Add your ERD as a screenshot to a Word document, and provide supporting discussion about the need for the enhanced diagram tools and the reasoning behind the multiplicity for the relationships.

Add the enhanced ERD and the discussion about the relationship multiplicity to your project template to the section titled "Database System Development Life Cycle."

Name the document CS352__IP2.doc, and submit the document for grading.

 

Project #3

Phase 3 IP has 2 parts:

Part 1: Analyze the following table (see the Word document called "CS352 - IP3") and reorganize the table into Boyce-Codd Normal Form, at each step describing what is needed to move to the next Normal Form and why each step meets the Normal Form requirements.

 

 

Charity
ID

Charity Name

Charity
Location

POC Name

POC ID

Tel Extn.

Customer ID

Customer Name

Date

Contribution Started

No of Month

Date Places

Expected Contribution End

 

 

 

 

 

 

 

 

 

  • Show unnormalized table given and progression through the normal forms up to Boyce Codd in logical data models.
  • Include explanation of how each normal form is met as you progress through the process of breaking down this unnormalized table to tables meeting Boyce Codd normal form.

Part 2: In addition, transform your data model (your EERD created in phase 2 IP) into a logical model, to third normal form. Describe why each table is in third Normal Form. In your logical data model identify the primary keys in each table as bolded and underlined and each foreign key asitalicized and underlined.

Submission for phase 3 IP includes:

  • Logical Data Model for the supplied table(Part 1) with a description of how it moved through UNF to 1NF to 2NF to 3NF and Boyce Codd.
  • Logical Data Model for Part 2 with a description of how each table is in third normal form.

Add both parts described to the project template section titled "Database Management Systems."

Name the document CS352__IP2.doc, and submit the document for grading.

 

Project # 4

Using the DBMS you chose in the previous Discussion Board assignment, download and install that software to prepare for the Database and Data Model to be created. Once the software is running and the database is available, complete the following:

Create the physical data model for the logical data model that you submitted in IP3. This should include all of the data definition language SQL.

Your submission should include all DDL needed to:

  • Create the tables
  • Create the primary keys
  • Create the foreign keys
  • Add DML statements to:
    • Add data of 1 customer who buys from the company
    • Provide the DML to add 1 employee who interacts with customers
    • Give DML to change data of the employee, giving the commission a 25% increase
    • Give DML to delete the customer and employee data
  • Write 3 SELECT statements:
    • To select the customer details
    • To select the employees details
    • To show which employee services which customer

Add the SQL for the DDL, DML, and SELECT statements to the project template section titled "Advanced SQL."

Name the document CS352__IP4.doc.

Submit your Word document and make sure that it contains the following:

  • Screenshot of the ERD, logical data model from previous assignments.
  • The DDL to create the tables, including the table definition and the primary and foreign key definitions.
  • SQL to add data to the tables
    • Add data of 1 customer who buys from the company
    • Provide the DML to add 1 employee who interacts with customers
    • Give DML to change data of the employee, giving the commission a 25% increase
    • Give DML to delete the customer and employee data
  • Write 3 SELECT statements:
    • To select the customer details
    • To select the employees details
    • To show which employee services which customer

 

Project #5

In addition, submit the star schema and the DDL to create the Star schema for the data warehouse. You want a single Fact table to track all orders with the following dimensions:

  • Time
  • Customer
  • Product
  • Employee

Be sure to include all DDL including primary and foreign keys; feel free to create new or needed primary keys. Finally, a specific and detailed discussion about the ETL process is to be used to move data from the OLTP environment to the data warehouse.

Your submission should include the following:

  • A description on your approach
  • The features of your enhanced ERD
  • A discussion about handling the M:M relationship between customer and products
  • The approach used to ensure 3NF
  • Required SQL statements for the database
  • Star schema for the fact table and its 4 dimension tables
  • The DDL for the Star schema for the data warehouse
  • A description of the ETL

Add the Data Warehouse Design and discussion about the ETL process to the project template section titled "Web and Data Warehousing and Mining in the Business World."

Name the document CS352__IP5.doc.

Submit your Word document and make sure that it contains:

  • A Screenshot of the ERD, logical data model from previous assignments.
  • The DDL to create the tables, including the table definition and the primary and foreign key definitions
  • 1–2  pages describing the solution
  • Star Schema
  • DDL for a Star schema
  • 2-3 paragraphs about the ETL process

Subject Computer
Due By (Pacific Time) 11/24/2013 12:00 am
Report DMCA
TutorRating
pallavi

Chat Now!

out of 1971 reviews
More..
amosmm

Chat Now!

out of 766 reviews
More..
PhyzKyd

Chat Now!

out of 1164 reviews
More..
rajdeep77

Chat Now!

out of 721 reviews
More..
sctys

Chat Now!

out of 1600 reviews
More..
sharadgreen

Chat Now!

out of 770 reviews
More..
topnotcher

Chat Now!

out of 766 reviews
More..
XXXIAO

Chat Now!

out of 680 reviews
More..
All Rights Reserved. Copyright by AceMyHW.com - Copyright Policy