First visit HYPERLINK "http://www.tdan.com/view-articles/5174/" http://www.tdan.com/view-articles/5174/ and learn what data related business rules are. Business rules are precisely written and unambiguous statements that are derived from a detailed description of an organization's operations. It includes entities, relationships, attributes, connectivity, cardinalities, and constraints.

Now create narrative/description to document business rules related to your business domain.

Describe each entity and its main attributes. For example:

Entity Name: EMPLOYEE

Entity Description: employees who work in an organization

Main attributes of EMPLOYEE:

Attribute Name: L_NAME

Attribute Description: last name.

Attribute Name: F_NAME

Attribute Description: first name.

Attribute Name: DOB

Attribute Description: date of birth.

Describe each relationships and the cardinality from both directions of the relationship. For example:

Relationship: works between EMPLOYEE and DEPARTMENT

Cardinality/Business rule: a department can have zero to many employees; an employee works for one and only one department

Describe any other assumptions and special considerations you may have.

Now that you have clarified your business requirements and business rules, you now start to design your database by creating the Entity Relationship Diagram (ERD).

First review some basic entity relationship modeling concepts and techniques by visiting HYPERLINK "https://www.youtube.com/watch?feature=player_embedded&v=mQ4D0drMrYI" https://www.youtube.com/watch?feature=player_embedded&v=mQ4D0drMrYI , HYPERLINK "https://www.youtube.com/watch?v=-fQ-bRllhXc%20" https://www.youtube.com/watch?v=-fQ-bRllhXc , and HYPERLINK "http://www.databaseanswers.org/tutorial4_data_modelling/index.htm" http://www.databaseanswers.org/tutorial4_data_modelling/index.htm.

Now create your initial conceptual level ERD which mainly captures and represents your business rules (business domain entities and their relationships) specified in step 2.

Then refine your model and create design level ERD by HYPERLINK "http://www.troubleshooters.com/littstip/ltnorm.html" normalization and resolving all many-to-many relationships.

The ERD should consist of a minimum of 5, but no more than 6, entities. Each entity should have a minimum of 5 attributes. Draw appropriate relationships to connect related entities.

The crow's feet notation is required.

ER Assistant, Visio, or SQL Developer Data Modeler is the suggested diagramming tool for this course. Use of another tool is acceptable as long as the final product is submitted in a common format (such as PDF or JPG) that can be read, evaluated and graded.

Create DDL: tables, columns, keys, indexes

Once the design model is completed and the ERD is approved, the next step is to create the physical database objects (tables, columns, keys, etc.) that implement the logical objects (entities, attributes, relationships, etc.) defined in your ERD. You use SQL Data Definition Language (DDL) to create your database schema and table structure which will include the following:

Drop statements for all objects in the lab project (drop existing objects first so that you can rerun your script multiple times without error). Please make sure this is the first part of your entire DDL script. For example:

DROP TABLE EMPLOYEE CASCADE CONSTRAINTS;

DROP TABLE DEPARTMENT CASCADE CONSTRAINTS;

DROP SEQUENCE seq_dept_id;

DROP SEQUENCE seq_emp_id;

Now add DDL Create statements for all tables and associated objects.

Review HYPERLINK "http://www.w3schools.com/sql/sql_create_table.asp" SQL CREATE TABLE statement and add your tables and columns including column name, size, type, constraint (such as NOT NULL).

Review HYPERLINK "http://www.w3schools.com/sql/sql_primarykey.asp" SQL Primary key constraint and HYPERLINK "http://www.w3schools.com/sql/sql_foreignkey.asp" foreign key constraint. Add keys to your table definition.

Make sure to create parent base tables first, and then child tables. For example:

CREATE TABLE DEPARTMENT

( DEPT_ID NUMBER(10) PRIMARY KEY,

DEPT_NAME VARCHAR2(50) NOT NULL,

DEPT_LOCATION VARCHAR2(50) NOT NULL

);

CREATE TABLE EMPLOYEE

( EMP_ID NUMBER(10) PRIMARY KEY,

EMP_NAME VARCHAR2(50) NOT NULL,

EMP_DOB DATE,

DEPT_ID NUMBER(10),

CONSTRAINT EMP_DEPT_ID_FK FOREIGN KEY (DEPT_ID) REFERENCES DEPARTMENT(DEPT_ID)

);

Now that your tables have been created, you can add indexes to certain columns to speed up queries.

Create unique index on natural key columns. For example:

CREATE UNIQUE INDEX DEPT_DEPT_NAME_UX ON DEPARTMENT(DEPT_NAME);

Create index on foreign key columns. For example:

