Database

Credit: CC0 Public Domain

Memory is the ability to take in information, store it, and recall it at a later time. In psychology, memory is broken into three stages: encoding, storage, and retrieval. Stages of memory: The three stages of memory: encoding, storage, and retrieval. Problems can occur at any stage of the process. ~lumen

This has explained the human memory process. Since human tends to forget, and can't live forever, a new approach is created to enhance storing information using Database technology.

Introduction:

The database is a patterned structure of information stored in fields under Tables, which allows data stored in a specific organized manner. Through this pattern, you can easily get any information as long as it is stored.

Contents:

The database uses tables for storing data. We can create tables as many as we need, but generally, tables are designed in a pattern that suits the information that is to be stored. Nevertheless, Tables are also designed to call each other through a calling reference. For example, I am staying in Dubai, and my friend stays as well in Dubai. so we create a table to hold countries' names and then reference people's tables to the country of residency. Didn't get it yet? hang on a more detailed example is explained below.

This is how Wikipedia defined Database:

A database is an organized collection of data, generally stored and accessed electronically from a computer system. ~Wiki

Example on Queries

Let's have a practical example of how we can use a Database to call for data.
I will be using SQLite studio you can download from here
and I will use this pre-structured Database for the purpose of this post. Download from here.

First look when you open a database file

After you download SQLite Studio Press Ctrl + O to open a database File. Select the "Sample. DB" file, you downloaded and you will get to this window

You can see, there are two Tables "Level" and "Room". This database is extracted from Revit and simplified for the purpose of this post.

Double click on Level Table, and then select Data Tab from the browsing area.

Any table is a collection of fields (Pattern) where you can fill in Data. Fields have extra properties, such as Primary Key, Uniq, Not Null...etc, which we will not discuss in this overview tutorial.

You will see the structure of "Level" is as follow:
Elevation,
ElementId,
Name,
Code,
GUID

These fields are the pattern used to shape the Level Table. Meaning every Row in this table will contain information that is bound to each field (Column Name)
see this snapshot of how data looks like after being filled in the Level table


Now, let's open the Room Table. You can see there is no much difference in the concept. The room's table is also another table that holds fields that shape the pattern of the Room Table.

If you look closely, you will find there is a field called "FKey_Level". Can you guess what is this field used for?. Well, since a room is a part of a level, tables need to know that. Tables need to have a calling reference to another data in another table. Let's rephrase this, but first, you need to know that the Database doesn't care about the data value you fill in a table, but it cares about the structure of the table. With that said, each table must have a Unique Field. This Unique Field is used to uniquely define each row. Let's illustrate this in this example.

Look to the first row in the Room Table, and copy the FKey_Level value, you would get this "c6422095-3e7a-45ff-bbc8-15e2c2942d27" This is a calling reference to information stored in Level Table. Now open Level Table and look in the GUID field for this number... It will take too long to find this information, isn't it? or even if not, it would be a bit silly, to search for something while the computer and Database are meant to do this for you. so let's start writing our first Query.

But wait for a second, Why I should be looking in the GUID field in the Level table for this value? this is because these 2 tables are designed to talk to each other through these Unique Fields. (GUID in Level Table Vs FKey_Level in Room Table) you can read more about FOREIGN KEY

From Menu Bar Select Tools--> Open Editor, or Click Alt + E

Opening SQL Editor

Now We need to ask the Database the following:
I need all the fields from Table Level, but only where Field GuId has this value  "c6422095-3e7a-45ff-bbc8-15e2c2942d27"


Translating that to SQL language would be like this
```Select * from Level where GuId = 'c6422095-3e7a-45ff-bbc8-15e2c2942d27'
```

Writing my First Query

Then press the F9 key to run this query

So basically * (asterisks) here means all the fields in a row. SQL by then will search in each row for the value "c6422095-3e7a-45ff-bbc8-15e2c2942d27" under the column GuId, and the result will show at the bottom window.


Of course, you can make this more advanced for example I need to select only Fields "Name" and "Elevation"



```Select [Name],[Elevation] from Level where GuId= 'c6422095-3e7a-45ff-bbc8-15e2c2942d27'
```
You can also show the table in Descending order of elevation

```Select * from Level Order by Elevation Desc
```

Descended order of Level

There is a very big list of queries where you can use to alter and manipulate your Database. Code Academy made a good referral list for this.

All in all, a Database is the core of any Computer Application. It helps Storing, Recalling information on the hard drive instead of computer Memory, which greatly improves the speed of the application, without affecting the performance of the CPU.

That's all for now, see you in the next post.

Excel Chart

A Picture Is Worth a Thousand Words. In Excel you can use Charts to deliver a better message of numbers. Let’s say we have the following scenario

A contractor during a construction project is submitting Material samples to the Project Manager (PM) for Approval. The PM asked a log for all submitted and how many materials are approved/pending

See this table

	Discipline	Total	Under Review.	Appr.A & B	Resub. Required B.	Resub. Required C.	Not Sub.
1	Structure	30	0	29	1	0	0
	Architecture	125	0	99	24	2	0
2	ID	256	1	195	58	2	0
3	Landscape + Kitchen+SPA Work-Swimming Pool+Lift works	131	0	127	4	0	0
4	MEP Elect	87	0	80	6	1	0
5	MEP Mech	96	0	88	8	0	0
	Total	725	1	618	101	5

Such a table with more or less intensive data is not much helpful for evaluation. Human loves colors and graphics. So how about this

Now such presentation will improve evaluating the status of Material submittals, where you can easily see there is a a lot of submissions in the ID approved compared to other disciplines.

How to do it:

you can write your own table in Excel or you can copy and paste the above table.
Then select the data you need for the chart including the headers.
in our case selected those cells that inside the Red Rectangle

now right click and select Quick Analysis, and follow the steps shown in the below images.

one more last thing, is to change the style of the chart.
Select the chart that is recently created and follow the below steps

hope this makes things easy for every body.
happy to share this excel file for educational purpose :)
Excel Example

My 1st Excel Formula

There is no doubt that any computer user, must have passed by Microsoft Excel. MsExel, is a very smart application that helps creating schedules, Tables with all its kinds. and draw Charts. In fact, I have been using Excel for a very long time, if I recall correctly version 3.0 on Windows 3.11 for Working group. but as a concept I used to use an older app called lotus123.

In this post i will not explain how Excel Works, or what its interface is about. There is a lot of blogs and details posts about that all over the internet. I will explain some basic concepts of how formulas and equations are used, so as later in another post we can combine that with Revit API.

How does it work

Before we use it, we need to understand some terms (Range, Formula)

Cell: is a location unit in a table, where you can fill it with data (number, text, image...etc)

Range: is a collection of two or more cells.
Formulas and Functions: is an expression which calculates the value of a cell.
Worksheets: a collection of cells where you keep and manipulate the data. Each Excel workbook can contain multiple worksheets.

I am assuming you already installed and running Microsoft Excel.

Write your first Formula:

let's say you have a collection of numbers written in cells, and you want to sum them up. you can simply go to an empty cell and type this formula =SUM(your Range) in the formula textbox.

where images and videos says more than reading a line. see this video