Transcript create table - dbmanagement.info

Chapter 11:
Physical Database Design
Object-Oriented Systems Analysis and
Design
Joey F. George, Dinesh Batra,
Joseph S. Valacich, Jeffrey A. Hoffer
Chapter Objectives

After studying this chapter you should
be able to:
– Understand the purpose of SQL
– Design database fields.
– Evaluate denormalization situations.
– Design file organization structures.
– Design object-relational features.
What Is
Physical Database Design?

The part of a database design that deals
with data integrity, efficiency, referential
integrity, data access and security for the
access of data

Processing speed, storage space, and data
manipulation are key issues in physical
database design
What Is SQL?
 Structured Query Language
 Often pronounced “sequel”
 The standard language for creating and using
relational databases
 ANSI Standards
– SQL-92 – most commonly available
– SQL-99 – included object-relational features
Common SQL Commands

CREATE TABLE – used to define table structures and
link tables together

SELECT – used to retrieve data using specified formats
and selection criteria

INSERT – used to add new rows to a table

UPDATE – used to modify data in existing table rows

DELETE – used to remove rows from tables
Example CREATE TABLE
Statement
• Create Table DEPT
(
deptno
number(2) not null,
deptname
varchar2(14),
location
varchar2(13),
constraint DEPT_PK primary key (deptno) )
• Here, a table called DEPT is created,
with a numeric primary key (deptno) and
two text fields (deptname and location).
Example INSERT Statement


This statement inserts a new row into the
DEPT table:
DEPTNO value is 50
 DNAME value is “DESIGN”
 LOCATION value is “MIAMI”
The SELECT Statement
• SELECT
<column>, <expression>, *, …
FROM
<table>, <view>, <inline_view>, …
WHERE
<filter> , <join condition> and/or …
GROUP BY <column>, <expression>, …
HAVING <filter> and/or …
ORDER BY <column>, …
• The SELECT, and FROM clauses are required.
• All others are optional.
2 tables
The
EMP
table
The
DEPT
table
SELECT Statement
Example 1 – Simple SELECT

select * from DEPT

Result: All fields of all rows in the DEPT table
SELECT Statement
Example 2 – with a filter

select * from EMP
where ENAME = ‘SMITH’

Result: All fields for employee “Smith”
SELECT Statement
Example 3 – only 2 cols, and a sort

select EMPNO, ENAME
from EMP
where JOB = ‘SALESMAN’
order by ENAME

Result: employee number, name and job for only
salesmen from the EMP table, sorted by name
What Is a Join Query?

A query in which the WHERE clause includes a
match of primary key and foreign key values
between tables that share a relationship

A join could also happen between the values of a
unique index of one table and a foreign key of
another

A join could also be on 2 columns that share the
same values, and are not keys (although the join
operation would be very inefficient)
SELECT Statement
Example 4 – Joining 2 tables

select
from
where
and

