SEIS630-02 database management systems

GRADUATE PROGRAM IN SOFTWARE

Your company wants to store the information about their ERD models in a database. Your job is to design and create a relational database that can store the objects listed on the handout ERD diagram. Use the information contained in the ERD diagram to load your database.

Your project consists of the following:

1. Step 1: Conceptual Model Creation - Develop the ERD model that represents the conceptual model for this database BASED ON THE PICTURE. You can create this model using modeling software in the lab or any modeling tool that you might have (such as ERWin, Visio, etc.). Hand in a printout of the Conceptual Model for your tables - due 10/1/2015.
2. Step 2: DDL Generation - Generate a physical model for your database engine of choice (Oracle, Sybase, Informix, DB2 and SQL Server. Do not use Access for this assignment). Generate a set of DDL statements for your tables and save to a text file. Hand in a printout of the DDL (create table, any constraints or index) statements – due 10/22/2015.
3. Step 3: Create the Tables- Create the tables from the DDL generated in the previous step. There is nothing to hand in for this step.
4. Step 4: Load Data into the Tables - Load your tables with the information from the ERD Model in the Load This Data PICTURE section below. There is nothing to hand in for this step.
5. Step 5: Create the Views - Create the views described in the Create These Views section below. Check the correctness of the views by selecting from them and making sure they return the right results. Hand in a printout of the create-view statements and the answers from the screen generated from the database software – due 11/19/2015.
6. Step 6: Create the Queries – Transform the English questions listed in the Create These Queries section below into SQL queries and run those queries against the tables and views you created above. Hand in a printout that includes each SQL statement along with the answers to the queries returned tothe screen. – due 12/17/2015.
7. Step 7: Provide an SQL Explain Plan – Provide an SQL execution Explain Plan for Query # 24. The Explain Plan shows each step in how the query is executed. All database vendors provide some sort of basic SQL tracing option. Check your documentation. Oracle Explain Plan documentation and SQL scripts are included in the course scripts. Hand in a printout that includes the SQL statement for Query# 24 along with the contents of the Explain Plan table for Query #24. – due 12/17/2015.
8. Step 8: Create a Deadlock – This is the final step in the project. Using any SQL statements against your tables, create a deadlock. Provide each SQL you used and a printout of what you see on the screen.You may attach any trace logs of the deadlock as well. – due 12/17/2015.

Create These Views - limited to the items IN THE PICTURE ONLYUse the Create View statement to create the following views:

1. All_Ents: This view returns the name of all entities in the picture.
2. All_Atts: This view returns the name of all attributes in the picture.
3. All_Keys: This view returns the name of all TABLE and ENTITY primary keys in the database.
4. All_Rels: This view returns the name of all relationships in the picture.
5. EntAtt: This view returns the name of every entity and the name of all attributes it contains in the picture (for each entity, print each attribute on a separate line).
6. RelEnt: This view returns the name of each relationship and the names of two entities it connects in the picture(print each relationship name on a separate line).
7. RelAtt: This view returns the name of every relationship and the name of all attributes it contains in the picture (for each relationship, print each attribute on a separate line).
8. AttDatatype: This view returns the name of each attribute from the picture and the name of the data type for each attribute.
9. EntKey: This view returns the name of Pkeys for each entity in the picture. One line per entity.
10. KeyAttr: This view returns the entity key names and the entity attributes that form the key in the picture (one attribute per line per key).

Create These Queries - limited to the items IN THE PICTURE ONLY

1. Print the name of all entities in the picture.
2. Print the name of all attributes in the picture.
3. Print the name of all relationships and the two entities they relate in the picture.
4. Print the name of all relationships and the two entities they relate and the cardinality for each side of the relationship in the picture.
5. Print the name of all relationships that have a cardinality of 1-to-1 in the picture.
6. Print the name of all relationships that have a cardinality of 1-to-M in the picture.
7. Print the name of all relationships that have a cardinality of M-to-N in the picture.
8. Print the name of all relationships that have attribute(s) as well as the name(s) of the attribute(s) in the picture.
9. Print the name of all relationships that do not have attribute(s) in the picture.
10. Print the name of all entities that have a super-type and the name of the super-type entity in the picture.
11. Print the name of all entities that have sub-type(s) and the name of the sub-type entities in the picture.
12. Print the name of each entity that has a Pkey including the name(s) of the attribute(s) that form the Pkey in the picture.
13. Print the name of each entity that does not have a Pkey in the picture.
14. Print the names of all attributes in the picture and the attribute data types.
15. Print the names of all derived attributes and the attributes they derive from in the picture.
16. Print the names of all attributes in the picture that have a date data type.
17. Print entity Person's attributes names in the picture.
18. Print relation Earns attributes names in the picture.
19. Print the names of all entities that are directly related to entity Student (directly means via only one relationship) in the picture.
20. Print the names of all entities that are indirectly related to the entity Student (related via two relationships) in the picture.
21. Print the names of attributes for entities College and Department (just the attribute names) in the picture.
22. Print the names of all entities that are participating in any relationship that Section participates in in the picture.
23. Print the name of all sub-types of entity Person in the picture.
24. Print the name of all entities that have exactly three attributes in the picture.
25. Print the name of all composite attributes and the attributes they contain in the picture.
26. Print label ‘Parent’ followed by the name of the parent entity and the label ‘Child’ followed by the name of the child entities for each disjoint type-sub-type relationship in the picture.
27. Print the name of each table and the columns within that table- by printing the table name once followed by each column within that table on a separate line.

Provide an SQL Explain Plan of Query # 24 above.

1. For Oracle Explain Plan, complete the following preparation and execution steps. For SQL Server or other database engine, please follow the vendor documentation to complete this task.
2. Within SQL*Plus, execute the spool command to save all your work that prints to the screen: SQL> spool c:\temp\myexplain.
3. Within SQL*Plus, execute the utlxplan.sql script as the schema owner of the tables and the user schema you will run the queries under. At the SQL*Plus prompt SQL> @c:\temp\utlxplan.sql. This script creates the table called PLAN_TABLE.
4. Run an analyze on the tables you access in Query # 24. To run analyze from the SQL*Plus prompt SQL> analyze table TABLENAME compute statistics;
5. Load your SQL statement into the PLAN_TABLE by entering the following command at the SQL*Plus prompt SQL>explain plan for YOUR SELECT STATEMENT HERE;
6. Run the utlxpls.sql or utlxplp.sql script from the SQL*Plus prompt SQL>@c:\temp\utlxpls.sql;
7. Within SQL*Plus, stop the spool command to save all your work that prints to the screen: SQL> spool off.
8. All you need hand in is a printout of the spool file you saved above in step 2.

Create a Deadlock

1. We studied deadlocks in the last part of the course. I gave some SQL examples of how to create a deadlock. Using any SQL statements against your tables, create a deadlock against the database you built for your project. Provide each SQL you used and a printout of what you see on the screen. You mayattach any trace logs of the deadlock as well. You may create any type of deadlock you wish. Yes, it is vague – that’s deliberate.