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

Process & Job control

• A Unix process is an instance of an executing program  It has a separate existence from all the other processes on the system we say that each process has its own address space
• A unique number known as its process-id or pid  
• The pid of its parent process is the process which started it,  usually a shell

Process state 

• Processes also have state
• One a single-CPU machine, only one process is running
• Most processes are sleeping, waiting for input or output to finish
• There are a few other odd states: swapped, zombie, or stopped. 

Job control 

• The mechanism that shuffle processes from foreground, background and stopped is known as job control

Job control requires three condition for support

• A shell that support job control.
• The terminal driver in the kernel must support job control
• Support for certain job-control signals must be provided.

Foreground and Background Tasks 

• Unix allows running processes to be disconnected from their terminals
• Unix has the concept of foreground processes which are attached to a user's terminal
• background processes, which have no terminal to read from

Job control & process management command

• ps        -  report process status
• kill      - terminate or signal processes

Control process execution

• jobs      - report job status 
• bg         - bring job to background process
• fg          - bring job to foreground process
• stop       - stop the job

Signals 

•   Signals are a technique used to notify a process that some condition has occurred.
•   Ordinary users can only send signals to their own processes. 
•   The command to send signal to process is  “kill”.

    example:

              $ kill -signal processid

Install Software Using Package Management

• pkginfo command to display information about the software packages that have been installed
• pkgadd command uncompresses and copies files from the installation media to the local system’s disk.
• pkgrm command deletes all files associated with that package unless those files are also shared with other packages.

Install Software From Source Code

Tool & Utility for Compile Source code

• Compiler & Library (GNUgcc &  libgcc)
• Make (GNUmake)
• Autoconfigure (GNUautoconfigure)
• Compress/Uncompress (gzip/gunzip/tar)

Apache Web Server Install

Overview for install apache

     $ ./configure --prefix=PREFIX

     $ make

     $ make install

NOTE: PREFIX by default use   

     "/usr/local/apache"

Configuration File

Finally you can customize your Apache HTTP server by editing the configuration files

        $ vi PREFIX/conf/httpd.conf

        $ vi PREFIX/conf/access.conf

        $ vi PREFIX/conf/srm.conf 

  And Statrt web server by 

        $PREFIX/bin/apachectl start

Most Important Configure

• ServerName avalon.bu.ac.th 
• ServerAdmin root@avalon.bu.ac.th
• DocumentRoot "/usr/local/apache/htdocs"

*Note ServerRoot and DocumentRoot  is difference meaning

The NFS Distributed File System

The NFS environment contains the following components:

• NFS server – A system that contains the file resources to be shared with other systems on the network.
• NFS client – A system that mounts the file resources shared over the network and presents the file resources as if they were local.

Configuring the NFS File Server

To set up an NFS server, complete the following steps:

1. Edit the /etc/dfs/dfstab file and add those file resources to be

       automatically shared whenever the system enters run level 3. For example:

       # share -F nfs /usr/share/man

2. Start the NFS server daemons by invoking the following:

       # /etc/init.d/nfs.server start

       This shares the contents of the /etc/dfs/dfstab file.

The NFS Client Setup

1.  Use the /usr/sbin/dfshares command to display a server’s available resources.

     # dfshares host1

2. Use the /usr/sbin/mount command to access the remote file resource.

    # mount host1:/usr/share/man /usr/share/man

3. To unmount the remote file resources from the client by using the /usr/sbin/umount command.

    # umount /usr/share/man

Account Management

Every user on a UNIX machine must have an account. 
Components of a user account are:

• login name (also called a username).
• password.
• The numeric user identifier or UID.
• The numeric group identifier or GID.
• home directory.
• login shell.
• startup files.

Add users from GUI tools

Configuration files related to user accounts

• /etc/passwd    -  the password file, holds most of an account characteristics including username, UID, GID,GCOS information, login shell, home directory 

• /etc/shadow  -  the shadow password file, a more secure mechanism for holding the password

•  /etc/group   -  the group file, holds characteristics about a system's groups including group name, GID and group members

Fields in /etc/passwd file 

• Each line in the /etc/passwd  file contains several fields, separated by a  colon  ( : ) , and is formatted as follows:

username:password:uid:gid:comment:

home-directory:loginshell

Fields in /etc/shadow file 

• Each line in the /etc/shadow  file contains several fields, separated by a colon  ( : ) , and is formatted as follows:

username:password:lastchange:min:max:

warn:inactive:expire

Startup Files

• Once the home directory is created the startup files can be copied in or created. Again you should remember that this will be done as the root user and so root will own the files. You must remember to change the ownership.

For example : 

The following is example set of commands that will perform 

these tasks.

• mkdir home_directory
• cp -pr /etc/skel/.* home_directory
• chown -R login_name home_directory
• chgrp -R group_name home_directory
• chmod -R 700 home_directory

Unix file type

• Regular file. A common file which contains data in text or binary. 
• Directory file. A file that contains name of other files and point to information on these files.
• Symbolic link. A type of file that point to another file.
• Character devices file. A type of file use for certain type of devices(read-write data in one character). 
• Block devices file. A type of file use for certain type of devices(read-write data in block). 
• FIFO. A type of file that use for interprocess communication,some time call named pipe. 
• Socket. A type of file that use for network communication. 

Regular File

A regular file simply holds data. Perhaps the most common file type found in the Solaris Operating Environment are regular files, which allow you to store many different kinds of data. Regular files can hold ASCII text, binary data, image data, databases, application-related data, and more.

