Database Design, Implementation, and Management Project

Project Description

(1)Project Description.
In this project you will be going through the entire process of database design, implementation, and management from start to finish. This project is a three-part series. In Part 1, you will start by creating diagrams and models for a business or organization. In Part 2, you will translate them to tables, perform queries, and finally, in Part 3, you will normalize and optimize it.

To begin, you need to identify a business or organization. This can be an existing one or something you make up yourself. The only limitation is that it should not be a university or educational system as those examples are used throughout the class. If you have questions about what makes a good test-case, please reach out to your instructor.

The example you choose will be the one you use for all three parts. Databases are dynamic objects, so you may see earlier parts evolve as you work on later sections. Just be sure to clarify how you have changed your design. While not required, it is good to include previous parts to give added context to your decisions.

You also do not need to use a DBMS to implement your solution in Part 2, although it is strongly advised to use one. MS Access and MySQL are good introductory ones to consider. You can also use testing tools like SQL Fiddle to try out your solutions.
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.
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.

1.SQL Data Definition Language (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 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 Insert/Update/Delete Manipulation.
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 Queries. 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.