SDEV 350 Homework 1 – Oracle Review
Overview:
This homework serves as a refresher and review of basic concepts using an Oracle database. Be sure to
review the reading and complete the tutorials in week 2 prior to completing this assignment
Assignment: Total 100 points
1. Create an Oracle user and assign appropriate privileges for a schema that will support a unique
e-Commerce application.
2. Using PowerPoint, Visio or any graphical tool of your choice, create an Entity Relationship
Diagram showing the entities, relationships and primary keys supporting your e-Commerce
application. Your application should have at least four (4) entities. The total number of
attributes across all entities should be at least fifteen (15). Be sure you use good database
design that includes the use of Primary, Foreign, Not Null, and other constraints.
3. Based on your design, create and describe each of your database tables. The tables must be
created using SQL DDL statements along with all necessary primary key, foreign key and other
integrity constraints. The tables should include comments describing each column that will be
displayed using the appropriate Oracle data dictionary views. Perform both a DESC command to
show the basic data about your tables' columns, and also perform a SELECT of the data
dictionary to show the comments data you've added.
4. Populate your tables with records. The records should represent meaningful data and not
placeholder data. You should populate at least 20 records total across all of your tables.
5. Create one database view showing the combination of at least two tables resulting from a join.
(Hint: Design and test the two-table join first, then create the view using the SQL join you just
created (e.g., create view myView as select ... ))
6. Demonstrate your knowledge of select statements by creating at least six (6) queries that will
return different data from your tables and view.
Deliverables:
1. Create a word or PDF document that contains your Entity relationship diagram, table
descriptions, and screen shots of you successfully running all of your SQL statements within
Oracle 12c. The document should be neat, well-organized, well-written and contain minimal
grammar and spelling errors.
2. A .sql script file containing all SQL statements you used for this effort. All SQL statements should
be in one file with appropriate connect statements to differentiate users who run the script. The
script file should contain comments describe each major set of SQL statements. The script
should run perfectly without error when executed from the Oracle SQL*PLUS environment.
Grading Rubric:
Attribute Meets Does not meet
Create Oracle user 5 points
Creates an Oracle user. (3 points)
0 points
Does not create an Oracle user.
Assigns appropriate privileges for a
schema that will support a unique
e-Commerce application. (2 points)
Does not assign appropriate privileges
for a schema that will support a unique
e-Commerce application.
Create ERD 10 points
Creates an Entity Relationship
Diagram showing the entities,
relationships and primary keys
supporting your e-Commerce
application. (4 points)
Includes at least four (4) entities. (2
points)
Includes at least fifteen (15)
attributes across all entities (2
points)
Includes the use of Primary,
Foreign, Not Null, and other
constraints. (2 points)
0 points
Does not create an Entity Relationship
Diagram showing the entities,
relationships and primary keys
supporting your e-Commerce
application.
Does not include at least four (4)
entities.
Does not include at least fifteen (15)
attributes across all entities
Does not include the use of Primary,
Foreign, Not Null, and other constraints.
Create Tables 20 points
Creates and describes each of your
database tables. (10 points)
Created using SQL DDL statements.
(2 points)
Includes all necessary primary key,
foreign key and other integrity
constraints. (4 points)
Includes comments describing each
column that will be displayed using
the appropriate Oracle data
dictionary views. Performs both a
DESC command to show the basic
data about your tables' columns,
and also perform a SELECT of the
data. (4 points)
0 points
Does not create or describe each of your
database tables.
Not created using SQL DDL statements.
Does not include all necessary primary
key, foreign key and other integrity
constraints.
Does not include comments describing
each column that will be displayed using
the appropriate Oracle data dictionary
views. Does not perform both a DESC
command to show the basic data about
your tables' columns, or perform a
SELECT of the data.
Insert Records 10 points
Populates your tables with records.
(5 points)
Records represent meaningful data
and not placeholder data. (2
points)
0 points
Does not populate your tables with
records. (5 points)
Records do not represent meaningful
data and appear to be placeholder data.
Populates at least 20 records total
across all of your tables. (3 points)
Does not populate at least 20 records
total across all of your tables.
Create View 10 points
Creates one view from the
combination of at least two tables
resulting from a join. (10 points)
0 points
Does not create one view from the
combination of at least two tables
resulting from a join.
Use select 20 points
Creates at least six (6) queries that
will return different data from your
tables and view. (20 points)
0 points
Does not creates at least six (6) queries
that will return different data from your
tables and view.
Documentation and
submission
25 points
Creates a word or PDF document
that contains your Entity
relationship diagram, table
descriptions, and screen shots of
you successfully running all of your
SQL statements within Oracle 12c.
(10 points)
The document should be neat,
well-organized, well-written and
contain minimal grammar and
spelling errors. (5 points)
Provides a .sql script file containing
all SQL statements you used for this
effort. (2 points)
All SQL statements should be in
one file with appropriate connect
statements to differentiate users
who run the script. (2 points)
The script file contains comments
describing each major set of SQL
statements. (2 points)
The script runs perfectly without
error when executed from the
Oracle SQL*PLUS environment. (4
points)
0 points
Does not create a word or PDF document that contains your Entity relationship diagram, table descriptions, or screen shots of you successfully running all of your SQL statements within Oracle 12c. The document is not neat, wellorganized, well-written and contain multiple grammar and spelling errors.
Does not provide a .sql script file containing all SQL statements you used for this effort. All SQL statements are not in one filen with appropriate connect statements tondifferentiate users who run the script.
The script file does not contain comments describing each major set of SQL statements. The script does not run perfectly without error when executed from the Oracle SQL*PLUS environment