Describe a basic SQL SELECT/FROM/WHERE framework as the basis for database queries

1. Describe a basic SQL SELECT/FROM/WHERE framework as the basis for database queries.

SQL or Structured Query Language is a computer language used to manage different databases. SQL can write new information into a database, search and retrieve information from a database and delete selected information form it. There are many sets of statements SQL that perform different operations.  These statements are then combined to form queries and perform specific operations. Some of these queries are Select, From, Where, Delete, Like, Inner Join, Order By and Update (“SQL Quick Reference”).

Though there are many combination of SQL statements, Select, “From” and Where provides a basis to the overall functioning of a SQL database. This statement is the basis of calling a database for specific data in the database tables and the respective rows and columns. Select statement provides the location of a specific column. The “From” statement provides the name of one or multiple tables. And “Where” is a condition operator which tells SQL to implement a certain query if the condition is met.

Contact us if you need this MidTerm solved

SELECT/FROM/WHERE framework is the most important because almost all other statements work in combination with this framework to function in the form of queries. For example if we want to print something when the logical quantity of Tibet Clear is less than 34500 you can write the following query:

Select Product Name;

From Table1;

Print ‘Stock less than 34500’ Where Logical Qty < 3500;

This will print the message to inform that the stock lower limit is reached.

 Table 1

 

2. Design an updateable database to store data received from another source.

SQL has the capacity to collect data sent to it from the outside sources and store it in its tables. As can be seen in Figure 1 that SQL can get data from multiple source. To store data from another source, the basic design of the database has to be writable.

Figure 1

INSERT INTO [destination database].[dbo].[destination table];

SELECT *

FROM [source database].[dbo].[source table];

The above query is a general design of importing data into a host database from another database. The destination and source databases and the respective tables have to be identified with the help of the query.

3. Describe and provide an example of an entity-relationship (E-R) diagram.

E-R diagrams are used to show relationships between the data in two different tables. The relationship is established with the help of foreign-key. The foreign key is the name of a certain table column that is used to relate its table to another table. Following is an E-R for two tables in a non-profit organization that collects donations from different people. These tables are from my Access database for another assignment.

Table 2

Figure 2

In the Figure 2, we can see the relationship diagram between the two tables. The DonorID is acting as a foreign key.

4. Describe and explain how to identify functional dependencies, identify determinants and dependent attributes in a database.

Functional dependencies are used in databases to define relationships and dependencies of different attributes on each other. There may or may not be a functional dependency. For example we might say that for a certain relation R that has two attributes Y and Z, we can describe a functional dependency as Y->Z. it signifies that Z is functionally dependent on Y or Y determines Z.

There are functional dependencies where not one but two attributes make another attribute dependent e.g. (X, Y) -> Z. In such cases we cannot say that either one of Y and X can determine Z.