DBST651 Project - Design and Implement a Relational Database Solved

Introduction

Relational databases are the foundations of the majority of information systems and represent one of the most pervasive technologies today. This project covers fundamental concepts necessary for the design, use, and implementation of relational database systems. Through the hands-on activities you will have a better understanding of database modeling and design, the languages and facilities provided by database management systems, and techniques for implementing relational database systems.
In this project, you are the lead of the company’s Database Design Team. A new database system is needed to support critical business functions. Your task is to plan, design and implement a relational database system for a domain specific application of your choice that meets the given requirements.
Your project will be completed in four stages: initialization, preparation and planning; system analysis and requirement definition; database design; and database implementation. We will divide those stages into individual activities and in this learning demonstration you will conduct those activities sequentially: first you will need to create a formal business document called Statement of Work (SOW) to define your objectives, tasks, deliverables, and constraints. Once the management approves your SOW, you can start to analyze your business domain and define the requirements. After all requirements have been articulated clearly, you can start to design your database by modeling. The most common relationship database model is Entity Relationship Diagram (ERD). Next you will follow your ERD and implement your database by creating database objects such as tables, indexes, views, and triggers using Data Definition Language (DDL). Once the database structure is ready, you can populate your tables with business data using Data Manipulation Language (DML). And finally, you will solve business problems and provide critical insight by querying your data using Structured Query Language (SQL) statements.

Learning Demonstration
Throughout this learning demonstration, a very simple Human Resource (HR) database is being used as an example. The database keeps track of information about employees and departments.

1. Before you can start your project, you need to define what exactly you want to do by creating a Statement of Work. It is a formal document and must be agreed upon by all parties and stakeholders.
a. First, point to your browser to the ProjectMinds website http://www.projectminds.com/Article11.html and read a concise introduction on how to write a SOW.
b. Once you understand the basic components of a SOW, write a 1-2 pages document to describe the need to create, design and implement the database that you propose. I also provided a sample SOW that is specifically written for a database project. Please check Course Content for the sample and notice that it is a barebone example and only provides a highlight of the basic components of a SOW. Please use it as reference only and add more contents to your SOW. The following should be addressed in your SOW:
i. What is the business need and business problem?
ii. What is the purpose of the project?
iii. What is the scope of the work?
iv. What will be achieved by implementing this database?
v. What benefits does the new database offer?

c. The SOW should have one paragraph of overview for executive summary, a section captures the purpose and objectives of the database, and a detailed description on related technologies to be used in the project such as diagram and design tools, DBMS system, hardware, software, DDL and DML (How you will use these in your project).

2. Once the SOW gets the approval from management, it will serve as the general guideline for your remaining project. Now you can start to analyze the business problem you want to solve, and put the results into a requirement definition document. The document describes business rules by identifying what business data you want to keep track of and how those data are related to each other.
a. First visit 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.
b. Now create narrative/description to document business rules related to your business domain.
i. 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.
ii. 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
iii. Describe any other assumptions and special considerations you may have.

3. 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).
a. First review some basic entity relationship modeling concepts and techniques by visiting https://www.youtube.com/watch?feature=player_embedded&v=mQ4D0drMrYI , https://www.youtube.com/watch?v=-fQ-bRllhXc , and http://www.databaseanswers.org/tutorial4_data_modelling/index.htm.
b. 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.
c. Then refine your model and create design level ERD by normalization and resolving all many-to-many relationships.
i. 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.
ii. The crow's feet notation is required.
iii. 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.

4. 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:
a. 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;

b. Now add DDL Create statements for all tables and associated objects.
i. Review SQL CREATE TABLE statement and add your tables and columns including column name, size, type, constraint (such as NOT NULL).
ii. Review SQL Primary key constraint and foreign key constraint. Add keys to your table definition.
iii. 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)
);
c. Now that your tables have been created, you can add indexes to certain columns to speed up queries.
i. Create unique index on natural key columns. For example:
CREATE UNIQUE INDEX DEPT_DEPT_NAME_UX ON DEPARTMENT(DEPT_NAME);
ii. Create index on foreign key columns. For example:
CREATE INDEX EMP_DEPT_ID_FK ON EMPLOYEE(DEPT_ID);
iii. 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);

5. 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.
a. 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 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
);
b. Then read about 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;
c. 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 sequence for each table’s primary key column. For example:
CREATE SEQUENCE seq_dept_id;
CREATE SEQUENCE seq_emp_id;
d. To automatically generate values for primary key columns, you will need a special database object called trigger. Please review table triggers by visiting 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;
/
e. 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;
6. 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.
a. Once all objects have been created in the database, create 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;
b. After entering sample data into each table, develop 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.
i. 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;
ii. Select 5 columns and all rows from one table.
iii. Select all columns and all rows from one view.
iv. Using a join on 2 tables, select all columns and all rows from the tables without the use of a Cartesian product.
v. Select and order data retrieved from one table.
vi. Using a join on 3 tables, select 5 columns from the 3 tables. Use syntax that would limit the output to 10 rows.
vii. Select distinct rows using joins on 3 tables.
viii. Use group by & having in a select statement using one or more tables.
ix. Use IN clause to select data from one or more tables.
x. Select Length of one column from one table (use Length function)
xi. Use the 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;
xii. Use the 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;
xiii. Perform 8 additional advanced (multiple table joins, sub-queries, aggregate, etc.) SQL statements.