Week 9- Chapter 7

Introduction to Structured

Query

Language SQL

Week 9

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

Continue the same material from week 8. 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 9 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 9 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 Week 9 Quiz Online

Homework

Practice Assignment Week 9

Assignment Week 8

Readings

Joins

Chapter 7 in the Textbook

Example Exercise

See

Discussions for the Week

Discussion 1 Week 9

Discussion 2 Week 9

Database Project

Weekly Quiz

Complete Week 9 Quiz online