E-r diagram and sql queries project.

Project Description

 

Please 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.

DELIVERABLES:

·         PART 1: (40 points)

o   Project Description (10 points)

o   Entity-relationship diagram (30 points)

·         PART 2: (60 points)

o   SQL Data Definition Language (DDL) and base tables with data (10 points)

o   SQL Data Manipulation Language (DML) queries (50 points)

·         PART 3: (50 points)

o   Display Entities/Tables (30 points)

o   Business Processes (15 points)

o   Optimization Strategy (5 points)

PART 1 (Due Week 3)

(1) Project Description. 10 points.

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-relationship Diagram. 30 points.

Use the entity-relationship diagram (crows-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 (Due Week 5)

1.       SQL Data Definition Language. Three (3) points each for 1.1 and 1.2; and four (4) points for 1.3.

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 SQL syntax.

1.2.    Create a view. Write the SQL to create a view on the base table.

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 Word table.)

2.       SQL Data Manipulation Language (insert, update, and delete). Five (5) points each for 2.1, 2.2, and 2.3.

2.1.    Insert a row. Write a SQL query to insert a row into one of your base tables.

2.2.    Update a row. Write a SQL query to change a data value in the inserted row.

2.3.    Delete a row. Write a SQL query to delete the row from your base table.

3.       SQL Data Manipulation Language for data retrieval. Five (5) points each for 3.1 through 3.7

For 3.1 through 3.7, write the SQL query AND show the output using the data in your base tables.

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-in function.

3.5.    Write a SQL query using the “GROUP BY” clause.

3.6.    Write a SQL join query using two or more base tables.

3.7.    Write a SQL subquery using two or more base tables.

 

PART 3 (Due Week 7)

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

·         Display your entities/tables in First Normal Form (10 points)

·         Display your entities/tables in Second Normal Form (10 points)

·         Display your entities/tables in Third Normal Form (10 points)

You may need to rework your relationships to achieve this. For each form, provide the tables and the defining associations. An example of the table overview can be seen on page 184 of the Fundamentals of Database Design textbook. An example of defining associations can be seen on page 185.

Next, define three (3) 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 be run to access it in the first, second, and third normal forms you provided in the previous section.

(15 points)

Finally, choose an optimization strategy as defined in Chapter 8, 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 strategy implemented.

(5 points)







Place your order
(550 words)

Approximate price: $22

Calculate the price of your order

550 words
We'll send you the first draft for approval by September 11, 2018 at 10:52 AM
Total price:
$26
The price is based on these factors:
Academic level
Number of pages
Urgency
Basic features
  • Free title page and bibliography
  • Unlimited revisions
  • Plagiarism-free guarantee
  • Money-back guarantee
  • 24/7 support
On-demand options
  • Writer’s samples
  • Part-by-part delivery
  • Overnight delivery
  • Copies of used sources
  • Expert Proofreading
Paper format
  • 275 words per page
  • 12 pt Arial/Times New Roman
  • Double line spacing
  • Any citation style (APA, MLA, Chicago/Turabian, Harvard)

Our guarantees

Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.

Money-back guarantee

You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.

Read more

Zero-plagiarism guarantee

Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.

Read more

Free-revision policy

Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.

Read more

Privacy policy

Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.

Read more

Fair-cooperation guarantee

By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.

Read more

Order your essay today and save 10% with the coupon code: best10