Final Database (I hope)

It seems like I tweak the database every couple of days. It can be a difficult thing to get the hang of, and once data starts being inserted into the database is when you quickly realize where your mistakes lie; however, figuring out how to fix them is not always so obvious. Here is what I can only hope is the last and final version of the database. Here is the schema and script to create it using MySQL Workbench.

*In my last post, I noted that I originally created the database tables with capital letters, which you’ll notice as you anxiously analyze the script. Linux likes to take my beautiful naming scheme and crush it with all lowercase letters.

Script

# 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,
Phone VARCHAR(16) NOT NULL,
Alert_List VARCHAR(1) NOT NULL,
Administrator VARCHAR(1) NOT NULL,
Pending_Approval VARCHAR(1) NOT NULL,
Active_Account VARCHAR(1) NOT NULL
);

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
);

CREATE TABLE Inventory_History (
IH_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
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,
FOREIGN KEY (Person_ID) REFERENCES Person(Person_ID),
FOREIGN KEY (Item_ID) REFERENCES Inventory(Item_ID)
);

CREATE TABLE Class (
Class_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Class_Name VARCHAR(50),
Class_Description VARCHAR(200),
Class_Active VARCHAR(1)
);

CREATE TABLE Inventory_Class (
IC_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Item_ID INT NOT NULL,
Class_ID INT NOT NULL,
FOREIGN KEY (Item_ID) REFERENCES Inventory(Item_ID),
FOREIGN KEY (Class_ID) REFERENCES Class(Class_ID)
);

CREATE TABLE Person_Class (
PC_ID  INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Person_ID INT NOT NULL,
Class_ID INT NOT NULL,
FOREIGN KEY (Person_ID) references Person(Person_ID),
FOREIGN KEY (Class_ID) REFERENCES Class(Class_ID)
);

CREATE TABLE Bin (
Bin_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Bin_Number VARCHAR(30) NOT NULL,
Bin_Size VARCHAR(2),
Bin_Active VARCHAR(1)
);

CREATE TABLE Building (
Building_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Building_Name VARCHAR(30) NOT NULL UNIQUE,
Building_Active VARCHAR(1) NOT NULL
);

CREATE TABLE Building_Management (
BM_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Building_ID INT NOT NULL,
Person_ID INT NOT NULL,
Manager_Active VARCHAR(1),
FOREIGN KEY (Building_ID) REFERENCES Building(Building_ID),
FOREIGN KEY (Person_ID) REFERENCES Person(Person_ID)
);

CREATE TABLE Room (
Room_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Room_Number VARCHAR(30) NOT NULL,
Building_ID INT NOT NULL,
Room_Active VARCHAR(1) NOT NULL,
FOREIGN KEY (Building_ID) REFERENCES Building(Building_ID)
);

CREATE TABLE Bin_Location (
Bin_Location_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Room_ID INT NOT NULL,
Bin_ID INT NOT NULL,
FOREIGN KEY (Bin_ID) REFERENCES Bin(Bin_ID),
FOREIGN KEY (Room_ID) REFERENCES Room(Room_ID)
);

CREATE TABLE Inventory_Location (
Inventory_Location_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Item_ID INT NOT NULL,
Item_Quantity INT NOT NULL,
Bin_Location_ID INT NOT NULL,
FOREIGN KEY (Item_ID) REFERENCES Inventory(Item_ID),
FOREIGN KEY (Bin_Location_ID) REFERENCES Bin_Location(Bin_Location_ID)
);

CREATE TABLE Config (
Link_Name VARCHAR(50),
Link_Path VARCHAR(200)
);

Student Activities Database Model

As I’ve been programming the site, I find it very convenient to have the schema readily available. So far I’ve started at the bottom and I’ve been (trying to) methodically move through the site as I go from one table to the next.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s