Directory file

Directories store information that associates file names with inode numbers. Unlike regular files that can hold many different kinds of data, directories can hold only one kind.

Symbolic link

A symbolic link is a file that points to another file. Like directories,symbolic links contain only one kind of data.  A symbolic link contains the pathname of the file to which it points.

File type & symbol

File Access Permission

The following example shows the use of the ls –la command to display permission file

Permission and Owner Command

• chmod  [–R]  permission filename 

                        Changes access permission of a file.

 

• chown [-R] username:groupname filename

                       Changes the ownership of a file. 

• chgrp [-R] groupid filename

                       Changes the group ownership of a file. 

Unix Shell

• Unix is a layered operating system.
• The OS not allow applications accessing the hardware directly. 
• The shell acts as a command interpreter.
• It takes each command and passes it to the operating system kernel to be acted upon. And then displays the results of this operation

Relationship for Kernel,Shell and Application

Features provided by the shell

Types of shell

There are several different shells available for Unix. The most popular are described here.

•     Bourne shell (sh) 
•     C shell (csh)
•     Korn shell (ksh) 
•     Bourne Again Shell (bash)

Unix Command

• A command is a program that tells the Unix system to do something. 
• Usually has the form: command [options] [arguments]
• Commands are case sensitive. command and Command are not the same.
• Options and syntax for a command are listed in the  man page

Navigation and Directory Control Commands

File Maintenance Commands

Miscellaneous commands

Shutdown

• A UNIX machine should (almost) never be just turned off.
• It is also important for the Systems Administrator to understand what the correct mechanism is to shut a UNIX machine down
• There are a number of steps to carry out to ensure that the operating system and many of its support functions remain in a consistent state.

Step before shutdown

• List of all logged-in user by “who” command. You might sent broadcast message to let user know the system being shutdown
• Flush file system buffer to physical disk by “sync”
• Use Shut Down command

Commands to shut down

• /usr/sbin/shutdown
• /sbin/init
• /usr/sbin/halt
• /usr/sbin/reboot

Start up

Start up

• Before the UNIX operating system can perform correctly, there are a number of steps that must be followed
• The failure of any one of these can mean that the system will not start. 
• It is important for the Systems Administrator to be aware of what happens during system startup so that any problems that occur can be remedied.

Booting - consists of the following steps

• ROM Phase
• Boot Program Phase
• Kernel initialization Phase
• Init Phase

Boot ROM Phase

• After turn on power to the system, the ROM will display system identification and run self-test diagnostics to verify system hardware

• Then the ROM will loads primary boot program called bootstrap program that store in bootblock of a boot device

Boot Program Phase

• The boot strap program find and execute the secondary boot program from the file system and load it into memory

• After the secondary boot program is loaded, it will load the kernel

Kernel initialization Phase

• The kernel initializes itself (Create process control and other data structures)
• Load device driver modules
• Mounts the necessary file system
• Create the process 0 (swapper)
• Then, bring the system to “initdefault” state specified in /etc/inittab

Swapper

• The first task for the kernel is to start the swapper process.
• The swapper process is the part of the kernel that schedules all other process.
• The swapper has process ID of 0
• It’s first job is to start up the init process

Init Phase

• The kernel start /sbin/init process, which starts other process by reading the /etc/inittab file
• The /sbin/init process start the run control scripts

Run Control Script

• Each init state has a corresponding series of run control scripts 
• The init process executes the /sbin/rc<n> script which execute a series of other scripts located in /etc/rc<n>.d directory
• All run control scripts are also located in the /etc/init.d directory. These file are linked to run control scripts in /etc/rc<n>.d directory

Partition Your HDD

Partition Your HDD

Single

• Easy for manage
• All the space on the disk is available for anyone to use

Multiple

• Allow finer control over such task as a backup
• If one file system corrupted the others remain intact
• Allow you control amount of storage for allocate type of use

Partition Scheme

• Root (/)  
• swap  
• /usr
• /var
• /home

Post Install

• Setup Network Connection
• Install Additional Software
• Configure others service such as Printer ,NFS,WWW etc

Network Configuration

• Display and configure the network interface using the “ifconfig” command
• Ensure that the DNS is configured in file /etc/resolv.conf
• Ensure that the routing table is set by command “netstat –r”
• Ensure that the gateway is set in file /etc/defaultrouter

Introduction to Unix Operating System

Introduction to Unix Operating System 

An Overview of the Unix OS

At first, unix was designed to let a number of programmer access the same time and share its resource. The important features that make unix favorite are:

• Multitasking
• Multiuser
• Portability
• Unix Tools
• Unix Communication
• Library of application software

Unix Structure

Information before install

• Size of Memory and Hard Disk
• Bandwidth and Memory of Video Card
• Mouse type 
• Host Name
• Network information such as IP address, Gateway,Network Address,DNS Server
• Time Zone
• Root Password

Choose Installation Class

• Core system support

     Contains the minimum software required to boot and run Solaris. but not include the OpenWindows software.

• End-user system support

     Contains the core system support software plus end user software, which include OpenWindows and the Common Desktop Environment software.

• Developer system support

     Contains the end-user software plus libraries, include files, man pages, and programming tools for developing software.

• Entire distribution. 

     Contains the entire Solaris 8 release.

• Entire distribution plus OEM system support

     Contains the entire Solaris 8 release software plus extended   

     hardware support.