DATABASE MANAGEMENT SYSTEM

DATABASE MANAGEMENT SYSTEM

Writer Dahlia Binti Lokeman

Published by POLYTECHNIC OF SULTAN HAJI AHMAD SHAH (POLISAS) Semambu 25350 Kuantan, Pahang Tel : 09-5655 300

Fax : 09 5663 104 www.polisas.edu.my

Copyright © 2022, by Politeknik Sultan Haji Ahmad Shah

All rights reserved. No part of this publication may be reproduced or distributed in any form or by any means, or stored in a database or retrieval system, without the prior written consent of POLISAS except for personal and non commercial use without the prior written permission of the author.

Preface

The traditional mechanism for storing computer data was data files. In fact, even today, many major computer applications run on file based computer systems. However, all this has also changedinthelast fewdecades. DatabaseManagementSystems(DBMS)hasbecomeasubject of great significance in the Information Technology industry. Most serious business applications need the presence of DBMS in some form or the other. DBMS is replacing files as the standard for storing data that is medium/long term in nature. This is especially true in the case of most newly developed applications.

DBMS is a fascinating subject. Understanding how DBMS technology really works involves the study of many theoretical concepts, such as database design, modelling, transaction management, security, concurrency, and so on. It provides comprehensive coverage of fundamentals of database management system. This book is for those who wish a better understanding of relational data modelling, its purpose, its nature, and the standards used in creating relational data model.

DBMS provides the knowledge of basic concepts of a database system and how to communicate with a database system. The main focus in this book is on relational databases and Structured Query Language(SQL) command. It requires students to design, develop, modify, implement and maintain the database systems. It also emphasizes on the profound database models as a guide to create a stable database system underlying most of the application. DAHLIA LOKEMAN Lecturer

Polytechnic of Sultan Haji Ahmad Shah Kuantan Pahang

Table Of Content

Chapter 1: INTRODUCTION TO DATABASE SYSTEM

Chapter 2: DATA MODELLING

Chapter 3: STRUCTURED QUERY LANGUAGE(SQL)

Chapter 4 : FUNCTIONS IN STRUCTURED QUERY LANGUAGE(SQL)

Chapter 1

INTRODUCTION TO DATABASE SYSTEM

In this chapter, you will learn how to: Expose database environment

Determine database management system

Database and database technology have a major impact on the growing use of computers. It is fair to say that databases play a critical role in almost all areas where computers are use, including business, electronic commerce, engineering, medicine, genetics, law, education and library science.

1.1 DATABASE ENVIRONMENT

Database System Concept

Database is a collection of related data shared bythe various categories users, to meet the needs and requirements of an organization. The database application: A program interact with the database at the time program execution

Data and Information

DATA is raw material for data processing. Data relates to fact, event and transactions. Data refers to unprocessed information. INFORMATION is data that has been processed in such a way as to be meaningful to the person who receives it. It is anything that is communicated.

Database Schemes, Model and Database Instances

Independent from the database model it is important to differentiate between the description of the database and the database itself. The description of the database is called database scheme or also metadata. The database scheme is defined during the database design process and changes very rarely afterwards.

The actual content of the database, the data, changes often over the years. A database state at a specific time defined through the currentlyexisting content and relationship and their attributes is called a database instance

The following illustration shows that a database scheme could be looked at like a template or building plan for one or several database instances.

Figure 1.1 : Analogy

Analogy Database Schemes and Building Plans

When designing a database it is differentiated between two levels of abstraction and their respective data schemes, the conceptual and the logical data scheme.

Conceptual Data Scheme

A conceptual data scheme is a system independent data description. That means that it is independent from the database or computer systems used.

Logical Data Scheme

A logical data scheme describes the data in a data definition language DDL of a specific database management system.

The conceptual data scheme orients itself exclusively by the database application and therefore bythe real world. It does not consider anydata technical infrastructure like DBMS or computer systems, which are eventually employed. Entity relationship diagrams and relations are tools for the development of a conceptual scheme.

When designing a database the conceptual data scheme is derived from the logical data scheme This derivation results in a logical data scheme for one specific application and one specific DBMS. A DB Development System converts then the logical scheme directly into instructions for the DBMS.

Figure 1.2 : Schematic Representation of the Different Schemes

Database Models

Database models are from the category of informatic models and are therefore exact models or implementation models respectively. Database models can be used quite often and are not restricted to spatial types of problems.

Examples for database models:

i. The relational database model.

ii. The object-relational database model.

iii. In the intermediate module data management, the representation of spatial data models in database models and the representation of spatial data in geodatabase system will be discussed.

Instances and schemas i. Instance

At any instant of time the content of the database is called the instance of the database. ii. Schema

Logical description of the database is known as schema of the database.

Two types:

a. Physical schema describes the database design at physical level.

b. Logical schema describes the database design at logical level.

Physical data independence if application programs are not depending on the physical schema and thus need not to be rewritten if the physical schema changes.

Database Three Schema Architecture

Three schema architecture is an idea in relational database design that breaks a database down into three different categories according to its use and structure, and to the roles played by system administrators, designers and end users.

Developed in the 1970s, the three schema architecture helps to evaluate a relational database from different vantage points. The first of the three levels is called the external level or user level. This is the view of the relational database that end users see, and it involves a high level ofabstraction.Thesecondlevelisthelogicalschemaorconceptuallevel,wheredesignerswork. Thethirdlevel is thephysical schemaorphysical level, whereprogrammersmaintainadatabase on a hardware system. The three schema architecture is generally attributed to the ANSI/SPARC group and is sometimes also called “ANSI/SPARC” architecture.

Part of the usage of the three schema architecture is to look at how the design maintenance differs from the core system maintenance. For example, items dealing with database tables and queries belong in the conceptual or logical schema, where issues such as memory handling are looked at in the physical level. Some IT experts talk about the three schema architecture in the context of changing levels without affecting other levels or in terms of data independence. In addition,thethree schemaarchitecturealso performsabreakdownofthecoredutiesofdatabase designers, network administrators or server maintenance teams.

: Database System Environment

There are three schema architecture

i. Internal level/storage level ii. Conceptual level/logical level iii. External level/interface

i. Internal level

It is physical representation of database. This level describes how the data is stored in database. It covers the data structures and file organization.

ii. Conceptual level

This level describes what data is stores and relationship among the data

a. All entities, attributes and their relationship

b. Comstraints on the data

c. Security and integrity interface

iii. External level

This level describes that part of database that is relevant to each user.

Database management system can be seen as either single tier or multitier. Architecture divides the whole system into related but independent and modules. i. 1 tier architecture: user interface, presentation service and application service ii. 2 tier architecture

It is a client serve arch, direct communication and run faster iii. 3 tier archictecture

It separates ties from each other based on complexity of the users and how they use the data present database. It is a web based application. Three layers divide into client layer, business layer and data layer.

1.2 DATABASE MANAGEMENT SYSTEM

Database Management System is a software package with computer program that controls the creation, maintenance, and use of a database.

DBMS contains information about a particular enterprise

i. Collection of interrelated data

ii. Set of programs to access the data

iii. An environment that is both convenient and efficient to use

Database Applications:

i. Banking: all transactions

ii. Airlines: reservations, schedules

iii. Universities: registration, grades

iv. Sales: customers, products, purchases

v. Online retailers: order tracking, customized recommendations

vi. Manufacturing: production, inventory, orders, supply chain vii. Human resources: employee records, salaries, tax deductions

Component of Database Management System

i. HARDWARE

Hardware consists of a set of physical electronic devices such as computers (together with associated I/O devices like disk drives), storage devices, I/O channels, electromechanical devices that make interface between computers and the real world systems etc, and so on. It is impossible to implement the DBMS without the hardware devices, In a network, a powerful computer with high data processing speed and a storage device with large storage capacity is required as database server.

ii. SOFTWARE

The software component comprises the DBMS software itself and the application programs, together with the operation system, including network software if the DBMS is being used over a network.

iii. DATA

Data is the most important component of the DBMS. The main purpose of DBMS is to process the data. In DBMS, databases are defined, constructed and then data is stored, updated and retrieved to and from the databases. The database contains both the actual (or operational) data and the metadata (data about data or description about data).

iv. PROCEDURE

Procedures refer to the instructions and rules that help to design the database and to use the DBMS. The users that operate and manage the DBMS require documented procedures on hot use or run the database management system. These may include.

a. Procedure to install the new DBMS.

b. To log on to the DBMS.

c. To use the DBMS or application program.

d. To make backup copies of database.

e. To change the structure of database.

f. To generate the reports of data retrieved from database.

v. USER

The users are the people who manage the databases and perform different operations on the databases in the database system. There are three kinds of people who play different roles in database system

a. Application Programmers

b. Database Administrators

c. End Users

Classification of database management system

• As we all know DBMS is an interesting subject and so is its classification. There are several criteria based on which DBMS is classified. The classification and types of Database Management System(DBMS) is explained in a detailed manner below based

on the different factors. At the end of this article, you will be given a free pdf copy of all these types of DBMS.

Based on the data model

i. Relational database This is the most popular data model used in industries. It is based on the SQL. They are table oriented which means data is stored in different access control tables, each has the key field whose task is to identify each row. The tables or the files with the data are called as relations that help in designating the row or record, and columns are referred to attributes or fields. Few examples are MYSQL(Oracle, open source), Oracle database (Oracle), Microsoft SQL server(Microsoft) and DB2(IBM).

ii. Object relational database

Relational DBMS are evolving continuously and they have been incorporating many concepts developed in object database leading to a new class called extended relational database or object relational database.

Based on the number of users

i. Single user As the name itself indicates it can support only one user at a time. It is mostly used with the personal computer on which the data resides accessible to a single person. The user may design, maintain and write the database programs.

ii. Multiple users It supports multiple users concurrently. Data can be both integrated and shared,a database should be integrated when the same information is not need be recorded in two places. For example a student in the college should have the database containing his information. It must be accessible to all the departments related to him. For example the library department and the fee section department should have information about student’s database. So in such case, we can integrate and even though database resides in only one place both the departments will have the access to it.

Based on the sites over which network is distributed

i. Centralized database system The DBMS and database are stored at the single site that is used by several other systems too. We can simply say that data here is maintained on the centralized server.

ii. Distributed database system In this data and the DBMS software are distributed over several sites but connected to the single computer.

iii. Based on the cost This classification simply based on the access to data in the database systems

Sequential access One after the other. Direct access

Inverted file structures

Advantages of DBMS

The database management system has promising potential advantages, which are explained below:

i. Controlling Redundancy

In file system, each application has its own private files, which cannot be shared between multiple applications. This can often lead to considerable redundancy in the stored data, which results in wastage of storage space. Byhaving centralized database most of this can be avoided. It is not possible that all redundancy should be eliminated. Sometimes there are sound business and technical reasons for· maintaining multiple copies of the same data. In a database system, however this redundancy can be controlled.

ii. Integrity can be enforced

Integrity of data means that data in database is always accurate, such that incorrect information cannot be stored in database. In order to maintain the integrity of data, some integrity constraints are enforced on the database. A DBMS should provide capabilities for defining and enforcing the constraints.

iii. Inconsistency can be avoided

When the same data is duplicated and changes are made at one site, which is not propagated to the other site, it gives rise to inconsistency and the two entries regarding the same data will not agree. At such times the data is said to be inconsistent. So, if the redundancy is removed chances of having inconsistent data is also removed.

iv. Data can be shared

As explained earlier, the data about Name, Class, Father __name etc. of General_Office is shared bymultipleapplications in centralizedDBMS as comparedto file system so now applications can be developed to operate against the same stored data. The applications may be developed without having to create any new stored files.

v. Standards can be enforced

Since DBMS is a central system, so standard can be enforced easily may be at Company level, Department level, National level or International level. The standardized data is very helpful during migration or interchanging of data. The file system is an independent system so standard cannot be easily enforced on multiple independent applications.

vi. Restricting unauthorized access

When multiple users share a database, it is likely that some users will not be authorized to access all information in the database. For example, account office data is often considered confidential, and hence only authorized persons are allow to access such data. In addition, some users may be permitted only to retrieve data, whereas other are allowed both to retrieve and to update. Hence, the type of access operation retrieval or update must also be controlled. Typically, users or user groups are given account numbers protected by passwords, which they can use to gain access to the database. A DBMS should provide a security and authorization subsystem, which the DBA uses to create accounts and to

specify account restrictions. The DBMS should then enforce these restrictions automatically.

vii. Solving Enterprise Requirement than Individual Requirement

Since many types of users with varying level of technical knowledge use a database, a DBMS should provide a variety of user interface. The overall requirements of the enterprise are more important than the individual user requirements. So, the DBA can structure the database system to provide an overall service that is "best for the enterprise".

viii. Providing Backup and Recovery

A DBMS must provide facilities for recovering from hardware or software failures. The backup and recovery subsystem of the DBMS is responsible for recovery. For example, if the computer system fails in the middle of a complex update program, the recovery subsystem is responsible for making sure that the database is restored to the state it was in before the program started executing.

ix. Cost of developing and maintaining system is lower

Itismucheasiertorespondtounanticipatedrequestswhendataiscentralizedinadatabase than when it is stored in a conventional file system. Although the initial cost of setting up of a database can be large, but the cost of developing and maintaining application programs to be far lower than for similar service using conventional systems. The productivity of programmers can be higher in using non procedural languages that have been developed with DBMS than using procedural languages.

x. Data Model can be developed

The centralized system is able to represent the complex data and interfile relationships, which results better data modeling properties. The data madding properties of relational model is based on Entity and their Relationship, which is discussed in detail in chapter 4 of the book.

xi. Concurrency Control

DBMS systems provide mechanisms to provide concurrent access of data to multiple users.

Disadvantages of DBMS

The disadvantages of the database approach are summarized as follows:

i. Complexity

The provision of the functionality that is expected of a good DBMS makes the DBMS an extremely complex piece of software. Database designers, developers, database administrators and end users must understand this functionality to take full advantage of it. Failure to understand the system can lead to bad design decisions, which can have serious consequences for an organization.

ii. Size

The complexity and breadth of functionality makes the DBMS an extremely large piece of software, occupying many megabytes of disk space and requiring substantial amounts of memory to run efficiently.