CREATE INDEX EMP_DEPT_ID_FK ON EMPLOYEE(DEPT_ID);

Create index on other columns that will be frequently used as query filters (i.e., Columns in the WHERE clause). For example:

CREATE INDEX EMP_EMP_NAME_IDX ON EMPLOYEE(EMP_NAME);

Now you basic table structure has been created. The second part of the DDL is to add additional database objects (sequences, views, triggers) to facilitate your data entries and queries.

First modify your table structure to add some audit columns so that you can keep track of who adds/changes a record and when. Use HYPERLINK "http://www.w3schools.com/sql/sql_alter.asp" DDL ALTER TABLE statement. For example:

ALTER TABLE DEPARTMENT ADD

( CREATED_BY VARCHAR2(30),

DATE_CREATED DATE,

MODIFIED_BY VARCHAR2(30),

DATE_MODIFIED DATE

);

ALTER TABLE EMPLOYEE ADD

( CREATED_BY VARCHAR2(30),

DATE_CREATED DATE,

MODIFIED_BY VARCHAR2(30),

DATE_MODIFIED DATE

);

Then read about HYPERLINK "http://www.w3schools.com/sql/sql_view.asp" SQL views and create a view for each table so that only business columns are included but not audit columns. For example:

-- This view shows basic department information including id, name and location (without audit columns)

CREATE OR REPLACE VIEW VW_DEPT AS

SELECT DEPT_ID, DEPT_NAME, DEPT_LOCATION FROM DEPARTMENT;

-- This view show basic employee information including id, name, DOB and department id (without audit info)

CREATE OR REPLACE VIEW VW_EMP AS

SELECT EMP_ID, EMP_NAME, EMP_DOB, DEPT_ID FROM EMPLOYEE;

It is a good practice to use surrogate key vs. natural key (business key) as primary key. To do so, you will need to create a HYPERLINK "http://www.w3schools.com/sql/sql_autoincrement.asp" sequence for each tables primary key column. For example:

CREATE SEQUENCE seq_dept_id;

CREATE SEQUENCE seq_emp_id;

To automatically generate values for primary key columns, you will need a special database object called trigger. Please review table triggers by visiting HYPERLINK "http://psoug.org/reference/table_trigger.html" http://psoug.org/reference/table_trigger.html. Now create a row level trigger for each table so that you can populate surrogate key and audit columns with appropriate values. For example:

--This trigger populates surrogate key and audit columns with appropriate values

CREATE OR REPLACE TRIGGER BIUR_DEPT_TRG

BEFORE INSERT OR UPDATE ON DEPARTMENT

FOR EACH ROW

BEGIN

-- use surrogate key

IF :NEW.dept_id IS NULL THEN

:NEW.dept_id := seq_dept_id.NEXTVAL;

END IF;

IF INSERTING THEN

IF :NEW.created_by IS NULL THEN :NEW.created_by := USER; END IF;

IF :NEW.date_created IS NULL THEN :NEW.date_created := SYSDATE; END IF;

END IF;

IF INSERTING OR UPDATING THEN

IF :NEW.modified_by IS NULL THEN :NEW.modified_by := USER; END IF;

IF :NEW.date_modified IS NULL THEN :NEW.date_modified := SYSDATE; END IF;

END IF;

END;

/

--This trigger populates surrogate key and audit columns with appropriate values

CREATE OR REPLACE TRIGGER BIUR_EMP_TRG

BEFORE INSERT OR UPDATE ON EMPLOYEE

FOR EACH ROW

BEGIN

-- use surrogate key

IF :NEW.emp_id IS NULL THEN

:NEW.emp_id := seq_emp_id.NEXTVAL;

END IF;

IF INSERTING THEN

IF :NEW.created_by IS NULL THEN :NEW.created_by := USER; END IF;

IF :NEW.date_created IS NULL THEN :NEW.date_created := SYSDATE; END IF;

END IF;

IF INSERTING OR UPDATING THEN

IF :NEW.modified_by IS NULL THEN :NEW.modified_by := USER; END IF;

IF :NEW.date_modified IS NULL THEN :NEW.date_modified := SYSDATE; END IF;

END IF;

END;

/

Now check the DBMS data dictionary to make sure all your objects have been created successfully. For example:

SELECT TABLE_NAME FROM USER_TABLES;

SELECT OBJECT_NAME, STATUS, CREATED, LAST_DDL_TIME FROM USER_OBJECTS;

Now you have completed the definition part of your database schema. The next step is to enter data into your tables and then query your data.

Once all objects have been created in the database, create HYPERLINK "http://www.w3schools.com/sql/sql_insert.asp" SQL INSERT statements (DML) to populate each table with sample data. Each table should have a minimum of 10 rows unless you have specific business rules that prevent it from having that many records. Make sure your sequences and triggers are valid and enabled so that surrogate keys and audit columns can be populated automatically. For example:

