SQL Concepts and Database Design

SQL Concepts and Database Design

Project Description

Objective of this technical paper is to provide the database design and documentation for a finance industry to meet the Sarbanes-Oxley (SOX) compliance. The report aims to prepare the documentation of the sales database of the organization. However, data dictionary is very critical to enhance proper functioning of a database since it holds information about the database as well as the data it stores. This paper prepares the database dictionary for Employee, Invoice, InvoiceLine, Product, Department and Job.

DATA DICTIONARY FOR THE ORGANIZATION

Column Name

Column Type

Column Size in Bytes

Column Status

Employee

This section provides several queries from the database

a) This query identifies the number of days that exist between the first invoice and last invoice for each month:

SELECT * FROM INVOICE_TBL;

SELECT* FIRST INVOICE

SELECT* LAST INVOICE

COMPARE * DateDiff FROM FirstInvoice and LastInvoice

b). A query showing the expected payment date of invoices due within 30 days of transaction.

select * from Invoices where DueDate + 30

c). A query showing distinct area codes of the customers.

SELECT CustomerName, CustomerAddress

FROM CUSTOMER_TBL

WHERE CITY = 'INDIANAPOLIS'

DLookup ("[ZipCode],"

3. Plan to Implement Valid Database Design Process

This section discusses the plan to implement database design.

Steps in DBMS selection stage

Requirements Analysis

Organizational objectives

Sale Database design and documentation to meet the Sarbanes-Oxley (SOX)

compliance.

Information system objectives

- keep track of customer' information

- keep track of order' information

- keep track of other vital resources needed to market a product

Administrative and operational policies

- Annual review of employees

- Weekly progress reports

- Annual review of customer's order

- Monthly inventory check

The first step is to construct Entity-Relationship (ER) diagram using an Entity-Relationship (ER) Model. The ER diagram should contain the following:

Entity - a class of real world objects that have common properties and characteristics to record information.

Relationship - an association among two or more entities

Attribute - characteristics of a relationship entity.

ER concepts are revealed in the following diagram.

The next step is to assign characters to each entity. Assigning character to the entity is presented below.

Entity

Entity key

Key length (max) in characters

No. Of occurrences

Customer

cust-no

6

80

Job

job-no

24

80

Order

order-no

9

Salesperson

sales-id

20

Department

dept-no

2

10

Item

item-no

6

5

Data Relationship

The next step is to describe the data relationships. The following is the data relationships:

Each customer has one title, however different customers may have the same title.

Each customer could place many orders, however only one customer could place a particular order.

Each department is having many salespeople, however each salesperson is required work to only one department.

Each department is having items for sale; however, each item is sold in one department.

The table below reveals the ER Construct and database file names (FD).

ER Construct

FDs

Job (one):Customer (many cust-no -> job-title

Customer (one): Order (many)

order-no -> cust-no

Department (one): Salesperson (many)

sales-id -> dept-no

Department (one): Item (many)

item-no -> dept-no

Item (many):Order (many)

order-no, item-no->sales-id

Salesperson (one)

Order (many): Department (many):

order-no, dept-no-> sales-id

Salesperson (one)

(Lightstone, Nadeau, & Jagadish, 2006).

The next step is to create database logical statement as being revealed below.

create table customer

(cust_no

char (6),

job_title

varchar (256),

primary key (cust_no),

foreign key (job_title) references job on delete set null on update cascade);

create table job

(job_no

char (6),

job_title

varchar (256),

primary key (job_no));

create table order

(order_no

char (9),

cust_no

char (6) not null, primary key (order_no),

foreign key (cust_no) references customer on delete set null on update cascade);

create table salesperson

(sales_id

char (10)

sales_name

varchar (256),

dept_no

char (2),

primary key (sales_id),

foreign key (dept_no) references department on delete set null on update cascade);

create table department

(dept_no

char (2),

dept_name

varchar (256),

manager_name

varchar (256),

primary key (dept_no));

create table item

(item_no

char (6),

dept_no

char (2),

primary key (item_no),

foreign key (dept_no) references department on delete set null on update cascade);

create table order_item_sales

(order_no

char (9),

item_no

char (6),

sales_id

varchar (256) not null, primary key (order_no, item_no),

foreign key (order_no) references order on delete cascade on update cascade, foreign key (item_no) references item on delete cascade on update cascade, foreign key (sales_id) references salesperson on delete cascade on update cascade);

create table order_dept_sales

(order_no

char (9),

dept_no

char (2),

sales_id

varchar (256) not null, primary key (order_no, dept_no),

foreign key (order_no) references order on delete cascade on update cascade, foreign key (dept_no) references department on delete cascade on update cascade, foreign key (sales_id) references salesperson on delete cascade on update cascade);

The next stage is to design the database as presented below.

VI. Normalization

The last stage is to implement normalization of the database from the First normal form (1NF) to the third normal form (3NF) and BCNF