iii. Performance

Typically, a File Based system is written for a specific application, such as invoicing. As result, performance is generally very good. However, the DBMS is written to be more general, to cater for many applications rather than just one. The effect is that some applications may not run as fast as they used to.

iv. Higher impact of a failure

The centralization of resources increases the vulnerability of the system. Since all users and applications rely on the ~vailabi1ity of the DBMS, the failure of any component can bring operations to a halt.

v. Cost of DBMS

The cost of DBMS varies significantly, depending on the environment and functionality provided. There is also the recurrent annual maintenance cost.

vi. Additional Hardware costs

The disk storage requirements for the DBMS and the database may necessitate the purchase of additional storage space. Furthermore, to achieve the required performance it may be necessary to purchase a larger machine, perhaps even a machine dedicated to running the DBMS. The procurement of additional hardware results in further expenditure.

vii. Cost of Conversion

In some situations, the cost of the DBMS and extra hardware may be insignificant compared with the cost of converting existing applications to run on the new DBMS and hardware. This cost also includes the cost of training staff to use these new systems and possibly the employment of specialist staff to help with conversion and running of the system. This cost is one of the main reasons why some organizations feel tied to their current systems and cannot switch to modern database technology.

Key points

• A database as a collection of related data, where data means recorded facts.

• Atypical databaserepresentssomeaspectoftherealworld and is used for specific purposes by one or more groups of users.

• A DBMS is a generalized software package for implementing and maintaining a computerized database.

• The database and software together form a database system.

• There are advantages and disadvantages using database management system

Chapter 2 DATA MODELLING

In this chapter, you will learn how to:

Construct entity relationship diagram in data modelling

Apply normalization process in data modelling

2.1 ENTITY RELATIONSHIP DIAGRAM

An entity relationship diagram (ERD) shows the relationships of entity sets stored in a database. An entity in this context is a component of data. In other words, ER diagrams illustrate the logical structure of databases.

At first glance an entity relationship diagram looks very much like a flowchart. It is the specialized symbols, and the meanings of those symbols, that make it unique.

: Entity Relationship Diagram Internet Sales Model

Figure

The History of Entity Relationship Diagrams

Peter Chen developed ERDs in 1976. Since then Charles Bachman and James Martin have added some slight refinements to the basic ERD principles.

Function, Component and Symbols of Entity Relationship Diagram

The ER diagram is used to represent database schema. In ER diagram:

• A rectangle represents an entity set.

• An ellipse represents an attribute.

• A diamond represents a relationship.

• Lines represents linking of attribute to entity sets and of entity sets to relationship sets.

Entity

Figure 2.2 : Symbols of Entity Relationship

Example of ER diagram

Let us consider a simple ER diagram as shown below. In the ER diagram the two entities are STUDENT and CLASS. Two simple attributes which are associated with the STUDENT are Roll number and the name. The attributes associated with the entity CLASS are Subject Name andHallNumber.TherelationshipbetweenthetwoentitiesSTUDENTand CLASSisAttends.

Figure 2.3 : ER Diagram

Theelements in ER diagram are entity, attributeandrelationship. Thedifferent types ofentities like strong, weak and associative entity, different types of attributes like multivalued and derived attributesandidentifyingrelationshipandtheircorrespondingsymbolsareshownlater.

Figure 2.4 : Symbols in ERD

Anentityrelationshipdiagramisameansofvisualizinghowtheinformation asystemproduces is related. There are four main components of an ERD:

i. Entities, which are represented by rectangles. An entity is an object or concept about which you want to store information.

Entity Relationship

ii. Actions, which are represented by diamond shapes, show how two entities share information in the database.

iii. In some cases, entities can be self linked. For example, employees can supervise other employees.

Employee

iv. Attributes, which are represented by ovals. A key attribute is the unique, distinguishing characteristic of the entity. For example, an employee's social security number might be the employee's key attribute.

Attribute

v. Connecting lines, solid lines that connect attributes to show the relationships of entities in the diagram.

Entities and Attributes in Entity Relationship Diagram

Component Entity sets can be broadly classified into:

i. Strong entity

ii. Weak entity

iii. Associative entity

Strong entity

Strong entity is one whose existence does not depend on other entity.

Example

Consider the example, student takes course. Here student is a strong entity.

In this example, course is considerd as weak entity because if there are no students to take a particular course, then that course cannot be offered. The COURSE entity depends on the STUDENT entity.

Weak entity

Weak entity is one whose existence depends on other entity. In many cases, weak entity does not have primary key.

Example

Consider the example, customer borrows loan. Here loan is a weak entity. For everyloan, there should be at least one customer. Here the entityloan depends on the entitycustomer hence loan is a weak entity.

Attribute

Attribute is used to describe the properties of the entity. This attribute can be broadly classified based on value and structure. Based on value the attribute can be classified into single value, multivalue, derived and null value attribute. Based on structure, the attribute can be classified as simple and composite attribute.

Single value attribute

Single value attribute means, there is only one value associated with that attribute.

Example

The examples of single value attribute are age of a person, roll number of the student, registration number of a car, etc.

Value attribute in ER Diagram

Multivalued attribute

In the case of multivalue attribute, more than one value will be associated with that attribute

Multivalued attribute in ER Diagram

Examples of multivalued attribute

i. Consider an entity EMPLOYEE. An employee can have many skills; hence skills associated to an employee are a multivalued attribute.

ii. Number of chefs in a hotel is an example of multivalued attribute. Moreover, a hotel will have variety of food items. Hence food items associated with the entity HOTEL is an example of multivalued attribute.

iii. Application associated with an IC (Integrated Circuit). An IC can be used for several applications. Here IC stands for Integrated Circuit.

iv. Subjects handled by a staff. A staff can handle more than one subject in a particular semester, hence it is an example of multivalued attribute.

More over a staff can be an expert in more than one area, hence area of specialization is considered as multivalued attribute.

Derived attribute

The value of the derived attribute can be derived from the value of other related attribute or entities. In ER diagram, the derived attribute is represented by dotted ellipse.

Derived attribute in ER Diagram

Example of derived attribute

Age of a person can be derived from the date of birth of the person. In this example, age is the derived attribute.

Composite attribute

As another example of composite attribute consider the degree earned by a particular scholar, which can range from undergraduate, postgraduate. Doctorate degree, etc. hence degree can be considered as composite attribute.

Types of Keys

The basic units in a database are tables and the relationship between them. Strictly, a relational database is a collection of relations (frequently called tables). Below we see how a relationship between two tables are defined using Primary Keys and Foreign Keys.

i. Primary key: the candidate key chosen to be used for identifying entities and accessing records. Unless otherwise noted "key" means "primary key"

Secondary key: attribute or set of attributes commonly used for accessing records, but not necessarily unique

ii. Foreign key: term used in relational databases (but not in the E R model) for an attribute that is the primary key of another table and is used to establish a relationship with that table where it appears as an attribute also.

So a foreign key value occurs in the table and again in the other table. This conflicts with the idea that a value is stored only once; the idea that a fact is stored once is not undermined.

iii. Candidate key: a superkey such that no proper subset of its attributes is also a superkey (minimal superkey has no unnecessary attributes)

iv. Composite key: a key requiring more than one attribute

v. Alternate key: a candidate key not used for primary key

Types of Entity Relationship Cardinality

You define foreign keys in a database to model relationships in the real world. Relationships between real world entities can be quite complex, involving numerous entities each having multiple relationships with each other. For example, a family has multiple relationships between multiple people all at the same time. These tables can be related in one of three different ways: one to one, one to many or many to many.

Cardinality specifies how many instances of an entity relate to one instance of another entity. Ordinality is also closely linked to cardinality. While cardinality specifies the occurrences of a relationship, ordinality describes the relationship as either mandatory or optional. In other words, cardinality specifies the maximum number of relationships and ordinality specifies the absolute minimum number of relationships

i. One-to-One Relationship

Two tables are related in a one to one (1 1) relationship if, for every row in the first table, there is at most one row in the second table. True one to one relationships seldom occur in the real world. This type of relationship is often created to get around some limitation of the database management software rather than to model a real world situation.

Forexample, youmightkeepmostpatientinformationintblPatient,butputespeciallysensitive information (e.g., patient name, social security number and address) in tblConfidential (see Figure 1).

Access to the information in tblConfidential could be more restricted than for tblPatient. As a second example, perhaps you need to transfer only a portion of a large table to some other application on a regular basis. You can split the table into the transferred and the non transferred pieces, and join them in a one to one relationship.

Figure 2.5 : The tables tblPatient and tblConfidential are related in a one to one relationship. The primary key of both tables is PatientId.

Tables that are related in a one to one relationship should always have the same primary key, which will serve as the join column.

ii. One to Many Relationships

Two tables are related in a one to many (1 M) relationship if for every row in the first table, there can be zero, one, or many rows in the second table, but for every row in the second table there is exactly one row in the first table. For example, each order for a pizza delivery business can have multiple items. Therefore, tblOrder is related to tblOrderDetails in a one to many relationship (see Figure 2).

The one to many relationship is also referred to as a parent child or master detail relationship. One to many relationships are the most commonly modeled relationship.

Figure 2.6 : There can be many detail lines for each order in the pizza delivery business, so tblOrder and tblOrderDetails are related in a one to many relationship.

One to many relationships are also used to link base tables to information stored in lookup tables. For example, tblPatient might have a short one letter DischargeDiagnosis code, which can be linked to a lookup table, tlkpDiagCode, to get more complete Diagnosis descriptions (stored in DiagnosisName). In this case, tlkpDiagCode is related to tblPatient in a one to many relationship (i.e., one row in the lookup table can be used in zero or more rows in the patient table).

iii. Many-to-Many Relationships

Two tables are related in a many to many (M M) relationship when for every row in the first table, there can be many rows in the second table, and for every row in the second table, there can be many rows in the first table. Many to many relationships can’t be directly modeled in relational database programs, including Microsoft Access. These types of relationships must be broken into multiple one to many relationships.

For example, a patient may be covered by multiple insurance plans and a given insurance company covers multiple patients. Thus, the tblPatient table in a medical database would be related to thetblInsurertablein amany to manyrelationship. Inorderto modeltherelationship between these two tables, you would create a third, linking table, perhaps called tblPtInsurancePgm that would contain a row for each insurance program under which a patient was covered (see Figure 3).

Then, the many to many relationship between tblPatient and tblInsurer could be broken into two one to many relationships (tblPatient would be related to tblPtInsurancePgm and tblInsurer would be related to tblPtInsurancePgm in one-to-many relationships).

Figure 2.7 : A linking table, tblPtInsurancePgm, is used to model the many to many relationship between tblPatient and tblInsurer.

Degree of Relationship

The number of roles in the relationship

i. Unary relationship exists when an association is maintained with in a single entity.

ii. Binary links two entity sets; set of ordered pairs (most common)

iii. Ternary links three entity sets; ordered triples (rare). If a relationship exists among the three entities, all three must be present

iv. N ary links n entity sets; ordered n tuples (very rare). If a relationship exists among the entities, then all must be present. Cannot represent subsets.

Note: ternary relationships may sometimes be replaced by two binary. Semantic equivalence between ternary relationships and two binary ones are not necessarily true.

Figure 2.8 : Degree of Relationship Steps to Convert ERD into Table

The ER Model is intended as a description of real world entities. Although it is constructed in such a way as to allow easy translation to the relational schema model, this is not an entirely trivial process. The ER diagram represents the conceptual level of database design meanwhile the relational schema is the logical level for the database design. There is simple wayof mapping from ER model to the relational model.

Seven Steps

ER to Relational Mapping Algorithm

a) Non weak entity

Weak entity

Binary 1:1 relationship

Non weak binary 1:N relationship

Binary M:N relationship

Relationship with three or more participating entities

Superclass or subclass structure

Mapping Algorithm

Figure 2.9 : The ER conceptual schema diagram for the COMPANY database.

Step 1:

a) Mapping of Regular Entity Types.

• For each regular (strong) entity type E in the ER schema, create a relation R that includes all the simple attributes of E.

• Choose one of the key attributes of E as the primary key for R.

• If the chosen key of E is composite, the set of simple attributes that form it will together form the primary key of R.

➲ Example: We create the relations EMPLOYEE, DEPARTMENT, and PROJECT in the relational schema corresponding to the regular entities in the ER diagram.

• SSN, DNUMBER, and PNUMBER are the primary keys for the relations EMPLOYEE, DEPARTMENT, and PROJECT as shown.

b) Converting Composite Attribute in an ER Diagram to Tables

• When a regular entity type has a composite attribute, only the simple component attributes of the composite attribute are included in the relation.

• Example:

In this example the composite attribute is the Customer address, which consists of Street, City, State, and Zip.

CUSTOMER

CustomerID Customer name Street City State Zip

c) Mapping of Multivalued attributes.

• For each multivalued attribute A, create a new relation R.

• This relation R will include an attribute corresponding to A, plus the primary key attribute K as a foreign key in R of the relation that represents the entity type of relationship type that has A as an attribute.

• The primary key of R is the combination of A and K. If the multivalued attribute is composite, we include its simple components.

➲ Example: The relation DEPT_LOCATIONS is created.

• The attribute DLOCATION represents the multivalued attribute LOCATIONS of DEPARTMENT, while DNUMBER as foreign key represents the primary key of the DEPARTMENT relation.

• The primary key of R is the combination of {DNUMBER, DLOCATION}.

Step 2: Mapping of Weak Entity Types

• For each weak entity type W in the ER schema with owner entity type E, create a relation R & include all simple attributes (or simple components of composite attributes) of W as attributes of R.

• Also, include as foreign key attributes of R the primary key attribute(s) of the relation(s) that correspond to the owner entity type(s).

• The primary key of R is the combination of the primary key(s) of the owner(s) and the partial key of the weak entity type W, if any.

➲ Example: Create the relation DEPENDENT in this step to correspond to the weak entity type DEPENDENT.

• Include the primary key SSN of the EMPLOYEE relation as a foreign key attribute of DEPENDENT (renamed to ESSN).

• The primary key of the DEPENDENT relation is the combination {ESSN, DEPENDENT_NAME} because DEPENDENT_NAME is the partial key of DEPENDENT.

