B5 - SQL - Paulina Gawor

What is SQL?

SQL is short for “Structured Query Language.” SQL was developed in the 1970s to query data from relational databases. It is a programming language made up of two parts: Data Definition Language (DDL), and Data Manipulation Language (DML). DDL is used to create or modify relational schema, for example relating university department to an offered course. DML is used to retrieve or modify data in a schema. SQL is centered around set theory and establishing joins between different sets of data.

Relational database management systems, powered by languages like SQL, are useful in many cases. A simple example would be a database of student information at a university. Each student might have an ID number, a log-in, a password, a date of birth, and GPA, among other data points. Each entry must have a unique entry acting as an identifying  primary key, in this case, it would be the ID number. Establishing this data set would appear as below.

The ‘not null’ indicates the field must be filled, ‘varchar(128)’ indicates alphanumeric input, and unique indicates the attribute or combination thereof must be unique in the data set. Beginning with a simple database like this, multiple sets of data can be used, referencing one another. A database can be searched for information about certain students, filter out students by GPA or age, and more. The student database can be cross referenced with registering for university classes, or classes can be associated with faculty information, creating information for payroll, financial, or other statistical analysis.

SQL queries can be used to easily retrieve and filter data from a database. It is established as a standard for all database relational schemes. A database must be simple to create, read, update, and even delete. Having information is useless if there is no good way to analyze or access it.

References

https://docs.microsoft.com/en-us/sql/odbc/reference/processing-a-sql-statement

INFO 210, Professor Michelle W. Purcell, Drexel University, Winter 2016.

Comments

Ronald Herazo

Ronald, it was very interesting to learn about these different conceptual schemes for how databases work. Relational databases are the framework for how SQL works. I was curious as to what a flat database is in comparison. Also, are there any downsides to using a relational structure versus an object oriented one?

Ray Powell

Ray explains where databases are used in design offices. They are what informs the use of BIM practices. Storing material information, its energy performance, ‘green’ factors, in addition to dimensions and finishes, are what makes modelling so valuable. It allows for lower propagation of errors, is more easily updated, and helps inform more complex life cycle analyses of projects.

Kerry Vogler

Kerry shows how databases can be useful to the construction industry. Databases are important in most businesses, whether the details are handled in house or outsourced is another manner. The first thing that I can think of is payroll. There is a significant amount of information in everyone’s tallied weekly hours that determines how clients are billed, and how subcontractors are paid. There is also security issue of billing information and bank accounts being handled by the software of choice. I agree with you that databases can be hugely useful for contractors, especially in creating estimates, as these can vary greatly by region and productivity of individual team.

Assignment B5 - Group A - Ronald Herazo

B5 – Ronald Herazo

Relational Database

There are different types of databases that can be used for different purposes. Nowadays, statistician use two types of databases: flat and relational databases. A good definition of relational databases is a database that contains two or more related tables [2]. In order words, a relational database is a database that treats all its data as a collection of relations; they are usually manipulated a set at a time rather than a record at a time. This particular database is mainly based on relational model of data, and it was first proposed by Dr. Codd in 1970. Also, this specific relational model contains components, such as collection of objects or relations, set of operations to act on the relations, and data integrity for accuracy and consistency.

There are multiple software using relational databases. These software systems are known as a relational database management system (RDBMS). For instance, EcoLab is a Swedish Life Cycle Assessment (LCA) program that has been used mainly in the European industry. One of the main advantages of using this software is its relational database [4]. Therefore, Relational Database Management System is a software that can store and updates a relational database. Nowadays, RDBMS has the two-major software that allow the user to work efficiently (see Figure 1 and 2). Client Server RDMS is a server software that provides a middleware layer between client applications and database files. Additionally, Embedded RDMS is integrated application program where the data is typically stored in a single file.

Figure 1 -Client-Server RDBMS

Figure 2 - Embedded RDBMS

