SQL Overview-2

    Chapter 7

Introduction to Structured

Query

Language SQL

 

Chapter seven will take us into Week 5 as well. It is an extremely comprehensive chapter. Chapter 7, begins to explain in detail the basic commands and functions in the SQL Language. The chapter will explain and illustrate to you how to use the SQL for data manipulation. Data Manipulation includes adding data to tables, updating data in tables and deleting data in table. Some of tis we have already started to do. We have been practicing how to a database for useful information on single tables in a database. This chapter will start to explain and illustrate the process using multiple tables.

This will add an additional activity to the process of creating a database. First, you write a brief paragraph or two describing the database problem that you are working on. You then create an ER Diagram from your understanding of the problem. After creating the ER Diagram, you then translate the diagram into a relational schema. You then use the relational schema to create the metadata for the relational schema. You then perform one last integrity check on your database schema by doing a normalization check on your relational schemas. Now in addition to the aforementioned activities, you physically create database and the table structures for the database using the SQL CREATE statements.  

 

Week 4
 

  • read the weekly overview
  • read the lecture notes posted in weekly modules
  • participate in the weekly discussion forums
  • review the supplement documents and example code posted in the weekly modules
  • attempt the weekly practice exercise
  • complete the weekly assignment
  • complete the weekly quiz
  • review the weekly question and answer forum

 

Highlights and Objectives This Week

  • The basic commands and functions of SQL
  • How to use SQL for data administration (to create tables and indexes)
  • How to use SQL for data manipulation (to add, modify, delete, and retrieve data)
  • How to use SQL to query a database for useful information

 

Highlights and Outcomes This Week

Throughout the semester we have been gradually processing in sequential order the steps outlined by professionals to design a relational database than can implemented in any relational database management system.  The first step is to obtain the requirements and understand the problem.  This involves writing a statement of understanding after interviewing the people involved with the currently implemented system.   The second step was to create a conceptual view of the entities and relationships extracted from the problem space that you are trying to create a relational database for.  This involves understanding the business rules that the stakeholders of the system currently utilize. The third step is to create a logical representation of the database.  This includes translating the conceptual ER diagram into a normalized relational schema.  Once a normalized relational schema is created, the data dictionary can be finalized.  The data dictionary is the metadata that is needed by the users of the database. Once the finalized schema and data dictionary is approved, the physical database is then implemented.  Last week, we left off discussing the nature of the RDBMS in how it evaluates joins.  We also continued to practice running single table queries in a real time database environment.  To accomplish that, you were provided DDL script to create the database and the tables in the database.  You were also provided with the DML INSERT INTO commands to insert and populate the tables with data.  This week we will study and practice creating our own DDL statements to create databases and tables.  To begin this, you will explore the structure of the CREATE TABLE command and some of its supplement modifier statements.  In the first week of the course, we examined three basic generic data types that we classified as text, numeric and Boolean. You will study a variety of sub types of these main categories. 

 

Under the numeric category there are floating point and integer data types. Under the integer data types there is the int and the integer. Under floating point there is the double and the float data types.  The main difference between the floating point data types is the size of the floating point number and the storage space needed to hold the value.  The float data type has a 32 bit size.  The double data type has a 64 bit size.  Under the text data type there is the Varchar data types and char data types.   In certain DBMS applications there is a difference in the char data type.  In the certain DBMS such as Oracle char is a constraint on the number of characters that must be placed in the attribute. MySQL does not enforce this constraint.   

The Date data type enforces that a correct date is entered into the attribute.  Different DBMS application structure the date differently.  The structure of the date attribute in MySQL is 'YYYY-MM-DD HH:MM:SS'.  You can optionally use a sub set of the date structure excluding the time 'YYYY-MM-DD'.

 

Homework

 

  

Readings

 

Chapter 7 

 

Example Exercise

 We will continue to use SQL script file to introduce applied sql this week.  We will expand on SQL script using multiple table joins.  

 

 

Discussions for the Week

 

Week 8 Chapter 7 Discussion 1

 Explain why it might be more appropriate to declare an attribute that contains only digits as a character data type instead of a numeric data type.

 

Week 8 Chapter 7 Discussion 2

Explain the relationship between the (PRODUCT Operation also referred to as the Cartesian Product) and the JOIN ON Operation used to work with two tables in a SELECT statement. 

 

Database Project

 Announced during lecture

Weekly Quiz 

Weekly Quiz for Week 8 on the Introduction to SQL

 

Important Reminder 

The correct process for responding to discussions is to first post your own reply to the discussion topic.  Other participant’s replies will not be visible until you post your first individual reply.  Second, you must post a brief reply to at least two other students’ replies. This along with a relevant individual reply will give you full credit in discussion assignments

 

To provide additional flexibility for students that do not have their own personal computer resources, I am extending the deadline for assignment submissions from Sundays at midnight to Mondays at midnight. 

 

 Highlights This Week

 

Writing Multiple Table Queries using the JOIN modifier to the SELECT statement

Writing Multiple Table Queries using the WHERE modifier to the SELECT statement

Writing CREATE TABLE statements in SQL

Writing INSERT INTO Table Queries

 

 

 

Homework Assignments This Week

 

 

Readings

      Chapter 7 In the Textbook

In Class Example Exercise

 

 

 

Database Project

 

 

Weekly Quiz 

Complete Quiz in Module

 

Homework

Practice Assignment 

Assignment 

Readings

 

Chapter 7 in the Textbook

Example Exercise

 

Discussions for the Week

Discussion 1 in Module

Discussion 2 in Module

Database Project
 

  • Weekly Quiz 

    Complete Quiz in Module Online