Designing the Database

The front end of the website will allow users to add, remove, and modify data in the inventory. Until I have the database structure solidified, there is minimal amount of work I can put into the back-end of the site. Designing the database is one of the next steps of the project.

Utilizing my experience from professor Ellison’s Database Administration class, I started by developing a proposal and the business rules. The business rules for a database define what the tables are, how the tables in the database shall be allowed to interact with each other, and the entities of the tables.

Business Rules:

The following is a list of business rules for the Student Activities inventory tracking relational database. The database will consist of five tables: Person, Inventory_History, Inventory, Inventory_Location, and Building. The rules explain what the tables are, what they consist of, and how they will be related to the other tables. Accompanied with these rules is a diagram showing a conceptual model of the relational database.

Person is a student, staff, or faculty member that manages or checks out inventory for Student Activities.

Person may have one or more entries in the Inventory_History.

Person has the following entities:

  • Person_ID is a unique identifier that every person in the database will have.
  • First_Name is the person’s first name.
  • Last_Name is the person’s last name.
  • Position is the person’s relation to Student Activities (work study, faculty, staff).
  • Email is the person’s email address.
  • Pitt_Username is the person’s University of Pittsburgh distributed username.
  • Administrator is whether or not the person has enhanced database privileges.

Inventory is an item that belongs to student activities.

Inventory must have one Inventory_Location.

Inventory may have many Inventory_History.

Inventory has the following entities:

  • Item_ID is a unique identifier that every piece of inventory in the database will have.
  • Person_ID is a unique identifier that every person in the database will have.
    • Person_ID is the person who entered the item into the database.
  • Description describes what the item does, what it is used for, what it looks like, etc..
  • Item_Total is the total quantity of that specific item.
  • Item_Availability is the quantity of that specific item that is currently available.
  • Location_ID is a unique identifier that is assigned to a specific location.

Inventory_Location is the location of a piece of inventory.

Inventory_Location must belong to one or more Inventory.

Inventory_Location must have one Building.

Inventory_Location has the following entities:

  • Location_ID is a unique identifier that every piece of inventory in the database will have.
    • Location_ID is unique to the combination of building, room, and bin.
  • Item_ID is a unique identifier that every piece of inventory in the database will have.
    • Item_ID is the item that the Location_ID is referring to.
  • Building is the building that the item is typically stored in.
  • Room is the room that the item is typically stored in.
  • Bin is the bin that the item is typically stored in.

 

Building represents the building where Inventory is stored.

Building must be associated with one Inventory_Location.

Building has the following attributes:

  • Building_ID is a unique identifier that every building will have.
  • Building_Name is the full name of the Building.

Inventory_History keeps track of the availabilty, due dates, and people who check out inventory.

Inventory_History must belong to one piece of Inventory.

Inventory_History has the following entities:

  • Item_ID is a unique identifier that every piece of inventory in the database will have.
  • Person_ID is the person who checked the item out.
  • Item_Quantity is the quantity of the item that was checked out.
  • Date_Checked_Out is the date the item was checked out.
  • Date_Due is the date the item was due back.
  • Date_Checked_In is the date the item was checked back in.

***

These rules set up the structure for the database. The next step is to physically show the structure of the database by creating with a program such as Microsoft Visio. For this project, I will be using Microsoft Visio 2010.

Student Activities Database Model

Visually, this diagram is much easier to follow and helpful to keep track of the database. Next I will start working on the MySQL script to start actually building the database.

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 )

w

Connecting to %s