The Query Series Database

Setting up the database

Create a new LibreOffice Base file. Name the file what you want. I named mine “TeamProjects.” Copy the SQL from http://pastebin.com/57Y0x4yV. In your new file, Tools > SQL. Paste the copied SQL into the SQL window and click Execute. This will create the database tables and populate them.

Using the SQL Query Tool

While we could use Tools > SQL to input our SQL queries, LibreOffice actually has tools designed for this. Insert > Query (SQL) gives you a dialog designed just for entering queries using SQL. To use the dialog, you type your query into the edit box. To run the query, you can 1. click the “Run Query” button on the toolbar, 2. use the menus, Edit > Run Query, or 3. press F5 on your keyboard. When you run the query, the results show in a table in a panel above the query input editor.

The TeamMembers Table

Field Name Type Properties
ID Integer auto generated primary key
FirstName varchar(50) Not Null
LastName varchar(50) Not Null
DateOfHire Date Not Null
SkillID integer foreign key (Skills.ID)
City varchar(50)
Married Boolean Not Null, default: FALSE

The table contains two checks that prevent FirstName and LastName from being all spaces.

The Skills Table

Field Name Type Properties
ID integer auto generated primary key
Title varchar(50) Not Null
Description varchar(256)

The table contains one check to make sure that the title does not contain just spaces.

The Projects Table

Field Name Type Properties
ID integer auto generated primary key
Title varchar(50) Not Null
Description varchar(256)
Start Date
End Date

The table contains one check to make sure the title is not just spaces.

The TeamMember2Project Table

Field Name Type Properties
TeammemberID integer foreign key (TeamMembers.ID)
ProjectID integer foreign key (Projects.ID)

The primary key for this table is the combination of the two fields.

How The Tables Work Together

The basic operation of the database is

  1. A team member is hired and assigned a primary skill
  2. The team member is then assigned to one or more projects
  3. Projects are added as needed and assigned a start and end date.

The connection between the tables are

  1. The TeamMembers table is connected to the Skills table ID field through the SkillID foreign key.
  2. The TeamMembers and Projects tables are connected through the TeamMembers2Projects table that accepts a unique combination of the TeamMembers table’s ID field and the Projects table’s ID field.

Written with StackEdit.

Advertisements

One comment on “The Query Series Database

  1. david sharpe says:

    I just “found” the very interesting series on “base” (found by “googling” for action queries). ACCESS spoiled me in this regard

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