SQL Concepts and Database Design Research Paper

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 <= GETDATE (); 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):….....