Step 3: Mapping of Binary 1:1 Relation Types

• For each binary 1:1 relationship type R in the ER schema, identify the relations S and T that correspond to the entity types participating in R.

• There are three possible approaches:

1. Foreign Key approach: Choose one of the relations say S and include a

foreign key in S the primary key of T. It is better to choose an entity type with total participation in R in the role of S. Example: 1:1 relation MANAGES is mapped by choosing the participating entity type DEPARTMENT to serve in the role of S, because its participation in the MANAGES relationship type is total.

i. Merged relation option: An alternate mapping of a 1:1 relationship type is possible by merging the two entity types and the relationship into a single relation. This may be appropriate when both participations are total.

ii. Cross-reference or relationship relation option: The third alternative is to set up a third relation R for the purpose of cross referencing the primary keys of the two relations S and T representing the entity types.

Step 4: Mapping of Binary 1:N Relationship Types.

• For each regular binary 1:N relationship type R, identify the relation S that represent the participating entity type at the N side of the relationship type.

• Include as foreign key in S the primary key of the relation T that represents the other entity type participating in R.

• Include any simple attributes of the 1:N relation type as

• attributes of S.

➲ Example: 1:N relationship types WORKS_FOR, CONTROLS, and SUPERVISION in the figure.

• For WORKS_FOR we include the primary key DNUMBER of the DEPARTMENT relation as foreign key in the EMPLOYEE relation and call it DNO.

Step 5: Mapping of Binary M:N Relationship Types.

• For each regular binary M:N relationship type R, create a new relation S to represent R.

• Include as foreign key attributes in S the primary keys of the relations that represent the participating entity types; their combination will form the primary key of S.

• Also include any simple attributes of the M:N relationship type (or simple components of composite attributes) as attributes of S.

➲ Example: The M:N relationship type WORKS_ON from the ER diagram is mapped by creating a relation WORKS_ON in the relational database schema.

• The primary keys of the PROJECT and EMPLOYEE relations are included as foreign keys in WORKS_ON and renamed PNO and ESSN, respectively.

• Attribute HOURS in WORKS_ON represents the HOURS attribute of the relation type. The primary key of the WORKS_ON relation is the combination of the foreign key attributes {ESSN, PNO}.

Step 6: Mapping of N-ary Relationship Types.

• For each n ary relationship type R, where n>2, create a new relationship S to represent R.

• Include as foreign key attributes in S the primary keys of the relations that represent the participating entity types.

• Also include any simple attributes of the n ary relationship type (or simple components of composite attributes) as attributes of S.

➲ Example: The relationship type SUPPY in the ER on the next slide.

• This can be mapped to the relation SUPPLY shown in the relational schema, whose primary key is the combination of the three foreign keys {SNAME, PARTNO, PROJNAME}

Ternary relationship types.

Figure 2.11 : The SUPPLY relationship.

Step 7: Superclass

• Supertype or superclass is a generic entity type that has a relationship with one or more subtypes.

• For example PIAYER is generic entity type which has relationship with one or more subtypes like CRICKET PLAYER, FOOTBALL PLAYER, HOCKEY PLAYER, TENNIS PLAYER, etc.

Subclass

• A subtype or subclass is a subgrouping of the entities in an entity type that is meaningful to the organization. A subclass entity type is a specialized type of superclass entity type. A subclass entity type represents a subset or subgrouping of superclass entity type's instances. Subtypes inherit the attributes and relationships associated with their supertype.

• Consider the entity type ENGINE, which has two subtypes PETROL ENGINE and DIESEL ENGINE.

• Consider the entity type STUDENT, which has two subtypes UNDERGRADUATE and POSTGRADUATE

The mapping algorithm gives the procedure to map ER diagram to tables. The rules in mapping algorithm are given as:

For each strong entity type say E, create a new table. The columns of the table are the attribute of the entity type E.

For each weak entity W that is associated with only one 1 1 identifying owner relationship, identify the table T of the owner entity type. Include as columns of T, all the simple attributes and simple components of the composite attributes of WV.

For each weak entity W that is associated with I N or NI N identifying relationship, or participates in more than one relationship, create new table T and include as its columns, all the simple attributes and simple components of the composite attributes of W. Also form its primary key by including as a foreign key in R, the primary key of its owner entity.

For each binary 1 1 relationship type R, identify the tables S and T of the participating entity types. Choose S, preferably the one with total participation. Include as foreign key in S, the primary key of T. Include as columns of S, all the simple attributes and simple components of the composite attributes of R.

For each binary I N relationship type R, identify the table St which is at N side and T of the participating entities. Include as a foreign key in S, the primary key of T. Also include as columns of S, all the simple attributes and simple components of composite attributes of R.

For each M N relationship type R, create a new table T and include as columns of T, all the simple attributes and simple components of composite attributes of R. Include as foreign keys, the primary keys of the participating entity types. Specify as the primary key of T, the list of foreign keys.

For each multivalued attribute, create new table T and include as columns of T, the simple attribute or simple components of the attribute A. Include as foreign key, the primary key of the entity or relationship type that has A. Specify as the primary key of T, the foreign key and the columns corresponding to A.

Thumb Rules to Remember

While determining the minimum number of tables required for binary relationships with given cardinality ratios, following thumb rules must be kept in mind

• For binary relationship with cardinality ration m : n , separate and individual tables will be drawn for each entity set and relationship.

• For binary relationship with cardinality ratio either m : 1 or 1 : n , always remember “many side will consume the relationship” i.e. a combined table will be drawn for many side entity set and relationship set.

• For binary relationship with cardinality ratio 1 : 1 , two tables will be required. You can combine the relationship set with any one of the entity sets.

2.2 DATABASE NORMALIZATION PROCESS

Basic Database Normalization

Normalization is the process of efficiently organizing data in a database. There are two goals of the normalization process: eliminating redundant data (for example, storing the same data in more than one table) and ensuring data dependencies make sense (only storing related data

in a table). Both of these are worthy goals as they reduce the amount of space a database consumes and ensure that data is logically stored.

Important of Normalization

You will learn how to use normalization to produce a set of normalized tables to store the data that will be used to generate the required information. The objective of normalization is to ensure that each table conforms to the concept of well formed relations, that is, tables that have the following characteristics:

Each table represents a single subject. For example, a course table will contain only data that directly pertains to courses. Similarly, a student table will contain only student data. No data item will be unnecessarily stored in more than one table (in short, tables have minimum controlled redundancy). The reason for this requirement is to ensure that the data are updated in only one place.

All nonprime attributes in a table are dependent on the primary key the entire primary key and nothing but the primary key. The reason for this requirement is to ensure that the data are uniquelyidentifiable bya primarykeyvalue. Each table is void of insertion, update, or deletion anomalies. This is to ensure the integrity and consistency of the data.

Functional dependency

The attribute B is fully functionally dependent on the attribute A if each value of A determines one and only one value of B.

Example: PROJ_NUM → PROJ_NAME (read as “PROJ_NUM functionally determines PROJ_NAME)

In this case, the attribute PROJ_NUM is known as the “determinant” attribute and the attribute PROJ_NAME is known as the “dependent” attribute.

Functional dependency (generalized definition)

Attribute A determines attribute B (that is, B is functionally dependent on A) if all of the rows in the table that agree in value for attribute A also agree in value for attribute B.

Fully functional dependency (composite key)

If attribute B is functionally dependent on a composite key A but not on any subset of that composite key, the attribute B is fully functionally dependent on A.

Types of normalization

i. Normal Forms

Emp Emp Emp Emp City Basic Var Total Code Name City One Two Salary Salary Salary

EMP001 Neeraj Delhi Gurgaun 10000 5000 15000 Tripathi

EMP002 Mayank Mumbai Pune 15000 8500 23500 Nath

ii. First Normal Form (1NF)

Their are only two root rules:

a. Their should be no duplicate columns.

b. For each group of related data their should be separate tables, in which identify each row uniquely by a column or set of column.

Example : This is EmployeeMaster as

Emp Emp Emp Emp City Basic Var Total Code Name City One Two Salary Salary Salary

EMP001 Neeraj Delhi Gurgaun 10000 5000 15000 Tripathi

EMP002 Mayank Mumbai Pune 15000 8500 23500 Nath

So apply the first normal form, Emp City will come in one column and Emp Name will break in two fiels:

Emp Emp Emp Emp City Basic Var Total Code First Last Salary Salary Salary Name Name

EMP001 Neeraj Tripathi Delhi 10000 5000 15000

EMP001 Neeraj Tripathi Gurgaun 10000 5000 15000

EMP002 Mayank Nath Mumbai 15000 8500 23500

EMP002 Mayank Nath Pune 15000 8500 23500

Now the given record set is in the first normal form.

iii. Second Normal Form (2NF)

iv.

This form state that after fulfilling all the condition of first normal form, there are two more conditions

• Place those data in separate tables which are not directly related to the primary key of that table.

• Create relationship between these tables using new table key (Foreign Key).

Emp Emp Emp Last Emp Basic Var Total Code First Name City Id Salary Salary Salary Name

EMP001 Neeraj Tripathi 1 10000 5000 15000

EMP001 Neeraj Tripathi 2 10000 5000 15000

EMP002 Mayank Nath 3 15000 8500 23500

EMP002 Mayank Nath 4 15000 8500 23500

City Master become in scene

Id City Name

That is now takes our database to a second normal form.

iv. Third Normal Form (3NF)

a. So now meet all the fundamentals for 2NF and then b. Remove all the non key fields which are depend on other non key fields. In our table “Total Salary” is a kind of field. So :

Emp Code Emp First Emp Last Emp City Id Basic Var Name Name Salary Salary

EMP001 Neeraj Tripathi 1 10000 5000 EMP001 Neeraj Tripathi 2 10000 5000

EMP002 Mayank Nath 3 15000 8500

EMP002 Mayank Nath 4 15000 8500

So the “Total Salary” field is removed which can be easily get by “Basic Salary” + “Var Salary”.

Boyce Codd normal form (BCNF)

A relational schema R is considered to be in Boyce Codd normal form (BCNF) if, for every one of its dependencies X → Y, one of the following conditions holds true:

• X → Y is a trivial functional dependency (i.e., Y is a subset of X)

• X is a superkey for schema R

Informally the Boyce Codd normal form is expressed as "Each attribute must represent a fact about the key, the whole key, and nothing but the key.”

Example

Let's take a look at this table, with some typical data. The table is not in BCNF.

Author Nationality Book title Genre Number of pages

William English

The Comedy of Errors Comedy 100 Shakespeare

Markus Winand Austrian SQL Performance Explained Textbook 200

Jeffrey Ullman American

Jennifer Widom American

A First Course in Database Textbook 500 Systems

A First Course in Database Textbook 500 Systems

The nontrivial functional dependencies in the table are:

author → nationality book title → genre, number of pages

We can easily see that the only key is the set {author, book title}.

The same data can be stored in a BCNF schema. However, this time we would need three tables.

Author Nationality

William Shakespeare English

Markus Winand Austrian

Jeffrey Ullman American

Jennifer Widom American

Book title Genre Number of pages

The Comedy of Errors Comedy 100

SQL Performance Explained Textbook 200

A First Course in Database Systems Textbook 500

Author Book title

William Shakespeare

Markus Winand

Jeffrey Ullman

Jennifer Widom

The Comedy of Errors

SQL Performance Explained

A First Course in Database Systems

A First Course in Database Systems

The functional dependencies for this schema are the same as before: author → nationality book title → genre, number of pages

The key of the first table is {author}. The key of the second table is {book title}. The key of the third table is {author, book title}. There are no functional dependencies violatingthe BCNF rules, so the schema is in Boyce Codd normal form.

How do you decompose your schema into Boyce Codd normal form?

To go from non BCNF normal form to BCNF, you must decompose your table using these two steps.

1. Find a nontrivial functional dependency X → Y which violates the BCNF condition (where the X is not a superkey)

2. Split your table in two tables:

o one with attributes XY (all attributes from the dependency),[1]

o one with X attributes together with the remaining attributes from the original relation

Then you keep repeating the decomposition process until all of your tables are in BCNF. After sufficient iterations you have a set of tables, each in BCNF, such that the original relation can be reconstructed.

Example

Let's get back to the example. The functional dependency:

book title → genre, number of pages

is one FD violating the BCNF rules. We split our relation into two relations:

• the ones in the functional dependency (book title, genre, number of pages)

• the rest: (book title, author, nationality). Note that the left hand side of the FD (book title) stays in the relation!

The example data look like this. We select the values of columns from the original relation and we eliminate the duplicate rows.

Book title Genre Number of pages

The Comedy of Errors Comedy 100

SQL Performance Explained Textbook 200

A First Course in Database Systems Textbook 500

Author Nationality Book title

William Shakespeare English The Comedy of Errors

Markus Winand Austrian SQL Performance Explained

Jeffrey Ullman American A First Course in Database Systems

Jennifer Widom American A First Course in Database Systems

Are we done? Nope. The (book title, genre, number of pages) table is in BCNF. But (book title, author, nationality) isn't. We have the dependency:

author → nationality

Together with the trivial dependency

book title → book title,

the pair (book title, author) is the key of the relation.

We have to decompose the table one more time. This time we decompose into:

• columns forming the functional dependency: (author, nationality)

• the remaining columns: (author, book title)

This time every table is in BCNF.

Fourth Normal Form (4NF)

A table is in fourth normal form (4NF) if and only if it is in BCNF and contains no more than one multi-valued dependency.

a. Anomalies can occur in relations in BCNF if there is more than one multi valued dependency.

b. If A >B and A >C but B and C are unrelated, ie A >(B,C) is false, then we have more than one multi valued dependency.

c. A relation is in 4NF when it is in BCNF and has no more than one multi valued dependency.

Example to understand 4NF:-

Take the following table structure as an example: info(employee, skills, hobbies)

Take the following table: employee skills hobbies

1 Programming Golf

1 Programming Bowling

1 Analysis Golf

1 Analysis Bowling

2 Analysis Golf

2 Analysis Gardening

2 Management Golf

2 Management Gardening

