Scripting the Database Creation

This week I worked on the database script in MySQL. This required some research to find the proper MySQL commands. Overall, the syntax and commands are very similar to Microsoft SQL Server, but they are far from identical. Using the schema I posted last week, here is the rough draft of the script:

*Note that everything that follows # is a comment*

—————————————————————————————————————–

drop database SA;
create database SA;
use SA;

# First it is necessary to “drop” (destroy) the database before creating/recreating it.
drop database SA;
# Create the database.
create database SA;
# Use the database.
use SA;

# AUTO_INCREMENT = This means that the ID will be automatically generated and incremented by 1.
# PRIMARY KEY = This is a unique identifier that every table must have.
# UNIQUE = Every value in the table must be unique. No two entries can contain the same value for this field.
# NOT NULL = This means that every entry in this table must have a value for the specific field.
# INT, VARCHAR, DATETIME = These are the datatypes. For example, if a field is listed as INT, the database will
#         reject any other type of data.

CREATE TABLE Person (
Person_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
First_Name VARCHAR(20) NOT NULL,
Last_Name VARCHAR(20) NOT NULL,
Position VARCHAR(20) NOT NULL,
Email VARCHAR(40) NOT NULL UNIQUE,
Pitt_Username VARCHAR(15) NOT NULL UNIQUE,
Administrator VARCHAR(1) NOT NULL
);

CREATE TABLE INVENTORY_HISTORY (
Item_ID INT NOT NULL,
Person_ID INT NOT NULL,
Item_Quantity INT NOT NULL,
Date_Checked_Out DATE NOT NULL,
Date_Due DATE NOT NULL,
Date_Checked_In DATE NOT NULL,
primary key(Item_ID, Person_ID)
);

CREATE TABLE Inventory (
Item_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Item_Name VARCHAR(300) NOT NULL,
Description VARCHAR(300) NOT NULL,
Item_Total INT NOT NULL,
Item_Availability INT NOT NULL,
Location_ID VARCHAR(300) NOT NULL,
Person_ID INT NOT NULL
);

CREATE TABLE Inventory_Location (
Location_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Building_ID INT NOT NULL,
Room_Number INT NOT NULL,
Bin_Number VARCHAR(10)
);

CREATE TABLE Building (
Building_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(40) NOT NULL
);

# Here is some data being inserted into the Building table.

INSERT INTO Building (Name) VALUES (‘Blaisdell Hall’);
INSERT INTO Building (Name) VALUES (‘Fisher Hall’);
INSERT INTO Building (Name) VALUES (‘Swarts Hall’);
INSERT INTO Building (Name) VALUES (‘Seneca Building’);
INSERT INTO Building (Name) VALUES (‘Frames-Westerberg Commons’);

# Sample data so that I can have something to test within the site.

Insert into Person (First_Name,Last_Name,Position,Email,Pitt_Username,Administrator) VALUES (‘t’,’n’,’Faculty’,’tizitomm’,’thn16′,’n’);
#Insert into Person (First_Name,Last_Name,Position,Email,Pitt_Username,Administrator) VALUES (‘Tom’,’Neilly’,’Student’,’tizitomm@gmail.com’,’thn6′,’Y’);

# Sample queries to assure that the script ran successfully and correctly.

use sa;
select * from person;
select * from inventory_location;
select * from inventory;
select * from building;

—————————————————————————————————————–

The script is not yet complete as I will be adding integrity constraints, secondary keys, triggers, and the schema itself may change some as well; but this gives me a starting point in order to start connecting the site with the database and programming some PHP functions to run insert, update, and select statements.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s