INSERT INTO DEPARTMENT(DEPT_NAME, DEPT_LOCATION)

VALUES('HR', 'Adelphi, MD');

INSERT INTO DEPARTMENT(DEPT_NAME, DEPT_LOCATION)

VALUES('Sales', 'College Park, MD');

COMMIT;

After entering sample data into each table, develop HYPERLINK "http://www.w3schools.com/sql/sql_select.asp" SQL SELECT statements to query your tables. You should have a minimum of 20 SQL select statements. Query 1 to 12 are basic queries, plus at least 8 advanced queries. Each query should have comment/description to explain its business purpose, as well as which requirement item you are satisfying (i.e., --1. Select all columns and all rows from one table). Please submit both query statements and query results.

Select all columns and all rows from one table. For example:

-- Query 1: select all columns and all rows from one table

-- Business purpose: this query selects all information about all departments

SELECT * FROM DEPARTMENT;

Select 5 columns and all rows from one table.

Select all columns and all rows from one view.

Using a join on 2 tables, select all columns and all rows from the tables without the use of a Cartesian product.

Select and order data retrieved from one table.

Using a join on 3 tables, select 5 columns from the 3 tables. Use syntax that would limit the output to 10 rows.

Select distinct rows using joins on 3 tables.

Use group by & having in a select statement using one or more tables.

Use IN clause to select data from one or more tables.

Select Length of one column from one table (use Length function)

Use the HYPERLINK "http://www.w3schools.com/sql/sql_delete.asp" SQL DELETE statement to delete one record from one table. Add select statements to demonstrate the table contents before and after the DELETE statement. Make sure to use ROLLBACK afterwards so that the data will not be physically removed. For example:

-- Query 11: use the SLQ DELETE statement to delete one record from one table

-- Business purpose: delete the HR department

DELETE FROM DEPARTMENT WHERE DEPT_NAME = 'HR';

-- revert the change

ROLLBACK;

Use the HYPERLINK "http://www.w3schools.com/sql/sql_update.asp" SQL UPDATE statement to change some data. Add select statements to demonstrate the table contents before and after the UPDATE statement. You can either COMMIT or ROLLBACK afterwards. For example:

-- Query 12: use the SQL UPDATE statement to change some data

-- Business purpose: change the location of HR department to Largo, MD

UPDATE DEPARTMENT SET DEPT_LOCATION = 'Largo, MD' WHERE DEPT_NAME = 'HR';

-- revert the change

ROLLBACK;

Perform 8 additional advanced (multiple table joins, sub-queries, aggregate, etc.) SQL statements.

Deliverable

You will have four deliverables for this project.

Statement of Work. Word or PDF. The name of this file will be as follows: LastName_FirstName_SOW.doc.

Requirement document (business rules narratives). Word or PDF. The name of this file will be as follows: LastName_FirstName_businessrules.doc.

Design ERD. Diagram specific file format or Word/GIF/JPEG/PDF if you are not using the suggested diagramming tool. File name: LastName_FirstName_ERD.doc.

DDL script in plain text. TXT or SQL file. The name of this file will be as follows: LastName_FirstName_DDL.txt or LastName_FirstName_DDL.sql.

Output file which includes script as well as results of running your script (copy and paste either text results or screen shots). Word or PDF. File name: LastName_FirstName_DDL_output.doc.

Consolidated DDL/DML script (INSERT statements and quries) in plain text. TXT or SQL file. The name of this file will be as follows: LastName_FirstName_DDL_DML.txt or LastName_FirstName_DDL_DML.sql.

Consolidated, formatted final project report. Word or PDF. Include SOW, requirements, ERD, and DDL/DML source code as well as DDL/DML output (copy and paste either text results or screen shots of running your DDL/DML statements). The name of this file will be as follows: LastName_FirstName_final_project.doc.

Helpful Hints

Keep your project simple and limited to five entities. Keep in mind two entities with a M:N relationship between them will be converted into three entities.

Come up with a M:N relationship early on so that your final ERD will have three tables related to each other (for example the Enroll relationship between STUDENT AND COURSE will be translated into three tables - STUDENT links to ENROLLMENT which links to COURSE). When writing advanced queries, you can easily develop ones with 3 table joins.

Decide on your database project and plan not to revise your ERD midway, although you are free to do it. From past experiences students had difficulties when they did this because of time constraints.

Format your deliverable as if you are submitting it professionally in a work environment (comments, structured output, etc.). Assume the reviewer is not an IT professional.