This table is difficult to maintain since adding a new hobby requires multiple new rows corresponding to each skill. This problem is created by the pair of multi valued dependencies

EMPLOYEE >SKILLS and EMPLOYEE >HOBBIES. A much better alternative would be to decompose INFO into two relations:

Key points

• A relational model real world objects are represented in tables. Each table is made out of rows and columns. Each row, also known as tupleor record, is made out of fields, also known as attributes. Each Attribute stands for a certain feature of the real world object. An attribute is defined by a name and its value.

• Relations between tuples represent existing relationships between objects (tables). Furthermore key attributes have to be defined (usually displayed underlined in a relation). They are necessary for the allocation (relation) of objects (tables) and allow unique accesses to tables.

• Integrity or consistency stands for the quality and reliability of data of a database system. A database is consistent if the data reflects the referenced objects correctly. It is inconsistent if there exist ambiguous or contradictory tuples, relations or tables in the database.

Chapter 3 Structured Query Language(SQL)

In this chapter, you will learn how to: Construct Structured Query Language (SQL)

Identify data definition Language

Manipulate data manipulation language

In this chapter, you will get an overview of the structured query language(SQL). You will explore the SQL concept and SQL commands. SQL concept include SQL fundamentals, data type and SQL constraints. SQL commands are data definition language and data manipulation language. You will learn simple queries in SQL.

3.1 SQL CONCEPT

Structured Query Language (SQL) is the standard language used to communicate with a relational database. The prototype was originally developed by IBM using Dr. E.F. Codd’s paper (“A Relational Model of Data for Large Shared Data Banks”) as a model. In 1979, not long after IBM’s prototype, the first SQL product, ORACLE, was released by Relational Software, Incorporated (which was later renamed Oracle Corporation). It is, today, one of the distinguished leaders in relational database technologies. SQL is pronounced either of two ways: as the letters S Q L, or as “sequel”; both pronunciations are acceptable. However, most experienced SQL users tend to use the latter pronunciation.

SQL Fundamentals

Types of SQL Commands

The following sections discuss the basic categories of commands used in SQL to perform various functions. These functions include building database objects, manipulating objects, populating database tables with data, updating existing data in tables, deleting data, performing database queries, controlling database access, and overall database administration.

The main categories are

. Data Definition Language (DDL)

. Data Manipulation Language (DML)

. Data Query Language (DQL)

. Data Control Language (DCL)

. Data administration commands

. Transactional control commands

Defining Database Structures

Data Definition Language (DDL) is the part of SQL that allows a database user to create and restructure database objects, such as the creation or the deletion of a table. Some of the most fundamental DDL commands discussed include the following:

CREATE TABLE

ALTER TABLE DROP TABLE CREATE INDEX ALTER INDEX DROP INDEX CREATE VIEW DROP VIEW

Manipulating Data

Data Manipulation Language (DML) is the part of SQL used to manipulate data within objects of a relational database. The three basic DML commands are INSERT UPDATE DELETE Selecting Data

Though comprised of only one command, Data Query Language (DQL) is the most concentrated focus of SQL for modern relational database users. The base command is as follows: SELECT

This command, accompanied by many options and clauses, is used to compose queries against a relational database. Queries, from simple to complex, from vague to specific, can be easily created.

A query is an inquiry to the database for information. A query is usually issued to the database through an application interface or via a command line prompt.

What is Identifiers?

An Identifier is essentially a name of a database, table, or table column. As the creator of the database, you are free to identify these objects as you please; we merely suggest you keep these guidelines in mind when you do.

• Develop your own unique naming scheme. Use terms that relate to one another and will be easy to recognize apart from your code.

• Be conscious of how long your names become. Especially be aware when the time comes to name your columns.

• Avoid names without meaning. Develop a working scheme that also has meaning behind the names.

•

Be consistent. If you capitalize one table name, capitalize each table name; if you choose to use abbreviations, make sure they do not have double meanings or ambiguous meaning.

Develop a clear, concise schema, and stick to it as your database develops.

What is Data Types? Data type Description

CHARACTER(n)

VARCHAR(n) or CHARACTER VARYING(n)

BINARY(n)

Character string. Fixed length n

Character string. Variable length. Maximum length n

Binary string. Fixed length n

BOOLEAN Stores TRUE or FALSE values

VARBINARY(n) or BINARY VARYING(n)

INTEGER(p)

Binary string. Variable length. Maximum length n

Integer numerical (no decimal). Precision p

SMALLINT Integer numerical (no decimal). Precision 5

INTEGER Integer numerical (no decimal). Precision 10

BIGINT Integer numerical (no decimal). Precision 19

DECIMAL(p,s)

NUMERIC(p,s)

FLOAT(p)

Exact numerical, precision p, scale s. Example: decimal(5,2) is a number that has 3 digits before the decimal and 2 digits after the decimal

Exact numerical, precision p, scale s. (Same as DECIMAL)

Approximate numerical, mantissa precision p. A floating number in base 10 exponential notation. The size argument for this type consists of a single number specifying the minimum precision

REAL Approximate numerical, mantissa precision 7

FLOAT Approximate numerical, mantissa precision 16

DOUBLE

PRECISION

DATE

Approximate numerical, mantissa precision 16

Stores year, month, and day values

TIME Stores hour, minute, and second values

TIMESTAMP Stores year, month, day, hour, minute, and second values

INTERVAL

ARRAY

MULTISET

Composed of a number of integer fields, representing a period of time, depending on the type of interval

A set length and ordered collection of elements

A variable length and unordered collection of elements

XML Stores XML data

What is Syntax?

• Syntax, by definition, means the study of linguistic rules and patterns. Every programming language, including SQL, must follow a unique set of guidelines termed syntax.

• Punctuation, spaces, mathematical operators, and special characters have special meaning when used inside of SQL commands and query statements.

• For example, each and every SQL command will end with a semi colon (;).

• Executing SQL commands that do not have proper syntax and formatting will result in a syntax error.

• Syntax errors might be the most common and first error messages new SQL developers will experience.

• Some database systems require a semicolon at the end of each SQL statement.

• Semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server.

• SQL is NOT case sensitive: select is the same as SELECT

SQL Constraints

SQL constraints are used to specify rules for the data in a table.

Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted.

Constraints can be column level or table level. Column level constraints apply to a column, and table level constraints apply to the whole table.

The following constraints are commonly used in SQL:

• NOT NULL Ensures that a column cannot have a NULL value

• UNIQUE Ensures that all values in a column are different

• PRIMARY KEY A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table

• FOREIGN KEY Prevents actions that would destroy links between tables

• CHECK Ensures that the values in a column satisfies a specific condition

• DEFAULT Sets a default value for a column if no value is specified

• CREATE INDEX Used to create and retrieve data from the database very quickly

3.2 DATA DEFINITION LANGUAGE

a. CREATE

The SQL CREATE DATABASE Statement

The CREATE DATABASE statement is used to create a database.

SQL CREATE DATABASE Syntax

CREATE DATABASE dbname;

SQL CREATE DATABASE Example

The following SQL statement creates a database called "my_db": CREATE DATABASE my_db;

Database tables can be added with the CREATE TABLE statement.

The SQL CREATE TABLE Statement

The CREATE TABLE statement is used to create a table in a database.

Tables are organized into rows and columns; and each table must have a name.

SQL CREATE TABLE Syntax

CREATE TABLE table_name

( column_name1 data_type(size), column_name2 data_type(size), column_name3 data_type(size),

);

The column_name parameters specify the names of the columns of the table.

The data_type parameter specifies what type of data the column can hold (e.g. varchar, integer, decimal, date, etc.).

The size parameter specifies the maximum length of the column of the table.

SQL CREATE TABLE Example

Now we want to create a table called "Persons" that contains five columns: PersonID, LastName, FirstName, Address, and City.

We use the following CREATE TABLE statement:

Example

CREATE TABLE Persons

( PersonID int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255)

);

The PersonID column is of type int and will hold an integer.

The LastName, FirstName, Address, and City columns are of type varchar and will hold characters, and the maximum length for these fields is 255 characters.

The empty "Persons" table will now look like this:

PersonID LastName FirstName Address City

Tip: The empty table can be filled with data with the INSERT INTO statement.

The UNIQUE constraint ensures that all values in a column are distinct.

For example, in the following CREATE TABLE statement,

CREATE TABLE Customer

(SID integer UNIQUE, Last_Name varchar (30), First_Name varchar(30));

column "SID" has a UNIQUE constraint, and hence cannot include duplicate values. Such constraint does not hold for columns "Last_Name" and "First_Name". So, if the table already contains the following rows:

SID Last_Name First_Name

1 Johnson Stella

2 James Gina

3 Aaron Ralph

Executing the following SQL statement,

b. DROP

SQL DROP INDEX, DROP TABLE, and DROP DATABASE

Indexes, tables, and databases can easily be deleted/removed with the DROP statement.

The DROP INDEX Statement

The DROP INDEX statement is used to delete an index in a table.

DROP INDEX Syntax for MS Access: DROP INDEX index_name ON table_name

DROP INDEX Syntax for MS SQL Server: DROP INDEX table_name.index_name

DROP INDEX Syntax for DB2/Oracle:

DROP INDEX index_name

DROP INDEX Syntax for MySQL:

ALTER TABLE table_name DROP INDEX index_name

The DROP TABLE Statement

The DROP TABLE statement is used to delete a table.

DROP TABLE table_name

The DROP DATABASE Statement

The DROP DATABASE statement is used to delete a database.

DROP DATABASE database_name;

The TRUNCATE TABLE Statement

What if we only want to delete the data inside the table, and not the table itself?

Then, use the TRUNCATE TABLE statement:

TRUNCATE TABLE table_name;

c. ALTER

The ALTER TABLE Statement

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

SQL ALTER TABLE Syntax

To add a column in a table, use the following syntax:

ALTER TABLE table_name ADD column_name datatype;

To delete a column in a table, use the following syntax (notice that some database systems don't allow deleting a column):

ALTER TABLE table_name

DROP COLUMN column_name;

To change the data type of a column in a table, use the following syntax:

SQL Server / MS Access:

ALTER TABLE table_name

ALTER COLUMN column_name datatype;

My SQL / Oracle (prior version 10G):

ALTER TABLE table_name

MODIFY COLUMN column_name datatype;

Oracle 10G and later:

ALTER TABLE table_name

MODIFY column_name datatype;

SQL ALTER TABLE Example

Look at the "Persons" table:

P_Id LastName FirstName Address City

1 Hansen Ola Timoteivn 10 Sandnes

2 Svendson Tove Borgvn 23 Sandnes

3 Pettersen Kari Storgt 20 Stavanger

Now we want to add a column named "DateOfBirth" in the "Persons" table.

We use the following SQL statement:

ALTER TABLE Persons ADD DateOfBirth date;

Notice that the new column, "DateOfBirth", is of type date and is going to hold a date. The data type specifies what type of data the column can hold. For a complete reference of all the data types available in MS Access, MySQL, and SQL Server,

The "Persons" table will now look like this:

P_Id LastName FirstName Address City DateOfBirth

1 Hansen Ola Timoteivn 10 Sandnes

2 Svendson Tove Borgvn 23 Sandnes

3 Pettersen Kari Storgt 20 Stavanger

Change Data Type Example

Now we want to change the data type of the column named "DateOfBirth" in the "Persons" table.

We use the following SQL statement:

ALTER TABLE Persons

ALTER COLUMN DateOfBirth year;

Notice that the "DateOfBirth" column is now of type year and is going to hold a year in a two digit or four digit format.

DROP COLUMN Example

Next, we want to delete the column named "DateOfBirth" in the "Persons" table.

We use the following SQL statement:

ALTER TABLE Persons DROP COLUMN DateOfBirth;

The "Persons" table will now look like this:

P_Id LastName FirstName Address City

1 Hansen Ola Timoteivn 10 Sandnes

2 Svendson Tove Borgvn 23 Sandnes

3 Pettersen Kari Storgt 20 Stavanger

3.3 DATA MANIPULATION LANGUAGE FUNCTIONS IN SQL

a. INSERT

The INSERT INTO statement is used to insert new records in a table.

The SQL INSERT INTO Statement

The INSERT INTO statement is used to insert new records in a table.

SQL INSERT INTO Syntax

It is possible to write the INSERT INTO statement in two forms.

The first form does not specify the column names where the data will be inserted, only their values:

INSERT INTO table_name VALUES (value1,value2,value3,...);

The second form specifies both the column names and the values to be inserted:

INSERT INTO table_name (column1,column2,column3,...) VALUES (value1,value2,value3,...);

Demo Database

Below is a selection from the "Customers" table:

CustomerID CustomerName ContactName Address City PostalCode Country

87 Wartian Herkku Pirkko Koskitalo Torikatu 38 Oulu 90110 Finland

88 Wellington Importadora Paula Parente Rua do Mercado, 12 Resende 08737 363 Brazil

89

90

White Clover Markets Karl Jablonski 305 14th Ave. S. Suite 3B Seattle 98128 USA

Wilman Kala Matti Karttunen Keskuskatu 45 Helsinki 21240 Finland

91 Wolski Zbyszek ul. Filtrowa 68 Walla 01 012 Poland

INSERT INTO Example

Assume we wish to insert a new row in the "Customers" table.

We can use the following SQL statement: Example

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)

VALUES ('Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway');

The selection from the "Customers" table will now look like this:

CustomerID CustomerName ContactName Address City PostalCode Country

87 Wartian Herkku Pirkko Koskitalo Torikatu 38 Oulu 90110 Finland

88 Wellington Importadora Paula Parente Rua do Mercado, 12 Resende 08737 363 Brazil

89

90

White Clover Markets Karl Jablonski 305 14th Ave. S. Suite 3B Seattle 98128 USA

Wilman Kala Matti Karttunen Keskuskatu 45 Helsinki 21240 Finland

91 Wolski Zbyszek ul. Filtrowa 68 Walla 01 012 Poland

92 Cardinal Tom B. Erichsen Skagen 21 Stavanger 4006 Norway

Insert Data Only in Specified Columns

It is also possible to only insert data in specific columns.

The following SQL statement will insert a new row, but only insert data in the "CustomerName", "City", and "Country" columns (and the CustomerID field will of course also be updated automatically):

