Week 6 (Wednesday and Friday)

Week 6 (Wednesday and Friday) - 1 Introduction to SQL...

Info iconThis preview shows pages 1–8. Sign up to view the full content.

View Full Document Right Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: 8/17/2009 1 Introduction to SQL Introduction to SQL Using and manipulating the data you stored Session 6 Checkpoint Checkpoint ¾ What we have learnt so far ¡ Why database design ¡ The tool for capturing business rules (ER diagrams) ¡ Defining relations (normalization) ¾ Data can now be stored ¡ What’s next? 6-2 8/17/2009 2 Using and manipulating data Using and manipulating data ¾ Relational model ¡ Model defines the “table” concept ¡ Relational algebra is the manipulator ¾ Implementation of relational model ¡ Tables in DBMS ¡ SQL for the relational algebra 6-3 About SQL About SQL ¾ Structured Query Language (SQL) ¾ Commands fall into two categories ¡ Data definition language (DDL) ¡ Data manipulation language (DML) ¾ Characteristics of SQL ¡ Non-procedural SQL is now an ANSI standard 6-4 ¡ SQL is now an ANSI standard ¡ Many dialects exist 8/17/2009 3 Examples of DDL Examples of DDL 6-5 Examples of DML Examples of DML 6-6 8/17/2009 4 From relations to tables From relations to tables ¾ Two step process: create and populate ¡ Create communicates the table structures to the DBMS using SQL ¡ SQL is used to insert/delete/update records into DBMS ¾ The internal model ¡ DBMS creates the physical structure on 6-7 physical storage device ¡ Varies depending on the DBMS being used (Oracle, MS Access, MS SQL Server etc.) Creating database Creating database ¾ Before populating the tables, you need to create the database ¡ A database is referenced by a name ¡ Contains a group of tables, indexes, meta-data, etc. ¾ CREATE DATABASE database-name 6-8 ¾ DROP DATABASE database-name 8/17/2009 5 Creating tables Creating tables ¾ Steps to create a table ¡ Convert ER diagram to relations by Convert ER diagram to relations by normalization ¡ Decide the attributes’ domain ¡ Issue SQL statements ¾ CREATE TABLE table-name ( column-name data type 6-9 data-type, …) Example Example ¾ Two tables to be created: ¡ Product (P_Code , P_Descript, P_Indate, P_Onhand, P_Min, P_Price, P_Discount, V_Code ) ¡ Vendor (V_Code , V_Name, V_Contact, 6-10 V_Areacode, V_Phone, V_State, V_Order) 8/17/2009 6 Example (cont.) Example (cont.) ¾ Creating table command CREATE TABLE Vendor ( V_Code INTEGER NOT NULL UNIQUE, V_Name VARCHAR(35) NOT NULL, V_Contact VARCHAR(15) NOT NULL, V_Areacode CHAR(4) NOT NULL, V_Phone CHAR(8) NOT NULL, V_State CHAR(3) NOT NULL, V_Order CHAR(1) NOT NULL, column PRIMARY KEY ( V_Code )); 28 July 2009 11 T2-2009 column name Example (cont.) Example (cont.) ¾ Creating table command data type CREATE TABLE Vendor ( V_Code INTEGER NOT NULL UNIQUE, V_Name VARCHAR(35) NOT NULL, V_Contact VARCHAR(15) NOT NULL, V_Areacode CHAR(4) NOT NULL, V_Phone CHAR(8) NOT NULL, V_State CHAR(3) NOT NULL, V_Order CHAR(1) NOT NULL, PRIMARY KEY ( V_Code )); 28 July 2009 12 T2-2009 8/17/2009 7 Example (cont.) Example (cont.) ¾ Creating table command CREATE TABLE Vendor ( V_Code INTEGER NOT NULL UNIQUE, V_Name VARCHAR(35) NOT NULL, V_Contact VARCHAR(15)...
View Full Document

This note was uploaded on 09/27/2009 for the course SEB 323 - S taught by Professor Professor during the Three '09 term at Deakin.

Page1 / 47

Week 6 (Wednesday and Friday) - 1 Introduction to SQL...

This preview shows document pages 1 - 8. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online