Sql Accounting Database for Kudler Fine Foods

Only available on StudyMode
  • Download(s) : 228
  • Published : February 26, 2012
Open Document
Text Preview
SQL Accounting Database for Kudler Fine Foods
Mark Black

Introduction
Kudler Fine Foods wants to convert the Chart of Accounts sheet in Excel to an SQL database. In order to do so, it is important to format the excel data in proper SQL tables. Normalization plays an important part in placing data fields in proper tables. It is also essential to import data from excel to SQL directly to avoid time wastage. The following report shall detail the creation of SQL database, tables and insertion of data in SQL tables. Data Import

Chart of Accounts
Select * INTO COAExcel FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database= C:\Users\student\Desktop\KudlerCOA.xls;HDR=YES', 'SELECT Account,Description,[Short Description],F4 as balance FROM [CHART OF ACCOUNTS$]') as A

SQL Queries
Database
Create database Kuddler_AccountData

Tables

Tables
USE Kuddler_AccountData

create table Locations(
LocationID INT PRIMARY KEY NOT NULL,
Location_Name varchar(50) NOT NULL,
)
create table AccountTypes(
AccountTypeID INT PRIMARY KEY NOT NULL,
AccountTypeName varchar(50) NOT NULL,
)
create table ChartofAccounts(
Account INT PRIMARY KEY,
AccountTypeID INT FOREIGN KEY (AccountTypeID) REFERENCES AccountTypes (AccountTypeID), LocationID INT FOREIGN KEY (LocationID) REFERENCES Locations (LocationID)) Balance Decimal(10,2) NOT NULL default 0.0)

create table Transactions(
TransactionID INT identity(1,1) Primary Key,
Account INT FOREIGN KEY (Account) REFERENCES ChartofAccounts (Account), LocationID INT FOREIGN KEY (LocationID) REFERENCES Locations (LocationID), TransactionDate DateTime NOT NULL,
Amount Decimal(10,2) NOT NULL)

Data Insertion
INSERT INTO locations(LocationID ,Location_Name)
VALUES
(1,'La Jolla'),
(2,'Del Mar'),
(3,' Encinitas')

INSERT INTO AccountTypes(AccountTypeID ,AccountTypeName)
INSERT into AccountTypes(AccountTypeName)
select distinct Description from COAExcel where description is not null...
tracking img