SELECT statement

Most basic SQL command, allows us to retrieve data from the database.

SELECT	Specifies which columns are to appear in output
FROM	Specifies table to be used
WHERE	Filters rows
GROUP BY	Forms groups of rows with same column value
HAVING	Filters groups subject to some condition
ORDER BY	Specifies the order of the output

Format
SELECT <list of attribute> FROM <source table>;

Select all from a table
SELECT * FROM table;

Select a few attributes from a table 
SELECT attribute1, attibute2 FROM table;

Examples
SELECT * FROM Gamez;
SELECT game_name, genre, console FROM Gamez;

Writing SQL Commands

SQL statements consist of reserved words and user defined words

• Reserved words are a fix part of SQL and must be spelled  exactly as required and cannot be split across lines.
• User-defined words are made up by user and represent names of various database objects such as relations, columns, views.
• Most component of an SQL statement are case insensitive except for literal character data.
• Good SQL Style:
• Each clause should begin on a new line
• Start of a clause should line up with start of other clauses
• If a clause has several parts, each should appear on a separate line and be indented under the start of the clause.

Data Manipulation

• The four statements used for data manipulation in SQL are:
• SELECT to query data in the database
• INSERT to insert data into a table
• UPDATE to update data in a table
• DELETE to delete data from a table
• Of the four statements the SELECT statement is the most complex
• The following slides present a number of examples of using the SELECT statement for making database queries.

Structured Query Language

The Structured Query Language (SQL) has emerged as one of the most commonly used relational database language.

SQL consists of three main components:

• A Data Definition Language (DDL) for defining the database structure.
• A Data Control Language (DCL) for controlling access to the data.
• A Data Manipulation Language (DML) for retrieving and updating the data in a database.

Boyce-Codd Normal Form

• Database relation in 3rd normal form have neither partial functional dependencies or transitive dependencies.
• The reason for this is that such dependencies can lead to update anomalies.
• However the rules for 1NF , 2NF, and 3NF do not take into account whether such dependencies remain on other candidate keys of a relation.
• Boyce-Codd Normal Form(BCNF) is based on functional dependencies that take into account all candidate keys in a relation.
• Every determinant in table is a candidate key
• When table contains only one candidate key, the 3NF and the BCNF are equivalent
• BCNF can be violated only when table contains more than one candidate key.
• 
  

A table is in Boyce-Codd normal form if It is in 3NF Every determinant in the table is a candidate key.

3NF without BCNF

When happens if a nonkey attribute is the determinant of a key attribute. It does not violate 3NF, yet fails to meet the BCNF requirements.

STU_ID	STAFF_ID	CLASS_CODE	ENROLL_GRADE
125	25	21334	A
125	20	32456	C
135	20	28458	B
135	25	27563	C
144	20	32456	B

• Each Class_Code identifies a class uniquely.
• A student can take many classes. 
• Note: Student 125 has taken both 21334 and 32456 earning the grades of A and C.
• A staff member can teach many classes, but each class is tough by only one staff
• Note:Staff member 20 teaches the classes 32456 and 28458. 

• Panel A is 3NF. It has the problem of trying to describe two things. If a different member is assigned to teach class 32456 two rows must be updated. Also if student 135 drops out we lose data on who teaches the class. 
• The solution is Panel B where we decompose the table into two new.

Key Types

Key Type	Definition
Superkey	An attribute (or combination of attributes) that uniquely identifies each row in a table
Candidate key	A minimal superkey. A superkey that does not contain a subset of attributes that is itself a superkey
Primary key	A candidate key selected to uniquely identify all other attribute values in any given row. Cannot contain null entries.
Secondary key	An attribute(or combination of attributes) used strictly for data retrieval purposes.
Foreign key	An attribute(or combination of attributes) in one table whose value must either match the primary key in another table or be null.

Improving the Design

• Table structures are cleaned up to eliminate troublesome initial partial and transitive dependencies.
• Normalization cannot, by itself be relied on to make good designs.
• It is valuable because its use helps eliminate data redundancies
• Issues to address to produce a good normalized set of tables:
• Evaluate Primary Key Assignment
• Create JOB_CODE and JOB table to reduce data entry errors
• E.g. Database designer in not mistakenly written as DB Designer
• Evaluate Naming Conventions
• Keep consistent with table name
• So CHG_HOUR-> JOB_CHG_HOUR
• Refine Attribute Atomicity
• An atomic attribute is one that cannot be subdivided and is said to display atomicity
• EMP_NAME becomes EMP_FNAME, EMP_LNAME
• Identify New Attributes that might be useful
• For example add EMP_HIREDATE or data about address etc. Identify New Relationship.
• Project and Employee tables have new relationships that may allow you to identify data about the project manager.
• Refine Primary Keys as Required for Data Granularity
• Granularity is the level of detail represented by the data. Data stored at their lowest level of granularity are said to be "atomic data".
• Use ASSIGN_NUM to get greater accuracy and granularity to avoid entity integrity violations.
• (e.g. what if employee worked on project at different times? then there is no longer a unique primary key if composite key PROJ_NUM and EMP_NUM is used.)
• Maintain Historical Accuracy
• Identify past and current charge rates using ASSIGN_CHG_HOUR instead of JOB_CHG_HOUR
• This ensures you have accurate record of what happened at the time of the project
• Evaluate Using Derive Attributes
• In large database, you might want to store derived attributes to reduce time to produce reports.