DML Overview-2

 

 

 

The Structured Query Language (SQL)

Delete From Table

Update Table

 

 

 

The first half of the semester primarily focused on designing relational database bases. You studied a sequence of processes that should take place to build a relational database efficiently and effectively. Let’s review that process once again

1. Analyze the requirements document or statement and rewrite the requirements in your own words to make sure that you fully understand them. If you cannot do this, it suggests that you do not fully understand what you’re doing yet
   
   .
2. Extract Entities from the requirements and identify relationships between the entities. Additionally, identify attributes that will describe instances of the entities in more detail. Pick candidate attributes that are being considered as unique entity identifiers. From the list of candidate attributes, pick one to be the Unique Entity Identifier. This will become the Primary Key when the translating the ER Diagram into a Relational Schema. Identify and label the carnality in the relationships between the Entities and add any necessary attributes to the relationships.
   
   
   
3. Translate the ER Diagram into the Relational Schema. During the translation, in 1 to many relationships, the primary key of the entity on the 1 side is placed as a foreign key in the entity on the many side. Any additional attributes placed in the relationship are also placed into the many side Entity, and the relationship is removed. In the case of many to many carnality. The relationship becomes a table. The primary keys from all entities connected to the relationship are copied into the relationship as foreign keys.   Identify the primary key for the relationship. The relationship is translated into a table.
   
   
   
4. Using the ER Diagram and the translated Schema, create the metadata/data dictionary documentation for the database. List the correct spelling of the attribute names for each table. Provide a description of the role the attribute plays in the table. Select a data type for each attribute. Provide a ranges of allowable values for each attribute. Identify attributes that are primary and foreign keys. Identify attribute that cannot contain null values
   
   
   
5. Do a final check integrity check of your relational schema by normalizing the schemas into First Normal Form. In First Normal Form, repeating groups are removed by creating a separate table for them. Multivalued attributes are handled the same way as repeating groups. In both cases, the primary key from the schema that they were found in is placed into the new tables as foreign keys.   Composite attributes are divided into smaller atomic attributes.  Update the metadata to reflect any changes made to the relational schema
   
   
   
6. Use the ER Diagram, Schema and the Metadata / Data Dictionary as a guide to write the CREATE TABLE statements. Run and test the CREATE TABLE statements.
   
   
   
7. Create test data for the tables. Insert the test data into the tables by placing the data in the VALUES clause of a series of INSERT INTO Statements.
   
   

Task 7, inserting data into the physical is where we left off last week in an in class practice assignment. This week, you will study and practice a couple of additional SQL Data Manipulation Language DML statements. The two new additional statements will be the UPDATE and DELETE statements. A few additional clauses to the SELECT statement will also get covered and practiced during class.

 

      

 

 

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

 

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. More multiple table queries

 

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. In the process of this you will practice creating sample data to use in the INSERT INTO statements.   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 data type and few other integer data types. 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'.

 Finish writing the INSERT INTO statements to load the Little League Football Commission Database with your sample data

 

Homework

 

Readings

 

 

Example Exercise

 

Discussions for the Week

 

Week 9 Chapter 7 Discussion 1

 

 

Week 9 Chapter 7 Discussion 2

 

Database Project

 

Weekly Quiz 

Weekly Quiz for Week 8 on the Introduction to SQL

 

 

 Highlights This Week

 

 

 

Readings