Design and Implementation

SQL Design and Implementation CIS 400 Final Project Dr. Bruce Maxim Brad Lloyd & Michelle Zukowski

SQL An Overview of SQL • SQL stands for Structured Query Language. • It is the most commonly used relational database language today. • SQL works with a variety of different fourth-generation (4GL) programming languages, such as Visual Basic. Brad Lloyd & Michelle Zukowski

SQL SQL is used for: • Data Manipulation • Data Definition • Data Administration • All are expressed as an SQL statement or command. Brad Lloyd & Michelle Zukowski

SQL SQL Requirements • SQL Must be embedded in a programming language, or used with a 4GL like VB • SQL is a free form language so there is no limit to the the number of words per line or fixed line break. • Syntax statements, words or phrases are always in lower case; keywords are in uppercase. Not all versions are case sensitive! Brad Lloyd & Michelle Zukowski

SQL is a Relational Database A Fully Relational Database Management System must: • Represent all info in database as tables • Keep logical representation of data independent from its physical storage characteristics • Use one high-level language for structuring, querying, and changing info in the database • Support the main relational operations • Support alternate ways of looking at data in tables • Provide a method for differentiating between unknown values and nulls (zero or blank) • Support Mechanisms for integrity, authorization, transactions, and recovery Brad Lloyd & Michelle Zukowski

SQL Design • SQL represents all information in the form of tables • Supports three relational operations: selection, projection, and join. These are for specifying exactly what data you want to display or use • SQL is used for data manipulation, definition and administration Brad Lloyd & Michelle Zukowski

SQL Table Design Columns describe one characteristic of the entity Rows describe the Occurrence of an Entity Brad Lloyd & Michelle Zukowski

Data Retrieval (Queries) SELECT • Queries search the database, fetch info, and display it. This is done using the keyword SELECT * FROM publishers The * Operator asks for every column in the table. Brad Lloyd & Michelle Zukowski

Data Retrieval (Queries) • Queries can be more specific with a few more lines SELECT *from publisherswhere state = ‘CA’ Only publishers in CA are displayed Brad Lloyd & Michelle Zukowski

Data Input INSERT Variable INSERT INTO publishersVALUES (‘0010’, ‘pragmatics’, ‘4 4th Ln’, ‘chicago’, ‘il’) • Putting data into a table is accomplished using the keyword Keyword Table is updated with new information Brad Lloyd & Michelle Zukowski

Types of Tables There are two types of tables which make up a relational database in SQL • User Tables: contain information that is the database management system • System Tables: contain the database description, kept up to date by DBMS itself Brad Lloyd & Michelle Zukowski

Using SQL SQL statements can be embedded into a program (cgi or perl script, Visual Basic, MS Access) OR SQL statements can be entered directly at the command prompt of the SQL software being used (such as mySQL) SQLDatabase Brad Lloyd & Michelle Zukowski

Using SQL To begin, you must first CREATE a database using the following SQL statement: CREATE DATABASE database_name Depending on the version of SQL being used the following statement is needed to begin using the database: USE database_name Brad Lloyd & Michelle Zukowski

Using SQL • To create a table in the current database, use the CREATE TABLE keyword CREATE TABLE authors(auth_id int(9) not null,auth_name char(40) not null) Brad Lloyd & Michelle Zukowski

Using SQL • To insert data in the current table, use the keyword INSERT INTO INSERT INTO authorsvalues(‘000000001’, ‘John Smith’) • Then issue the statement SELECT * FROM authors 000000001 John Smith Brad Lloyd & Michelle Zukowski

Using SQL If you only want to display the author’s name and city from the following table: SELECT auth_name, auth_cityFROM publishers Brad Lloyd & Michelle Zukowski

Using SQL To delete data from a table, use the DELETE statement: DELETE from authorsWHERE auth_name=‘John Smith’ Brad Lloyd & Michelle Zukowski

Using SQL To Update information in a database use the UPDATE keyword UPDATE authorsSET auth_name=‘hello’ Hello Hello Sets all auth_name fields to hello Brad Lloyd & Michelle Zukowski

Using SQL To change a table in a database use ALTER TABLE. ADD adds a characteristic. ALTER TABLE authorsADD birth_date datetime null Type Initializer ADD puts a new column in the table called birth_date Brad Lloyd & Michelle Zukowski

Using SQL To delete a column or row, use the keyword DROP ALTER TABLE authorsDROP birth_date DROP removed the birth_date characteristic from the table Brad Lloyd & Michelle Zukowski

Using SQL The DROP statement is also used to delete an entire database. DROP DATABASE authors DROP removed the database and returned the memory to system Brad Lloyd & Michelle Zukowski

Conclusion • SQL is a versatile language that can integrate with numerous 4GL languages and applications • SQL simplifies data manipulation by reducing the amount of code required. • More reliable than creating a database using files with linked-list implementation Brad Lloyd & Michelle Zukowski

References • “The Practical SQL Handbook”, Third Edition, Bowman. Brad Lloyd & Michelle Zukowski

Design and Implementation