For Project Part 2, I Would Like You To Build The Following

For Project Part 2 I Would Like For You To Build The Following Databa

For project part 2, I would like for you to build the following database shown in Project Part 2 ERD in DB2. Then I will ask you to answer some questions using SQL Queries. Insert 5 rows of data into your tables (you make it up), except for client #1 has to be Marvel Studios, contact Stan Lee, phone. Remember to build and populate one table at a time. Start with your tables that do not have FK's to avoid errors.

QUERIES! Provide a listing of all the projects for Stan Lee. Create a query that uses a COUNT function. Create a query that uses a SUM function. Create a query that uses either a MIN or a MAX function.

Create a query that uses an INNER JOIN. Create a query that uses either a LEFT or a RIGHT OUTER JOIN. HINT: Some of you have an index named X1AC (You would normally name the account index this). So if you get an error when creating your index, just change the last character of the name! To turn in your assignment, please take screenshots of: The successful output for the tablespaces, tables, and indexes. The successful data insert statements for each table. The successful queries and their outputs. Paste the images into word and then save it as a .pdf. No other file types will be accepted.

Paper For Above instruction

The task involves designing and creating a database in IBM DB2 based on a specified Entity-Relationship Diagram (ERD). The process requires building and populating tables with sample data, executing various SQL queries, and documenting the results through screenshots. This comprehensive assignment aims to assess proficiency in database creation, data manipulation, and SQL querying, particularly involving functions and joins.

Firstly, the database schema must be constructed adhering to the ERD provided in Project Part 2. The tables should be created in an order that respects the foreign key constraints; tables lacking foreign keys should be created first. Once the tables are established, five rows of sample data should be inserted into each table, with the exception of specific constraints—namely, the client #1 must be "Marvel Studios," with a contact named "Stan Lee" and an associated phone number. This helps in managing referential integrity and meeting specific client data requirements.

Following the database setup, the next phase involves executing SQL queries to extract meaningful insights. One query should list all projects associated with Stan Lee, identified via his contact or client ID. The subsequent queries should demonstrate the use of aggregate functions: COUNT to enumerate projects, SUM to calculate total project costs or similar metrics, and MIN or MAX to identify the smallest or largest values within data attributes such as project duration or budget.

Advanced SQL joins are also necessary to illustrate relational data handling. An INNER JOIN query should link relevant tables, such as projects and clients, to fetch combined data where relationships exist. Additionally, a LEFT or RIGHT OUTER JOIN should be used to demonstrate handling of partial data relationships, especially highlighting cases where related records may be absent on one side of the join.

Additionally, indexing is an important aspect; it is hinted that if an index named X1AC causes errors during creation, modifying its name should resolve the issue. Proper creation and subsequent screenshots of the tablespaces, tables, indexes, data inserts, and query outputs are required. The final deliverable includes assembling these screenshots into a Word document, converting it to a PDF format for submission, ensuring all steps are well-documented and clearly presented.

Paper For Above instruction

The process of building a relational database in IBM DB2 based on a specified ERD involves careful planning, execution, and validation. This assignment emphasizes practical skills in database design, data manipulation, and SQL proficiency, particularly in employing aggregate functions and table joins to extract valuable insights from data.

Initially, the creation of tables must respect relational integrity. Tables without foreign key dependencies should be built first, followed by tables with foreign keys to prevent creation errors. Once the schema is established, populating the tables with data requires attention to constraints—most notably, ensuring that client #1 is preset as "Marvel Studios," with "Stan Lee" as the contact and a specified phone number. This data setup allows for consistent querying and demonstrates control over data integrity.

The next key component involves crafting SQL queries that showcase different capabilities. Listing all projects for Stan Lee involves a SELECT statement with filtering by client or contact details. Use of the COUNT function provides a total number of projects linked to him, while SUM calculates aggregate values such as total project costs, and MIN or MAX identify extreme values (e.g., shortest or longest project durations). These functions highlight aggregate data analysis within relational databases.

Joins are instrumental in combining data from multiple tables. An INNER JOIN will fetch records where related data exists in both tables—perhaps projects and clients—allowing comprehensive views. In contrast, a LEFT or RIGHT OUTER JOIN showcases the ability to handle incomplete relationships, returning all records from one table and matching records from another, with nulls where matches are absent. This demonstrates a nuanced understanding of relational data structures and their practical application.

Another technical consideration pertains to indexing—named indices like X1AC should be created carefully, and if errors occur, renaming during creation can resolve issues. Proper documentation through screenshots of the successful creation of tablespaces, tables, indexes, and data inserts is critical for validation. These visual evidences affirm that the database setup and data entry processes were correctly executed.

The culmination of this assignment is compiling all these screenshots into a Word document, which is then converted into a PDF file for submission. The comprehensive documentation exemplifies not only technical proficiency but also the procedural clarity necessary for effective database management and reporting. This workflow ensures the database functions correctly, queries produce the expected outputs, and all findings are appropriately documented and accessible for review.

References

• Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems (7th ed.). Pearson.