In relational database, entities are stored in tables in which each row of a table stores one record and each column of a table stores one attribute per record. Additionally, the primary key of a relation is an attribute or set of attributes that exclusively identifies a record [1]. Therefore, the relationships between entities can be represented in an Entity-Relationship (ER) diagrams with the purpose of showing attributes, keys, and relationships. In this diagram, primary key is underlined (see Figure 3).

Figure 3 - Entity-Relationship Diagrams

Relational databases have real world objects that need to be modeled in the database. These real-world objects are called entities. Within relational databases, entities can be related to other entities by three different relationships: one-to-one, one-to-many, and many-to-many. For instance, in a relational database, a one-to-one relationship exists when one row in a table may be linked with only one row in another table, and vice-versa [3]. Moreover, a one-to-many relationship occurs when one row in table X may be linked with many rows in table Y, but one row in table Y is linked to only one row in table X [1]. Finally, a many-to-many relationship is a relationship between two entities (X and Y).  For example, an author can have many journals, and a journal can have many authors (see Figure 4).

Figure 4 - Many-to-many relationships

Finally, the Structured Query Language (SQL) is used to manipulate relational databases. SQL has four different languages:

1.     Data Definition Language is typically used when a database is first created with the purpose of creating, modifying, and deleting database tables.

2.     Data Manipulation Control Language allows the user to inert, update and delete data from tables.

3.     Transaction Control Language allows the user to create transactions.

4.     Data Control Language is mainly used for setting permissions.

References

[1] Simpkins, Christopher. "Relational Databases." Relational Databases (1986): n. pag. Georgia Institute of Technology. Web. 11 Feb. 2017.

[2] Telvent. "Relational Database Performance." Relational Databases (1986): 339-59. ArcFMSolution. Web. 11 Feb. 2017.

[3] Veenstra, Peter. "Relational Database Management Systems." Relational Database Management Systems, Database Design, and GIS (2009): 3-20. University of Missouri Columbia. Web. 11 Feb. 2017.

[4] Andrae, A. S. G. Global Life Cycle Impact Assessments of Material Shifts: The Example of a Lead-free Electronics Industry. London: Springer, 2010. 59-62. Print.

Comments:

Egla,

Before this class, I had no clue about Structured Query Language (SQL). You really did a good job explaining SQL and the main commands that are used in this software. One thing I know is that the Structured Query Language (SQL) is used to manipulate relational databases. But, I did not have a chance to read more about it. However, I like when you highlighted on your post that “Learning SQL is important because it gives the user the power to access and interpret data, especially in the programming/developing sphere." As an engineer, I think this software is very important because it helps us to be better in our profession; like you mentioned “it gives the user the power to access and interpret data.”

https://ae-410-510-ay16-17.blogspot.com/2017/02/b5-sql-egla-qori.html

Raggousis,

After reading your post, I have a strong idea of what Structured Query Language is and what are its most important aspects. Thanks for breaking this down; it was very easy to read it. I did use Access before and I think this software is very powerful. I also believe this system has no barriers and it allows people (with businesses) to develop their internal databases. It is impressive how much SQL can offer to this world. SQL is very important because they offer to every single company to have more productivity and organization; I believe this is what all companies want nowadays.  I like when you mentioned “SQL is important because without it, you lose productivity, efficiency, and organization;” you are correct!! I enjoyed reading your post.

https://ae-410-510-ay16-17.blogspot.com/2017/02/assignment-b5-group-b-maria-raggousis.html

Selden,

I liked the way you summarize databases in construction. It seems that databases are very useful to during and after construction. I believe that scheduling is the most important part of a new project. Not having a good visualization of what is going on with the project, contracts, delays, and errors, can affect the engineering firm in charged on the project. This is why I believe that putting all together can help during and after construction. I like when you mentioned that “The construction industry is one that has extensive amounts of information, which makes the creation and use of the database somewhat simpler.” It is obviously that databases are more than numbers, they provide a lot of efficiency and productivity; his is the main reason industries use them.

https://ae-410-510-ay16-17.blogspot.com/2017/02/selden-b5-databases-in-construction.html

B5 - SQL - Egla Qori

SQL

