Chapter 11 Physical Database Design ObjectOriented Systems Analysis

Chapter 11: Physical Database Design Object-Oriented Systems Analysis and Design Joey F. George, Dinesh Batra, Joseph S. Valacich, Jeffrey A. Hoffer

Chapter Objectives l 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? l The part of a database design that deals with data integrity, efficiency, referential integrity, data access and security for the access of data l Processing speed, storage space, and data manipulation are key issues in physical database design

What Is SQL? l Structured Query Language l Often pronounced “sequel” l The standard language for creating and using relational databases l ANSI Standards – SQL-92 – most commonly available – SQL-99 – included object-relational features

Common SQL Commands l CREATE TABLE – used to define table structures and link tables together l SELECT – used to retrieve data using specified formats and selection criteria l INSERT – used to add new rows to a table l UPDATE – used to modify data in existing table rows l DELETE – used to remove rows from tables

Example CREATE TABLE Statement • Create Table DEPT ( deptno number(2) not null, deptname varchar 2(14), location varchar 2(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 l This statement inserts a new row into the DEPT table: DEPTNO value is 50 l DNAME value is “DESIGN” l LOCATION value is “MIAMI” l

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 l select * from DEPT l Result: All fields of all rows in the DEPT table

SELECT Statement Example 2 – with a filter l select * from EMP where ENAME = ‘SMITH’ l Result: All fields for employee “Smith”

SELECT Statement Example 3 – only 2 cols, and a sort l select EMPNO, ENAME from EMP where JOB = ‘SALESMAN’ order by ENAME l Result: employee number, name and job for only salesmen from the EMP table, sorted by name

What Is a Join Query? l A query in which the WHERE clause includes a match of primary key and foreign key values between tables that share a relationship l A join could also happen between the values of a unique index of one table and a foreign key of another l 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 l select EMPNO, ENAME, DNAME from EMP, DEPT where EMP. dept_no = DEPT. dept_no and DEPT. LOC = ‘CHICAGO’; l Result: All employees’ number and name (from the EMP table, and their associated department names, obtained by joining the tables based on DEPT_NO. l Only employees housed in department located in Chicago will be included

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? l A query results in summary information about a group of records, such as sums, counts, or averages l These involve using aggregate functions in the SELECT clause such as (SUM, AVG, COUNT, MIN and MAX) l 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 l select JOB, AVG(SALARY) from EMP group by JOB l Select the job category and average salary of employees in the EMP table. l Group (sub-total) the result for each JOB category

SELECT Statement Example 6 – Filter after Aggregation l select JOB, AVG(SALARY) from EMP group by JOB having AVG(SALARY) >= 3000; l Only jobs with average salaries exceeding $3000 will be displayed l 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 l UPDATE EMP set SAL = 3000 where EMPNO = 7698; – Modifies the existing employee’s 7698 salary l DELETE from EMP where EMPNO = 7844 – Removes employee 7844 from the EMP table

Designing Fields l Field – the smallest unit of named data element recognized by system software such as a DBMS l Fields map roughly onto attributes in the conceptual data model l 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 l Balance these four objectives: Minimize storage space 2. Represent all possible values of the field 3. Improve data integrity for the field 4. Support all data manipulations desired for the field (e. g. mathematical, text) 1.

Data Integrity Controls l Null Value Controls – determines whether fields can be left empty l Default Values – specifies values for fields if no explicit values are entered l Range Controls – forces values to be among an acceptable set of values l Format Controls – restricts data entry values in specific character positions

Data Integrity Controls (cont) l Referential Integrity – forces foreign keys to align with primary keys (or other unique indexes) l Unique Control – forces each value within the column to be unique l 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? l The process of combining columns from various normalized tables into a combined table based on usage and retrieval frequencies l Results in better speed of access for SELECT l 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 region. Manager data

Example of M: N Denormalization

Example of Denormalization If I combine both Item and Can. Supply into Can. Supply. DR, 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? l. A technique for physically organizing the data in a DBMS. It includes determining tablespace sizes, index sizes, the placement of tables and indexes, etc. l Main purpose of file organization which is a DBA function, is to optimize speed of data access and modification

What Is Hashing? l. A technique that uses an algorithm to convert a key value to an memory address l Useful for random access, but not for sequential access (e. g. using a phone number) l Best suited for densely populated data

What Is an Indexed File Organization? l A storage structure involving indexes, which are key values and pointers to row addresses l Indexed file organizations are structured to enable fast random and sequential access l 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 l 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 11 g Creating Using

Composite Attributes in Oracle 11 g (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 -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 11 g • create type PH_AR as varray(10) of varchar 2(15); • create table CONTACT ( contact. No number, name varchar 2(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 t 1. contact. No, t 1. name, t 2. column_value from CONTACT t 1, table(phones) t 2;

Generalization in Oracle 11 g • create type EMP_TYPE as object ( emp. No emp. Name emp. Addr ) Create a type as an object number, varchar 2(50), varchar 2(100) not final; Allow for subtype • create type HOURLY_TYPE under EMP_TYPE ( hourly. Rate 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 11 g • create cluster ORDER_CLUSTER ( order. No number ); Create a cluster • create table ORDER_TABLE ( order. No order. Date ) number, date cluster order_cluster (order. No); Request a common cluster • create table ORDER_LINE_TABLE ( order. No number, product. No varchar 2(10), price number, quantity number ) cluster order_cluster (order. No); • create index ord_idx Index the cluster on cluster order_cluster;

Recap l 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.