Example

INSERT INTO Customers (CustomerName, City, Country) VALUES ('Cardinal', 'Stavanger', 'Norway');

The selection from the "Customers" table will now look like this: CustomerID CustomerName ContactName Address City PostalCode Country

87

Wartian Herkku Pirkko Koskitalo Torikatu 38 Oulu 90110 Finland

Wellington Importadora

Paula Parente

Rua do Mercado, 12 Resende 08737 363 Brazil

White Clover Markets Karl Jablonski 305 14th Ave. S. Suite 3B Seattle 98128 USA

Wilman Kala Matti Karttunen Keskuskatu 45 Helsinki 21240 Finland

Wolski Zbyszek Filtrowa 68 Walla 01 012 Poland

Cardinal null null Stavanger null Norway

b. UPDATE SQL UPDATE Statement

The UPDATE statement is used to update records in a table.

The SQL UPDATE Statement

The UPDATE statement is used to update existing records in a table.

SQL UPDATE Syntax

UPDATE table_name

SET column1=value1,column2=value2

WHERE some_column=some_value;

Notice the WHERE clause in the SQL UPDATE statement!

The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated!

Demo Database

Below is a selection from the "Customers" table:

CustomerI D CustomerNam e ContactNam e Address City PostalCod e Country

1

Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 German y

2 Ana Trujillo Emparedados y helados Ana Trujillo

3 Antonio Moreno Taquería

Avda. de la Constitución 2222 México D.F. 05021 Mexico

Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico

4 Around the Horn Thomas Hardy 120 Hanover Sq. Londo n WA1 1DP UK

5 Berglunds snabbköp Christina Berglund Berguvsväge n 8 Luleå S 958 22 Sweden

SQL UPDATE Example

Assume we wish to update the customer "Alfreds Futterkiste" with a new contact person and city.

We use the following SQL statement:

Example

UPDATE Customers

SET ContactName='Alfred Schmidt', City='Hamburg'

WHERE CustomerName='Alfreds Futterkiste';

The selection from the "Customers" table will now look like this:

CustomerI D CustomerNam e ContactNam e Address City PostalCod e Country

1

Alfreds Futterkiste Alfred Schmidt Obere Str. 57 Hambur g 12209 German y

2 Ana Trujillo

Emparedados y helados Ana Trujillo

3 Antonio Moreno Taquería

Avda. de la Constitución 2222 México D.F. 05021 Mexico

Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico

4

Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK

5

Berglunds snabbköp Christina Berglund

Update Warning!

Berguvsväge n 8 Luleå S 958 22 Sweden

Be careful when updating records. If we had omitted the WHERE clause, in the example above, like this:

UPDATE Customers

SET ContactName='Alfred Schmidt', City='Hamburg';

The "Customers" table would have looked like this:

CustomerI D CustomerNam e ContactNam e Address City PostalCod e Country

1

Alfreds Futterkiste

Alfred Schmidt Obere Str. 57 Hambur g 12209 German y

2

Ana Trujillo Emparedados y helados Alfred Schmidt

3 Antonio Moreno Taquería

Avda. de la Constitución 2222 Hambur g 05021 Mexico

Alfred Schmidt Mataderos 2312 Hambur g 05023 Mexico

4 Around the Horn Alfred Schmidt 120 Hanover Sq. Hambur g WA1 1DP UK

5 Berglunds snabbköp Alfred Schmidt Berguvsväge n 8 Hambur g S 958 22 Sweden

c. DELETE

SQL DELETE Statement

The DELETE statement is used to delete records in a table.

The SQL DELETE Statement

The DELETE statement is used to delete rows in a table.

SQL DELETE Syntax

DELETE FROM table_name

WHERE some_column=some_value;

Notice the WHERE clause in the SQL DELETE statement!

The WHERE clause specifies which record or records that should be deleted. If you omit the WHERE clause, all records will be deleted!

Demo Database

Below is a selection from the "Customers" table:

CustomerI D CustomerNam e ContactNam e Address City PostalCod e Country

1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 German y

2 Ana Trujillo Emparedados y helados Ana Trujillo

3

Avda. de la Constitución 2222 México D.F. 05021 Mexico

Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico

4 Around the Horn Thomas Hardy 120 Hanover Sq. Londo n WA1 1DP UK

5 Berglunds snabbköp Christina Berglund Berguvsväge n 8 Luleå S 958 22 Sweden

SQL DELETE Example

Assume we wish to delete the customer "Alfreds Futterkiste" from the "Customers" table.

We use the following SQL statement:

Example

DELETE FROM Customers

WHERE CustomerName='Alfreds Futterkiste' AND ContactName='Maria Anders';

The "Customers" table will now look like this:

CustomerI D CustomerNam e ContactNam e Address City PostalCod e Countr y

2 Ana Trujillo Emparedados y helados Ana Trujillo

3

Avda. de la Constitución 2222 México D.F. 05021 Mexico

Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico

4 Around the Horn Thomas Hardy 120 Hanover Sq. Londo n WA1 1DP UK

5 Berglunds snabbköp Christina Berglund Berguvsväge n 8 Luleå S 958 22 Sweden

Delete All Data

It is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact:

DELETE FROM table_name; or

DELETE * FROM table_name;

Note: Be very careful when deleting records. You cannot undo this statement!

d. SELECT

The SQL SELECT Statement

The SELECT statement is used to select data from a database.

The result is stored in a result table, called the result set.

SQL SELECT Syntax

SELECT column_name,column_name FROM table_name; and SELECT * FROM table_name;

e. DISTINC

The SQL SELECT DISTINCT Statement

The SELECT DISTINCT statement is used to return only distinct (different) values.

In a table, a column may contain many duplicate values; and sometimes you only want to list the different (distinct) values.

The DISTINCT keyword can be used to return only distinct (different) values.

SQL SELECT DISTINCT Syntax

SELECT DISTINCT column_name,column_name FROM table_name;

SELECT DISTINCT Example

The following SQL statement selects only the distinct values from the "City" columns from the "Customers" table:

Example

SELECT DISTINCT City FROM Customers;

Below is a selection from the "Customers" table: CustomerID CustomerName ContactName Address City PostalCode Country

1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany

2 Ana Trujillo Emparedados y helados

Ana Trujillo Avda. de la Constitución 2222

México D.F. 05021 Mexico

3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico

4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK

5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S 958 22 Sweden

Result: City México D.F. Luleå Strasbourg Madrid Marseille Tsawassen London Buenos Aires Bern

Paulo Nantes

SQL SELECT AS Alias

TheASkeywordisusedtogivecolumnsortablesatemporarynamethatcan beusedtoidentify that column or table later. For example,

SELECT first_name AS name FROM Customers; Run Code

Here, the SQL command selects the first_name of Customers. However, its column name will be name instead of first_name in the result set.

SQL AS With More Than One Column

We can also use aliases with more than one column. For example, SELECT customer_id AS cid, first_name AS name FROM Customers;

SQL ORDER BY Clause

The SQL ORDER BY clause is used to sort the result set in either ascending or descending order. For example, SELECT * FROM Customers ORDER BY first_name; Run Code

Here, the SQL command selects all customers and then sorts them in ascending order by first_name.

Example: ORDER BY in SQL ORDER BY ASC (Ascending Order)

We can use the ASC keyword explicitly to sort selected records in ascending order. For example,

SELECT * FROM Customers ORDER BY age ASC;

Run Code

Here, the SQL command selects all the customers and then sorts them in ascending order by age.

Example: ORDER BY ASC in SQL ORDER BY DESC (Descending Order)

We use the DESC keyword to sort the selected records in descending order. For example, SELECT * FROM Customers ORDER BY age DESC;

Run Code

Here, the SQL command selects all the customers and then sorts them in descending order by age.

Example: ORDER BY DESC in SQL

ORDER BY With Multiple Columns

We can also use ORDER BY with multiple columns. For example, SELECT * FROM Customers

ORDER BY first_name, age; Run Code

Here, the SQL command selects all the records and then sorts them by first_name. If the first_name repeats more than once, it sorts those records by age.

Example: SQL ORDER BY with multiple columns

ORDER BY With WHERE

We can also use ORDER BY with the SELECT WHERE clause. For example, SELECT last_name, age FROM Customers WHERE NOT country = 'UK' ORDER BY last_name DESC;

Run Code

• The SQL command first selects last_name and age fields from the Customers table if their country is not UK.

•

Then, the selected records are sorted in descending order by their last_name.

Example: ORDER BY with WHERE in SQL

Note: The WHERE clause must appear before the ORDER BY clause, while using the WHERE clause with ORDER BY.

3.3 QUERY WITH SQL

SQL WHERE Clause

The WHERE clause is used to filter records.

The SQL WHERE Clause

The WHERE clause is used to extract only those records that fulfill a specified criterion.

SQL WHERE Syntax

SELECT column_name,column_name

FROM table_name

WHERE column_name operator value;

Demo Database

Below is a selection from the "Customers" table:

CustomerI D CustomerNam e ContactNam e Address City PostalCod e Country

1

Alfreds Futterkiste

Maria Anders Obere Str. 57 Berlin 12209 German y

2 Ana Trujillo Emparedados y helados Ana Trujillo

3 Antonio Moreno Taquería

Avda. de la Constitución 2222 México D.F. 05021 Mexico

Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico

4 Around the Horn Thomas Hardy 120 Hanover Sq. Londo n WA1 1DP UK

5 Berglunds snabbköp Christina Berglund Berguvsväge n 8 Luleå S 958 22 Sweden

WHERE Clause Example

The following SQL statement selects all the customers from the country "Mexico", in the "Customers" table:

Example

SELECT * FROM Customers

WHERE Country='Mexico';

Text Fields vs. Numeric Fields

SQL requires single quotes around text values (most database systems will also allow double quotes).

However, numeric fields should not be enclosed in quotes:

Example

SELECT * FROM Customers

WHERE CustomerID=1;

Operators in The WHERE Clause

The following operators can be used in the WHERE clause:

Operator Description

Equal

Not equal. Note: In some versions of SQL this operator may be written as !=

> Greater than

Less than

Greater than or equal

Less than or equal

BETWEEN Between an inclusive range LIKE Search for a pattern

IN To specify multiple possible values for a column

Simple queries in SQL

SQL AND, OR, and NOT Operators

In this tutorial, we'll learn to use the AND, OR, and NOT operators in SQL with the help of various examples.

The AND, OR and NOT operators in SQL are used with the WHERE or HAVING clauses.

SQL AND Operator

The SQL AND operator selects data if all conditions are TRUE. For example,

SELECT first_name, last_name FROM Customers WHERE country = 'USA' AND last_name = 'Doe';

Run Code

Here, the SQL command selects first_name and last_name of all customers where the country is USA and last_name as Doe from the Customers table.

SQL OR Operator

The SQL OR operator selects data if any one condition is TRUE. For example,

SELECT first_name, last_name FROM Customers

WHERE country = 'USA' OR last_name = 'Doe';

Run Code

Here, the SQL command selects first_name and last_name of all customers where the country is USA or if their last name is Doe from the Customers table.

Example: SQL AND Operator

Example: SQL OR Operator

SQL NOT Operator

The SQL NOT operator selects data if the given condition is FALSE. For example SELECT first_name, last_name FROM Customers

WHERE NOT country = 'USA'; Run Code

Here, the SQL command selects first_name and last_name of all customers where the country is not USA from the Customers table.

Example: SQL NOT Operator

Combining Multiple Operators

It is also possible to combine multiple AND, OR and NOT operators in an SQL statement. For example,

Let's suppose we want to select customers where the country is either USA or UK, and the age is less than 26.

SELECT *

FROM Customers

WHERE (country = 'USA' OR country = 'UK') AND age < 26; Run Code

Example: SQL AND and OR Operators

Let's take a look at another example

SELECT *

FROM Customers

WHERE NOT country = 'USA' AND NOT last_name = 'Doe'; Run Code

Here, the SQL command selects all customers where the country is not USA and last_name is not Doe from the Customers table.

Example: SQL AND and NOT Operators

BETWEEN

he BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.

The BETWEEN operator is inclusive: begin and end values are included.

BETWEEN filters your query to return only results that fit a specified range.

BETWEEN Syntax

SELECT column_name(s)

FROM table_name

WHERE column_name BETWEEN value1 AND value2;

SELECT name FROM customers

WHERE age BETWEEN 45 AND 55;

For example, SELECT item, amount FROM Orders WHERE amount BETWEEN 300 AND 500; Run Code

Here, the SQL command selects all orders that have amounts between 300 and 500 including 300 and 500.

Example: SQL BETWEEN Operator

SQL BETWEEN OPERATOR With Texts

The BETWEEN operator also works with texts. For example, SELECT item, amount FROM Orders WHERE item BETWEEN 'I' AND 'L'; Run Code

Here, the SQL command selects all orders where the item name lies between I and L.

Example: SQL BETWEEN Operator With Text

Here, the list of values that the above command selects that starts with L.

Text Remarks

L selects Laptop doesn't select Lan Cable doesn't select Lamp doesn't select

It's because Laptop, Lan Cable and Lamp do not lie between I and L.

If we need to include all the words that start with L as well, we can use ~ like this.

SELECT item, amount FROM Orders

WHERE item BETWEEN 'I' AND 'L~';

Let's take another example of BETWEEN with texts. SELECT item FROM Orders

WHERE item BETWEEN 'Key' AND 'Mou';

f. LIKE

The SQL LIKE Operator

The LIKE operator is used to search for a specified pattern in a column.

SQL LIKE Syntax

SELECT column_name(s)

FROM table_name

WHERE column_name LIKE pattern;

Below is a selection from the "Customers" table:

CustomerID CustomerName ContactName Address City PostalCode Country

1

Alfreds Futterkiste

2 Ana Trujillo Emparedados y helados

Maria Anders Obere Str. 57 Berlin 12209 Germany

Ana Trujillo Avda. de la Constitución 2222

México D.F. 05021 Mexico

3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico

4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK

5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S 958 22 Sweden

SQL LIKE Operator Examples

The following SQL statement selects all customers with a C starting with the letter "s": Example

SELECT * FROM Customers

