Database Design

1
Database Design

• Sections 13 16- Introduction to HTML DB SQL
  editor, Introduction to SQL statements

2
Using HTML_DB SQL editor

• Reference Lab 6 problem 1
• You may either type the command into the SQL
  editor or use the cut and paste option
• If you are going to cut/paste the command copy
  the command from the word document into NotePad.
  This will drop out hidden characters.
• Next copy the command from the NotePad into the
  editor

3
Enter SQL command
4
Display the Table structure

• Enter the following command
• DESCRIBE MUSIC
• The structure of the table should be shown.
• Results on next slide

5
Music Table Structure

6
Typical error messages

• The following statement has a spelling error
• SELCT FROM employeesThe error message is
        ORA-00900 invalid SQL statement
• The following statement incorrectly names the
  table employee instead of employees
• SELECT FROM employeeThe error message is
        ORA-00942 table or view does not exist

7
Insert Data

• Note the data types for each column
• Inserting data into the table.
• Since the table is empty all fields need to be
  populated, so column names can be omited
• INSERT INTO musicVALUES (10,'Marge
  Hohly','Folk')
• This will insert one record into the table
• Next display the contents of the table to view
  the data
• SELECT FROM music

8
Resulting Screen
9
Inserting Data

• INSERT INTO tablename (column1,
  column2,....)VALUES(value1,value2,...)
• Remember character data needs to be enclosed in
  single quotes.

10
SQL DESCRIBE

• DESCRIBE lttable namegt
• DESCRIBE employeesTry the last statement.

11
Subset of data WHERE clause

• SELECT ltcolumn name 1, column name 2, etc.gt FROM
  lttable namegtWHERE ltconditiongt
• SELECT first_name, last_name, salaryFROM
  employeesWHERE salary gt 5000

12
Insert Data into table

• INSERT INTO lttable namegtVALUES (value 1, value
  2, value 3, etc)
• INSERT INTO musicVALUES (10,'Marge
  Hohly','Folk')

13
ALTER table structure

• ALTER TABLE lttable namegtADD (ltnew_column_namegt
  ltdata typegt)
• Try to modify the structure of the MUSIC table
• Add a column (which you will misspell) COUNTRI
  instead of COUNTRY).

14
Deleting a column from a table

• ALTER TABLE lttable namegtDROP COLUMN
  ltcolumn_namegt
• Now delete the column (COUNTRI) you just added.

15
Deleting a row

• DELETE from lttable namegtWHERE ltcolumn_namegt
  'some value'
• DELETE from musicWHERE musicid 10
• DELETE from musicWHERE type Folk

16
Database Facts

• Currently 20 of the world's data resides in
  relational DBMSs.
• In the next two years, databases are expected to
  grow larger than 100 terabytes. A database this
  big would be able to store 100,000 copies of the
  Encyclopedia Britannica or 200,000 hours of music
  or about 10 billion web pages.
• The top 10 world's largest databases using the
  Oracle DBMS are
• France Telecom, 29.2TB -- a communications
  company (a TB is a terabyte equivalent to 1,000
  gigabytes)
• Amazon.com with, 13 TB -- selling books and
  merchandise
• The Claria Corporation,12TB -- Internet
  behavioral marketing company tracking Internet
  user behavior

17
HTML DB SQL editor

• The SQL course will use the three following sets
  of database tables for examples and practice
  exercises.
• Oracle tables COUNTRIES, REGIONS, DEPARTMENTS,
  EMPLOYEES, JOBS, JOB_HISTORY AND JOB_GRADES
• DJs on Demand database tables D_CDS, D_PACKAGES,
  D_TYPES, D_THEMES, D_CLIENTS, D_VENUES, D_SONGS,
  D_TRACK_LISTINGS, D_PARTNERS, D_EVENTS,
  D_PLAY_LIST_ITEMS, D_JOB_ASSIGNMENTS
• Global Fast Foods database tables F_CUSTOMERS,
  F_REGULAR_MENUS, F_PROMOTIONAL_MENUS, F_SHIFTS,
  F_STAFFS, F_FOOD_ITEMS, F_ORDERS, F_ORDER_LINES,
  F_SHIFT_ASSIGNMENTS
• Print out these tables for your reference when
  using the HTML DB editor
• These tables are available on the Student
  Resource web page for this class

18
Review the tables

• There are six properties of tables in a
  relational database
• Property 1 Entries in columns are single-valued.
• Property 2 Entries in columns are of the same
  kind.
• Property 3 Each row is unique.
• Property 4 Sequence of columns is insignificant.
• Property 5 Sequence of rows is insignificant.
• Property 6 Each column has a unique name.

19
Categories of SQL Statements

• Data manipulation language (DML) statements
• Begin with INSERT, UPDATE, DELETE, or MERGE
• Used to modify the table by entering new rows,
  changing existing rows, or removing existing
  rows.
• Data definition language (DDL) statements set up,
  change, and remove data structures from the
  database.
• The keywords CREATE, ALTER, DROP, RENAME, and
  TRUNCATE begin DDL statements.
• Transaction control (TCL) statements are used to
  manage the changes made by DML statements.
• Changes to the data are executed using COMMIT,
  ROLLBACK, and SAVEPOINT. TCL changes can be
  grouped together into logical transactions.
• Data control language (DCL)
• keywords GRANT and REVOKE are used to give or
  remove access rights to the database and the
  structures within it.

20
16.2.6 Questions

• The Fast Foods database consists of how many
  tables? ____ tables
• How is the F_SHIFTS table related to the F_STAFFS
  table?
• What are the names of the columns in the
  F_CUSTOMERS table?
• How many rows of data have been entered in the
  F_PROMOTIONAL_MENUS table?
• In the F_FOOD_ITEMS table, column _________ is a
  foreign-key column. What table and column is this
  key referencing?
• List the primary key to foreign key relationships
  required to go from the F_SHIFTS table to the
  F_REGULAR_MENUS table.
• Which table(s) contain null values?

21
KEYWORD, CLAUSE, STATEMENT

• Throughout this course, the words keyword,
  clause, and statement are used as follows
• A keyword refers to an individual SQL element.
  For example, SELECT and FROM are keywords.
• A clause is a part of a SQL statement. SELECT
  employee_id, last_name, .... is a clause.
• A statement is a combination of two or more
  clauses.SELECT FROM employees is a
  SQL statement.

22
Selection vs. Projection

• SELECT salaryFROM employeesWHERE last_name like
  Smith
• Selection (row) Projection (column)

23
What is null?

• If a row lacks the data value for a particular
  column, that value is said to be null, or to
  contain a null. A null isa value that is
  unavailable, unassigned, unknown, or
  inapplicable.
• A null is not the same as zero. Zero is a number.
• A null is not a space. Space is a character.

24
Arithmetic Expressions

• Create expressions with number and date data by
  using arithmetic operators.

25
Operator Precedence

• Operator Precedence
• Multiplication and division take priority over
  addition and subtraction.
• Operators of the same priority are evaluated from
  left to right.
• Parentheses are used to force prioritized
  evaluation and to clarify statements.
• Remember Please excuse my dear aunt Sally

26
Rewrite in order of precedence

• You want to calculate the annual salary if the
  employee received 100 raise each month
• Revise the following statement in the correct
  order of precedence to get the desired results
• Run each example in the editor
• SELECT last_name, salary, 12salary100FROM
  employees

27
Null Values in Arithmetic Expressions

• SELECT last_name, salary, commission_pct,
  salarycommission_pctFROM employees

28
Practice

• SELECT FROM d_songs
• SELECT id, title, duration, artist,
  type_codeFROM d_songs
• SELECT id, title, artist, FROM d_songs
• SELECT last_name, salary, salary 300FROM
  employees
• SELECT last_name, salary, salary 1.05FROM
  employees

29
Column Alias

• Renames a column heading
• Is useful in naming columns of derived values
• Immediately follow the column name
• Uses optional AS keyword between the column name
  and alias
• Required double quotation marks if it contains
  spaces or special characters or is case sensitive

30
Using Aliases

• SELECT last_name name, salary AS Salary,
  salary12 Annual SalaryFROM employees