SQL – Data Definition - 4

System Tables…

• Whenever definition change occurs those activities will be recorded in the database.
• Those information are kept in tables known as system tables.
• Similar to the tables we created earlier but this will maintain only by the database itself.
• No user intervention is needed.
• This type of data is called meta data.

Schemas…

• Used to group database objects such as tables, views, stored procedures, etc
• Can be created using CREATE SCHEMA statement

Example…

• Create an academic schema and allow it access to the user, AccDiv
• CREATE SCHEMA Academic AUTHORIZATION AccDiv
• Then create the table Modules in this schema
• The name given to a table must be unique within the schema
• Same object name can be created in another schema
• DROP SCHEMA Academic

Catalogs…

• A catalog is a named group of schemas.
• Virtual one.
• There is no CREATE CATALOG statement.
• Each catalog should contain a schema called INFORMATION_SCHEMA which defines no.of views for system tables.
• INFORMATION_SCHEMA.TABLES
• INFORMATION_SCHEMA.COLUMNS

SQL – Data Definition - 3

DROP TABLE….

• Delete table definition permanently from the database
• DROP TABLE Modules
• Can not recover again unless you use a database backup

MODIFYING TABLES…

• Add an extra column
• Drop a column
• Modify the data type or length
• Add a new table constraint
• Drop a table constraint
• Change a column property (Ex: DEFAULT VALUE, NULL NOT NULL, etc)

ALTER TABLE…

• ALTER TABLE Lecturers

          ADD COLUMN roomNo SMALLINT

• ALTER TABLE Lecturers

         ALTER COLUMN staffName VARCHAR(20)

• ALTER TABLE Lecturers

        DROP COLUMN staffName 

• ALTER TABLE Lecturers

       ALTER COLUMN level SET DEFAULT 3

SQL – Data Definition - 2

CREATE TABLE…

CREATE TABLE Modules
(
moduleName CHARACTER(15)
,level SMALLINT
,courseCode CHARACTER(3)
,staffNo INTEGER
)

DATA TYPES…

• Act in part as a definition for domains.
• Every data value within the column must be of the same type.
• Define certain properties concerning the allowable values for a domain.
• Imagine an Excel sheet for an example.
• STRING Types

                          CHARACTER(n)

                          CHARACTER VARYING(n)

                          BIT(n)

                          BIT VARYING(n)

• NUMERIC Types

                            NUMERIC(m,n) A synonym for decima

                            DECIMAL(m,n)

                            INTEGER or INT

                            SMALLINT 

                            FLOAT (A number stored in floating point representation)

                            REAL (A synonym for FLOAT)

                            DOUBLE PRECISION (A synonym for FLOAT)

• DATETIME Types

                           DATE

                           TIME

                           TIMESTAMP (Date and times)

                           INTERVAL (Intervals between dates)

Other Domain Properties…

• NOT NULL – user must enter value for that column
• UNIQUE – prohibits the user from entering duplicates
• DEFAULT VALUES – Value a column should take in case user didn’t enter the value

Example…

CREATE TABLE Modules

(

moduleName CHARACTER(15) NOT NULL UNIQUE

,level SMALLINT DEFAULT 1

,courseCode CHARACTER(3)

,staffNo INTEGER

)

Database Design and Development SQL – Data Definition

History of SQL…

• Developed by IBM as SEQUEL in 1970s
• Oracle coperation took the ownership of SQL after 1977
• ANSI standardized SQL in 1986 – SQL1
• SQL2 introduced in 1992
• SQL3 was produced in 1999

Database Sublanguage…

• SQL2 standard can be divided in to four major parts;

DDL – commands concerned with declaring the structure of schemas and tables.
DML – commands concerned with retrieving data and updating data in tables. 
DIL – data integrity is attached to the DD. Commands concerned with declaring entity, referential and domain integrity.
DCL – commands concerned with administering the database.

Third Normal Form (3NF)

Remove transitive dependencies

Transitive dependency

• A non-prime attribute is dependent on another, non-prime attribute or attributes
• Attribute is the result of a calculation

Examples:

• Area code attribute based on City attribute of a customer
• Total price attribute of order entry based on quantity attribute and unit price attribute (calculated value)

Solution:

•  Any transitive dependencies are moved into a smaller table.

Transitive Dependence

Second Normal Form (2NF)

Each attribute must be functionally dependent on the primary key.

•  If the primary key is a single attribute, then the relation is in 2NF
• The test for 2NF involves testing for FDs whose left-hand-side 
• attribute are part of the primary key
• Disallow partial dependency, where non-keys attributes depend on
•  part of a composite primary key
• In short, remove partial dependencies

2NF improves data integrity.

• Prevents update, insert, and delete anomalies.

First Normal Form (1NF)

Each attribute must be atomic (single value)

•  No repeating columns within a row (composite attributes)
•  No multi-valued columns.

1NF simplifies attributes

•  Queries become easier.

1NF

Database Normalization

Functional dependency (FD) X----------> Y         means that if
there is only one  possible  value of  Y for every value of X, then
Y is Functionally dependent on X.

• Functional Dependency is “good”.  With functional dependency the primary key (Attribute A) determines the value of all the other non-key attributes (Attributes B,C,D,etc.)

• Transitive dependency is “bad”.  Transitive dependency exists if the primary/candidate key (Attribute A) determines non-key Attribute B, and Attribute B determines non-key Attribute C.

• If a relation schema has more than one key, each is called a candidate key

• An attribute in a relation schema R is called prim if it is a member of some candidate key of R

Normalization

Database Normalization

• Proposed by Codd (1972)
• Introduced 3 normal forms, the first, second and third normal form
• A stronger definition of 3NF  - called Boyce-Codd normal form (CDNF) was               proposed later
• Later, 4NF and 5NF were proposed

The minimum, and most common, goal is to achieve 3NF.



Normalization Is the process of analyzing the given relational schema based on its functional dependencies and keys to achieve the desirable properties of:

• Minimizing redundancy
• Minimizing the insertion, deletion, and updating  anomalies
• Minimize data storage
• Unsatisfactory relation schema that do not meet  a given normal form test are decomposed into smaller relational schemas that meet the test and hence possess the desired properties.
• Key Concepts in normalization are Functional Dependency and keys

Example

• Sales

(Order#, Date, CustID, Name, Address, City, State, Zip, {Product#, ProductDesc, Price, QuantityOrdered}, Subtotal, Tax, S&H, Total)

What are the problems with using a single table for all order information?

• Insert Anomaly
• Update Anomaly
• Delete Anomaly