WHERE Country LIKE 's%'; Result:

CustomerID CustomerName ContactName Address City PostalCode Country

5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S 958 22 Sweden

The following SQL statement selects all customers with a Country containing the pattern "land": Example

SELECT * FROM Customers

WHERE Country LIKE '%land%';

Result: Customer ID CustomerNa me ContactNa me Address City PostalCo de Country

14 Chop suey Chinese Yang Wang Hauptstr. 29 Bern 3012 Switzerla nd

Hungry Owl All Night Grocers

Patricia McKenna 8 Johnstown Road Cork Ireland

Richter Supermarkt Michael Holz Grenzacher weg 237 Genèv e 1203 Switzerla nd

Wartian Herkku Pirkko Koskitalo Torikatu 38 Oulu 90110 Finland

90 Wilman Kala Matti Karttunen Keskuskatu 45 Helsin ki 21240 Finland

91 Wolski Zbyszek ul. Filtrowa 68 Walla 01 012 Poland

Using the NOT keyword allows you to select records that do NOT match the pattern.

The following SQL statement selects all customers with Country NOT containing the pattern "land": Example

SELECT * FROM Customers

WHERE Country NOT LIKE '%land%';

Ana Trujillo Emparedados y helados

Ana Trujillo Avda. de la Constitución

México D.F. 05021 Mexico

Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico

Berglunds snabbköp Christina Berglund Berguvsvägen

Luleå S 958 22 Sweden

Blondel père et fils Frédérique Citeaux 24, place Kléber Strasbourg 67000 France

Bólido Comidas preparadas Martín Sommer C/ Araquil, 67 Madrid 28023 Spain

Bon app' Laurence Lebihans 12, rue des Bouchers Marseille 13008 France

Bottom Dollar Marketse Elizabeth Lincoln 23 Tsawassen Blvd. Tsawassen T2F 8M4 Canada

g. IN The IN Operator

The IN operator allows you to specify multiple values in a WHERE clause.

SQL IN Syntax

SELECT column_name(s)

FROM table_name

column_name IN (value1,value2,...);

IN Operator Example

The following SQL statement selects all customers with a City of "Paris" or "London": Example

SELECT * FROM Customers WHERE City IN ('Paris','London');

B's Beverages Victoria Ashworth Fauntleroy Circus London EC2 5NT UK

Consolidated Holdings Elizabeth Brown Berkeley Gardens 12 Brewery

London WX1 6LT UK

Eastern Connection Ann Devon 35 King George London WX3 6FW UK

North/South Simon Crowther South House 300 Queensbridge

Paris spécialités Marie Bertrand 265, boulevard Charonne

London SW7 1RZ UK

Paris 75012 France

Seven Seas Imports Hari Kumar 90 Wadhurst Rd. London OX15 4NB UK

74 Spécialités du monde Dominique Perrier 25, rue Lauriston Paris 75016 France

Key points

• DDL is used to create and alter database objects.

• The three major commands comprising DDL are CREATE, DROP, and ALTER.

• You can apply these commands to most of the objects in your database.

• For example, you can CREATE, DROP, and ALTER tables, views, functions, stored procedures, triggers, and databases.

• Other objects such as indexes and defaults can be created and dropped but not altered.

FUNCTIONS IN STRUCTURED QUERY LANGUAGE(SQL)

In this chapter, you will learn how to:

Construct aggregate function in SQL

Manipulate table manipulation function

Apply database administration function

User Defined Functions play an important role in SQL Server. User Defined functions can be used to perform a complex logic, can accept parameters and return data. Many a times we have to write complex logic which cannot be written using a single query. In such scenarios, UDFs play an important role.

4.1 AGGREGATE FUNCTION IN SQL

Aggregate Function in Calculation

SQL aggregate functions return a single value, calculated from values in a column.

Useful aggregate functions:

• COUNT() Returns the number of rows

• SUM() Returns the sum

• AVG() Returns the average value

• MAX() - Returns the largest value

• MIN() Returns the smallest value

• ROUND() Rounds a numeric field to the number of decimals specified

a. COUNT

The COUNT() function returns the number of rows that matches a specified criteria.

SQL COUNT(column_name) Syntax

The COUNT(column_name) function returns the number of values (NULL values will not be counted) of the specified column:

SELECT COUNT(column_name) FROM table_name;

SQL COUNT(*) Syntax

The COUNT(*) function returns the number of records in a table:

SELECT COUNT(*) FROM table_name; SQL COUNT(DISTINCT column_name) Syntax

The COUNT(DISTINCT column_name) function returns the number of distinct values of the specified column:

SELECT COUNT(DISTINCT column_name) FROM table_name; Note: COUNT(DISTINCT) works with ORACLE and Microsoft SQL Server, but not with Microsoft Access.

Demo Database

In this tutorial we will use the well known Northwind sample database.

Below is a selection from the "Orders" table:

OrderID CustomerID EmployeeID OrderDate ShipperID

10265 7 2 1996 07 25 1

10266 87 3 1996 07 26 3

10267 25 4 1996 07 29 1

SQL COUNT(column_name) Example

The following SQL statement counts the number of orders from "CustomerID"=7 from the "Orders" table: Example

SELECT COUNT(CustomerID) AS OrdersFromCustomerID7 FROM Orders WHERE CustomerID=7;

b. SUM

The SUM() Function

The SUM() function returns the total sum of a numeric column.

SQL SUM() Syntax

SELECT SUM(column_name) FROM table_name;

Demo Database

In this tutorial we will use the well known Northwind sample database. Below is a selection from the "OrderDetails" table:

SQL SUM() Example

SUM(Quantity)

AVG

AVG() Function

AVG() Syntax

AVG(column_name) FROM table_name Demo Database

tutorial

OrderDetails;

"OrderDetails"

sample

4 Chef Anton's Cajun Seasoning

5 Chef Anton's Gumbo Mix

SQL AVG() Example

6 oz jars 21.35

boxes 25

The following SQL statement gets the average value of the "Price" column from the "Products" table: Example

SELECT AVG(Price) AS PriceAverage FROM Products;

The following SQL statement selects the "ProductName" and "Price" records that have an above average price: Example

SELECT ProductName, Price FROM Products WHERE Price>(SELECT AVG(Price) FROM Products);

d. MAX

The MAX() Function

The MAX() function returns the largest value of the selected column.

SQL MAX() Syntax

SELECT MAX(column_name) FROM table_name; Demo Database

In this tutorial we will use the well known Northwind sample database. Below is a selection from the "Products" table:

ProductID ProductName SupplierID CategoryID Unit Price

Chais 1

Chang

boxes x 20 bags 18

12 oz bottles 19

Aniseed Syrup

Chef Anton's Cajun Seasoning

Chef Anton's Gumbo Mix

SQL MAX() Example

12 550 ml bottles 10

6 oz jars 21.35

boxes 25

The following SQL statement gets the largest value of the "Price" column from the "Products" table: Example

SELECT MAX(Price) AS HighestPrice FROM Products; e. MIN The MIN() Function

The MIN() function returns the smallest value of the selected column. SQL MIN() Syntax

SELECT MIN(column_name) FROM table_name; Demo Database

In this tutorial we will use the well known Northwind sample database.

Below is a selection from the "Products" table:

Chais

Chang

Aniseed Syrup

Chef Anton's Cajun Seasoning

Unit Price

boxes x 20 bags 18

12 oz bottles 19

12 550 ml bottles 10

6 oz jars 21.35

SQL MIN() Example

The following SQL statement gets the smallest value of the "Price" column from the "Products" table: Example

SELECT MIN(Price) AS SmallestOrderPrice FROM Products;

f. ROUND

The ROUND() Function

The ROUND() function is used to round a numeric field to the number of decimals specified. Note: Many database systems do rounding differently than you might expect. When rounding a number with a fractional part to an integer, our school teachers told us to round .1 through .4 DOWN to the next lower integer, and .5 through .9 UP to the next higher integer. But if all the digits 1 through 9 are equally likely, this introduces a slight bias towards infinity, since we always round .5 up. Many database systems have adopted the IEEE 754 standard for arithmetic operations, according to which the default rounding behavior is "round half to even." In this scheme, .5 is rounded to the nearest even integer. So, both 11.5 and 12.5 would be rounded to 12.

SQL ROUND() Syntax

SELECT ROUND(column_name,decimals) FROM table_name; Parameter Description

column_name

decimals

Demo Database

Required. The field to round.

Required. Specifies the number of decimals to be returned.

In this tutorial we will use the well known Northwind sample database. Below is a selection from the "Products" table:

ProductID ProductName SupplierID CategoryID Unit Price

Syrup

Anton's Cajun

Anton's Gumbo Mix

SQL ROUND() Example

The following SQL statement selects the product name and rounds the price in the "Products" table: Example

SELECT ProductName, ROUND(Price,0) AS RoundedPrice FROM Products;

GROUP BY

SQL GROUP BY Statement

The GROUP BY statement is used in conjunction with the aggregate functions to group the result set by one or more columns.

SQL GROUP BY Syntax

SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name;

Demo Database

Below is a selection from the "Orders" table:

CustomerID

81 6 1996 07 05 1

10250 34 4 1996 07 08 2

And a selection from the "Shippers" table: ShipperID ShipperName Phone

1

Speedy Express (503) 555 9831

United Package (503) 555 3199

3 Federal Shipping (503) 555 9931

And a selection from the "Employees" table: EmployeeID LastName FirstName BirthDate Photo Notes

1 Davolio Nancy 1968 12 08 EmpID1.pic Education includes a BA....

2 Fuller Andrew 1952 02 19 EmpID2.pic Andrew received his BTS....

3 Leverling Janet 1963 08 30 EmpID3.pic Janet has a BS degree....

SQL GROUP BY Example

Now we want to find the number of orders sent by each shipper.

The following SQL statement counts as orders grouped by shippers: Example

SELECT Shippers.ShipperName,COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders

LEFT JOIN Shippers ON Orders.ShipperID=Shippers.ShipperID GROUP BY ShipperName;

Federal Shipping

Package

Function For String

SQL Scalar functions

SQL scalar functions return a single value, based on the input value.

Useful scalar functions:

LCASE() Converts a field to lower case

UCASE() Converts a field to upper case

LEN() Returns the length of a text field

MID() Extract characters from a text field

FIRST() - Returns the first value

LAST() Returns the last value

LCASE

The LCASE() Function

The LCASE() function converts the value of a field to lowercase.

LCASE() Syntax

SELECT LCASE(column_name) FROM table_name; Syntax for SQL Server

SELECT LOWER(column_name) FROM table_name; Demo Database

In this tutorial we will use the well known Northwind sample database.

Below is a selection from the "Customers" table:

CustomerName

PostalCode Country

ID

Alfreds Futterkiste

Ana Trujillo Emparedados y helados

Maria Anders Obere Str. 57 Berlin 12209 Germany

Ana Trujillo Avda. de la Constitución 2222

México D.F. 05021 Mexico

Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico

Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK

5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S 958 22 Sweden

SQL LCASE() Example

The following SQL statement selects the "CustomerName" and "City" columns from the "Customers" table, and converts the "CustomerName" column to lowercase: Example

SELECT LCASE(CustomerName) AS Customer, City FROM Customers;

b. UCASE The UCASE() Function

The UCASE() function converts the value of a field to uppercase. SQL UCASE() Syntax

SELECT UCASE(column_name) FROM table_name; Syntax for SQL Server

SELECT UPPER(column_name) FROM table_name; Demo Database

In this tutorial we will use the well known Northwind sample database.

Below is a selection from the "Customers" table:

Customer ID

CustomerName ContactName Address City PostalCode Country

1 Alfreds Futterkiste

2 Ana Trujillo Emparedados y helados

Maria Anders Obere Str. 57 Berlin 12209 Germany

Ana Trujillo Avda. de la Constitución 2222

México D.F. 05021 Mexico

3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico

4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK

5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S 958 22 Sweden

SQL UCASE() Example

The following SQL statement selects the "CustomerName" and "City" columns from the "Customers" table, and converts the "CustomerName" column to uppercase: Example

SELECT UCASE(CustomerName) AS Customer, City FROM Customers; c. LEN

The LEN() Function

The LEN() function returns the length of the value in a text field.

SQL LEN() Syntax

SELECT LEN(column_name) FROM table_name;

Demo Database

In this tutorial we will use the well known Northwind sample database.

Below is a selection from the "Customers" table:

Customer CustomerName ContactName Address City PostalCode Country

Anders

Ana Trujillo

Trujillo

de la

y

Antonio Moreno

Antonio Moreno

Around the Horn Thomas Hardy

Berglunds

Christina Berglund

Hanover

Berlin

D.F. 05021 Mexico

SQL LEN() Example

México D.F. 05023 Mexico

WA1 1DP UK

S 958 22 Sweden

The following SQL statement selects the "CustomerName" and the length of the values in the "Address" column from the "Customers" table: Example

SELECT CustomerName,LEN(Address) as LengthOfAddress FROM Customers;

d. MID

The MID() Function

The MID() function is used to extract characters from a text field.

SQL MID() Syntax

SELECT MID(column_name,start[,length]) AS some_name FROM table_name; Parameter Description column_name Required. The field to extract characters from start Required. Specifies the starting position (starts at 1)

length Optional. The number of characters to return. If omitted, the MID() function

Note: The equivalent function for SQL Server is SUBSTRING():

SELECT SUBSTRING(column_name,start,length) AS some_name FROM table_name; Demo Database

In this tutorial we will use the well known Northwind sample database.

Below is a selection from the "Customers" table:

CustomerID CustomerName

1

Alfreds Futterkiste

ContactName Address

Maria Anders Obere Str. 57

2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución

3 Antonio Moreno Taquería

4

Around the Horn

5 Berglunds snabbköp

SQL MID() Example

Antonio Moreno Mataderos 2312

Thomas Hardy 120 Hanover Sq.

Christina Berglund Berguvsvägen 8

The following SQL statement selects the first four characters from the "City" column from the "Customers" table: Example

SELECT MID(City,1,4) AS ShortCity FROM Customers;

e. FIRST

The FIRST() Function

The FIRST() function returns the first value of the selected column.

SQL FIRST() Syntax