SQL was developed in the 1970's, becoming the most widely used database language and it stands for Structured Query Language. [1] This type of language is utilized to access, communicate and interoperate with a large database, containing a lot of information. Some of the capabilities of SQL include, but are not only limited to: inserting/updating records in a database, creating/erasing databases, creating tables and stored procedures in a database, retrieving data and executing queries in a database. [3] SQL is considered to be an ANSI Standard (American National Standards Institution). [2] However, there are various versions of the language, depending on the relational database management system that it is being used for. Despite the differences between versions, some of the commands used in SQL such as Insert, Delete, Create, etc., remain universal and sufficient to accomplish the main goal that the user needs from a database.
Figure 1 below displays the major syntax used in the language, with a brief description of what each symbol means and a simple example to help new learners understand the language elements faster.

Figure 1: Operators used in SQL [2]

The three main "sub-languages" SQL is comprised of include: data definition language, data manipulation language and data control language. [2] SQL is often referred to as a declarative language (builds the structure by expressing the logic of computation, not describing the control flow). However, it is often argued that SQL also includes procedural elements (programming that contains a number of steps to be carried out). 

Learning SQL is important, because it gives the user the power to access and interpret data, especially in the programming/developing sphere. All the data generated around the world ( around 1 Hexabyte/minute) needs to be stored in databases [4]. Since SQL is the language of databases, given that is a common factor in most of them, having prior knowledge in it can ensure the user has knowledge on how to access data, assemble it, compare/analyze/summarize it, etc. SQL is a proven technology where it is the easiest, most familiar and most compatible way to write queries. [5] It offers vast flexibility in the number of standards, open-source projects and vendors it applies to. 

Comments:

Drew Hovey: I believe that to us as future architectural engineers, the most important and applicable aspect of relational databases lies in the benefits of such technology. As discussed in class over the course of 5 weeks, technology is advancing rapidly in many spheres. The three main benefits that you have listed, especially the "real time data entry" aspect, are extremely important. Our group  (B-Team) talked of potential uses of BIM, 10-20 years from now, where a scanning technology could convert the physical world into digital 2D or 3D models. Real Time Data entry would be crucial in such cases, for an efficient design and construction, where changes in a particular model, equipment or procedure entered in such a relational database, would be automatically updated in all used models for either design or construction of a project.

Hayley Selden: Storing project information in a database is extremely important. Bigger companies invest thousands of dollars to buy storage from different clouds every year, in this way expanding their network. I agree with your statement of how important is to store all information, whether it is technical or legal. Having information secured "in writing" is a good way to assign responsibilities in case something goes wrong, or to use that information as a "database" you can learn and pull information from. I can see the applicability of this in many spheres, especially the ones more closely related to us, such as design and construction.

Maissoun Ksara: It's interesting how you mention the Parent-Child relationship technology. I recently volunteered to help the company I was interning at, to update their database with dozens of new contact information that the company had come across in the last few months. They used a very similar database to store all this information, making good use of the Parent-Child technology. Each "first time entry" would be considered a parent company, and each branch of that company in a different city would be called a child company. Contact information would be carefully placed under the correct branch, making it easier to search for someone in case the user knew what office the contact was out of, but didn't know his exact name or title.

Resources

[1] http://www.sqlcourse.com/intro.html
"SQLCourse. "What is SQL?" SQLCourse - Lesson 1: What is SQL? N.p., n.d. Web. 09 Feb. 2017."

[2] https://en.wikipedia.org/wiki/SQL
"SQL." Wikipedia. Wikimedia Foundation, n.d. Web. 09 Feb. 2017.

[3] http://www.w3schools.com/sql/
"SQL Tutorial." SQL Tutorial. N.p., n.d. Web. 09 Feb. 2017.

[4] https://www.quora.com/Why-is-it-important-to-learn-SQL
"Why is it important to learn SQL?" Why is it important to learn SQL? - Quora. N.p., n.d. Web. 09 Feb. 2017.

[5] https://www.voltdb.com/why-sql-important
"Why SQL Is Important." Why SQL Is Important. N.p., n.d. Web. 09 Feb. 2017.