Homework answers / question archive / Your organization has requested that you create a new database to support organizational needs

Your organization has requested that you create a new database to support organizational needs

Computer Science

Share With

Your organization has requested that you create a new database to support organizational needs. Based on the information you provided in this week’s Discussion, create a database as defined in the database project.

The project must include at least five tables, and should have no more than 10 tables.

1. Define your database.
   • Describe the database, its purpose, why it was created, who it was created for, who the users are, and what type of processes and functions it will support.
   • List the entities of your database.
   • Present the business rules that determine connectivity, using the format reviewed in Week 2.
   • Draw the ERD; copy the drawing into your Word project document.
2. Create the database and tables for the database. Show all SQL statements. Include primary and foreign keys.-SEE BELOW

3. Insert data into each table. Show select statements and display the output of each table. Note: Student’s name must be inserted into table as part of the data!
4. Perform the SQL below:
   • Query one table and use WHERE to filter the results. The SELECT clause should have a column list, not an asterisk (*). State the purpose of the query; show the query and the output.
   • Get information from at least 3 tables in one statement, and provide the output using the Join operator. Use ANSI Join syntax. State the purpose of the query; show the query and the output. Add a screen shot of SS Management Studio showing the query and results.
   • Get information from 2 tables in one statement, and provide the output using the Left Outer Join operator. State the purpose of the query; show the query and the output. The outer join should be designed to retrieve information from the left table that has no matches in the right table. If that is not possible for your database, explain why.
   • Create a query using the IN keyword with a subquery. State the purpose of the query; show the query and the output.
   • Create a query using an aggregate function (i.e., min, max, avg, sum, count) and the GROUP BY command. State the purpose of the query; show the query and the output.
   • Create a query using an aggregate function (i.e., min, max, avg, sum, count) and the GROUP BY command using the HAVING clause to filter the aggregate results. State the purpose of the query; show the query and the output.
   • Update one row. State the purpose of the query; show the result set for the row(s) before the update; show the query; show the row(s) after the update.
   • Delete one row. State the purpose of the query; show the result set before the delete; show the query; show the result set after the delete.