PART 2
1. SQL Data Definition Language.
1.1. Create a base table. Write the SQL to create a table based on one ofyour 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).
2.1. Insert a row. Write a SQL query to insert a row into one ofyour 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. 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 SQLjoin query using two or more base tables.
3.7. Write a SQL subquery using two or more base tables.
PART 3
1. For the database concept you have chosen for Parts1 and 2, provide the following:
Display your entities/tables in first normal form
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 (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 run
to access it in the firstl secondI and third normal forms you provided in the previous section.
3. Finally, choose a database optimization strategy, whether physical or logical, and describe how it could potentially benefit
any or all ofyour normalform examples. Provide a comparison between a use case with and without this strategy
implemented.