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
Sample Source(s) Used
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
Goals of normalization
Related Documents
Study Document
Health Care Database Design and
A patchwork of laws provided narrow privacy protections for selected health data and certain keepers of that data." (Administrative Simplification in the Health Care Industry) Therefore, new technologies such as relational databases have simplified the data gathering and maintenance processes of all types of healthcare related data like the physician information process. It is not unheard of today for healthcare and insurance providers matching or 'sinking data' on a
Study Document
Database Technology Administration Database Technology
" "STUDENT INFORMATION TABLE" ( "STUDENT ID" CHARACTER (4) NOT NULL, NAME CHARACTER (25) NOT NULL, "STREET NUMBER" CHARACTER (10) NOT NULL, "STREET NAME" CHARACTER (25) NOT NULL, CITY CHARACTER (25) NOT NULL, STATE CHARACTER (10) NOT NULL, EMAIL CHARACTER (25) NOT NULL, "PHONE NUMBER" CHARACTER (15) NOT NULL ); CONNECT RESET; SQL FOR Book_Information CONNECT TO LIBRARY; CREATE TABLE "USER"; CREATE TABLE BookInformation ( "BookISBN" CHARACTER (10) NOT NULL; "AUTHOR" CHARACTER (25) NOT NULL; "TITLE" CHARACTER (50) NOT NULL; "PRICE" CHARACTER (10) NOT NULL; CONNECT RESET; SQL STATEMENT FOR
Study Document
Databases and Regulatory Compliance Challenges
Databases and Regulatory Compliance Challenges The advent of technology has increased the popularity of database usage in firms, yet the legislation regulating the field has yet to be finalized. The changing nature of the IT sector, coupled with the legislative traits, creates several situations in which the companies find it difficult to comply with the regulations. This paper recognizes some of those difficulties, and also proposes some solutions. Databases Regulatory challenges for databases No
Study Document
Database Administration Today in Evaluating
Design criteria exist at the levels of the technical, system integration aspects of the database to other systems through XML. This integration is critically important to ensure that the applications created can be effectively used over time and not have any scalability issues. There is also the need for designing the databases at the presentation layer to provide for scalability and flexibility of being able to create applications relatively quickly
Study Document
Database Data Warehousing Design Data Warehousing Is
Database Data Warehousing Design Data warehousing is a powerful tool that business organizations use to enhance competitive advantages. Data warehousing supports business decision by collecting, organizing and consolidating data for analysis and reporting using tools such as OLAP (online analytical processing ) and data mining. Typically, the relational database technology is generally being used to design a data warehousing and a relational database is a database having collection of tables, and
Study Document
Database Concepts
Database Concepts Compare and contrast Microsoft Access, Microsoft SQL Server, DB2, and Oracle database programs Providers: Microsoft Corporation provides Microsoft Access and SQL Server, Oracle by Oracle Corporation and DB2 is a product of IBM. Brief about each: MS Access: Microsoft Access is a database, which comes bundled in Microsoft Office Products. Access is fully compatible with Active Server Pages (ASP) scripting. Database programs such as Access (sometimes called Relational Database Management systems or