BCIS 4620 – Spring 2009
Assignment #7
(40 points)
DUE: April 9, 2009, prior to your class period.
PART 1. -- RELATIONAL
DATABASE LOADS (20 PTS)
PART 2. --
RELATIONAL QUERIES (20 PTS)
This
is the second half of the table Creation and Load exercise
======================================================================
PART 1. REQUIREMENTS --
TABLE LOADS (20 POINTS)
READ
Alameda Construction Company Case in Windsor & Spence pages 95-102 with
APPENDIX C DATA (and other data that has been provided). Revisit the PROJECT AND REQUIREMENTS sections
very carefully. Examine the DATA in
Appendix C for understanding of the attribute DOMAINS. Review questions at the end of the case.
NOTE: You may need a 1" - 3-ring binder for
this assignment.
A. The List the Relations Report, again! Include the KEY fields (primary and foreign
keys) and ATTRIBUTE names. This is similar to the report from Homework 6. Be sure to update for any changes you have
made. Please, include at least one
intersection table.
B. The SQL CREATE TABLE and VIEW statements for
the tables and views needed to build this database. ONLY one CREATE TABLE or CREATE VIEW per SQL
file. Turn in the listings of the SQL
files. Be sure you include the most
recent versions. If you did not need to
revise your CREATEs from Homework 5, you may include them.
LOAD THE DATA INTO YOUR
DATABASE.
C. Turn in the SQL statements (or equivalent)
that you use to load data into your database.
You may use any tool you wish to load the data. Also turn in the TableViews
once the data is loaded. Be sure to
include at least one intersection table along with the tables it connects. See
Table of Contents for organization suggestions.
PART 2. REQUIREMENTS -- SQL
QUERIES/REPORTS (20 POINTS)
Read
the Alameda Case requirements on p. 102.
Pay particular attention to questions 5 and 6. Both of these questions outline several
"MUST" queries on the database, after loading the data in Part 1
above.
A. Design the queries/reports needed for each of
the simple objects (TABLE or VIEW) in the database. EMPLOYEE, SKILLS, PROJECT,
NEXT_OF_KIN, CODE_TABLES, etc.
These VIEWs should represent the JOINs of related tables needed to produce the objects that
appear in your SOM. Recall that an
object, as defined in Semantic Object Modeling, would include the data from
objects that are linked to an object.
For example, EMPLOYEE object would include the data from its related
“code” tables. Use ORDER BY clauses as
needed. Include most recent SOM diagram.
TURN
IN COPIES OF EACH OF THE ABOVE "SIMPLE OBJECT" LISINGS. You may use SQL SELECT statements to produce the
Listings. You may also use COBOL with embedded SQL!
B. PRODUCE THE FOLLOWING REPORT OUTPUTS. Indicate the source of the report (SQL or
COBOL). Turn in a listing of the SQL
query used and the corresponding CREATE VIEW … SQL code, along with the TableView printout.
1.
LIST
OF EMPLOYEES AND THEIR SKILLS SORTED BY SKILL TYPES with associated TRAINING (alphabetically).
2.
LIST
OF EMPLOYEES AND THE PROJECTS THEY ARE ASSIGNED TO, SORTED BY PROJECT NAME AND
THEIR START DATE.
3.
EMPLOYEE
NEXT OF KIN REPORT. Be sure to include
Employee Name, Address, Phone Number, and the Next-of-kin Name, Address and
Phone Number. Sort by Employee last
name, first name.
4.
AFFIRMATIVE
ACTION REPORT. Table/Report should
display the races represented in the database, along with employee counts and
percentages by ethnic group.
HINT:
This is an excellent time to use a more than one view, a subquery, or
both.
TURN
IN COPIES OF EACH OF THE ABOVE REPORTS.
SQL
or COBOL is permissible. Include both
the SQL or COBOL source code and TableView’s.
Assignment 7 --
Table of Contents
|
Tab |
Description |
Page |
|
1 |
SQL Creates and Loads (Part 1.A, B, C); Group as
follows: |
|
|
1 |
Relations List |
1 |
|
1 |
TableDesigner SOM Printout |
|
|
1 |
Employee Create, Load, and Table View, SQL View |
|
|
1 |
Skill_Info Create, Load, and Table View, SQL View |
|
|
1 |
Next-of-Kin Create, Load, and Table View, SQL View |
|
|
1 |
Project Create, Load, Table View, SQL View |
|
|
1 |
Salary Create, Load, and Table View, SQL View |
|
|
1 |
Training (if separate) Load, and Table View, SQL View |
|
|
|
|
|
|
1 |
Code Tables: Create, Load, and Table View, SQL View Following possible: |
|
|
1 |
Skill_Code, Race_Code, Education_Code, Primary_Seconday_Code |
|
|
1 |
Job_Titles_Code, Citizenship_Code, Training_Code |
|
|
1 |
Intersection Table Create, Load, and Table View, SQL View |
|
|
|
|
|
|
2 |
QUERIES/REPORTS, CREATE Object VIEWs (Part 2.A) |
|
|
2 |
1) EMPLOYEE VIEW: CREATE VIEW, SQL View & TableView |
|
|
|
Combines Employee + All related Code Tables, except SKILL & PROJECT |
|
|
2 |
2) SKILL VIEW: CREATE VIEW, SQL View & TableView |
|
|
|
Combines Employee + Skill + Skill_Codes (if used) |
|
|
2 |
3) PROJECT VIEW: CREATE VIEW, SQL View & TableView |
|
|
|
Combines Project + Employee |
|
|
2 |
4) NEXT-OF-KIN VIEW: CREATE VIEW, SQL View & TableView |
|
|
|
Combines Employee + Next_of_Kin |
|
|
2 |
5) INTERSECTION VIEW: CREATE VIEW, SQL View & TableView |
|
|
|
Combines Intersection Table with its related Tables |
|
|
2 |
6) SALARY VIEW: CREATE VIEW, SQL View & TableView |
|
|
Combines Salary + Employee + Salary_Codes (if used) |
||
|
2 |
7) MISCELLANEOUS VIEW: CREATE VIEW, SQL View & TableView |
|
|
|
Others you may need to build |
|
|
|
|
|
|
3 |
Report Outputs (Part 2.B) – SQL Code, TableViews |
|
|
3 |
Employee Skill Report (1) |
|
|
3 |
Project Assignment Report (2) |
|
|
3 |
Next of Kin Report (3) |
|
|
3 |
Affirmative Action Report (4) |
|
|
3 |
--- Other Views or Subqueries for Affirmative Action Report |
|