SELECT FIRST(column_name) FROM table_name; Note: The FIRST() function is only supported in MS Access.

SQL FIRST() Workaround in SQL Server, MySQL and Oracle

SQL Server Syntax

SELECT TOP 1 column_name FROM table_name ORDER BY column_name ASC; Example

SELECT TOP 1 CustomerName FROM Customers ORDER BY CustomerID ASC; MySQL Syntax

SELECT column_name FROM table_name ORDER BY column_name ASC LIMIT 1; Example

SELECT CustomerName FROM Customers ORDER BY CustomerID ASC LIMIT 1; Oracle Syntax

SELECT column_name FROM table_name WHERE ROWNUM <=1 ORDER BY column_name ASC; Example

SELECT CustomerName FROM Customers WHERE ROWNUM <=1 ORDER BY CustomerID ASC;

Demo Database

In this tutorial we will use the well known Northwind sample database.

Below is a selection from the "Customers" table:

Customer ID

PostalCode Country

1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany

2 Ana Trujillo Emparedados y helados

Ana Trujillo Avda. de la Constitución 2222

México D.F. 05021 Mexico

4

the Horn

Hardy

5 Berglunds

SQL FIRST() Example

The following SQL statement selects the first value of the "CustomerName" column from the "Customers" table: Example

SELECT FIRST(CustomerName) AS FirstCustomer FROM Customers;

f. LAST

The LAST() Function

The LAST() function returns the last value of the selected column.

SQL LAST() Syntax

SELECT LAST(column_name) FROM table_name; Note: The LAST() function is only supported in MS Access. SQL LAST() Workaround in SQL Server, MySQL and Oracle SQL Server Syntax

SELECT TOP 1 column_name FROM table_name ORDER BY column_name DESC;

Example

SELECT TOP 1 CustomerName FROM Customers ORDER BY CustomerID DESC; MySQL Syntax

SELECT column_name FROM table_name ORDER BY column_name DESC

LIMIT 1;

Example

SELECT CustomerName FROM Customers

ORDER BY CustomerID DESC

LIMIT 1; Oracle Syntax

SELECT column_name FROM table_name

ORDER BY column_name DESC

WHERE ROWNUM <=1;

Example

SELECT CustomerName FROM Customers

ORDER BY CustomerID DESC

WHERE ROWNUM <=1; Demo Database

In this tutorial we will use the well known Northwind sample database.

Below is a selection from the "Customers" table:

Alfreds Futterkiste

Ana Trujillo Emparedados y helados

Anders

Ana Trujillo

de la Constitución

Berlin

México D.F.

Antonio Moreno Taquería Antonio Moreno

Around the Horn Thomas Hardy

Berglunds

Berglund

Hanover

México D.F.

SQL LAST() Example

The following SQL statement selects the last value of the "CustomerName" column from the "Customers" table:

UK

Example

LAST(CustomerName) AS LastCustomer FROM Customers;

Function for Date And Time

FORMAT() Formats how a field

- Returns the current system

to be displayed

time

CURRENT DATE returns the current date.

CURRENT TIME returns the current time.

a. FORMAT

The FORMAT() Function

FORMAT()

FORMAT() Syntax

used to format

a field

to be displayed.

FORMAT(column_name,format) FROM table_name;

The field to be formatted.

Specifies the format.

Demo Database

tutorial

use the well known Northwind sample database.

a selection from the "Products" table:

SQL FORMAT() Example

The following SQL statement selects the product name, and price for today (formatted like YYYY MM DD) from the "Products" table: Example

SELECT ProductName, Price, FORMAT(Now(),'YYYY MM DD') AS PerDate FROM Products;

b. NOW

The NOW() Function

The NOW() function returns the current system date and time.

NOW() Syntax

SELECT NOW() FROM table_name; Demo Database

In this tutorial we will use the well known Northwind sample database.

Below is a selection from the "Products" table:

ProductName SupplierID CategoryID Unit Price

Chais

Chang

Aniseed Syrup

Anton's Cajun Seasoning

Chef Anton's Gumbo Mix

SQL NOW() Example

10 boxes x 20 bags 18

24 12 oz bottles 19

12 550 ml bottles 10

6 oz jars 21.35

boxes 25

The following SQL statement selects the product name, and price for today from the "Products" table: Example

SELECT ProductName, Price, Now() AS PerDate FROM Products;

c. CURRENT DATE

Definition and Usage

CURDATE() returns the current date.

Syntax

CURDATE()

Example

The following SELECT statement: SELECT NOW(),CURDATE(),CURTIME() will result in something like this: NOW() CURDATE() CURTIME()

2014 11 11 12:45:34

Example

2014 11 11 12:45:34

The following SQL creates an "Orders" table with a datetime column (OrderDate): CREATE TABLE Orders ( OrderId int NOT NULL, ProductName varchar(50) NOT NULL, OrderDate datetime NOT NULL DEFAULT CURDATE(), PRIMARY KEY (OrderId) )

Notice that the OrderDate column specifies CURDATE() as the default value. As a result, when you insert a row into the table, the current date are automatically inserted into the column.

Now we want to insert a record into the "Orders" table: INSERT INTO Orders (ProductName) VALUES ('Jarlsberg Cheese')

The "Orders" table will now look something like this: OrderId ProductName OrderDate

1

Jarlsberg Cheese

2014 11 11

d. CURRENT TIME

Definition and Usage

CURTIME() returns the current time.

Syntax CURTIME()

Example

The following SELECT statement: SELECT NOW(),CURDATE(),CURTIME() will result in something like this:

NOW() CURDATE() CURTIME() 2014 11 11 12:45:34 2014 11 11 12:45:34

SQL JOIN

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

Different Types of SQL JOINs

Here are the different types of the JOINs in SQL:

• (INNER) JOIN: Returns records that have matching values in both tables

• LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table

• RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table

• FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table

QL INNER JOIN

The INNER JOIN keyword selects records that have matching values in both tables.

INNER JOIN Syntax

SELECT column_name(s) FROM table1

INNER JOIN table2 ON table1.column_name = table2.column_name;

Example

SELECT Customers.customer_id, Customers.first_name, Orders.amount FROM Customers

INNER JOIN Orders ON Customers.customer_id = Orders.customer;

Run Code

Here's how this code works:

Example: SQL INNER JOIN

Here, the SQL command selects customer_id and first_name columns (from the Customers table) and the amount column (from the Orders table).

And, the result set will contain those rows where there is a match between customer_id (of the Customers table) and customer (of the Orders table).

INNER JOIN With WHERE Clause

Here's an example of the INNER JOIN with the WHERE clause:

SELECT Customers.customer_id, Customers.first_name, Orders.amount FROM Customers INNER JOIN Orders ON Customers.customer_id = Orders.customer WHERE Orders.amount >= 500;

Run Code

Here, the SQL command joins two tables and selects rows where the amount is greater than or equal to 500.

SQL LEFT JOIN

The LEFT JOIN keyword returns all records from the left table (table1), and the matching records from the right table (table2). The result is 0 records from the right side, if there is no match.

LEFT JOIN Syntax

SELECT column_name(s)

FROM table1

LEFT JOIN table2

ON table1.column_name = table2.column_name;

Note: In some databases LEFT JOIN is called LEFT OUTER JOIN.

Example

SELECT Customers.customer_id, Customers.first_name, Orders.amount FROM Customers

LEFT JOIN Orders ON Customers.customer_id = Orders.customer;

Run Code

Example: SQL LEFT JOIN

Here, the SQL command selects customer_id and first_name columns (from the Customers table) and the amount column (from the Orders table).

And, the result set will contain those rows where there is a match between customer_id (of the Customers table) and customer (of the Orders table) along with all the remaining rows from the Customers table.

LEFT JOIN With WHERE Clause

The SQL command can have an optional WHERE clause with the LEFT JOIN statement. For example,

SELECT Customers.customer_id, Customers.first_name, Orders.amount FROM Customers

LEFT JOIN Orders

ON Customers.customer_id = Orders.customer

WHERE Orders.amount >= 500;

Run Code

Here, the SQL command joins two tables and selects rows where the amount is greater than or equal to 500.

SQL RIGHT JOIN Keyword

The RIGHT JOIN keyword returns all records from the right table (table2), and the matching records from the left table (table1). The result is 0 records from the left side, if there is no match.

RIGHT JOIN Syntax

SELECT column_name(s)

FROM table1

RIGHT JOIN table2

ON table1.column_name = table2.column_name;

Note: In some databases RIGHT JOIN is called RIGHT OUTER JOIN

Example

SELECT Customers.customer_id, Customers.first_name, Orders.amount

FROM Customers

RIGHT JOIN Orders

ON Customers.customer_id = Orders.customer;

Example: SQL RIGHT JOIN

Here, the SQL command selects customer_id and first_name columns (from the Customers table) and the amount column (from the Orders table).

And, the result set will contain those rows where there is a match between customer_id (of the Customers table) and customer (of the Orders table) along with all the remaining rows from the Orders table.

RIGHT JOIN With WHERE Clause

The SQL command can have an optional WHERE clause with the RIGHT JOIN statement. For example, SELECT Customers.customer_id, Customers.first_name, Orders.amount FROM Customers

INNER JOIN Orders ON Customers.customer_id = Orders.customer WHERE Orders.amount >= 500;

Here, the SQL command joins two tables and selects rows where the amount is greater than or equal to 500.

SQL FULL OUTER JOIN Keyword

The FULL OUTER JOIN keyword returns all records when there is a match in left (table1) or right (table2) table records.

Tip: FULL OUTER JOIN and FULL JOIN are the same.

FULL OUTER JOIN Syntax

SELECT column_name(s)

FROM table1

FULL OUTER JOIN table2

ON table1.column_name = table2.column_name

WHERE condition;

Example

SELECT Customers.customer_id, Customers.first_name, Orders.amount

FROM Customers

FULL OUTER JOIN Orders

ON Customers.customer_id = Orders.customer;

Here's how this code works:

Example: SQL FULL OUTER JOIN

Here, the SQL command selects customer_id and first_name columns (from the Customers table) and the amount column (from the Orders table).

And, the result set will contain those rows where there is a match between customer_id (of the Customers table) and customer (of the Orders table) along with all the remaining rows from both of the tables.

FULL OUTER JOIN With WHERE Clause

The SQL command can have an optional WHERE clause with the FULL OUTER JOIN statement. For example,

SELECT Customers.customer_id, Customers.first_name, Orders.amount

FROM Customers

FULL OUTER JOIN Orders

ON Customers.customer_id = Orders.customer

WHERE Orders.amount >= 500;

Here, the SQL command joins two tables and selects rows where the amount is greater than or equal to 500.

4.2 TABLE MANIPULATION FUNCTION IN SQL

• CREATE VIEW

• UNION

• INTERSECT

• EXCEPT

a. CREATE VIEW

Views can be considered as virtual tables. Generally speaking, a table has a set of definition, and it physically stores the data. A view also has a set of definitions, which is build on top of table(s) or other view(s), and it does not physically store the data.

The syntax for creating a view is as follows:

CREATE VIEW "VIEW_NAME" AS "SQL Statement";

"SQL Statement" can be any of the SQL statements we have discussed in this tutorial.

Let's use a simple example to illustrate. Say we have the following table:

Table Customer

Column Name Data Type

First_Name char(50)

Last_Name char(50) Address char(50) City char(50) Country char(25) Birth_Date datetime

and we want to create a view called V_Customer that contains only the First_Name, Last_Name, and Country columns from this table, we would type in,

CREATE VIEW V_Customer

AS SELECT First_Name, Last_Name, Country FROM Customer;

Now we have a view called V_Customer with the following structure: View V_Customer

Column Name Data Type

First_Name char(50)

Last_Name char(50) Country char(25)

We can also use a view to apply joins to two tables. In this case, users only see one view rather than two tables, and the SQL statement users need to issue becomes much simpler. Let's say we have the following two tables:

Table Store_Information

Table

East

and we want to build a view that has sales by region information. We would issue the following SQL statement:

CREATE VIEW V_REGION_SALES

AS SELECT A1.Region_Name REGION, SUM(A2.Sales) SALES

FROM Geography A1, Store_Information A2 WHERE A1.Store_Name = A2.Store_Name GROUP BY A1.Region_Name;

This gives us a view, V_REGION_SALES, that has been defined to store sales by region records. If we want to find out the content of this view, we type in,

SELECT * FROM V_REGION_SALES;

Result:

REGION SALES

East 700 West 2050

b. UNION

The SQL UNION Operator

The UNION operator is used to combine the result set of two or more SELECT statements.

Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.

SQL UNION Syntax

SELECT column_name(s) FROM table1

UNION

SELECT column_name(s) FROM table2;

Note: The UNION operator selects only distinct values by default. To allow duplicate values, use the ALL keyword with UNION.

SQL UNION ALL Syntax

SELECT column_name(s) FROM table1

UNION ALL

SELECT column_name(s) FROM table2;

PS: The column names in the result set of a UNION are usually equal to the column names in the first SELECT statement in the UNION.

Demo Database

In this tutorial we will use the well known Northwind sample database.

Below is a selection from the "Customers" table: CustomerID CustomerName ContactName Address City PostalCode Country

1

Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany

2 Ana Trujillo Emparedados y helados

Ana Trujillo Avda. de la Constitución 2222

México D.F. 05021 Mexico

3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico

And a selection from the "Suppliers" table: SupplierID SupplierName ContactName Address City PostalCode Country

1 Exotic Liquid Charlotte Cooper 49 Gilbert St. Londona EC1 4SD UK

2 New Orleans Cajun Delights

Shelley Burke P.O. Box 78934 New Orleans 70117 USA

3 Grandma Kelly's Homestead Regina Murphy 707 Oxford Rd.

SQL UNION Example

Ann Arbor 48104 USA

The following SQL statement selects all the different cities (only distinct values) from the "Customers" and the "Suppliers" tables:

Example

SELECT City FROM Customers UNION

SELECT City FROM Suppliers ORDER BY City;

Note: UNION cannot be used to list ALL cities from the two tables. If several customers and suppliers share the same city, each city will only be listed once. UNION selects only distinct values. Use UNION ALL to also select duplicate values!

