Write a sql join query using two or more basetables

Assignment Help PL-SQL Programming

Reference no: EM131903862

Project Assignment

Description

Note that this project must be unique in its design (E-R diagram) and implementation (SQL queries). You are not to copy or use any part of a database project that was previously submitted or appears on the Web, in a textbook, or otherwise made available via an external source. Contact your instructor if you have any questions regarding this requirement.

PART 1

(1) ProjectDescription.

Provide an overview of your project identifying the major components as part of a database design. The format is an executive summary to be presented to management for review.

(2) Entity-RelationshipDiagram.

Use the entity-relationship diagram (crow's foot) notation to design your database system. Be sure to:

(1) identify entity types; (2) describe relationships between or among entity types; (3) identify modality and cardinality for each relationship; (4) identify associative entities and intersection data; and (5) label primary and foreign key attributes.

Your entity-relationship diagram will have a minimum of six (6) entity types of which at least two (2) are associative entities with intersection data. The ERD is accompanied by a brief explanation of each component in the entity-relationship model.

Note: You can use Microsoft Visio, Word, PowerPoint, or Access as a diagramming tool. Please obtain approval from your instructor regarding the use of other diagramming tools.

PART 2

1. SQL Data DefinitionLanguage (DDL) and Base Tables with Data/Views.

1.1. Create a base table. Write the SQL to create a table based on one of your entities. Be sure to identify the primary key as part of your SQLsyntax.

1.2. Create a view. Write the SQL to create a view on the basetable.

1.3. Show the base tables with data. Show a base table along with six rows containing data for three entities in your entity-relationship diagram. (Display each table in the form of an MS Wordtable.)

2. SQL Insert/Update/Delete Manipulation.

2.1. Insert a row. Write a SQL query to insert a row into one of your basetables.
2.2. Update a row. Write a SQL query to change a data value in the insertedrow.
2.3. Delete a row. Write a SQL query to delete the row from your basetable.

3. SQL Data Queries. For 3.1 through 3.7, write the SQL query AND show the output using the data in your basetables.

3.1. Write a SQL query using a comparison operator (e.g., =, >,<).
3.2. Write a SQL query using the "AND" or "OR"operator.
3.3. Write a SQL query using the "BETWEEN" or "LIKE"operator.
3.4. Write a SQL query using a built-infunction.
3.5. Write a SQL query using the "GROUP BY"clause.
3.6. Write a SQL join query using two or more basetables.
3.7. Write a SQL subquery using two or more basetables.

PART 3

1. For the database concept you have chosen for Parts 1 and 2, provide thefollowing:

• Display your entities/tables in first normalform
• Display your entities/tables in second normalform
• Display your entities/tables in third normalform

You may need to rework your relationships to achieve this. For each form, provide the tables and the defining associations.

2. Next, define three business processes that will access a particular table and attribute. For example, "A customer login process will require accessing the CUSTOMER table and retrieving the CustomerID and CustomerPassword attributes". Provide detail on what the business process is and who is involved. For each business process, provide the SQL that would need to run to access it in the first, second, and third normal forms you provided in the previoussection.

3. Finally, choose a database optimization strategy, whether physical or logical, and describe how it could potentially benefit any or all of your normal form examples. Provide a comparison between a use case with and without this strategyimplemented.

Reference no: EM131903862

Questions Cloud

Write merge script to correct the missing calorie count info : Merge - Write MERGE script to correct the missing calorie count information. Use the cals.txt document provided.

Give some examples of expansion or growth opportunities : What actions do you think J&J and the other three firms should have taken when they received the stolen property from Stegora?

Write a c program to implement the function interface : Write a C program to implement the function interface below as an External function. Test your solution using the stub program shown below.

How much money do you expect to have at the end : How much money do you expect to have at the end of your investment horizon if you invest in the mutual fund?

Write a sql join query using two or more basetables : Write a SQL query using a built-infunction. Write a SQL query using the GROUP BYclause. Write a SQL join query using two or more basetables.

Create a linux server environment running apache web server : Create a Linux server environment, running an apache web server. Create five users on the server. One user will have administrative privileges.

Write a complete class for an object called a box : Write a complete Class for an object called a Box. The Box should have three integer instance variables, one for the length, one for the width and one for Box.

How much less can the borrower with a mortgage borrow : How much less can the borrower with a 6% mortgage borrow to keep the same monthly mortgage payment as the borrower with a 5%.

Implement all of the functions in bits c file : Your task is to implement all of the functions in bits.c. All instructions regarding individual functions are in the comments above each function.

User Account

All Pages