Result: All employees’ number and name (from the
EMP table, and their associated department names,
obtained by joining the tables based on DEPT_NO.

Only employees housed in department located in
Chicago will be included
EMPNO, ENAME, DNAME
EMP, DEPT
EMP.dept_no = DEPT.dept_no
DEPT.LOC = ‘CHICAGO’;
SELECT Statement
Example 4 (continue)
Join queries almost always involve matching
the primary key of the dominant (parent) table
with the foreign key of the dependent (child) table.
What Is an Aggregation Query?

A query results in summary information about a
group of records, such as sums, counts, or averages

These involve using aggregate functions in the
SELECT clause such as (SUM, AVG, COUNT,
MIN and MAX)

Aggregations can be grouped using the GROUP BY
clause and can/or can be filtered using the HAVING
clause
SELECT Statement
Example 5 – Using Aggregation

select JOB, AVG(SALARY)
from
EMP
group by JOB

Select the job category and average salary of employees
in the EMP table.

Group (sub-total) the result for each JOB category
SELECT Statement
Example 6 – Filter after Aggregation

select JOB, AVG(SALARY)
from
EMP
group by JOB
having AVG(SALARY) >= 3000;

Only jobs with average salaries exceeding $3000 will
be displayed

Using Having instead of Where
SELECT Statement
Example 6 (continue)
Note that job categories CLERK and SALESMAN
were not included, because the average salaries for
these jobs are below $3000.
Example Data Manipulation
 UPDATE EMP
set SAL = 3000
where EMPNO = 7698;
– Modifies the existing employee’s 7698 salary
 DELETE from EMP
where EMPNO = 7844
– Removes employee 7844 from the EMP table
Designing Fields

Field – the smallest unit of named data
element recognized by system software such
as a DBMS

Fields map roughly onto attributes in the
conceptual data model

Field design involves consideration of
identity, data types, sizes, and constraints
Data Types
A codification scheme recognized by the
software for representing types of data
Considerations for Choosing
Data Types

Balance these four objectives:
1.
Minimize storage space
Represent all possible values of the field
Improve data integrity for the field
Support all data manipulations desired
for the field (e.g. mathematical, text)
2.
3.
4.
Data Integrity Controls

Null Value Controls – determines whether
fields can be left empty

Default Values – specifies values for fields
if no explicit values are entered

Range Controls – forces values to be
among an acceptable set of values

Format Controls – restricts data entry
values in specific character positions
Data Integrity Controls (cont)

Referential Integrity – forces foreign keys
to align with primary keys (or other unique
indexes)

Unique Control – forces each value within
the column to be unique

Indexing – forces the column to be indexed
for faster retrieval
Referential Integrity
Referential
integrity is
important
for ensuring
that data
relationships
are accurate
and consistent
Ensures that no value in the cust_order (customer_id) field
exists without a corresponding value in customer table.
What Is Denormalization?

The process of combining columns from
various normalized tables into a combined
table based on usage and retrieval frequencies

Results in better speed of access for SELECT

However, reduces data integrity and increases
data redundancy
Example of 1:1 Denormalization
This will result in null values for all students who have not
applied for a scholarship
Example of 1:N Denormalization
This will result in Duplicate regionManager data
Example of M:N Denormalization
Example of Denormalization
If I combine both Item and CanSupply into CanSupplyDR,
this will result in duplication of item descriptions
(We’re assuming that an item can be supplied by many
different vendors)
What Is a File Organization?

A technique for physically organizing the
data in a DBMS. It includes determining
tablespace sizes, index sizes, the placement
of tables and indexes, etc.

Main purpose of file organization which is a
DBA function, is to optimize speed of data
access and modification
What Is Hashing?


A technique that uses an algorithm to
convert a key value to an memory address
Useful for random access, but not for
sequential access (e.g. using a phone number)
 Best suited for densely populated data
What Is an
Indexed File Organization?

A storage structure involving indexes, which are
key values and pointers to row addresses

Indexed file organizations are structured to enable
fast random and sequential access

Index files are fast for queries, but require
additional overhead for inserts, deletes, and
updates
Random Access Using a
B-Tree Organization
Best suited for randomly balanced data.
Which Fields should be Indexed?
Primary Keys
are always
indexed
Number of rows
in the object
No
Number
small?
Yes
Design of Object Relational
Features

Object-relational databases support creation
of custom data types.
(available in recent releases of DBMS)
– Composite attributes
– Multi-valued attributes
– Generalization and inheritance
– Aggregation/Composition relationships
Composite Attributes
Allows you to map a composite attribute onto a
custom data type
Table Employee
uses the composite
data type
Person_Type is a
composite data type,
and so is Name_Type
and Address_Type
Composite Attributes in Oracle 11g
Creating
Using
Composite Attributes in Oracle 11g
(Continue)
Create table employee
( empid number,
emp person_type );
Insert into employee
values (123, person_type(
name_type(‘Sultan’, ‘Sam’, ‘E’),
addr_type(‘123 main street’, ‘New York’, ‘NY’, ‘12345’),
‘01-May-1984’, ‘212-123-1234’)
);
Select e.empid, e.emp.name.lastname, e.emp.name.firstname,
e.emp.address.street, e.emp.address.city, e.emp.birthdate
from employee e
Multivalued Attributes in Oracle 11g
• create type
PH_AR as varray(10) of varchar2(15);
• create table CONTACT
(
contactNo number,
name
varchar2(50),
phones
PH_AR
);
• insert into CONTACT
Create an array
Use the array
values (53, ‘Sam Sultan’, PH_AR(‘212-111-2222’, ‘222-1234’) );
• select t1.contactNo,
t1.name, t2.column_value
from CONTACT t1, table(phones) t2;
Generalization in Oracle 11g
• create type EMP_TYPE as object
(
empNo
empName
empAddr
Create a type
as an object
number,
varchar2(50),
varchar2(100)
) not final;
Allow for subtype
• create type HOURLY_TYPE under EMP_TYPE
(
hourlyRate number
);
• create table HR_EMPLOYEE of
• insert into HR_EMPLOYEE
Create a subtype
HOURLY_TYPE;
Use the subtype
values (100, ‘Sam Sultan’, ‘123 Main St’, 24.50);
Aggregation/Composition in Oracle 11g
• create cluster ORDER_CLUSTER
( orderNo
number );
Create a cluster
•create table ORDER_TABLE
( orderNo
orderDate
number,
date
) cluster order_cluster (orderNo);
Request a common
cluster
• create table ORDER_LINE_TABLE
( orderNo
number,
productNo varchar2(10),
price
number,
quantity
number
) cluster order_cluster (orderNo);
• create index ord_idx
Index the cluster
on cluster order_cluster;
Recap

After studying this chapter we learned to:
– The purpose and usage of SQL
– Design database fields.
– Evaluate denormalization situations.
– Design file organization structures.
– Design object-relational features.