SQL UNION ALL Example

The following SQL statement uses UNION ALL to select all (duplicate values also) cities from the "Customers" and "Suppliers" tables: Example

SELECT City FROM Customers

UNION ALL

SELECT City FROM Suppliers ORDER BY City;

SQL UNION ALL With WHERE

The following SQL statement uses UNION ALL to select all (duplicate values also) German cities from the "Customers" and "Suppliers" tables: Example

SELECT City, Country FROM Customers

WHERE Country='Germany'

UNION ALL SELECT City, Country FROM Suppliers WHERE Country='Germany' ORDER BY City;

4.3 DATABASE ADMINISTRATION FUNCTION

Data Control Language

Data control commands in SQL allow you to control access to data within the database. These Data Control Language (DCL) commands are normally used to create objects related to user access and also control the distribution of privileges among users. Some data control commands are as follows: ALTER PASSWORD GRANT REVOKE

CREATE SYNONYM

You will find that these commands are often grouped with other commands.

SQL GRANT REVOKE Commands

DCL commands are used to enforce database security in a multiple user database environment. Two types of DCL commands are GRANT and REVOKE. Only Database Administrator's or owner's of the database object can provide/remove privileges on a database object.

• GRANT

• REVOKE

a. GRANT SQL GRANT Command

SQL GRANT is a command used to provide access or privileges on the database objects to the users.

The Syntax for the GRANT command is: GRANT privilege_name ON object_name TO {user_name |PUBLIC |role_name}

[WITH GRANT OPTION];

For Example: GRANT SELECT ON employee TO user1; This command grants a SELECT permission on employee table to user1.You should use the WITH GRANT option carefullybecauseforexampleif youGRANTSELECT privilegeon employeetable to user1 using the WITH GRANT option, then user1 can GRANT SELECT privilege on employee table to another user, such as user2 etc. Later, if you REVOKE the SELECT privilege on employee from user1, still user2 will have SELECT privilege on employee table.

It's easier to GRANT or REVOKE privileges to the users through a role rather than assigning aprivilegedirectlyto everyuser. If a roleis identified byapassword,then, when you GRANT or REVOKE privileges to the role, you definitely have to identify it with the password.

We can GRANT or REVOKE privilege to a role as below.

For example: To grant CREATE TABLE privilege to a user by creating a testing role:

First, create a testing Role

CREATE ROLE testing

Second, grant a CREATE TABLE privilege to the ROLE testing. You can add more privileges to the ROLE.

GRANT CREATE TABLE TO testing;

Third, grant the role to a user. GRANT testing TO user1;

b. REVOKE

SQL REVOKE Command:

The REVOKE command removes user access rights or privileges to the database objects. The Syntax for the REVOKE command is: REVOKE privilege_name ON object_name

FROM {user_name |PUBLIC |role_name}

For Example: REVOKE SELECT ON employee FROM user1;This command will REVOKEaSELECT privilegeon employeetable from user1.When youREVOKESELECT privilege on a table from a user, the user will not be able to SELECT data from that table anymore. However, if the user has received SELECT privileges on that table from more than one users, he/she can SELECT from that table until everyone who granted the permission revokes it. You cannot REVOKE privileges if they were not initially granted by you.

To revoke a CREATE TABLE privilege from testing ROLE, you can write: REVOKE CREATE TABLE FROM testing;

The Syntax to drop a role from the database is as below: DROP ROLE role_name;

For example: To drop a role called developer, you can write: DROP ROLE testing;

•

Key points

performs a calculation on multiple values and returns a single value.

aggregate function in

SQL provides many aggregate functions that include avg, count, sum, min, max, etc.

• An aggregate function ignores NULL values when it performs the calculation, except for the count function.

Chapter

DATABASE SECURITY

5.1 DATABASE SECURITY

Securityreferstoactivitiesandmeasurestoensuretheconfidentiality,integrity,and availability of an information system and its main asset, data. It is important to understand that securing data requires a comprehensive, company wide approach. That is, you cannot secure data if you do not secure all the processes and systems around it. Indeed, securing data entails securing the overall information system architecture, including hardware systems, software applications, the network and its devices, people (internal and external users), procedures, and the data itself.

To understand the scope of data security, let’s discuss each of the three security goals in more detail:

i. Confidentiality deals with ensuringthat data is protected against unauthorized access, and if the data are accessed by an authorized user, that the data are used only for an authorized purpose. In other words, confidentialityentails safeguardingdata against disclosure of any information that would violate the privacy rights of a person or organization. Data must be evaluated and classified according to the level of confidentiality: highly restricted (very few people have access), confidential (only certain groups have access), and unrestricted (can be accessed by all users). The data security officer spends a great amount of time ensuring that the organization is in compliance with the desired levels of confidentiality.

Compliance refers to activities undertaken to meet data privacy and security reporting guidelines. These reporting guidelines are either part of internal procedures or are imposed by external regulatory agencies such as the federal government. Examples of U.S. legislation enacted with the purpose of ensuring data privacy and confidentialit y include the Health Insurance Portability and Accountability Act (HIPAA), Gramm Leach Bliley Act (GLBA), and Sarbanes Oxley Act (SOX).5

In this chapter, you will learn how to: Describe the database security Identify threats to database Explain security countermeasure

ii. Integrity, within the data security framework, is concerned with keeping data consistent, free of errors or anomalies. Integrity focuses on maintaining the data free of inconsistencies and anomalies (see Chapter 1, Database Systems, to review the concepts of data inconsistencies and data anomalies). The DBMS plays a pivotal role in ensuring the integrity of the data in the database. However, from the security point of view, integrity deals not only with the data in the database, but also with ensuring that organizational processes, users, and usage patterns maintain such integrity. For example, a work at home employee using the Internet to access product costing could be considered an acceptable use; however, security standards might require the employee to use a secure connection and follow strict procedures to manage the data at home (shredding printed reports, using encryption to copy data to the local hard drive, etc.). Maintaining the integrity of the data is a process that starts with data collection and continues with data storage, processing, usage, and archival. The rationale behind integrity is to treat data as the most valuable asset in the organization and therefore to ensure that rigorous data validation is carried out at all levels within the organization.

iii. Availability refers to the accessibility of data whenever required by authorized users and for authorized purposes. To ensure data availability, the entire system (not only the data component) must be protected from service degradation or interruption caused by any source (internal or external). Service interruptions could be very costly for companies and users alike recall the JetBlue6 case in the Part V Business Vignette of this book, and, more recently the case of SKYPE, the voice over IP (VoIP) telephone service provider who suffered a 48 hour worldwide service interruption.7 System availability is an important goal of security.

Definition

Security considerations do not only apply to the data held in a database. Breaches of security may affect other parts of the system, which may in turn affect the database. Consequently, database security encompasses hardware, software, people, and data. Database security refers to the collective measures used to protect and secure a database or database management software from illegitimate use and malicious threats and attacks.

Scope

To effectively implement security requires appropriate controls, which are defined in specific mission objectives for the system. This need for security, while often having been neglected or overlooked in the past, is now increasingly recognized by organizations. The reason for this turn around is due to the increasing amounts of crucial corporate data being stored on computer and the acceptance that any loss or unavailability of this data could be potentially disastrous.

Security Guidelines

Security guidelines that a conscientious database designer should follow:

i. Keep the database simple

ii. Normalize the database

iii. Always follow the principle of assuming privileges must be explicitly granted rather than excluded

iv. Create unique views for each user or group of users

5.2 DATABASE SECURITY THREATS

Database security begins with physical security for the systems that host the database management system (DBMS). Database Management system is not safe from intrusion, corruption,ordestructionbypeoplewhohavephysical access to thecomputers.Oncephysical security has been established, database must be protected from unauthorized access by authorized users as well as unauthorized users. There are three main objects when designing a secure database system, and anything prevents from a database management system to achieve these goals would be consider a threat to database security. There are many internal and external threats to database systems.

Some of threats are as follows:

a. Weak Authentication

Weak authentication schemes allow attackers to assume the identity of legitimate database users. Specific attack strategies include brute force attacks, social engineering, and so on. Implementation of passwords or two factor authentication is a must.

b. Excessive privileges

Privileges of database can be abused in many ways. User may abuse privilege for unauthorized purpose. Privilege abuse comes in different flavours: Excessive privilege abuse, legitimate privileges abuse and unused privilege abuse. This type of threat is most dangerous because authorized users are doing misuse of data. These privileges can be abused and creates unnecessary risk. Granting excessive permissions is problematic for two reasons. About 80% of the attacks on company data are actually executed by employees or ex employees. Granting too many privileges or not revoking those privileges in time makes it unnecessarily simple for them to execute their wrongdoing. Some of these actions might even be executed inadvertently or without the perception of those actions being illegal Abuse of legitimate privileges can be considered database vulnerability, if the malicious user misuses their database access privileges.

c. Malware

- Cybercriminals, state-sponsored hackers, and spies use advanced attacks that blend multiple tactics such as spear phishing emails and malware to penetrate organizations and steal sensitive data. Unaware that malware has infected their device; legitimate users become a conduit for these groups to access your networks and sensitive data.

d. Backup Exposure

Backup storage media is often completely unprotected from attack. As a result, numerous security breaches have involved the theft of database backup disks and tapes. Furthermore, failure to audit and monitor the activities of administrators who have low level access to sensitive information can put your data at risk. Taking the appropriate measures to protect backup copies of sensitive data and monitor your most highly privileged users is not only a data security best practice, but also mandated by many regulations.

5.3 DATABASE SECURITY COUNTERMEASURES

To protect the database system from the above mentioned threats. Here are some countermeasures which are as follows:

a. Authentication:

Access to the database is a matter of authentication. It provides the guidelines how the database is accessed. Every access should be monitored.

b. Access Control:

A database for an organization contains a great deal of information and usually has several users. Most of them need to access only a small part of the database. A policy defines the requirements that are to be implemented within hardware and software and those that are external to the system, including physical, personal, and procedural controls.

c. Encryption:

An encryption algorithm should be applied to the data, using a user specified encryption key. The output of the algorithm is the encrypted version. There is also a decryption algorithm, which takes the encrypted data and a decryption key as input and then returns the original data.

d. firewall protection and Install Antivirus

Firewall is the specified version of router. In this all data packets are entering or leaving the network pass through firewall and after examine firewall decide whether to allow or not.In firewall all traffic must pass through it and only authorized traffic should be allow to pass. Firewall should be strong.

e. Backup:

At every instant, backup should be done. In case of any disaster, Organizations can retrieve their data.

KEY POINT

• The institutions, organizations and business firms mainly stored their important information and valuable assets as digital formats in online related excellent databases.

• The safety and security issues of database become strongly an essential role in the modern world for enterprises.

• To save from harm of database is to prevent the companies & a circum touchable information resources and digital belongings. Database is the multifarious system and very complicated to handle and difficult to prevent from invaders.

• Database protection is also to be taken significantly to the confidentiality, availability and integrity of the organizations like other measures of the safety systems.

• It can be guarded as diverse natures to cover up. Although auditing is critical, but analysis is also very tough while potential analytical tools will be an enormous contribution to protect the online rationality of database system.

• There should be reinforced to the corporate safety and security issues. Means of verification and encryption will play the essential role in modern database precaution and safety system.

QUESTION AND ANSWER

What is Database Management System (DBMS)?

The computer Software to manage, maintain database as well as view update and retrieve data is called database management system.

What is Database?

A database is an organization of data related to a particular subject or purpose so that the data can be retrieved or processed.

What is relational database?

A database with tables related to each other on a common field to facilitate the data retrieval from multiple tables is known as relational database.

What is a key field?

A common field on which two tables are linked is known as key field.

What is primary key?

A primary key is a rule which ensures that unique data is entered for the field and the field is not left blank. This is the field that would identify a record uniquely in table.

What do you mean by foreign key?

The common field in child table that maintains relation with master table is foreign key.

What are the elements of a database?

The major six elements of a database are Tables, Queries, Form, Reports, Macros, Modules .

What is a table?

A table is a collection of data about a specific topic such as products, students or suppliers. A table organizes data into columns (fields) and rows (records or tuples).

What is a field?

A field in a database is a piece of information about a subject. Each field is arranged as a column in table.

What is a record?

A record is complete information about a subject. A record is a collection of fields and presented as a row in a table of database

What is a query?

A query is a question about data in database. It results a set of data from database that can be used as a source of records for reports and forms.

Define relationship.

Arelationship is alogical linkagebetweentwo entities thatdescribehowthe entities areassociated with each other.

How many relationships are possible between two tables or entities?

Therearethreerelationshipspossiblebetweentwotablesnamely: one to one,one to many,many to many.

What do you understand by one-to-one relationship between two tables?

A relation between two tables such that one primary key value corresponds to one foreign key value is known as one to one relationship. In other words, each record in parent table is related to one and only one record in child table is one to one relationship.

Clarify one to many relationship between two tables.

A relation between two tables such that one primary key value corresponds to many foreign key value is known as one to many relationship. In other words, each record in parent table is related to zero or many records in child table is one to many relationship.

Define many to many relationship.

A relationship where many instances of an entity are associated with many instances of another entity is known as many to many relationship. Consider the enrollment relationship between a student and a class tables. A single student maybe enrolled in many classes and a single class may enroll many students.

EXERCISE 1 : CREATE ENTITIY RELATIONSHIP DIAGRAM AND DATABASE

All exercises will be referred to this problem scenario.

Your teacher, Mrs. Salmi has asked you to create a system that can keep all information about extra curricular activities in her school. The database should store the information of students, extra curricular activities and their members. Create ERD notation to represent the database base on this info:

• Each student has to give detail of his personal information.

• The detail of every extra curricular should be supplied.

• One student can involve in more than one extra curricular.

You are required to :

1. list attributes for every entities

2. find key attributes for each of the entity

3. illustrate them using ERD notation

4. connect both entities with a correct type of relationship.

5. find associative entity and attributes.

6. identify all type of keys involved.

7. configure the cardinality ratios and degree of relationship.

After that, you have to

1. Identify the best normal formal (UNF, 1NF, 2NF OR 3NF).

2. Write functional dependencies, relation and table.

Create database name SisKo in your own folder.