/* Kudler Fine Foods Database Creation Script - Week 2 IA
** Shawn Belanger - POS 410
** 11/17/07
** This script will be used to create a database named "Kudler", insert tables
** named Employee and Job_title, and build the columns and rows for each table.
*/
-- This statement is used to create the database
CREATE DATABASE Kudler
ON
(
NAME = 'Kudler',
FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL.1\mssql\data\KudlerData.mdf',
SIZE = 50MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
)
/* This statement is used to create the database log, normally the log would
** be stored on a separate drive from the database
*/
LOG ON
(
NAME = 'KudlerLog',
FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL.1\mssql\data\KudlerLog.ldf',
SIZE = 10MB,
MAXSIZE = 50MB,
FILEGROWTH = 5MB
)
GO
/* We can now verify our database structure information
** using the sp_helpdb command
*/
EXEC sp_helpdb Kudler
-- This statement declares the database we will be using
USE Kudler
/* This statement will be used to create the Employee table,
** the columns within that table, and set the primary key
** for the table. Emp_id will be used as the primary key
** because it uniquely identifies each employee by a number
** that is generated by SQL Server that can not be duplicated
** within the table. Some columns such as Telephone_number,
** Wage, and Age are set to except null values if these values
** are unknown or do not exist.
*/
CREATE TABLE Employee
(
Emp_id int IDENTITY NOT NULL PRIMARY KEY,
Last_name varchar(25) NOT NULL,
First_name varchar(25) NOT NULL,
Address varchar(40) NOT NULL,
City varchar(15) NOT NULL,
State varchar(2) NOT NULL,
Telephone_area_code varchar(3) NULL,
Telephone_number varchar(8) NULL,
Job_title varchar(50) NOT NULL,
Hire_data smalldatetime NOT NULL,
Wage money NULL,
Gender char(1) NULL,
Race varchar(25) NULL,
Age int NULL
)
-- Now we can verify the Employee table and columns using the sp_help command
EXEC sp_help Employee
/* This statement will be used to create the Job_title
** table, the columns within that table, and set the
** primary key for the table. The combination of the
** Job_title and EEO_1_Classification columns could
** comprise the primary key because while the same job title may
** exist in various EEO-1 classifications, 2 jobs
** with the same title will never exist in the same
** EEO-1 classification thus making the combination
** unique. However, job titles and EEO-1 classifications
** are subject to change over time making both inappropriate
** choices to be part of a primary key. Because of this,
** the Job_id column has been added as an identity to be
** generated by SQL Server which will uniquely identify
** each job.
*/
CREATE TABLE Job_title
(
Job_id int IDENTITY NOT NULL PRIMARY KEY,
Job_title varchar(50) NOT NULL,
EEO_1_Classification varchar(30) NOT NULL,
Job_description varchar(250) NOT NULL,
Exempt_Non_Exempt_status bit NOT NULL
)
/* Now we can verify the Job_title table and columns using
** the sp_help command
*/
EXEC sp_help Job_title
/* These statements will add records to the Employee table for
** the following employees:
** Glenn Edelman
** Eric McMullen
** Raj Slentz
** Erin Broun
** Donald Carpenter
** David Esquivez
** Nancy Sharp
*/
INSERT INTO Employee
VALUES
('Edelman', 'Glenn', '175 Bishops Ln', 'La Jolla', 'CA', '619',
'555-0199', 'Cashier', '10/7/2003', '10.75', 'M', 'Caucasian', '64')
INSERT INTO Employee
VALUES
('McMullen', 'Eric', '763 Church St', 'Lemon Grove', 'CA', '619',
'555-0133', 'Bagger', '11/1/2002', '6.75', 'M', 'Caucasian', '20')
INSERT INTO Employee
VALUES
('Slentz', 'Raj', '123 Torrey Dr', 'North Clairmont', 'CA', '619',
'555-0123', 'Assistant Manager', '6/1/2000', 'NULL', 'M', 'Asian', '34')
INSERT INTO Employee
VALUES
('Broun', 'Erin', '2045 Parkway - APT 2B', 'Encinitas', 'CA', '760',
'555-0100', 'Bagger', '3/12/2003', '6.75', 'F', 'Caucasian', '24')
INSERT INTO Employee
VALUES
('Carpenter', 'Donald', '927 Second St', 'Encinitas', 'CA', '619',
'555-0154', 'Stocker', '11/1/2003', '7.50', 'M', 'African American', '18')
INSERT INTO Employee
VALUES
('Esquivez', 'David', '10983 North Coast Hwy - APT 902', 'Encinitas', 'CA', '760',
'555-0108', 'Asst. - Butchers & Seafood Specialists', '7/25/2003', '9.25', 'M',
'Hispanic', '25')
INSERT INTO Employee
VALUES
('Sharp', 'Nancy', '10793 Montecino Rd', 'Ramona', 'CA', '858',
'555-0135', 'Cashier', '7/12/2003', '10.50', 'F', 'Caucasian', '24')
/* Now we can verify the data in the Employee table
** using the sp_help command
*/
EXEC sp_help Employee
/* These statements will add records to the Job_title
** table for the following jobs:
** Accounting Clerk
** Assistant Manager
** Bagger
** Cashier
** Computer Support Specialist
** Director of Finance & Accounting
** Retail Asst. - Bakery & Pastry
** Retail Asst. - Butchers and Seafood Specialists
** Stocker
*/
INSERT INTO Job_title
VALUES
('Accounting Clerk', 'Office/Clerical',
'Computes, classifies, records, and verifies numerical data for use in maintaining
accounting records.', '0')
INSERT INTO Job_title
VALUES
('Assistant Manager', 'Officials & Managers',
'Supervises and coordinates activities of workers in department of food store.
Assists store manager in daily operations of store.', '1')
INSERT INTO Job_title
VALUES
('Bagger', 'Sales Workers',
'Places customer orders in bags. Performs carryout duties for customers', '0')
INSERT INTO Job_title
VALUES
('Cashier', 'Sales Workers',
'Operates cash register to itemize and total customer’s purchases in grocery
store.', '0')
INSERT INTO Job_title
VALUES
('Computer Support Specialist', 'Technician',
'Installs, modifies, and makes minor repairs to personal computer hardware and
software systems, and provides technical assistance and training to system
users.', '0')
INSERT INTO Job_title
VALUES
('Director of Finance & Accounting', 'Officials & Managers',
'Plans and directs the finance and accounting activities for Kudler Fine Foods.', '1')
INSERT INTO Job_title
VALUES
('Retail Asst. - Bakery & Pastry', 'Craft Workers (Skilled)',
'Obtains or prepares food items requested by customers in retail food store', '0')
INSERT INTO Job_title
VALUES
('Retail Asst. - Butchers and Seafood Specialists', 'Operatives (Semi skilled)',
'Obtains or prepares food items requested by customers in retail food store.', '0')
INSERT INTO Job_title
VALUES
('Stocker', 'Office/Clerical', 'Stores, prices and restocks merchandise displays
in store.', '0')
/* Now we can verify the data in the Job_title
** table using the sp_help command
*/
EXEC sp_help Job_title
/* This statement is used to verify all of the data
** from both the Employee and Job_title tables
*/
SELECT *
FROM Employee, Job_title