BCIS 4620 –
Spring 2009
Assignment
#6: NetExpress SQL –
Relational DB
Design –CREATE TABLES
(40
points)
===============================================================
DUE: March 28 prior to your class
period.
This assignment builds on the SOM assignment from Homework 5. Use the answer key and corrections from that assignment to improve your database design. You will be given some additional tables (See website --Project.htm (Names and starting dates), Salary.htm) to add to your SO Model from Homework 5. Homework 5 was actually the pre-first stage of your final project; this is the official first stage of your final semester project.
In this exercise you will be CREATING the tables you need for the final project. You will be designing the actual database structure, but DO NOT actually load the data—yet!
CAUTION: The design is critical to the success of your system. DO NOT underestimate the importance of this assignment, nor the amount time it will take to complete this assignment.
Use TableDesigner® to draw your Semantic Object diagram(s) and generate the SQL statements. Use any E-R Diagramming tool (e.g. ACCESS Relationship Diagrams) you are most familiar with to draw the Entity-Relationship Diagram.
Note: TableDesigner generates the Relation Diagram in ACCESS along with the database itself. If you plan to do this you may use the Create/Database option in TableDesigner®. DO NOT use the Create/Database option until you are ready to go to the Database creation stage. Save a backup copy of your TableDesigner® SOM model (.apm file) before you create the database.
REQUIREMENTS
GENERAL (2 points:
organization; table of contents, 3-ring binder, tabs, etc.)
Read
1. SEMANTIC OBJECT
DIAGRAMS (16 points)
Identify the semantic objects in the case. Create the Semantic Object diagrams that will meet the design specifications in the case. I recommend you use TableDesigner® Version 1.0.
State all significant assumptions about DOMAINS for each RELATION. For example: 1) Only one next of kin per employee; 2) Only the last three years of employee work history data will be stored online; 3) Only one primary skill per worker; any number of secondary skills; etc. This can be done in a separate Word document. You may also include domain restrictions in the “properties of domains” window in TableDesigner® along with field type and size information. Print the Object Reports that shows DOMAIN properties.
In addition to objects suggested in Homework 5 key, you are required to include the following objects: Salary and additional Code tables (Training Codes, Education Codes, Race codes, etc.). Turn in the following for part 1 of this assignment:
a. The SOM Diagram report
b. SOM Object Report (Object list report; one page for each Object)
c. Domain Restrictions/Limitations Report (Word .doc)
d. Printout of SQL Generated by TableDesigner®
2.
ENTITY-RELATIONSHIP DIAGRAMS (ERD) AND RELATION LISTS (7
points)
Complete and turn in the following:
a. Draw the E-R Diagram (normalized) for your database design. You may use the CASE tool or drawing tool you are most familiar with, such as ACCESS or Oracle's Designer® tool. Be sure to correct the primary keys and foreign keys to agree with your RELATIONS listed in Part 2.b below.
Note: TableDesigner® actually may be used to generate an ACCESS relation diagram!
b. List the RELATIONS and include the KEY field and ATTRIBUTE names. You may use reports generated by the software to help you with this part of the assignment. However, I want you to actually create a one page summary of the Relations using the relation notation found in Kroenke. For example:
EMPLOYEE(EMPNO, LastName, FirstName, ...deptno, … . )
Use italics to denote foreign keys and bold or underlines for primary keys. You will have two documents to turn in for part 2.b:
1) The one-page Relation List Summary (best done in WORD)
2) The ER Diagram properties report from ACCESS (Tools/Analyze/Documenter)
3. SQL CREATE STATEMENTS (SQL and Table
View; 15 points)
Type and turn in the SQL CREATE table statements that will be needed to build this database. Use the EXPORT SQL file feature in NX SQL Wizard. Please store them in a SQL type file. Print a copy of each SQL file and a copy of the Table View (portrait or landscape, accordingly; no data) for each table.
DATA ENTRY IS NEITHER REQUIRED NOR RECOMMENDED AT THIS
TIME!!
**Be sure to denote PRIMARY KEYs in the CREATE or ALTER statements. Foreign key CONSTRAINTS are NOT recommended at this time. At least one “true” intersection table is required.
Note: You will need to modify the Generated SQL Code for SQL/DS. Be sure to include at least four (4) code tables. (See Table of Contents below.)
Use a separate “SQL” file for each CREATE TABLE or VIEW statement. YOU WILL LOSE 5 points if you put all the CREATE TABLE statements into only one SQL file!
GOOD luck, Dr. B.
Assignment 6
Alameda Construction Co.
Table of Contents
|
Tab |
Description |
Page |
|
|
SEMANTIC OBJECTS
DIAGRAMS |
|
|
1 |
TableDesigner® SOM Diagram Report |
|
|
1 |
TableDesigner® Object List Report |
|
|
1 |
Domain Restrictions/Limitations Report (Word document) |
|
|
1 |
SQL Generated by TableDesigner® |
|
|
|
ENTITY-RELATIONSHIP
DIAGRAMS |
|
|
2 |
Relations List Summary (Word document) |
|
|
2 |
Normalized E-R Diagram in Access (Access) |
|
|
2 |
ER Diagram Properties Report (Access) |
|
|
|
SQL CREATE TABLE
STATEMENTS |
|
|
|
Sample of Suggested Tables &
Views |
|
|
3 |
EMPLOYEE SQL |
|
|
3 |
EMPLOYEE Table View |
|
|
3 |
SKILL_INFO SQL |
|
|
3 |
SKILL_INFO Table View |
|
|
3 |
NEXT_OF_KIN SQL |
|
|
3 |
NEXT_OF_KIN Table View |
|
|
3 |
PROJECT SQL |
|
|
3 |
PROJECT Table View |
|
|
3 |
SALARY SQL |
|
|
3 |
SALARY Table View |
|
|
|
Code Tables (at least four of the
following) |
|
|
3 |
TRAINING_CODES SQL |
|
|
3 |
TRAINING_CODES Table View |
|
|
3 |
SKILL_CODES SQL |
|
|
3 |
SKILL_CODES Table View |
|
|
3 |
EDUCATION_CODES SQL |
|
|
3 |
EDUCATION_CODES Table View |
|
|
3 |
CITIZENSHIP_CODES SQL |
|
|
3 |
CITIZENSHIP_CODES Table View |
|
|
3 |
RACE_CODES SQL |
|
|
3 |
RACE_CODES Table View |
|
|
3 |
ZIP_CODES SQL |
|
|
3 |
ZIP_CODES Table View |
|